[#MDEV-6704] Wrong result (extra rows) on 2nd execution of PS with exists_to

[MDEV-6704] Wrong result (extra rows) on 2nd execution of PS with exists_to_in, MERGE view Created: 2014-09-05 Updated: 2023-11-28
Status:	Stalled
Project:	MariaDB Server
Component/s:	Optimizer, Prepared Statements
Affects Version/s:	10.0.13, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6

Type:

Bug

Priority:

Critical

Reporter:

Elena Stepanova

Assignee:

Igor Babaev

Resolution:

Unresolved

Votes:

Labels:

None

Issue Links:

Blocks
is blocked by	MDEV-30073	Wrong result on 2nd execution of PS f...	Stalled

Sprint:

10.0.20, 10.0.24

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;

bar

EXECUTE stmt;

foo

bar

Expected result:

EXECUTE stmt;

bar

EXECUTE stmt;

bar

revision-id: knielsen@knielsen-hq.org-20140902120701-txesdmkczel84qgf

revno: 4369

branch-nick: 10.0

Comments

Comment by Oleksandr Byelkin [ 2015-05-06 ]

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))))))

Comment by Oleksandr Byelkin [ 2015-05-07 ]

Above is result about taking materialization strategy for IN subquery which was done later then printed first line but kept till next execution.

Comment by Oleksandr Byelkin [ 2015-05-07 ]

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"

Comment by Oleksandr Byelkin [ 2015-05-11 ]

Above happened because it trying to store in Field_long 'i' !!!

Comment by Oleksandr Byelkin [ 2015-05-11 ]

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.

Comment by Oleksandr Byelkin [ 2015-05-11 ]

tmp_key->key_part changed

Comment by Oleksandr Byelkin [ 2016-02-11 ]

First call
T@6 : | | | | | | | | | | | | | | | >subselect_uniquesubquery_engine::exec
T@6 : | | | | | | | | | | | | | | | | >subselect_uniquesubquery_engine::copy_ref_key
T@6 : | | | | | | | | | | | | | | | | | >save_field_in_field
T@6 : | | | | | | | | | | | | | | | | | | XXX: a.t1(./test/t1) -> b.<subquery2>(/home/sanja/maria/git/server/mysql-test/var/tmp/mysqld.1/#sql_739c_1)
T@6 : | | | | | | | | | | | | | | | | | <save_field_in_field
T@6 : | | | | | | | | | | | | | | | | <subselect_uniquesubquery_engine::copy_ref_key
T@6 : | | | | | | | | | | | | | | | | >ha_index_init
T@6 : | | | | | | | | | | | | | | | | <ha_index_init
T@6 : | | | | | | | | | | | | | | | | >handler::ha_index_read_map
T@6 : | | | | | | | | | | | | | | | | | XXX: Memory: 0x7f60d1111fa8 Bytes: (7)
00 03 00 66 6F 6F 00

Second call:

Comment by Oleksandr Byelkin [ 2016-02-11 ]

changing the DESTINATION field can explain a problem...

Comment by Oleksandr Byelkin [ 2016-02-11 ]

Second time used other engine, so probably passing the same JOIN_TAB address to it is just a coincidence or (using wrong JOIN (small probability, because it should be freed)).

Comment by Oleksandr Byelkin [ 2016-02-14 ]

The problem is that Item_in_subselect::setup_mat_engine() uses select_engine->join->fields_list which on the second execution chaged due to transformation rollback!!!

Comment by Oleksandr Byelkin [ 2016-02-14 ]

revision-id: 016f44b15455d2ca0018902cdc78d1cb29a14423 (mariadb-10.0.23-30-g016f44b)
parent(s): 089b65fd5e8ecd1971cf4c6c372e20575ff4ca9e
committer: Oleksandr Byelkin
timestamp: 2016-02-14 16:52:56 +0100
message:

MDEV-6704: Wrong result (extra rows) on 2nd execution of PS with exists_to_in, MERGE view

The problem was that after first execution permanent exists-to-in transformation became broken back by rolling back temporary changes of view resolving.

Added method to rewrite temporary changes.

—

Comment by Sergei Petrunia [ 2016-02-20 ]

Attempted to do a review pass, discussed with sanja. Current approach works but in my opinion change_item_tree() is evil and should not be extended.
I'd like to come up with some other solution. I need to think about this.

Comment by Igor Babaev [ 2023-08-06 ]

Just reassigned the bug to myself.

Generated at Thu Feb 08 07:13:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-6704] Wrong result (extra rows) on 2nd execution of PS with exists_to_in, MERGE view Created: 2014-09-05 Updated: 2023-11-28