Bug #71332 Manual does NOT explain how row constructors work in non-equal comparisons
Submitted: 9 Jan 2014 15:55 Modified: 6 Apr 2015 17:49
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: missing manual, row constructor

[9 Jan 2014 15:55] Valeriy Kravchuk
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
[10 Jan 2014 8:45] Umesh Shastry
Hello Valeriy,

Thank you for the bug report.

Thanks,
Umesh
[6 Apr 2015 17:49] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Instead of updating
http://dev.mysql.com/doc/en/row-subqueries.html
I updated the operator descriptions at
http://dev.mysql.com/doc/en/comparison-operators.html
and pointed the row-subqueries page there.

See also Bug#76448.