You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Soma Mondal <ma...@gmail.com> on 2019/08/23 10:32:17 UTC
Preserving CAST of STRING operands in comparison operator
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
Re: Preserving CAST of STRING operands in comparison operator
Posted by 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
> > > > >
> > >
> > >
>
Re: Preserving CAST of STRING operands in comparison operator
Posted by Danny Chan <yu...@gmail.com>.
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 <ma...@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 <ma...@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
> > > >
> >
> >
Re: Preserving CAST of STRING operands in comparison operator
Posted by 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 <ma...@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 <ma...@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
> >>
>
>
Re: Preserving CAST of STRING operands in comparison operator
Posted by Julian Hyde <jh...@apache.org>.
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 <ma...@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 <ma...@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
>>
Re: Preserving CAST of STRING operands in comparison operator
Posted by Soma Mondal <ma...@gmail.com>.
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 <ma...@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
>
Re: Preserving CAST of STRING operands in comparison operator
Posted by Julian Hyde <jh...@apache.org>.
I guess you’re talking about the JDBC adapter, and generating SQL for other dialects.
I don’t recall why we have stripCastFromString() but I know that it was introduced for a good reason. Try making it no-op and see which tests fail.
Julian
> On Aug 23, 2019, at 3:32 AM, Soma Mondal <ma...@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