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

Assertion `!(p->table->table->file->ha_table_flags() & (1ULL << 60))' failed in Duplicate_weedout_picker::check_qep

Details

    Description

      INSTALL SONAME 'ha_federatedx'; 
       
      		 
      eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
      		 
       
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2);
      		 
       
      		 
      CREATE TABLE t2 (b INT);
      		 
      INSERT INTO t2 VALUES (3),(4);
      		 
       
      		 
      CREATE TABLE t1_fed ENGINE=FEDERATED CONNECTION='s/t1';
      		 
      CREATE VIEW v AS SELECT * FROM t1_fed;
      		 
       
      		 
      SELECT * FROM v WHERE a IN ( SELECT b FROM t2);
      		 
       
      		 
      # Cleanup
      		 
       
      		 
      DROP TABLE t1_fed, t1, t2;
      		 
      DROP SERVER s;
      		 
       
      		 
      UNINSTALL SONAME 'ha_federatedx';

      11.0 b90a23a36

      		 
      mariadbd: /data/src/bb-11.0-debug/sql/opt_subselect.cc:3665: virtual bool Duplicate_weedout_picker::check_qep(JOIN*, uint, table_map, const JOIN_TAB*, double*, double*, table_map*, sj_strategy_enum*, POSITION*): Assertion `!(p->table->table->file->ha_table_flags() & (1ULL << 60))' failed.
      		 
      230204 22:07:45 [ERROR] mysqld got signal 6 ;
      		 
       
      		 
      #9  0x00007f99bb33edf2 in __GI___assert_fail (assertion=0x55e67eb666a0 "!(p->table->table->file->ha_table_flags() & (1ULL << 60))", file=0x55e67eb643e0 "/data/src/bb-11.0/sql/opt_subselect.cc", line=3665, function=0x55e67eb665e0 "virtual bool Duplicate_weedout_picker::check_qep(JOIN*, uint, table_map, const JOIN_TAB*, double*, double*, table_map*, sj_strategy_enum*, POSITION*)") at ./assert/assert.c:101
      		 
      #10 0x000055e67cec710e in Duplicate_weedout_picker::check_qep (this=0x629000278768, join=0x6290002744c0, idx=1, remaining_tables=0, new_join_tab=0x629000277b98, record_count=0x7f99b2020f30, read_time=0x7f99b2020f50, handled_fanout=0x7f99b2020f10, strategy=0x7f99b2020f00, loose_scan_pos=0x629000278ef8) at /data/src/bb-11.0/sql/opt_subselect.cc:3665
      		 
      #11 0x000055e67cec254c in optimize_semi_joins (join=0x6290002744c0, remaining_tables=0, idx=1, current_record_count=0x7f99b20212b0, current_read_time=0x7f99b20212d0, loose_scan_pos=0x629000278ef8) at /data/src/bb-11.0/sql/opt_subselect.cc:2959
      		 
      #12 0x000055e67ca5d667 in best_extension_by_limited_search (join=0x6290002744c0, remaining_tables=1, idx=1, record_count=2, read_time=0.010348410000000001, search_depth=61, use_cond_selectivity=4, processed_eq_ref_tables=0x7f99b2021650) at /data/src/bb-11.0/sql/sql_select.cc:11084
      		 
      #13 0x000055e67ca5e7cf in best_extension_by_limited_search (join=0x6290002744c0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, use_cond_selectivity=4, processed_eq_ref_tables=0x7f99b2021930) at /data/src/bb-11.0/sql/sql_select.cc:11251
      		 
      #14 0x000055e67ca577ba in greedy_search (join=0x6290002744c0, remaining_tables=3, search_depth=62, use_cond_selectivity=4) at /data/src/bb-11.0/sql/sql_select.cc:9997
      		 
      #15 0x000055e67ca551f0 in choose_plan (join=0x6290002744c0, join_tables=3, emb_sjm_nest=0x0) at /data/src/bb-11.0/sql/sql_select.cc:9518
      		 
      #16 0x000055e67ca3ccfa in make_join_statistics (join=0x6290002744c0, tables_list=..., keyuse_array=0x629000274820) at /data/src/bb-11.0/sql/sql_select.cc:6053
      		 
      #17 0x000055e67ca18e1e in JOIN::optimize_inner (this=0x6290002744c0) at /data/src/bb-11.0/sql/sql_select.cc:2569
      		 
      #18 0x000055e67ca11f69 in JOIN::optimize (this=0x6290002744c0) at /data/src/bb-11.0/sql/sql_select.cc:1897
      		 
      #19 0x000055e67ca33434 in mysql_select (thd=0x62b00007e218, tables=0x6290000e6920, fields=..., conds=0x6290000e8768, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x6290000e9d28, unit=0x62b000082660, select_lex=0x6290000e6300) at /data/src/bb-11.0/sql/sql_select.cc:5112
      		 
      #20 0x000055e67ca03d90 in handle_select (thd=0x62b00007e218, lex=0x62b000082588, result=0x6290000e9d28, setup_tables_done_option=0) at /data/src/bb-11.0/sql/sql_select.cc:608
      		 
      #21 0x000055e67c92d028 in execute_sqlcom_select (thd=0x62b00007e218, all_tables=0x6290000e6920) at /data/src/bb-11.0/sql/sql_parse.cc:6265
      		 
      #22 0x000055e67c91b995 in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/bb-11.0/sql/sql_parse.cc:3949
      		 
      #23 0x000055e67c937914 in mysql_parse (thd=0x62b00007e218, rawbuf=0x6290000e6238 "SELECT * FROM v WHERE a IN ( SELECT b FROM t2)", length=46, parser_state=0x7f99b2023a20) at /data/src/bb-11.0/sql/sql_parse.cc:8000
      		 
      #24 0x000055e67c90e374 in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x629000253219 "SELECT * FROM v WHERE a IN ( SELECT b FROM t2)", packet_length=46, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1894
      		 
      #25 0x000055e67c90b119 in do_command (thd=0x62b00007e218, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1407
      		 
      #26 0x000055e67cdbd81e in do_handle_one_connection (connect=0x608000002638, put_in_cache=true) at /data/src/bb-11.0/sql/sql_connect.cc:1416
      		 
      #27 0x000055e67cdbd1b4 in handle_one_connection (arg=0x6080000025b8) at /data/src/bb-11.0/sql/sql_connect.cc:1318
      		 
      #28 0x000055e67d9a4086 in pfs_spawn_thread (arg=0x617000004d98) at /data/src/bb-11.0/storage/perfschema/pfs.cc:2201
      		 
      #29 0x00007f99bb392fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      		 
      #30 0x00007f99bb41366c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-26974 Improve selectivity and related costs in optimizer

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30395 Wrong result with semijoin and Federated as outer table

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            This is realated to 

            commit 981a6b704475176d032c13461baffcaf88802d34 (origin/bb-10.5-monty)
            		 
            Author: Monty <monty@mariadb.org>
            		 
            Date:   Thu Jan 12 22:31:18 2023 +0200
            		 
             
            		 
                MDEV-30395 Wrong result with semijoin and Federated as outer table
            		 
                
                The problem was that federated engine does not support comparable rowids
            		 
                which was not taken into account by semijoin code.
            		 
                
                Fixed by checking that we don't use semijoin with tables that does not
            		 
                support comparable rowids.

            That patch introduces the logic "don't use semi-joins if the select has tables that do not provide ROWIDs"

                  /* Check if any table is not supporting comparable rowids */
            		 
                  {
            		 
            =>      List_iterator_fast<TABLE_LIST> li(select_lex->outer_select()->leaf_tables);                                                                                       
                    TABLE_LIST *tbl;
            		 
                    while ((tbl = li++))
            		 
                    {
            		 
                      TABLE *table= tbl->table;
            		 
                      if (table && table->file->ha_table_flags() & HA_NON_COMPARABLE_ROWID)

            When the execution reaches that point, we have:

            (gdb) p select_lex->outer_select()->leaf_tables.elements
            		 
              $121 = 1
            		 
            (gdb) p select_lex->outer_select()->leaf_tables.elem(0)->table->alias.Ptr
            		 
              $124 = 0x7fffa0016f80 "v"
            		 
            (gdb) p select_lex->outer_select()->leaf_tables.elem(0)->table->file
            		 
              $125 = (ha_heap *) 0x7fffa098e7c0

            So, we only see the view?

            psergei Sergei Petrunia added a comment - This is realated to commit 981a6b704475176d032c13461baffcaf88802d34 (origin/bb-10.5-monty) Author: Monty <monty@mariadb.org> Date: Thu Jan 12 22:31:18 2023 +0200   MDEV-30395 Wrong result with semijoin and Federated as outer table The problem was that federated engine does not support comparable rowids which was not taken into account by semijoin code. Fixed by checking that we don't use semijoin with tables that does not support comparable rowids. That patch introduces the logic "don't use semi-joins if the select has tables that do not provide ROWIDs" /* Check if any table is not supporting comparable rowids */ { => List_iterator_fast<TABLE_LIST> li(select_lex->outer_select()->leaf_tables); TABLE_LIST *tbl; while ((tbl = li++)) { TABLE *table= tbl->table; if (table && table->file->ha_table_flags() & HA_NON_COMPARABLE_ROWID) When the execution reaches that point, we have: (gdb) p select_lex->outer_select()->leaf_tables.elements $121 = 1 (gdb) p select_lex->outer_select()->leaf_tables.elem(0)->table->alias.Ptr $124 = 0x7fffa0016f80 "v" (gdb) p select_lex->outer_select()->leaf_tables.elem(0)->table->file $125 = (ha_heap *) 0x7fffa098e7c0 So, we only see the view?
            psergei Sergei Petrunia added a comment -

            ...Yes, because VIEW merging is done after the semi-join check!

              #0  base_list_iterator::replace (this=0x7ffff00f5190, new_list=@0x7fffbc019bd0: {<Sql_alloc> = {<No data fields>}, first = 0x7fffbc020c58, last = 0x7fffbc020c58, elemen
            		 
              #1  0x0000555555f30369 in List_iterator<TABLE_LIST>::replace (this=0x7ffff00f5190, a=@0x7fffbc019bd0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fffbc
            		 
              #2  0x0000555555f0f078 in st_select_lex::replace_leaf_table (this=0x7fffbc0169a0, table=0x7fffbc016f88, tbl_list=@0x7fffbc019bd0: {<base_list> = {<Sql_alloc> = {<No dat
            		 
              #3  0x0000555555f0f48d in st_select_lex::merge_subquery (this=0x7fffbc0169a0, thd=0x7fffbc000d78, derived=0x7fffbc016f88, subq_select=0x7fffbc0199b8, table_no=1, map=2)
            		 
              #4  0x0000555555ee4050 in mysql_derived_merge (thd=0x7fffbc000d78, lex=0x7fffbc0050e8, derived=0x7fffbc016f88) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_de
            		 
              #5  0x0000555555ee3ad6 in mysql_handle_single_derived (lex=0x7fffbc0050e8, derived=0x7fffbc016f88, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_deri
            		 
              #6  0x00005555560ce2ee in TABLE_LIST::handle_derived (this=0x7fffbc016f88, lex=0x7fffbc0050e8, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/table.cc:960
            		 
              #7  0x0000555555ee2e96 in LEX::handle_list_of_derived (this=0x7fffbc0050e8, table_list=0x7fffbc016f88, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_
            		 
              #8  0x0000555555f0ee98 in st_select_lex::handle_derived (this=0x7fffbc0169a0, lex=0x7fffbc0050e8, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_lex.c
            		 
              #9  0x0000555555fac765 in JOIN::optimize_inner (this=0x7fffbc01fe80) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_select.cc:2099
            		 
              #10 0x0000555555fabd95 in JOIN::optimize (this=0x7fffbc01fe80) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_select.cc:1897
            		 
              #11 0x0000555555fb7976 in mysql_select (thd=0x7fffbc000d78, tables=0x7fffbc016f88, fields=@0x7fffbc016c40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7

            psergei Sergei Petrunia added a comment - ...Yes, because VIEW merging is done after the semi-join check! #0 base_list_iterator::replace (this=0x7ffff00f5190, new_list=@0x7fffbc019bd0: {<Sql_alloc> = {<No data fields>}, first = 0x7fffbc020c58, last = 0x7fffbc020c58, elemen #1 0x0000555555f30369 in List_iterator<TABLE_LIST>::replace (this=0x7ffff00f5190, a=@0x7fffbc019bd0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fffbc #2 0x0000555555f0f078 in st_select_lex::replace_leaf_table (this=0x7fffbc0169a0, table=0x7fffbc016f88, tbl_list=@0x7fffbc019bd0: {<base_list> = {<Sql_alloc> = {<No dat #3 0x0000555555f0f48d in st_select_lex::merge_subquery (this=0x7fffbc0169a0, thd=0x7fffbc000d78, derived=0x7fffbc016f88, subq_select=0x7fffbc0199b8, table_no=1, map=2) #4 0x0000555555ee4050 in mysql_derived_merge (thd=0x7fffbc000d78, lex=0x7fffbc0050e8, derived=0x7fffbc016f88) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_de #5 0x0000555555ee3ad6 in mysql_handle_single_derived (lex=0x7fffbc0050e8, derived=0x7fffbc016f88, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_deri #6 0x00005555560ce2ee in TABLE_LIST::handle_derived (this=0x7fffbc016f88, lex=0x7fffbc0050e8, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/table.cc:960 #7 0x0000555555ee2e96 in LEX::handle_list_of_derived (this=0x7fffbc0050e8, table_list=0x7fffbc016f88, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_ #8 0x0000555555f0ee98 in st_select_lex::handle_derived (this=0x7fffbc0169a0, lex=0x7fffbc0050e8, phases=8) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_lex.c #9 0x0000555555fac765 in JOIN::optimize_inner (this=0x7fffbc01fe80) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_select.cc:2099 #10 0x0000555555fabd95 in JOIN::optimize (this=0x7fffbc01fe80) at /home/psergey/dev-git2/11.0-jan-rebase-done/sql/sql_select.cc:1897 #11 0x0000555555fb7976 in mysql_select (thd=0x7fffbc000d78, tables=0x7fffbc016f88, fields=@0x7fffbc016c40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7
            psergei Sergei Petrunia added a comment -

            Fixed in bb-11.0 tree

            psergei Sergei Petrunia added a comment - Fixed in bb-11.0 tree

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.