Bug #131773 for SQL-Statement: Unable to process complex SQL statement with bracket before IN clause

Thu Feb 13 10:19:53 2020 mark_robbins1 [...] hotmail.com - Ticket created

Subject:	Unable to process complex SQL statement with bracket before IN clause
Date:	Thu, 13 Feb 2020 15:19:41 +0000
To:	"bug-SQL-Statement [at] rt.cpan.org." <"bug-SQL-Statement [at] rt.cpan.org." [...] xx1.develooper.com>
From:	Mark R <mark_robbins1 [...] hotmail.com>

Hello, could you please investigate this problem. It looks like the parser can't handle certain types of complex SQL statements. SQL statement: select * from docs where (sourcetable = 'AA' and recid = 11111 ) or ( sourcetable='AA' and sourceid in (select infouid from aa where aaid= '' and class= '' )) or ( sourcetable='BB' and sourceid in (select infouid from bb where bbid= '' and class= '' )) This was adapted from an ORACLE database. the table/column names and values have been substituted for the real values. code sample ==== #### this is an example of the full SQL but a shorter example is included below ###my $sqlStmtStr = "select * from docs where (sourcetable = 'AA' and recid = 11111 ) or ( sourcetable='AA' and sourceid in (select infouid from aa where aaid= '' and class= '' )) or ( sourcetable='BB' and sourceid in (select infouid from bb where bbid= '' and class= '' )) "; my $sqlStmtStr = "select * from docs where ( sourceid in (select infouid from aa where aaid= '' )) "; my $sqlParser = SQL::Parser->new(); $sqlParser->{RaiseError} = 0; $sqlParser->{PrintError} = 1; print STDERR "about to process: $sqlStmtStr\n"; my $sqlStmt = SQL::Statement->new($sqlStmtStr,$sqlParser); Error messages/warnings ===== about to process: select * from docs where ( sourceid in (select infouid from aa where aaid= '' )) Bad table or column name: 'sourceid in select infouid from aa where aaid' has chars not alphanumeric or underscore! at /home/xxxxx/perl5/lib/site_perl/5.26.1/SQL/Statement.pm line 90. Couldn't find predicate! at /home/xxxxx/perl5/lib/site_perl/5.26.1/SQL/Statement.pm line 90. version info ==== Perl version: This is perl 5, version 26, subversion 1 (v5.26.1) built for i686-linux [xxxxx@localhost outputArchive]$ uname -a Linux localhost.localdomain 2.6.32-358.11.1.el6.i686 #1 SMP Wed Jun 12 01:01:27 UTC 2013 i686 i686 i386 GNU/Linux cpan[4]> i SQL::Parser Module id = SQL::Parser CPAN_USERID REHSACK (Jens Rehsack <rehsack@cpan.org>) CPAN_VERSION 1.412 CPAN_FILE R/RE/REHSACK/SQL-Statement-1.412.tar.gz UPLOAD_DATE 2017-04-06 MANPAGE SQL::Parser -- validate and parse SQL strings INST_FILE /home/xxxxx/perl5/lib/site_perl/5.26.1/SQL/Parser.pm INST_VERSION 1.412 cpan[5]> i SQL::Statement Module id = SQL::Statement CPAN_USERID REHSACK (Jens Rehsack <rehsack@cpan.org>) CPAN_VERSION 1.412 CPAN_FILE R/RE/REHSACK/SQL-Statement-1.412.tar.gz UPLOAD_DATE 2017-04-06 MANPAGE SQL::Statement - SQL parsing and processing engine INST_FILE /home/xxxxx/perl5/lib/site_perl/5.26.1/SQL/Statement.pm INST_VERSION 1.412 thanks. mark

Thu Feb 13 11:32:38 2020 REHSACK [...] cpan.org - Correspondence added

Would you please read the documentation first? Thanks!

Thu Feb 13 11:32:39 2020 The RT System itself - Status changed from 'new' to 'open'

Thu Feb 13 11:32:39 2020 REHSACK [...] cpan.org - Status changed from 'open' to 'rejected'

Thu Feb 13 11:41:13 2020 mark_robbins1 [...] hotmail.com - Correspondence added

CC:	"Dan [...] DWright.Org" <Dan [...] DWright.Org>
Subject:	Re: [rt.cpan.org #131773] Unable to process complex SQL statement with bracket before IN clause
Date:	Thu, 13 Feb 2020 16:41:00 +0000
To:	"bug-SQL-Statement [...] rt.cpan.org" <bug-SQL-Statement [...] rt.cpan.org>
From:	Mark R <mark_robbins1 [...] hotmail.com>

Hello, thanks for the prompt reply. could you please highlight which part of the documentation I have misunderstood or missed. I thought I had gone through everything in detail. I have got the code working with other pieces of SQL. I have a second piece of SQL that has a similar problem. if you can point me at the relevant part then I will happily do what I can to use this. I have tried the AnyData and Ansi dialects. thanks, Mark Show quoted text

________________________________ From: Jens Rehsack via RT <bug-SQL-Statement@rt.cpan.org> Sent: 13 February 2020 16:32 To: mark_robbins1@hotmail.com <mark_robbins1@hotmail.com> Cc: Dan@DWright.Org <Dan@DWright.Org> Subject: [rt.cpan.org #131773] Unable to process complex SQL statement with bracket before IN clause <URL: https://rt.cpan.org/Ticket/Display.html?id=131773 > Would you please read the documentation first? Thanks!

Thu Feb 13 11:44:29 2020 REHSACK [...] cpan.org - Correspondence added

Sure: https://metacpan.org/pod/SQL::Statement#BUGS-AND-LIMITATIONS 5th item from top. I agree, the reported error is misleading, but when sub-selects were detected correctly by the broken parser, executing them will be easy :)

Thu Feb 13 11:56:00 2020 mark_robbins1 [...] hotmail.com - Correspondence added

CC:	"Dan [...] DWright.Org" <Dan [...] DWright.Org>
Subject:	Re: [rt.cpan.org #131773] Unable to process complex SQL statement with bracket before IN clause
Date:	Thu, 13 Feb 2020 16:55:52 +0000
To:	"bug-SQL-Statement [...] rt.cpan.org" <bug-SQL-Statement [...] rt.cpan.org>
From:	Mark R <mark_robbins1 [...] hotmail.com>

Hi Jens, thanks for the reply. I was caught out by the error message and I didn't appreciate that the sub select was actually the problem. I saw there were references to developing version 2 which relied on a BNF structure. I'm guessing from the lack of releases that this is still on the back burner. Are there any plans to bring this work forward? Do you have an estimate for how long it would take to write/complete it? I know that working with BNF isn't a quick task - I studied it's use for programming languages quite a long time ago. I ask because I may have some funding available. If I can't get this working then I would have to use a commercial solution and the current commercial option is not implemented in Perl and it is potentally very expensive. I'm happy to talk privately. best regards, Mark Show quoted text

________________________________ From: Jens Rehsack via RT <bug-SQL-Statement@rt.cpan.org> Sent: 13 February 2020 16:44 To: mark_robbins1@hotmail.com <mark_robbins1@hotmail.com> Cc: Dan@DWright.Org <Dan@DWright.Org> Subject: [rt.cpan.org #131773] Unable to process complex SQL statement with bracket before IN clause <URL: https://rt.cpan.org/Ticket/Display.html?id=131773 > Sure: https://metacpan.org/pod/SQL::Statement#BUGS-AND-LIMITATIONS 5th item from top. I agree, the reported error is misleading, but when sub-selects were detected correctly by the broken parser, executing them will be easy :)

Thu Feb 13 12:02:47 2020 REHSACK [...] cpan.org - Correspondence added

On Thu Feb 13 11:56:00 2020, mark_robbins1@hotmail.com wrote: Show quoted text

> Hi Jens, > > thanks for the reply. I was caught out by the error message and I > didn't appreciate that the sub select was actually the problem.

Honestly - I don't know whether it's really the problem but it smells heavily for it :) Show quoted text

> I saw there were references to developing version 2 which relied on a > BNF structure.

But - even if - not for Perl5. The future belongs to Raku. If you would investigate and provide patches (with tests!), I try to find tuit to review and merge. Show quoted text

> I'm guessing from the lack of releases that this is still on the back > burner. > > Are there any plans to bring this work forward? > Do you have an estimate for how long it would take to write/complete > it?

It's not a tiny thing. And there is no project visible which could fund such a development. As a quick workaround, I suggest using temporary tables. Show quoted text

> I know that working with BNF isn't a quick task - I studied it's use > for programming languages quite a long time ago. > > I ask because I may have some funding available. If I can't get this > working then I would have to use a commercial solution and the current > commercial option is not implemented in Perl and it is potentally very > expensive.

I suggest ask on dbi-users@ when a temporary table isn't satisfying. Best regards, Jens

Thu Feb 13 12:24:17 2020 mark_robbins1 [...] hotmail.com - Correspondence added

CC:	"Dan [...] DWright.Org" <Dan [...] DWright.Org>
Subject:	Re: [rt.cpan.org #131773] Unable to process complex SQL statement with bracket before IN clause
Date:	Thu, 13 Feb 2020 17:24:09 +0000
To:	"bug-SQL-Statement [...] rt.cpan.org" <bug-SQL-Statement [...] rt.cpan.org>
From:	Mark R <mark_robbins1 [...] hotmail.com>

Hi Jens, Thanks for taking the time to have the conversation. I work for a commercial organisation. we might have had money available to fund the development if it was closer to completion. I wasn't able to use my work email because my competitors google for references to my company. Thanks for the suggestion about the temporary tables. I can't do that because I am analysing code generated from a 3rd party system. My aim was to use the code to check the SQL statement and identify the where clauses/tables. I could then use it to check the available indexes etc. best regards, Mark

Thu Feb 13 12:31:38 2020 REHSACK [...] cpan.org - Correspondence added

On Thu Feb 13 12:24:17 2020, mark_robbins1@hotmail.com wrote: Show quoted text

> Hi Jens, > > Thanks for taking the time to have the conversation. > I work for a commercial organisation. we might have had money > available to fund the development if it was closer to completion.

From time to time I have contracts where such a further development is possible. It always requires a business case. My current contract allows me to dig a bit deeper into Raku, so maybe time will come ;) Show quoted text

> I wasn't able to use my work email because my competitors google for > references to my company.

No worries ;) Show quoted text

> Thanks for the suggestion about the temporary tables. I can't do that > because I am analysing code generated from a 3rd party system.

You could try to s,(\(select[^)]*),subs1, and put $1 into a prepended statement doing "create temp table subs1 as $1". Show quoted text

> My aim was to use the code to check the SQL statement and identify the > where clauses/tables. I could then use it to check the available > indexes etc. >

You just have to "extract" the sub-selects first. Best regards, Jens