Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.6.4, 10.6.5, 10.6, 10.7(EOL)
-
None
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) |
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
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; |
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.
Thank you!
Reproduced as described:
10.6.4
211118 10:29:18 [ERROR] mysqld got signal 11 ;Server version: 10.6.4-MariaDBsigaction.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 uld11on 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')