Details
Major
Description
The following test case
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:
field1 0 7
while the same query without the view returns 3 rows:
field1 NULL 0 7
EXPLAIN with the default optimizer_switch:
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`
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
As Serg mentioned it could be http://bugs.mysql.com/bug.php?id=66896
remove_duplicates (sql_select.cc) do not take into account null_ptr of fields, it uses for comparison only fields data without NULL flag.
Distinct index of temporary table to implement DISTINCT clause used only in case of absence of GROUP BY clause.
create table t1(i int, g int);
insert into t1
values (null, 1), (0, 2);
select distinct i from t1 group by g;
drop table t1;
pushed in 5.3
The suspiciouse thing is DEPENDENT SUBQUERY #4 because here is only one subquery and it should be (and is) DEPENDENT SUBQUERY #3