← Back to team overview

drizzle-discuss team mailing list archive

Re: Where clause with non-Boolean

 

Hi Jobin, all,

On Sun, Dec 6, 2009 at 3:47 PM, Jobin Augustine <jobinau@xxxxxxxxx> wrote:
> Hi All,
> during the testing, found that Where clause is accepting non-booleans.
> for example.
>
> select * from tablename where 1;
> select * from tablename where columnname;
>
> in other planets it is illegal. and citizens uses loopholes like:

FYI, the last example could be valid in standard (2003) SQL. Here's
the parse that would allow it:

<column reference>
 <nonparenthesized value expression primary>
   <boolean predicand>
     <boolean primary>
       <boolean test>
         <boolean factor>
           <boolean term>
             <boolean value expression>
               <search condition>

It's not all about parsing though, a semantic rule has to be applied too:

6.34

"1) The declared type of a <nonparenthesized value expression primary>
shall be boolean."

This rule applies to <boolean value expression> - it means like: "a
<nonparenthesized value expression primary> is a <boolean value
expression> if it's declared type is boolean"

So at least, when the declared type of <column> is boolean it would be
allowed in standard SQL.
Now in MySQL (and drizzle too AFAIK) , you boolean does not exist as a
type you can use in declaration.  Question is, should <column
reference> be automatically converted to a runtine boolean expression
in case it appears in a context that calls for a boolean expression
(such as in your 2nd example) or should one say, "no, since I cannot
declare a column to be of the boolean type, a column cannot be a
boolean type, and hence this is not valid syntax" (one can aks the
same things about the 1st syntax example.

Now in MySQL (and I think currenly in drizzle too) boolean expressions
are emulated with integers, where 0 is false, en everything else is
true.
So, in MySQL, if <column> is an integer type, it will work.

Basically, what happens, when MySQL sees this expression, it says, ah,
this must be a boolean, so let's cast it to an integer, and if the
casting doesn't work, it will be a 0 (false)

You can see this in action if you do a statement like this:

mysql> select 1 from information_schema.tables where 'a';
Empty set, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

This freaks many people out: instead of throwing an error, it just
says, well I couldn't parse it as an integer, so we pretend its 0.
You can see that behaviour in a query like this:

mysql> select 1 + 'a';
+---------+
| 1 + 'a' |
+---------+
|       1 |
+---------+
1 row in set, 1 warning (0.00 sec)

So, the 'a' amounts 0 here.

> create table x as select * from y where 1=2;
> to get a empty table x with same structure as y.

This syntax is AFAICS completely valid, regardless of the original
problem posed.

1 = 2

is by all means a valid comparison, and the result is a boolean value
expression; therefore, it should be valid as WHERE search condition.
So I don't think this particular example is related to the first few examples.

> Don't know it is MySQL ism or expected behaviour.
>
> i noticed it because a typo like this :
> delete from emp where id-2;
> cleaned up my table. :(

sorry to hear that.

kind regards,

Roland

>
> Thank you,
> Jobin.
>
> _______________________________________________
> Mailing list: https://launchpad.net/~drizzle-discuss
> Post to     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~drizzle-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>



-- 
Roland Bouman
http://rpbouman.blogspot.com/

Author of "Pentaho Solutions: Business Intelligence and Data
Warehousing with Pentaho and MySQL",
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html



Follow ups

References