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

Crash when executing PS for query with eliminated subquery using view

Details

    Description

      CREATE VIEW t AS SELECT 1 AS a;
      PREPARE s FROM 'SELECT EXISTS (SELECT 1 FROM t GROUP BY a IN (SELECT a FROM t))';
      EXECUTE s;
      

      Leads to:

      10.4.33 d4be70afb4eaffbd1c2dd3326e19752770b08bc6 (Optimized)

      Core was generated by `/test/mtest/MD161123-mariadb-10.4.33-linux-x86_64-opt/bin/mariadbd --no-default'.
      Program terminated with signal SIGSEGV, Segmentation fault.
      #0  0x000055b92328b901 in st_select_lex::restore_item_list_names (
          this=this@entry=0x0) at /test/mtest/10.4_opt/sql/sql_lex.cc:10725
      [Current thread is 1 (Thread 0x14573979c700 (LWP 3347462))]
      (gdb) bt
      #0  0x000055b92328b901 in st_select_lex::restore_item_list_names (this=this@entry=0x0) at /test/mtest/10.4_opt/sql/sql_lex.cc:10725
      #1  0x000055b92325dc50 in mysql_derived_reinit (thd=0x1456fc000c48, lex=<optimized out>, derived=0x1456fc01aa28) at /test/mtest/10.4_opt/sql/sql_derived.cc:1343
      #2  0x000055b92325e9d8 in mysql_handle_single_derived (lex=0x1456fc0174c8, derived=derived@entry=0x1456fc01aa28, phases=phases@entry=128) at /test/mtest/10.4_opt/sql/sql_derived.cc:200
      #3  0x000055b9233682d1 in mysql_make_view (thd=thd@entry=0x1456fc000c48, share=share@entry=0x1456fc01d9d0, table=table@entry=0x1456fc01aa28, open_view_no_parse=open_view_no_parse@entry=false) at /test/mtest/10.4_opt/sql/sql_view.cc:1261
      #4  0x000055b923237d5b in open_table (thd=0x1456fc000c48, table_list=0x1456fc01aa28, ot_ctx=0x145739797a80) at /test/mtest/10.4_opt/sql/sql_base.cc:2029
      #5  0x000055b92323b75e in open_and_process_table (ot_ctx=0x145739797a80, has_prelocking_list=false, prelocking_strategy=0x145739797b70, flags=0, counter=0x145739797b1c, tables=0x1456fc01aa28, thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_base.cc:3915
      #6  open_tables (thd=thd@entry=0x1456fc000c48, options=@0x1456fc018ae0: {m_options = DDL_options_st::OPT_NONE}, start=start@entry=0x145739797b08, counter=counter@entry=0x145739797b1c, flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x145739797b70) at /test/mtest/10.4_opt/sql/sql_base.cc:4397
      #7  0x000055b92323c25a in open_and_lock_tables (thd=thd@entry=0x1456fc000c48, options=<optimized out>, tables=<optimized out>, tables@entry=0x1456fc019bd8, derived=derived@entry=true, flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x145739797b70) at /test/mtest/10.4_opt/sql/sql_base.cc:5344
      #8  0x000055b92329528d in open_and_lock_tables (flags=0, derived=true, tables=0x1456fc019bd8, thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_base.h:503
      #9  execute_sqlcom_select (thd=0x1456fc000c48, all_tables=0x1456fc019bd8) at /test/mtest/10.4_opt/sql/sql_parse.cc:6396
      #10 0x000055b9232a4047 in mysql_execute_command (thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_parse.cc:3978
      #11 0x000055b9232bc4a6 in Prepared_statement::execute (this=0x1456fc0170e8, expanded_query=<optimized out>, open_cursor=false) at /test/mtest/10.4_opt/sql/sql_prepare.cc:5025
      #12 0x000055b9232bc6a1 in Prepared_statement::execute_loop (packet=<optimized out>, packet_end=<optimized out>, open_cursor=<optimized out>, expanded_query=0x145739799a70, this=0x1456fc0170e8) at /test/mtest/10.4_opt/sql/sql_prepare.cc:4472
      #13 Prepared_statement::execute_loop (this=0x1456fc0170e8, expanded_query=0x145739799a70, open_cursor=<optimized out>, packet=<optimized out>, packet_end=<optimized out>) at /test/mtest/10.4_opt/sql/sql_prepare.cc:4421
      #14 0x000055b9232bc9db in mysql_sql_stmt_execute (thd=thd@entry=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_prepare.cc:3531
      #15 0x000055b9232a0a67 in mysql_execute_command (thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_parse.cc:3994
      #16 0x000055b9232a67ba in mysql_parse (thd=0x1456fc000c48, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /test/mtest/10.4_opt/sql/sql_parse.cc:8014
      #17 0x000055b9232a8dd5 in dispatch_command (command=COM_QUERY, thd=0x1456fc000c48, packet=<optimized out>, packet_length=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /test/mtest/10.4_opt/sql/sql_class.h:1242
      #18 0x000055b9232ab4cf in do_command (thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_parse.cc:1378
      #19 0x000055b9233a5b7e in do_handle_one_connection (connect=connect@entry=0x55b9264717b8) at /test/mtest/10.4_opt/sql/sql_connect.cc:1419
      #20 0x000055b9233a5caf in handle_one_connection (arg=0x55b9264717b8) at /test/mtest/10.4_opt/sql/sql_connect.cc:1323
      #21 0x000014575ffab609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #22 0x000014575fb97133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      Bug confirmed present in:
      MariaDB: 10.4.33 (dbg), 10.4.33 (opt), 10.5.24 (dbg), 10.5.24 (opt)

      Attachments

        Issue Links

          Activity

            ramesh Ramesh Sivaraman added a comment - - edited

            The crash occurs after this commit

            commit eb8053b37756fe99e8ca12bbd966726d9f21c91a (HEAD, origin/bb-10.4-MDEV-31995)
            Author: Rex <rex.johnston@mariadb.com>
            Date:   Mon Sep 25 12:56:30 2023 +1100
             
                MDEV-31995 Bogus error executing PS for query using CTE with renaming of columns
            

            ramesh Ramesh Sivaraman added a comment - - edited The crash occurs after this commit commit eb8053b37756fe99e8ca12bbd966726d9f21c91a (HEAD, origin/bb-10.4-MDEV-31995) Author: Rex <rex.johnston@mariadb.com> Date: Mon Sep 25 12:56:30 2023 +1100   MDEV-31995 Bogus error executing PS for query using CTE with renaming of columns
            Johnston Rex Johnston added a comment - - edited

            One of MariaDB's query simplification techniques, obliquely referred to here https://mariadb.com/kb/en/optimizing-group-by/ is removing group by clauses in sub-queries where the outer condition allows this. These conditions include exists, in, > and <, where duplicate rows are irrelevant to the outcome. Removing these clauses allows the query planner more latitude in selecting a good execution plan. This simplification takes place during the prepare phase of join processing. The whole "GROUP BY" clause is removed, both simple column references and expressions. Any containing unit in sub-selects that have been removed have the select_lex structure removed, the pointer set to null (this is important as you'll see later).

            Any sub-selects, removed by the above simplification, that also contain a view will be affected by this bug.

            Consider the following example

            create table t1 (a int, b int);
            insert into t1 values (1,2),(3,4);
            create view v1 as select * from t1;
            PREPARE s FROM 'select exists (select * from t1 group by a in (select * from v1))';
            execute s;
            

            A view is used in a sub-query clause,

            select * from v1
            

            which is part of a group by outer clause.

            group by a in (select * from v1)
            

            When preparing the statement,

            PREPARE s FROM 'select exists (select * from t1 group by a in (select * from v1))';
            

            mysql_test_select() will partially execute the statement and build the join structure(s) required for execution.
            During JOIN::prepare(), remove_redundant_subquery_clauses() notices that

            group by a in (select * from v1)
            

            is redundant, as it is in a sub-query, there are no aggregate functions and no HAVING clause, so removes it.

            This leaves the table structures associated with that instance of the view with it's containing unit having no select_lex pointers.
            When the statement is executed,

            execute s;
            

            mysql_derived_reinit() is called to reinitialize the unit and select_lex item list names.
            As there is no select_lex (removed as described above), we crash.

            Note that if a normal table is removed

            PREPARE s FROM 'select exists (select * from t1 group by a in (select a from t1))';
            execute s;
            

            mysql_derived_reinit() is not called.

            I should also note that a stored procedure can be used to cause this crash.

            create procedure aproc() select exists (select * from t1 group by a in (select * from v1));
            call aproc();
            call aproc();
            

            During the second execution, mysql_derived_reinit() will be called on the emptied unit that used to represent

            select * from v1
            

            and crash.

            CTEs and derived tables are not affected as only a view has a table structure left over from a previous prepare, they are both "local" to the currently executing query in this respect.

            Fixed by ensuring restore_item_list_names() is never called from a null pointer.

            Johnston Rex Johnston added a comment - - edited One of MariaDB's query simplification techniques, obliquely referred to here https://mariadb.com/kb/en/optimizing-group-by/ is removing group by clauses in sub-queries where the outer condition allows this. These conditions include exists, in, > and <, where duplicate rows are irrelevant to the outcome. Removing these clauses allows the query planner more latitude in selecting a good execution plan. This simplification takes place during the prepare phase of join processing. The whole "GROUP BY" clause is removed, both simple column references and expressions. Any containing unit in sub-selects that have been removed have the select_lex structure removed, the pointer set to null (this is important as you'll see later). Any sub-selects, removed by the above simplification, that also contain a view will be affected by this bug. Consider the following example create table t1 (a int , b int ); insert into t1 values (1,2),(3,4); create view v1 as select * from t1; PREPARE s FROM 'select exists (select * from t1 group by a in (select * from v1))' ; execute s; A view is used in a sub-query clause, select * from v1 which is part of a group by outer clause. group by a in ( select * from v1) When preparing the statement, PREPARE s FROM 'select exists (select * from t1 group by a in (select * from v1))' ; mysql_test_select() will partially execute the statement and build the join structure(s) required for execution. During JOIN::prepare(), remove_redundant_subquery_clauses() notices that group by a in ( select * from v1) is redundant, as it is in a sub-query, there are no aggregate functions and no HAVING clause, so removes it. This leaves the table structures associated with that instance of the view with it's containing unit having no select_lex pointers. When the statement is executed, execute s; mysql_derived_reinit() is called to reinitialize the unit and select_lex item list names. As there is no select_lex (removed as described above), we crash. Note that if a normal table is removed PREPARE s FROM 'select exists (select * from t1 group by a in (select a from t1))' ; execute s; mysql_derived_reinit() is not called. I should also note that a stored procedure can be used to cause this crash. create procedure aproc() select exists ( select * from t1 group by a in ( select * from v1)); call aproc(); call aproc(); During the second execution, mysql_derived_reinit() will be called on the emptied unit that used to represent select * from v1 and crash. CTEs and derived tables are not affected as only a view has a table structure left over from a previous prepare, they are both "local" to the currently executing query in this respect. Fixed by ensuring restore_item_list_names() is never called from a null pointer.

            Ok to push into 10.4

            igor Igor Babaev (Inactive) added a comment - Ok to push into 10.4

            People

              Johnston Rex Johnston
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.