[MDEV-2598] LP:780425 - sql_buffer_result=1 gives wrong result for GROUP BY with a constant expression Created: 2011-05-10  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Michael Widenius Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug780425.xml    

 Description   

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).

 Comments   
Comment by Oleksandr Byelkin [ 2011-07-22 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with subquery
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;

Comment by Oleksandr Byelkin [ 2011-07-22 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with subquery
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

Comment by Oleksandr Byelkin [ 2011-07-22 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with subquery
end_write set instead of end_write_group.

Comment by Oleksandr Byelkin [ 2011-07-25 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with subquery
The problem has nothing related to subquery, just constant groupping:

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

Comment by Oleksandr Byelkin [ 2011-07-25 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with a constant expression
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;

Comment by Oleksandr Byelkin [ 2011-07-25 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with a constant expression
join->sort_and_group set to false (which is incorrect)

Comment by Oleksandr Byelkin [ 2011-07-25 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with a constant expression
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.

Comment by Rasmus Johansson (Inactive) [ 2011-11-15 ]

Launchpad bug id: 780425

Comment by Michael Widenius [ 2011-11-15 ]

Re: sql_buffer_result=1 gives wrong result for GROUP BY with a constant expression
Fix pushed into MariaDB 5.2
(Very old MySQL bug, should not be critical for MariaDB 5.1)

Generated at Thu Feb 08 06:42:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.