[MDEV-10053] Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_long::val_int() Created: 2016-05-10  Updated: 2017-04-22  Resolved: 2017-04-22

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.0.31

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

Issue Links:
Relates
relates to MDEV-6486 Assertion `!table || (!table->read_se... Closed

 Description   

Note: I'm filing it separately from MDEV-6486, because the test case in MDEV-6486 does not cause a crash on 10.1 and 10.2, while this one does.

CREATE TABLE t1 (pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
 
 
 
SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1
 
WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );

Stack trace from 10.0 commit cee9ab9d85a8d75290b0d60bc7af26c8cf179a1d

 
mysqld: /src/10.0/sql/field.cc:3794: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
 
160511  1:10:11 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007feeb507e1d2 in __assert_fail () from /lib64/libc.so.6
 
#8  0x0000000000838184 in Field_long::val_int (this=0x7feeafb28fe0) at /src/10.0/sql/field.cc:3794
 
#9  0x0000000000873d42 in Item_field::val_int (this=0x7feeada2ec28) at /src/10.0/sql/item.cc:2592
 
#10 0x0000000000880e7f in Item_direct_ref::val_int (this=0x7feeada30be8) at /src/10.0/sql/item.cc:7519
 
#11 0x000000000088ad5b in Item_direct_view_ref::val_int (this=0x7feeada30be8) at /src/10.0/sql/item.h:3821
 
#12 0x0000000000891e59 in Arg_comparator::compare_int_signed (this=0x7feeada8f608) at /src/10.0/sql/item_cmpfunc.cc:1201
 
#13 0x00000000008a265a in Arg_comparator::compare (this=0x7feeada8f608) at /src/10.0/sql/item_cmpfunc.h:81
 
#14 0x0000000000894169 in Item_func_eq::val_int (this=0x7feeada8f540) at /src/10.0/sql/item_cmpfunc.cc:1976
 
#15 0x00000000006cd016 in evaluate_join_record (join=0x7feeada304b8, join_tab=0x7feeada8e120, error=0) at /src/10.0/sql/sql_select.cc:17938
 
#16 0x00000000006ccc55 in sub_select (join=0x7feeada304b8, join_tab=0x7feeada8e120, end_of_records=false) at /src/10.0/sql/sql_select.cc:17841
 
#17 0x00000000006cc4c9 in do_select (join=0x7feeada304b8, fields=0x7feeada78cd0, table=0x0, procedure=0x0) at /src/10.0/sql/sql_select.cc:17503
 
#18 0x00000000006a973a in JOIN::exec_inner (this=0x7feeada304b8) at /src/10.0/sql/sql_select.cc:3084
 
#19 0x00000000006a6c0a in JOIN::exec (this=0x7feeada304b8) at /src/10.0/sql/sql_select.cc:2373
 
#20 0x00000000007d8ccd in join_tab_execution_startup (tab=0x7feead860968) at /src/10.0/sql/opt_subselect.cc:5119
 
#21 0x00000000006ccb85 in sub_select (join=0x7feeada2e5e0, join_tab=0x7feead860968, end_of_records=false) at /src/10.0/sql/sql_select.cc:17829
 
#22 0x00000000006cd333 in evaluate_join_record (join=0x7feeada2e5e0, join_tab=0x7feead860640, error=0) at /src/10.0/sql/sql_select.cc:18063
 
#23 0x00000000006ccc55 in sub_select (join=0x7feeada2e5e0, join_tab=0x7feead860640, end_of_records=false) at /src/10.0/sql/sql_select.cc:17841
 
#24 0x00000000006cc4c9 in do_select (join=0x7feeada2e5e0, fields=0x7feeb11ee128, table=0x0, procedure=0x0) at /src/10.0/sql/sql_select.cc:17503
 
#25 0x00000000006a973a in JOIN::exec_inner (this=0x7feeada2e5e0) at /src/10.0/sql/sql_select.cc:3084
 
#26 0x00000000006a6c0a in JOIN::exec (this=0x7feeada2e5e0) at /src/10.0/sql/sql_select.cc:2373
 
#27 0x00000000006a9fb7 in mysql_select (thd=0x7feeb11e9fb0, rref_pointer_array=0x7feeb11ee288, tables=0x7feeada79ed0, wild_num=0, fields=..., conds=0x7feeada2e398, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748610, result=0x7feeada2e5c0, unit=0x7feeb11ed920, select_lex=0x7feeb11ee010) at /src/10.0/sql/sql_select.cc:3308
 
#28 0x00000000006a0333 in handle_select (thd=0x7feeb11e9fb0, lex=0x7feeb11ed858, result=0x7feeada2e5c0, setup_tables_done_option=0) at /src/10.0/sql/sql_select.cc:373
 
#29 0x0000000000674b85 in execute_sqlcom_select (thd=0x7feeb11e9fb0, all_tables=0x7feeada79ed0) at /src/10.0/sql/sql_parse.cc:5293
 
#30 0x000000000066d09f in mysql_execute_command (thd=0x7feeb11e9fb0) at /src/10.0/sql/sql_parse.cc:2562
 
#31 0x00000000006777cd in mysql_parse (thd=0x7feeb11e9fb0, rawbuf=0x7feeada77088 "SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1\nWHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 )", length=161, parser_state=0x7feeb7128650) at /src/10.0/sql/sql_parse.cc:6574
 
#32 0x000000000066a309 in dispatch_command (command=COM_QUERY, thd=0x7feeb11e9fb0, packet=0x7feeafb3a1b1 "", packet_length=161) at /src/10.0/sql/sql_parse.cc:1308
 
#33 0x00000000006695d6 in do_command (thd=0x7feeb11e9fb0) at /src/10.0/sql/sql_parse.cc:998
 
#34 0x0000000000787cb2 in do_handle_one_connection (thd_arg=0x7feeb11e9fb0) at /src/10.0/sql/sql_connect.cc:1378
 
#35 0x0000000000787a24 in handle_one_connection (arg=0x7feeb11e9fb0) at /src/10.0/sql/sql_connect.cc:1293
 
#36 0x0000000000df561c in pfs_spawn_thread (arg=0x7feeaebbf4f0) at /src/10.0/storage/perfschema/pfs.cc:1860
 
#37 0x00007feeb6da10a4 in start_thread () from /lib64/libpthread.so.0
 
#38 0x00007feeb513504d in clone () from /lib64/libc.so.6

EXPLAIN

 
EXPLAIN EXTENDED
 
SELECT STRAIGHT_JOIN sq1.f2 FROM ( SELECT * FROM t1 ) AS sq1
 
WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00
 
1       PRIMARY <subquery3>     eq_ref  distinct_key    distinct_key    4       test.t1.f1      1       100.00
 
3       MATERIALIZED    <subquery4>     eq_ref  distinct_key    distinct_key    4       func    1       100.00
 
3       MATERIALIZED    sq2     ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
 
4       MATERIALIZED    t1      ALL     NULL    NULL    NULL    NULL    2       100.00
 
Warnings:
 
Note    1276    Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1
 
Note    1276    Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1
 
Note    1003    select straight_join `test`.`t1`.`f2` AS `f2` from  <materialize> (select `test`.`sq2`.`f1` from `test`.`t1` `sq2` semi join (`test`.`t1`) where (`test`.`t1`.`pk` = `test`.`t1`.`f1`)) join `test`.`t1` where (`<subquery3>`.`f1` = `test`.`t1`.`f1`)

5.5 does not fail.



 Comments   
Comment by Igor Babaev [ 2017-03-31 ]

Here's what happened with this query.
After merging with derived table sq1
the EXIST subquery was re-written into an IN sibquery:

SELECT STRAIGHT_JOIN sq1.f2 FROM  t1  AS sq1
 
WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
 
 =>
 
SELECT STRAIGHT_JOIN sq1.f2 FROM  t1  AS sq1
 
WHERE sq1.f1  IN ( SELECT sq2.f1 t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) );

It was done in Item_exists_subselect::exists2in_processor. When doing this the function checked whether the subquery under IN will be correlated. This check was done incorrectly: the outer reference sq1.`pk` was not taken into account because Item::collect_outer_ref_processor was never called for it.
The Item_exists_subselect::exists2in_processor called check_and_do_in_subquery_rewrites().
Here it was found out that the optimization for the obtained IN subquery cannot be applied
due to STRAIGHT_JOIN and the subquery is re-written back into an EXIST subquery.

SELECT STRAIGHT_JOIN sq1.f2 FROM  t1  AS sq1
 
WHERE sq1.f1  IN ( SELECT sq2.f1 t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) );
 
=>
 
SELECT STRAIGHT_JOIN sq1.f2 FROM  t1  AS sq1
 
WHERE EXISTS ( SELECT sq2.f1 t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq1.f1=sq2.f1);

Having misled by the bug the optimizer decided that the subquery predicate

 
 sq1.`pk` IN ( SELECT f1 FROM t1 )
 
(noformat}
 
has no outer references and it can be evaluated with materialization of the subquery
 
(noformat}
 
SELECT f1 FROM t1

Yet, when the executer tried to get the first key for a look-up into this table it discovered
that the field sq1.`pk` is not among read fields of sq2.

Comment by Igor Babaev [ 2017-04-22 ]

The fix for this bug was pushed into the 10.0 tree.

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