[MDEV-32609] Derived subquery selecting from dummy table causes segv Created: 2023-10-27  Updated: 2024-02-08

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - Window functions, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.1, 11.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.1, 11.2

Type: Bug Priority: Critical
Reporter: csfuzz Assignee: Yuchen Pei
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates

 Description    

CREATE TABLE v0 ( v1 INTEGER ) ENGINE = InnoDB ;
 
INSERT INTO v0 ( v1 ) ( SELECT 2231626 <= NULL v1 FROM v0 WHERE v1 IN ( v1 ) ORDER BY v1 ) ORDER BY v1 , AVG ( v1 ) OVER ( ) DESC ;
 
DROP SHOW CREATE TABLE v0 ;

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f1f87d04880 thread_stack 0x5fc00
/usr/local/mysql/bin/mariadbd(__interceptor_backtrace+0x5b)[0x781b5b]
mysys/stacktrace.c:215(my_print_stacktrace)[0x228cfae]
sql/signal_handler.cc:0(handle_fatal_signal)[0x12bd0d2]
sigaction.c:0(__restore_rt)[0x7f1faba9a420]
sql/item.cc:3028(Item_field)[0x1315c2a]
sql/sql_window.cc:3135(Window_funcs_sort::setup(THD*, SQL_SELECT*, List_iterator<Item_window_func>&, st_join_table*))[0x1113ef3]
sql/sql_window.cc:3174(Window_funcs_computation::setup(THD*, List<Item_window_func>, st_join_table))[0x1115585]
/usr/local/mysql/bin/mariadbd(_ZN4JOIN21make_aggr_tables_infoEv+0x3d67)[0xc34837]
/usr/local/mysql/bin/mariadbd(_ZN4JOIN15optimize_stage2Ev+0xc891)[0xc0c5d1]
/usr/local/mysql/bin/mariadbd(_ZN4JOIN14optimize_innerEv+0x3919)[0xc17249]
/usr/local/mysql/bin/mariadbd(_ZN4JOIN8optimizeEv+0x176)[0xbffb46]
sql/sql_derived.cc:1037(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0xa5bf21]
/usr/local/mysql/bin/mariadbd(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0x172)[0xa5f042]
/usr/local/mysql/bin/mariadbd(_ZN4JOIN14optimize_innerEv+0x3138)[0xc16a68]
/usr/local/mysql/bin/mariadbd(_ZN4JOIN8optimizeEv+0x176)[0xbffb46]
sql/sql_select.cc:5237(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0xbe886f]
sql/sql_select.cc:628(handle_select(THD*, LEX*, select_result*, unsigned long long))[0xbe7e59]
/usr/local/mysql/bin/mariadbd(_Z21mysql_execute_commandP3THDb+0x9d8c)[0xb39e7c]
sql/sql_class.h:2830(THD::enter_stage(PSI_stage_info_v1 const*, char const*, char const*, unsigned int))[0xb24c79]
/usr/local/mysql/bin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x2cf8)[0xb1e648]
sql/sql_parse.cc:1407(do_command(THD*, bool))[0xb25971]
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0xf0d066]
sql/sql_connect.cc:1322(handle_one_connection)[0xf0caa9]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x19d710b]
nptl/pthread_create.c:478(start_thread)[0x7f1faba8e609]
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530f28)
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f1fab7a6133]

 Comments   
Comment by Yuchen Pei [ 2024-02-06 ]

In the original case, if I change to default mtr engine (myisam) then the crash does not happen.

Comment by Yuchen Pei [ 2024-02-07 ]

Some low level analysis.

Consider the simplified case

(SELECT 5 a FROM dual ORDER BY a) ORDER BY AVG(a) OVER () ;

The subquery (SELECT 5 a FROM dual ORDER BY a) is marked as
derived, with the dummy table having derived_type == 10. The offending
null valued field join_tab->table->field[0] was never non-null.

For comparison, the following works, with the same query marked as
derived, but it does not go into JOIN::make_aggr_tables_info() for
the subquery because const_table_map is 1 instead of 0, which was
updated in make_join_statistics(), and in the crashed case,
make_join_statistics() was skipped because tables_list is empty.

create table t (c int);
 
(SELECT c FROM t ORDER BY c) ORDER BY AVG(c) OVER () ;
 
drop table t;

To take a step back, why is the subquery marked as derived in the
first place?

The kb[1] says

> Derived tables are subqueries in the FROM clause.

But there's no FROM before (SELECT 5 a ORDER BY a). Is there a
"canonical form" of this query with a FROM?

[1] https://mariadb.com/kb/en/optimizations-for-derived-tables/

Comment by Yuchen Pei [ 2024-02-07 ]

The reason that the original case fails under innodb but not myisam is
that innodb's initial table flags do not contain
HA_STATS_RECORDS_IS_EXACT but myisam does, which causes myisam
case to enter the following if branch in make_join_statistics(),
which results in not entering JOIN::make_aggr_tables_info()
because const_table_map is 1.

11.0 35cc4b6c058aeb905e8d802569444bdfce02193a

 
    if (!table->is_filled_at_execution() &&
 
        (table->s->system ||
 
         (table->file->stats.records <= 1 &&
 
          (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) ||
 
         all_partitions_pruned_away) &&
 
	!s->dependent &&
 
        !table->fulltext_searched && !join->no_const_tables)
 
    {
 
      set_position(join,const_count++,s,(KEYUSE*) 0);
 
      no_rows_const_tables |= table->map;
 
    }

Perhaps we could fix it by skipping make_aggr_tables_info() for
the subqueries in both the simplified case and the original case,
since the subqueries are both selecting const things (5 in the
simplified case and 2231626 <= NULL in the original case) whether
from dummy table or a real table, which is similar to a const table.
What do you think psergei?

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