MySQL Bugs: #8793: Boolean IS TRUE OR FALSE returns incorrect result

Bug #8793	Boolean IS TRUE OR FALSE returns incorrect result
Submitted:	24 Feb 2005 21:15	Modified:	25 Feb 2005 21:49
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
A search for IS TRUE returns the correct result,
a search for IS FALSE returns the correct result,
even a search for IS FALSE OR TRUE returns the
correct result. But a search for IS TRUE OR FALSE
does not return the correct result.

How to repeat:
mysql> create table t1 (col1 boolean);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1),(0);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1 where col1 is true;
+------+
| col1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
-- Correct.

mysql> select * from t1 where col1 is false;
+------+
| col1 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
-- Correct.

mysql> select * from t1 where col1 is true or false;
+------+
| col1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
-- Incorrect. The result should be both 1 and 0.

mysql> select * from t1 where col1 is false or true;
+------+
| col1 |
+------+
|    1 |
|    0 |
+------+
2 rows in set (0.00 sec)
-- Turn the conditions around and the result is, again,
correct.

Hello Trudy,

are you sure it is a bug? I think you should use this query to obtain right result:
- select * from t1 where col1 is true or false;
+ select * from t1 where col1 is true or col1 is false;

I expect "col1 is true or false" equals to "(col1 is true) or false".

Hi Sergey,
Good point. It's true that the "correct" way to get the result is:
select * from t1 where col1 is true or col1 is false;

But I worry about the inconsistency shown when a different 
result is returned just because the operands in a WHERE 
clause are reversed. Even with parentheses, the result is
 inconsistent:
mysql> select * from t1 where (col1 is true) or false;
+------+
| col1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t1 where (col1 is false) or true;
+------+
| col1 |
+------+
|    1 |
|    0 |
+------+
2 rows in set (0.00 sec)

I think a good solution would be to return a syntax error for 
the "incorrect" construction. For example, if I try to select the 
values directly from the table:
mysql> select * from t1 where col1 is 1 or 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 or 0' at line 1
-- I get an error that says the query is badly formed. Returning 
the same error for "where col1 is true or false" would solve the 
problem.

With parentheses results are completely consistent - it's easier to see by moving the test to the SELECT clause:

mysql> select col1, col1 is true, (col1 is true) or false from t1;
+------+--------------+-------------------------+
| col1 | col1 is true | (col1 is true) or false |
+------+--------------+-------------------------+
|    1 |            1 |                       1 |
|    0 |            0 |                       0 |
+------+--------------+-------------------------+
2 rows in set (0.00 sec)

mysql> select col1, col1 is false, (col1 is false) or true from t1;
+------+---------------+-------------------------+
| col1 | col1 is false | (col1 is false) or true |
+------+---------------+-------------------------+
|    1 |             0 |                       1 |
|    0 |             1 |                       1 |
+------+---------------+-------------------------+
2 rows in set (0.01 sec)

As you see, result are correct, according to the definition of OR:

x OR true  == true
x OR false == x

An example with 'is 1 or 0' is not relevant here - it fails for a different reason:

mysql> select col1 from t1 where col1 is 1;                        
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

Because, according to the sql:2003 (6.34 <boolean value expression>) the syntax is

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]
<truth value> ::=
    TRUE
  | FALSE
  | UNKNOWN

so IS 1 is not allowed