Bug #24678 can't use SQL_BUFFER_RESULT on union all
Submitted: 29 Nov 2006 5:33 Modified: 1 Apr 2009 16:09
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.1.14-BK, 5.1.11 OS:Linux (linux)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc, SQL_BUFFER_RESULT

[29 Nov 2006 5:33] Roberto Spadim
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'
[29 Nov 2006 5:47] Valeriy Kravchuk
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.
[29 Nov 2006 6:21] Roberto Spadim
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
[11 Mar 2009 13:43] Georgi Kodinov
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.
[11 Mar 2009 16:28] Roberto Spadim
OK, that's a better documentation problem :)
tnkx
[1 Apr 2009 16:09] Paul DuBois
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.