SQL::Abstract inequality warning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL::Abstract inequality warning

Duncan Garland-2
Hi,


Can somebody explain why I've started to get this warning when I run the statement below.

As far as I can see, I'm doing exactly as asked, and the code works so it doesn't evaluate to 1=1.

SQL::Abstract::belch(): [SQL::Abstract::_where_field_op_ARRAYREF] Warning: A multi-element arrayref as an argument to the inequality op '!=' is technically equivalent to an always-true 1=1 (you probably wanted to say ...{ $inequality_op => [ -and => @values ] }... instead)

  my @service_contracts = $c->model('DB::Mbfl2ServiceContract')->search(
    { vehicle_id => $vehicle_id,
      deleted_yn => 'N',
      status     => { '!=' => [ '-and' => [ 'T', 'X', 'E' ] ] }
    },
    { order_by => { '-desc' => 'date_updated' } }
  );

Regards

Duncan


The information contained in this message is for the intended addressee only and may contain confidential and/or privileged information. If you are not the intended addressee, please delete this message and notify the sender; do not copy or distribute this message or disclose its contents to anyone. Any views or opinions expressed in this message are those of the author and do not necessarily represent those of Motortrak Limited or of any of its associated companies. No reliance may be placed on this message without written confirmation from an authorised representative of the company.

Registered in England 3098391 V.A.T. Registered No. 667463890

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Abstract inequality warning

Dagfinn Ilmari Mannsåker
Duncan Garland <[hidden email]> writes:

> Hi,
>
>
> Can somebody explain why I've started to get this warning when I run the
> statement below.
>
> As far as I can see, I'm doing exactly as asked, and the code works so it
> doesn't evaluate to 1=1.

It doesn't evaluate to literally "1=1", but by default array refs are
ORed, so it evaluates to 'foo != x OR foo != y', which is always true
if x != y.

> SQL::Abstract::belch(): [SQL::Abstract::_where_field_op_ARRAYREF] Warning:
> A multi-element arrayref as an argument to the inequality op '!=' is
> technically equivalent to an always-true 1=1 (you probably wanted to say
> ...{ $inequality_op => [ -and => @values ] }... instead)

Note it says @values, not \@values. [ -and => ... ] and [ -or => ... ]
only override the operator used between the rest of the values in that
same array, not recursively into contained arrays.

>   my @service_contracts = $c->model('DB::Mbfl2ServiceContract')->search(
>     { vehicle_id => $vehicle_id,
>       deleted_yn => 'N',
>       status     => { '!=' => [ '-and' => [ 'T', 'X', 'E' ] ] }

If you run this with DBIC_TRACE=1, you'll see this renders as

     status != ? OR status != ? OR status != ?

If you'd had [ -and => [...], [...] ] you would have seen it more clearly

    (status != ? OR status != ?) AND (status != ? OR status != ?)

What you actually want is:

        status     => { '!=' => [ '-and' => 'T', 'X', 'E' ] }

Which renders as

    status != ? AND status != ? AND status != ?

Hope this clarifies things.

--
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Abstract inequality warning

Duncan Garland-2
Thanks

On Fri, Dec 12, 2014 at 5:21 PM, Dagfinn Ilmari Mannsåker <[hidden email]> wrote:
Duncan Garland <[hidden email]> writes:

> Hi,
>
>
> Can somebody explain why I've started to get this warning when I run the
> statement below.
>
> As far as I can see, I'm doing exactly as asked, and the code works so it
> doesn't evaluate to 1=1.

It doesn't evaluate to literally "1=1", but by default array refs are
ORed, so it evaluates to 'foo != x OR foo != y', which is always true
if x != y.

> SQL::Abstract::belch(): [SQL::Abstract::_where_field_op_ARRAYREF] Warning:
> A multi-element arrayref as an argument to the inequality op '!=' is
> technically equivalent to an always-true 1=1 (you probably wanted to say
> ...{ $inequality_op => [ -and => @values ] }... instead)

Note it says @values, not \@values. [ -and => ... ] and [ -or => ... ]
only override the operator used between the rest of the values in that
same array, not recursively into contained arrays.

>   my @service_contracts = $c->model('DB::Mbfl2ServiceContract')->search(
>     { vehicle_id => $vehicle_id,
>       deleted_yn => 'N',
>       status     => { '!=' => [ '-and' => [ 'T', 'X', 'E' ] ] }

If you run this with DBIC_TRACE=1, you'll see this renders as

     status != ? OR status != ? OR status != ?

If you'd had [ -and => [...], [...] ] you would have seen it more clearly

    (status != ? OR status != ?) AND (status != ? OR status != ?)

What you actually want is:

        status     => { '!=' => [ '-and' => 'T', 'X', 'E' ] }

Which renders as

    status != ? AND status != ? AND status != ?

Hope this clarifies things.

--
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

The information contained in this message is for the intended addressee only and may contain confidential and/or privileged information. If you are not the intended addressee, please delete this message and notify the sender; do not copy or distribute this message or disclose its contents to anyone. Any views or opinions expressed in this message are those of the author and do not necessarily represent those of Motortrak Limited or of any of its associated companies. No reliance may be placed on this message without written confirmation from an authorised representative of the company.

Registered in England 3098391 V.A.T. Registered No. 667463890

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...