[Dbix-class] Incorrect SQL being generated after DBIC library upgrade

[demerphq]

2009/3/19 Peter Rabbitson <rabbit+dbic at rabbit.us>:
[...]
>
> Reading your example again I noticed you are actually getting what you asked for.
> Consider (if we assume -and in a hashref is OK)

FWIW I agree with the OP.  And the docs state that -and in a hashref
is legal as far as i recall.

> 1) You say -and as in AND all the contents of the following arrayref
> 2) Then as first element of the array you say - OR the elements following the -or
>   modifier
> 3) The first element after OR is another arrayref - you get the
>   ( module_access.expires > ? OR scheme_access.expires > ? ) chunk
> 4) Then due to the -or keyword (2) you get ... OR me.person = ?

It doesnt work like that, at least not in my experience.

The '-and => [ ... ]' is self contained, the "-and" says join together
the next thing using "AND" (and not whatever the default join operand
is for that type), not "join together everything following".

> 5) Leaving the scope of the arrayref there is nothing to -and (1)
>
> Again someone who knows SQLA internals better than me should comment, although
> what 1.50 does seems very logical.

I dont think 1.50 can change this.  I would consider this a clear regression.

See the attached program and in particular the last test/query
structure for why.

cheers,
Yves

use SQL::Abstract;
use Data::Dumper;

print "Version: $SQL::Abstract::VERSION\n";
my $sqla = SQL::Abstract->new;

my $id=1234;
my $time="2009-01-01";
sub test {
    for my $query (@_) {
        my ($sql, at args)= $sqla->where($query);
        print Data::Dumper->Dump( [ $sql, \@args ],[ qw( sql *args ) ] );
        print "####\n";
    }
};
test
{
     -and => [
                 -or => [
                  "module_access.expires" => { ">", $time },
                  "scheme_access.expires" => { ">", $time },
               ],
               "me.person" => $id,
           ],
},
[
     -and => {
                 -or => {
                  "module_access.expires" => { ">", $time },
                  "scheme_access.expires" => { ">", $time },
               },
               "me.person" => $id,
           },
],
[
     -and => [
                 -or => {
                  "module_access.expires" => { ">", $time },
                  "scheme_access.expires" => { ">", $time },
               },
               "me.person" => $id,
               -or => [
                    favourite_food => "chicken",
                    hates_food => "spinach",
                ],
                color => "mauve",
                -or => [
                    -and => [ "guiness" => "black", "guiness" => "white" ],
                   "foo" => { "in", [ 1..10 ] },
                ],
           ],
];

__END__
Version: 1.24
$sql = ' WHERE ( ( ( ( ( module_access.expires > ? ) OR (
scheme_access.expires > ? ) ) ) AND ( me.person = ? ) ) )';
@args = (
          '2009-01-01',
          '2009-01-01',
          1234
        );
####
$sql = ' WHERE ( ( ( ( module_access.expires > ? OR
scheme_access.expires > ? ) AND me.person = ? ) ) )';
@args = (
          '2009-01-01',
          '2009-01-01',
          1234
        );
####
$sql = ' WHERE ( ( ( ( ( module_access.expires > ? OR
scheme_access.expires > ? ) ) AND ( me.person = ? ) AND ( ( (
favourite_food = ? ) OR ( hates_food = ? ) ) ) AND ( color = ? ) AND (
( ( ( ( guiness = ? ) AND ( guiness = ? ) ) ) OR ( foo IN ( ?, ?, ?,
?, ?, ?, ?, ?, ?, ? ) ) ) ) ) ) )';
@args = (
          '2009-01-01',
          '2009-01-01',
          1234,
          'chicken',
          'spinach',
          'mauve',
          'black',
          'white',
          1,
          2,
          3,
          4,
          5,
          6,
          7,
          8,
          9,
          10
        );
####





-- 
perl -Mre=debug -e "/just|another|perl|hacker/"