[MDEV-25601] MariaDB 10.5 crash when execute query with SET Created: 2021-05-05  Updated: 2021-06-09  Resolved: 2021-06-09

Status: Closed
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.5
Fix Version/s: 10.5.11

Type: Bug Priority: Critical
Reporter: Joanne Seneque Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: regression
Environment:

Windows server 2012 R2

Attachments: Text File mariadb_error_log.txt     File my.ini    
Issue Links:
Problem/Incident
is caused by MDEV-23094 Multiple calls to a Stored Procedure ... Closed
Relates
relates to MDEV-23886 Reusing CTE inside a function fails w... Closed

 Description   

Hello,

MariaDb 10.5.9 crashes when executing the following :

SET @scopeId   = 3004,
 
    @NPSER     = (SELECT pd.id FROM shrm_payded pd  WHERE pd.scopeId = @scopeId AND   pd.pdCode = '001'),
 
    @NPSERA    = (SELECT pd.id FROM shrm_payded pd WHERE pd.scopeId = @scopeId  AND   pd.pdCode = '001');

When having each SET on a single line (i.e. ending with ";") it works fine.

This same query executes without error on MariaDB 10.3.14, but in 10.5.9 it crashes the SQL server.

Is there a configuration / system variable that needs to be set so that it executes correctly ?

Thanks

Joanne

NOTE: attached an extract of the error log and the "my.ini" file

 Comments   
Comment by Alice Sherepa [ 2021-05-05 ]

Thanks for the report!

CREATE TABLE t1 (id int);
 
INSERT INTO t1 VALUES (1),(2);
 
SET @a=5, @b = (SELECT id FROM t1 WHERE id = @a), @c = (SELECT id FROM t1 WHERE id = @a);

Repeatable on 10.5, not on earlier versions.
Regression started from 10.5.7, after this merge https://github.com/MariaDB/server/commit/5ff7e68c7e

10.5 d44a10f4dd3567fd84a7f

 
Version: '10.5.10-MariaDB-debug-log'  
mariadbd: /10.5/sql/sql_select.cc:4225: bool JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool): Assertion `select_lex->select_number == (0x7fffffff * 2U + 1U) || select_lex->select_number == 0x7fffffff || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex' failed.
 
210505 15:25:25 [ERROR] mysqld got signal 6 ;
 
 
 
??:0(__restore_rt)[0x7eff968f0730]
 
linux/raise.c:51(__GI_raise)[0x7eff9642c7bb]
 
stdlib/abort.c:81(__GI_abort)[0x7eff96417535]
 
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7eff9641740f]
 
??:0(__assert_fail)[0x7eff96425102]
 
sql/sql_select.cc:4227(JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool))[0x55cd79273e3c]
 
sql/sql_select.cc:1609(JOIN::build_explain())[0x55cd79257ba1]
 
sql/sql_select.cc:1670(JOIN::optimize())[0x55cd792586c7]
 
sql/item_subselect.cc:3995(subselect_single_select_engine::exec())[0x55cd79bd0c6f]
 
sql/item_subselect.cc:834(Item_subselect::exec())[0x55cd79bad795]
 
sql/item_subselect.cc:1441(Item_singlerow_subselect::val_int())[0x55cd79bb31c5]
 
sql/item_func.cc:5120(Item_func_set_user_var::check(bool))[0x55cd79b040c7]
 
sql/set_var.cc:895(set_var_user::check(THD*))[0x55cd78f03aff]
 
sql/set_var.cc:739(sql_set_variables(THD*, List<set_var_base>*, bool))[0x55cd78f02aa0]
 
sql/sql_parse.cc:5099(mysql_execute_command(THD*))[0x55cd791ae6d7]
 
sql/sql_parse.cc:8099(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55cd791c2460]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55cd791988d9]
 
sql/sql_parse.cc:1370(do_command(THD*))[0x55cd791951e5]
 
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x55cd795c7b76]
 
sql/sql_connect.cc:1314(handle_one_connection)[0x55cd795c74d3]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55cd7a2927aa]
 
nptl/pthread_create.c:487(start_thread)[0x7eff968e5fa3]
 
x86_64/clone.S:97(clone)[0x7eff964ee4cf]
 
 
 
Query (0x62b0000382a8): SET @a=5, @b = (SELECT id FROM t1 WHERE id = @a), @c = (SELECT id FROM t1 WHERE id = @a)
 



Version: '10.5.9-MariaDB'  
210505 15:20:57 [ERROR] mysqld got signal 11 ;
 
 
 
??:0(__restore_rt)[0x7f9d71922730]
 
sql/sql_analyze_stmt.h:139(JOIN::exec())[0x55a49b1f8a93]
 
sql/item_subselect.cc:4001(subselect_single_select_engine::exec())[0x55a49b4540f5]
 
sql/item_subselect.cc:787(Item_subselect::exec())[0x55a49b45286a]
 
sql/item_subselect.cc:1375(Item_singlerow_subselect::val_int())[0x55a49b4530be]
 
sql/item_func.cc:5090(Item_func_set_user_var::check(bool))[0x55a49b41329c]
 
sql/set_var.cc:895(set_var_user::check(THD*))[0x55a49b0df39c]
 
sql/set_var.cc:739(sql_set_variables(THD*, List<set_var_base>*, bool))[0x55a49b0e1159]
 
sql/sql_parse.cc:5068(mysql_execute_command(THD*))[0x55a49b19c80a]
 
sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a49b19f169]
 
sql/sql_audit.h:169(mysql_audit_general)[0x55a49b1a1215]
 
sql/sql_parse.cc:1375(do_command(THD*))[0x55a49b1a2bdb]
 
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x55a49b28dbc2]
 
sql/sql_connect.cc:1318(handle_one_connection)[0x55a49b28de84]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55a49b61128d]
 
nptl/pthread_create.c:487(start_thread)[0x7f9d71917fa3]
 
x86_64/clone.S:97(clone)[0x7f9d714f14cf]
 
 
 
Query (0x7f9c68011de0): SET @a=5, @b = (SELECT id FROM t1 WHERE id = @a), @c = (SELECT id FROM t1 WHERE id = @a)

Comment by Sergei Golubchik [ 2021-05-05 ]

This is caused by https://github.com/MariaDB/server/commit/0f080dd60a9a4fb — a fix for MDEV-23094

Comment by Joanne Seneque [ 2021-05-11 ]

Hello, do you have an idea in which version and when this issue will be resolved ?

Thanks & Regards

Joanne

Comment by Sergei Golubchik [ 2021-05-13 ]

No promises, but it's a regression, so hopefully in the next release

Comment by Joanne Seneque [ 2021-05-31 ]

Hello, can we get an estimated date for the next release with this correction ? Else we have a lot of SQLs to change in our existing code and management is putting pressure on our team.

Thanks & Regards,
Joanne

Comment by Sergei Golubchik [ 2021-06-05 ]

Yes, you can see the estimated date for the next release at jira.mariadb.org. At the moment it shows

MariaDB Server : 10.5.11 2021-07-30

Note, that we cannot say whether the fix for this regression will be in 10.5.11, but it's very likely

Comment by Oleksandr Byelkin [ 2021-06-09 ]

It looks like it was fixed:

CREATE TABLE t1 (id int);
 
INSERT INTO t1 VALUES (1),(2);
 
SET @a=5, @b = (SELECT id FROM t1 WHERE id = @a), @c = (SELECT id FROM t1 WHERE id = @a);
 
select @a, @b, @c;
 
@a	@b	@c
 
5	NULL	NULL
 
INSERT INTO t1 VALUES (5);
 
SET @a=5, @b = (SELECT id FROM t1 WHERE id = @a), @c = (SELECT id FROM t1 WHERE id = @a);
 
select @a, @b, @c;
 
@a	@b	@c
 
5	5	5
 
drop table t1;

Comment by Elena Stepanova [ 2021-06-09 ]

The failure disappeared after this patch:

commit 675716e1cb34e29f7d3f46aac9a93adf02ac0851
 
Author: Igor Babaev
 
Date:   Tue May 25 17:13:17 2021 -0700
 
 
 
    MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
 
    
    In the code existed just before this patch binding of a table reference to

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