← Back to team overview

drizzle-discuss team mailing list archive

Re: Where clause with non-Boolean

 

Thank you very much Roland.
You done a very detailed analysis of the problem.

Agreeing to your observations / analysis.
So a statement like : select * from tablename where 1;
is completely invalid.

A statement like : select * from tablename where columnname;
is valid provided "columnname" is of Boolean datatype.
as you said, since we don't have a such a datatype, it is invalid again.

and like in another planets a Boolean column can be compared very explicitly
like   : WHERE COLUMNNAME = TRUE;

so i think team identifies it a s problem case, such things probably can be
trapped in parser.

Yes!. after going though your mail, now i understands that internally it is
doing automatic type casting.
which is doing the damage.
That is the whole reason why SQL is behaving like C : )
a real "Ceequel" : )

since i am coming from different planet, just wondering..
why illegal / not working cases are given just a warning and allowed to
proceed?

Thank you once again.
Jobin.

On Sun, Dec 6, 2009 at 9:47 PM, Roland Bouman <roland.bouman@xxxxxxxxx>wrote:

> 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<https://launchpad.net/%7Edrizzle-discuss>
> > Post to     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
> > Unsubscribe : https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-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