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

Server crash or assertion failure in upon 2nd execution of PS with views and HAVING

Details

    Description

      It is possible that the problem is limited to degenerate use cases like the one below. If the analysis confirms it, it can be demoted from a blocker.

      CREATE TABLE t (f INT);
      INSERT INTO t VALUES (1),(2); # Optional, fails either way
      CREATE VIEW v1 AS SELECT 1 AS a;
      CREATE VIEW v2 AS SELECT a FROM v1;
       
      PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
      EXECUTE stmt;
      EXECUTE stmt;
       
      # Cleanup
      DROP VIEW v1;
      DROP VIEW v2;
      DROP TABLE t;
      

      10.4 c6ef9b1c debug

      mysqld: /data/src/10.4/sql/table.cc:6470: void TABLE_LIST::set_check_materialized(): Assertion `!derived->is_excluded()' failed.
      230504 17:12:19 [ERROR] mysqld got signal 6 ;
       
      #9  0x00007fc497253df2 in __GI___assert_fail (assertion=0x55b58203ea60 "!derived->is_excluded()", file=0x55b582039240 "/data/src/10.4/sql/table.cc", line=6470, function=0x55b58203ea00 "void TABLE_LIST::set_check_materialized()") at ./assert/assert.c:101
      #10 0x000055b58023deb4 in TABLE_LIST::set_check_materialized (this=0x62b00008e4f8) at /data/src/10.4/sql/table.cc:6470
      #11 0x000055b57fdaa1ff in TABLE_LIST::set_materialized_derived (this=0x62b00008e4f8) at /data/src/10.4/sql/table.h:2883
      #12 0x000055b580253187 in TABLE_LIST::init_derived (this=0x62b00008e4f8, thd=0x62b00005b208, init_view=true) at /data/src/10.4/sql/table.cc:9241
      #13 0x000055b57fe3821e in mysql_derived_init (thd=0x62b00005b208, lex=0x62b00008c238, derived=0x62b00008e4f8) at /data/src/10.4/sql/sql_derived.cc:564
      #14 0x000055b57fe35aec in mysql_handle_derived (lex=0x62b00008c238, phases=1) at /data/src/10.4/sql/sql_derived.cc:123
      #15 0x000055b57fd8e8de in open_and_lock_tables (thd=0x62b00005b208, options=..., tables=0x62b00008e4f8, derived=true, flags=0, prelocking_strategy=0x7fc48f4daa00) at /data/src/10.4/sql/sql_base.cc:5353
      #16 0x000055b57fce8616 in open_and_lock_tables (thd=0x62b00005b208, tables=0x62b00008e4f8, derived=true, flags=0) at /data/src/10.4/sql/sql_base.h:503
      #17 0x000055b57ff216a3 in execute_sqlcom_select (thd=0x62b00005b208, all_tables=0x62b00008e4f8) at /data/src/10.4/sql/sql_parse.cc:6384
      #18 0x000055b57ff0f638 in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3966
      #19 0x000055b57ff7ee7f in Prepared_statement::execute (this=0x619000084388, expanded_query=0x7fc48f4dcb30, open_cursor=false) at /data/src/10.4/sql/sql_prepare.cc:5024
      #20 0x000055b57ff7a4bf in Prepared_statement::execute_loop (this=0x619000084388, expanded_query=0x7fc48f4dcb30, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/10.4/sql/sql_prepare.cc:4493
      #21 0x000055b57ff74335 in mysql_sql_stmt_execute (thd=0x62b00005b208) at /data/src/10.4/sql/sql_prepare.cc:3577
      #22 0x000055b57ff0f67d in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3982
      #23 0x000055b57ff2b2fb in mysql_parse (thd=0x62b00005b208, rawbuf=0x62b000062228 "EXECUTE stmt", length=12, parser_state=0x7fc48f4de860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7998
      #24 0x000055b57ff017a7 in dispatch_command (command=COM_QUERY, thd=0x62b00005b208, packet=0x62900023f209 "EXECUTE stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
      #25 0x000055b57fefe320 in do_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:1378
      #26 0x000055b5802fc31c in do_handle_one_connection (connect=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1420
      #27 0x000055b5802fbc33 in handle_one_connection (arg=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1324
      #28 0x000055b580f6674c in pfs_spawn_thread (arg=0x615000003508) at /data/src/10.4/storage/perfschema/pfs.cc:1869
      #29 0x00007fc4972a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      #30 0x00007fc4973285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
      

      10.4 c6ef9b1c non-debug

      #2  <signal handler called>
      #3  0x000055871ef47ec6 in st_select_lex::mark_as_dependent (this=this@entry=0x7fb77407e478, thd=thd@entry=0x7fb774000c58, last=last@entry=0x7fb77407e478, dependency=0x7fb77407e950) at /data/src/10.4/sql/sql_lex.cc:2831
      #4  0x000055871f18ce00 in mark_as_dependent (thd=thd@entry=0x7fb774000c58, last=0x7fb77407e478, current=0x7fb77407e478, resolved_item=resolved_item@entry=0x7fb77407e950, mark_item=<optimized out>, suppress_warning_output=suppress_warning_output@entry=false) at /data/src/10.4/sql/item.cc:5119
      #5  0x000055871f19d88d in Item_field::fix_outer_field (this=this@entry=0x7fb77407e950, thd=thd@entry=0x7fb774000c58, from_field=from_field@entry=0x7fb784c8f9d0, reference=reference@entry=0x7fb7741283e8) at /data/src/10.4/sql/item.cc:5810
      #6  0x000055871f19e347 in Item_field::fix_fields (this=0x7fb77407e950, thd=0x7fb774000c58, reference=0x7fb7741283e8) at /data/src/10.4/sql/item.cc:6013
      #7  0x000055871f19f06c in Item::fix_fields_if_needed (ref=0x7fb7741283e8, thd=0x7fb774000c58, this=0x7fb77407e950) at /data/src/10.4/sql/item.h:964
      #8  Item::fix_fields_if_needed (ref=0x7fb7741283e8, thd=0x7fb774000c58, this=0x7fb77407e950) at /data/src/10.4/sql/item.h:964
      #9  Item_direct_view_ref::fix_fields (this=0x7fb7740883b0, thd=0x7fb774000c58, reference=0x7fb77407bb50) at /data/src/10.4/sql/item.cc:9071
      #10 0x000055871ef0eac9 in Item::fix_fields_if_needed (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:966
      #11 Item::fix_fields_if_needed (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:964
      #12 Item::fix_fields_if_needed_for_scalar (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:970
      #13 setup_fields (thd=0x7fb774000c58, ref_pointer_array=..., fields=..., column_usage=column_usage@entry=MARK_COLUMNS_READ, sum_func_list=sum_func_list@entry=0x7fb774010618, pre_fix=<optimized out>, allow_sum_func=<optimized out>) at /data/src/10.4/sql/sql_base.cc:7738
      #14 0x000055871efc7a61 in JOIN::prepare (this=this@entry=0x7fb774010300, tables_init=tables_init@entry=0x7fb77407bb90, wild_num=wild_num@entry=0, conds_init=conds_init@entry=0x0, og_num=og_num@entry=0, order_init=order_init@entry=0x0, skip_order_by=<optimized out>, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /data/src/10.4/sql/sql_select.cc:1308
      #15 0x000055871efdb589 in mysql_select (thd=thd@entry=0x7fb774000c58, tables=<optimized out>, wild_num=<optimized out>, fields=..., conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.4/sql/sql_select.cc:4774
      #16 0x000055871efdb904 in handle_select (thd=thd@entry=0x7fb774000c58, lex=lex@entry=0x7fb774079928, result=result@entry=0x7fb77407e348, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/10.4/sql/sql_select.cc:442
      #17 0x000055871ef637a0 in execute_sqlcom_select (thd=thd@entry=0x7fb774000c58, all_tables=0x7fb77407bb90) at /data/src/10.4/sql/sql_parse.cc:6463
      #18 0x000055871ef720bb in mysql_execute_command (thd=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:3966
      #19 0x000055871ef89c66 in Prepared_statement::execute (this=this@entry=0x7fb774115338, expanded_query=expanded_query@entry=0x7fb784c91350, open_cursor=open_cursor@entry=false) at /data/src/10.4/sql/sql_prepare.cc:5024
      #20 0x000055871ef89de5 in Prepared_statement::execute_loop (packet=<optimized out>, packet_end=<optimized out>, open_cursor=<optimized out>, expanded_query=0x7fb784c91350, this=0x7fb774115338) at /data/src/10.4/sql/sql_prepare.cc:4493
      #21 Prepared_statement::execute_loop (this=0x7fb774115338, expanded_query=0x7fb784c91350, open_cursor=<optimized out>, packet=<optimized out>, packet_end=<optimized out>) at /data/src/10.4/sql/sql_prepare.cc:4442
      #22 0x000055871ef8a11b in mysql_sql_stmt_execute (thd=thd@entry=0x7fb774000c58) at /data/src/10.4/sql/sql_prepare.cc:3577
      #23 0x000055871ef6fb85 in mysql_execute_command (thd=thd@entry=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:3982
      #24 0x000055871ef743e1 in mysql_parse (thd=0x7fb774000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/src/10.4/sql/sql_parse.cc:7998
      #25 0x000055871ef76915 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fb774000c58, packet=packet@entry=0x7fb774007d59 "EXECUTE stmt", packet_length=packet_length@entry=12, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.4/sql/sql_parse.cc:1958
      #26 0x000055871ef78bbf in do_command (thd=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:1378
      #27 0x000055871f071c7e in do_handle_one_connection (connect=connect@entry=0x5587222445a8) at /data/src/10.4/sql/sql_connect.cc:1420
      #28 0x000055871f071d9d in handle_one_connection (arg=arg@entry=0x5587222445a8) at /data/src/10.4/sql/sql_connect.cc:1324
      #29 0x000055871f402fab in pfs_spawn_thread (arg=0x55872219d818) at /data/src/10.4/storage/perfschema/pfs.cc:1869
      #30 0x00007fb78aca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      #31 0x00007fb78ad285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
      

      Reproducible on debug- and non-debug build as shown above, with at least MyISAM, InnoDB, Aria.

      The failure started happening on 10.4 branch after this commit:

      commit ccec9b1de95a66b7597bc30e0a60bd61866f225d
      Author: Igor Babaev
      Date:   Wed Mar 1 22:49:27 2023 -0800
       
          MDEV-30706 Different results of selects from view and CTE with same definition
          MDEV-30668 Set function aggregated in outer select used in view definition
      

      Attachments

        Activity

          It is assert that derived is not excluded here:

          #7  0x000055c03dc0c8b9 in TABLE_LIST::set_check_materialized (
              this=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/table.cc:6470
          #8  0x000055c03da1a07b in TABLE_LIST::set_materialized_derived (
              this=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/table.h:2883
          #9  0x000055c03dc14e40 in TABLE_LIST::init_derived (this=0x7f5ffc0d7a08, 
              thd=0x7f5ffc000da0, init_view=true)
              at /home/sanja/maria/git/10.4/sql/table.cc:9241
          #10 0x000055c03da59446 in mysql_derived_init (thd=0x7f5ffc000da0, 
              lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08)
              at /home/sanja/maria/git/10.4/sql/sql_derived.cc:564
          #11 0x000055c03da5836c in mysql_handle_derived (lex=0x7f5ffc0d57a0, phases=1)
              at /home/sanja/maria/git/10.4/sql/sql_derived.cc:123
          #12 0x000055c03da0da8f in open_and_lock_tables (thd=0x7f5ffc000da0, 
              options=..., tables=0x7f5ffc0d7a08, derived=true, flags=0, 
              prelocking_strategy=0x7f600d7929d0)
              at /home/sanja/maria/git/10.4/sql/sql_base.cc:5353
          #13 0x000055c03d9c080d in open_and_lock_tables (thd=0x7f5ffc000da0, 
              tables=0x7f5ffc0d7a08, derived=true, flags=0)
              at /home/sanja/maria/git/10.4/sql/sql_base.h:503
          #14 0x000055c03dac2c77 in execute_sqlcom_select (thd=0x7f5ffc000da0, 
              all_tables=0x7f5ffc0d7a08)
          

          the derived is in deed excluded, and it had happened here:

          #0  0x000055c03da80084 in st_select_lex_unit::exclude_level (
              this=0x7f5ffc09dce8) at /home/sanja/maria/git/10.4/sql/sql_lex.cc:2746
          #1  0x000055c03da58b06 in mysql_derived_merge (thd=0x7f5ffc000da0, 
              lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08)
              at /home/sanja/maria/git/10.4/sql/sql_derived.cc:417
          #2  0x000055c03da5869f in mysql_handle_single_derived (lex=0x7f5ffc0d57a0, 
              derived=0x7f5ffc0d7a08, phases=8)
              at /home/sanja/maria/git/10.4/sql/sql_derived.cc:200
          #3  0x000055c03dc1497b in TABLE_LIST::handle_derived (this=0x7f5ffc0d7a08, 
              lex=0x7f5ffc0d57a0, phases=8)
              at /home/sanja/maria/git/10.4/sql/table.cc:9090
          #4  0x000055c03da75d75 in LEX::handle_list_of_derived (this=0x7f5ffc0d57a0, 
              table_list=0x7f5ffc0d7a08, phases=8)
              at /home/sanja/maria/git/10.4/sql/sql_lex.h:4388
          #5  0x000055c03da830e6 in st_select_lex::handle_derived (this=0x7f5ffc0d73f8, 
              lex=0x7f5ffc0d57a0, phases=8)
              at /home/sanja/maria/git/10.4/sql/sql_lex.cc:4309
          #6  0x000055c03db09a1f in JOIN::optimize_inner (this=0x7f5ffc014a68)
              at /home/sanja/maria/git/10.4/sql/sql_select.cc:1910
          #7  0x000055c03db08e56 in JOIN::optimize (this=0x7f5ffc014a68)
              at /home/sanja/maria/git/10.4/sql/sql_select.cc:1690
          #8  0x000055c03db14d72 in mysql_select (thd=0x7f5ffc000da0, 
              tables=0x7f5ffc0d7a08, wild_num=0, fields=..., conds=0x0, og_num=0, 
              order=0x0, group=0x0, having=0x7f5ffc1912a8, proc_param=0x0, 
              select_options=2416184064, result=0x7f5ffc0da1c0, unit=0x7f5ffc0d5860, 
              select_lex=0x7f5ffc0d73f8)
              at /home/sanja/maria/git/10.4/sql/sql_select.cc:4797
          #9  0x000055c03db033eb in handle_select (thd=0x7f5ffc000da0, 
              lex=0x7f5ffc0d57a0, result=0x7f5ffc0da1c0, setup_tables_done_option=0)
              at /home/sanja/maria/git/10.4/sql/sql_select.cc:442
          #10 0x000055c03dac3198 in execute_sqlcom_select (thd=0x7f5ffc000da0, 
              all_tables=0x7f5ffc0d7a08)
              at /home/sanja/maria/git/10.4/sql/sql_parse.cc:6463
          #11 0x000055c03dab9656 in mysql_execute_command (thd=0x7f5ffc000da0)
              at /home/sanja/maria/git/10.4/sql/sql_parse.cc:3966
          #12 0x000055c03daeb6df in Prepared_statement::execute (this=0x7f5ffc197a60, 
              expanded_query=0x7f600d793940, open_cursor=false)
          

          sanja Oleksandr Byelkin added a comment - It is assert that derived is not excluded here: #7 0x000055c03dc0c8b9 in TABLE_LIST::set_check_materialized ( this=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/table.cc:6470 #8 0x000055c03da1a07b in TABLE_LIST::set_materialized_derived ( this=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/table.h:2883 #9 0x000055c03dc14e40 in TABLE_LIST::init_derived (this=0x7f5ffc0d7a08, thd=0x7f5ffc000da0, init_view=true) at /home/sanja/maria/git/10.4/sql/table.cc:9241 #10 0x000055c03da59446 in mysql_derived_init (thd=0x7f5ffc000da0, lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/sql_derived.cc:564 #11 0x000055c03da5836c in mysql_handle_derived (lex=0x7f5ffc0d57a0, phases=1) at /home/sanja/maria/git/10.4/sql/sql_derived.cc:123 #12 0x000055c03da0da8f in open_and_lock_tables (thd=0x7f5ffc000da0, options=..., tables=0x7f5ffc0d7a08, derived=true, flags=0, prelocking_strategy=0x7f600d7929d0) at /home/sanja/maria/git/10.4/sql/sql_base.cc:5353 #13 0x000055c03d9c080d in open_and_lock_tables (thd=0x7f5ffc000da0, tables=0x7f5ffc0d7a08, derived=true, flags=0) at /home/sanja/maria/git/10.4/sql/sql_base.h:503 #14 0x000055c03dac2c77 in execute_sqlcom_select (thd=0x7f5ffc000da0, all_tables=0x7f5ffc0d7a08) the derived is in deed excluded, and it had happened here: #0 0x000055c03da80084 in st_select_lex_unit::exclude_level ( this=0x7f5ffc09dce8) at /home/sanja/maria/git/10.4/sql/sql_lex.cc:2746 #1 0x000055c03da58b06 in mysql_derived_merge (thd=0x7f5ffc000da0, lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/sql_derived.cc:417 #2 0x000055c03da5869f in mysql_handle_single_derived (lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08, phases=8) at /home/sanja/maria/git/10.4/sql/sql_derived.cc:200 #3 0x000055c03dc1497b in TABLE_LIST::handle_derived (this=0x7f5ffc0d7a08, lex=0x7f5ffc0d57a0, phases=8) at /home/sanja/maria/git/10.4/sql/table.cc:9090 #4 0x000055c03da75d75 in LEX::handle_list_of_derived (this=0x7f5ffc0d57a0, table_list=0x7f5ffc0d7a08, phases=8) at /home/sanja/maria/git/10.4/sql/sql_lex.h:4388 #5 0x000055c03da830e6 in st_select_lex::handle_derived (this=0x7f5ffc0d73f8, lex=0x7f5ffc0d57a0, phases=8) at /home/sanja/maria/git/10.4/sql/sql_lex.cc:4309 #6 0x000055c03db09a1f in JOIN::optimize_inner (this=0x7f5ffc014a68) at /home/sanja/maria/git/10.4/sql/sql_select.cc:1910 #7 0x000055c03db08e56 in JOIN::optimize (this=0x7f5ffc014a68) at /home/sanja/maria/git/10.4/sql/sql_select.cc:1690 #8 0x000055c03db14d72 in mysql_select (thd=0x7f5ffc000da0, tables=0x7f5ffc0d7a08, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x7f5ffc1912a8, proc_param=0x0, select_options=2416184064, result=0x7f5ffc0da1c0, unit=0x7f5ffc0d5860, select_lex=0x7f5ffc0d73f8) at /home/sanja/maria/git/10.4/sql/sql_select.cc:4797 #9 0x000055c03db033eb in handle_select (thd=0x7f5ffc000da0, lex=0x7f5ffc0d57a0, result=0x7f5ffc0da1c0, setup_tables_done_option=0) at /home/sanja/maria/git/10.4/sql/sql_select.cc:442 #10 0x000055c03dac3198 in execute_sqlcom_select (thd=0x7f5ffc000da0, all_tables=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/sql_parse.cc:6463 #11 0x000055c03dab9656 in mysql_execute_command (thd=0x7f5ffc000da0) at /home/sanja/maria/git/10.4/sql/sql_parse.cc:3966 #12 0x000055c03daeb6df in Prepared_statement::execute (this=0x7f5ffc197a60, expanded_query=0x7f600d793940, open_cursor=false)

          diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
          index 8c31545eb84..97d19aa2690 100644
          --- a/mysql-test/main/view.result
          +++ b/mysql-test/main/view.result
          @@ -6956,4 +6956,22 @@ create algorithm=merge view v as
           select * from t1 left join t2 on t1.a=t2.b and t1.a in (select d from t3);
           ERROR 42S22: Unknown column 'd' in 'field list'
           drop table t1,t2,t3;
          +#
          +# MDEV-31189: Server crash or assertion failure in upon 2nd
          +# execution of PS with views and HAVING
          +#
          +CREATE TABLE t (f INT);
          +INSERT INTO t VALUES (1),(2);
          +CREATE VIEW v1 AS SELECT 1 AS a;
          +CREATE VIEW v2 AS SELECT a FROM v1;
          +PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
          +EXECUTE stmt;
          +a
          +1
          +EXECUTE stmt;
          +a
          +1
          +DROP VIEW v1;
          +DROP VIEW v2;
          +DROP TABLE t;
           # End of 10.4 tests
          diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
          index 0e2dce1fb70..385ca523436 100644
          --- a/mysql-test/main/view.test
          +++ b/mysql-test/main/view.test
          @@ -6696,4 +6696,23 @@ create algorithm=merge view v as
           
           drop table t1,t2,t3;
           
          +--echo #
          +--echo # MDEV-31189: Server crash or assertion failure in upon 2nd
          +--echo # execution of PS with views and HAVING
          +--echo #
          +
          +CREATE TABLE t (f INT);
          +INSERT INTO t VALUES (1),(2); # Optional, fails either way
          +CREATE VIEW v1 AS SELECT 1 AS a;
          +CREATE VIEW v2 AS SELECT a FROM v1;
          +
          +PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
          +EXECUTE stmt;
          +EXECUTE stmt;
          +
          +# Cleanup
          +DROP VIEW v1;
          +DROP VIEW v2;
          +DROP TABLE t;
          +
           --echo # End of 10.4 tests
          diff --git a/sql/table.cc b/sql/table.cc
          index 15a92818b81..0f296a85e58 100644
          --- a/sql/table.cc
          +++ b/sql/table.cc
          @@ -9163,8 +9163,13 @@ void TABLE_LIST::wrap_into_nested_join(List<TABLE_LIST> &join_list)
           
           static inline bool derived_table_optimization_done(TABLE_LIST *table)
           {
          -  return table->derived &&
          -      (table->derived->is_excluded() ||
          +  SELECT_LEX_UNIT *derived= (table->derived ?
          +                             table->derived :
          +                             (table->view ?
          +                              &table->view->unit:
          +                              NULL));
          +  return derived &&
          +      (derived->is_excluded() ||
                  table->is_materialized_derived());
           }
           
          @@ -9226,8 +9231,7 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
               set_derived();
             }
           
          -  if (is_view() ||
          -      !derived_table_optimization_done(this))
          +  if (!derived_table_optimization_done(this))
             {
               /* A subquery might be forced to be materialized due to a side-effect. */
               if (!is_materialized_derived() && unit->can_be_merged() &&
          

          sanja Oleksandr Byelkin added a comment - diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 8c31545eb84..97d19aa2690 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6956,4 +6956,22 @@ create algorithm=merge view v as select * from t1 left join t2 on t1.a=t2.b and t1.a in (select d from t3); ERROR 42S22: Unknown column 'd' in 'field list' drop table t1,t2,t3; +# +# MDEV-31189: Server crash or assertion failure in upon 2nd +# execution of PS with views and HAVING +# +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT a FROM v1; +PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)"; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +DROP VIEW v1; +DROP VIEW v2; +DROP TABLE t; # End of 10.4 tests diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 0e2dce1fb70..385ca523436 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6696,4 +6696,23 @@ create algorithm=merge view v as drop table t1,t2,t3; +--echo # +--echo # MDEV-31189: Server crash or assertion failure in upon 2nd +--echo # execution of PS with views and HAVING +--echo # + +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); # Optional, fails either way +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT a FROM v1; + +PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)"; +EXECUTE stmt; +EXECUTE stmt; + +# Cleanup +DROP VIEW v1; +DROP VIEW v2; +DROP TABLE t; + --echo # End of 10.4 tests diff --git a/sql/table.cc b/sql/table.cc index 15a92818b81..0f296a85e58 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9163,8 +9163,13 @@ void TABLE_LIST::wrap_into_nested_join(List<TABLE_LIST> &join_list) static inline bool derived_table_optimization_done(TABLE_LIST *table) { - return table->derived && - (table->derived->is_excluded() || + SELECT_LEX_UNIT *derived= (table->derived ? + table->derived : + (table->view ? + &table->view->unit: + NULL)); + return derived && + (derived->is_excluded() || table->is_materialized_derived()); } @@ -9226,8 +9231,7 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view) set_derived(); } - if (is_view() || - !derived_table_optimization_done(this)) + if (!derived_table_optimization_done(this)) { /* A subquery might be forced to be materialized due to a side-effect. */ if (!is_materialized_derived() && unit->can_be_merged() &&

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            1 Vote for this issue
            Watchers:
            7 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.