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 21:43	Modified:	18 May 2010 15:21
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

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.

I am closing this bug report. If/when Bug#46211 is updated, I will update the manual regarding simultaneous use of SQL_{BIG,SMALL}_RESULT.