calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Danny Chan <yuzhao....@gmail.com>
Subject Re: Preserving CAST of STRING operands in comparison operator
Date Fri, 30 Aug 2019 01:49:24 GMT
This is kind of in the scope of implicit type coercion which is supported in CALCITE-2302.

For sql dialect that does not support implicit type coercion, strip explicit cast is a mistake.

I think this can be seen as a bug and we should log an issue to fix. But just like you said,
if we support it for every sql dialect, it would be a huge work, we should think of a flexible
way.

Best,
Danny Chan
在 2019年8月28日 +0800 PM4:56,Soma Mondal <mail.mondal.soma@gmail.com>,写道:
> Hi Julian,
>
> After some further analysis, it seems that the mandatory cast is only
> required in SOME cases for BigQuery.
> Please see attached my analysis for Hive, MySQL, Netezza and Oracle.
>
> https://drive.google.com/open?id=1GJ_VuDY7GQS-aPbWf4EKj73dYCqaRaEqPTjmXLkPW_g
>
> I'm thinking of having the dialect intercept this and check the specific
> conditions (specified in the sheet above) and decide whether or not to
> remove the cast.
>
> Regards,
> Soma
>
>
> On Mon, 26 Aug 2019 at 22:29, Julian Hyde <jhyde@apache.org> wrote:
>
> > I might be mistaken, but disabling stripCastFromString() for some dialects
> > and not others doesn’t sound like it’s solving the root cause of the
> > problem.
> >
> > Julian
> >
> >
> > > On Aug 26, 2019, at 7:49 AM, Soma Mondal <mail.mondal.soma@gmail.com>
> > wrote:
> > >
> > > Hi Julian,
> > >
> > > 2 tests failed when I made the stripCastFromString() no-op.
> > >
> > > 1.
> > >
> > > testDb2DialectSelectQueryWithGroup
> > > 2.
> > >
> > > testSelectQueryWithGroup
> > >
> > > Above tests pretty much do the same thing and basically strip the cast
> > from
> > > String literal something like this:
> > >
> > > Expected:
> > >
> > > SELECT COUNT(*), SUM(employee_id)
> > >
> > > FROM foodmart.reserve_employee
> > >
> > > WHERE hire_date > '2015-01-01' AND (position_title = 'SDE' OR
> > > position_title = 'SDM')
> > >
> > > GROUP BY store_id, position_title
> > >
> > > But with no-op we get this:
> > >
> > > SELECT COUNT(*), SUM(employee_id)
> > >
> > > FROM foodmart.reserve_employee
> > >
> > > WHERE hire_date > CAST('2015-01-01' AS TIMESTAMP(0)) AND (position_title
> > =
> > > 'SDE' OR position_title = 'SDM')
> > >
> > > GROUP BY store_id, position_title
> > >
> > > Can I go ahead and make changes where calls to stripCastFromString() will
> > > be skipped for specific dialects?
> > >
> > > Regards,
> > >
> > > Soma
> > >
> > >
> > > On Fri, 23 Aug 2019 at 16:02, Soma Mondal <mail.mondal.soma@gmail.com>
> > > wrote:
> > >
> > > > Hello,
> > > >
> > > > We have a REL which has this information
> > > > select * from employee where employee_id = cast('12' as float);
> > > >
> > > > but Calcite removes the CAST from the STRING literal('12' in our case).
> > > > select * from employee where employee_id = '12';
> > > >
> > > > There are dialects which needs explicit casting in the above case and
we
> > > > need to maintain the CAST in our dialect.
> > > > Calcite removes the cast in SqlImplementor's stripCastFromString()
> > > > method. I would like to understand why Calcite removes the CAST and
> > shall
> > > > we go ahead and make the changes in Calcite to maintain the CAST.
> > > >
> > > > Thanks & Regards,
> > > > Soma Mondal
> > > >
> >
> >

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message