Details
-
Type:
Bug
-
Status: Stalled
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.0.13
-
Fix Version/s: 10.0
-
Component/s: None
-
Labels:None
-
Sprint:10.0.20
Description
CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('foo'),('bar'); CREATE TABLE t2 (i INT, b VARCHAR(3)) ENGINE=MyISAM; CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; INSERT INTO t2 VALUES (4,'foo'),(5,'baz'); PREPARE stmt FROM "SELECT * FROM t1 WHERE NOT EXISTS ( SELECT i FROM v2 WHERE b = a )"; EXECUTE stmt; EXECUTE stmt;
Actual result:
EXECUTE stmt; a bar EXECUTE stmt; a foo bar
Expected result:
EXECUTE stmt; a bar EXECUTE stmt; a bar
revision-id: knielsen@knielsen-hq.org-20140902120701-txesdmkczel84qgf revno: 4369 branch-nick: 10.0
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Above is result about taking materialization strategy for IN subquery which was done later then printed first line but kept till next execution.
subselect_uniquesubquery_engine::exec() on the second execution looks for incorrect key:
gdb) p (*key)@9
$3 = "\000\000\000\000\000\000\000\000"
against first execution key:
(gdb) p (*key)@9
$2 = "\000\003\000foo\000\000"
Above happened because it trying to store in Field_long 'i' !!!
subselect_hash_sj_engine::make_unique_engine() creates subselect_uniquesubquery_engine for every execution, so probably works incorrectly on the second one picking up wrong field.
tmp_key->key_part changed
Difference between 2 executions found:
WHERE:(t1 final_pushdown_cond) 0x7fa04c1599c8 (not(((`test`.`t1`.`a` is not null) and <in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`b` from `test`.`v2` where (`test`.`t2`.`b` is not null))))))
WHERE:(t1 final_pushdown_cond) 0x7fa04c1599c8 (not(((`test`.`t1`.`a` is not null) and <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in (select `test`.`t2`.`i` from `test`.`t2` where (`test`.`t2`.`b` is not null))))))