[MDEV-29217] Assertion failure in Item_param::can_return_value upon PS with SET STATEMENT and placeholder for a value Created: 2022-07-30  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-24860 Incorrect behaviour of SET STATEMENT ... Closed
Relates
relates to MDEV-29074 GET_BIT variables crash in SET STATEMENT Closed

 Description   

Apparently the range of affected variables is the same as in MDEV-29074 (GET_BIT), although I'm not 100% certain.

prepare stmt from "set statement log_queries_not_using_indexes= ? for select @@log_queries_not_using_indexes";

10.3 25219920

 
mysqld: /data/src/10.3/sql/item.cc:4672: bool Item_param::can_return_value() const: Assertion `0' failed.
 
220730 21:19:25 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f5e25b50662 in __GI___assert_fail (assertion=0x558a313f2f23 "0", file=0x558a313f3df8 "/data/src/10.3/sql/item.cc", line=4672, function=0x558a313f5508 "bool Item_param::can_return_value() const") at assert.c:101
 
#8  0x0000558a309776c1 in Item_param::can_return_value (this=0x7f5e0412a7b0) at /data/src/10.3/sql/item.cc:4672
 
#9  0x0000558a3098d344 in Item_param::val_str (this=0x7f5e0412a7b0, str=0x7f5e20377320) at /data/src/10.3/sql/item.h:3638
 
#10 0x0000558a307cf771 in Sys_var_typelib::do_check (this=0x558a31c6d220 <Sys_log_queries_not_using_indexes>, thd=0x7f5e04000d90, var=0x7f5e0412a968) at /data/src/10.3/sql/sys_vars.inl:299
 
#11 0x0000558a30522379 in sys_var::check (this=0x558a31c6d220 <Sys_log_queries_not_using_indexes>, thd=0x7f5e04000d90, var=0x7f5e0412a968) at /data/src/10.3/sql/set_var.cc:248
 
#12 0x0000558a30523dd2 in set_var::check (this=0x7f5e0412a968, thd=0x7f5e04000d90) at /data/src/10.3/sql/set_var.cc:790
 
#13 0x0000558a30523afb in sql_set_variables (thd=0x7f5e04000d90, var_list=0x7f5e04129e00, free=false) at /data/src/10.3/sql/set_var.cc:733
 
#14 0x0000558a3062b4d0 in run_set_statement_if_requested (thd=0x7f5e04000d90, lex=0x7f5e04128ef0) at /data/src/10.3/sql/sql_parse.cc:3390
 
#15 0x0000558a30657718 in Prepared_statement::prepare (this=0x7f5e04006d90, packet=0x7f5e04012d70 "set statement log_queries_not_using_indexes= ? for select @@log_queries_not_using_indexes", packet_len=89) at /data/src/10.3/sql/sql_prepare.cc:4231
 
#16 0x0000558a306541d5 in mysql_sql_stmt_prepare (thd=0x7f5e04000d90) at /data/src/10.3/sql/sql_prepare.cc:2927
 
#17 0x0000558a3062c90f in mysql_execute_command (thd=0x7f5e04000d90) at /data/src/10.3/sql/sql_parse.cc:3882
 
#18 0x0000558a3063a1d6 in mysql_parse (thd=0x7f5e04000d90, rawbuf=0x7f5e04012c20 "prepare stmt from \"set statement log_queries_not_using_indexes= ? for select @@log_queries_not_using_indexes\"", length=109, parser_state=0x7f5e203785b0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7871
 
#19 0x0000558a30626a0b in dispatch_command (command=COM_QUERY, thd=0x7f5e04000d90, packet=0x7f5e04008f31 "prepare stmt from \"set statement log_queries_not_using_indexes= ? for select @@log_queries_not_using_indexes\"", packet_length=109, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852
 
#20 0x0000558a306253c9 in do_command (thd=0x7f5e04000d90) at /data/src/10.3/sql/sql_parse.cc:1398
 
#21 0x0000558a307a2a14 in do_handle_one_connection (connect=0x558a32c2f170) at /data/src/10.3/sql/sql_connect.cc:1403
 
#22 0x0000558a307a277f in handle_one_connection (arg=0x558a32c2f170) at /data/src/10.3/sql/sql_connect.cc:1308
 
#23 0x0000558a31152e1e in pfs_spawn_thread (arg=0x558a32d2fa40) at /data/src/10.3/storage/perfschema/pfs.cc:1869
 
#24 0x00007f5e25ce9ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#25 0x00007f5e25c19def in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Before MDEV-29074 patch the failure was the same as in MDEV-29074.

Non-debug build returns

mysqltest: At line 1: query 'prepare stmt from "set statement log_queries_not_using_indexes= ? for select @@log_queries_not_using_indexes"' failed: 1231: Variable 'log_queries_not_using_indexes' can't be set to the value of 'NULL'



 Comments   
Comment by Sergei Golubchik [ 2022-08-06 ]

Not GET_BIT only:

prepare stmt from "set statement updatable_views_with_limit= ? for select 1";

Every variable is affected:

prepare stmt from "set statement tmp_disk_table_size=cast(? as int) for select 1";
 
prepare stmt from "set statement standard_compliant_cte= ? for select 1";

note that tmp_disk_table_size incorrectly thinks that ? is a string (it's another bug), so fails with "wrong type", but cast bypasses that

Comment by Sergei Golubchik [ 2022-08-06 ]

This is caused by the incorrect fix for MDEV-24860, that caused set statement to be evaluated at the prepare phase.
The reason for doing that was the case

prepare stmt from "set statement sql_mode = '' for create table t1 as select concat('abc') as c1";

without changing the sql_mode at the prepare phase, the table structure was incorrect.
And that happened, because of

bool Item_str_func::fix_fields(THD *thd, Item **ref)
 
{
 
  bool res= Item_func::fix_fields(thd, ref);
 
  maybe_null= maybe_null || thd->is_strict_mode();
 
  return res;
 
}

which made any Item_str_func (such as CONCAT() to decide at prepare phase whether it can be null or not, based on the sql_mode. And the test case above with the set statement was just one of the effects. Another one is

prepare stmt from "create table t1 as select concat('abc') as c1";
 
execute stmt;
 
show create table t1;
 
drop table t1;
 
set sql_mode=''; -- this does not affect the table structure below
 
execute stmt;
 
show create table t1;
 
drop table t1;

Or even something as simple as

set sql_mode='';
 
prepare stmt from 'select concat(a,b) from t1';
 
set sql_mode='strict_all_tables';
 
execute stmt;

can, likely, make a maybe_null=false item to return NULL.

Comment by Sergei Golubchik [ 2022-08-06 ]

possible ideas how to fix:

  • reprepare if sql_mode changes — wrong, will bring MDEV-11848 back
  • reprepare if strict mode changes — does not fix MDEV-24860 and other issues with set statement
  • reprepare if strict mode changes and MDEV-24860 — does not fix this MDEV-29217 crash if set statement cannot be evaluated at prepare time
  • forget all the above and recalculate nullability at execution time?
Comment by Sergei Golubchik [ 2022-08-06 ]

followup question, what other sql modes, except strict, can affect metadata?

  • those sql modes that can affect parsing must be stored and restored/reused on reprepare, like in MDEV-11848
  • those, that affect metadata must be treated like the strict mode, problems like in comments above
Comment by Oleksandr Byelkin [ 2022-08-11 ]

OK, I agree, to make it clear it should:

  • be reparsed in the same items independent of sql_mode
  • but executed according to the current sql_mode
Comment by Sergei Golubchik [ 2022-10-23 ]

More thoughts.

* reprepare if sql_mode changes — wrong, will bring MDEV-11848 back

why is that? MDEV-11848 is about automatic reprepare at some unspecified point in time. That is, the EXECUTE works, works, then some user in another connection does something and your EXECUTE starts behaving differently.

If we say that sql_mode changes forces a reprepare, it's not MDEV-11848, it's a reprepare caused by explicit user action and it happens at a well defined and user-controlled point in time — on the first EXECUTE after the sql_mode change. So this could be a solution too — if we define that any sql_mode changes cause reprepare.

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