[MDEV-23828] Wrong result upon 2nd execution of PS/SP with EXISTS subquery converted to semijoin Created: 2020-09-27  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer, Prepared Statements
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-30073 Wrong result on 2nd execution of PS f... Stalled
Relates
relates to MDEV-30724 Assertion `0' failed in on 2nd SP cal... Open
relates to MDEV-23919 Assertion `0' failed in subselect_has... Confirmed

 Description    

CREATE TABLE t (a INT, b VARCHAR(8));
 
INSERT INTO t VALUES (1,'foo'),(2,'bar');
 
 
 
PREPARE stmt FROM 'SELECT * FROM t WHERE EXISTS (SELECT b FROM ( SELECT * FROM t ) sq WHERE sq.a = t.a)';
 
EXECUTE stmt;
 
EXECUTE stmt;
 
 
 
# Cleanup
 
DROP TABLE t;

Actual result 10.2 7c5519c1

 
PREPARE stmt FROM 'SELECT * FROM t WHERE EXISTS (SELECT b FROM ( SELECT * FROM t ) sq WHERE sq.a = t.a)';
 
EXECUTE stmt;
 
a	b
 
1	foo
 
2	bar
 
EXECUTE stmt;
 
a	b
 
DROP TABLE t;

Reproducible on 10.1-10.5 with at least MyISAM, InnoDB, Aria.
Not reproducible on MySQL 5.6.
Not reproducible with optimizer_switch='exists_to_in=off'

A slightly different test case of the same nature additionally causes a debug assertion failure (and still a wrong result on a non-debug build):

CREATE TABLE t (a INT, b TEXT);
 
INSERT INTO t VALUES (1,'foo'),(2,'bar');
 
 
 
PREPARE stmt FROM 'SELECT * FROM t WHERE EXISTS (SELECT b FROM ( SELECT * FROM t ) sq WHERE sq.a = t.a) OR t.a > 5';
 
EXECUTE stmt;
 
EXECUTE stmt;
 
 
 
# Cleanup
 
DROP TABLE t;

(The difference is an extra condition in WHERE and TEXT instead of VARCHAR as a column type).

10.2 debug 7c5519c1

 
mysqld: /data/src/10.2/sql/item_subselect.cc:5029: bool subselect_hash_sj_engine::init(List<Item>*, uint): Assertion `0' failed.
 
200928  2:32:52 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f64076a8f36 in __GI___assert_fail (assertion=0x564d7c6f8b35 "0", file=0x564d7c6f9340 "/data/src/10.2/sql/item_subselect.cc", line=5029, function=0x564d7c6fb000 "bool subselect_hash_sj_engine::init(List<Item>*, uint)") at assert.c:101
 
#8  0x0000564d7bea7300 in subselect_hash_sj_engine::init (this=0x7f63f00161f8, tmp_columns=0x7f63f007f9d8, subquery_id=2) at /data/src/10.2/sql/item_subselect.cc:5029
 
#9  0x0000564d7bea39af in Item_in_subselect::setup_mat_engine (this=0x7f63f0095908) at /data/src/10.2/sql/item_subselect.cc:3430
 
#10 0x0000564d7bd040ba in JOIN::choose_subquery_plan (this=0x7f63f00130b0, join_tables=1) at /data/src/10.2/sql/opt_subselect.cc:5933
 
#11 0x0000564d7bb9d042 in make_join_statistics (join=0x7f63f00130b0, tables_list=..., keyuse_array=0x7f63f00133a0) at /data/src/10.2/sql/sql_select.cc:4617
 
#12 0x0000564d7bb926af in JOIN::optimize_inner (this=0x7f63f00130b0) at /data/src/10.2/sql/sql_select.cc:1584
 
#13 0x0000564d7bb90ba6 in JOIN::optimize (this=0x7f63f00130b0) at /data/src/10.2/sql/sql_select.cc:1114
 
#14 0x0000564d7bb3ff57 in st_select_lex::optimize_unflattened_subqueries (this=0x7f63f007e1c0, const_only=false) at /data/src/10.2/sql/sql_lex.cc:3871
 
#15 0x0000564d7bd028bc in JOIN::optimize_unflattened_subqueries (this=0x7f63f0012a90) at /data/src/10.2/sql/opt_subselect.cc:5326
 
#16 0x0000564d7bb9431a in JOIN::optimize_inner (this=0x7f63f0012a90) at /data/src/10.2/sql/sql_select.cc:2076
 
#17 0x0000564d7bb90ba6 in JOIN::optimize (this=0x7f63f0012a90) at /data/src/10.2/sql/sql_select.cc:1114
 
#18 0x0000564d7bb9a0e4 in mysql_select (thd=0x7f63f0000d90, tables=0x7f63f007f280, wild_num=0, fields=..., conds=0x7f63f0012878, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f63f0082788, unit=0x7f63f007da80, select_lex=0x7f63f007e1c0) at /data/src/10.2/sql/sql_select.cc:3819
 
#19 0x0000564d7bb8e31a in handle_select (thd=0x7f63f0000d90, lex=0x7f63f007d9c0, result=0x7f63f0082788, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:361
 
#20 0x0000564d7bb58eb8 in execute_sqlcom_select (thd=0x7f63f0000d90, all_tables=0x7f63f007f280) at /data/src/10.2/sql/sql_parse.cc:6218
 
#21 0x0000564d7bb4f77a in mysql_execute_command (thd=0x7f63f0000d90) at /data/src/10.2/sql/sql_parse.cc:3524
 
#22 0x0000564d7bb7afa9 in Prepared_statement::execute (this=0x7f63f0037e40, expanded_query=0x7f6401802990, open_cursor=false) at /data/src/10.2/sql/sql_prepare.cc:4842
 
#23 0x0000564d7bb794a8 in Prepared_statement::execute_loop (this=0x7f63f0037e40, expanded_query=0x7f6401802990, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/10.2/sql/sql_prepare.cc:4271
 
#24 0x0000564d7bb77200 in mysql_sql_stmt_execute (thd=0x7f63f0000d90) at /data/src/10.2/sql/sql_prepare.cc:3379
 
#25 0x0000564d7bb4f7bf in mysql_execute_command (thd=0x7f63f0000d90) at /data/src/10.2/sql/sql_parse.cc:3540
 
#26 0x0000564d7bb5cc47 in mysql_parse (thd=0x7f63f0000d90, rawbuf=0x7f63f00126f8 "EXECUTE stmt", length=12, parser_state=0x7f64018035f0, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7733
 
#27 0x0000564d7bb4af56 in dispatch_command (command=COM_QUERY, thd=0x7f63f0000d90, packet=0x7f63f0008b51 "EXECUTE stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1823
 
#28 0x0000564d7bb49a51 in do_command (thd=0x7f63f0000d90) at /data/src/10.2/sql/sql_parse.cc:1377
 
#29 0x0000564d7bca3695 in do_handle_one_connection (connect=0x564d7e34daa0) at /data/src/10.2/sql/sql_connect.cc:1336
 
#30 0x0000564d7bca33fa in handle_one_connection (arg=0x564d7e34daa0) at /data/src/10.2/sql/sql_connect.cc:1241
 
#31 0x0000564d7c4c7a6c in pfs_spawn_thread (arg=0x564d7e356770) at /data/src/10.2/storage/perfschema/pfs.cc:1869
 
#32 0x00007f6407bba609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#33 0x00007f6407794103 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.2 non-debug 7c5519c1

 
EXECUTE stmt;
 
a	b
 
1	foo
 
2	bar
 
EXECUTE stmt;
 
a	b
 
Warnings:
 
Warning	1292	Truncated incorrect DOUBLE value: 'foo'
 
Warning	1292	Truncated incorrect DOUBLE value: 'bar'
 
Warning	1292	Truncated incorrect DOUBLE value: 'foo'
 
Warning	1292	Truncated incorrect DOUBLE value: 'bar'
 
DROP TABLE t;

Same reproducibility notes apply.
Generated at Thu Feb 08 09:25:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.