Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.28, 5.3.10, 5.2.12, 5.1.62
-
None
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.