[MDEV-20056] Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check Created: 2019-07-12  Updated: 2021-06-29  Resolved: 2019-11-27

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4.11

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-20851 optimizer_switch 'mrr=on' causes fail... Closed
is duplicated by MDEV-21040 InnoDB: fulltext search with ` in(...... Closed
Problem/Incident
causes MDEV-20252 Specific SQL query consistently crash... Closed
Relates
relates to MDEV-19919 Assertion `!prebuilt->index->is_prima... Closed
relates to MDEV-21356 ERROR 1032 Can't find record when run... Closed
relates to MDEV-16188 Use in-memory PK filters built from r... Closed
relates to MDEV-19361 Assertion `marked_for_read()' failed ... Closed
relates to MDEV-19820 Wrong result with multiple single col... Closed
relates to MDEV-21446 Assertion `!prebuilt->index->is_prima... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (ch varchar(1),id int,id2 int) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES ('l',3,2),('e',NULL,NULL),('r',7,3),('h',NULL,2),(NULL,4,4),('c',4,NULL),('k',NULL,NULL),('h',NULL,NULL),('b',9,NULL),('f',6,NULL);
 
 
 
CREATE TABLE t2 (pk int NOT NULL , col_date_key date , ch2 varchar(1) , id2 int , PRIMARY KEY (pk), KEY (col_date_key), KEY (ch2), KEY (id2)) ENGINE=InnoDB;
 
INSERT INTO t2 VALUES  (1,'2034-04-21','g',9),(2,'2006-09-08','y',1),(3,NULL,'h',2),(4,'1987-03-02','s',2),(5,'2019-07-02','u',NULL),(6,'2012-12-18','z',1),(7,NULL,'w',4),(8,'2005-03-10','o',8),(9,'1987-02-12','d',4);
 
 
 
CREATE TABLE t3 ( id int) ENGINE=InnoDB;
 
INSERT INTO t3 VALUES (6);
 
 
 
SELECT 1 FROM t3 
WHERE EXISTS(SELECT 1 FROM t1 WHERE t3.id IN (
 
  SELECT bt1.id FROM (t2 JOIN t1 AS bt1 ON bt1.ch = t2.ch2 and bt1.id = t2.pk) WHERE t2.ch2 <= 'g') 
OR t1.id2 = t1.id);

10.4 a0230bc76d78202178f43

 
 
 
#4  0x00007f9c1f600428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007f9c1f60202a in __GI_abort () at abort.c:89
 
#6  0x00007f9c1f5f8bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55c9649735a0 "!prebuilt->index->is_primary()", file=file@entry=0x55c9649724d0 "/10.4/storage/innobase/row/row0sel.cc", line=line@entry=3906, function=function@entry=0x55c9649766c0 <row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long const*)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*)") at assert.c:92
 
#7  0x00007f9c1f5f8c82 in __GI___assert_fail (assertion=0x55c9649735a0 "!prebuilt->index->is_primary()", file=0x55c9649724d0 "/10.4/storage/innobase/row/row0sel.cc", line=3906, function=0x55c9649766c0 <row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long const*)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*)") at assert.c:101
 
#8  0x000055c96431101a in row_search_idx_cond_check (mysql_rec=0x7f9bc8068900 "\375\001", prebuilt=0x7f9bc80727f8, rec=0x7f9c13d4007f "\200", offsets=0x7f9c18378f80) at /10.4/storage/innobase/row/row0sel.cc:3906
 
#9  0x000055c964314a24 in row_search_mvcc (buf=0x7f9bc8068900 "\375\001", mode=PAGE_CUR_G, prebuilt=0x7f9bc80727f8, match_mode=0, direction=0) at /10.4/storage/innobase/row/row0sel.cc:5220
 
#10 0x000055c9641707df in ha_innobase::index_read (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /10.4/storage/innobase/handler/ha_innodb.cc:9337
 
#11 0x000055c964171748 in ha_innobase::index_first (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001") at /10.4/storage/innobase/handler/ha_innodb.cc:9710
 
#12 0x000055c964171920 in ha_innobase::rnd_next (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001") at /10.4/storage/innobase/handler/ha_innodb.cc:9803
 
#13 0x000055c963e01d83 in handler::ha_rnd_next (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001") at /10.4/sql/handler.cc:2834
 
#14 0x000055c963f968ec in rr_sequential (info=0x7f9bc81af090) at /10.4/sql/records.cc:477
 
#15 0x000055c963a14c13 in READ_RECORD::read_record (this=0x7f9bc81af090) at /10.4/sql/records.h:69
 
#16 0x000055c963b3b2be in join_init_read_record (tab=0x7f9bc81aefc8) at /10.4/sql/sql_select.cc:21055
 
#17 0x000055c963c80004 in JOIN_TAB_SCAN::open (this=0x7f9bc81b0a50) at /10.4/sql/sql_join_cache.cc:3349
 
#18 0x000055c963c7e44d in JOIN_CACHE::join_matching_records (this=0x7f9bc81b0920, skip_last=false) at /10.4/sql/sql_join_cache.cc:2252
 
#19 0x000055c963c7df7e in JOIN_CACHE::join_records (this=0x7f9bc81b0920, skip_last=false) at /10.4/sql/sql_join_cache.cc:2088
 
#20 0x000055c963b38b71 in sub_select_cache (join=0x7f9bc81a47c0, join_tab=0x7f9bc81aefc8, end_of_records=true) at /10.4/sql/sql_select.cc:19892
 
#21 0x000055c963b38d7c in sub_select (join=0x7f9bc81a47c0, join_tab=0x7f9bc81aec20, end_of_records=true) at /10.4/sql/sql_select.cc:20063
 
#22 0x000055c963b38546 in do_select (join=0x7f9bc81a47c0, procedure=0x0) at /10.4/sql/sql_select.cc:19654
 
#23 0x000055c963b0e7b3 in JOIN::exec_inner (this=0x7f9bc81a47c0) at /10.4/sql/sql_select.cc:4391
 
#24 0x000055c963b0d960 in JOIN::exec (this=0x7f9bc81a47c0) at /10.4/sql/sql_select.cc:4173
 
#25 0x000055c963ee0e71 in subselect_hash_sj_engine::exec (this=0x7f9bc81aa9c8) at /10.4/sql/item_subselect.cc:5483
 
#26 0x000055c963ed246e in Item_subselect::exec (this=0x7f9bc8017d50) at /10.4/sql/item_subselect.cc:745
 
#27 0x000055c963ed2ac0 in Item_in_subselect::exec (this=0x7f9bc8017d50) at /10.4/sql/item_subselect.cc:925
 
#28 0x000055c963ed596a in Item_in_subselect::val_bool (this=0x7f9bc8017d50) at /10.4/sql/item_subselect.cc:1796
 
#29 0x000055c9639a6b41 in Item::val_bool_result (this=0x7f9bc8017d50) at /10.4/sql/item.h:1521
 
#30 0x000055c963e42f08 in Item_in_optimizer::val_int (this=0x7f9bc81a4e70) at /10.4/sql/item_cmpfunc.cc:1628
 
#31 0x000055c9639a6ab5 in Item::val_int_result (this=0x7f9bc81a4e70) at /10.4/sql/item.h:1517
 
#32 0x000055c963e31ead in Item_cache_int::cache_value (this=0x7f9bc81b16b0) at /10.4/sql/item.cc:9712
 
#33 0x000055c963e3a10c in Item_cache_wrapper::cache (this=0x7f9bc81b15f0) at /10.4/sql/item.cc:8531
 
#34 0x000055c963e2f420 in Item_cache_wrapper::val_bool (this=0x7f9bc81b15f0) at /10.4/sql/item.cc:8717
 
#35 0x000055c963e4f0a3 in Item_cond_or::val_int (this=0x7f9bc81a78c8) at /10.4/sql/item_cmpfunc.cc:5300
 
#36 0x000055c963b39386 in evaluate_join_record (join=0x7f9bc81a4070, join_tab=0x7f9bc81a7178, error=0) at /10.4/sql/sql_select.cc:20209
 
#37 0x000055c963b38fb0 in sub_select (join=0x7f9bc81a4070, join_tab=0x7f9bc81a7178, end_of_records=false) at /10.4/sql/sql_select.cc:20114
 
#38 0x000055c963b384e0 in do_select (join=0x7f9bc81a4070, procedure=0x0) at /10.4/sql/sql_select.cc:19652
 
#39 0x000055c963b0e7b3 in JOIN::exec_inner (this=0x7f9bc81a4070) at /10.4/sql/sql_select.cc:4391
 
#40 0x000055c963b0d960 in JOIN::exec (this=0x7f9bc81a4070) at /10.4/sql/sql_select.cc:4173
 
#41 0x000055c963eddd0a in subselect_single_select_engine::exec (this=0x7f9bc8018ed0) at /10.4/sql/item_subselect.cc:3927
 
#42 0x000055c963ed246e in Item_subselect::exec (this=0x7f9bc8018d30) at /10.4/sql/item_subselect.cc:745
 
#43 0x000055c963ed5488 in Item_exists_subselect::val_int (this=0x7f9bc8018d30) at /10.4/sql/item_subselect.cc:1644
 
#44 0x000055c963e42c73 in Item_in_optimizer::val_int (this=0x7f9bc81a5190) at /10.4/sql/item_cmpfunc.cc:1546
 
#45 0x000055c963b0e1e3 in JOIN::exec_inner (this=0x7f9bc81a38f8) at /10.4/sql/sql_select.cc:4287
 
#46 0x000055c963b0d960 in JOIN::exec (this=0x7f9bc81a38f8) at /10.4/sql/sql_select.cc:4173
 
#47 0x000055c963b0f005 in mysql_select (thd=0x7f9bc8000b00, tables=0x7f9bc8013820, wild_num=0, fields=..., conds=0x7f9bc8018d30, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f9bc8019020, unit=0x7f9bc8004a30, select_lex=0x7f9bc80132d8) at /10.4/sql/sql_select.cc:4605
 
#48 0x000055c963aff5ce in handle_select (thd=0x7f9bc8000b00, lex=0x7f9bc8004968, result=0x7f9bc8019020, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:425
 
#49 0x000055c963ac8f0a in execute_sqlcom_select (thd=0x7f9bc8000b00, all_tables=0x7f9bc8013820) at /10.4/sql/sql_parse.cc:6340
 
#50 0x000055c963abed58 in mysql_execute_command (thd=0x7f9bc8000b00) at /10.4/sql/sql_parse.cc:3882
 
#51 0x000055c963accce2 in mysql_parse (thd=0x7f9bc8000b00, rawbuf=0x7f9bc8013108 "SELECT 1 FROM t3\nWHERE EXISTS(SELECT 1 FROM t1 WHERE id IN (\nSELECT bt1.id FROM (t2 JOIN t1 AS bt1 ON bt1.ch = t2.ch2 and bt1.id = t2.pk) WHERE t2.ch2 <= 'g') \nOR t1.id2 = t1.id)", length=178, parser_state=0x7f9c1837c000, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7892
 
#52 0x000055c963ab8f51 in dispatch_command (command=COM_QUERY, thd=0x7f9bc8000b00, packet=0x7f9bc819dca1 "SELECT 1 FROM t3\nWHERE EXISTS(SELECT 1 FROM t1 WHERE id IN (\nSELECT bt1.id FROM (t2 JOIN t1 AS bt1 ON bt1.ch = t2.ch2 and bt1.id = t2.pk) WHERE t2.ch2 <= 'g') \nOR t1.id2 = t1.id)", packet_length=178, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1827
 
#53 0x000055c963ab76e3 in do_command (thd=0x7f9bc8000b00) at /10.4/sql/sql_parse.cc:1360
 
#54 0x000055c963c31994 in do_handle_one_connection (connect=0x55c9674bb080) at /10.4/sql/sql_connect.cc:1404
 
#55 0x000055c963c316bd in handle_one_connection (arg=0x55c9674bb080) at /10.4/sql/sql_connect.cc:1306
 
#56 0x000055c96455d015 in pfs_spawn_thread (arg=0x55c967439830) at /10.4/storage/perfschema/pfs.cc:1862
 
#57 0x00007f9c204416ba in start_thread (arg=0x7f9c1837d700) at pthread_create.c:333
 
#58 0x00007f9c1f6d241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Alice Sherepa [ 2019-09-12 ]

probably the same as MDEV-19919

Comment by Marko Mäkelä [ 2019-09-12 ]

The assertion was added by igor in MDEV-19820. It looks valid to me. If the code is supposed to filter secondary index records, it will have to be adjusted.

As far as I understand, MDEV-16188 is supposed to apply to PRIMARY KEY scans only.

Comment by Igor Babaev [ 2019-11-25 ]

If to run EXPLAIN EXTENDED for the query from the reported test case we can see that the optimizer builds an invalid execution plan:

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+
 
| id   | select_type        | table | type         | possible_keys                | key     | key_len | ref  | rows    | filtered | Extra                                                                            |
 
+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+
 
|    1 | PRIMARY            | t3    | ALL          | NULL                         | NULL    | NULL    | NULL | 1       |   100.00 | Using where                                                                      |
 
|    2 | DEPENDENT SUBQUERY | t1    | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 | Using where                                                                      |
 
|    3 | MATERIALIZED       | bt1   | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 | Using where                                                                      |
 
|    3 | MATERIALIZED       | t2    | index|filter | PRIMARY,col_date_key,ch2,id2 | ch2|ch2 | 4|4     | NULL | 9 (22%) |    11.11 | Using where; Using index; Using join buffer (flat, BNL join); Using rowid filter |
 
+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

In the last line of this plan we see that the rowid filter uses the same index by which we scan the table rows. This does not make any sense.

If I simplify the test case query for:

SELECT 1 FROM t3 
WHERE EXISTS ( SELECT 1 FROM t1
 
               WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1
 
                                WHERE bt1.id = t2.pk AND
 
                                      t2.ch2 <= 'g' ) OR
 
                     t1.id2 = t1.id);

the execution of it does not fail, but it still uses some nonsensical plan

MariaDB [test]> EXPLAIN EXTENDED
 
    -> SELECT 1 FROM t3 
    -> WHERE EXISTS ( SELECT 1 FROM t1
 
    ->                WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1
 
    ->                                 WHERE bt1.id = t2.pk AND
 
    ->                                       t2.ch2 <= 'g' ) OR
 
    ->                      t1.id2 = t1.id);
 
+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+
 
| id   | select_type        | table | type         | possible_keys                | key     | key_len | ref  | rows    | filtered | Extra                                                                            |
 
+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+
 
|    1 | PRIMARY            | t3    | ALL          | NULL                         | NULL    | NULL    | NULL | 1       |   100.00 | Using where                                                                      |
 
|    2 | DEPENDENT SUBQUERY | t1    | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 | Using where                                                                      |
 
|    3 | MATERIALIZED       | bt1   | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 |                                                                                  |
 
|    3 | MATERIALIZED       | t2    | range|filter | PRIMARY,col_date_key,ch2,id2 | ch2|ch2 | 4|4     | NULL | 2 (22%) |    22.22 | Using where; Using index; Using join buffer (flat, BNL join); Using rowid filter |
 
+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

Comment by Igor Babaev [ 2019-11-25 ]

To catch such invalid plans before the execution plan and even to catch them for EXPLAIN commands I've added
the following assertions in bool JOIN::make_range_rowid_filters():

    DBUG_ASSERT(!(tab->ref.key >= 0 &&
 
                  tab->ref.key == (int) tab->range_rowid_filter_info->key_no));
 
    DBUG_ASSERT(!(tab->ref.key == -1 && tab->quick &&
 
                  tab->quick->index == tab->range_rowid_filter_info->key_no));

Now both EXPLAIN EXTENDED commands fail with the second assertion.

Comment by Igor Babaev [ 2019-11-27 ]

A fix for this bug was pushed into 10.4.

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