[MDEV-31440] choose_best_splitting: crash on update query using correlated subquery after minor update Created: 2023-06-09  Updated: 2024-01-12

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.9.6, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 10.9.7
Fix Version/s: 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Critical
Reporter: VAROQUI Stephane Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 3
Labels: regression
Environment:

Linux Debian jemalloc ldd (Debian GLIBC 2.31-13+deb11u6) 2.31

Attachments: Zip Archive mycrashingquery.zip    
Issue Links:
Duplicate
is duplicated by MDEV-32571 A specific query crashes MariaDB in c... Closed
is duplicated by MDEV-32624 Mariadb crashes with query Closed
is duplicated by MDEV-32643 "[ERROR] mysqld got signal 11" with o... Closed
Problem/Incident
is caused by MDEV-26301 Split optimization refills temporary ... Closed
Relates
relates to MDEV-31818 Server crashes in choose_best_splitting Open
relates to MDEV-32064 Crash when searching for the best spl... Closed
relates to MDEV-32602 Server crash after query Closed

 Description   

Following mineur update from 10.9.5 to 10.9.7 the attached query will crash on the updated replica but also when executed in command line .

Server version: 10.9.7-MariaDB-1:10.9.7+maria~deb11-log source revision: 33fd519ca7318cd85bee56e8f79df4608ead194e
 
key_buffer_size=268435456
 
read_buffer_size=1048576
 
max_used_connections=1
 
max_threads=65636
 
thread_count=12
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 201930294 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
Thread pointer: 0x7f74f0c071d8
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f75782fc4a8 thread_stack 0x40000
 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x559f942c648e]
 
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x559f93d9ff15]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f8de2d9e140]
 
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x559f93d1d784]
 
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x559f93b9246f]
 
/usr/sbin/mariadbd(+0x84c21f)[0x559f93b9621f]
 
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
 
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
 
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x559f93b97034]
 
/usr/sbin/mariadbd(+0x877760)[0x559f93bc1760]
 
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12e4)[0x559f93bc6064]
 
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x559f93bc6670]
 
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x559f93bc675a]
 
/usr/sbin/mariadbd(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x16d)[0x559f93c2d2dd]
 
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x45a0)[0x559f93b4d080]
 
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x559f93b4e917]
 
/usr/sbin/mariadbd(_ZN15Query_log_event14do_apply_eventEP14rpl_group_infoPKcj+0xb26)[0x559f93ee5636]
 
/usr/sbin/mariadbd(_ZN9Log_event11apply_eventEP14rpl_group_info+0x74)[0x559f93ed8b74]
 
/usr/sbin/mariadbd(_Z39apply_event_and_update_pos_for_parallelP9Log_eventP3THDP14rpl_group_info+0x173)[0x559f93a8aee3]
 
/usr/sbin/mariadbd(+0x994f4c)[0x559f93cdef4c]
 
/usr/sbin/mariadbd(handle_rpl_parallel_thread+0xdc5)[0x559f93ce3b05]
 
/usr/sbin/mariadbd(+0xc86282)[0x559f93fd0282]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f8de2d92ea7]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f8de2999a2f]


Create Table: CREATE TABLE `lastname_page` (
 
  `obituary_id` int(11) unsigned NOT NULL,
 
  `lastname_uri` varchar(255) NOT NULL,
 
  `lastname_page` smallint(5) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`obituary_id`,`lastname_uri`),
 
  KEY `lastname_uri_page_id` (`lastname_uri`,`lastname_page`,`obituary_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
 
 
 
CREATE TABLE `data_entity` (
 
  `uri` varchar(255) NOT NULL,
 
  `type_uri` varchar(255) NOT NULL,
 
  `latitude` float DEFAULT NULL,
 
  `longitude` float DEFAULT NULL,
 
  `status` tinyint(4) DEFAULT 1,
 
  PRIMARY KEY (`uri`),
 
  KEY `data_entity_type_uri_idx` (`type_uri`),
 
  KEY `data_entity_latitude_longitude_idx` (`latitude`,`longitude`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci



 Comments   
Comment by VAROQUI Stephane [ 2023-06-09 ]

Tested the crash was introduced in 10.9.6

Comment by VAROQUI Stephane [ 2023-06-09 ]

Attaching show variables and query

Comment by VAROQUI Stephane [ 2023-06-09 ]

The explain of the query make the server crash confirming the issue is in optimizer

Comment by VAROQUI Stephane [ 2023-06-09 ]

Can confirm SET global optimizer_switch='semijoin=off'; is a valid workaround

Comment by Daniel Black [ 2023-06-13 ]

The SQL didn't include the table structure for data_entity.

Comment by Alice Sherepa [ 2023-08-02 ]

stephane@skysql.com Could you please provide the way to repeat the crash (test case or dump of this 2 tables + .cnf - show variables were not attached). I tried to repeat with some mock data, but without luck.

Comment by VAROQUI Stephane [ 2023-08-30 ]

Alice we may have an other case of siimliar semi join issue but on 10.6.14 the sysadmin is more willing to spend time to reproduce base on a snapshot of a VM will keep you posted when we have reproductible the case

Comment by Sergei Golubchik [ 2023-10-02 ]

stephane@skysql.com, there was no feedback for a month. the issue will auto-close in a month after no activity (this my comment counts as activity too). But don't worry you can leave feedback whenever you have it, we'll reopen the issue if it'll be closed.

Comment by Alice Sherepa [ 2023-10-30 ]

test case from MDEV-32624 - reproducible after 10.5 e87440b (MDEV-26301)
Crash is repeatable on 10.5-10.11, but not on 11.0+

--source include/have_innodb.inc
 
 
CREATE TABLE `table1` (
 
`id_product`int(11),
 
`id_stock`varchar(32),
 
`quantity` mediumint(9),
 
PRIMARY KEY (`id_product`,`id_stock`)
 
) engine=innodb;
 
 
CREATE TABLE `table2` (
 
`id_product`int(10) unsigned,
 
`id_shop`int(10) unsigned,
 
PRIMARY KEY (`id_product`,`id_shop`)
 
)  engine=innodb;
 
 
 
 
 
SELECT backups.id_product, quantity_backup, quantity_bl
 
FROM (
 
SELECT id_product, SUM(quantity) as quantity_backup
 
FROM`table1`
 
) backups
 
INNER JOIN (
 
SELECT id_product
 
FROM`table1`
 
GROUP BY id_product
 
) normals ON (normals.id_product = backups.id_product)
 
LEFT JOIN (
 
SELECT quantity as quantity_bl, id_product FROM table1
 
) bl ON (backups.id_product = bl.id_product )
 
WHERE
 
backups.id_product IN (
 
SELECT id_product
 
FROM table2
 
);


Version: '10.5.20-MariaDB-debug-log'  
231030 15:51:48 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.5.20-MariaDB-debug-log source revision: e87440b79ec6f3e3ed2e6639a3239a4d02630b7e
 
 
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x55a1094053ea]
 
sigaction.c:0(__restore_rt)[0x7feeb8d12420]
 
sql/opt_split.cc:1047(st_join_table::choose_best_splitting(unsigned int, unsigned long long, unsigned long long*))[0x55a10924134d]
 
sql/sql_select.cc:7555(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55a108cca97c]
 
sql/sql_select.cc:9869(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd798c]
 
sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]
 
sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]
 
sql/sql_select.cc:9037(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x55a108cd36f7]
 
sql/sql_select.cc:8599(choose_plan(JOIN*, unsigned long long))[0x55a108cd13c3]
 
sql/sql_select.cc:5738(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55a108cbc0e7]
 
sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55a108c98b66]
 
sql/sql_select.cc:1700(JOIN::optimize())[0x55a108c9203f]
 
sql/sql_select.cc:4830(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55a108cb2ea6]
 
sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a108c83d35]
 
sql/sql_parse.cc:6331(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a108be98c9]
 
sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55a108bd863c]
 
sql/sql_parse.cc:8106(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a108bf4bc3]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a108bca819]
 
sql/sql_parse.cc:1375(do_command(THD*))[0x55a108bc718b]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55a109023ea7]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x55a10902380b]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a109cad45a]
 
nptl/pthread_create.c:478(start_thread)[0x7feeb8d06609]
 
 
 
Query (0x62b0000852a8): SELECT backups.id_product, quantity_backup, quantity_bl
 
FROM (
 
SELECT id_product, SUM(quantity) as quantity_backup
 
FROM`table1`
 
) backups
 
INNER JOIN (
 
SELECT id_product
 
FROM`table1`
 
GROUP BY id_product
 
) normals ON (normals.id_product = backups.id_product)
 
LEFT JOIN (
 
SELECT quantity as quantity_bl, id_product FROM table1
 
) bl ON (backups.id_product = bl.id_product )
 
WHERE
 
backups.id_product IN (
 
SELECT id_product
 
FROM table2
 
)

Comment by Alice Sherepa [ 2023-10-30 ]

Test case from MDEV-32602 , crashes on 10.5-11.2, after e87440b commit

CREATE TABLE t1 ( id int NOT NULL, PRIMARY KEY (id));
 
 
 
INSERT INTO t1 VALUES
 
(-65632),(-65622),(-65570),(-65560),(-65553),(-65543),(-65504),(-65494),(-65491),(-65481),(-65442),(-65432),(-65425),(-65415),(-65363),(-65353),(-32864),(-32854),(-32802),(-32792),(-32785),(-32775),(-32736),(-32726),(-32723),(-32713),(-32674),(-32664),(-32657),(-32647),(-32595),(-32585),(-96),(-86),(-34),(-24),(-17),(-7),(32),(42),(45),(55),(94),(104),(111),(121),(173),(183),(2191719),(2191729),(2191781),(2191791),(2191798),(2191808),(2191847),(2191857),(2191860),(2191870),(2191909),(2191919),(2191926),(2191936),(2191988),(2191998),(2224487),(2224497),(2224549),(2224559),(2224566),(2224576),(2224615),(2224625),(2224628),(2224638),(2224677),(2224687),(2224694),(2224704),(2224756),(2224766),(2257255),(2257265),(2257317),(2257327),(2257334),(2257344),(2257383),(2257393),(2257396),(2257406),(2257445),(2257455),(2257462),(2257472),(2257524),(2257534);
 
 
 
 
 
INSERT INTO t1 SELECT id  
FROM t1 
NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
 
WHERE id IN ( SELECT id LIKE 'x' FROM t1 ) ;
 
 
 
DROP TABLE t1;


Version: '10.5.23-MariaDB-debug-log'  
231028 13:29:08 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.5.23-MariaDB-debug-log source revision: b5e43a1d3539c7254c298dc9f63a261281345d59
 
 
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x5615c8385d90]
 
sigaction.c:0(__restore_rt)[0x7fc23cb66420]
 
sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x5615c81c7a1e]
 
sql/sql_select.cc:7570(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x5615c7c4ccce]
 
sql/sql_select.cc:9886(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c59d6a]
 
sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]
 
sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]
 
sql/sql_select.cc:9054(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5615c7c55ad5]
 
sql/sql_select.cc:8616(choose_plan(JOIN*, unsigned long long))[0x5615c7c537a1]
 
sql/sql_select.cc:5753(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5615c7c3e42f]
 
sql/sql_select.cc:2363(JOIN::optimize_inner())[0x5615c7c1b02c]
 
sql/sql_select.cc:1721(JOIN::optimize())[0x5615c7c14505]
 
sql/sql_select.cc:4845(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5615c7c351cc]
 
sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5615c7c06073]
 
sql/sql_parse.cc:4790(mysql_execute_command(THD*))[0x5615c7b5f885]
 
sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5615c7b76977]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5615c7b4c3a5]
 
sql/sql_parse.cc:1375(do_command(THD*))[0x5615c7b48d0d]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5615c7fa795b]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x5615c7fa72bf]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5615c8c33aba]
 
nptl/pthread_create.c:478(start_thread)[0x7fc23cb5a609]
 
 
 
Query (0x62b0000852a8): INSERT INTO t1 SELECT id  
FROM t1 
NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
 
WHERE id IN ( SELECT id LIKE 'x' FROM t1 )

Comment by Alice Sherepa [ 2023-12-15 ]

not reproducible on current 10.5 2c60d43d7df134c3875b3760a5d54a-11.3, after 6cfd2ba397 commit Merge branch '10.4' into 10.5
but maybe not fixed - possible that query plan changed. Currently:

MariaDB [test]> explain extended SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 );
 
+------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+
 
| id   | select_type  | table       | type  | possible_keys | key     | key_len | ref                    | rows | filtered | Extra                                                     |
 
+------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+
 
|    1 | PRIMARY      | <subquery5> | ALL   | distinct_key  | NULL    | NULL    | NULL                   | 1    |   100.00 |                                                           |
 
|    1 | PRIMARY      | <derived2>  | ref   | key1          | key1    | 5       | test.table2.id_product | 2    |   100.00 | Using where                                               |
 
|    1 | PRIMARY      | table1      | ALL   | PRIMARY       | NULL    | NULL    | NULL                   | 1    |   100.00 | Using where; Using join buffer (flat, BNL join)           |
 
|    1 | PRIMARY      | <derived3>  | ref   | key0          | key0    | 4       | backups.id_product     | 2    |   100.00 |                                                           |
 
|    5 | MATERIALIZED | table2      | index | PRIMARY       | PRIMARY | 8       | NULL                   | 1    |   100.00 | Using index                                               |
 
|    3 | DERIVED      | table1      | range | PRIMARY       | PRIMARY | 4       | NULL                   | 2    |   100.00 | Using index for group-by; Using temporary; Using filesort |
 
|    2 | DERIVED      | table1      | ALL   | NULL          | NULL    | NULL    | NULL                   | 1    |   100.00 |                                                           |
 
+------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+
 
7 rows in set, 1 warning (0,001 sec)

I could not compare with the previous one - EXPLAIN crashes also.

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