[MDEV-13723] Server crashes in ha_heap::find_unique_row or Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT Created: 2017-09-03  Updated: 2020-08-25  Resolved: 2017-11-10

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3
Fix Version/s: 10.3.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-14346 incorrect result of intersect with AN... Closed
relates to MDEV-10141 Add support for INTERSECT (and common... Closed

 Description    

CREATE TABLE t (i INT);
 
INSERT INTO t VALUES (1),(2);
 
SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 );

10.3 non-debug 016c35a7f2f4a2

 
#2  <signal handler called>
 
#3  ha_heap::find_unique_row (this=0x7fc4080aa1c0, record=0x7fc4080a9f80 "\377\003", unique_idx=<optimized out>) at /data/src/10.3/storage/heap/ha_heap.cc:813
 
#4  0x000056176c1f1fe5 in select_unit::send_data (this=0x7fc40802010d, values=...) at /data/src/10.3/sql/sql_union.cc:209
 
#5  0x000056176c1b2462 in JOIN::exec_inner (this=this@entry=0x7fc408014548) at /data/src/10.3/sql/sql_select.cc:3418
 
#6  0x000056176c1b26b9 in JOIN::exec (this=0x7fc408014548) at /data/src/10.3/sql/sql_select.cc:3337
 
#7  0x000056176c1f403b in st_select_lex_unit::exec (this=0x7fc408012080) at /data/src/10.3/sql/sql_union.cc:1428
 
#8  0x000056176c3a870d in subselect_union_engine::exec (this=0x7fc4080132d0) at /data/src/10.3/sql/item_subselect.cc:3873
 
#9  0x000056176c3a8ebd in Item_subselect::exec (this=0x7fc4080130c0) at /data/src/10.3/sql/item_subselect.cc:715
 
#10 0x000056176c3a92bf in Item_in_subselect::val_bool (this=0x7fc4080130c0) at /data/src/10.3/sql/item_subselect.cc:1729
 
#11 0x000056176c34b94d in Item_in_optimizer::val_int (this=0x7fc4080142b0) at /data/src/10.3/sql/item_cmpfunc.cc:1634
 
#12 0x000056176c327f42 in Item_cache_int::cache_value (this=0x7fc40806d280) at /data/src/10.3/sql/item.cc:9410
 
#13 0x000056176c33aa67 in cache (this=0x7fc40806d1c8) at /data/src/10.3/sql/item.cc:8252
 
#14 Item_cache_wrapper::val_int (this=0x7fc40806d1c8) at /data/src/10.3/sql/item.cc:8306
 
#15 0x000056176c3497ec in Item_func_nop_all::val_int (this=0x7fc408013308) at /data/src/10.3/sql/item_cmpfunc.cc:285
 
#16 0x000056176c185d08 in evaluate_join_record (join=join@entry=0x7fc4080134d0, join_tab=join_tab@entry=0x7fc408016050, error=error@entry=0) at /data/src/10.3/sql/sql_select.cc:18962
 
#17 0x000056176c18fc5b in sub_select (join=0x7fc4080134d0, join_tab=0x7fc408016050, end_of_records=<optimized out>) at /data/src/10.3/sql/sql_select.cc:18906
 
#18 0x000056176c1b24cc in do_select (procedure=<optimized out>, join=0x7fc4080134d0) at /data/src/10.3/sql/sql_select.cc:18407
 
#19 JOIN::exec_inner (this=this@entry=0x7fc4080134d0) at /data/src/10.3/sql/sql_select.cc:3542
 
#20 0x000056176c1b26b9 in JOIN::exec (this=this@entry=0x7fc4080134d0) at /data/src/10.3/sql/sql_select.cc:3337
 
#21 0x000056176c1b27fb in mysql_select (thd=thd@entry=0x7fc4080009a8, tables=0x7fc408011570, wild_num=1, fields=..., conds=0x7fc408013308, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc4080134b0, unit=0x7fc4080044a8, select_lex=0x7fc408004be8) at /data/src/10.3/sql/sql_select.cc:3737
 
#22 0x000056176c1b3210 in handle_select (thd=thd@entry=0x7fc4080009a8, lex=lex@entry=0x7fc4080043e0, result=result@entry=0x7fc4080134b0, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/10.3/sql/sql_select.cc:378
 
#23 0x000056176c09a2eb in execute_sqlcom_select (thd=0x7fc4080009a8, all_tables=0x7fc408011570) at /data/src/10.3/sql/sql_parse.cc:6466
 
#24 0x000056176c16016c in mysql_execute_command (thd=0x7fc4080009a8) at /data/src/10.3/sql/sql_parse.cc:3731
 
#25 0x000056176c162bca in mysql_parse (thd=0x7fc4080009a8, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/src/10.3/sql/sql_parse.cc:7921
 
#26 0x000056176c166738 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fc4080009a8, packet=packet@entry=0x7fc408008fc9 "", packet_length=packet_length@entry=62, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.3/sql/sql_parse.cc:1819
 
#27 0x000056176c166e3f in do_command (thd=0x7fc4080009a8) at /data/src/10.3/sql/sql_parse.cc:1370
 
#28 0x000056176c230724 in do_handle_one_connection (connect=connect@entry=0x56176f1b21b8) at /data/src/10.3/sql/sql_connect.cc:1418
 
#29 0x000056176c2308c4 in handle_one_connection (arg=arg@entry=0x56176f1b21b8) at /data/src/10.3/sql/sql_connect.cc:1324
 
#30 0x000056176c515134 in pfs_spawn_thread (arg=0x56176f1d8448) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#31 0x00007fc41fd33494 in start_thread (arg=0x7fc418084700) at pthread_create.c:333
 
#32 0x00007fc41deab93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

10.3 debug 016c35a7f2f4a2

 
mysqld: /data/src/10.3/sql/sql_union.cc:1308: bool st_select_lex_unit::optimize(): Assertion `0' failed.
 
170903 23:55:15 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f0289fd5ee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#8  0x00005602da76a3f2 in st_select_lex_unit::optimize (this=0x7f0274015758) at /data/src/10.3/sql/sql_union.cc:1308
 
#9  0x00005602da76a76f in st_select_lex_unit::exec (this=0x7f0274015758) at /data/src/10.3/sql/sql_union.cc:1374
 
#10 0x00005602daa11a53 in subselect_union_engine::exec (this=0x7f02740169a8) at /data/src/10.3/sql/item_subselect.cc:3873
 
#11 0x00005602daa05f78 in Item_subselect::exec (this=0x7f0274016798) at /data/src/10.3/sql/item_subselect.cc:715
 
#12 0x00005602daa065b4 in Item_in_subselect::exec (this=0x7f0274016798) at /data/src/10.3/sql/item_subselect.cc:895
 
#13 0x00005602daa092a4 in Item_in_subselect::val_bool (this=0x7f0274016798) at /data/src/10.3/sql/item_subselect.cc:1729
 
#14 0x00005602da5a9fa9 in Item::val_bool_result (this=0x7f0274016798) at /data/src/10.3/sql/item.h:1119
 
#15 0x00005602da97e206 in Item_in_optimizer::val_int (this=0x7f0274017988) at /data/src/10.3/sql/item_cmpfunc.cc:1634
 
#16 0x00005602da5a9f1d in Item::val_int_result (this=0x7f0274017988) at /data/src/10.3/sql/item.h:1115
 
#17 0x00005602da96d9e1 in Item_cache_int::cache_value (this=0x7f027411c998) at /data/src/10.3/sql/item.cc:9410
 
#18 0x00005602da975ae8 in Item_cache_wrapper::cache (this=0x7f027411c8e0) at /data/src/10.3/sql/item.cc:8252
 
#19 0x00005602da96a74e in Item_cache_wrapper::val_int (this=0x7f027411c8e0) at /data/src/10.3/sql/item.cc:8306
 
#20 0x00005602da979e78 in Item_func_nop_all::val_int (this=0x7f02740169e0) at /data/src/10.3/sql/item_cmpfunc.cc:285
 
#21 0x00005602da6f1516 in evaluate_join_record (join=0x7f0274016ba8, join_tab=0x7f0274019728, error=0) at /data/src/10.3/sql/sql_select.cc:18962
 
#22 0x00005602da6f12e4 in sub_select (join=0x7f0274016ba8, join_tab=0x7f0274019728, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:18906
 
#23 0x00005602da6f06c0 in do_select (join=0x7f0274016ba8, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18407
 
#24 0x00005602da6c91b8 in JOIN::exec_inner (this=0x7f0274016ba8) at /data/src/10.3/sql/sql_select.cc:3542
 
#25 0x00005602da6c863e in JOIN::exec (this=0x7f0274016ba8) at /data/src/10.3/sql/sql_select.cc:3337
 
#26 0x00005602da6c9831 in mysql_select (thd=0x7f0274000b00, tables=0x7f0274014c48, wild_num=1, fields=..., conds=0x7f02740169e0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f0274016b88, unit=0x7f02740047c0, select_lex=0x7f0274004f00) at /data/src/10.3/sql/sql_select.cc:3737
 
#27 0x00005602da6bddbc in handle_select (thd=0x7f0274000b00, lex=0x7f02740046f8, result=0x7f0274016b88, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378
 
#28 0x00005602da68926e in execute_sqlcom_select (thd=0x7f0274000b00, all_tables=0x7f0274014c48) at /data/src/10.3/sql/sql_parse.cc:6466
 
#29 0x00005602da67f7ef in mysql_execute_command (thd=0x7f0274000b00) at /data/src/10.3/sql/sql_parse.cc:3731
 
#30 0x00005602da68cbd4 in mysql_parse (thd=0x7f0274000b00, rawbuf=0x7f0274014a08 "SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 )", length=62, parser_state=0x7f028426c610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7921
 
#31 0x00005602da67a32e in dispatch_command (command=COM_QUERY, thd=0x7f0274000b00, packet=0x7f0274149c61 "", packet_length=62, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1819
 
#32 0x00005602da678d90 in do_command (thd=0x7f0274000b00) at /data/src/10.3/sql/sql_parse.cc:1370
 
#33 0x00005602da7cf646 in do_handle_one_connection (connect=0x5602dcd1d260) at /data/src/10.3/sql/sql_connect.cc:1418
 
#34 0x00005602da7cf3d3 in handle_one_connection (arg=0x5602dcd1d260) at /data/src/10.3/sql/sql_connect.cc:1324
 
#35 0x00005602dac471dc in pfs_spawn_thread (arg=0x5602dcdb6240) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#36 0x00007f028bf1a494 in start_thread (arg=0x7f028426d700) at pthread_create.c:333
 
#37 0x00007f028a09293f in clone () from /lib/x86_64-linux-gnu/libc.so.6



 Comments   
Comment by Oleksandr Byelkin [ 2017-11-09 ]

Here is other test suite which we got from respectable source:

--source include/have_innodb.inc
 
 
 
 
 
CREATE TABLE t1 (
 
  F1 varchar(36) DEFAULT NULL,
 
  F6 varchar(3) DEFAULT NULL,
 
  F7 varchar(5) DEFAULT NULL,
 
  F8 varchar(2) DEFAULT NULL,
 
  F2 varchar(6) DEFAULT NULL,
 
  F5 char(1) DEFAULT NULL,
 
  pk int(11) NOT NULL,
 
  PRIMARY KEY (pk),
 
  UNIQUE KEY PK_1 (F1,F7,F8,F2,F6),
 
  KEY I1 (F8,F5),
 
  KEY I2 (F1,F6,F8,F2)
 
) engine=innodb;
 
 
 
insert into t1 values
 
("123","ABC","ABCDE","DE","ABCDEF", "I", 1234), 
("124","ABE","ABCD4","DE","ABCDEF", "I", 1235);
 
 
 
CREATE TABLE t2 (
 
  F1 varchar(36) NOT NULL,
 
  F6 varchar(3) NOT NULL,
 
  F7 varchar(5) NOT NULL,
 
  F8 varchar(2) NOT NULL,
 
  F3 datetime NOT NULL,
 
  F2 varchar(6) NOT NULL,
 
  F9 decimal(20,4) DEFAULT NULL,
 
  F4 char(1) NOT NULL,
 
  FF1 char(1) NOT NULL DEFAULT 'P',
 
  PRIMARY KEY (F1,F7,F8,F3,F2,F6),
 
  UNIQUE KEY U1 (F1,F7,F8,F3,F2,F6),
 
  KEY K1 (F3),
 
  KEY K2 (F1,F7,F8,F2,F6),
 
  KEY K3 (F1,F8,F6,F2,F3),
 
  KEY K4 (F3,F8,F2,F4,FF1),
 
  KEY K5 (F2,F3,F9,F4),
 
  CONSTRAINT C1 FOREIGN KEY (F1, F7, F8, F2, F6) REFERENCES t1 (F1, F7, F8, F2, F6)
 
) engine=innodb;
 
 
 
 
 
insert into t2 values
 
("123","ABC","ABCDE","DE", "20171104", "ABCDEF", 123, "I", "P");
 
 
 
SELECT * FROM t1
 
  WHERE F1 IN (SELECT F1 FROM t1 WHERE F2 ='ABCDEF'
 
               EXCEPT
 
               SELECT F1 FROM t2 WHERE F2='ABCDEF' AND
 
                                       F3='20171104' AND
 
                                       F4='I'  )
 
        AND F5 = 1;
 
 
 
drop table t1,t2;

Comment by Oleksandr Byelkin [ 2017-11-09 ]

It looks like the table was not emptied between rexecutions...

Comment by Oleksandr Byelkin [ 2017-11-10 ]

The problem was that now with EXCEPT & INTERSECT it is not rue anymore that is Item on subquery is not assigned value the temporary table is empty (it is possible that something was added then deleted)

Comment by Oleksandr Byelkin [ 2017-11-10 ]

github tree: bb-10.3-MDEV-13723

revision-id: 78a31db6b322ec0e63601ed471feaedc0ad2987c (mariadb-10.3.2-40-g78a31db6b32)
parent(s): 06a9a366a3df18b02a97fce8e585229f0c01bd09
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2017-11-10 11:22:51 +0100
message:

MDEV-13723: Server crashes in ha_heap::find_unique_row or Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT

With INTERSECT/EXCEPT fakt that subquery item of IN/ALL/ANY was not assigned value does not mean that temporary table used for calculating unit is empty (records could be deleted).

—

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