[MDEV-25772] Virtual Column with CURDATE function cause suddenly entire MariaDB crash - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Duplicate
Affects Version/s: 10.3.28, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Fix Version/s: N/A
Component/s: Data Manipulation - Subquery
Labels:
- crash
- virtual_columns
Environment:
CentOS

Description

I see that MariaDb is restarting continuosly after I inserted the new column "is_available".
This is a virtual column on:

`date_availability_start` < curdate() and `date_availability_end` > curdate()

It crash with a 30% of possibility if I run just:

SELECT * FROM `products_gas` LIMIT 0, 25

So every 3 refresh the server is restarting.
I decided to drop the column and now it works back again.
I'll attach the log to this.

CREATE TABLE `products_gas` (

  `product_gas_id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `provider_id` int(11) unsigned NOT NULL,

  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

  `product_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

  `market_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,

  `customer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.customer_type'))) VIRTUAL,

  `offer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.offer_type'))) VIRTUAL,

  `product_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

  `parsed_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,

  `is_parsed` tinyint(1) GENERATED ALWAYS AS (`date_parsed` is not null) VIRTUAL,

  `is_available` tinyint(1) GENERATED ALWAYS AS (`date_availability_start` < curdate() and `date_availability_end` > curdate()) VIRTUAL,

  `is_deleted` tinyint(1) GENERATED ALWAYS AS (`date_deleted` is not null) VIRTUAL,

  `date_created` datetime NOT NULL,

  `date_parsed` datetime DEFAULT NULL,

  `date_availability_start` datetime DEFAULT NULL,

  `date_availability_end` datetime DEFAULT NULL,

  `date_deleted` datetime DEFAULT NULL,

  PRIMARY KEY (`product_gas_id`),

  KEY `provider_id` (`provider_id`),

  KEY `product_code` (`product_code`),

  KEY `market_type` (`market_type`),

  KEY `customer_type` (`customer_type`),

  KEY `offer_type` (`offer_type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mariadb-crash-log.txt
2021-09-13 22:22
4 kB
Roland
mariadb-crash-log.txt
2021-05-25 17:10
7 kB
Massimiliano Cuttini

Issue Links

is duplicated by

MDEV-24176 Server crashes after insert in the table with virtual column generated using date_format() and if()

Closed

MDEV-26619 CURDATE() functions results in lost connection when used in a VIRTUAL column

Closed

relates to

MDEV-26437 Server crashes in Item_args::walk_args

Closed

Activity

Ascending order - Click to sort in descending order

Roland added a comment - 2021-09-13 22:24

I can reproduce this on 10.6.4 using the official docker image.

I'm trying to create a column automatically marking users in a forum invisible if they haven't logged in in a year and never made a post:

ALTER TABLE u_users ADD COLUMN `invisible` INT GENERATED ALWAYS AS (CASE WHEN (posts < 1 AND last_online < date_sub(curdate(), interval 1 year)) THEN 1 ELSE 0 END) VIRTUAL AFTER `last_consent`;

The column is created just fine and the database happily returns it as part of SELECT queries. Once I try to use it in a condition like a WHERE clause, however, the database reliably crashes.

mariadb-crash-log.txt

Roland added a comment - 2021-09-13 22:24 I can reproduce this on 10.6.4 using the official docker image. I'm trying to create a column automatically marking users in a forum invisible if they haven't logged in in a year and never made a post: ALTER TABLE u_users ADD COLUMN `invisible` INT GENERATED ALWAYS AS (CASE WHEN (posts < 1 AND last_online < date_sub(curdate(), interval 1 year)) THEN 1 ELSE 0 END) VIRTUAL AFTER `last_consent`; The column is created just fine and the database happily returns it as part of SELECT queries. Once I try to use it in a condition like a WHERE clause, however, the database reliably crashes. mariadb-crash-log.txt

Alice Sherepa added a comment - 2021-09-14 09:07

Thank you!
I repeated on 10.2-10.6
This is probably some variation of ~~MDEV-24176~~ (and ~~MDEV-26437~~)

--source include/have_innodb.inc

CREATE TABLE `products_gas` (

  `product_gas_id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `provider_id` int(11) unsigned NOT NULL,

  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

  `product_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

  `market_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,

  `customer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.customer_type'))) VIRTUAL,

  `offer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.offer_type'))) VIRTUAL,

  `product_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

  `parsed_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,

  `is_parsed` tinyint(1) GENERATED ALWAYS AS (`date_parsed` is not null) VIRTUAL,

  `is_available` tinyint(1) GENERATED ALWAYS AS (`date_availability_start` < curdate() and `date_availability_end` > curdate()) VIRTUAL,

  `is_deleted` tinyint(1) GENERATED ALWAYS AS (`date_deleted` is not null) VIRTUAL,

  `date_created` datetime NOT NULL,

  `date_parsed` datetime DEFAULT NULL,

  `date_availability_start` datetime DEFAULT NULL,

  `date_availability_end` datetime DEFAULT NULL,

  `date_deleted` datetime DEFAULT NULL,

  PRIMARY KEY (`product_gas_id`),

  KEY `provider_id` (`provider_id`),

  KEY `product_code` (`product_code`),

  KEY `market_type` (`market_type`),

  KEY `customer_type` (`customer_type`),

  KEY `offer_type` (`offer_type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into products_gas(`product_gas_id`,`provider_id`,`product_name`,`product_code`,`market_type`,`product_raw`,`parsed_raw`,`date_created`,`date_parsed`,`date_availability_start`,`date_availability_end`,`date_deleted`) values

(1,1,'1','1','1','1','1','2021-09-05 08:38:23','2021-09-05 08:38:23','2021-09-11 08:38:23','2021-09-11 08:38:23','2021-09-06 08:38:23'),(2,2,'2','2','2','2','2','2021-09-13 08:38:23','2021-09-06 08:38:23','2021-09-01 08:38:23','2021-09-04 08:38:23','2021-09-14 08:38:23');

SELECT * FROM `products_gas` where is_available=1;

SELECT * FROM `products_gas` LIMIT 0, 25;

on 10.3:

10.3 46cb16388a865d9561ba23401
Version: '10.3.32-MariaDB-debug-log'
210914 10:53:06 [ERROR] mysqld got signal 11 ;

sql/signal_handler.cc:221(handle_fatal_signal)[0x55e242502d6e]
sigaction.c:0(__restore_rt)[0x7f67f9f703c0]
sql/item_cmpfunc.cc:751(Arg_comparator::compare_datetime())[0x55e2425c379c]
sql/item_cmpfunc.h:102(Arg_comparator::compare())[0x55e24260369e]
sql/item_cmpfunc.cc:1803(Item_func_lt::val_int())[0x55e2425d0f81]
sql/sql_type.cc:3288(Type_handler_int_result::Item_val_bool(Item*) const)[0x55e24229f01a]
sql/item.h:1220(Item::val_bool())[0x55e241affcd4]
sql/item_cmpfunc.cc:5077(Item_cond_and::val_int())[0x55e2425efe72]
sql/item.cc:6890(Item::save_int_in_field(Field*, bool))[0x55e242583997]
sql/sql_type.cc:2593(Type_handler_int_result::Item_save_in_field(Item, Field, bool) const)[0x55e24229caa6]
sql/item.cc:6900(Item::save_in_field(Field*, bool))[0x55e242583b7b]
sql/table.cc:8006(TABLE::update_virtual_fields(handler*, enum_vcol_update_mode))[0x55e2420a5fe8]
sql/handler.cc:2866(handler::ha_rnd_next(unsigned char*))[0x55e242518a6c]
sql/records.cc:485(rr_sequential(READ_RECORD*))[0x55e242913673]
sql/records.h:70(READ_RECORD::read_record())[0x55e241c0059e]
sql/sql_select.cc:20781(join_init_read_record(st_join_table*))[0x55e241ecd4bc]
sql/sql_select.cc:19842(sub_select(JOIN, st_join_table, bool))[0x55e241ec671c]
sql/sql_select.cc:19385(do_select(JOIN, Procedure))[0x55e241ec4a70]
sql/sql_select.cc:4142(JOIN::exec_inner())[0x55e241e57c75]
sql/sql_select.cc:3937(JOIN::exec())[0x55e241e555f2]
sql/sql_select.cc:4346(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))[0x55e241e590da]
sql/sql_select.cc:372(handle_select(THD, LEX, select_result*, unsigned long))[0x55e241e2f77b]
sql/sql_parse.cc:6339(execute_sqlcom_select(THD, TABLE_LIST))[0x55e241da0d9b]
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x55e241d8edd6]
sql/sql_parse.cc:7870(mysql_parse(THD, char, unsigned int, Parser_state*, bool, bool))[0x55e241daaaf8]
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD, char, unsigned int, bool, bool))[0x55e241d819d5]
sql/sql_parse.cc:1398(do_command(THD*))[0x55e241d7e518]
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55e24214e18f]
sql/sql_connect.cc:1309(handle_one_connection)[0x55e24214da49]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e2437794e9]
nptl/pthread_create.c:478(start_thread)[0x7f67f9f64609]
x86_64/clone.S:97(__GI___clone)[0x7f67f9e8b293]

Query (0x62b000000290): SELECT * FROM `products_gas` LIMIT 0, 25

on 10.2:

10.2 c7184c470eec1777cd6ea
#3 <signal handler called>
#4 0x0000556b91056393 in Item::val_temporal_packed (this=0x7ff9980150b8, f_type=MYSQL_TYPE_DATETIME) at /10.2/src/sql/item.h:1531
#5 0x0000556b9106074b in Arg_comparator::compare_temporal (this=0x7ff9980a6208, type=MYSQL_TYPE_DATETIME) at /10.2/src/sql/item_cmpfunc.cc:797
#6 0x0000556b90caeaff in Arg_comparator::compare_datetime (this=0x7ff9980a6208) at /10.2/src/sql/item_cmpfunc.h:105
#7 0x0000556b91075556 in Arg_comparator::compare (this=0x7ff9980a6208) at /10.2/src/sql/item_cmpfunc.h:87
#8 0x0000556b91063c63 in Item_func_lt::val_int (this=0x7ff9980a6148) at /10.2/src/sql/item_cmpfunc.cc:1860
#9 0x0000556b91034e53 in Item::val_bool (this=0x7ff9980a6148) at /10.2/src/sql/item.cc:112
#10 0x0000556b9106e449 in Item_cond_and::val_int (this=0x7ff9980a6d58) at /10.2/src/sql/item_cmpfunc.cc:5092
#11 0x0000556b91046aaa in Item::save_in_field (this=0x7ff9980a6d58, field=0x7ff9980393c0, no_conversions=false) at /10.2/src/sql/item.cc:6429
#12 0x0000556b90eb36b9 in TABLE::update_virtual_fields (this=0x7ff998035300, h=0x7ff998035f08, update_mode=VCOL_UPDATE_FOR_READ) at /10.2/src/sql/table.cc:7793
#13 0x0000556b910265f8 in handler::ha_rnd_next (this=0x7ff998035f08, buf=0x7ff998036eb8 "3\374\001") at /10.2/src/sql/handler.cc:2675
#14 0x0000556b91199dd7 in rr_sequential (info=0x7ff998015a40) at /10.2/src/sql/records.cc:492
#15 0x0000556b90e0e137 in join_init_read_record (tab=0x7ff998015978) at /10.2/src/sql/sql_select.cc:19821
#16 0x0000556b90e0be76 in sub_select (join=0x7ff9980130f8, join_tab=0x7ff998015978, end_of_records=false) at /10.2/src/sql/sql_select.cc:18892
#17 0x0000556b90e0b43a in do_select (join=0x7ff9980130f8, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
#18 0x0000556b90de4f4b in JOIN::exec_inner (this=0x7ff9980130f8) at /10.2/src/sql/sql_select.cc:3651
#19 0x0000556b90de43f2 in JOIN::exec (this=0x7ff9980130f8) at /10.2/src/sql/sql_select.cc:3446
#20 0x0000556b90de55cc in mysql_select (thd=0x7ff998000d90, tables=0x7ff998012948, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff9980130d8, unit=0x7ff998004988, select_lex=0x7ff9980050d8) at /10.2/src/sql/sql_select.cc:3849
#21 0x0000556b90dd9720 in handle_select (thd=0x7ff998000d90, lex=0x7ff9980048c8, result=0x7ff9980130d8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
#22 0x0000556b90da3d86 in execute_sqlcom_select (thd=0x7ff998000d90, all_tables=0x7ff998012948) at /10.2/src/sql/sql_parse.cc:6271
#23 0x0000556b90d9a8fa in mysql_execute_command (thd=0x7ff998000d90) at /10.2/src/sql/sql_parse.cc:3582
#24 0x0000556b90da7b42 in mysql_parse (thd=0x7ff998000d90, rawbuf=0x7ff998012708 "SELECT * FROM `products_gas` LIMIT 0, 25", length=40, parser_state=0x7ff9e301c560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
#25 0x0000556b90d95d9d in dispatch_command (command=COM_QUERY, thd=0x7ff998000d90, packet=0x7ff998008b61 "", packet_length=40, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
#26 0x0000556b90d94898 in do_command (thd=0x7ff998000d90) at /10.2/src/sql/sql_parse.cc:1381
#27 0x0000556b90ef0661 in do_handle_one_connection (connect=0x556b94a37f10) at /10.2/src/sql/sql_connect.cc:1336
#28 0x0000556b90ef03c6 in handle_one_connection (arg=0x556b94a37f10) at /10.2/src/sql/sql_connect.cc:1241
#29 0x0000556b91719ee8 in pfs_spawn_thread (arg=0x556b94a1b1d0) at /10.2/src/storage/perfschema/pfs.cc:1869
#30 0x00007ff9ed1fb609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#31 0x00007ff9ecdd6293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

shorter test:

CREATE TABLE t1 (d1 datetime , v_d1 tinyint(1)  AS (d1 < curdate()));

insert into t1 (d1) values ('2021-09-11 08:38:23'),('2021-09-01 08:38:23');

SELECT * FROM t1 where v_d1=1;

SELECT * FROM t1;

Alice Sherepa added a comment - 2021-09-14 09:07 Thank you! I repeated on 10.2-10.6 This is probably some variation of MDEV-24176 (and MDEV-26437 ) --source include/have_innodb.inc CREATE TABLE `products_gas` ( `product_gas_id` int (11) unsigned NOT NULL AUTO_INCREMENT, `provider_id` int (11) unsigned NOT NULL , `product_name` varchar (255) COLLATE utf8mb4_unicode_ci NOT NULL , `product_code` varchar (255) COLLATE utf8mb4_unicode_ci NOT NULL , `market_type` varchar (32) COLLATE utf8mb4_unicode_ci NOT NULL , `customer_type` varchar (255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`, '$.customer_type' ))) VIRTUAL, `offer_type` varchar (255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`, '$.offer_type' ))) VIRTUAL, `product_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL , `parsed_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL , `is_parsed` tinyint(1) GENERATED ALWAYS AS (`date_parsed` is not null ) VIRTUAL, `is_available` tinyint(1) GENERATED ALWAYS AS (`date_availability_start` < curdate() and `date_availability_end` > curdate()) VIRTUAL, `is_deleted` tinyint(1) GENERATED ALWAYS AS (`date_deleted` is not null ) VIRTUAL, `date_created` datetime NOT NULL , `date_parsed` datetime DEFAULT NULL , `date_availability_start` datetime DEFAULT NULL , `date_availability_end` datetime DEFAULT NULL , `date_deleted` datetime DEFAULT NULL , PRIMARY KEY (`product_gas_id`), KEY `provider_id` (`provider_id`), KEY `product_code` (`product_code`), KEY `market_type` (`market_type`), KEY `customer_type` (`customer_type`), KEY `offer_type` (`offer_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci; insert into products_gas(`product_gas_id`,`provider_id`,`product_name`,`product_code`,`market_type`,`product_raw`,`parsed_raw`,`date_created`,`date_parsed`,`date_availability_start`,`date_availability_end`,`date_deleted`) values (1,1, '1' , '1' , '1' , '1' , '1' , '2021-09-05 08:38:23' , '2021-09-05 08:38:23' , '2021-09-11 08:38:23' , '2021-09-11 08:38:23' , '2021-09-06 08:38:23' ),(2,2, '2' , '2' , '2' , '2' , '2' , '2021-09-13 08:38:23' , '2021-09-06 08:38:23' , '2021-09-01 08:38:23' , '2021-09-04 08:38:23' , '2021-09-14 08:38:23' ); SELECT * FROM `products_gas` where is_available=1; SELECT * FROM `products_gas` LIMIT 0, 25; on 10.3: 10.3 46cb16388a865d9561ba23401 Version: '10.3.32-MariaDB-debug-log' 210914 10:53:06 [ERROR] mysqld got signal 11 ; sql/signal_handler.cc:221(handle_fatal_signal)[0x55e242502d6e] sigaction.c:0(__restore_rt)[0x7f67f9f703c0] sql/item_cmpfunc.cc:751(Arg_comparator::compare_datetime())[0x55e2425c379c] sql/item_cmpfunc.h:102(Arg_comparator::compare())[0x55e24260369e] sql/item_cmpfunc.cc:1803(Item_func_lt::val_int())[0x55e2425d0f81] sql/sql_type.cc:3288(Type_handler_int_result::Item_val_bool(Item*) const)[0x55e24229f01a] sql/item.h:1220(Item::val_bool())[0x55e241affcd4] sql/item_cmpfunc.cc:5077(Item_cond_and::val_int())[0x55e2425efe72] sql/item.cc:6890(Item::save_int_in_field(Field*, bool))[0x55e242583997] sql/sql_type.cc:2593(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x55e24229caa6] sql/item.cc:6900(Item::save_in_field(Field*, bool))[0x55e242583b7b] sql/table.cc:8006(TABLE::update_virtual_fields(handler*, enum_vcol_update_mode))[0x55e2420a5fe8] sql/handler.cc:2866(handler::ha_rnd_next(unsigned char*))[0x55e242518a6c] sql/records.cc:485(rr_sequential(READ_RECORD*))[0x55e242913673] sql/records.h:70(READ_RECORD::read_record())[0x55e241c0059e] sql/sql_select.cc:20781(join_init_read_record(st_join_table*))[0x55e241ecd4bc] sql/sql_select.cc:19842(sub_select(JOIN*, st_join_table*, bool))[0x55e241ec671c] sql/sql_select.cc:19385(do_select(JOIN*, Procedure*))[0x55e241ec4a70] sql/sql_select.cc:4142(JOIN::exec_inner())[0x55e241e57c75] sql/sql_select.cc:3937(JOIN::exec())[0x55e241e555f2] sql/sql_select.cc:4346(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*))[0x55e241e590da] sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e241e2f77b] sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e241da0d9b] sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x55e241d8edd6] sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e241daaaf8] sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e241d819d5] sql/sql_parse.cc:1398(do_command(THD*))[0x55e241d7e518] sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55e24214e18f] sql/sql_connect.cc:1309(handle_one_connection)[0x55e24214da49] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e2437794e9] nptl/pthread_create.c:478(start_thread)[0x7f67f9f64609] x86_64/clone.S:97(__GI___clone)[0x7f67f9e8b293] Query (0x62b000000290): SELECT * FROM `products_gas` LIMIT 0, 25 on 10.2: 10.2 c7184c470eec1777cd6ea #3 <signal handler called> #4 0x0000556b91056393 in Item::val_temporal_packed (this=0x7ff9980150b8, f_type=MYSQL_TYPE_DATETIME) at /10.2/src/sql/item.h:1531 #5 0x0000556b9106074b in Arg_comparator::compare_temporal (this=0x7ff9980a6208, type=MYSQL_TYPE_DATETIME) at /10.2/src/sql/item_cmpfunc.cc:797 #6 0x0000556b90caeaff in Arg_comparator::compare_datetime (this=0x7ff9980a6208) at /10.2/src/sql/item_cmpfunc.h:105 #7 0x0000556b91075556 in Arg_comparator::compare (this=0x7ff9980a6208) at /10.2/src/sql/item_cmpfunc.h:87 #8 0x0000556b91063c63 in Item_func_lt::val_int (this=0x7ff9980a6148) at /10.2/src/sql/item_cmpfunc.cc:1860 #9 0x0000556b91034e53 in Item::val_bool (this=0x7ff9980a6148) at /10.2/src/sql/item.cc:112 #10 0x0000556b9106e449 in Item_cond_and::val_int (this=0x7ff9980a6d58) at /10.2/src/sql/item_cmpfunc.cc:5092 #11 0x0000556b91046aaa in Item::save_in_field (this=0x7ff9980a6d58, field=0x7ff9980393c0, no_conversions=false) at /10.2/src/sql/item.cc:6429 #12 0x0000556b90eb36b9 in TABLE::update_virtual_fields (this=0x7ff998035300, h=0x7ff998035f08, update_mode=VCOL_UPDATE_FOR_READ) at /10.2/src/sql/table.cc:7793 #13 0x0000556b910265f8 in handler::ha_rnd_next (this=0x7ff998035f08, buf=0x7ff998036eb8 "3\374\001") at /10.2/src/sql/handler.cc:2675 #14 0x0000556b91199dd7 in rr_sequential (info=0x7ff998015a40) at /10.2/src/sql/records.cc:492 #15 0x0000556b90e0e137 in join_init_read_record (tab=0x7ff998015978) at /10.2/src/sql/sql_select.cc:19821 #16 0x0000556b90e0be76 in sub_select (join=0x7ff9980130f8, join_tab=0x7ff998015978, end_of_records=false) at /10.2/src/sql/sql_select.cc:18892 #17 0x0000556b90e0b43a in do_select (join=0x7ff9980130f8, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439 #18 0x0000556b90de4f4b in JOIN::exec_inner (this=0x7ff9980130f8) at /10.2/src/sql/sql_select.cc:3651 #19 0x0000556b90de43f2 in JOIN::exec (this=0x7ff9980130f8) at /10.2/src/sql/sql_select.cc:3446 #20 0x0000556b90de55cc in mysql_select (thd=0x7ff998000d90, tables=0x7ff998012948, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff9980130d8, unit=0x7ff998004988, select_lex=0x7ff9980050d8) at /10.2/src/sql/sql_select.cc:3849 #21 0x0000556b90dd9720 in handle_select (thd=0x7ff998000d90, lex=0x7ff9980048c8, result=0x7ff9980130d8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361 #22 0x0000556b90da3d86 in execute_sqlcom_select (thd=0x7ff998000d90, all_tables=0x7ff998012948) at /10.2/src/sql/sql_parse.cc:6271 #23 0x0000556b90d9a8fa in mysql_execute_command (thd=0x7ff998000d90) at /10.2/src/sql/sql_parse.cc:3582 #24 0x0000556b90da7b42 in mysql_parse (thd=0x7ff998000d90, rawbuf=0x7ff998012708 "SELECT * FROM `products_gas` LIMIT 0, 25", length=40, parser_state=0x7ff9e301c560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793 #25 0x0000556b90d95d9d in dispatch_command (command=COM_QUERY, thd=0x7ff998000d90, packet=0x7ff998008b61 "", packet_length=40, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827 #26 0x0000556b90d94898 in do_command (thd=0x7ff998000d90) at /10.2/src/sql/sql_parse.cc:1381 #27 0x0000556b90ef0661 in do_handle_one_connection (connect=0x556b94a37f10) at /10.2/src/sql/sql_connect.cc:1336 #28 0x0000556b90ef03c6 in handle_one_connection (arg=0x556b94a37f10) at /10.2/src/sql/sql_connect.cc:1241 #29 0x0000556b91719ee8 in pfs_spawn_thread (arg=0x556b94a1b1d0) at /10.2/src/storage/perfschema/pfs.cc:1869 #30 0x00007ff9ed1fb609 in start_thread (arg=<optimized out>) at pthread_create.c:477 #31 0x00007ff9ecdd6293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 shorter test: CREATE TABLE t1 (d1 datetime , v_d1 tinyint(1) AS (d1 < curdate())); insert into t1 (d1) values ( '2021-09-11 08:38:23' ),( '2021-09-01 08:38:23' ); SELECT * FROM t1 where v_d1=1; SELECT * FROM t1;

Alice Sherepa added a comment - 2021-09-16 14:43

from ~~MDEV-26619~~

CREATE TABLE t1 ( c int , vc tinyint(4) AS (if(curdate() BETWEEN '2020-01-01' AND '2020-01-02',1,0))) ;

SELECT vc FROM t1;

SELECT vc FROM t1;

#3  <signal handler called>

#4  0x000055ea3d5a4e91 in Item_args::walk_args (this=0x7f25bc035320, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4134

#5  0x000055ea3d5a52db in Item_func_or_sum::walk (this=0x7f25bc035298, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4420

#6  0x000055ea3d5a4ead in Item_args::walk_args (this=0x7f25bc0356d0, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4134

#7  0x000055ea3d5a52db in Item_func_or_sum::walk (this=0x7f25bc035648, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4420

#8  0x000055ea3d71f2e3 in TABLE::mark_virtual_col (this=0x7f25bc175dc0, field=0x7f25bc176ac0) at /10.2/src/sql/table.cc:6807

#9  0x000055ea3d59aac4 in update_field_dependencies (thd=0x7f25bc000d90, field=0x7f25bc176ac0, table=0x7f25bc175dc0) at /10.2/src/sql/sql_base.cc:5312

#10 0x000055ea3d59b9f0 in find_field_in_table (thd=0x7f25bc000d90, table=0x7f25bc175dc0, name=0x7f25bc012798 "vc", length=2, allow_rowid=true, cached_field_index_ptr=0x7f25bc012864) at /10.2/src/sql/sql_base.cc:5649

#11 0x000055ea3d59be24 in find_field_in_table_ref (thd=0x7f25bc000d90, table_list=0x7f25bc0128e8, name=0x7f25bc012798 "vc", length=2, item_name=0x7f25bc012798 "vc", db_name=0x0, table_name=0x0, ref=0x7f25bc0128a8, check_privileges=true, allow_rowid=true, cached_field_index_ptr=0x7f25bc012864, register_tree_change=true, actual_table=0x7f2614437dc8) at /10.2/src/sql/sql_base.cc:5764

#12 0x000055ea3d59c978 in find_field_in_tables (thd=0x7f25bc000d90, item=0x7f25bc0127a0, first_table=0x7f25bc0128e8, last_table=0x0, ref=0x7f25bc0128a8, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true) at /10.2/src/sql/sql_base.cc:6056

#13 0x000055ea3d8b2092 in Item_field::fix_fields (this=0x7f25bc0127a0, thd=0x7f25bc000d90, reference=0x7f25bc0128a8) at /10.2/src/sql/item.cc:5463

#14 0x000055ea3d59f84c in setup_fields (thd=0x7f25bc000d90, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f25bc013328, pre_fix=0x7f25bc005218, allow_sum_func=true) at /10.2/src/sql/sql_base.cc:7275

#15 0x000055ea3d648a69 in JOIN::prepare (this=0x7f25bc013008, tables_init=0x7f25bc0128e8, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f25bc0050d8, unit_arg=0x7f25bc004988) at /10.2/src/sql/sql_select.cc:807

#16 0x000055ea3d65351a in mysql_select (thd=0x7f25bc000d90, tables=0x7f25bc0128e8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f25bc012fe8, unit=0x7f25bc004988, select_lex=0x7f25bc0050d8) at /10.2/src/sql/sql_select.cc:3827

#17 0x000055ea3d647720 in handle_select (thd=0x7f25bc000d90, lex=0x7f25bc0048c8, result=0x7f25bc012fe8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361

#18 0x000055ea3d611d86 in execute_sqlcom_select (thd=0x7f25bc000d90, all_tables=0x7f25bc0128e8) at /10.2/src/sql/sql_parse.cc:6271

#19 0x000055ea3d6088fa in mysql_execute_command (thd=0x7f25bc000d90) at /10.2/src/sql/sql_parse.cc:3582

#20 0x000055ea3d615b42 in mysql_parse (thd=0x7f25bc000d90, rawbuf=0x7f25bc012708 "SELECT vc FROM t1", length=17, parser_state=0x7f2614439560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793

#21 0x000055ea3d603d9d in dispatch_command (command=COM_QUERY, thd=0x7f25bc000d90, packet=0x7f25bc008b61 "SELECT vc FROM t1", packet_length=17, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827

#22 0x000055ea3d602898 in do_command (thd=0x7f25bc000d90) at /10.2/src/sql/sql_parse.cc:1381

#23 0x000055ea3d75e661 in do_handle_one_connection (connect=0x55ea40fb2b80) at /10.2/src/sql/sql_connect.cc:1336

#24 0x000055ea3d75e3c6 in handle_one_connection (arg=0x55ea40fb2b80) at /10.2/src/sql/sql_connect.cc:1241

#25 0x000055ea3df87f18 in pfs_spawn_thread (arg=0x55ea40f95e40) at /10.2/src/storage/perfschema/pfs.cc:1869

#26 0x00007f2619db4609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#27 0x00007f261998f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Alice Sherepa added a comment - 2021-09-16 14:43 from MDEV-26619 CREATE TABLE t1 ( c int , vc tinyint(4) AS ( if (curdate() BETWEEN '2020-01-01' AND '2020-01-02' ,1,0))) ; SELECT vc FROM t1; SELECT vc FROM t1; #3 <signal handler called> #4 0x000055ea3d5a4e91 in Item_args::walk_args (this=0x7f25bc035320, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4134 #5 0x000055ea3d5a52db in Item_func_or_sum::walk (this=0x7f25bc035298, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4420 #6 0x000055ea3d5a4ead in Item_args::walk_args (this=0x7f25bc0356d0, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4134 #7 0x000055ea3d5a52db in Item_func_or_sum::walk (this=0x7f25bc035648, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4420 #8 0x000055ea3d71f2e3 in TABLE::mark_virtual_col (this=0x7f25bc175dc0, field=0x7f25bc176ac0) at /10.2/src/sql/table.cc:6807 #9 0x000055ea3d59aac4 in update_field_dependencies (thd=0x7f25bc000d90, field=0x7f25bc176ac0, table=0x7f25bc175dc0) at /10.2/src/sql/sql_base.cc:5312 #10 0x000055ea3d59b9f0 in find_field_in_table (thd=0x7f25bc000d90, table=0x7f25bc175dc0, name=0x7f25bc012798 "vc", length=2, allow_rowid=true, cached_field_index_ptr=0x7f25bc012864) at /10.2/src/sql/sql_base.cc:5649 #11 0x000055ea3d59be24 in find_field_in_table_ref (thd=0x7f25bc000d90, table_list=0x7f25bc0128e8, name=0x7f25bc012798 "vc", length=2, item_name=0x7f25bc012798 "vc", db_name=0x0, table_name=0x0, ref=0x7f25bc0128a8, check_privileges=true, allow_rowid=true, cached_field_index_ptr=0x7f25bc012864, register_tree_change=true, actual_table=0x7f2614437dc8) at /10.2/src/sql/sql_base.cc:5764 #12 0x000055ea3d59c978 in find_field_in_tables (thd=0x7f25bc000d90, item=0x7f25bc0127a0, first_table=0x7f25bc0128e8, last_table=0x0, ref=0x7f25bc0128a8, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true) at /10.2/src/sql/sql_base.cc:6056 #13 0x000055ea3d8b2092 in Item_field::fix_fields (this=0x7f25bc0127a0, thd=0x7f25bc000d90, reference=0x7f25bc0128a8) at /10.2/src/sql/item.cc:5463 #14 0x000055ea3d59f84c in setup_fields (thd=0x7f25bc000d90, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f25bc013328, pre_fix=0x7f25bc005218, allow_sum_func=true) at /10.2/src/sql/sql_base.cc:7275 #15 0x000055ea3d648a69 in JOIN::prepare (this=0x7f25bc013008, tables_init=0x7f25bc0128e8, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f25bc0050d8, unit_arg=0x7f25bc004988) at /10.2/src/sql/sql_select.cc:807 #16 0x000055ea3d65351a in mysql_select (thd=0x7f25bc000d90, tables=0x7f25bc0128e8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f25bc012fe8, unit=0x7f25bc004988, select_lex=0x7f25bc0050d8) at /10.2/src/sql/sql_select.cc:3827 #17 0x000055ea3d647720 in handle_select (thd=0x7f25bc000d90, lex=0x7f25bc0048c8, result=0x7f25bc012fe8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361 #18 0x000055ea3d611d86 in execute_sqlcom_select (thd=0x7f25bc000d90, all_tables=0x7f25bc0128e8) at /10.2/src/sql/sql_parse.cc:6271 #19 0x000055ea3d6088fa in mysql_execute_command (thd=0x7f25bc000d90) at /10.2/src/sql/sql_parse.cc:3582 #20 0x000055ea3d615b42 in mysql_parse (thd=0x7f25bc000d90, rawbuf=0x7f25bc012708 "SELECT vc FROM t1", length=17, parser_state=0x7f2614439560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793 #21 0x000055ea3d603d9d in dispatch_command (command=COM_QUERY, thd=0x7f25bc000d90, packet=0x7f25bc008b61 "SELECT vc FROM t1", packet_length=17, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827 #22 0x000055ea3d602898 in do_command (thd=0x7f25bc000d90) at /10.2/src/sql/sql_parse.cc:1381 #23 0x000055ea3d75e661 in do_handle_one_connection (connect=0x55ea40fb2b80) at /10.2/src/sql/sql_connect.cc:1336 #24 0x000055ea3d75e3c6 in handle_one_connection (arg=0x55ea40fb2b80) at /10.2/src/sql/sql_connect.cc:1241 #25 0x000055ea3df87f18 in pfs_spawn_thread (arg=0x55ea40f95e40) at /10.2/src/storage/perfschema/pfs.cc:1869 #26 0x00007f2619db4609 in start_thread (arg=<optimized out>) at pthread_create.c:477 #27 0x00007f261998f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Nikita Malyavin added a comment - 2021-10-01 12:52

alice Thanks for the tests!

Nikita Malyavin added a comment - 2021-10-01 12:52 alice Thanks for the tests!

Nikita Malyavin added a comment - 2021-10-01 18:44

I have checked that the issue is not reproduced with midenok's patch https://github.com/MariaDB/server/commit/c91cc1697ea5325880987750f605caf0051690b7

Nikita Malyavin added a comment - 2021-10-01 18:44 I have checked that the issue is not reproduced with midenok 's patch https://github.com/MariaDB/server/commit/c91cc1697ea5325880987750f605caf0051690b7

Aleksey Midenkov added a comment - 2021-10-01 19:52

nikitamalyavin I guess you have to reassign such duplicates to the current assignee of the original fix.

Aleksey Midenkov added a comment - 2021-10-01 19:52 nikitamalyavin I guess you have to reassign such duplicates to the current assignee of the original fix.

MariaDB Server

Virtual Column with CURDATE function cause suddenly entire MariaDB crash

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration