GROUP BY not observed with derived_merge=on and unsatisfied join condition in maria-5.3

[Philip Stoev]

Not repeatable with derived_merge=off. The following query:

SELECT alias2.f1 AS field1
FROM t1
LEFT JOIN (
( SELECT * FROM t2 ) AS alias2
STRAIGHT_JOIN t3
ON t3.f2 > 5
) ON alias2.f1 >= 1
GROUP BY field1;

returns 2 NULLs even though the GROUP BY should have prevented the return of duplicate NULLs. Note that the inner ON condition is not satisfiable for any row. Only NULLs have ever been seen in the wrong result set.

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where

test case:

CREATE TABLE t1 ( f2 int) ;
INSERT IGNORE INTO t1 VALUES (1),(2);

CREATE TABLE t2 ( f1 int NOT NULL ) ;
INSERT INTO t2 VALUES (1),(2);

CREATE TABLE t3 ( f1 int, f2 int ) ;
INSERT INTO t3 VALUES (3,3),(4,4);

SELECT alias2.f1 AS field1
FROM t1
LEFT JOIN (
                ( SELECT * FROM t2 ) AS alias2
                STRAIGHT_JOIN t3
                ON t3.f2 > 5
) ON alias2.f1 >= 1
GROUP BY field1;

bzr version-info
revision-id: <email address hidden>
date: 2011-06-21 18:17:28 -0700
build-date: 2011-06-22 10:47:37 +0300
revno: 3054
branch-nick: maria-5.3