[MDEV-31551] [Draft] Assortment of crashes in convert_join_subqueries_to_semijoins with GIS Created: 2023-06-26  Updated: 2023-11-26  Resolved: 2023-11-26

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-32684 Server crash in JOIN::transform_in_pr... Open
Relates
relates to MDEV-20770 Server crashes in JOIN::transform_in_... Closed

 Description   

Reproducible, needs a clean up.
Reducing the test case makes the stack trace mutate.

# Search pattern(s): (?^s:JOIN::transform_in_predicates_into_in_subq)
 
 
 
CREATE DATABASE IF NOT EXISTS range_access_db;
 
CREATE DATABASE IF NOT EXISTS advanced_db;
 
CREATE TABLE advanced_db.t9_InnoDB (col1 GEOMETRY);
 
CREATE TABLE range_access_db.L AS SELECT * FROM advanced_db.t9_InnoDB;
 
CREATE TABLE advanced_db.t2_InnoDB (col1 DATETIME(2) NOT NULL, col2 VARBINARY(170) DEFAULT NULL COMMENT 'music', col3 BIGINT DEFAULT NULL WITHOUT SYSTEM VERSIONING);
 
CREATE TABLE range_access_db.P LIKE range_access_db.L;
 
USE range_access_db;
 
CREATE TABLE CreateOrReplaceTable21613 (col1 TEXT);
 
CREATE TABLE range_access_db.C (col1 INT ZEROFILL DEFAULT NULL, col2 MEDIUMINT NULL DEFAULT NULL COMMENT 'honest', col3 BIT NULL);
 
CREATE TABLE range_access_db.G LIKE advanced_db.t2_InnoDB;
 
USE advanced_db;
 
CREATE TABLE CreateOrReplaceTable21613 AS SELECT * FROM range_access_db.G;
 
CREATE TABLE CreateOrReplaceTable21615 (col1 BIT DEFAULT NULL CHECK (col1 IS NULL OR col1), col2 DECIMAL NULL);
 
 
 
PREPARE stmt_ExecuteAsPS_21613 FROM ' SELECT alias1.col1 AS cfield1 FROM range_access_db.C AS alias1 WHERE EXISTS (SELECT SQ1_alias1.col1 AS SQ1_cfield1 FROM advanced_db.CreateOrReplaceTable21615 AS SQ1_alias1 WHERE SQ1_alias1.col1 IN ((SELECT C_SQ1_alias1.col1 AS C_SQ1_ifield1 FROM (advanced_db.CreateOrReplaceTable21613 AS C_SQ1_alias1 INNER JOIN advanced_db.CreateOrReplaceTable21615 AS C_SQ1_alias2 ) WHERE C_SQ1_alias2.col1 >= alias1.col1 AND C_SQ1_alias2.col2 = @var1)) AND alias1.col1 IN ((SELECT C_SQ2_alias1.col1 AS C_SQ2_ifield1 FROM range_access_db.P AS C_SQ2_alias1))) GROUP BY cfield1';
 
 
 
--error 4078
 
EXECUTE stmt_ExecuteAsPS_21613;
 
EXECUTE stmt_ExecuteAsPS_21613;

10.4 f5dceafd

 
==1903678==ERROR: AddressSanitizer: use-after-poison on address 0x62b000063c30 at pc 0x55fee68a7802 bp 0x7ff7a9235a20 sp 0x7ff7a9235a18
 
WRITE of size 8 at 0x62b000063c30 thread T5
 
    #0 0x55fee68a7801 in base_list::empty() /data/src/10.4/sql/sql_list.h:159
 
    #1 0x55fee71580af in JOIN::transform_in_predicates_into_in_subq(THD*) /data/src/10.4/sql/sql_tvc.cc:1150
 
    #2 0x55fee7052641 in convert_join_subqueries_to_semijoins(JOIN*) /data/src/10.4/sql/opt_subselect.cc:1130
 
    #3 0x55fee6c3cec4 in JOIN::optimize_inner() /data/src/10.4/sql/sql_select.cc:1967
 
    #4 0x55fee6c3a790 in JOIN::optimize() /data/src/10.4/sql/sql_select.cc:1711
 
    #5 0x55fee6b188c1 in st_select_lex::optimize_unflattened_subqueries(bool) /data/src/10.4/sql/sql_lex.cc:4236
 
    #6 0x55fee7075638 in JOIN::optimize_unflattened_subqueries() /data/src/10.4/sql/opt_subselect.cc:5602
 
    #7 0x55fee6c49a1e in JOIN::optimize_stage2() /data/src/10.4/sql/sql_select.cc:3165
 
    #8 0x55fee6c41af2 in JOIN::optimize_inner() /data/src/10.4/sql/sql_select.cc:2394
 
    #9 0x55fee6c3a790 in JOIN::optimize() /data/src/10.4/sql/sql_select.cc:1711
 
    #10 0x55fee6c5b664 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.4/sql/sql_select.cc:4812
 
    #11 0x55fee6c2c460 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.4/sql/sql_select.cc:442
 
    #12 0x55fee6b9c28a in execute_sqlcom_select /data/src/10.4/sql/sql_parse.cc:6473
 
    #13 0x55fee6b8979f in mysql_execute_command(THD*) /data/src/10.4/sql/sql_parse.cc:3976
 
    #14 0x55fee6bf8d46 in Prepared_statement::execute(String*, bool) /data/src/10.4/sql/sql_prepare.cc:5024
 
    #15 0x55fee6bf4386 in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /data/src/10.4/sql/sql_prepare.cc:4493
 
    #16 0x55fee6bee1f8 in mysql_sql_stmt_execute(THD*) /data/src/10.4/sql/sql_prepare.cc:3577
 
    #17 0x55fee6b897e4 in mysql_execute_command(THD*) /data/src/10.4/sql/sql_parse.cc:3992
 
    #18 0x55fee6ba5462 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.4/sql/sql_parse.cc:8008
 
    #19 0x55fee6b7b7a5 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.4/sql/sql_parse.cc:1857
 
    #20 0x55fee6b78314 in do_command(THD*) /data/src/10.4/sql/sql_parse.cc:1378
 
    #21 0x55fee6f770b9 in do_handle_one_connection(CONNECT*) /data/src/10.4/sql/sql_connect.cc:1420
 
    #22 0x55fee6f769d0 in handle_one_connection /data/src/10.4/sql/sql_connect.cc:1324
 
    #23 0x55fee7be3aed in pfs_spawn_thread /data/src/10.4/storage/perfschema/pfs.cc:1869
 
    #24 0x7ff7b1087fd3 in start_thread nptl/pthread_create.c:442
 
    #25 0x7ff7b11085bb in clone3 ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
 
 
 
0x62b000063c30 is located 6704 bytes inside of 24608-byte region [0x62b000062200,0x62b000068220)
 
allocated by thread T5 here:
 
    #0 0x7ff7b16b89cf in __interceptor_malloc ../../../../src/libsanitizer/asan/asan_malloc_linux.cpp:69
 
    #1 0x55fee872ad13 in my_malloc /data/src/10.4/mysys/my_malloc.c:101
 
    #2 0x55fee8706eaf in reset_root_defaults /data/src/10.4/mysys/my_alloc.c:152
 
    #3 0x55fee6a62c14 in THD::init_for_queries() /data/src/10.4/sql/sql_class.cc:1388
 
    #4 0x55fee6f762ec in prepare_new_connection_state(THD*) /data/src/10.4/sql/sql_connect.cc:1254
 
    #5 0x55fee6f76a16 in thd_prepare_connection(THD*) /data/src/10.4/sql/sql_connect.cc:1339
 
    #6 0x55fee6f77018 in do_handle_one_connection(CONNECT*) /data/src/10.4/sql/sql_connect.cc:1410
 
    #7 0x55fee6f769d0 in handle_one_connection /data/src/10.4/sql/sql_connect.cc:1324
 
    #8 0x55fee7be3aed in pfs_spawn_thread /data/src/10.4/storage/perfschema/pfs.cc:1869
 
    #9 0x7ff7b1087fd3 in start_thread nptl/pthread_create.c:442
 
 
 
Thread T5 created by T0 here:
 
    #0 0x7ff7b1649726 in __interceptor_pthread_create ../../../../src/libsanitizer/asan/asan_interceptors.cpp:207
 
    #1 0x55fee7be3eda in spawn_thread_v1 /data/src/10.4/storage/perfschema/pfs.cc:1919
 
    #2 0x55fee6883f28 in inline_mysql_thread_create /data/src/10.4/include/mysql/psi/mysql_thread.h:1275
 
    #3 0x55fee689b62e in create_thread_to_handle_connection(CONNECT*) /data/src/10.4/sql/mysqld.cc:6287
 
    #4 0x55fee689bd79 in create_new_thread(CONNECT*) /data/src/10.4/sql/mysqld.cc:6357
 
    #5 0x55fee689c247 in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /data/src/10.4/sql/mysqld.cc:6455
 
    #6 0x55fee689d0f3 in handle_connections_sockets() /data/src/10.4/sql/mysqld.cc:6613
 
    #7 0x55fee689ad91 in mysqld_main(int, char**) /data/src/10.4/sql/mysqld.cc:5945
 
    #8 0x55fee68820b8 in main /data/src/10.4/sql/main.cc:25
 
    #9 0x7ff7b1026189 in __libc_start_call_main ../sysdeps/nptl/libc_start_call_main.h:58
 
 
 
SUMMARY: AddressSanitizer: use-after-poison /data/src/10.4/sql/sql_list.h:159 in base_list::empty()
 
Shadow bytes around the buggy address:
 
  0x0c5680004730: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c5680004740: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c5680004750: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c5680004760: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c5680004770: 00 00 00 00 00 00 00 00 00 00 00 00 00 f7 00 00
 
=>0x0c5680004780: f7 00 00 f7 00 00[f7]00 00 f7 00 00 f7 00 00 f7
 
  0x0c5680004790: 00 00 f7 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c56800047a0: 00 00 00 00 00 f7 00 00 00 00 00 f7 00 00 00 f7
 
  0x0c56800047b0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c56800047c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c56800047d0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
  Left alloca redzone:     ca
 
  Right alloca redzone:    cb
 
==1903678==ABORTING

Variations of the stack trace

#4  0x00005588a429b75e in base_list_iterator::next (this=0x7f69f9a77a80) at /data/src/10.4/sql/sql_list.h:431
 
#5  0x00005588a4374edd in List_iterator<TABLE_LIST>::operator++ (this=0x7f69f9a77a80) at /data/src/10.4/sql/sql_list.h:596
 
#6  0x00005588a445f501 in st_select_lex::update_used_tables (this=0x62b00008ff80) at /data/src/10.4/sql/sql_lex.cc:4572
 
#7  0x00005588a49976a8 in convert_join_subqueries_to_semijoins (join=0x62b000062f80) at /data/src/10.4/sql/opt_subselect.cc:1132


#4  0x000055644fe575ef in is_eliminated_table (eliminated_tables=93889384035904, tbl=0x62b000063b68) at /data/src/10.4/sql/sql_select.cc:27786
 
#5  0x000055644fc6f5b4 in st_select_lex::update_used_tables (this=0x62b00008ffc8) at /data/src/10.4/sql/sql_lex.cc:4605
 
#6  0x00005564501a76a8 in convert_join_subqueries_to_semijoins (join=0x62b000062fa0) at /data/src/10.4/sql/opt_subselect.cc:1132



 Comments   
Comment by Elena Stepanova [ 2023-07-09 ]

Test case with inet4 instead of GIS

# Remaining options: --mysqld=--innodb
 
# Basedir: /data/bld/10.11-asan
 
# Search pattern(s): (?^s:JOIN::transform_in_predicates_into_in_subq)
 
 
 
CREATE DATABASE IF NOT EXISTS simple_db;
 
CREATE TABLE simple_db.A (pk INTEGER AUTO_INCREMENT,
 
col_int_key INTEGER,
 
col_varchar_key VARCHAR(1),
 
col_varchar_nokey VARCHAR(1),
 
PRIMARY KEY (pk ASC),
 
KEY (col_varchar_key, col_int_key)
 
);
 
CREATE TABLE simple_db.B (pk INTEGER AUTO_INCREMENT,
 
col_int_key INTEGER,
 
col_varchar_key VARCHAR(1),
 
col_varchar_nokey VARCHAR(1),
 
PRIMARY KEY (pk DESC),
 
KEY (col_varchar_key, col_int_key ASC)
 
);
 
CREATE TABLE simple_db.C (pk INTEGER AUTO_INCREMENT,
 
col_int_nokey INTEGER,
 
col_int_key INTEGER,
 
col_datetime_key DATETIME,
 
col_varchar_key VARCHAR(1),
 
PRIMARY KEY (pk),
 
KEY (col_varchar_key DESC, col_int_key ASC)
 
);
 
CREATE TABLE simple_db.CC (pk INTEGER AUTO_INCREMENT,
 
col_int_nokey INTEGER,
 
col_int_key INTEGER,
 
col_varchar_key VARCHAR(1),
 
col_varchar_nokey VARCHAR(1),
 
PRIMARY KEY (pk ASC),
 
KEY (col_varchar_key ASC, col_int_key)
 
) AUTO_INCREMENT=10;
 
ALTER IGNORE TABLE simple_db.C CHANGE IF EXISTS col_int_nokey col_int_nokey INET4 NULL DEFAULT '93.37.124.71';
 
USE simple_db;
 
 
 
PREPARE stmt_ExecuteAsPreparedTwice_13061 FROM ' SELECT SQL_SMALL_RESULT alias1.col_datetime_key AS field1, alias1.col_datetime_key AS field2, alias1.pk AS field3 FROM (C AS alias1, (SELECT SQ1_alias2.* FROM (B AS SQ1_alias1 STRAIGHT_JOIN CC AS SQ1_alias2 ON (SQ1_alias2.col_int_nokey = SQ1_alias1.col_int_key)) WHERE (SQ1_alias2.col_int_key < SQ1_alias2.col_int_nokey AND SQ1_alias1.col_int_key IN (SELECT C_SQ1_alias1.pk AS C_SQ1_field1 FROM (CC AS C_SQ1_alias1 JOIN ((CC AS C_SQ1_alias2 STRAIGHT_JOIN CC AS C_SQ1_alias3 ON (C_SQ1_alias3.col_varchar_nokey = C_SQ1_alias2.col_varchar_key))) ON (C_SQ1_alias3.col_varchar_key = C_SQ1_alias2.col_varchar_key)) WHERE C_SQ1_alias2.col_varchar_key > SQ1_alias1.col_varchar_nokey))) AS alias2, C AS alias3) WHERE (alias1.col_int_key, alias3.col_int_nokey) IN (SELECT SQ2_alias1.col_int_nokey AS SQ2_field1, SQ2_alias1.col_int_key AS SQ2_field2 FROM (C AS SQ2_alias1))';
 
 
 
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
 
EXECUTE stmt_ExecuteAsPreparedTwice_13061;
 
EXECUTE stmt_ExecuteAsPreparedTwice_13061;

Comment by Elena Stepanova [ 2023-11-26 ]

Too similar to MDEV-32684

Generated at Thu Feb 08 10:24:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.