[MDEV-31281] Server crash on 2nd execution of query with window function and IN subquery Created: 2023-05-16  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-31003 Second execution for ps-protocol Stalled

 Description   

Server crash on second execution of prepare statement with derived table and aggregate function. Maybe the problem is the same with an issue MDEV-31175
Test case:

CREATE TABLE t1 (i int);
 
INSERT INTO t1 VALUES (1),(2),(3);
 
 
 
prepare stmt1 from "SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1";
 
 
 
execute stmt1;
 
execute stmt1;
 
 
 
deallocate prepare stmt1;
 
 
 
DROP TABLE t1;

Stacktrace:

Thread pointer: 0x7f6dd0000da0
 
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 = 0x7f6de2ad1c70 thread_stack 0x49000
 
mysys/stacktrace.c:174(my_print_stacktrace)[0x557f6960568a]
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x557f68c92120]
 
libc_sigaction.c:0(__restore_rt)[0x7f6de8a42520]
 
nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7f6de8a96a7c]
 
posix/raise.c:27(__GI_raise)[0x7f6de8a42476]
 
stdlib/abort.c:81(__GI_abort)[0x7f6de8a287f3]
 
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f6de8a2871b]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x39e96)[0x7f6de8a39e96]
 
sql/item_subselect.cc:1814(Item_in_subselect::val_int())[0x557f68d89161]
 
sql/item.cc:6713(Item::save_int_in_field(Field*, bool))[0x557f68cc9ce2]
 
sql/sql_type.cc:3843(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x557f68b7bc84]
 
sql/item.cc:6723(Item::save_in_field(Field*, bool))[0x557f68cc9d95]
 
sql/item.h:3283(Item_result_field::save_in_result_field(bool))[0x557f6889d56f]
 
sql/sql_select.cc:26086(copy_funcs(Item**, THD const*))[0x557f689ca4dc]
 
sql/sql_select.cc:22265(end_write(JOIN*, st_join_table*, bool))[0x557f689c061c]
 
sql/sql_select.cc:29512(AGGR_OP::put_record(bool))[0x557f689d4f84]
 
sql/sql_select.h:1054(AGGR_OP::put_record())[0x557f689dbaa9]
 
sql/sql_select.cc:20550(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x557f689bc283]
 
sql/sql_select.cc:21059(evaluate_join_record(JOIN*, st_join_table*, int))[0x557f689bcffb]
 
sql/sql_select.cc:20832(sub_select(JOIN*, st_join_table*, bool))[0x557f689bc84a]
 
sql/sql_select.cc:20366(do_select(JOIN*, Procedure*))[0x557f689bbc0a]
 
sql/sql_select.cc:4590(JOIN::exec_inner())[0x557f6898f336]
 
sql/sql_select.cc:4373(JOIN::exec())[0x557f6898e406]
 
sql/sql_select.cc:4813(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*))[0x557f6898fc02]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x557f6897ec99]
 
sql/sql_parse.cc:6463(execute_sqlcom_select(THD*, TABLE_LIST*))[0x557f68940d7c]
 
sql/sql_parse.cc:3966(mysql_execute_command(THD*))[0x557f689372d4]
 
sql/sql_prepare.cc:5024(Prepared_statement::execute(String*, bool))[0x557f68967ad5]
 
sql/sql_prepare.cc:4493(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x557f68965d64]
 
sql/sql_prepare.cc:3578(mysql_sql_stmt_execute(THD*))[0x557f68963516]
 
sql/sql_parse.cc:3983(mysql_execute_command(THD*))[0x557f68937319]
 
sql/sql_parse.cc:7998(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x557f68944f13]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x557f68930b41]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x557f6892f38d]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x557f68ad1fc0]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x557f68ad1d1c]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x557f6905c50d]
 
nptl/pthread_create.c:442(start_thread)[0x7f6de8a94b43]
 
x86_64/clone3.S:83(__clone3)[0x7f6de8b26a00]



 Comments   
Comment by Igor Babaev [ 2023-08-11 ]

The following test case that uses aggregare function instead of window function fails in the same way:

CREATE TABLE t1 (a int);
 
INSERT INTO t1 VALUES (1), (2), (3);
 
 
 
CREATE TABLE t2 (b int);
 
INSERT INTO t2 VALUES (3), (7), (1);
 
 
 
PREPARE stmt FROM "
 
SELECT SUM(a) IN ( SELECT b FROM t2 ) FROM t1;
 
";
 
EXECUTE stmt;
 
EXECUTE stmt;


MariaDB [test]> PREPARE stmt FROM "
 
    "> SELECT SUM(a) IN ( SELECT b FROM t2 ) FROM t1;
 
    "> ";
 
Query OK, 0 rows affected (0.000 sec)
 
Statement prepared
 
 
 
MariaDB [test]> execute stmt;
 
+--------------------------------+
 
| SUM(a) IN ( SELECT b FROM t2 ) |
 
+--------------------------------+
 
|                              0 |
 
+--------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> execute stmt;
 
MariaDB [test]> PREPARE stmt FROM "
 
    "> SELECT SUM(a) IN ( SELECT b FROM t2 ) FROM t1;
 
    "> ";
 
Query OK, 0 rows affected (0.000 sec)
 
Statement prepared
 
 
 
MariaDB [test]> execute stmt;
 
+--------------------------------+
 
| SUM(a) IN ( SELECT b FROM t2 ) |
 
+--------------------------------+
 
|                              0 |
 
+--------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> execute stmt;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query


mysqld: /home/igor/maria-git/10.4/sql/item_subselect.cc:1837: virtual String* Item_in_subselect::val_str(String*): Assertion `0' failed.
 
 
 
Thread 32 "mysqld" received signal SIGABRT, Aborted.

Comment by Igor Babaev [ 2023-08-11 ]

The following queries also fail on the second execution of PS in the same way as the above queries:

SELECT SUM(a) IN ( SELECT b+3 FROM t2 ) FROM t1;
 
SELECT SUM(a) OVER () IN ( SELECT b+3 FROM t2 ) FROM t1;

as well as the procedures:

CREATE PROCEDURE p1() SELECT SUM(a) IN ( SELECT b FROM t2 ) FROM t1;
 
CREATE PROCEDURE p2() SELECT SUM(a) IN ( SELECT b+3 FROM t2 ) FROM t1;
 
CREATE PROCEDURE p3() SELECT SUM(a) OVER () IN ( SELECT b FROM t2 ) FROM t1;
 
CREATE PROCEDURE p4() SELECT SUM(a) OVER () IN ( SELECT b+3 FROM t2 ) FROM t1;

fail on the second call of them.

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