[MDEV-15881] Assertion `is_valid_value_slow()' failed in Datetime::Datetime or corrupt data after ALTER with indexed persistent column Created: 2018-04-16  Updated: 2019-04-26  Resolved: 2019-04-26

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types, Virtual Columns
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Duplicate Votes: 0
Labels: None

Attachments: HTML File threads    
Issue Links:
Duplicate
duplicates MDEV-18486 Database crash on a table with indexe... Closed
Relates
relates to MDEV-18486 Database crash on a table with indexe... Closed
relates to MDEV-16971 Assertion `is_valid_value_slow()' fa... Closed

 Description    

CREATE TABLE t1 (i INT, d1 DATE, d2 DATE NOT NULL, t TIMESTAMP, KEY(t)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES  (1,'2023-03-16','2023-03-15','2012-12-12 12:12:12');
 
ALTER TABLE t1 MODIFY t FLOAT AS (i) PERSISTENT;
 
SELECT i, d1, d2 FROM t1 INTO OUTFILE 'load_t1';
 
DELETE FROM t1;
 
LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2);
 
SELECT * FROM t1 WHERE d2 < d1;
 
 
 
# Cleanup
 
--let $datadir= `SELECT @@datadir`
 
--remove_file $datadir/test/load_t1
 
DROP TABLE t1;

10.3 df51dc28

 
mysqld: /data/src/10.3/sql/sql_type.h:374: Datetime::Datetime(THD*, Item*, sql_mode_t): Assertion `is_valid_value_slow()' failed.
 
190220  1:24:01 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f93b73d0ee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#8  0x00005621f32aeca2 in Datetime::Datetime (this=0x7f93b1575a40, thd=0x7f93a0000b00, item=0x7f93a0015558, flags=33554433) at /data/src/10.3/sql/sql_type.h:374
 
#9  0x00005621f32af89e in Item::val_datetime_packed (this=0x7f93a0015558) at /data/src/10.3/sql/item.h:1504
 
#10 0x00005621f36efaf1 in Arg_comparator::compare_datetime (this=0x7f93a0015840) at /data/src/10.3/sql/item_cmpfunc.cc:736
 
#11 0x00005621f3704094 in Arg_comparator::compare (this=0x7f93a0015840) at /data/src/10.3/sql/item_cmpfunc.h:102
 
#12 0x00005621f36f3033 in Item_func_lt::val_int (this=0x7f93a0015780) at /data/src/10.3/sql/item_cmpfunc.cc:1782
 
#13 0x00005621f372c0e1 in eval_const_cond (cond=0x7f93a0015780) at /data/src/10.3/sql/item_func.cc:81
 
#14 0x00005621f34212ca in Item_bool_func2::remove_eq_conds (this=0x7f93a0015780, thd=0x7f93a0000b00, cond_value=0x7f93a0015ce8, top_level_arg=true) at /data/src/10.3/sql/sql_select.cc:16387
 
#15 0x00005621f3404076 in make_join_statistics (join=0x7f93a00159d8, tables_list=..., keyuse_array=0x7f93a0015cc8) at /data/src/10.3/sql/sql_select.cc:4865
 
#16 0x00005621f33f9c73 in JOIN::optimize_inner (this=0x7f93a00159d8) at /data/src/10.3/sql/sql_select.cc:1888
 
#17 0x00005621f33f82cb in JOIN::optimize (this=0x7f93a00159d8) at /data/src/10.3/sql/sql_select.cc:1451
 
#18 0x00005621f3401ef2 in mysql_select (thd=0x7f93a0000b00, tables=0x7f93a0014ef0, wild_num=1, fields=..., conds=0x7f93a0015780, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f93a00159b0, unit=0x7f93a00049b8, select_lex=0x7f93a0005128) at /data/src/10.3/sql/sql_select.cc:4225
 
#19 0x00005621f33f3eb4 in handle_select (thd=0x7f93a0000b00, lex=0x7f93a00048f0, result=0x7f93a00159b0, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:385
 
#20 0x00005621f33be8ec in execute_sqlcom_select (thd=0x7f93a0000b00, all_tables=0x7f93a0014ef0) at /data/src/10.3/sql/sql_parse.cc:6551
 
#21 0x00005621f33b4f5d in mysql_execute_command (thd=0x7f93a0000b00) at /data/src/10.3/sql/sql_parse.cc:3772
 
#22 0x00005621f33c28a5 in mysql_parse (thd=0x7f93a0000b00, rawbuf=0x7f93a0014ce8 "SELECT * FROM t1 WHERE d2 < d1", length=30, parser_state=0x7f93b15775f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8095
 
#23 0x00005621f33afa59 in dispatch_command (command=COM_QUERY, thd=0x7f93a0000b00, packet=0x7f93a0126141 "SELECT * FROM t1 WHERE d2 < d1", packet_length=30, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1854
 
#24 0x00005621f33ae431 in do_command (thd=0x7f93a0000b00) at /data/src/10.3/sql/sql_parse.cc:1396
 
#25 0x00005621f351674f in do_handle_one_connection (connect=0x5621f5aef8a0) at /data/src/10.3/sql/sql_connect.cc:1403
 
#26 0x00005621f35164d3 in handle_one_connection (arg=0x5621f5aef8a0) at /data/src/10.3/sql/sql_connect.cc:1309
 
#27 0x00005621f39b22e1 in pfs_spawn_thread (arg=0x5621f5b3c4c0) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#28 0x00007f93b90a7494 in start_thread (arg=0x7f93b1578700) at pthread_create.c:333
 
#29 0x00007f93b748d93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Same on 10.4 debug.

10.2 and non-debug builds of 10.3/10.4 don't crash, but produce different kinds of meaningless output:

SELECT * FROM t1 WHERE d2 < d1;
 
i	d1	d2	t


i	d1	d2	t
 
1118830592	0000-03-31	0000-00-00	NULL

etc.

10.1 works okay:

SELECT * FROM t1 WHERE d2 < d1;
 
i	d1	d2	t
 
1	2023-03-16	2023-03-15	1

Note: i field is not important for the scenario, it's there only to make the test case applicable to 10.1 which doesn't allow a constant in the virtual column expression.

 Comments   
Comment by Alexander Barkov [ 2019-04-26 ]

Also repeatable with this script (10.3 df51dc28f599112356461533a9ce40d6f6c8baa8):

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (
 
  i INT DEFAULT NULL,
 
  d1 DATE DEFAULT NULL,
 
  d2 DATE NOT NULL,
 
  t FLOAT GENERATED ALWAYS AS (i) STORED,
 
  KEY (t)
 
) ENGINE=MyISAM;
 
 
 
SELECT 1, '2023-03-16', '2023-03-16' INTO OUTFILE 'load_t1';
 
 
 
LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2);
 
 
 
SELECT * FROM t1 WHERE d2 < d1;

Note, ALTER is not needed.

Additionally, this query returns a strange result:

SELECT * FROM t1;


+------+------+------------+------+
 
| i    | d1   | d2         | t    |
 
+------+------+------------+------+
 
| NULL | NULL | B375-12-15 | NULL |
 
+------+------+------------+------+

Comment by Alexander Barkov [ 2019-04-26 ]

This problem was fixed by the patch for MDEV-18486

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