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

Server crashes in Bitmap<64u>::intersect

Details

    Description

      create table t1 (v1 varchar(1)) engine=myisam ;
      create table t2 (v1 varchar(1)) engine=myisam ;
       
      select 1 from t1 where exists 
      	(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ;
      

      10.0 12f362c3338d803b665

      #4  0x00000000006017c8 in Bitmap<64u>::intersect (this=0x8f8f8f8f8f8f8ff7, map2=...) at /git/10.0/sql/sql_bitmap.h:154
      #5  0x0000000000896d93 in Item_field::update_table_bitmaps (this=0x7fde84d46c30) at /git/10.0/sql/item.h:2312
      #6  0x0000000000896e4c in Item_field::update_used_tables (this=0x7fde84d46c30) at /git/10.0/sql/item.h:2326
      #7  0x00000000008afe05 in Item_equal::update_used_tables (this=0x7fde84d478c0) at /git/10.0/sql/item_cmpfunc.cc:6444
      #8  0x000000000064e379 in st_select_lex::update_used_tables (this=0x7fde84cf88b8) at /git/10.0/sql/sql_lex.cc:3934
      #9  0x000000000064d663 in st_select_lex::optimize_unflattened_subqueries (this=0x7fde8d6540f8, const_only=false) at /git/10.0/sql/sql_lex.cc:3554
      #10 0x00000000007dd596 in JOIN::optimize_unflattened_subqueries (this=0x7fde84f644a0) at /git/10.0/sql/opt_subselect.cc:5301
      #11 0x00000000006958c5 in JOIN::optimize_inner (this=0x7fde84f644a0) at /git/10.0/sql/sql_select.cc:1953
      #12 0x0000000000692432 in JOIN::optimize (this=0x7fde84f644a0) at /git/10.0/sql/sql_select.cc:1041
      #13 0x000000000069a95e in mysql_select (thd=0x7fde8d650070, rref_pointer_array=0x7fde8d6543a0, tables=0x7fde84cf82c0, wild_num=0, fields=..., conds=0x7fde84f64238, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fde84f64480, unit=0x7fde8d653a08, select_lex=0x7fde8d6540f8) at /git/10.0/sql/sql_select.cc:3334
      #14 0x0000000000690374 in handle_select (thd=0x7fde8d650070, lex=0x7fde8d653940, result=0x7fde84f64480, setup_tables_done_option=0) at /git/10.0/sql/sql_select.cc:377
      #15 0x0000000000660fb3 in execute_sqlcom_select (thd=0x7fde8d650070, all_tables=0x7fde84cf82c0) at /git/10.0/sql/sql_parse.cc:5308
      #16 0x0000000000658f9e in mysql_execute_command (thd=0x7fde8d650070) at /git/10.0/sql/sql_parse.cc:2558
      #17 0x0000000000664194 in mysql_parse (thd=0x7fde8d650070, rawbuf=0x7fde84cf8088 "select 1 from t1 where exists \n(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j'))", length=106, parser_state=0x7fde9a6b7670) at /git/10.0/sql/sql_parse.cc:6644
      #18 0x0000000000655ff8 in dispatch_command (command=COM_QUERY, thd=0x7fde8d650070, packet=0x7fde92973071 "select 1 from t1 where exists \n(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ", packet_length=107) at /git/10.0/sql/sql_parse.cc:1301
      #19 0x000000000065525f in do_command (thd=0x7fde8d650070) at /git/10.0/sql/sql_parse.cc:1003
      #20 0x000000000078ba38 in do_handle_one_connection (thd_arg=0x7fde8d650070) at /git/10.0/sql/sql_connect.cc:1377
      #21 0x000000000078b786 in handle_one_connection (arg=0x7fde8d650070) at /git/10.0/sql/sql_connect.cc:1292
      #22 0x0000000000e4ae4e in pfs_spawn_thread (arg=0x7fde8d3fa3f0) at /git/10.0/storage/perfschema/pfs.cc:1861
      #23 0x00007fde998626ba in start_thread (arg=0x7fde9a6b8700) at pthread_create.c:333
      #24 0x00007fde98f0d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
      
      

      Introduced in commit d0d0f88f2cd4da23c2c2da702da51fb533e7fb8a
      Author: Varun Gupta <varun.gupta@mariadb.com>
      Date: Sun Jan 6 23:15:25 2019 +0530

      Attachments

        Issue Links

          Activity

            Approach 1
            Don't call update_used_tables for JOINS that have been cleaned up
            Patch
            http://lists.askmonty.org/pipermail/commits/2019-January/013290.html

            varun Varun Gupta (Inactive) added a comment - Approach 1 Don't call update_used_tables for JOINS that have been cleaned up Patch http://lists.askmonty.org/pipermail/commits/2019-January/013290.html
            varun Varun Gupta (Inactive) added a comment - - edited

            Approach 2

            JOIN::optimize_unflattened_subqueries

            for a certain select $X:
            at the end of its JOIN::optimize() call
            we call JOIN::optimize_unflattened_subqueries (this causes children of select $X be optimized)
            the suggestion is that at the end of JOIN::optimize_unflattened_subqueries we call
            this>select_lex->update_used_tables()

            varun Varun Gupta (Inactive) added a comment - - edited Approach 2 JOIN::optimize_unflattened_subqueries for a certain select $X: at the end of its JOIN::optimize() call we call JOIN::optimize_unflattened_subqueries (this causes children of select $X be optimized) the suggestion is that at the end of JOIN::optimize_unflattened_subqueries we call this>select_lex->update_used_tables()

            Also some further details

            CREATE TABLE t1 (a int,b int,
            UNIQUE (a), KEY (b)
            );
            INSERT INTO t1 VALUES (1,1), (2,1);
            CREATE TABLE st1 like t1;
            INSERT INTO st1 VALUES (1,1), (2,1);
            CREATE TABLE st2 like t1;
            INSERT INTO st2 VALUES (1,1), (2,1);
            

            Case 1

            MariaDB [test]> SELECT MAX(b), (SELECT count(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230;
            +--------+----------------------------------------------------+
            | MAX(b) | (SELECT count(*) FROM st1,st2 WHERE st2.b <= t1.b) |
            +--------+----------------------------------------------------+
            |   NULL |                                               NULL |
            +--------+----------------------------------------------------+
            1 row in set (0.024 sec)
             
            h3. Case 2
            MariaDB [test]> SELECT MAX(b), (SELECT count(*) FROM st1,st2 WHERE 1=0) FROM t1 WHERE a = 230;
            +--------+------------------------------------------+
            | MAX(b) | (SELECT count(*) FROM st1,st2 WHERE 1=0) |
            +--------+------------------------------------------+
            |   NULL |                                        0 |
            +--------+------------------------------------------+
            1 row in set (0.003 sec)
            

            So we have two cases , in case 1 we don't execute the subquery while in the 2nd case we execute the subquery.
            Both the queries have an IMPOSSIBLE WHERE in the parant select.
            So to keep the behaviour constant we would be executing the subqueries even when we have an IMPOSSIBLE WHERE in the parent.

            varun Varun Gupta (Inactive) added a comment - Also some further details CREATE TABLE t1 (a int ,b int , UNIQUE (a), KEY (b) ); INSERT INTO t1 VALUES (1,1), (2,1); CREATE TABLE st1 like t1; INSERT INTO st1 VALUES (1,1), (2,1); CREATE TABLE st2 like t1; INSERT INTO st2 VALUES (1,1), (2,1); Case 1 MariaDB [test]> SELECT MAX(b), (SELECT count(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; +--------+----------------------------------------------------+ | MAX(b) | (SELECT count(*) FROM st1,st2 WHERE st2.b <= t1.b) | +--------+----------------------------------------------------+ | NULL | NULL | +--------+----------------------------------------------------+ 1 row in set (0.024 sec)   h3. Case 2 MariaDB [test]> SELECT MAX(b), (SELECT count(*) FROM st1,st2 WHERE 1=0) FROM t1 WHERE a = 230; +--------+------------------------------------------+ | MAX(b) | (SELECT count(*) FROM st1,st2 WHERE 1=0) | +--------+------------------------------------------+ | NULL | 0 | +--------+------------------------------------------+ 1 row in set (0.003 sec) So we have two cases , in case 1 we don't execute the subquery while in the 2nd case we execute the subquery. Both the queries have an IMPOSSIBLE WHERE in the parant select. So to keep the behaviour constant we would be executing the subqueries even when we have an IMPOSSIBLE WHERE in the parent.
            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2019-January/013297.html

            Looking at the patch, this result difference:

            @@ -6647,7 +6647,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
             2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
             SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
             COUNT(f1)	f4
            -0	NULL
            +0	7
             EXPLAIN
             SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
             id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
            @@ -6655,7 +6655,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
             2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
             SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
             COUNT(f1)	f4
            -0	0
            +0	1
             EXPLAIN
             SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
             id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
            

            The testcase for it is:

            #
            # LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
            # main query and implicit grouping
            #
            CREATE TABLE t1 (f1 int) engine=MyISAM;
            INSERT INTO t1 VALUES (7),(8);
            CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
            INSERT INTO t2 VALUES (3,'f');
             
            SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
            SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
            

            The change in .result matche the discussion between Varun and me:

            • The parent query's join doesn't produce any rows.
            • But aggregate function is present without GROUP BY, which means implied grouping is performed
            • Implied grouping generates a special summary row
              • aggregate functions have summary values
              • references to table columns are not allowed by the standard, but MySQL's
                Extended GROUP BY syntax allows them. And they all have NULL values.
              • Expressions are allowed and are computed. (if an expression has column references, the expression is still computed, column references have NULL values)

            We consider a subquery to be a kind of expression. So, in the above example, the subqueries must be computed, with outside references having NULL values.

            But when I look at the bug which introduced the testcase: https://bugs.launchpad.net/maria/+bug/985667

            I see that Timour has intentionally changed the behavior that we think is correct to something that we think is not correct.

            This requires careful analysis.

            psergei Sergei Petrunia added a comment - Looking at the patch, this result difference: @@ -6647,7 +6647,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6655,7 +6655,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra The testcase for it is: # # LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in # main query and implicit grouping # CREATE TABLE t1 (f1 int ) engine=MyISAM; INSERT INTO t1 VALUES (7),(8); CREATE TABLE t2 (f2 int , f3 varchar (1)) engine=MyISAM; INSERT INTO t2 VALUES (3, 'f' );   SELECT COUNT (f1), ( SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v' = f3; SELECT COUNT (f1), exists( SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v' = f3; The change in .result matche the discussion between Varun and me: The parent query's join doesn't produce any rows. But aggregate function is present without GROUP BY, which means implied grouping is performed Implied grouping generates a special summary row aggregate functions have summary values references to table columns are not allowed by the standard, but MySQL's Extended GROUP BY syntax allows them. And they all have NULL values. Expressions are allowed and are computed. (if an expression has column references, the expression is still computed, column references have NULL values) We consider a subquery to be a kind of expression. So, in the above example, the subqueries must be computed, with outside references having NULL values. But when I look at the bug which introduced the testcase: https://bugs.launchpad.net/maria/+bug/985667 I see that Timour has intentionally changed the behavior that we think is correct to something that we think is not correct. This requires careful analysis.
            psergei Sergei Petrunia added a comment - - edited

            In MySQL 8.0, one needs to remove ONLY_FULL_GROUP_BY from @@sql_mode to get the
            query accepted. Then, the output is

            mysql> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
            +-----------+------+
            | COUNT(f1) | f4   |
            +-----------+------+
            |         0 | NULL |
            +-----------+------+
             
            mysql> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
            +-----------+------+
            | COUNT(f1) | f4   |
            +-----------+------+
            |         0 |    0 |
            +-----------+------+
            

            That is, it doesn't compute the subqueries.

            psergei Sergei Petrunia added a comment - - edited In MySQL 8.0, one needs to remove ONLY_FULL_GROUP_BY from @@sql_mode to get the query accepted. Then, the output is mysql> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; +-----------+------+ | COUNT(f1) | f4 | +-----------+------+ | 0 | NULL | +-----------+------+   mysql> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; +-----------+------+ | COUNT(f1) | f4 | +-----------+------+ | 0 | 0 | +-----------+------+ That is, it doesn't compute the subqueries.

            Look at this code in item_subselect.cc:

            void Item_singlerow_subselect::no_rows_in_result()
            {
              /*
                Subquery predicates outside of the SELECT list must be evaluated in order
                to possibly filter the special result row generated for implicit grouping
                if the subquery is in the HAVING clause.
                If the predicate is constant, we need its actual value in the only result
                row for queries with implicit grouping.
              */
              if (parsing_place != SELECT_LIST || const_item())
                return;
              value= Item_cache::get_cache(new Item_null());
            

            It makes non-constant subquery in select list to produce NULL value for implict-grouping's NULL-summary-row.

            const_item gets its value from const_item_cache:

            bool Item_subselect::const_item() const
            {
              DBUG_ASSERT(thd);
              return (thd->lex->context_analysis_only ?
                      FALSE :
                      forced_const || const_item_cache);
            }
            

            .

            Here, before the patch we have const_item_cache=false, even if subquery_item->used_tables()=0 and the subquery item is marked as uncorrelated.

            psergei Sergei Petrunia added a comment - Look at this code in item_subselect.cc: void Item_singlerow_subselect::no_rows_in_result() { /* Subquery predicates outside of the SELECT list must be evaluated in order to possibly filter the special result row generated for implicit grouping if the subquery is in the HAVING clause. If the predicate is constant, we need its actual value in the only result row for queries with implicit grouping. */ if (parsing_place != SELECT_LIST || const_item()) return ; value= Item_cache::get_cache( new Item_null()); It makes non-constant subquery in select list to produce NULL value for implict-grouping's NULL-summary-row. const_item gets its value from const_item_cache: bool Item_subselect::const_item() const { DBUG_ASSERT(thd); return (thd->lex->context_analysis_only ? FALSE : forced_const || const_item_cache); } . Here, before the patch we have const_item_cache=false, even if subquery_item->used_tables()=0 and the subquery item is marked as uncorrelated.

            Agreed to push the first patch as it is a more limited, conservative solution.

            psergei Sergei Petrunia added a comment - Agreed to push the first patch as it is a more limited, conservative solution.

            People

              varun Varun Gupta (Inactive)
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.