Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.0, 5.5.28, 5.3.10
-
None
-
None
Description
The test case below returns different results when a query is run with a MERGE view comparing to a TEMPTABLE view or a base table. I believe that the TEMPTABLE/base table result is correct.
It can be related to MDEV-3873, there are similarities.
CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(2); |
|
|
CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (1),(3); |
|
|
CREATE OR REPLACE ALGORITHM=MERGE VIEW v AS SELECT * FROM t2; |
|
|
SELECT a FROM t1 AS alias |
WHERE a >= ALL ( |
SELECT b FROM t1 LEFT JOIN v ON (a = b) |
WHERE a = alias.a ); |
Actual result:
a
|
----
|
1
|
2
|
Result with a TEMPTABLE view or a base table:
Result:
a
|
----
|
1
|
EXPLAIN with the MERGE view:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where
|
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`alias`.`a` AS `a` from `test`.`t1` `alias` where <not>(<expr_cache><`test`.`alias`.`a`>(<in_optimizer>(`test`.`alias`.`a`,<exists>(select `test`.`t2`.`b` from `test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`b` = `test`.`t1`.`a`)) where ((`test`.`t1`.`a` = `test`.`alias`.`a`) and (<cache>(`test`.`alias`.`a`) < `test`.`t2`.`b`))))))
|
branch: maria/5.3
|
bzr version-info
|
revision-id: sergii@pisem.net-20121117155015-4ab41ncach4iavao
|
date: 2012-11-17 16:50:15 +0100
|
revno: 3603
|
Also reproducible on MariaDB 5.5, 10.0.
Not reproducible on MariaDB 5.2, MySQL 5.5, 5.6.
Attachments
Issue Links
- relates to
-
-
- Closed
-