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
Triage: Triaged: D4 (Minor)

[28 Feb 2008 21:43] Roland Bouman
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
[28 Feb 2008 21:47] Sveta Smirnova
Thank you for the report.

Verified as described.
[4 Mar 2008 3:23] Marc Alff
See related Bug#35020
[15 Jul 2009 18:05] Paul Dubois
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.
[15 Jul 2009 18:17] Paul Dubois
See also Bug#46211.
[18 May 2010 15:21] Paul Dubois
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.