You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Andrew O <ao...@gmail.com> on 2019/06/06 11:06:49 UTC
Parsing DB2 sql statements
I'm doing a project trying to parse some IBM DB2 sql expressions (to
analyze table / column usages).
To note,
1) these are existing ad-hoc user queries so I can't change their syntax
2) I don't have a connection / schema for the database, but my
understanding is that this shouldn't be required by Calcite for this
kind of work.
Currently I'm hitting some parsing issues with queries like
select Date(x.col1), x.col2 from myTable x
It fails parse at the point of the Date function call. This is trying to
use the function to convert the value of col1 to a date (almost similar to
a cast/convert).
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
My current thinking would be that this may need changes to the parser
grammar (through an extension?). Or perhaps this could be registered as a
custom User Defined Function of some kind (although Date seems like a
reserved word)?
Is this the right thinking, or is there another approach I could look at?
Thanks in advance
Andrew
Re: Parsing DB2 sql statements
Posted by Yuzhao Chen <yu...@gmail.com>.
Another choice is just like you said, you can add Date as UDF but because
Date is a reserved word, you may need to quote the function name like
`Date`(col1) based on what quoting character you use.
Andrew O <ao...@gmail.com> 于2019年6月6日周四 下午7:07写道：
> I'm doing a project trying to parse some IBM DB2 sql expressions (to
> analyze table / column usages).
>
> To note,
> 1) these are existing ad-hoc user queries so I can't change their syntax
> 2) I don't have a connection / schema for the database, but my
> understanding is that this shouldn't be required by Calcite for this
> kind of work.
>
> Currently I'm hitting some parsing issues with queries like
>
> select Date(x.col1), x.col2 from myTable x
>
> It fails parse at the point of the Date function call. This is trying to
> use the function to convert the value of col1 to a date (almost similar to
> a cast/convert).
>
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
>
>
> My current thinking would be that this may need changes to the parser
> grammar (through an extension?). Or perhaps this could be registered as a
> custom User Defined Function of some kind (although Date seems like a
> reserved word)?
>
> Is this the right thinking, or is there another approach I could look at?
>
> Thanks in advance
>
> Andrew
>
Re: Parsing DB2 sql statements
Posted by Andrew O <ao...@gmail.com>.
Thanks & will do.
Having tested the trunk /pending release changes, they resolved the issue
we had been seeing.
Andrew
On Fri, 7 Jun 2019, 20:53 Julian Hyde, <jh...@apache.org> wrote:
> If you encounter further issues, please log them. We can’t fix them if we
> don’t know what they are. (We won’t necessarily fix them… we’re busy too…
> but they might be easy to fix while we’re fixing something else.)
>
> With Babel, due to its very large scope, there are a lot of unknown issues.
>
> Julian
>
>
>
> > On Jun 6, 2019, at 5:42 AM, Andrew O <ao...@gmail.com> wrote:
> >
> > Thanks for the replies.
> >
> > @Yuzhao unfortunately I won't be able to change the input sql to add the
> > quotes, so as you point out the UDF may have issues with the reserved
> > word.
> >
> > @Hongze, Yes, I'm only interested in parsing. I had tried the Babel
> parser
> > without success (on v1.19.0). However that's great / fortunate that
> there
> > have been recent changes to support additional syntax. Thanks for the
> > JIRA ticket reference. The new test cases seem to show similar sql now
> > being supported. So I will give that a try.
> >
> > Andrew
> >
> > On Thu, 6 Jun 2019, 13:19 Hongze Zhang, <no...@126.com> wrote:
> >
> >> Hi Andrew,
> >>
> >> If you are focusing parsing only, I believe you can just use the babel
> >> parser - in babel we have added support for DATE function via
> CALCITE-3022.
> >> The thing is you need to compile Calcite's code by yourself - 1.20.0
> hasn't
> >> been released yet.
> >>
> >> Hongze
> >>
> >>> On Jun 6, 2019, at 19:06, Andrew O <ao...@gmail.com> wrote:
> >>>
> >>> I'm doing a project trying to parse some IBM DB2 sql expressions (to
> >>> analyze table / column usages).
> >>>
> >>> To note,
> >>> 1) these are existing ad-hoc user queries so I can't change their
> syntax
> >>> 2) I don't have a connection / schema for the database, but my
> >>> understanding is that this shouldn't be required by Calcite for this
> >>> kind of work.
> >>>
> >>> Currently I'm hitting some parsing issues with queries like
> >>>
> >>> select Date(x.col1), x.col2 from myTable x
> >>>
> >>> It fails parse at the point of the Date function call. This is trying
> to
> >>> use the function to convert the value of col1 to a date (almost similar
> >> to
> >>> a cast/convert).
> >>>
> >>>
> >>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
> >>>
> >>>
> >>> My current thinking would be that this may need changes to the parser
> >>> grammar (through an extension?). Or perhaps this could be registered
> as a
> >>> custom User Defined Function of some kind (although Date seems like a
> >>> reserved word)?
> >>>
> >>> Is this the right thinking, or is there another approach I could look
> >> at?
> >>>
> >>> Thanks in advance
> >>>
> >>> Andrew
> >>
> >>
>
>
Re: Parsing DB2 sql statements
Posted by Julian Hyde <jh...@apache.org>.
If you encounter further issues, please log them. We can’t fix them if we don’t know what they are. (We won’t necessarily fix them… we’re busy too… but they might be easy to fix while we’re fixing something else.)
With Babel, due to its very large scope, there are a lot of unknown issues.
Julian
> On Jun 6, 2019, at 5:42 AM, Andrew O <ao...@gmail.com> wrote:
>
> Thanks for the replies.
>
> @Yuzhao unfortunately I won't be able to change the input sql to add the
> quotes, so as you point out the UDF may have issues with the reserved
> word.
>
> @Hongze, Yes, I'm only interested in parsing. I had tried the Babel parser
> without success (on v1.19.0). However that's great / fortunate that there
> have been recent changes to support additional syntax. Thanks for the
> JIRA ticket reference. The new test cases seem to show similar sql now
> being supported. So I will give that a try.
>
> Andrew
>
> On Thu, 6 Jun 2019, 13:19 Hongze Zhang, <no...@126.com> wrote:
>
>> Hi Andrew,
>>
>> If you are focusing parsing only, I believe you can just use the babel
>> parser - in babel we have added support for DATE function via CALCITE-3022.
>> The thing is you need to compile Calcite's code by yourself - 1.20.0 hasn't
>> been released yet.
>>
>> Hongze
>>
>>> On Jun 6, 2019, at 19:06, Andrew O <ao...@gmail.com> wrote:
>>>
>>> I'm doing a project trying to parse some IBM DB2 sql expressions (to
>>> analyze table / column usages).
>>>
>>> To note,
>>> 1) these are existing ad-hoc user queries so I can't change their syntax
>>> 2) I don't have a connection / schema for the database, but my
>>> understanding is that this shouldn't be required by Calcite for this
>>> kind of work.
>>>
>>> Currently I'm hitting some parsing issues with queries like
>>>
>>> select Date(x.col1), x.col2 from myTable x
>>>
>>> It fails parse at the point of the Date function call. This is trying to
>>> use the function to convert the value of col1 to a date (almost similar
>> to
>>> a cast/convert).
>>>
>>>
>> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
>>>
>>>
>>> My current thinking would be that this may need changes to the parser
>>> grammar (through an extension?). Or perhaps this could be registered as a
>>> custom User Defined Function of some kind (although Date seems like a
>>> reserved word)?
>>>
>>> Is this the right thinking, or is there another approach I could look
>> at?
>>>
>>> Thanks in advance
>>>
>>> Andrew
>>
>>
Re: Parsing DB2 sql statements
Posted by Andrew O <ao...@gmail.com>.
Thanks for the replies.
@Yuzhao unfortunately I won't be able to change the input sql to add the
quotes, so as you point out the UDF may have issues with the reserved
word.
@Hongze, Yes, I'm only interested in parsing. I had tried the Babel parser
without success (on v1.19.0). However that's great / fortunate that there
have been recent changes to support additional syntax. Thanks for the
JIRA ticket reference. The new test cases seem to show similar sql now
being supported. So I will give that a try.
Andrew
On Thu, 6 Jun 2019, 13:19 Hongze Zhang, <no...@126.com> wrote:
> Hi Andrew,
>
> If you are focusing parsing only, I believe you can just use the babel
> parser - in babel we have added support for DATE function via CALCITE-3022.
> The thing is you need to compile Calcite's code by yourself - 1.20.0 hasn't
> been released yet.
>
> Hongze
>
> > On Jun 6, 2019, at 19:06, Andrew O <ao...@gmail.com> wrote:
> >
> > I'm doing a project trying to parse some IBM DB2 sql expressions (to
> > analyze table / column usages).
> >
> > To note,
> > 1) these are existing ad-hoc user queries so I can't change their syntax
> > 2) I don't have a connection / schema for the database, but my
> > understanding is that this shouldn't be required by Calcite for this
> > kind of work.
> >
> > Currently I'm hitting some parsing issues with queries like
> >
> > select Date(x.col1), x.col2 from myTable x
> >
> > It fails parse at the point of the Date function call. This is trying to
> > use the function to convert the value of col1 to a date (almost similar
> to
> > a cast/convert).
> >
> >
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
> >
> >
> > My current thinking would be that this may need changes to the parser
> > grammar (through an extension?). Or perhaps this could be registered as a
> > custom User Defined Function of some kind (although Date seems like a
> > reserved word)?
> >
> > Is this the right thinking, or is there another approach I could look
> at?
> >
> > Thanks in advance
> >
> > Andrew
>
>
Re: Parsing DB2 sql statements
Posted by Hongze Zhang <no...@126.com>.
Hi Andrew,
If you are focusing parsing only, I believe you can just use the babel parser - in babel we have added support for DATE function via CALCITE-3022. The thing is you need to compile Calcite's code by yourself - 1.20.0 hasn't been released yet.
Hongze
> On Jun 6, 2019, at 19:06, Andrew O <ao...@gmail.com> wrote:
>
> I'm doing a project trying to parse some IBM DB2 sql expressions (to
> analyze table / column usages).
>
> To note,
> 1) these are existing ad-hoc user queries so I can't change their syntax
> 2) I don't have a connection / schema for the database, but my
> understanding is that this shouldn't be required by Calcite for this
> kind of work.
>
> Currently I'm hitting some parsing issues with queries like
>
> select Date(x.col1), x.col2 from myTable x
>
> It fails parse at the point of the Date function call. This is trying to
> use the function to convert the value of col1 to a date (almost similar to
> a cast/convert).
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
>
>
> My current thinking would be that this may need changes to the parser
> grammar (through an extension?). Or perhaps this could be registered as a
> custom User Defined Function of some kind (although Date seems like a
> reserved word)?
>
> Is this the right thinking, or is there another approach I could look at?
>
> Thanks in advance
>
> Andrew