Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-83 Cost-based choice for the pushdown of subqueries to joined tables
  3. MDEV-4408

SQ pushdown: Assertion `cache != __null' fails in sub_select_cache

        Details

        • Type: Technical task
        • Status: Closed
        • Priority: Minor
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: 10.0.3
        • Component/s: None
        • Labels:
          None

          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)

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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;

                Show
                timour Timour Katchaounov added a comment - 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;
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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().
                Show
                timour Timour Katchaounov added a comment - 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().
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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.

                Show
                timour Timour Katchaounov added a comment - 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.
                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                OK to push after changes we discussed.

                Show
                sanja Oleksandr Byelkin added a comment - OK to push after changes we discussed.
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                fix pushed to 10.0-md83 on LP

                Show
                timour Timour Katchaounov added a comment - fix pushed to 10.0-md83 on LP

                  People

                  • Assignee:
                    timour Timour Katchaounov
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Time Tracking

                      Estimated:
                      Original Estimate - Not Specified
                      Not Specified
                      Remaining:
                      Remaining Estimate - 0 minutes
                      0m
                      Logged:
                      Time Spent - 1 week, 1 hour
                      1w 1h