Skip Menu |
 

This queue is for tickets about the SQL-Statement CPAN distribution.

Report information
The Basics
Id: 80423
Status: open
Priority: 0/
Queue: SQL-Statement

People
Owner: Nobody in particular
Requestors: JHANNAH [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: 1.400_004
Fixed in: (no value)

Attachments
0001-SET_FUNCTION_SPEC-can-t-handle-multiple-complex-func.patch



Subject: SET_FUNCTION_SPEC can't handle multiple, complex functions?
Download (untitled) / with headers
text/plain 1.6k
So when my evil SQL statement looks like this: SELECT ... foo, ROUND ( SUM((ROUND (sf.quantity * DECODE(sf.unit_cost, 0, si.unitcost, sf.unit_cost), 2) )) / SUM (sf.quantity), 2 ) avg_cost, SUM (( ROUND (sf.quantity * DECODE (sf.unit_cost, 0, si.unitcost, sf.unit_cost), 2) )) extended_cost, bar, ... parse() explodes with errors like this: Bad table or column name: ',2' has chars not alphanumeric or underscore! at graphviz.pl line 101. at /Users/jhannah/src/SQL-Statement/lib/SQL/Parser.pm line 2950, <DATA> line 2242. SQL::Parser::do_err('SQL::Parser=HASH(0x7ffcf6b84708)', 'Bad table or column name: \',2\' has chars not alphanumeric o...') called at /Users/jhannah/src/SQL- Statement/lib/SQL/Parser.pm line 2740 SQL::Parser::IDENTIFIER('SQL::Parser=HASH(0x7ffcf6b84708)', ',2') called at /Users/jhannah/src/SQL-Statement/lib/SQL/Parser.pm line 2485 SQL::Parser::COLUMN_NAME('SQL::Parser=HASH(0x7ffcf6b84708)', ',2') called at /Users/jhannah/src/SQL-Statement/lib/SQL/Parser.pm line 2408 SQL::Parser::ROW_VALUE('SQL::Parser=HASH(0x7ffcf6b84708)', ',2') called at /Users/jhannah/src/SQL-Statement/lib/SQL/Parser.pm line 2245 SQL::Parser::ROW_VALUE('SQL::Parser=HASH(0x7ffcf6b84708)', 'SUM ((ROUND (sf.quantity * DECODE (sf.unit_cost,0,si.unitcost...') called at /Users/jhannah/src/SQL- Statement/lib/SQL/Parser.pm line 1438 SQL::Parser::SET_FUNCTION_SPEC('SQL::Parser=HASH(0x7ffcf6b84708)', 'ROUND (SUM ((ROUND (sf.quantity * DECODE (sf.unit_cost,0,si.u...') called at /Users/jhannah/src/SQL- Statement/lib/SQL/Parser.pm line 1381 ... I'll try to create you a patch. Have you guys though about moving to github? :) Thanks! Jay Hannah
Download (untitled) / with headers
text/plain 1.1k
Does this help? j SQL-Statement/lib/SQL$ svn diff Index: Parser.pm =========================================================== ======== --- Parser.pm (revision 15449) +++ Parser.pm (working copy) @@ -1412,8 +1412,23 @@ { my ( $self, $col_str ) = @_; - if ( $col_str =~ m/^(COUNT|AVG|SUM|MAX|MIN) \((.*)\)\s*$/i ) + if ( $col_str =~ /\(.*\(/ ) { + # SET_FUNCTION_SPEC can't handle multiple, complex functions? + # https://rt.cpan.org/Ticket/Display.html?id=80423 + # Instead of dying later on just flag this as 'complex' and don't sweat the details (for now)... + my $value = { + name => 'complex', + arg => 'complex', + argstr => 'complex', + distinct => 'unknown', + type => 'setfunc', + fullorg => $col_str, + }; + return $value; + } + elsif ( $col_str =~ m/^(COUNT|AVG|SUM|MAX|MIN) \((.*)\)\s*$/i ) + { my $set_function_name = uc $1; my $set_function_arg_str = $2; my $distinct = 'ALL';
Download (untitled) / with headers
text/plain 156b
FYI: I ran svn2git on it. If nothing else, I have to have MY trivial little branch in github. :) https://github.com/iinteractive/sql-statement Thanks, j
Download (untitled) / with headers
text/plain 151b
I created a pushed a branch, and merged it into master. https://github.com/iinteractive/sql- statement/commit/f496564f342383d4a1be31f87393fbea8753bb92
Download (untitled) / with headers
text/plain 995b
On Fri Oct 26 18:27:12 2012, JHANNAH wrote: Show quoted text
> I'll try to create you a patch. Have you guys though about moving to > github? :)
Yes, me guys thought about that and discarded the idea - to near to facebook. If - and not when - I'm using git, I store my repos at repo.or.cz Show quoted text
> SQL-Statement/lib/SQL$ svn diff > Index: Parser.pm > =========================================================== > ======== > --- Parser.pm (revision 15449) > +++ Parser.pm (working copy)
... Well - and who handles it? Where is the corresponding functionality in SQL::Statement? Show quoted text
> I created a pushed a branch, and merged it into master. > > https://github.com/iinteractive/sql- > statement/commit/f496564f342383d4a1be31f87393fbea8753bb92
Would be great if you could do a "git format-patch" and attach the patches by uploading files to this ticket. If there is a full solution included - with test cases - I'll add them to next development release (even if I planned to do a full release next). Best regards, Jens
CC: Dan [...] DWright.Org
Subject: Re: [rt.cpan.org #80423] SET_FUNCTION_SPEC can't handle multiple, complex functions?
Date: Tue, 30 Oct 2012 20:22:07 -0500
To: bug-SQL-Statement [...] rt.cpan.org
From: Jay Hannah <jay.hannah [...] iinteractive.com>
Download (untitled) / with headers
text/plain 1.1k
On Oct 27, 2012, at 2:33 AM, Jens Rehsack via RT <bug-SQL-Statement@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=80423 > >
>> SQL-Statement/lib/SQL$ svn diff >> Index: Parser.pm >> =========================================================== >> ======== >> --- Parser.pm (revision 15449) >> +++ Parser.pm (working copy)
> ... > > Well - and who handles it? Where is the corresponding functionality in > SQL::Statement?
Nothing *actually* handles it. I'm dealing with some truly horrific SQL here. Flagging it as 'complex' and not bombing out served my purposes for that day (get a table list). Show quoted text
> Would be great if you could do a "git format-patch" and attach the > patches by uploading files to this ticket.
Below. Show quoted text
> If there is a full solution included - with test cases - I'll add them > to next development release (even if I planned to do a full release next).
I can add tests if you want. I have hundreds of insane Oracle SQL statements that fail without my patch... Do you just want one simple-ish test added? Thanks, Jay Hannah Project Lead / Programmer http://www.iinteractive.com Email: jay.hannah@iinteractive.com AOL IM: deafferret Mobile: 1.402.598.7782 Fax: 1.402.691.9496

Message body is not shown because sender requested not to inline it.

RT-Send-CC: ribasushi [...] cpan.org, Dan [...] DWright.Org
Download (untitled) / with headers
text/plain 1.8k
On Tue Oct 30 21:22:32 2012, jay.hannah@iinteractive.com wrote: Show quoted text
> On Oct 27, 2012, at 2:33 AM, Jens Rehsack via RT <bug-SQL- > Statement@rt.cpan.org> wrote:
> > <URL: https://rt.cpan.org/Ticket/Display.html?id=80423 > > >
> >> SQL-Statement/lib/SQL$ svn diff > >> Index: Parser.pm > >> =========================================================== > >> ======== > >> --- Parser.pm (revision 15449) > >> +++ Parser.pm (working copy)
> > ... > > > > Well - and who handles it? Where is the corresponding functionality
> in
> > SQL::Statement?
> > Nothing *actually* handles it. I'm dealing with some truly horrific > SQL here. Flagging it as 'complex' and not bombing out served my > purposes for that day (get a table list).
Sounds for me as you fix symptom, not reason. Show quoted text
> > Would be great if you could do a "git format-patch" and attach the > > patches by uploading files to this ticket.
> > Below.
Thanks :) Show quoted text
> > If there is a full solution included - with test cases - I'll add
> them
> > to next development release (even if I planned to do a full release
> next). > > > I can add tests if you want. I have hundreds of insane Oracle SQL > statements that fail without my patch... Do you just want one simple- > ish test added?
Well, first of all it needs to be served in any way. It is reasonable that it bombs out when it can't handle the query - and those queries can't be handled at the moment. For the moment, I do not understand how the patch helps you out, 'cause you didn't get the results you asked for. Probably some simple tests would help to understand what you're trying to reach. Anyway - adding parser support for something which isn't executed later sounds strange to me and I like some feedback from more people (even some current DBI/DBD developers, so please go to dbi-dev@ and dbi-users@perl.org and ask there referring to this ticket). Thanks in advance, Jens


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.