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

Assertion `tl->jtbm_subselect' failed in JOIN::calc_allowed_top_level_tables on 2nd execution of PS

Details

    Description

      CREATE TABLE t (a INT);
      INSERT INTO t VALUES (1),(2); # Optional, fails either way
       
      PREPARE stmt FROM 'CREATE TABLE tmp AS 
      SELECT * 
      FROM (select t1.* from t t1 join t t2 on(t1.a = t2.a)) sq 
      WHERE "x"=0';
      --error ER_TRUNCATED_WRONG_VALUE
      EXECUTE stmt;
      --error ER_TRUNCATED_WRONG_VALUE
      EXECUTE stmt;
       
      # Cleanup
      DROP TABLE t;
      

      main 947de4b1db9782ed9fe9c07c9c7af03d0c369eb5

      mariadbd: /data/bld/main-asan/sql/sql_select.cc:20065: void JOIN::calc_allowed_top_level_tables(SELECT_LEX*): Assertion `tl->jtbm_subselect' failed.
      241102 21:29:11 [ERROR] mysqld got signal 6 ;
       
      #9  0x00007f50f0e53e32 in __GI___assert_fail (assertion=0x5636ef664780 "tl->jtbm_subselect", file=0x5636ef6586c0 "/data/bld/main-asan/sql/sql_select.cc", line=20065, function=0x5636ef664720 "void JOIN::calc_allowed_top_level_tables(SELECT_LEX*)") at ./assert/assert.c:101
      #10 0x00005636ed44b69b in JOIN::calc_allowed_top_level_tables (this=0x6290000e6750, lex=0x62d00025aad8) at /data/bld/main-asan/sql/sql_select.cc:20065
      #11 0x00005636ed3c9015 in JOIN::optimize_inner (this=0x6290000e6750) at /data/bld/main-asan/sql/sql_select.cc:2344
      #12 0x00005636ed3c5f0b in JOIN::optimize (this=0x6290000e6750) at /data/bld/main-asan/sql/sql_select.cc:2004
      #13 0x00005636ed3e87fa in mysql_select (thd=0x62c0000c0218, tables=0x62d00025dcb0, fields=..., conds=0x62d00025e530, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2201187781376, result=0x6290000e6608, unit=0x62d000258528, select_lex=0x62d00025aad8) at /data/bld/main-asan/sql/sql_select.cc:5346
      #14 0x00005636ed3b6fcd in handle_select (thd=0x62c0000c0218, lex=0x62d000258448, result=0x6290000e6608, setup_tables_done_option=0) at /data/bld/main-asan/sql/sql_select.cc:643
      #15 0x00005636ed600d49 in Sql_cmd_create_table_like::execute (this=0x62d00025a300, thd=0x62c0000c0218) at /data/bld/main-asan/sql/sql_table.cc:13201
      #16 0x00005636ed2d734b in mysql_execute_command (thd=0x62c0000c0218, is_called_from_prepared_stmt=true) at /data/bld/main-asan/sql/sql_parse.cc:5864
      #17 0x00005636ed378770 in Prepared_statement::execute (this=0x61900009a698, expanded_query=0x7f50e5c16590, open_cursor=false) at /data/bld/main-asan/sql/sql_prepare.cc:5050
      #18 0x00005636ed3736d3 in Prepared_statement::execute_loop (this=0x61900009a698, expanded_query=0x7f50e5c16590, open_cursor=false, packet=0x0, packet_end=0x0) at /data/bld/main-asan/sql/sql_prepare.cc:4427
      #19 0x00005636ed36cb8c in mysql_sql_stmt_execute (thd=0x62c0000c0218) at /data/bld/main-asan/sql/sql_prepare.cc:3446
      #20 0x00005636ed2c9653 in mysql_execute_command (thd=0x62c0000c0218, is_called_from_prepared_stmt=false) at /data/bld/main-asan/sql/sql_parse.cc:3972
      #21 0x00005636ed2e44a4 in mysql_parse (thd=0x62c0000c0218, rawbuf=0x6290000e6238 "EXECUTE stmt", length=12, parser_state=0x7f50e5c179d0) at /data/bld/main-asan/sql/sql_parse.cc:7891
      #22 0x00005636ed2bb428 in dispatch_command (command=COM_QUERY, thd=0x62c0000c0218, packet=0x629000253219 "EXECUTE stmt", packet_length=12, blocking=true) at /data/bld/main-asan/sql/sql_parse.cc:1892
      #23 0x00005636ed2b8130 in do_command (thd=0x62c0000c0218, blocking=true) at /data/bld/main-asan/sql/sql_parse.cc:1405
      #24 0x00005636ed7a8e54 in do_handle_one_connection (connect=0x608000003638, put_in_cache=true) at /data/bld/main-asan/sql/sql_connect.cc:1448
      #25 0x00005636ed7a8810 in handle_one_connection (arg=0x6080000035b8) at /data/bld/main-asan/sql/sql_connect.cc:1350
      #26 0x00005636ee441926 in pfs_spawn_thread (arg=0x617000005b98) at /data/bld/main-asan/storage/perfschema/pfs.cc:2198
      #27 0x00007f50f0ea8044 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      #28 0x00007f50f0f2861c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
      

      Also fails with a merge view instead of a sub-select.

      The failure started happening after this commit in main

      commit 4b6922a315fa5411665ac99c0b40fd7238093403 (HEAD)
      Author: Yuchen Pei
      Date:   Thu Aug 29 11:10:59 2024 +1000
       
          MDEV-25008: UPDATE/DELETE: Cost-based choice IN->EXISTS vs Materialization
      

      No obvious immediate effect on a non-debug build.

      Attachments

        Issue Links

          Activity

            ycp Yuchen Pei added a comment -

            I don't have much to add. Just some notes on top of the two comments above. First, the sp version of the testcase in this issue fails as a regression of MDEV-25008, just like the ps case. Second, I extracted the failing main.ps case into the one below and observe that after removing "!m_first_execution", the version is 3 at the time of the error. The global var Cversion which starts as 1 and got incremented at the create function and create procedure statements, and at the call statement its value was passed to sp->m_sp_cache_version in sp_cache_insert(). So perhaps this assignment needs to happen later, or check_and_update_routine_version has to be invoked with a NULL thd->m_reprepare_observer at least once so that rt->m_sp_cache_version catches up.

            create table t3 (n int unsigned not null primary key, f bigint unsigned);
             
            delimiter |;
             
            create function fac(n int unsigned) returns bigint unsigned
            begin
              declare f bigint unsigned default 1;
             
              while n > 1 do
                set f = f * n;
                set n = n - 1;
              end while;
              return f;
            end|
             
            create procedure ifac(n int unsigned)
            begin
              declare i int unsigned default 1;
             
              if n > 20 then
                set n = 20;		# bigint overflow otherwise
              end if;
              while i <= n do
                begin
                  insert into t3 values (i, fac(i));
                  set i = i + 1;
                end;
              end while;
            end|
             
            delimiter ;|
            call ifac(20);
             
            drop procedure ifac;
            drop function fac;
            drop table t3;

            ycp Yuchen Pei added a comment - I don't have much to add. Just some notes on top of the two comments above. First, the sp version of the testcase in this issue fails as a regression of MDEV-25008 , just like the ps case. Second, I extracted the failing main.ps case into the one below and observe that after removing "!m_first_execution", the version is 3 at the time of the error. The global var Cversion which starts as 1 and got incremented at the create function and create procedure statements, and at the call statement its value was passed to sp->m_sp_cache_version in sp_cache_insert() . So perhaps this assignment needs to happen later, or check_and_update_routine_version has to be invoked with a NULL thd->m_reprepare_observer at least once so that rt->m_sp_cache_version catches up. create table t3 (n int unsigned not null primary key , f bigint unsigned);   delimiter |;   create function fac(n int unsigned) returns bigint unsigned begin declare f bigint unsigned default 1;   while n > 1 do set f = f * n; set n = n - 1; end while; return f; end |   create procedure ifac(n int unsigned) begin declare i int unsigned default 1;   if n > 20 then set n = 20; # bigint overflow otherwise end if ; while i <= n do begin insert into t3 values (i, fac(i)); set i = i + 1; end ; end while; end |   delimiter ;| call ifac(20);   drop procedure ifac; drop function fac; drop table t3;
            psergei Sergei Petrunia added a comment -

            Discussed the issue with shulga. Take away: it seems we should use our own mechanism and not hook into reprepare observer.

            psergei Sergei Petrunia added a comment - Discussed the issue with shulga . Take away: it seems we should use our own mechanism and not hook into reprepare observer.
            psergei Sergei Petrunia added a comment -

            Produced a new variant of the fix.
            sanja, could you review please?

            commit 8e79d7944bdc5d007b5e37e1d27de052546a565c
            Author: Sergei Petrunia <sergey@mariadb.com>
            Date:   Mon Jan 20 17:44:51 2025 +0200
             
                MDEV-35318 Assertion `tl->jtbm_subselect' failed in JOIN::calc_allowed_top_level_tables
                
                Alternative, more general fix, Variant 2.
                
            

            psergei Sergei Petrunia added a comment - Produced a new variant of the fix. sanja , could you review please? commit 8e79d7944bdc5d007b5e37e1d27de052546a565c Author: Sergei Petrunia <sergey@mariadb.com> Date: Mon Jan 20 17:44:51 2025 +0200   MDEV-35318 Assertion `tl->jtbm_subselect' failed in JOIN::calc_allowed_top_level_tables Alternative, more general fix, Variant 2.
            psergei Sergei Petrunia added a comment -

            Fixed trivial failures...

            commit 3a54ede0e9fd06962010ab8d27a594fbc59ec972 (HEAD -> bb-11.7-mdev-35318-own-reprepare-system, origin/bb-11.7-mdev-35318-own-reprepare-system)
            Author: Sergei Petrunia <sergey@mariadb.com>
            Date:   Mon Feb 3 14:58:58 2025 +0200
             
                MDEV-35318 Assertion `tl->jtbm_subselect' failed...  - PART 2
                
                After the fix in the previous commit, we should never end up in a
                situation where select_lex->first_cond_optimization=false (i.e, "it's done")
                but select_lex->leaf_tables_saved==false ("not done").
                
                So, in setup_tables(), revert the the condition added in the fix for
                MDEV-25008. Add an assert instead.
            

            this is the one to review:

            commit 3461e2ef7f7b922203e957a7668877658607f49c
            Author: Sergei Petrunia <sergey@mariadb.com>
            Date:   Mon Jan 20 17:44:51 2025 +0200
             
                MDEV-35318 Assertion `tl->jtbm_subselect' failed in JOIN::calc_allowed_top_level_tables
                
            

            psergei Sergei Petrunia added a comment - Fixed trivial failures... commit 3a54ede0e9fd06962010ab8d27a594fbc59ec972 (HEAD -> bb-11.7-mdev-35318-own-reprepare-system, origin/bb-11.7-mdev-35318-own-reprepare-system) Author: Sergei Petrunia <sergey@mariadb.com> Date: Mon Feb 3 14:58:58 2025 +0200   MDEV-35318 Assertion `tl->jtbm_subselect' failed... - PART 2 After the fix in the previous commit, we should never end up in a situation where select_lex->first_cond_optimization=false (i.e, "it's done") but select_lex->leaf_tables_saved==false ("not done"). So, in setup_tables(), revert the the condition added in the fix for MDEV-25008. Add an assert instead. this is the one to review: commit 3461e2ef7f7b922203e957a7668877658607f49c Author: Sergei Petrunia <sergey@mariadb.com> Date: Mon Jan 20 17:44:51 2025 +0200   MDEV-35318 Assertion `tl->jtbm_subselect' failed in JOIN::calc_allowed_top_level_tables
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              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.