We're updating the issue view to help you get more done. 

Wrong result (missing row) on a DISTINCT query with the same subquery in the SELECT list and GROUP BY

Description

The following test case

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(7); CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (7),(0),(3); CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT DISTINCT ( SELECT MAX(a) FROM t1 WHERE alias.b = a ) AS field1 FROM t2 AS alias GROUP BY field1; SELECT * FROM v;

returns two rows only:

1 2 3 field1 0 7

while the same query without the view returns 3 rows:

1 2 3 4 field1 NULL 0 7

EXPLAIN with the default optimizer_switch:

1 2 3 4 5 6 7 8 9 10 11 EXPLAIN EXTENDED SELECT * FROM v; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 2 DERIVED alias ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort 4 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.alias.b' of SELECT #3 was resolved in SELECT #2 Note 1276 Field or reference 'test.alias.b' of SELECT #4 was resolved in SELECT #2 Note 1003 select `v`.`field1` AS `field1` from `test`.`v`
1 2 3 4 5 branch: maria/5.5 bzr version-info revision-id: wlad@montyprogram.com-20121120142439-zvx42vxhc8lurmnv date: 2012-11-20 15:24:39 +0100 revno: 3576

Reproducible with the default optimizer_switch as well as with all OFF values, except for in_to_exists or materialization that have to be on to run the query.

Also reproducible on all current versions of MariaDB, and on MySQL 5.1 and 5.5; but not reproducible on MySQL 5.6 revno 4458.

Environment

None

Status

Assignee

Sergei Golubchik

Reporter

Elena Stepanova

Labels

Fix versions

Affects versions

10.0.0
5.5.28
5.3.10
5.2.12
5.1.62

Priority

Major