While trying to answer this question, I found a syntax which I would have considered wrong
select * from table where area and block
I tried an sqlFiddle, and found it was valid.
But I've been unable to find how it works (all the doc I found was including operators)...
Made some try on an sql fiddle here
It seems to "eliminate" null and 0 results for an nullable int field, or 0 result for a non nullable int field.
It also looks like it keeps varchar values... which contains int values (but not '0') !
Even if I don't think it's a good idea (at all) to use such syntax, I would appreciate if someone had any explanation on how this is managed...
From the MySQL documentation for Logical Operators:
MySQL evaluates any nonzero, non-NULL value to TRUE. For example, the following statements all assess to TRUE:
mysql> SELECT 10 IS TRUE;<br/>
-> 1
mysql> SELECT -10 IS TRUE;<br/>
-> 1
mysql> SELECT 'string' IS NOT NULL;<br/>
-> 1
It seems that it evaluates any non-zero int field as TRUE. Also, any varchar field which contains a number gets treated as numeric field. I checked in your fiddle that a varchar field of '0' does not evaluate to TRUE. And any varchar field which cannot be converted to a number does not evaluate to TRUE unless it is part of an IS NOT NULL expression. Hence:
mysql> SELECT 'string' IS TRUE;<br/>
-> 0
From testing your fiddle, I found the following 2 queries to behave identically:
select * from test where ttt;
select * from test where ttt IS TRUE;
I don't have any definitive proof or source code, but it appears that MySQL actually is using an operator on the first query above even if it be omitted.