[MDEV-31175] Server crash on 2n execution of select with view and aggregate function Created: 2023-05-03  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Prepared Statements, Views
Affects Version/s: 10.4, 10.5, 10.6, 10.8, 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

Attachments: File v1.frm    
Issue Links:
Blocks
is blocked by MDEV-30073 Wrong result on 2nd execution of PS f... Stalled
Duplicate
duplicates MDEV-26944 Server crash on selecting some data f... Confirmed
Relates
relates to MDEV-31003 Second execution for ps-protocol Stalled
relates to MDEV-31937 Server crash on 2nd execution query S... Open

 Description   

Server crash on second execution of prepare statement with view and aggregate function, when view has recursive column
Test case:

CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1,1), (2,2);
 
CREATE view v1 AS
SELECT a as x, (select x) as y FROM t1;
 
prepare stmt1 from "SELECT sum(y) FROM v1";
execute stmt1;
execute stmt1;
 
deallocate prepare stmt1;
 
DROP TABLE t1;
DROP VIEW v1;

Stacktrace:

Thread pointer: 0x7faef8000da0
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 = 0x7faf143f2c70 thread_stack 0x49000
mysys/stacktrace.c:174(my_print_stacktrace)[0x5576d476c68a]
sql/signal_handler.cc:238(handle_fatal_signal)[0x5576d3df9120]
libc_sigaction.c:0(__restore_rt)[0x7faf19242520]
nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7faf19296a7c]
posix/raise.c:27(__GI_raise)[0x7faf19242476]
stdlib/abort.c:81(__GI_abort)[0x7faf192287f3]
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7faf1922871b]
/lib/x86_64-linux-gnu/libc.so.6(+0x39e96)[0x7faf19239e96]
sql/item.cc:5430(resolve_ref_in_select_and_group(THD*, Item_ident*, st_select_lex*))[0x5576d3e2d33f]
sql/item.cc:5707(Item_field::fix_outer_field(THD*, Field**, Item**))[0x5576d3e2deb7]
sql/item.cc:5994(Item_field::fix_fields(THD*, Item**))[0x5576d3e2eeda]
sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5576d397705b]
sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5576d3977095]
sql/sql_base.cc:7738(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool))[0x5576d39fdc8f]
sql/sql_select.cc:1308(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5576d3ae96b9]
sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5576d3ef8d31]
sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5576d3eeabc7]
sql/table.cc:6702(create_view_field(THD*, TABLE_LIST*, Item**, st_mysql_const_lex_string*))[0x5576d3be8433]
sql/table.cc:6678(Field_iterator_view::create_item(THD*))[0x5576d3be8221]
sql/sql_base.cc:5848(find_field_in_view(THD*, TABLE_LIST*, char const*, unsigned long, char const*, Item**, bool))[0x5576d39f8975]
sql/sql_base.cc:6190(find_field_in_table_ref(THD*, TABLE_LIST*, char const*, unsigned long, char const*, char const*, char const*, Item**, bool, bool, unsigned int*, bool, TABLE_LIST**))[0x5576d39f9b0e]
sql/sql_base.cc:6426(find_field_in_tables(THD*, Item_ident*, TABLE_LIST*, TABLE_LIST*, Item**, find_item_error_report_type, bool, bool))[0x5576d39fa4e8]
sql/item.cc:5904(Item_field::fix_fields(THD*, Item**))[0x5576d3e2eb56]
sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5576d397705b]
sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5576d3977095]
sql/item_sum.cc:1122(Item_sum_num::fix_fields(THD*, Item**))[0x5576d3f062bb]
sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5576d397705b]
sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5576d3977095]
sql/sql_base.cc:7738(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool))[0x5576d39fdc8f]
sql/sql_select.cc:1308(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5576d3ae96b9]
sql/sql_select.cc:4774(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*))[0x5576d3af6a7f]
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5576d3ae5c99]
sql/sql_parse.cc:6463(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5576d3aa7d7c]
sql/sql_parse.cc:3966(mysql_execute_command(THD*))[0x5576d3a9e2d4]
sql/sql_prepare.cc:5024(Prepared_statement::execute(String*, bool))[0x5576d3acead5]
sql/sql_prepare.cc:4493(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x5576d3accd64]
sql/sql_prepare.cc:3578(mysql_sql_stmt_execute(THD*))[0x5576d3aca516]
sql/sql_parse.cc:3983(mysql_execute_command(THD*))[0x5576d3a9e319]
sql/sql_parse.cc:7998(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5576d3aabf13]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5576d3a97b41]
sql/sql_parse.cc:1378(do_command(THD*))[0x5576d3a9638d]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5576d3c38fc0]
sql/sql_connect.cc:1325(handle_one_connection)[0x5576d3c38d1c]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5576d41c350d]
nptl/pthread_create.c:442(start_thread)[0x7faf19294b43]
x86_64/clone3.S:83(__clone3)[0x7faf19326a00]



 Comments   
Comment by Igor Babaev [ 2023-07-21 ]

lstartseva: how is it supposed to work?

MariaDB [test]> SELECT a as x, (select x) as y FROM t1;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.002 sec)
 
MariaDB [test]> SELECT a as x, x as y FROM t1;
ERROR 1054 (42S22): Unknown column 'x' in 'field list'

Comment by Lena Startseva [ 2023-07-21 ]

igor, in these cases, it looks like this is the expected behavior.

Comment by Igor Babaev [ 2023-08-08 ]

If we use the derived table with the same specifying query as for the view v1 we also can observe a crash on the second execution of PS:

MariaDB [test]> PREPARE stmt FROM "
SELECT sum(y) FROM (SELECT a AS x, (select x) AS y FROM t1) dt;
";
Query OK, 0 rows affected (0.001 sec)
Statement prepared
 
MariaDB [test]> EXECUTE stmt;
+--------+
| sum(y) |
+--------+
|      3 |
+--------+
1 row in set (0.005 sec)
 
MariaDB [test]> EXECUTE stmt;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysqld: /home/igor/maria-git/10.4/sql/item.cc:5429: Item** resolve_ref_in_select_and_group(THD*, Item_ident*, SELECT_LEX*): Assertion `(*select_ref)->is_fixed()' failed.
 
Thread 33 "mysqld" received signal SIGABRT, Aborted.

Similar crashes happen on the second call of the stored procedures:

CREATE PROCEDURE p1() SELECT sum(y) FROM v1;
CREATE PROCEDURE p2() SELECT sum(y) FROM (SELECT a AS x, (select x) AS y FROM t1) dt;

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