[MDEV-27292] Assertion `!prebuilt->index->is_primary()' failed upon SELECT with subqueries and rowid_filter Created: 2021-12-17  Updated: 2023-09-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5, 10.6, 10.7
Fix Version/s: 10.5, 10.6

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

Issue Links:
Relates
relates to MDEV-27366 SIGSEGV in handler_index_cond_check o... In Review

 Description   

With the provided test case, the failure is reproducible on 10.5+, including older versions of 10.5, but not reproducible on 10.4 – neither now nor before MDEV-21446 fix.

innodb_stats_persistent doesn't seem to make a difference, it fails either way, but I'm keeping it for (hopefully) better stability.

--source include/have_innodb.inc
 
 
 
SET @stats.save= @@innodb_stats_persistent;
 
SET global innodb_stats_persistent=on;
 
 
 
CREATE TABLE t1 (
 
  id int,
 
  i int,
 
  c char(32),
 
  PRIMARY KEY (id),
 
  UNIQUE KEY (id),
 
  KEY (c)
 
) ENGINE=InnoDB
 
;
 
INSERT INTO t1 values (1,8908081,'London'),(2,12506468,'Moscow'),(3,542599,'Edinburgh'),(4,NULL,'Zagreb');
 
 
 
ANALYZE TABLE t1;
 
 
 
CREATE TABLE t2 (f INT);
 
INSERT INTO t2 VALUES (100000),(200000);
 
 
 
SELECT * FROM t1 AS outer_t1 WHERE i NOT IN ( SELECT id FROM t1 WHERE i IN ( SELECT f FROM t2 WHERE f <= outer_t1.i ) AND c > 'U' );
 
 
 
# Cleanup
 
DROP TABLE t1, t2;
 
SET global innodb_stats_persistent= @stats.save;

10.5 2776635c

 
mariadbd: /data/src/10.5/storage/innobase/row/row0sel.cc:4029: check_result_t row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const rec_offs*): Assertion `!prebuilt->index->is_primary()' failed.
 
211217  2:08:56 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f1fb3640f36 in __GI___assert_fail (assertion=0x56128fb47790 "!prebuilt->index->is_primary()", file=0x56128fb45b50 "/data/src/10.5/storage/innobase/row/row0sel.cc", line=4029, function=0x56128fb47730 "check_result_t row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const rec_offs*)") at assert.c:101
 
#8  0x000056128f3d5523 in row_search_idx_cond_check (mysql_rec=0x7f1f7806b478 "\371\001", prebuilt=0x7f1f7806c5a8, rec=0x7f1fad03807e "\200", offsets=0x7f1fac89a1e0) at /data/src/10.5/storage/innobase/row/row0sel.cc:4029
 
#9  0x000056128f3d9793 in row_search_mvcc (buf=0x7f1f7806b478 "\371\001", mode=PAGE_CUR_G, prebuilt=0x7f1f7806c5a8, match_mode=0, direction=0) at /data/src/10.5/storage/innobase/row/row0sel.cc:5324
 
#10 0x000056128f1d2134 in ha_innobase::index_read (this=0x7f1f7806b8e0, buf=0x7f1f7806b478 "\371\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.5/storage/innobase/handler/ha_innodb.cc:8838
 
#11 0x000056128f1d315c in ha_innobase::index_first (this=0x7f1f7806b8e0, buf=0x7f1f7806b478 "\371\001") at /data/src/10.5/storage/innobase/handler/ha_innodb.cc:9199
 
#12 0x000056128f1d334c in ha_innobase::rnd_next (this=0x7f1f7806b8e0, buf=0x7f1f7806b478 "\371\001") at /data/src/10.5/storage/innobase/handler/ha_innodb.cc:9292
 
#13 0x000056128ed8de2c in handler::ha_rnd_next (this=0x7f1f7806b8e0, buf=0x7f1f7806b478 "\371\001") at /data/src/10.5/sql/handler.cc:3080
 
#14 0x000056128ef565dd in rr_sequential (info=0x7f1f78071128) at /data/src/10.5/sql/records.cc:519
 
#15 0x000056128e9595af in READ_RECORD::read_record (this=0x7f1f78071128) at /data/src/10.5/sql/records.h:80
 
#16 0x000056128ea9bd35 in read_first_record_seq (tab=0x7f1f78071060) at /data/src/10.5/sql/sql_select.cc:21775
 
#17 0x000056128ea99b39 in sub_select (join=0x7f1f780781f0, join_tab=0x7f1f78071060, end_of_records=false) at /data/src/10.5/sql/sql_select.cc:20877
 
#18 0x000056128ea99032 in do_select (join=0x7f1f780781f0, procedure=0x0) at /data/src/10.5/sql/sql_select.cc:20414
 
#19 0x000056128ea6c3b1 in JOIN::exec_inner (this=0x7f1f780781f0) at /data/src/10.5/sql/sql_select.cc:4516
 
#20 0x000056128ea6b4af in JOIN::exec (this=0x7f1f780781f0) at /data/src/10.5/sql/sql_select.cc:4296
 
#21 0x000056128ee84d8b in subselect_single_select_engine::exec (this=0x7f1f78019d78) at /data/src/10.5/sql/item_subselect.cc:4075
 
#22 0x000056128ee77847 in Item_subselect::exec (this=0x7f1f78019b30) at /data/src/10.5/sql/item_subselect.cc:836
 
#23 0x000056128ee77f2d in Item_in_subselect::exec (this=0x7f1f78019b30) at /data/src/10.5/sql/item_subselect.cc:1016
 
#24 0x000056128ee7be98 in Item_in_subselect::val_bool (this=0x7f1f78019b30) at /data/src/10.5/sql/item_subselect.cc:1929
 
#25 0x000056128e8e1869 in Item::val_bool_result (this=0x7f1f78019b30) at /data/src/10.5/sql/item.h:1575
 
#26 0x000056128edd8fe1 in Item_in_optimizer::val_int (this=0x7f1f78078e98) at /data/src/10.5/sql/item_cmpfunc.cc:1638
 
#27 0x000056128e8e17d1 in Item::val_int_result (this=0x7f1f78078e98) at /data/src/10.5/sql/item.h:1571
 
#28 0x000056128edc6d51 in Item_cache_int::cache_value (this=0x7f1f7807e978) at /data/src/10.5/sql/item.cc:10053
 
#29 0x000056128edcfae0 in Item_cache_wrapper::cache (this=0x7f1f7807e8b0) at /data/src/10.5/sql/item.cc:8839
 
#30 0x000056128edc3b91 in Item_cache_wrapper::val_bool (this=0x7f1f7807e8b0) at /data/src/10.5/sql/item.cc:9025
 
#31 0x000056128edd3dd4 in Item_func_not::val_int (this=0x7f1f78019db8) at /data/src/10.5/sql/item_cmpfunc.cc:202
 
#32 0x000056128ea99fc1 in evaluate_join_record (join=0x7f1f7801a7e0, join_tab=0x7f1f7807a4a0, error=0) at /data/src/10.5/sql/sql_select.cc:20978
 
#33 0x000056128ea99d5e in sub_select (join=0x7f1f7801a7e0, join_tab=0x7f1f7807a4a0, end_of_records=false) at /data/src/10.5/sql/sql_select.cc:20919
 
#34 0x000056128ea99032 in do_select (join=0x7f1f7801a7e0, procedure=0x0) at /data/src/10.5/sql/sql_select.cc:20414
 
#35 0x000056128ea6c3b1 in JOIN::exec_inner (this=0x7f1f7801a7e0) at /data/src/10.5/sql/sql_select.cc:4516
 
#36 0x000056128ea6b4af in JOIN::exec (this=0x7f1f7801a7e0) at /data/src/10.5/sql/sql_select.cc:4296
 
#37 0x000056128ea6cd34 in mysql_select (thd=0x7f1f78000db8, tables=0x7f1f78015a90, fields=..., conds=0x7f1f78019db8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1f7801a7b8, unit=0x7f1f78004f60, select_lex=0x7f1f78015468) at /data/src/10.5/sql/sql_select.cc:4773
 
#38 0x000056128ea5c587 in handle_select (thd=0x7f1f78000db8, lex=0x7f1f78004e98, result=0x7f1f7801a7b8, setup_tables_done_option=0) at /data/src/10.5/sql/sql_select.cc:444
 
#39 0x000056128ea1e257 in execute_sqlcom_select (thd=0x7f1f78000db8, all_tables=0x7f1f78015a90) at /data/src/10.5/sql/sql_parse.cc:6314
 
#40 0x000056128ea1553c in mysql_execute_command (thd=0x7f1f78000db8) at /data/src/10.5/sql/sql_parse.cc:4005
 
#41 0x000056128ea23106 in mysql_parse (thd=0x7f1f78000db8, rawbuf=0x7f1f78015300 "SELECT * FROM t1 AS outer_t1 WHERE i NOT IN ( SELECT id FROM t1 WHERE i IN ( SELECT f FROM t2 WHERE f <= outer_t1.i ) AND c > 'U' )", length=131, parser_state=0x7f1fac89c490, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:8100
 
#42 0x000056128ea0f073 in dispatch_command (command=COM_QUERY, thd=0x7f1f78000db8, packet=0x7f1f7800b5b9 "", packet_length=131, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1891
 
#43 0x000056128ea0d865 in do_command (thd=0x7f1f78000db8) at /data/src/10.5/sql/sql_parse.cc:1370
 
#44 0x000056128ebbf3f0 in do_handle_one_connection (connect=0x56129307e1a8, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1418
 
#45 0x000056128ebbf0a3 in handle_one_connection (arg=0x561292f84d38) at /data/src/10.5/sql/sql_connect.cc:1312
 
#46 0x000056128f0e603f in pfs_spawn_thread (arg=0x56129307db78) at /data/src/10.5/storage/perfschema/pfs.cc:2201
 
#47 0x00007f1fb3b59609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#48 0x00007f1fb372c293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

EXPLAIN

 
EXPLAIN EXTENDED SELECT * FROM t1 AS outer_t1 WHERE i NOT IN ( SELECT id FROM t1 WHERE i IN ( SELECT f FROM t2 WHERE f <= outer_t1.i ) AND c > 'U' );
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	outer_t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 
2	DEPENDENT SUBQUERY	t1	eq_ref|filter	PRIMARY,id,c	id|c	4|33	func	1 (25%)	25.00	Using where; Full scan on NULL key; Using rowid filter
 
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 
Warnings:
 
Note	1276	Field or reference 'test.outer_t1.i' of SELECT #3 was resolved in SELECT #1
 
Note	1003	/* select#1 */ select `test`.`outer_t1`.`id` AS `id`,`test`.`outer_t1`.`i` AS `i`,`test`.`outer_t1`.`c` AS `c` from `test`.`t1` `outer_t1` where !<expr_cache><`test`.`outer_t1`.`i`>(<in_optimizer>(`test`.`outer_t1`.`i`,<exists>(/* select#2 */ select `test`.`t1`.`id` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`c` > 'U' and `test`.`t2`.`f` <= `test`.`outer_t1`.`i` and trigcond(<cache>(`test`.`outer_t1`.`i`) = `test`.`t1`.`id`) and `test`.`t2`.`f` = `test`.`t1`.`i`)))



 Comments   
Comment by Igor Babaev [ 2021-12-22 ]

This bug is actually a duplicate of the bug MDEV-22846 and EXPLAIN for the query added in one of the comments that reproduced the problem of MDEV-22846 clearly shows this:

EXPLAIN EXTENDED
 
SELECT 1 FROM t1 
WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 
WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
 
id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
1      PRIMARY t1      ALL     NULL    NULL    NULL    NULL    15      100.00  Using where
 
2      DEPENDENT SUBQUERY      t2      ref|filter      c1,i1   c1|i1   3|5     func    6 (33%) 33.33   Using where; Full scan on NULL key; Using rowid filter
 
2      DEPENDENT SUBQUERY      a1      ALL     NULL    NULL    NULL    NULL    15

In fact after having applied the fix of MDEV-22846 to 10.5 I get:

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 AS outer_t1 WHERE i NOT IN ( SELECT id FROM t1 WHERE i IN ( SELECT f FROM t2 WHERE f <= outer_t1.i ) AND c > 'U' );
 
+------+--------------------+----------+---------------+---------------+------+---------+------+---------+----------+-----------------------------------------------------------------+
 
| id   | select_type        | table    | type          | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                                           |
 
+------+--------------------+----------+---------------+---------------+------+---------+------+---------+----------+-----------------------------------------------------------------+
 
|    1 | PRIMARY            | outer_t1 | ALL           | NULL          | NULL | NULL    | NULL | 4       |   100.00 | Using where                                                     |
 
|    2 | DEPENDENT SUBQUERY | t1       | eq_ref|filter | PRIMARY,id,c  | id|c | 4|33    | func | 1 (25%) |    25.00 | Using where; Full scan on NULL key; Using rowid filter          |
 
|    2 | DEPENDENT SUBQUERY | t2       | ALL           | NULL          | NULL | NULL    | NULL | 2       |   100.00 | Using where; FirstMatch(t1); Using join buffer (flat, BNL join) |
 
+------+--------------------+----------+---------------+---------------+------+---------+------+---------+----------+-----------------------------------------------------------------+
 
3 rows in set, 2 warnings (0.004 sec)
 
 
 
MariaDB [test]> SELECT * FROM t1 AS outer_t1 WHERE i NOT IN ( SELECT id FROM t1 WHERE i IN ( SELECT f FROM t2 WHERE f <= outer_t1.i ) AND c > 'U' );
 
+----+----------+-----------+
 
| id | i        | c         |
 
+----+----------+-----------+
 
|  1 |  8908081 | London    |
 
|  2 | 12506468 | Moscow    |
 
|  3 |   542599 | Edinburgh |
 
|  4 |     NULL | Zagreb    |
 
+----+----------+-----------+
 
4 rows in set (0.005 sec)

The crash with the query reported in MDEV-27292 indeed cannot be reproduced in 10.4. However this is only because in 10.5 Monty made the cost of index only scans cheaper and thus made usage of rowid filters more favorable. In 10.4 the reported query does not use rowid filters at all.

Generated at Thu Feb 08 09:51:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.