[MDEV-32829] Crash when executing PS for query with eliminated subquery using view Created: 2023-11-17  Updated: 2023-11-28  Resolved: 2023-11-22

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2

Type: Bug Priority: Major
Reporter: Ramesh Sivaraman Assignee: Rex Johnston
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-31995 Bogus error executing PS for query us... Closed

 Description    

CREATE VIEW t AS SELECT 1 AS a;
 
PREPARE s FROM 'SELECT EXISTS (SELECT 1 FROM t GROUP BY a IN (SELECT a FROM t))';
 
EXECUTE s;

Leads to:

10.4.33 d4be70afb4eaffbd1c2dd3326e19752770b08bc6 (Optimized)

 
Core was generated by `/test/mtest/MD161123-mariadb-10.4.33-linux-x86_64-opt/bin/mariadbd --no-default'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x000055b92328b901 in st_select_lex::restore_item_list_names (
 
    this=this@entry=0x0) at /test/mtest/10.4_opt/sql/sql_lex.cc:10725
 
[Current thread is 1 (Thread 0x14573979c700 (LWP 3347462))]
 
(gdb) bt
 
#0  0x000055b92328b901 in st_select_lex::restore_item_list_names (this=this@entry=0x0) at /test/mtest/10.4_opt/sql/sql_lex.cc:10725
 
#1  0x000055b92325dc50 in mysql_derived_reinit (thd=0x1456fc000c48, lex=<optimized out>, derived=0x1456fc01aa28) at /test/mtest/10.4_opt/sql/sql_derived.cc:1343
 
#2  0x000055b92325e9d8 in mysql_handle_single_derived (lex=0x1456fc0174c8, derived=derived@entry=0x1456fc01aa28, phases=phases@entry=128) at /test/mtest/10.4_opt/sql/sql_derived.cc:200
 
#3  0x000055b9233682d1 in mysql_make_view (thd=thd@entry=0x1456fc000c48, share=share@entry=0x1456fc01d9d0, table=table@entry=0x1456fc01aa28, open_view_no_parse=open_view_no_parse@entry=false) at /test/mtest/10.4_opt/sql/sql_view.cc:1261
 
#4  0x000055b923237d5b in open_table (thd=0x1456fc000c48, table_list=0x1456fc01aa28, ot_ctx=0x145739797a80) at /test/mtest/10.4_opt/sql/sql_base.cc:2029
 
#5  0x000055b92323b75e in open_and_process_table (ot_ctx=0x145739797a80, has_prelocking_list=false, prelocking_strategy=0x145739797b70, flags=0, counter=0x145739797b1c, tables=0x1456fc01aa28, thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_base.cc:3915
 
#6  open_tables (thd=thd@entry=0x1456fc000c48, options=@0x1456fc018ae0: {m_options = DDL_options_st::OPT_NONE}, start=start@entry=0x145739797b08, counter=counter@entry=0x145739797b1c, flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x145739797b70) at /test/mtest/10.4_opt/sql/sql_base.cc:4397
 
#7  0x000055b92323c25a in open_and_lock_tables (thd=thd@entry=0x1456fc000c48, options=<optimized out>, tables=<optimized out>, tables@entry=0x1456fc019bd8, derived=derived@entry=true, flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x145739797b70) at /test/mtest/10.4_opt/sql/sql_base.cc:5344
 
#8  0x000055b92329528d in open_and_lock_tables (flags=0, derived=true, tables=0x1456fc019bd8, thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_base.h:503
 
#9  execute_sqlcom_select (thd=0x1456fc000c48, all_tables=0x1456fc019bd8) at /test/mtest/10.4_opt/sql/sql_parse.cc:6396
 
#10 0x000055b9232a4047 in mysql_execute_command (thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_parse.cc:3978
 
#11 0x000055b9232bc4a6 in Prepared_statement::execute (this=0x1456fc0170e8, expanded_query=<optimized out>, open_cursor=false) at /test/mtest/10.4_opt/sql/sql_prepare.cc:5025
 
#12 0x000055b9232bc6a1 in Prepared_statement::execute_loop (packet=<optimized out>, packet_end=<optimized out>, open_cursor=<optimized out>, expanded_query=0x145739799a70, this=0x1456fc0170e8) at /test/mtest/10.4_opt/sql/sql_prepare.cc:4472
 
#13 Prepared_statement::execute_loop (this=0x1456fc0170e8, expanded_query=0x145739799a70, open_cursor=<optimized out>, packet=<optimized out>, packet_end=<optimized out>) at /test/mtest/10.4_opt/sql/sql_prepare.cc:4421
 
#14 0x000055b9232bc9db in mysql_sql_stmt_execute (thd=thd@entry=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_prepare.cc:3531
 
#15 0x000055b9232a0a67 in mysql_execute_command (thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_parse.cc:3994
 
#16 0x000055b9232a67ba in mysql_parse (thd=0x1456fc000c48, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /test/mtest/10.4_opt/sql/sql_parse.cc:8014
 
#17 0x000055b9232a8dd5 in dispatch_command (command=COM_QUERY, thd=0x1456fc000c48, packet=<optimized out>, packet_length=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /test/mtest/10.4_opt/sql/sql_class.h:1242
 
#18 0x000055b9232ab4cf in do_command (thd=0x1456fc000c48) at /test/mtest/10.4_opt/sql/sql_parse.cc:1378
 
#19 0x000055b9233a5b7e in do_handle_one_connection (connect=connect@entry=0x55b9264717b8) at /test/mtest/10.4_opt/sql/sql_connect.cc:1419
 
#20 0x000055b9233a5caf in handle_one_connection (arg=0x55b9264717b8) at /test/mtest/10.4_opt/sql/sql_connect.cc:1323
 
#21 0x000014575ffab609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#22 0x000014575fb97133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.4.33 (dbg), 10.4.33 (opt), 10.5.24 (dbg), 10.5.24 (opt)

 Comments   
Comment by Ramesh Sivaraman [ 2023-11-17 ]

The crash occurs after this commit

commit eb8053b37756fe99e8ca12bbd966726d9f21c91a (HEAD, origin/bb-10.4-MDEV-31995)
 
Author: Rex <rex.johnston@mariadb.com>
 
Date:   Mon Sep 25 12:56:30 2023 +1100
 
 
 
    MDEV-31995 Bogus error executing PS for query using CTE with renaming of columns

Comment by Rex Johnston [ 2023-11-20 ]

One of MariaDB's query simplification techniques, obliquely referred to here https://mariadb.com/kb/en/optimizing-group-by/ is removing group by clauses in sub-queries where the outer condition allows this. These conditions include exists, in, > and <, where duplicate rows are irrelevant to the outcome. Removing these clauses allows the query planner more latitude in selecting a good execution plan. This simplification takes place during the prepare phase of join processing. The whole "GROUP BY" clause is removed, both simple column references and expressions. Any containing unit in sub-selects that have been removed have the select_lex structure removed, the pointer set to null (this is important as you'll see later).

Any sub-selects, removed by the above simplification, that also contain a view will be affected by this bug.

Consider the following example

create table t1 (a int, b int);
 
insert into t1 values (1,2),(3,4);
 
create view v1 as select * from t1;
 
PREPARE s FROM 'select exists (select * from t1 group by a in (select * from v1))';
 
execute s;

A view is used in a sub-query clause,

select * from v1

which is part of a group by outer clause.

group by a in (select * from v1)

When preparing the statement,

PREPARE s FROM 'select exists (select * from t1 group by a in (select * from v1))';

mysql_test_select() will partially execute the statement and build the join structure(s) required for execution.
During JOIN::prepare(), remove_redundant_subquery_clauses() notices that

group by a in (select * from v1)

is redundant, as it is in a sub-query, there are no aggregate functions and no HAVING clause, so removes it.

This leaves the table structures associated with that instance of the view with it's containing unit having no select_lex pointers.
When the statement is executed, 

execute s;

mysql_derived_reinit() is called to reinitialize the unit and select_lex item list names.
As there is no select_lex (removed as described above), we crash.

Note that if a normal table is removed

PREPARE s FROM 'select exists (select * from t1 group by a in (select a from t1))';
 
execute s;

mysql_derived_reinit() is not called.

I should also note that a stored procedure can be used to cause this crash.

create procedure aproc() select exists (select * from t1 group by a in (select * from v1));
 
call aproc();
 
call aproc();

During the second execution, mysql_derived_reinit() will be called on the emptied unit that used to represent

select * from v1

and crash.

CTEs and derived tables are not affected as only a view has a table structure left over from a previous prepare, they are both "local" to the currently executing query in this respect.

Fixed by ensuring restore_item_list_names() is never called from a null pointer.

Comment by Igor Babaev [ 2023-11-22 ]

Ok to push into 10.4

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