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

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.0.13, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: 10.5, 10.6
Component/s: Optimizer, Prepared Statements
Labels:
None

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

Attachments

Issue Links

is blocked by

MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS

In Progress

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2014-09-05 18:19

Oleksandr Byelkin made changes - 2014-09-30 14:43

Field	Original Value	New Value
Status	Open [ 1 ]	In Progress [ 3 ]

Oleksandr Byelkin made changes - 2015-05-04 12:05

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2015-05-06 14:54

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2015-05-06 18:48 - edited

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

Oleksandr Byelkin added a comment - 2015-05-06 18:48 - edited 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))))))

Oleksandr Byelkin added a comment - 2015-05-07 09:59

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

Oleksandr Byelkin added a comment - 2015-05-07 09:59 Above is result about taking materialization strategy for IN subquery which was done later then printed first line but kept till next execution.

Oleksandr Byelkin added a comment - 2015-05-07 17:54

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"

Oleksandr Byelkin added a comment - 2015-05-07 17:54 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"

Oleksandr Byelkin added a comment - 2015-05-11 12:22

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

Oleksandr Byelkin added a comment - 2015-05-11 12:22 Above happened because it trying to store in Field_long 'i' !!!

Oleksandr Byelkin added a comment - 2015-05-11 19:31

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.

Oleksandr Byelkin added a comment - 2015-05-11 19:31 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.

Oleksandr Byelkin added a comment - 2015-05-11 19:50

tmp_key->key_part changed

Oleksandr Byelkin added a comment - 2015-05-11 19:50 tmp_key->key_part changed

Oleksandr Byelkin made changes - 2015-05-12 19:50

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 53736 ]

MariaDB v3 [ 66560 ]

Sergei Golubchik made changes - 2015-06-09 16:57

Sprint

10.0.20 [ 5 ]

Sergei Golubchik made changes - 2015-06-17 01:05

Sprint

10.0.20 [ 5 ]

10.0.20, 10.1.6-1 [ 5, 6 ]

Sergei Golubchik made changes - 2015-06-17 01:05

Rank

Ranked higher

Sergei Golubchik made changes - 2015-06-17 01:25

Sprint

10.0.20, 10.1.6-1 [ 5, 6 ]

10.0.20 [ 5 ]

Rasmus Johansson (Inactive) made changes - 2016-02-10 10:29

Sprint

10.0.20 [ 5 ]

10.0.20, 10.0.24-0 [ 5, 35 ]

Rasmus Johansson (Inactive) made changes - 2016-02-10 10:29

Rank

Ranked higher

Oleksandr Byelkin made changes - 2016-02-11 17:05

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2016-02-11 20:20

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:

Oleksandr Byelkin added a comment - 2016-02-11 20:20 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: 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) -> i.<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: 0x7f60d112a008 Bytes: (5) 00 00 00 00 00

Oleksandr Byelkin added a comment - 2016-02-11 20:25

changing the DESTINATION field can explain a problem...

Oleksandr Byelkin added a comment - 2016-02-11 20:25 changing the DESTINATION field can explain a problem...

Oleksandr Byelkin added a comment - 2016-02-11 22:27 - edited

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

Oleksandr Byelkin added a comment - 2016-02-11 22:27 - edited 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)).

Oleksandr Byelkin added a comment - 2016-02-14 13:46 - edited

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

Oleksandr Byelkin added a comment - 2016-02-14 13:46 - edited 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!!!

Oleksandr Byelkin made changes - 2016-02-14 15:07

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2016-02-14 16:47

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2016-02-14 17:53

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.

—

Oleksandr Byelkin added a comment - 2016-02-14 17:53 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. —

Oleksandr Byelkin made changes - 2016-02-14 18:24

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Petrunia made changes - 2016-02-15 12:35

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2016-02-15 12:36

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Sergei Petrunia added a comment - 2016-02-20 18:07

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.

Sergei Petrunia added a comment - 2016-02-20 18:07 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.

Elena Stepanova made changes - 2019-02-18 12:47

Component/s		Optimizer [ 10200 ]
Component/s		Prepared Statements [ 10804 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s	10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Julien Fritsch made changes - 2020-12-01 12:40

Fix Version/s

10.1 [ 16100 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 66560 ]

MariaDB v4 [ 143717 ]

Ralf Gebhardt made changes - 2022-08-04 09:20

Fix Version/s

10.2 [ 14601 ]

Alice Sherepa made changes - 2022-12-02 17:34

Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]

Alice Sherepa made changes - 2022-12-02 17:35

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]

Sergei Golubchik made changes - 2022-12-02 17:38

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2023-03-06 08:43

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 14:23

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2023-04-27 14:45

Fix Version/s

10.8 [ 26121 ]

Igor Babaev (Inactive) made changes - 2023-08-06 04:04

Assignee

Sergei Petrunia [ psergey ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) added a comment - 2023-08-06 04:06 - edited

Just reassigned the bug to myself.

Igor Babaev (Inactive) added a comment - 2023-08-06 04:06 - edited Just reassigned the bug to myself.

Igor Babaev (Inactive) made changes - 2023-08-06 04:06

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-10-26 20:32

Link

This issue is blocked by MDEV-30073 [ MDEV-30073 ]

Julien Fritsch made changes - 2023-11-28 10:39

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2024-09-10 15:07

Fix Version/s

10.4 [ 22408 ]

Rex Johnston made changes - 2025-03-27 00:46

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

People

Assignee:: Rex Johnston

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2014-09-05 18:19

Updated:: 2025-03-27 00:46

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration