Bug #76448 Ambiguous documentation of comparison operations on row subqueries
Submitted: 23 Mar 2015 9:57 Modified: 31 Mar 2015 13:57
Reporter: Alan Egerton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:All OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[23 Mar 2015 9:57] Alan Egerton 
Description:
The documentation on row subqueries[1] states:

> Legal operators for row subquery comparisons are:
> 
>     =  >  <  >=  <=  <>  !=  <=>

But thereafter the manual only discusses how the *equality operator* applies to row subqueries.

It is not *explicit* how row comparisons are ordered, and some people[2] have misunderstood that (a,b) >= (1,2) should expand to (a>=1 AND b>=2)—whereas it does of course expand to ((a=1 AND b>=2) OR a>1).

[1]: http://dev.mysql.com/doc/en/row-subqueries.html
[2]: http://stackoverflow.com/q/29206527

How to repeat:
mysql> CREATE TABLE foo (a INT, b INT);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO foo VALUES (1,2),(1,3),(2,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM foo WHERE (a,b) >= (1,2);
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    2 |    1 |
+------+------+
3 rows in set (0.00 sec)

Suggested fix:
Explicitly document how row subqueries are ordered for the purposes of comparisons with inequality operators.
[23 Mar 2015 16:46] MySQL Verification Team 
I have inspected the manual and I have verified that many of the row expressions are not documented properly. Hence, I am verifying this bug !!!!
[26 Mar 2015 13:14] 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.
[26 Mar 2015 16:46] Alan Egerton 
Thanks Paul.

Unfortunately, I think you have documented < and > incorrectly and that they should, respectively, instead be:

(a < x) || ((a = x) && (b < y))
(a < x) || ((a = x) && (b > y))
[26 Mar 2015 16:47] Alan Egerton 
Sorry, I mean:

(a < x) || ((a = x) && (b < y))
(a > x) || ((a = x) && (b > y))
[27 Mar 2015 12:52] Alan Egerton 
Thanks Paul.

Unfortunately, I think you have documented < and > incorrectly and that they should, respectively, instead be:

(a < x) || ((a = x) && (b < y))
(a > x) || ((a = x) && (b > y))
[31 Mar 2015 13:57] 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.