Cost-based choice for the pushdown of subqueries to joined tables (MDEV-83)

[MDEV-4408] SQ pushdown: Assertion `cache != __null' fails in sub_select_cache Created: 2013-04-22  Updated: 2013-05-20  Resolved: 2013-05-20

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.3

Type: Technical task Priority: Minor
Reporter: Elena Stepanova Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 Description    

mysqld: /data/bzr/10.0-mdev83/sql/sql_select.cc:16723: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed.
 
130422  4:09:26 [ERROR] mysqld got signal 6 ;
 

#7  0x00007f5e41b27192 in __GI___assert_fail (assertion=0xd5f55b "cache != __null", file=0xd5df98 "/data/bzr/10.0-mdev83/sql/sql_select.cc", line=16723, function=0xd60aa0 "enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool)") at assert.c:103
 
#8  0x000000000067292c in sub_select_cache (join=0x7f5e2c0360f8, join_tab=0x7f5e2c045688, end_of_records=false) at /data/bzr/10.0-mdev83/sql/sql_select.cc:16723
 
#9  0x000000000067344a in evaluate_join_record (join=0x7f5e2c0360f8, join_tab=0x7f5e2c045358, error=0) at /data/bzr/10.0-mdev83/sql/sql_select.cc:17159
 
#10 0x0000000000672d43 in sub_select (join=0x7f5e2c0360f8, join_tab=0x7f5e2c045358, end_of_records=false) at /data/bzr/10.0-mdev83/sql/sql_select.cc:16937
 
#11 0x0000000000672602 in do_select (join=0x7f5e2c0360f8, fields=0x7f5e2c02b8a8, table=0x0, procedure=0x0) at /data/bzr/10.0-mdev83/sql/sql_select.cc:16607
 
#12 0x0000000000650f8a in JOIN::exec_inner (this=0x7f5e2c0360f8) at /data/bzr/10.0-mdev83/sql/sql_select.cc:3019
 
#13 0x000000000064e2cb in JOIN::exec (this=0x7f5e2c0360f8) at /data/bzr/10.0-mdev83/sql/sql_select.cc:2310
 
#14 0x0000000000884abe in subselect_single_select_engine::exec (this=0x7f5e2c02cb80) at /data/bzr/10.0-mdev83/sql/item_subselect.cc:3756
 
#15 0x000000000087b957 in Item_subselect::exec (this=0x7f5e2c02d6c0) at /data/bzr/10.0-mdev83/sql/item_subselect.cc:734
 
#16 0x000000000087e130 in Item_exists_subselect::val_bool (this=0x7f5e2c02d6c0) at /data/bzr/10.0-mdev83/sql/item_subselect.cc:1648
 
#17 0x000000000082450e in Item_cond_or::val_int (this=0x7f5e2c069448) at /data/bzr/10.0-mdev83/sql/item_cmpfunc.cc:4700
 
#18 0x00000000006725cc in do_select (join=0x7f5e2c02db30, fields=0x7f5e2c02def8, table=0x0, procedure=0x0) at /data/bzr/10.0-mdev83/sql/sql_select.cc:16604
 
#19 0x0000000000650f8a in JOIN::exec_inner (this=0x7f5e2c02db30) at /data/bzr/10.0-mdev83/sql/sql_select.cc:3019
 
#20 0x000000000064e2cb in JOIN::exec (this=0x7f5e2c02db30) at /data/bzr/10.0-mdev83/sql/sql_select.cc:2310
 
#21 0x0000000000651839 in mysql_select (thd=0x28e9810, rref_pointer_array=0x28ec7c0, tables=0x7f5e2c007a00, wild_num=0, fields=..., conds=0x7f5e2c02d7f8, og_num=1, order=0x7f5e2c02da10, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f5e2c02db10, unit=0x28ebe88, select_lex=0x28ec560) at /data/bzr/10.0-mdev83/sql/sql_select.cc:3247
 
#22 0x0000000000648023 in handle_select (thd=0x28e9810, lex=0x28ebdd0, result=0x7f5e2c02db10, setup_tables_done_option=0) at /data/bzr/10.0-mdev83/sql/sql_select.cc:376
 
#23 0x000000000061e0ed in execute_sqlcom_select (thd=0x28e9810, all_tables=0x7f5e2c007a00) at /data/bzr/10.0-mdev83/sql/sql_parse.cc:4804
 
#24 0x0000000000616797 in mysql_execute_command (thd=0x28e9810) at /data/bzr/10.0-mdev83/sql/sql_parse.cc:2268
 
#25 0x0000000000620957 in mysql_parse (thd=0x28e9810, rawbuf=0x7f5e2c0075e8 "SELECT  COUNT(*) AS cnt FROM t1, ( SELECT b FROM t2 ) AS t2_sq\nWHERE b < ANY ( SELECT a FROM t1, t2 ) \nOR EXISTS ( SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c = t3_1.c AND t3_2.c <> b  )  \nORDER BY cn"..., length=201, parser_state=0x7f5e3720a520) at /data/bzr/10.0-mdev83/sql/sql_parse.cc:5927
 
#26 0x0000000000613ad2 in dispatch_command (command=COM_QUERY, thd=0x28e9810, packet=0x28ede21 "", packet_length=201) at /data/bzr/10.0-mdev83/sql/sql_parse.cc:1091
 
#27 0x0000000000613001 in do_command (thd=0x28e9810) at /data/bzr/10.0-mdev83/sql/sql_parse.cc:810
 
#28 0x0000000000728c86 in do_handle_one_connection (thd_arg=0x28e9810) at /data/bzr/10.0-mdev83/sql/sql_connect.cc:1266
 
#29 0x00000000007289f4 in handle_one_connection (arg=0x28e9810) at /data/bzr/10.0-mdev83/sql/sql_connect.cc:1181
 
#30 0x0000000000992296 in pfs_spawn_thread (arg=0x28777d0) at /data/bzr/10.0-mdev83/storage/perfschema/pfs.cc:1015
 
#31 0x00007f5e428f7e9a in start_thread (arg=0x7f5e3720b700) at pthread_create.c:308
 
#32 0x00007f5e41bebcbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
 

revision-id: timour@askmonty.org-20130417090331-mdqmyx1dwkciogqn
 
revno: 3611
 
branch-nick: 10.0-mdev83

Could not reproduce on current 10.0-base, which makes me assume it's either related to the new code in 10.0-mdev83, or to a different plan being triggered.

CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (9),(3),(4),(2),(5),(3),(1),(3),(6),(7),(5),(1),(2),(4),(9),(5);
 
 
 
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (9);
 
 
 
CREATE TABLE t3 (c INT) ENGINE=MyISAM;
 
INSERT INTO t3 VALUES (4),(6);
 
 
 
SELECT  COUNT(*) AS cnt FROM t1, ( SELECT b FROM t2 ) AS t2_sq
 
WHERE b < ANY ( SELECT a FROM t1, t2 ) 
OR EXISTS ( SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c = t3_1.c AND t3_2.c <> b )  
ORDER BY cnt;

EXPLAIN:

EXPLAIN EXTENDED
 
SELECT  COUNT(*) AS cnt FROM t1, ( SELECT b FROM t2 ) AS t2_sq
 
WHERE b < ANY ( SELECT a FROM t1, t2 ) 
OR EXISTS ( SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c = t3_1.c AND t3_2.c <> b )  
ORDER BY cnt;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	Using temporary
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	16	100.00	Using where; Subqueries: 4 3
 
4	DEPENDENT SUBQUERY	t3_1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 
4	DEPENDENT SUBQUERY	t3_2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
 
3	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	16	100.00	
Warnings:
 
Note	1276	Field or reference 'b' of SELECT #4 was resolved in SELECT #1
 
Note	1003	select count(0) AS `cnt` from `test`.`t1` where (<nop>(<in_optimizer>(9,((select max(`test`.`t1`.`a`) from `test`.`t1`) > <cache>(9)))) or <expr_cache><9>(exists(select 1 from `test`.`t3` `t3_1` join `test`.`t3` `t3_2` where ((`test`.`t3_2`.`c` = `test`.`t3_1`.`c`) and (`test`.`t3_1`.`c` <> 9))))) order by count(0)



 Comments   
Comment by Timour Katchaounov (Inactive) [ 2013-05-09 ]

A bit shorter variant of the query:

SELECT COUNT(b)
FROM t1, (SELECT b FROM t2) AS t2_sq
WHERE b < ANY (SELECT a FROM t1)
OR EXISTS (SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c <> b)
GROUP BY a;

Comment by Timour Katchaounov (Inactive) [ 2013-05-10 ]

Analysis:

  • The WHERE clause is determined incorrectly to be dependent on OUTER_REF_TABLE_BIT.
  • As a result, make_join_select() determines that JOIN::outer_ref_cond = the where clause.
  • The optimizer decides to use JOIN_CACHE_BNL for the execution of the EXISTS subquery.
  • Execution proceeds normally, then the outer join is being cleaned up by the outermost
    JOIN::exec_inner:
    curr_join->join_free(); /* Free quick selects */
    This cleanup also deletes the JOIN_CACHE_BNL of the EXISTS subquery.
  • Close to the end of JOIN::exec_inner, it calls do_select again:
    result->send_result_set_metadata((procedure ? curr_join->procedure_fields_list :
    *curr_fields_list),
    Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);
    error= do_select(curr_join, curr_fields_list, NULL, procedure);
  • In turn do_select calls join->outer_ref_cond->val_int(), which triggers the evaluation of the
    EXISTS subquery.
  • This leads to a crash because the JOIN_CACHE_BNL object was already deleted by
    curr_join->join_free().
Comment by Timour Katchaounov (Inactive) [ 2013-05-10 ]

One step closer to the reason why the WHERE clause becomes dependent on OUTER_REF_TABLE_BIT:

In the non-modified code the ANY subquery is rewitten to:
"<nop>(<in_optimizer>(9,9 < any (select `test`.`t1`.`a` from `test`.`t1`)))"
while in mdev-83 it is rewritten to:
"<nop>(<in_optimizer>(9,((select max(`test`.`t1`.`a`) from `test`.`t1`) > <cache>(9))))"

The object Item_cache_int<9> created inside Item_in_optimizer::fix_left is the one
that depends on OUTER_REF_TABLE_BIT.

Comment by Oleksandr Byelkin [ 2013-05-20 ]

OK to push after changes we discussed.

Comment by Timour Katchaounov (Inactive) [ 2013-05-20 ]

fix pushed to 10.0-md83 on LP

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