MySQL Bugs: #39042: Row subquery can be used with all relational operators

Bug #39042	Row subquery can be used with all relational operators
Submitted:	26 Aug 2008 11:25	Modified:	26 Nov 2008 16:29
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.1/en/comparisons-using-subqueries.html

"For a comparison performed with one of these operators, the subquery must return a scalar, with the exception that = can be used with row subqueries. See Section 12.2.8.5, “Row Subqueries”."

This does not seem to hold true. I can use >, <. >=, <=, <=>, !=.

How to repeat:
create table one_row(i int, c char(1));
insert into one_row values (1,'A');

select (select * from one_row) >   (select * from one_row);
select (select * from one_row) <   (select * from one_row);
select (select * from one_row) >=  (select * from one_row);
select (select * from one_row) <=  (select * from one_row);
select (select * from one_row) <=> (select * from one_row);
select (select * from one_row) !=  (select * from one_row);

Suggested fix:
Fix the text in 
http://dev.mysql.com/doc/refman/5.1/en/comparisons-using-subqueries.html
to read: 

"For a comparison performed with one of these operators, the subquery must return a scalar. For the operators >, <. <=, =>, <=>, <> and !=, the subquery may also be a row subquery (or row constructor), provided both operands have the same number of columns. See Section 12.2.8.5, “Row Subqueries”."

It probably can't hurt to list these operators also at:

http://dev.mysql.com/doc/refman/5.1/en/row-subqueries.html

to explain that these are all legal relational operators for row constructors (such as as row subqueries)

I would also suggest adding a note to 

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

that the listed relational operators ('comparison operators') can be used with row constructor operands, linking to the section on row subqueries (i.e. http://dev.mysql.com/doc/refman/5.1/en/row-subqueries.html). I suggest this because currently, this page seems to indicate that the operators work only for scalars:

"These operations work for both numbers and strings."

(no mention of row constructors)

Thank you for the report.

Verified as described.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Modified http://dev.mysql.com/doc/refman/5.1/en/comparisons-using-subqueries.html:

"For a comparison of the subquery to a scalar, the subquery must return a scalar. For a comparison of the subquery to a row constructor, the subquery must be a row subquery that returns a row with the same number of values as the row constructor."

(all the operators are allowed, there's no need to enumerate them again.)

Also modified the other two sections to indicate that row constructor comparisons are allowed.