MySQL Bugs: #69235: [DOC] Very important notes missing about multiple comparisons with same subquery

Bug #69235	[DOC] Very important notes missing about multiple comparisons with same subquery
Submitted:	14 May 2013 19:18	Modified:	15 Jun 2013 12:22
Reporter:	matteo sisti sette	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	comparison, documentation, subquery

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

not a word is said about how to make multiple comparison on the result of a subquery. This is the most common kind of comparison when using a subquery (that is, ANYBODY using comparisons with subqueries will need to do that) and this is exactly the place in documentation where anybody would expect to find it.

That is, there are examples like:

SELECT * FROM t1
  WHERE column1 = (SELECT MAX(column2) FROM t2);

but there is no example about how to use the result from the subquery in more than one comparison, e.g.:

SELECT * FROM t1
  WHERE (SELECT MAX(column2) FROM t2)>some_value AND (how-the-hell-you-name-the-subquery-result-here)<some_other_value ;

How to repeat:
http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-subqueries.html

Suggested fix:
Explain how to accomplish that, or if not possible, put a note saying it's not possible.

Hi!

SQL standard does not provide means on saving results from the nested queries. MySQL as well as some other SQL databases, follows the standard strictly. Hence, you are left to your own means. Luckily, SQL knows for user variables. So, you can do:

Here is an example from our manual:

----------------

   For example, to find the articles with the highest and lowest
   price you can do this:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

---------------

Our manual has several examples on how to do that. Let us know if you think that anything is still missing.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".