drizzle-discuss team mailing list archive
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:
<nonparenthesized value expression primary>
<boolean value expression>
It's not all about parsing though, a semantic rule has to be applied too:
"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
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
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.
> Thank you,
> 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
Author of "Pentaho Solutions: Business Intelligence and Data
Warehousing with Pentaho and MySQL",