sql_buffer_result=1 gives wrong result for GROUP BY with a constant expression

[Michael Widenius]

This fails in MySQL 5.1, MariaDB 5.1 and above:

drop table if exists t1;
CREATE TABLE t1(f1 DECIMAL(5,1) UNSIGNED);
INSERT INTO t1 VALUES (0.2),(0.1);
set sql_buffer_result=0;
SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0);
set sql_buffer_result=1;
SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0);

The second query return two rows, the first one only one row (which is correct).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

A bit simplified test suite:

CREATE TABLE t1(f1 int UNSIGNED);
INSERT INTO t1 VALUES (2),(1);
set sql_buffer_result=0;
SELECT 1 as 'one' FROM t1 GROUP BY (SELECT f1 FROM t1 WHERE f1 = 0);
set sql_buffer_result=1;
SELECT 1 as 'one' FROM t1 GROUP BY (SELECT f1 FROM t1 WHERE f1 = 0);
drop table t1;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Number of rows in the secind case depends in number of rows in the table:
CREATE TABLE t1(f1 int UNSIGNED);
INSERT INTO t1 VALUES (3),(2),(1);
set sql_buffer_result=0;
SELECT 1 as 'one' FROM t1 GROUP BY (SELECT f1 FROM t1 WHERE f1 = 0);
one
1
set sql_buffer_result=1;
SELECT 1 as 'one' FROM t1 GROUP BY (SELECT f1 FROM t1 WHERE f1 = 0);
one
1
1
1

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

end_write set instead of end_write_group.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem has nothing related to subquery, just constant groupping:

SELECT 1 as 'one' FROM t1 GROUP BY '123' = 'abc';
one
1
1
1

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Current minimal test suite is:

CREATE TABLE t1(f1 int UNSIGNED);
INSERT INTO t1 VALUES (3),(2),(1);
set sql_buffer_result=0;
SELECT 1 as 'one' FROM t1 GROUP BY '123' = 'abc';
set sql_buffer_result=1;
SELECT 1 as 'one' FROM t1 GROUP BY '123' = 'abc';
drop table t1;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

join->sort_and_group set to false (which is incorrect)

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

in JOIN::exec in if(need_tmp) branch make_simple_join() drops JOIN::group flag (wich was the only trace (after reducing group list by optimizer) that we need grouping).

For query with no sql_buffer_result set need_tmp is also unset so we have no such problem.

[Michael Widenius (monty)]

Fix pushed into MariaDB 5.2
(Very old MySQL bug, should not be critical for MariaDB 5.1)