MySQL Bugs: #46211: SQL_BIG_RESULT, SQL_SMALL_RESULT, SQL_BUFFER

Bug #46211	SQL_BIG_RESULT, SQL_SMALL_RESULT, SQL_BUFFER_RESULT should be mutually exclusive
Submitted:	15 Jul 20:16	Modified:	15 Jul 23:46
Reporter:	Paul DuBois
Status:	Verified
Category:	Server: Parser	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Martin Hansson	Target Version:
Triage:	Triaged: D3 (Medium)

Description:
This report is a follow-up to Bug#34943, filed for the purpose
of obtaining information from a developer, and possibly causing
a change to the SELECT parser.

http://dev.mysql.com/doc/refman/6.0/en/select.html
describes the effect of three SELECT options that
all relate to the use of temporary tables:

* SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the
  optimizer that the result set has many rows. In this case, MySQL
  directly uses disk-based temporary tables if needed, and prefers
  sorting to using a temporary table with a key on the GROUP BY
  elements.

* 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. This option can be used only for top-level SELECT statements,
  not for subqueries or following UNION.

* SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the
  optimizer that the result set is small. In this case, MySQL uses
  fast temporary tables to store the resulting table instead of using
  sorting. This should not normally be needed.

Question: All three of these options can be given in the same
statement. Shouldn't they be mutually exclusive? (Similar to
the way that SQL_CACHE and SQL_NO_CACHE have been made mutually
exclusive; see Bug#35020.)

If so, it is a bug that they are not mutually exclusive.

If not (i.e., they are not mutually exclusive), what is the
effect of specifying these options in combination? Undefined?
Or is there some specific meaning to combining them?

How to repeat:
N/A

Thank you for the bug report.

IMHO:
It would not make sense to mix SQL_BIG_RESULT with SQL_SMALL_RESULT. However, both of
these should be allowed in combination with SQL_BUFFER_RESULT as they address different
issues/bottlenecks.

SQL_[BIG|SMALL]_RESULT tells the optimizer how to compute groups or remove duplicates
(GROUP BY and DISTINCT constructs, respectively), whereas SQL_BUFFER_RESULT seems to have
the intention of alleviating communication bottlenecks between server and client, hence
they should be useful even in combination. 

Example:

SELECT SQL_BUFFER_RESULT SQL_SMALL_RESULT MAX(s1.price), 
(SELECT hires_image FROM sales 
 WHERE date = s1.date and branch = s1.branch and price = max(s1.price))
FROM sales s1 
GROUP BY s1.date, s1.branch
HAVING MAX(price) > 100;

Computing the group might not require that much memory, but sending the result to a
client over a narrow bandwidth connection might take long.

Martin, thanks for the info.

What is the effect if SQL_BIG_RESULT with SQL_SMALL_RESULT are given together? Does one
take precedence? Is the effect "undefined"?