String literals in a truth test such as you set up are coerced to integers.
(A la C's 'atoi()' function.)
The following returns all rows from a table:
SELECT * FROM test WHERE '1'; // String becomes 1
SELECT * FROM test WHERE '1aaaaa'; // String becomes 1
SELECT * FROM test WHERE '-1'; // String becomes -1
...
The following returns none:
SELECT * FROM test WHERE '0'; // String becomes 0
SELECT * FROM test WHERE '0aaaaa'; // String becomes 0
SELECT * FROM test WHERE 'a'; // String becomes 0
SELECT * FROM test WHERE 'a1'; // String becomes 0
...
If you're used to weakly-typed languages, such as Perl this is fairly
typical behavior although it does seem counterintuitive in the context of
SQL.
-JF
> -----Original Message-----
> From: Svensson, B.A.T. (HKG) [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, April 16, 2002 10:22 AM
> To: Christopher Thompson; Mysql List
> Subject: RE: WHERE codition test
>
>
> Basically I am just curious, and I did perform some few small test.
> But, I was suspecting to get all rows when I did something like
> "WHERE <valid string>", but had to write something like "WHERE NOT
> <valid string>" which feels a little bit upside down for me considering
> that integers apart from zero evaluates to true.
>
> Also it seams like this is not ISO SQL, so it could be a bit tricky
> to predict the outcome without actually having some specification of
> the behavior.
>
> //Anders
>
> > -----Original Message-----
> > From: Christopher Thompson
> > Sent: Tuesday, April 16, 2002 6:24 PM
> > To: Svensson, B.A.T. (HKG); Mysql List
> > Subject: Re: WHERE codition test
> >
> >
> > I'm not sure what you are asking. Everything is working
> correctly as far as
> > I can see.
> >
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
