[MDEV-30569] Assertion `!(p->table->table->file->ha_table_flags() & (1ULL << 60))' failed in Duplicate_weedout_picker::check_qep Created: 2023-02-04  Updated: 2023-02-15  Resolved: 2023-02-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - Federated, Views
Affects Version/s: N/A
Fix Version/s: 11.0.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-26974 Improve selectivity and related costs... Closed
Relates
relates to MDEV-30395 Wrong result with semijoin and Federa... Closed

 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



 Comments   
Comment by Sergei Petrunia [ 2023-02-06 ]

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?

Comment by Sergei Petrunia [ 2023-02-06 ]

...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

Comment by Sergei Petrunia [ 2023-02-06 ]

Fixed in bb-11.0 tree

Generated at Thu Feb 08 10:17:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.