Description:
Please, consider this case:
mysql> create table tt(c1 int, c2 int);
Query OK, 0 rows affected (0.62 sec)
mysql> insert into tt values (0,1), (1,0);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
Some users expect that this query:
mysql> select * from tt where c1>=0 and c2>=1;
+------+------+
| c1 | c2 |
+------+------+
| 0 | 1 |
+------+------+
1 row in set (0.00 sec)
is equivalent to the following:
mysql> select * from tt where (c1,c2)>=(0,1);
+------+------+
| c1 | c2 |
+------+------+
| 0 | 1 |
| 1 | 0 |
+------+------+
2 rows in set (0.02 sec)
while results are actually different.
(Check comment from Sinisa dated "[24 Oct 2013 15:57] Sinisa Milivojevic" in Bug #70705 to see that even best of the best may assume something similar)
The query above is actually equivalent to the following:
mysql> select * from tt where (c1>=0) or (c1=0 and c2>=1);
+------+------+
| c1 | c2 |
+------+------+
| 0 | 1 |
| 1 | 0 |
+------+------+
2 rows in set (0.00 sec)
It would be nice to see this clearly documented when row constructors are discussed, at http://dev.mysql.com/doc/refman/5.5/en/row-subqueries.html for example. It says only this:
"For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;"
How to repeat:
Try to find some place in the manual that explains this difference in results:
mysql> create table tt(c1 int, c2 int);
Query OK, 0 rows affected (0.62 sec)
mysql> insert into tt values (0,1), (1,0);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tt where c1>=0 and c2>=1;
+------+------+
| c1 | c2 |
+------+------+
| 0 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tt where (c1,c2)>=(0,1);
+------+------+
| c1 | c2 |
+------+------+
| 0 | 1 |
| 1 | 0 |
+------+------+
2 rows in set (0.02 sec)
Suggested fix:
Add explanation (or link to some good page explaining this in general, not for MySQL context) to http://dev.mysql.com/doc/refman/5.5/en/row-subqueries.html
