Re: Preserving CAST of STRING operands in comparison operator

[Soma Mondal <ma...@gmail.com>]

Hi,

I have created a JIRA regarding this issue, URL:
https://issues.apache.org/jira/browse/CALCITE-3318, and also made a PR
https://github.com/apache/calcite/pull/1437.
Please go through it.

Regards,
Soma

On Fri, 30 Aug 2019 at 07:19, Danny Chan <yu...@gmail.com> wrote:

> 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 <ma...@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 <jh...@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
> > > > >
> > >
> > >
>