Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-2686

LP:675981 - Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89

    Details

    • Type: Bug
    • Status: Closed
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      The following query:

      EXPLAIN
      SELECT table1.f5
      FROM t2 AS table1 JOIN t1 AS table3 ON table3.f1
      WHERE table1.f1 IN (
      SELECT SUBQUERY1_t1.f2
      FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4
      );

      asserts in maria-5.3-mwl89 with the following backtrace:

      #8 0x00a72d98 in __assert_fail () from /lib/libc.so.6
      #9 0x08321c82 in sub_select_cache (join=0xae66d920, join_tab=0xae674068, end_of_records=true) at sql_select.cc:12914
      #10 0x08321ecc in sub_select (join=0xae66d920, join_tab=0xae673ea8, end_of_records=true) at sql_select.cc:13082
      #11 0x0832147b in do_select (join=0xae66d920, fields=0xae63248c, table=0x0, procedure=0x0) at sql_select.cc:12678
      #12 0x083077ac in JOIN::exec (this=0xae66d920) at sql_select.cc:2338
      #13 0x08238172 in subselect_single_select_engine::exec (this=0xae6331c0) at item_subselect.cc:2766
      #14 0x0823268f in Item_subselect::exec (this=0xae6330c8) at item_subselect.cc:552
      #15 0x0823288e in Item_in_subselect::exec (this=0xae6330c8) at item_subselect.cc:649
      #16 0x082341e4 in Item_in_subselect::val_bool (this=0xae6330c8) at item_subselect.cc:1327
      #17 0x081d18c2 in Item::val_bool_result (this=0xae6330c8) at item.h:783
      #18 0x081fd39e in Item_in_optimizer::val_int (this=0xae633828) at item_cmpfunc.cc:1893
      #19 0x0830554f in JOIN::exec (this=0xae668aa0) at sql_select.cc:1821
      #20 0x08307f17 in mysql_select (thd=0xa8891e0, rref_pointer_array=0xa88ac5c, tables=0xae631bc0, wild_num=0, fields=..., conds=0xae6330c8, og_num=0,
      order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xae633258, unit=0xa88a8bc, select_lex=0xa88ab58)
      at sql_select.cc:2548
      #21 0x083313dc in mysql_explain_union (thd=0xa8891e0, unit=0xa88a8bc, result=0xae633258) at sql_select.cc:18901
      #22 0x0829db0a in execute_sqlcom_select (thd=0xa8891e0, all_tables=0xae631bc0) at sql_parse.cc:5080
      #23 0x082947a3 in mysql_execute_command (thd=0xa8891e0) at sql_parse.cc:2281
      #24 0x082a022c in mysql_parse (thd=0xa8891e0,
      rawbuf=0xae631920 "EXPLAIN\nSELECT table1.f5\nFROM t2 AS table1 JOIN t1 AS table3 ON table3.f1\nWHERE table1.f1 IN\n(\nSELECT SUBQUERY1_t1.f2\nFROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 )", length=186, found_semicolon=0xae7ff228) at sql_parse.cc:6109
      #25 0x0829228c in dispatch_command (command=COM_QUERY, thd=0xa8891e0, packet=0xa8a1a71 "", packet_length=186) at sql_parse.cc:1209
      #26 0x082916f6 in do_command (thd=0xa8891e0) at sql_parse.cc:902
      #27 0x0828e7d4 in handle_one_connection (arg=0xa8891e0) at sql_connect.cc:1154
      #28 0x00bea919 in start_thread () from /lib/libpthread.so.0
      #29 0x00b2ccbe in clone () from /lib/libc.so.6

      Working EXPLAIN is vitally important for automatic testing, so this bug needs to be fixed before testing on mwl-89 can continue.

      Test case:
      CREATE TABLE t1 (f1 int,f2 int) ;
      INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);

      CREATE TABLE t2 (f1 int, f5 int) ;
      INSERT IGNORE INTO t2 VALUES (1,0);

      CREATE TABLE t3 (f4 int) ;
      INSERT IGNORE INTO t3 VALUES (0),(0);

      EXPLAIN
      SELECT table1.f5
      FROM t2 AS table1 JOIN t1 AS table3 ON table3.f1
      WHERE table1.f1 IN
      (
      SELECT SUBQUERY1_t1.f2
      FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );

      EXPLAIN from 5.3:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
      2 DEPENDENT SUBQUERY SUBQUERY1_t1 ALL NULL NULL NULL NULL 2 Using where
      2 DEPENDENT SUBQUERY SUBQUERY1_t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            timour Timour Katchaounov added a comment -

            Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89
            Even simpler test case (based on bug analysis):

            set @@optimizer_switch='semijoin=off';
            EXPLAIN
            SELECT * FROM t2
            WHERE f1 IN (SELECT SUBQUERY1_t1.f2
            FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );

            Show
            timour Timour Katchaounov added a comment - Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89 Even simpler test case (based on bug analysis): set @@optimizer_switch='semijoin=off'; EXPLAIN SELECT * FROM t2 WHERE f1 IN (SELECT SUBQUERY1_t1.f2 FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );
            Hide
            philipstoev Philip Stoev added a comment -

            Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89
            New test case that still crashes:

            CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ;
            INSERT IGNORE INTO t1 VALUES ('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'),('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'),('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0');

            CREATE TABLE t3 (f3 int) ;
            INSERT IGNORE INTO t3 VALUES ('2');

            CREATE TABLE t2 (f3 int) ;
            INSERT IGNORE INTO t2 VALUES ('7');

            EXPLAIN
            SELECT table2.f4 AS field1
            FROM t2 AS table1 JOIN t1 AS table2 ON table2.f6
            WHERE
            ( table2.f2 ) IN (
            SELECT SUBQUERY2_t1.f3
            FROM t3 AS SUBQUERY2_t1 JOIN (
            t1 AS SUBQUERY2_t2
            JOIN t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1
            )
            ON SUBQUERY2_t3.f2 )
            GROUP BY field1
            ORDER BY table2.f1
            LIMIT 10;

            Show
            philipstoev Philip Stoev added a comment - Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89 New test case that still crashes: CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ; INSERT IGNORE INTO t1 VALUES ('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'),('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'),('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0'); CREATE TABLE t3 (f3 int) ; INSERT IGNORE INTO t3 VALUES ('2'); CREATE TABLE t2 (f3 int) ; INSERT IGNORE INTO t2 VALUES ('7'); EXPLAIN SELECT table2.f4 AS field1 FROM t2 AS table1 JOIN t1 AS table2 ON table2.f6 WHERE ( table2.f2 ) IN ( SELECT SUBQUERY2_t1.f3 FROM t3 AS SUBQUERY2_t1 JOIN ( t1 AS SUBQUERY2_t2 JOIN t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1 ) ON SUBQUERY2_t3.f2 ) GROUP BY field1 ORDER BY table2.f1 LIMIT 10;
            Hide
            philipstoev Philip Stoev added a comment -

            Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89
            Second test case separated into a new bug 680005

            Show
            philipstoev Philip Stoev added a comment - Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89 Second test case separated into a new bug 680005
            Hide
            ratzpo Rasmus Johansson added a comment -

            Launchpad bug id: 675981

            Show
            ratzpo Rasmus Johansson added a comment - Launchpad bug id: 675981

              People

              • Assignee:
                timour Timour Katchaounov
                Reporter:
                philipstoev Philip Stoev
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: