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

    XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Closed (View Workflow)
    • 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)

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: