[MDEV-27073] Crash when a date conversion function is the return value of DECODE() in a virtual column Created: 2021-11-17  Updated: 2023-03-03

Status: Confirmed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.6.4, 10.6.5, 10.6, 10.7
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Assen Totin (Inactive) Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-26407 Server crashes in Item_func_in::clean... Closed

 Description   

In Oracle compatibility mode it is possible to create a virtual column which value is computed by calling the DECODE() function that returns the value from a real column from the same table.

However, if the return value of DECODE() contains a date conversion function (that still uses a real column as a source), selecting the data immediately after it has been inserted results in a crash.

If the tables are flushed, or if server get restarted before the SELECT query, data is returned properly.

If the date conversion function is first put into another derived column, it seems the crash is also avoided.

Tested on both latest ES 10.6.4 and Community 10.6.5.

All examples below require SQL_MODE='oracle':

– Example 1: crash in SELECT right after the INSERT

CREATE TABLE uld11 (
 
	schedule_date DATE NOT NULL,
 
	hold_position VARCHAR(5),
 
	uld_type VARCHAR(3),
 
	hld_unique_vcol VARCHAR(160) AS 
		(DECODE 
			(uld_type, 'HLD', 
				SUBSTR(TO_CHAR(schedule_date,'mmddyyyy')||hold_position,1,10), NULL
 
			)
 
		)
 
);
 
 
 
INSERT INTO uld11 (SCHEDULE_DATE) VALUES ('2011-06-20');
 
 
 
MariaDB [test]> select * from uld11;
 
ERROR 2013 (HY000): Lost connection to server during query

– Example 2: after the server is restarted or FLUSH TABLES is ran, the same query returns proper result:

MariaDB [test]> select * from uld11;
 
+---------------------+---------------+----------+-----------------+
 
| schedule_date       | hold_position | uld_type | hld_unique_vcol |
 
+---------------------+---------------+----------+-----------------+
 
| 2011-06-20 00:00:00 | NULL          | NULL     | NULL            |
 
+---------------------+---------------+----------+-----------------+
 
1 row in set (0.001 sec)

– Example 3: moving the date function into another virtual column "tmp1" seems to avoid the crash:

CREATE TABLE uld12 (
 
	schedule_date DATE NOT NULL,
 
	hold_position VARCHAR(5),
 
	uld_type VARCHAR(3),
 
	tmp1  VARCHAR(160) AS (SUBSTR(TO_CHAR(schedule_date,'mmddyyyy')||hold_position,1,10)),
 
	hld_unique_vcol VARCHAR(160) AS 
		(DECODE (uld_type, 'HLD', tmp1, NULL))
 
);
 
 
 
INSERT INTO uld12 (SCHEDULE_DATE) VALUES ('2011-06-20');
 
 
 
MariaDB [test]> select * from uld12;
 
+---------------------+---------------+----------+----------+-----------------+
 
| schedule_date       | hold_position | uld_type | tmp1     | hld_unique_vcol |
 
+---------------------+---------------+----------+----------+-----------------+
 
| 2011-06-20 00:00:00 | NULL          | NULL     | 06202011 | NULL            |
 
+---------------------+---------------+----------+----------+-----------------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Alice Sherepa [ 2021-11-18 ]

Thank you!
Reproduced as described:

10.6.4

 
211118 10:29:18 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.6.4-MariaDB
 
 
 
sigaction.c:0(__restore_rt)[0x7f577434a3c0]
 
sql/item_cmpfunc.h:2094(Predicant_to_list_comparator::cmp_args_nulls_equal(THD*, Item_args*, unsigned int))[0x55bbeca18661]
 
sql/item_cmpfunc.h:2282(Predicant_to_list_comparator::cmp_nulls_equal(THD*, Item_args*, unsigned int*))[0x55bbeca0782d]
 
sql/item_cmpfunc.cc:3036(Item_func_case::str_op(String*))[0x55bbec9fcb76]
 
sql/item.cc:6665(Item::save_str_in_field(Field*, bool))[0x55bbec9ecf61]
 
sql/item.cc:6713(Item::save_in_field(Field*, bool))[0x55bbec9dea46]
 
sql/table.cc:8736(TABLE::update_virtual_fields(handler*, enum_vcol_update_mode))[0x55bbec872fef]
 
sql/handler.cc:3411(handler::ha_rnd_next(unsigned char*))[0x55bbec9cc25a]
 
sql/records.cc:519(rr_sequential(READ_RECORD*))[0x55bbecb0fd8c]
 
sql/sql_select.cc:21055(sub_select(JOIN*, st_join_table*, bool))[0x55bbec7db2ba]
 
sql/sql_select.cc:20605(JOIN::exec_inner())[0x55bbec809abf]
 
sql/sql_select.cc:4516(JOIN::exec())[0x55bbec809e43]
 
sql/sql_select.cc:4995(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55bbec80813e]
 
sql/sql_select.cc:557(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55bbec8089b4]
 
sql/sql_parse.cc:6257(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55bbec6700aa]
 
sql/sql_parse.cc:3946(mysql_execute_command(THD*, bool))[0x55bbec7ac612]
 
sql/sql_parse.cc:8031(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55bbec7aea6b]
 
sql/sql_parse.cc:1955(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55bbec7b0cd7]
 
sql/sql_parse.cc:1406(do_command(THD*, bool))[0x55bbec7b2373]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55bbec8a6f97]
 
sql/sql_connect.cc:1318(handle_one_connection)[0x55bbec8a7234]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55bbecc2e8d2]
 
nptl/pthread_create.c:478(start_thread)[0x7f577433e609]
 
??:0(clone)[0x7f5773f2c293]
 
 
 
Query (0x7f5720010b20): select * from uld11

on debug (with asan) version I've got an assertion after insert:

 /10.6/src/sql/field.cc:7135: virtual bool Field_datetimef::get_TIME(MYSQL_TIME*, const uchar*, date_mode_t) const: Assertion `marked_for_read()' failed.
 
211118 10:14:11 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.6.6-MariaDB-debug-log
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x25729)[0x7f3f30b69729]
 
??:0(__assert_fail)[0x7f3f30b7af36]
 
sql/field.cc:7136(Field_datetimef::get_TIME(st_mysql_time*, unsigned char const*, date_mode_t) const)[0x55fb8ab34b83]
 
sql/field.h:3935(Field_datetimef::get_date(st_mysql_time*, date_mode_t))[0x55fb8ab63741]
 
sql/item.cc:3377(Item_field::get_date(THD*, st_mysql_time*, date_mode_t))[0x55fb8ac03c9b]
 
sql/item_timefunc.cc:2696(Item_func_tochar::val_str(String*))[0x55fb8ae51b29]
 
sql/item_strfunc.h:1712(Item_func_conv_charset::Item_func_conv_charset(THD*, Item*, charset_info_st const*, bool))[0x55fb8ac4c6c5]
 
sql/item.cc:1292(Item::safe_charset_converter(THD*, charset_info_st const*))[0x55fb8abf1c57]
 
sql/item.cc:2662(Type_std_attributes::agg_item_set_converter(DTCollation const&, st_mysql_const_lex_string const&, Item**, unsigned int, unsigned int, int))[0x55fb8abfce02]
 
sql/sql_type.h:3249(Type_std_attributes::agg_arg_charsets(DTCollation&, st_mysql_const_lex_string const&, Item**, unsigned int, unsigned int, int))[0x55fb8a8dc64b]
 
sql/sql_type.h:3264(Type_std_attributes::agg_arg_charsets_for_string_result(DTCollation&, st_mysql_const_lex_string const&, Item**, unsigned int, int))[0x55fb8a8dc6a5]
 
sql/item.h:5366(Item_func_or_sum::agg_arg_charsets_for_string_result(DTCollation&, Item**, unsigned int, int))[0x55fb8a8dcebd]
 
sql/item_strfunc.cc:703(Item_func_concat::fix_length_and_dec())[0x55fb8ad7e3c4]
 
sql/item_func.cc:359(Item_func::fix_fields(THD*, Item**))[0x55fb8ace4e59]
 
sql/item_strfunc.cc:122(Item_str_func::fix_fields(THD*, Item**))[0x55fb8ad7944d]
 
sql/item.h:1144(Item::fix_fields_if_needed(THD*, Item**))[0x55fb8a082ec3]
 
sql/item_func.cc:347(Item_func::fix_fields(THD*, Item**))[0x55fb8ace4ba6]
 
sql/item_strfunc.cc:122(Item_str_func::fix_fields(THD*, Item**))[0x55fb8ad7944d]
 
sql/item.h:1144(Item::fix_fields_if_needed(THD*, Item**))[0x55fb8a082ec3]
 
sql/item_func.cc:347(Item_func::fix_fields(THD*, Item**))[0x55fb8ace4ba6]
 
sql/item_cmpfunc.cc:3132(Item_func_case::fix_fields(THD*, Item**))[0x55fb8ac7b625]
 
sql/table.cc:3596(fix_vcol_expr(THD*, Virtual_column_info*))[0x55fb8a6a1aba]
 
sql/table.cc:3624(fix_session_vcol_expr(THD*, Virtual_column_info*))[0x55fb8a6a20db]
 
sql/sql_base.cc:5439(TABLE::fix_vcol_exprs(THD*))[0x55fb8a19dc47]
 
sql/sql_base.cc:5475(fix_all_session_vcol_exprs(THD*, TABLE_LIST*))[0x55fb8a19e225]
 
sql/sql_base.cc:5659(lock_tables(THD*, TABLE_LIST*, unsigned int, unsigned int))[0x55fb8a19f4cf]
 
sql/sql_base.cc:5271(open_and_lock_tables(THD*, DDL_options_st const&, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*))[0x55fb8a19cbbc]
 
sql/sql_base.h:509(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int))[0x55fb8a0f3d60]
 
sql/sql_insert.cc:757(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x55fb8a2696ff]
 
sql/sql_parse.cc:4565(mysql_execute_command(THD*, bool))[0x55fb8a336de8]
 
sql/sql_parse.cc:8030(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55fb8a34f630]
 
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55fb8a3256f1]
 
sql/sql_parse.cc:1404(do_command(THD*, bool))[0x55fb8a322415]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55fb8a788ec3]
 
sql/sql_connect.cc:1314(handle_one_connection)[0x55fb8a78874f]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55fb8b4a8329]
 
nptl/pthread_create.c:478(start_thread)[0x7f3f31093609]
 
??:0(clone)[0x7f3f30c66293]
 
 
 
Query (0x62b0001182a8): INSERT INTO uld11 (SCHEDULE_DATE) VALUES ('2011-06-20')

Comment by Alice Sherepa [ 2021-11-18 ]

It is probably related to MDEV-26407 (but here in the test TO_CHAR function is used, so the test is applicable only on 10.6+)
Similar to MDEV-26407 tests:

set sql_mode='oracle';
 
create table t1 (d1 varchar(100) default (decode ('a', to_char(now(),'mmddyyyy'), 1 ))); 
insert into t1 values ();
 
insert into t1 values ();

10.6 0a168398a0a1f80ae9bec

 
Version: '10.6.6-MariaDB-debug-log'  
211118 11:09:03 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.6.6-MariaDB-debug-log
 
 
 
sigaction.c:0(__restore_rt)[0x7f120109f3c0]
 
sql/item_cmpfunc.h:2115(Predicant_to_list_comparator::Predicant_to_value_comparator::cleanup())[0x55cb6299e075]
 
sql/item_cmpfunc.h:2223(Predicant_to_list_comparator::cleanup())[0x55cb6299e579]
 
sql/item_cmpfunc.h:2408(Item_func_case_simple::cleanup())[0x55cb6299eaae]
 
sql/item.cc:574(Item::cleanup_processor(void*))[0x55cb62954469]
 
sql/item.h:2143(Item::cleanup_excluding_fields_processor(void*))[0x55cb626ee586]
 
sql/item.h:5437(Item_func_or_sum::walk(bool (Item::*)(void*), bool, void*))[0x55cb624ee068]
 
sql/table.cc:3623(fix_session_vcol_expr(THD*, Virtual_column_info*))[0x55cb624ee234]
 
sql/sql_base.cc:5443(TABLE::fix_vcol_exprs(THD*))[0x55cb624ee93a]
 
sql/sql_base.cc:5475(fix_all_session_vcol_exprs(THD*, TABLE_LIST*))[0x55cb624eda86]
 
sql/sql_base.cc:5659(lock_tables(THD*, TABLE_LIST*, unsigned int, unsigned int))[0x55cb624a366b]
 
sql/sql_base.cc:5271(open_and_lock_tables(THD*, DDL_options_st const&, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*))[0x55cb6254395e]
 
sql/sql_base.h:509(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int))[0x55cb62599e74]
 
sql/sql_insert.cc:757(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x55cb625a596f]
 
sql/sql_parse.cc:4565(mysql_execute_command(THD*, bool))[0x55cb62591d9b]
 
sql/sql_parse.cc:8030(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55cb62590737]
 
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55cb6274f0d3]
 
sql/sql_parse.cc:1404(do_command(THD*, bool))[0x55cb6274ed63]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55cb62cc15eb]
 
nptl/pthread_create.c:478(start_thread)[0x7f1201093609]
 
??:0(clone)[0x7f1200c66293]
 
 
 
Query (0x7f11c00156e0): insert into t1 values ()


set sql_mode='oracle';
 
create table t1 (d1 varchar(100) default (decode ('a', to_char(now(),'mmddyyyy'), 1 ))); 
select default(d1) from t1;

Comment by Assen Totin (Inactive) [ 2021-11-18 ]

I got a hint from a colleague that the particular date conversion function may be part of the culprit - using DATE_FORMAT() instead of TO_CHAR() seems not to trigger the bug.

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