MySQL Bugs: #34943: effect of combining SQL_%_RESULT modifiers and use in subqueries should be doc'd

Bug #34943	effect of combining SQL_%_RESULT modifiers and use in subqueries should be doc'd
Submitted:	28 Feb 2008 22:43	Modified:	28 Feb 2008 22:47
Reporter:	Roland Bouman
Status:	Verified
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Any
Assigned to:	Paul DuBois	Target Version:
Triage:	Triaged: D4 (Minor)

Description:
In

http://dev.mysql.com/doc/refman/5.1/en/select.html

it reads:

SELECT
...
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

Implying that the SQL_%_RESULT modifiers can be combined at least syntactically. This is
the case. However, the documentation does not explain what the effect is if these
modifiers are combined. 

At least, the semantics for SQL_BIG_RESULT and SQL_SMALL_RESULT seem to contradict one
another so there should be some explanation of the resulting behaviour when we have
queries like 

SELECT SQL_SMALL_RESULT SQL_BIG_RESULT SQL_BUFFER_RESULT CountryCode, Count(*)
FROM City GROUP BY CountryCode

It would also be interesting to know whether there is a difference in whether these
modifiers appear in only the outer SELECT as opposed to in a subquery.

Something that is not documented at all as far as i can see is the fact that
SQL_BUFFER_RESULT cannot be used in subqueries and results in an error:

ERROR 1234 (42000): Incorrect usage/placement of 'SQL_BUFFER_RESULT'

How to repeat:
na

Suggested fix:
1) please document that SQL_BUFFER_RESULT will result in 

ERROR 1234 (42000): Incorrect usage/placement of 'SQL_BUFFER_RESULT'

when used in a subquery

2) please describe the effect of combining these SQL_%%_RESULT modifiers in one query,
and what the effect is of combining them in one SELECT expression

Thank you for the report.

Verified as described.

See related Bug#35020

I have added this for SQL_BUFFER_RESULT:

This option can be used only for top-level SELECT statements, not for subqueries or
following UNION.

See also Bug#46211.