[MDEV-27957] Select from view with subselect fails with lost connection Created: 2022-02-28  Updated: 2023-10-19  Resolved: 2023-10-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: excluded-select-and-explain

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open
Relates
relates to MDEV-26047 MariaDB server crash at Item_subselec... Closed
relates to MDEV-26249 Crash in in Explain_node::print_expla... Closed

 Description   

Test:

CREATE TABLE t1 (id INT PRIMARY KEY);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
CREATE OR REPLACE VIEW v1 AS SELECT
 
  1 IN (
 
    SELECT
 
      (SELECT COUNT(id)
 
       FROM t1
 
       WHERE t1_outer.id <> id
 
       ) AS f
 
    FROM
 
      t1 AS t1_outer
 
    GROUP BY f
 
  );
 
 
 
SELECT * FROM v1;
 
DROP VIEW v1;
 
DROP TABLE t1;

Errors:

mysqltest: At line 16: query 'SELECT * FROM v1' failed: 2013: Lost connection to MySQL server during query
 
 
 
 
 
Server [mysqld.1 - pid: 59119, winpid: 59119, exit: 256] failed during test run
 
Server log from this test:
 
----------SERVER LOG START-----------
 
$ /home/elena/work/MariaDB/10.3/bld/sql/mysqld --defaults-group-suffix=.1 --defaults-file=/home/elena/work/MariaDB/10.3/bld/mysql-test/var/my.cnf --log-output=file --core-file --loose-debug-sync-timeout=300
 
2022-02-28 14:31:46 0 [Note] /home/elena/work/MariaDB/10.3/bld/sql/mysqld (mysqld 10.3.35-MariaDB-debug-log) starting as process 59120 ...
 
2022-02-28 14:31:46 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 32198)
 
2022-02-28 14:31:46 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 151)  table_cache: 421 (was 2000)
 
2022-02-28 14:31:46 0 [Note] Plugin 'partition' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'InnoDB' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'SEQUENCE' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_DATAFILES' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_TABLESTATS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_LOCKS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_MUTEXES' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_CMPMEM' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_CMP_PER_INDEX' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_CMP' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_FT_DELETED' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_CMP_RESET' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_LOCK_WAITS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_TABLESPACES_ENCRYPTION' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_BUFFER_PAGE_LRU' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_FIELDS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_CMPMEM_RESET' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_COLUMNS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_FT_INDEX_TABLE' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_CMP_PER_INDEX_RESET' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'user_variables' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_FT_INDEX_CACHE' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_FOREIGN_COLS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_FT_BEING_DELETED' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_BUFFER_POOL_STATS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_TRX' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_FOREIGN' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_TABLES' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_FT_DEFAULT_STOPWORD' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_FT_CONFIG' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_BUFFER_PAGE' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_TABLESPACES' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_METRICS' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_INDEXES' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_VIRTUAL' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_TABLESPACES_SCRUBBING' is disabled.
 
2022-02-28 14:31:46 0 [Note] Plugin 'INNODB_SYS_SEMAPHORE_WAITS' is disabled.
 
2022-02-28 14:31:46 0 [Warning] /home/elena/work/MariaDB/10.3/bld/sql/mysqld: unknown option '--loose-pam-debug'
 
2022-02-28 14:31:46 0 [Note] Server socket created on IP: '127.0.0.1'.
 
2022-02-28 14:31:46 0 [Note] Reading of all Master_info entries succeeded
 
2022-02-28 14:31:46 0 [Note] Added new Master_info '' to hash table
 
2022-02-28 14:31:46 0 [Note] /home/elena/work/MariaDB/10.3/bld/sql/mysqld: ready for connections.
 
Version: '10.3.35-MariaDB-debug-log'  socket: '/home/elena/work/MariaDB/10.3/bld/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
 
220228 14:31:46 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.3.35-MariaDB-debug-log
 
key_buffer_size=1048576
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63289 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f18cc000d90
 
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 = 0x7f18dc399db0 thread_stack 0x49000
 
mysys/stacktrace.c:174(my_print_stacktrace)[0x564a238a97e8]
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x564a22fcb40a]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x5b43cc vs 0x429a58)
 
/lib/x86_64-linux-gnu/libc.so.6(+0x46520)[0x7f18e2383520]
 
sql/item_subselect.cc:6878(Item_subselect::init_expr_cache_tracker(THD*))[0x564a230c6ae5]
 
sql/item_subselect.cc:1322(Item_singlerow_subselect::expr_cache_insert_transformer(THD*, unsigned char*))[0x564a230b3b7d]
 
sql/item.cc:727(Item::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x564a22fe9db4]
 
sql/sql_select.cc:3671(JOIN::setup_subquery_caches())[0x564a22ce3062]
 
sql/sql_select.cc:2537(JOIN::optimize_stage2())[0x564a22cdea17]
 
sql/sql_select.cc:2003(JOIN::optimize_inner())[0x564a22cdcaf7]
 
sql/sql_select.cc:1519(JOIN::optimize())[0x564a22cdae7c]
 
sql/sql_lex.cc:4118(st_select_lex::optimize_unflattened_subqueries(bool))[0x564a22c65d3d]
 
sql/opt_subselect.cc:5377(JOIN::optimize_constant_subqueries())[0x564a22e7aedb]
 
sql/sql_select.cc:1718(JOIN::optimize_inner())[0x564a22cdb844]
 
sql/sql_select.cc:1519(JOIN::optimize())[0x564a22cdae7c]
 
sql/sql_derived.cc:962(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x564a22c40580]
 
sql/sql_derived.cc:193(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x564a22c3e620]
 
sql/sql_select.cc:1800(JOIN::optimize_inner())[0x564a22cdbf1f]
 
sql/sql_select.cc:1519(JOIN::optimize())[0x564a22cdae7c]
 
sql/sql_select.cc:4339(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x564a22ce5223]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564a22cd641e]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564a22c9ac83]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x564a22c913a0]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564a22c9f205]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564a22c8b18f]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x564a22c89ab3]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x564a22e11a89]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x564a22e117f8]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564a238330f4]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x5b43cc vs 0x429a58)
 
/lib/x86_64-linux-gnu/libc.so.6(+0x98927)[0x7f18e23d5927]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x44)[0x7f18e24659e4]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f18cc012ad8): SELECT * FROM v1
 
 
 
Connection ID (thread ID): 4
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
 
 
 
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
information that should help you find out what is causing the crash.
 
Writing a core file...
 
Working directory at /home/elena/work/MariaDB/10.3/bld/mysql-test/var/mysqld.1/data
 
Resource Limits:
 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             255959               255959               processes 
Max open files            1024                 1024                 files     
Max locked memory         8393773056           8393773056           bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       255959               255959               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E
 
 
 
----------SERVER LOG END-------------



 Comments   
Comment by Shunsuke Tokunaga [ 2022-03-05 ]

I made a PR to solve this issue: https://github.com/MariaDB/server/pull/2042

Comment by Lena Startseva [ 2022-03-14 ]

Review done

Comment by Anel Husakovic [ 2022-03-21 ]

Hi nayuta-yanagisawa seems PR is approved are you going to merge PR 2042 ? Should it go to 10.3 as earliest branch?

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-22 ]

anel Hi, Anel. I believe that I'm not qualified to merge it because the issue is out of the scope of our team (Spider + partitioning).

lstartseva sanja Could you merge the pull request?

Comment by Oleksandr Byelkin [ 2022-03-23 ]

The real problem is to remove unit and leave it still connected inside unit (because t used in explain), the question is why EXPLAIN need to explain removed units.

Comment by Oleksandr Byelkin [ 2022-03-23 ]

Here created explain node which never then used, so we just can allow it to be created.

Comment by Oleksandr Byelkin [ 2022-03-23 ]

ommit 3b42b998feb9bebdf35648e4b948af43b6bed8f8 (HEAD > bb-10.3MDEV-27957, origin/bb-10.3-MDEV-27957)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Wed Mar 23 10:35:38 2022 +0100

MDEV-27957 Select from view with subselect fails with lost connection

The problem is in getting select number of excluded EXPLAIN-node,
because on excluding links in exclued unit removed.

IMHO it is simplier to get the number in other way and let the EXPLAIN
proceed then fix EXPLAIN. (the other solution is to do not create nodes
for excluded units)

Comment by Sergei Petrunia [ 2022-03-24 ]

A similar problem to MDEV-26249. Eventually we should remove unused subqueries from the EXPLAIN output.

Comment by Sergei Petrunia [ 2022-03-24 ]

The submitted patch tries to just produce the query plan anyway.

The crash in Item_subselect::init_expr_cache_tracker() happened, when
the code tried to evaluate

unit->first_select()->select_number

but we had first_select()==NULL.

The patch avoids the crash by using engine->get_identifier() which keeps
a pointer to select_lex:

int subselect_single_select_engine::get_identifier()
 
{
 
  return select_lex->select_number; 
}

But if the subquery is a UNION, the get_identifier() will still use unit->first_select():

int subselect_union_engine::get_identifier()
 
{
 
  return unit->first_select()->select_number;
 
}

Will one get a similar crash with UNION?

Comment by Sergei Petrunia [ 2022-03-24 ]

.. yes, one will:

create table t3 (a int);
 
 
CREATE OR REPLACE VIEW v1a AS SELECT
 
  1 IN (
 
    SELECT
 
      (SELECT COUNT(id)
 
       FROM t1
 
       WHERE t1_outer.id <> id
 
       union
 
       select a from t3
 
       ) AS f
 
    FROM
 
      t1 AS t1_outer
 
    GROUP BY f
 
  );
 
select * from v1a;

the server with the above patch crashes here:

  Thread 34 "mysqld" received signal SIGSEGV, Segmentation fault.
 
  0x000055555605a392 in subselect_union_engine::get_identifier (this=0x7fff7401e620) at /home/psergey/dev-git/10.3/sql/item_subselect.cc:3685
 
(gdb) wher
 
  #0  0x000055555605a392 in subselect_union_engine::get_identifier (this=0x7fff7401e620) at /home/psergey/dev-git/10.3/sql/item_subselect.cc:3685
 
  #1  0x00005555560621e9 in Item_subselect::init_expr_cache_tracker (this=0x7fff7401e498, thd=0x7fff74000d50) at /home/psergey/dev-git/10.3/sql/item_subselect.cc:6882
 
  #2  0x000055555604fd49 in Item_singlerow_subselect::expr_cache_insert_transformer (this=0x7fff7401e498, tmp_thd=0x7fff74000d50, unused=0x0) at /home/psergey/dev-git/10.3/sql/item_subselect.cc:1321
 
  #3  0x0000555555f8f4b9 in Item::transform (this=0x7fff7401e498, thd=0x7fff74000d50, transformer=&virtual Item::expr_cache_insert_transformer(THD*, unsigned char*), arg=0x0) at /home/psergey/dev-git/10.3/sql/item.cc:726
 
  #4  0x0000555555c9f6e7 in JOIN::setup_subquery_caches (this=0x7fff7401fe20) at /home/psergey/dev-git/10.3/sql/sql_select.cc:3670
 
  #5  0x0000555555c9b165 in JOIN::optimize_stage2 (this=0x7fff7401fe20) at /home/psergey/dev-git/10.3/sql/sql_select.cc:2537
 
  #6  0x0000555555c9926b in JOIN::optimize_inner (this=0x7fff7401fe20) at /home/psergey/dev-git/10.3/sql/sql_select.cc:2003

Comment by Andrew Hutchings [ 2022-08-25 ]

I don't seem to be able to reproduce this in 10.9 or 10.11, neither the original test or the one in the comments. Should we close this one?

Comment by Alice Sherepa [ 2023-10-12 ]

fixed by commit 5100b20b15edd93200f34a79d25f1b14e46a677e 

https://jira.mariadb.org/browse/MDEV-27957Author: Sergei Petrunia <sergey@mariadb.com>
 
Date:   Fri Apr 22 20:26:14 2022 +0300
 
 
 
    MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker

Comment by Mason Sharp [ 2023-10-19 ]

Closing because of the comment that it has been fixed.

Generated at Thu Feb 08 09:56:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.