Skip Menu |
 

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

Report information
The Basics
Id: 71783
Status: stalled
Priority: 0/
Queue: SQL-Statement

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

Bug Information
Severity: Normal
Broken in: 1.33
Fixed in: (no value)



Subject: BETWEEN clause doesn't logically flow in where_clause
Download (untitled) / with headers
text/plain 1.6k
Hey, two bugs in one day. Sorry about that. SQL::Statement seems to have some funky logic to how it arranges its lower/upper values for BETWEEN clauses. Here's an example: use Data::Dumper; use SQL::Statement; my $sql = " SELECT fg.a, dbi.q FROM asdmib.b fg INNER JOIN dbimib.c dbi ON dbi.q = fg.r WHERE dbi.z BETWEEN 5 AND 10 AND NOT dbi.e IN (6, 7, 250) ORDER BY fg.f DESC LIMIT 5,2"; my $parser = SQL::Parser->new('ANSI'); my $stmt = SQL::Statement->new($sql, $parser); my $d = Data::Dumper->new([ $parser, $stmt, $stmt->command(), $stmt->column_defs(), $stmt->where, $stmt->where->op(), $stmt->limit(), $stmt->offset() ], [qw( *parser *stmt *stmt_command *stmt_column_defs *stmt_where *stmt_where_op *stmt_limit *stmt_offset )]); print $d->Dump(); So, the clause "dbi.z BETWEEN 5 AND 10" ends up looking like this: 'OP' => 'AND', 'LEFT' => bless( { 'OP' => 'BETWEEN', 'LEFT' => bless( { 'VALUE' => 'dbimib.c.z', 'OWNER' => \%stmt }, 'SQL::Statement::ColumnValue' ), 'RIGHT' => bless( { 'VALUE' => '5', 'OWNER' => \%stmt }, 'SQL::Statement::ConstantTerm' ), 'OWNER' => \%stmt }, 'SQL::Statement::Operation::Between' ), 'RIGHT' => bless( { 'OP' => 'AND', 'LEFT' => bless( { 'VALUE' => '10', 'OWNER' => \%stmt }, 'SQL::Statement::ConstantTerm' ), Oookay. So why did the 10 wander over there? According to the docs, BETWEEN should have a RIGHT side array with both values. It seems like it's getting confused with the AND, but it's supposed to be referenced as "BETWEEN X AND Y".
Download (untitled) / with headers
text/plain 2.5k
Show quoted text
> Hey, two bugs in one day. Sorry about that.
No worries about the amount of the bugs, but probably of their content ^^ I strongly recommend talking on dbi-users@ or dbi-dev@ or #dbi on irc.perl.org. Show quoted text
> SQL::Statement seems to have some funky logic to how it arranges its > lower/upper values for BETWEEN clauses. Here's an example: > > use Data::Dumper; > use SQL::Statement; > > my $sql = " > SELECT fg.a, dbi.q > FROM > asdmib.b fg > INNER JOIN dbimib.c dbi ON > dbi.q = fg.r > WHERE > dbi.z BETWEEN 5 AND 10 > AND NOT dbi.e IN (6, 7, 250) > ORDER BY fg.f DESC > LIMIT 5,2";
The where clause is parsed as: WHERE dbi.z BETWEEN 5 AND (10 AND NOT dbi.e IN (6, 7, 250)) which is fairly ok. You probably write it 1) WHERE (dbi.z BETWEEN 5 AND 10) AND NOT dbi.e IN (6, 7, 250) 2) WHERE dbi.e NOT IN (6, 7, 250) AND dbi.z BETWEEN 5 AND 10 - Zitierten Text anzeigen - Show quoted text
> my $parser = SQL::Parser->new('ANSI'); > my $stmt = SQL::Statement->new($sql, $parser); > > my $d = Data::Dumper->new([ > $parser, $stmt, $stmt->command(), $stmt->column_defs(), $stmt->where, > $stmt->where->op(), $stmt->limit(), $stmt->offset() > ], [qw( *parser *stmt *stmt_command *stmt_column_defs *stmt_where > *stmt_where_op *stmt_limit *stmt_offset )]); > print $d->Dump(); > > So, the clause "dbi.z BETWEEN 5 AND 10" ends up looking like this: > > 'OP' => 'AND', > 'LEFT' => bless( { > 'OP' => 'BETWEEN', > 'LEFT' => bless( { > 'VALUE' => 'dbimib.c.z', > 'OWNER' => \%stmt > }, 'SQL::Statement::ColumnValue' ), > 'RIGHT' => bless( { > 'VALUE' => '5', > 'OWNER' => \%stmt > }, 'SQL::Statement::ConstantTerm' ), > 'OWNER' => \%stmt > }, 'SQL::Statement::Operation::Between' ), > 'RIGHT' => bless( { > 'OP' => 'AND', > 'LEFT' => bless( { > 'VALUE' => '10', > 'OWNER' => \%stmt > }, 'SQL::Statement::ConstantTerm' ),
What is exactly what you've asked for. You should use brackets to explain exactly what you mean. When I find a lot of time, I will rewrite the parser and fix such issues to allow more user friendly clauses. But this will require a full rewrite and as long noone pays for this, it must be stay on hold until I have enough free personal time for it. Show quoted text
> Oookay. So why did the 10 wander over there? According to the docs, > BETWEEN should have a RIGHT side array with both values. It seems like > it's getting confused with the AND, but it's supposed to be referenced > as "BETWEEN X AND Y".
SQL::Parser first split unbraced logical operators to create terms from them. /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.