Bug #114462 Strange SQL_BUFFER_RESULT error
Submitted: 23 Mar 2024 10:30 Modified: 25 Mar 2024 6:32
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.3.0, 8.0.36 OS:Ubuntu (Ubuntu 22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: SQL_BUFFER_RESULT, VIEW

[23 Mar 2024 10:30] Pedro Ferreira 
Description:
Run the following statements:

SET SESSION SQL_BUFFER_RESULT = ON;
CREATE VIEW v0 (c0) AS ((SELECT 1) UNION (SELECT 1));
SELECT c0 FROM v0; --Incorrect usage/placement of 'SQL_BUFFER_RESULT'

The error seems strange here, plus only happens if the view is defined as a UNION.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the statements above.
[25 Mar 2024 6:32] MySQL Verification Team 
Hello Pedro Ferreira,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh
[4 Apr 2024 9:20] Dag Wanvik 
Posted by developer:
 
Can be seen in 5.7 also.
SET SESSION SQL_BUFFER_RESULT = ON;
CREATE VIEW v0 (c0) AS SELECT 1 UNION SELECT 1;
> select * from v0;
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_BUFFER_RESULT'

One would get the same error by this syntax:
CREATE VIEW v0 (c0) AS SELECT 1 UNION SELECT SQL_BUFFER_RESULT 1;

Perhaps the semantics of this variable isn't clearly enough defined.. Should it be applied only on the top level SELECT
or on all SELECTs where it's applicable? 
It seems wrong in any case that setting it should give an error here..