| Subject: | BETWEEN clause doesn't logically flow in where_clause |
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".