[MDEV-19919] Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check Created: 2019-07-01  Updated: 2020-01-15  Resolved: 2019-11-26

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

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 2
Labels: affects-tests

Issue Links:
Duplicate
is duplicated by MDEV-21112 Can't find record when using LIMIT 1 ... Closed
Relates
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-19820 Wrong result with multiple single col... Closed
relates to MDEV-20056 Assertion `!prebuilt->index->is_prima... Closed
relates to MDEV-20629 SELECT with WHERE IN and ORDER BY doe... Closed
relates to MDEV-20851 optimizer_switch 'mrr=on' causes fail... Closed
relates to MDEV-21446 Assertion `!prebuilt->index->is_prima... Closed

 Description    

--source include/have_innodb.inc
 
 
 
SET @stats.save= @@innodb_stats_persistent;
 
SET GLOBAL innodb_stats_persistent= ON;
 
 
 
CREATE TABLE t1 (
 
    a INT,
 
    b VARCHAR(10),
 
    c VARCHAR(1024),
 
    KEY (b),
 
    KEY (c)
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO t1 VALUES
 
  (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'),
 
  (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'),
 
  (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'),
 
  (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'),
 
  (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i');
 
 
 
CREATE TABLE t2 (d INT) ENGINE=MyISAM;
 
 
 
SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;
 
SET GLOBAL innodb_stats_persistent= @stats.save;

10.4 223c550d

 
mysqld: /data/src/10.4/storage/innobase/row/row0sel.cc:3906: ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*): Assertion `!prebuilt->index->is_primary()' failed.
 
190702  2:52:40 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fd21b70df12 in __GI___assert_fail (assertion=0x55bbcd9d4ca8 "!prebuilt->index->is_primary()", file=0x55bbcd9d3be0 "/data/src/10.4/storage/innobase/row/row0sel.cc", line=3906, function=0x55bbcd9d7dc0 <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  0x000055bbcd263c8f in row_search_idx_cond_check (mysql_rec=0x7fd1b8131658 "\370\001", prebuilt=0x7fd1b8131f28, rec=0x7fd2009881ca "", offsets=0x7fd218159db0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3906
 
#9  0x000055bbcd2675e7 in row_search_mvcc (buf=0x7fd1b8131658 "\370\001", mode=PAGE_CUR_GE, prebuilt=0x7fd1b8131f28, match_mode=1, direction=0) at /data/src/10.4/storage/innobase/row/row0sel.cc:5220
 
#10 0x000055bbcd0c1785 in ha_innobase::index_read (this=0x7fd1b8130e98, buf=0x7fd1b8131658 "\370\001", key_ptr=0x7fd1b8063440 "", key_len=6, find_flag=HA_READ_KEY_EXACT) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9339
 
#11 0x000055bbcd0c28e7 in ha_innobase::rnd_pos (this=0x7fd1b8130e98, buf=0x7fd1b8131658 "\370\001", pos=0x7fd1b8063440 "") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9843
 
#12 0x000055bbcce92314 in handler::ha_rnd_pos (this=0x7fd1b8130e98, buf=0x7fd1b8131658 "\370\001", pos=0x7fd1b8063440 "") at /data/src/10.4/sql/handler.cc:2866
 
#13 0x000055bbcd022343 in rr_from_pointers (info=0x7fd1b8065828) at /data/src/10.4/sql/records.cc:543
 
#14 0x000055bbccaa7fe7 in READ_RECORD::read_record (this=0x7fd1b8065828) at /data/src/10.4/sql/records.h:69
 
#15 0x000055bbccbcd4bd in join_init_read_record (tab=0x7fd1b8065760) at /data/src/10.4/sql/sql_select.cc:21055
 
#16 0x000055bbccbcb141 in sub_select (join=0x7fd1b80163d0, join_tab=0x7fd1b8065760, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20111
 
#17 0x000055bbccbca6d0 in do_select (join=0x7fd1b80163d0, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19652
 
#18 0x000055bbccba0aad in JOIN::exec_inner (this=0x7fd1b80163d0) at /data/src/10.4/sql/sql_select.cc:4391
 
#19 0x000055bbccb9fc5a in JOIN::exec (this=0x7fd1b80163d0) at /data/src/10.4/sql/sql_select.cc:4173
 
#20 0x000055bbccba12ff in mysql_select (thd=0x7fd1b8000b00, tables=0x7fd1b80135c0, wild_num=0, fields=..., conds=0x7fd1b8015758, og_num=1, order=0x7fd1b80159d0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fd1b80163a8, unit=0x7fd1b8004a28, select_lex=0x7fd1b8012ff8) at /data/src/10.4/sql/sql_select.cc:4605
 
#21 0x000055bbccb918ee in handle_select (thd=0x7fd1b8000b00, lex=0x7fd1b8004960, result=0x7fd1b80163a8, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:425
 
#22 0x000055bbccb5b48f in execute_sqlcom_select (thd=0x7fd1b8000b00, all_tables=0x7fd1b80135c0) at /data/src/10.4/sql/sql_parse.cc:6340
 
#23 0x000055bbccb512d6 in mysql_execute_command (thd=0x7fd1b8000b00) at /data/src/10.4/sql/sql_parse.cc:3882
 
#24 0x000055bbccb5f223 in mysql_parse (thd=0x7fd1b8000b00, rawbuf=0x7fd1b8012ef8 "SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a", length=75, parser_state=0x7fd21815c1d0, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7892
 
#25 0x000055bbccb4b4cd in dispatch_command (command=COM_QUERY, thd=0x7fd1b8000b00, packet=0x7fd1b8142621 "", packet_length=75, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1827
 
#26 0x000055bbccb49c8b in do_command (thd=0x7fd1b8000b00) at /data/src/10.4/sql/sql_parse.cc:1360
 
#27 0x000055bbcccc2f81 in do_handle_one_connection (connect=0x55bbd0803b40) at /data/src/10.4/sql/sql_connect.cc:1403
 
#28 0x000055bbcccc2ce5 in handle_one_connection (arg=0x55bbd0803b40) at /data/src/10.4/sql/sql_connect.cc:1306
 
#29 0x00007fd21d2824a4 in start_thread (arg=0x7fd21815d700) at pthread_create.c:456
 
#30 0x00007fd21b7cad0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Not reproducible with the provided test case on 10.3.

 Comments   
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 Alice Sherepa [ 2019-09-19 ]

igor, could you please check if the following patch fixes the problem from MDEV-20629:

--source include/have_innodb.inc
 
 
 
CREATE TABLE `services` (
 
  `service_id` char(36) NOT NULL,
 
  `service_supplier_id` char(64) DEFAULT NULL,
 
  `billing_group_id` char(64) DEFAULT NULL,
 
  `seq` tinyint(4) unsigned NOT NULL,
 
  `service_descrip` varchar(216) NOT NULL,
 
  `provisioned` enum('S','U','C','H','M','R','F','D') NOT NULL DEFAULT 'S',
 
  KEY `service_id` (`service_id`),
 
  KEY `provisioned` (`provisioned`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8  ;
 
 
INSERT INTO `services` VALUES ('189c2d2e-017f-40b8-9555-4ea30f0ae351',NULL,NULL,1,'','S'),('189c2d2e-017f-40b8-9555-4ea30f0ae351',NULL,NULL,2,'','S'),('045b1b0e-cf53-4249-b062-35149e587e48',NULL,NULL,0,'','C'),('bb63e272-d312-40f9-ac17-2c3660842363',NULL,NULL,0,'','C'),('189c2d2e-017f-40b8-9555-4ea30f0ae351',NULL,NULL,0,'','S'),('142f9f75-506c-4e6e-b625-0e1ca3b19dd8',NULL,NULL,0,'','C'),('c4af6e73-7fe7-4d48-a61d-d5a01f88b07f',NULL,NULL,0,'','C'),('0ae942eb-7cf1-4669-afe0-7707af632ce2',NULL,NULL,0,'','C'),('15df6311-9a12-4465-b8d5-1cf17487162d',NULL,NULL,0,'','C'),('a4cb7786-fc04-4e01-927a-7c428bb55627',NULL,NULL,0,'','C'),('ae7416e7-5fc3-47a2-96e8-eb3efe3a8bd7',NULL,NULL,0,'','C'),('17b774c7-2092-4565-9671-5387977fe197',NULL,NULL,0,'','C'),('f42836ab-73c1-4942-8b1e-72fff02b1ffe',NULL,NULL,0,'','C'),('ade04016-def2-4845-b0ea-7f02a3341b7e',NULL,NULL,0,'','C'),('1f0e6e01-b9d8-41f4-9831-c08d47059c5e',NULL,NULL,0,'','C'),('b16d8cb8-ae69-422a-a371-8b164bbf711f',NULL,NULL,0,'','C'),('7da1d364-d849-4508-a7c9-04c2f6ee730a',NULL,NULL,0,'','C'),('6080d648-e561-4e6d-b59b-1489726ecb98',NULL,NULL,0,'','C');
 
 
 
SELECT * FROM services
 
WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
 
ORDER BY seq;
 
 
 
SELECT * FROM services
 
WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
 
ORDER BY seq;

Comment by richardeaxon [ 2019-10-22 ]

Hi folks, any idea when this will get attention? I get isolated incorrect query results on 10.4 with our application which is bad, but I am not sure what other incorrect results we have lurking due to this.

Comment by Igor Babaev [ 2019-11-22 ]

Elena,
Here's what I have on my laptop for the current state of 10.4:

igor@stephan:~/maria-git/10.4/mysql-test> ../client/mysql test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 8
 
Server version: 10.4.11-MariaDB-debug Source distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> SET @stats.save= @@innodb_stats_persistent;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> SET GLOBAL innodb_stats_persistent= ON;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> CREATE TABLE t1 (
 
    ->     a INT,
 
    ->     b VARCHAR(10),
 
    ->     c VARCHAR(1024),
 
    ->     KEY (b),
 
    ->     KEY (c)
 
    -> ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.034 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t1 VALUES
 
    ->   (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'),
 
    ->   (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'),
 
    ->   (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'),
 
    ->   (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'),
 
    ->   (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i');
 
Query OK, 22 rows affected (0.029 sec)
 
Records: 22  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> CREATE TABLE t2 (d INT) ENGINE=MyISAM;
 
Query OK, 0 rows affected (0.009 sec)
 
 
 
MariaDB [test]> SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
 
+------+
 
| a    |                                                                                                             
+------+
 
|    1 |
 
|    5 |
 
+------+
 
2 rows in set (0.005 sec)

I other words I've failed to reproduce the problem with your test case.

Comment by Igor Babaev [ 2019-11-22 ]

If I run the test case with mtr I have the reported failure.
This happens because with mtr a plan that uses a rowid_filter is chosen:

+EXPLAIN EXTENDED
 
+SELECT * FROM t1;
 
+id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      100.00
 
+Warnings:
 
+Note   1003    select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1`
 
+EXPLAIN EXTENDED
 
+SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
 
+id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
+1      SIMPLE  t2      system  NULL    NULL    NULL    NULL    0       0.00    Const row not found
 
+1      SIMPLE  t1      range|filter    b,c     b|c     13|1027 NULL    5 (43%) 43.48   Using index condition; Using where; Using filesort; Using rowid filter
 
+Warnings:
 
+Note   1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`c` < 'k' and `test`.`t1`.`b` > 't' order by `test`.`t1`.`a`

While when running the query from the mysql client a plan with table scan is chosen:

MariaDB [test]> EXPLAIN EXTENDED
 
    -> SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
 
+------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+
 
| id   | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
 
+------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+
 
|    1 | SIMPLE      | t2    | system | NULL          | NULL | NULL    | NULL | 0    |     0.00 | Const row not found         |
 
|    1 | SIMPLE      | t1    | ALL    | b,c           | NULL | NULL    | NULL | 22   |    10.33 | Using where; Using filesort |
 
+------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+
 
2 rows in set, 1 warning (0.002 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED
 
    -> SELECT * FROM t1;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 22   |   100.00 |       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
1 row in set, 1 warning (0.001 sec)

We can see that the estimate for number of rows for these two runs are different 23 and 23.
This explains the different choices of plans.

Now if I add these two rows to the table t1

 INSERT INTO t1 VALUES (3,'g','x'), (2,'h','y');

I have the plan that uses rowid filter for mysql client as well though the estimate of the table rows are still different (24 - for mysql client, 25 - for mtr).

Now the query fails with mysql client as well.

Apparently the second table is not needed to get the failure.

Comment by Igor Babaev [ 2019-11-23 ]

Using the command

 ANALYZE TABLE t1;

allows to get the same estimate for number of rows in t1 with any run of the above test case.

Comment by Igor Babaev [ 2019-11-26 ]

A fix for this bug was pushed into 10.4

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