MySQL Bugs: #24678: can't use SQL_BUFFER

Bug #24678	can't use SQL_BUFFER_RESULT on union all
Submitted:	29 Nov 2006 6:33	Modified:	1 Apr 18:09
Reporter:	Roberto Spadim (Basic Quality Contributor)
Status:	Closed
Category:	Server: Docs	Severity:	S1 (Critical)
Version:	5.1.14-BK, 5.1.11	OS:	Linux (linux)
Assigned to:	Paul DuBois	Target Version:	5.1+
Tags:	SQL_BUFFER_RESULT, qc
Triage:	Triaged: D3 (Medium) / R2 (Low) / E3 (Medium)

Description:
can't use SQL_BUFFER_RESULT on queries like

SELECT fields FROM (
(SELECT SQL_BUFFER_RESULT fileds FROM table1 WHERE condition)
union all
(SELECT SQL_BUFFER_RESULT fileds FROM table1 WHERE condition)
) AS tmp_table

How to repeat:
create an table with some fields and do:

SELECT fields FROM (
(SELECT SQL_BUFFER_RESULT fileds FROM table1 WHERE condition)
union all
(SELECT SQL_BUFFER_RESULT fileds FROM table1 WHERE condition)
) AS tmp_table

Suggested fix:
i don't know why can't use?

portuguese language return:

Errado uso/colocação de 'SQL_BUFFER_RESULT'

Thank you for a bug report. Verified just as described with 5.1.14-BK on Linux:

mysql> select sql_buffer_result i from t1;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select i from (select sql_buffer_result i from t1) as tmp;
ERROR 1234 (42000): Incorrect usage/placement of 'sql_buffer_result'

So, UNION does not matter really. If this is intended behaviour, it should be described
at http://dev.mysql.com/doc/refman/5.1/en/select.html.

what could be done with this scenario:

select fields from (select SQL_BUFFER_RESULT fields from very_busy_table) AS
table1,table2 where table1.some_field=table2.some_field

in this case an workaround is create temporary table of table1 and use it 
but could we use it inline without creating temporary tables??

on UNION maybe could be used too, on UNION ALL no problems 
the main problem is, when using very busy tables locking is a problem so buffering it on
a temporary file help a lot to don't lock table

SQL_BUFFER_RESULT was never designed to work for anything but top level SELECTs. 
Our documentation (http://dev.mysql.com/doc/refman/5.1/en/select.html) says : 
"SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL
free the table locks early and helps in cases where it takes a long time to send the
result set to the client.".

Note the "to the client" part : this implies that the SQL_BUFFER_RESULT can be used only
for top level SELECTs. I agree that the documentation should be more explicit in this
respect, so I'm moving this to a documentation bug.
We have an idea to use SQL_BUFFER_RESULT as a hint for the optimizer controlling how it
should execute non-top-level SELECTs, but we don't have a schedule for this and it's tied
to other new development taking place in the MySQL development branch.

OK, that's a better documentation problem :)
tnkx

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.