path: root/mysql-test/suite/vcol/t/vcol_keys_myisam.test

--source include/have_sequence.inc
--let $datadir= `select @@datadir`
###############################################################################
# t/vcol_keys_myisam.test                                                     #
#                                                                             #
# Purpose:                                                                    #
#  Testing keys, indexes defined upon virtual columns.                        #
#                                                                             #
#        MyISAM branch                                                        #
#                                                                             #
#-----------------------------------------------------------------------------#
# Original Author: Andrey Zhakov                                              #
# Original Date: 2008-09-04                                                   #
# Change Author:                                                              #
# Change Date:                                                                #
# Change:                                                                     #
###############################################################################

#
# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
#       THE SOURCED FILES ONLY.
#

#------------------------------------------------------------------------------
# General not engine specific settings and requirements
--source suite/vcol/inc/vcol_init_vars.pre

#------------------------------------------------------------------------------
# Cleanup
--source suite/vcol/inc/vcol_cleanup.inc

#------------------------------------------------------------------------------
# Engine specific settings and requirements

##### Storage engine to be tested
# Set the session storage engine
SET @@session.storage_engine = 'MyISAM';

##### Workarounds for known open engine specific bugs
#     none

#------------------------------------------------------------------------------
# Execute the tests to be applied to all storage engines
--source suite/vcol/inc/vcol_keys.inc

# Cleanup
--source suite/vcol/inc/vcol_cleanup.inc

#-----------------------------------------------------------------------------#
# Execute storage engine specific test
#-----------------------------------------------------------------------------#

--echo #
--echo # Original test
--echo #

create table t1 (a int, b int as (a+1), c int, index(b));
insert t1 (a,c) values (0x7890abcd, 0x76543210);
insert t1 (a,c) select seq, sin(seq)*10000 from seq_1_to_1000;
explain select * from t1 where b=10;
select * from t1 where b=10;
--replace_result $datadir datadir
--exec $MYISAMCHK -d $datadir/test/t1
update t1 set a=20 where b=10;
select * from t1 where b=10;
select * from t1 where b=21;
delete from t1 where b=21;
select * from t1 where b=21;
alter table t1 add column d char(20) as (concat(a,c));
select * from t1 where b=11;
create index i on t1 (d);
check table t1;
select * from t1 where b=11;
check table t1 quick;
select * from t1 where b=11;
check table t1 medium;
select * from t1 where b=11;
check table t1 extended;
show keys from t1;
select * from t1 where b=11;
delete from t1 where b=12;
analyze table t1;
show keys from t1;
select * from t1 where b=11;
optimize table t1;
show keys from t1;
select * from t1 where b=11;
repair table t1;
select * from t1 where b=11;
repair table t1 quick;
select * from t1 where b=11;
repair table t1 extended;
select * from t1 where b=11;
repair table t1 use_frm;
select * from t1 where b=11;
update t1 set a=30 where b=11;
select * from t1 where b=11;
select * from t1 where b=31;

--error 1
--exec $MYISAMCHK $datadir/test/t1
--error 1
--exec $MYISAMCHK -r $datadir/test/t1
drop table t1;

--echo #
--echo # MDEV-11606 Server crashes in mi_make_key / sort_key_read
--echo #

CREATE TABLE t1 ( 
 pk BIGINT AUTO_INCREMENT,
 col_date DATE NULL,
 col_datetime DATETIME(1) NULL,
 col_int TINYINT(13) UNSIGNED ZEROFILL NULL,
 col_varchar VARBINARY(2222) NULL,
 col_timestamp TIMESTAMP(2) NULL,
 col_bit BIT(64) NOT NULL DEFAULT 0,
 col_blob MEDIUMBLOB NULL,
 col_dec DECIMAL(10,9) ZEROFILL NOT NULL DEFAULT 0,
 col_time TIME(4) NULL,
 col_year YEAR NOT NULL DEFAULT '1970',
 col_char CHAR(129) NULL,
 col_enum SET('','a','b','c','d','e','f','foo','bar') NULL,
 vcol_dec DECIMAL(50,18) ZEROFILL AS (col_dec) VIRTUAL,
 vcol_bit BIT(48) AS (col_bit) VIRTUAL,
 vcol_char CHAR(224) AS (col_char) VIRTUAL,
 vcol_datetime DATETIME(4) AS (col_datetime) VIRTUAL,
 vcol_year YEAR AS (col_year) VIRTUAL,
 vcol_varchar VARBINARY(356) AS (col_varchar) VIRTUAL,
 vcol_blob MEDIUMBLOB AS (col_blob) VIRTUAL,
 vcol_timestamp TIMESTAMP(5) AS (col_timestamp) VIRTUAL,
 vcol_int BIGINT(46) AS (col_int) VIRTUAL,
 vcol_time TIME(1) AS (col_time) VIRTUAL,
 vcol_date DATE AS (col_date) VIRTUAL,
 vcol_enum SET('','a','b','c','d','e','f','foo','bar') AS (col_enum) VIRTUAL,
 UNIQUE(pk),
 PRIMARY KEY(pk)
 ) ENGINE=MyISAM;

--disable_query_log
INSERT INTO t1 (col_date,col_datetime,col_int,col_varchar,col_timestamp,col_bit,col_blob,col_dec,col_time,col_year,col_char,col_enum) VALUES
 (NULL,'2011-04-17 15:46:11.056462',6,'rsprn','1980-01-01 00:00:00',b'0001011111000111001110000110100110010101101','spr',0.0,'00:00:00',1988,'p',''),
 ('2007-05-18',NULL,5,'rnwg','2009-07-07 23:46:32.052699',b'01010','n',0.6,'04:35:56.018027',1995,'wgrpq',''),
 ('1994-03-16','2006-03-15 22:48:25.013225',7,'grpquarw','2034-05-17 10:51:23.048265',b'0001011101001011000111101','rp',0.3,'15:13:22.043368',2004,'pq',''),
 ('1980-01-01','1987-06-01 04:14:04.027480',1,'qu','1989-07-05 09:46:16.038513',b'001111011000100011100111010100101010000100010100101','uar',0.6,'11:56:35.031314',1986,'',''),
 ('2014-08-16','2021-01-08 20:59:16.041252',0,'arw','2022-12-20 22:48:53.014627',b'110110101100001011001110110100','m',0.6,'17:26:26.039855',2008,'rw',''),
 ('1981-12-02','1992-02-10 09:29:41.028674',6,'wk','2017-09-25 10:37:25.043516',b'010101001110111010101001101000101010',NULL,0.7,'00:00:00',2035,'kaz',''),
 ('2022-11-11','2029-03-07 17:24:19.043489',122,'a','1980-01-01 00:00:00',b'0001100111011','z',0.0,'10:23:45.050733',2018,'a',''),
 ('2004-03-12','1979-02-18 00:00:00',0,'zjeiwvd','1998-07-03 16:09:05.053954',b'0000110101111001001110100100111001111111100001110','',0.4,'07:43:46.015324',2028,'je',''),
 ('2016-08-08','1986-03-10 00:00:00',3,'eiwv','2025-08-07 12:24:53.040869',b'010001101110100111111','iw',0.3,'02:48:45.058781',2020,'',''),
 ('1979-02-25',NULL,2,'wvd','1980-01-01 00:00:00',b'010','vdm',0.1,'13:35:24.021296',1995,'dmd',''),
 ('2012-04-19','2034-10-06 23:29:21.057367',9,'mdiv','2022-05-16 05:28:40.005808',b'110111111101000010011011001','divj',0.3,'11:24:50.017885',1997,'iv',''),
 ('2009-10-13','1997-01-21 13:04:34.011524',1,'vjqs','1980-01-01 00:00:00',b'11110011100101011100001110110000101001100010000011110110011','j',0.7,'00:00:00',2008,'qsxmh',''),
 ('2012-01-01','2011-06-23 06:11:33.014723',9,'r','2022-04-27 05:29:32.023395',b'1001011010101100100111','',0.5,'03:34:01.048002',1976,'sx',''),
 ('2007-08-24','2012-01-24 22:16:53.014811',2,'fpp','2005-10-11 08:58:32.021273',b'011011111011',NULL,0.4,'18:11:17.036115',2026,'m',''),
 (NULL,'1985-05-28 00:00:00',7,'hjw','2030-06-10 10:15:54.061818',b'0011110101001100011011000101010','',0.5,'02:03:21.020237',1994,'z',''),
 ('2006-09-09','2002-11-10 06:16:27.008631',0,'jw','2030-07-16 00:00:00',b'11101110111101000010101110000010001110110001001101110100','wa',0.8,'00:00:00',1974,'',''),
 ('1993-04-22','1980-01-01 00:00:00',3,'a','2020-02-06 08:11:00.051515',b'001110110010','gew',0.4,'11:59:05.013933',1998,NULL,''),
 ('1985-11-05','2019-12-24 04:13:43.062741',7,'foo','1986-07-02 00:00:00',b'10100110001010100011111111101011011101001110010110101100110000','',0.5,'17:32:55.060182',1978,'w',''),
 ('2007-02-20','2033-10-16 18:47:42.006517',1,'clc','1971-10-14 00:00:00',b'100001010','',0.1,'20:38:42.062598',2004,'lcfy',''),
 ('1979-12-10','1972-08-04 00:00:00',8,'','2021-12-24 04:51:09.011443',b'011010010010010010011101000010110011110110110010100010','e',0.1,'23:08:10.014396',2026,'cfykywl',''),
 ('2002-07-04',NULL,NULL,'fyky','1980-11-02 00:00:00',b'10000010111010000110','f',0.6,'13:07:14.014203',1979,'ykywl',''),
 ('2019-02-15','2004-08-28 10:16:46.053753',8,'kywl','2004-12-01 00:00:00',b'010111010110001110110011000010110111011','ywlcne',0.2,'09:10:39.028897',2031,'wlcnemiuaab',''),
 ('1989-05-15','1973-09-04 00:00:00',2,'lcne',NULL,b'100001100101110110000011110','',0.9,'19:22:16.015548',2013,'cnemiuaa',''),
 ('2023-04-20','2018-12-04 04:19:46.040664',8,NULL,'1985-04-18 05:32:12.013509',b'111011011111100100000001','nem',0.1,'23:18:05.007794',2026,'emiua',''),
 (NULL,'2024-08-10 03:52:31.047182',2,'miua','2035-09-03 06:07:22.008308',b'000111','',0.1,'03:58:02.003946',1988,'iua',''),
 ('1977-09-01','1987-02-01 03:44:00.061840',5,'u','1997-11-27 04:02:13.014551',b'111110001001100101101000100010001011101000011',NULL,0.3,'00:00:00',1985,'aab',''),
 (NULL,'1980-01-01 00:00:00',6,'a','1989-03-02 09:07:11.058643',b'10101001011110110010111010010100001010000110000110010',NULL,0.8,'17:41:15.057101',1975,'br',''),
 ('1980-01-01','2019-02-10 20:56:51.063985',NULL,'rr','2000-02-28 01:38:27.004468',b'0101001011110001010001','rifnhu',0.5,'20:55:57.029281',1973,'if',''),
 (NULL,'2019-04-15 02:13:03.019581',7,'fnhu','2000-03-25 18:48:46.063113',b'011000110','nhuu',0.0,'00:00:00',2030,'h',''),
 ('1997-04-01',NULL,3,NULL,'1976-05-22 04:48:42.013754',b'101110101010000111101',NULL,0.1,'12:09:48.030076',2031,'u',''),
 (NULL,'1993-10-10 15:11:03.021823',NULL,'ufzasunkrcpvasdqkxbwptigbpnesqigwegcnfeuvrgnecpthm','1986-06-12 04:29:01.017855',b'11110','fza',0.9,NULL,1994,'zasun',''),
 ('1997-02-13','2001-04-08 02:01:53.018388',0,'as','1975-09-18 00:00:00',b'00111000101001001101001000100100010101110011010111000001011011','su',0.3,'15:15:31.011102',1972,'unk',''),
 ('1975-07-26','2022-12-24 00:00:00',0,'foo','1986-01-22 21:27:38.024505',b'1111110000000001000010111','krc',0.9,'05:46:08.055789',1996,'rcp',''),
 ('2000-08-04','1980-01-01 00:00:00',48,'foo','2015-12-21 17:04:06.008790',b'0100001110011001011101011101110110100010000101101100011010','g',0.7,'18:31:50.046211',2025,'pvasdq',''),
 ('2001-03-26','2032-03-19 13:15:13.063368',NULL,'foo','2026-09-05 02:46:10.026919',b'11','a',0.3,'03:12:20.039599',2019,'sdqkx',''),
 ('2035-01-14','1987-09-15 05:46:00.041527',4,'p','1980-01-01 00:00:00',b'1111100111','dqkxbwpt',0.0,'09:57:41.059145',1995,'q',''),
 ('2035-07-13','2011-04-13 04:05:14.025091',212,'kxbw','1985-02-14 11:58:32.002055',b'1010100011110101011111111011010','x',0.2,'19:35:02.024655',1975,'bwptig',''),
 ('2026-02-08','2015-05-04 09:31:22.017074',4,'wpt','2024-01-26 11:06:03.057899',b'011000010000100000011000011011000100101111001100000111011010','ptig',0.0,'00:00:00',1977,'tig',''),
 ('1981-05-11',NULL,NULL,'foo','1981-12-09 10:10:34.008107',b'01000100100100110011111','gbp',0.0,'13:05:42.035253',2019,'bpn',''),
 ('1977-05-16',NULL,9,'pne',NULL,b'001101100111001110110010111001110100','s',0.0,'15:09:37.063819',1998,'ne',''),
 ('1980-01-01','2018-12-02 00:27:35.056455',2,'','1981-07-07 23:39:32.028644',b'0000101001010111010001101000','es',0.3,'15:43:30.016638',2013,NULL,''),
 ('2027-09-05','1998-05-14 04:15:42.009728',1,'s','2015-07-16 00:00:00',b'01011101101010000110011010000111001000001000011','',0.6,'08:39:24.041879',2035,'qigweg',''),
 ('2005-02-04',NULL,2,'i','1974-01-11 11:02:16.024653',b'01001101110001001101101010011001001101010010000','gw',0.6,'03:28:30.012172',1978,'weg',''),
 (NULL,'1976-06-21 00:00:00',5,NULL,'2023-11-25 15:49:52.021725',b'101011010001100','e',0.1,'00:00:00',1977,'gcn',''),
 ('1989-07-13',NULL,1,'c','1978-02-22 02:55:14.047104',b'01101010100001100110111011101000111011101101110011','f',0.8,NULL,1987,'nfeu',''),
 ('2004-04-27','2019-06-28 08:04:35.039213',0,'f','1990-01-09 14:22:27.065127',b'00101001011','eu',0.0,'13:33:09.039791',2007,'uvrgne',''),
 ('2008-09-08','1990-11-05 00:00:00',1,'w','2026-12-23 00:00:00',b'0001101','vrgnec',0.3,'19:13:14.037732',1983,'d',''),
 ('2026-08-12','2026-11-23 11:18:35.012315',4,'rgnec','1988-09-06 07:11:55.057710',b'11010111001001101100100010110011100001000100001011000000000010','',0.9,NULL,1982,'gnecpth',''),
 ('1992-12-03','2033-08-18 04:47:11.033829',65,'n','1989-11-21 17:42:13.012747',b'11011011110000000',NULL,0.9,'10:08:34.006377',1971,'ecpth',''),
 ('1976-10-11','1975-05-18 00:00:00',3,'c','2017-11-06 03:33:38.002741',b'0111100010000111000111111100111100111000101100111111100','p',0.7,'03:28:07.039921',2014,'thmhf',''),
 ('2014-04-19','2023-08-07 16:18:59.024013',0,'','2006-05-04 23:01:46.019351',b'0101101011011101101011101110000001001000110100101000011001110','h',0.5,'00:00:00',1995,'m',''),
 ('1990-07-16','1980-01-01 00:00:00',8,'hffqbythjwpukqubzpomntrddrwhzjtqvb','1985-08-04 05:33:20.030471',b'001101111111100110101111000011100','ff',0.0,'00:00:00',2002,'fqbythj',''),
 (NULL,'2019-01-12 00:00:00',0,NULL,'2009-01-25 00:00:00',b'100111111010000110010011100100000011101001010101111','qb',0.4,'20:35:33.059895',1981,'byt',''),
 ('1991-10-07',NULL,2,'yt','2027-04-19 06:38:46.020191',b'001','t',0.4,'10:02:06.014126',2004,'h',''),
 (NULL,'2009-07-05 00:00:00',241,'j','1981-06-26 12:35:20.061910',b'10101110001101001000011010010111000','wpukqu',0.5,'00:00:00',1973,'pukqu',''),
 ('2001-05-26','2007-01-06 00:57:02.048605',0,'u',NULL,b'111100','k',0.7,'03:19:10.052988',2026,'q',NULL),
 ('2008-03-15','1990-09-11 00:00:00',5,'ubz','1980-01-01 00:00:00',b'11010111011110001101111000000011000111101100111','b',0.5,'00:34:27.006616',2013,NULL,''),
 ('1984-08-01','2000-09-20 09:35:47.025609',3,'zp','2016-11-22 19:38:52.053299',b'00000010','po',0.7,'19:47:19.014687',1996,'o',''),
 ('1978-02-05','1978-05-08 04:30:57.023271',7,'foo','2000-04-06 08:42:13.019650',b'11000110111100101010001110111101111000001101','n',0.8,NULL,1980,'trdd',''),
 ('2017-04-11','2002-09-26 12:59:43.051659',8,'rd','1972-03-27 13:09:07.017459',b'00011110001001001000000100110100101010','ddrwh',0.7,'00:00:00',2021,'drwhzj',''),
 ('1980-01-01','1986-05-04 05:15:19.008418',0,'r','2005-10-04 09:21:09.020131',b'1101100010101001010011010001011101001111110010101111011','wh',0.0,'00:00:00',1975,'hzjtqv',''),
 ('2035-12-12','1980-01-01 00:00:00',0,'x',NULL,b'0010000101010110111100000110000010001000100001000110111000010110','zjt',0.4,'15:51:12.040679',1984,'jtqvbji',''),
 ('1993-05-12','2000-11-11 20:54:49.053753',0,'tqvb','2022-02-26 14:26:36.004981',b'110000101110000111011','qv',0.7,'00:00:00',1972,'v',''),
 ('1971-08-22','2029-02-15 16:39:35.007278',2,NULL,'2033-09-22 08:28:19.057517',b'11111101011101110111100011011111001','bji',0.8,'08:34:37.000701',2000,'o',''),
 (NULL,NULL,5,'foo','1982-02-24 00:00:00',b'00111111000111111111010111010111011101','iklce',0.8,'01:23:11.014485',2021,'klcek',''),
 (NULL,NULL,8,'lce','1988-07-28 11:48:23.011427',b'101101000101010000000100000001011','',0.3,'17:15:34.034697',1991,'cekxqy',''),
 ('2029-12-07','1993-12-24 00:45:29.060155',3,'ekx','1980-01-01 00:00:00',b'01001010110110000100100100111010110000000101001011111110001100','q',0.7,'10:39:47.004022',2006,'foo',''),
 ('2015-10-20','1980-01-01 00:00:00',189,'xqy','2028-12-19 00:00:00',b'101001011011100101110010101000101110100110','qy',0.5,'15:16:59.059052',1993,'foo',''),
 ('1998-08-07','2017-08-07 01:53:34.056737',5,'oxsolbx',NULL,b'1000111010110010110','xsolbxth',0.3,'22:56:09.003450',2014,'s',''),
 ('2016-01-25','2000-09-14 22:35:41.048328',6,'foo','2004-10-11 00:00:00',b'001','olbxt',0.0,'14:15:54.033066',1983,'lbxt',''),
 ('1979-09-02','2027-01-19 09:34:15.034597',4,'bxth','1989-10-23 09:11:09.055445',b'011011001110000011011011',NULL,0.8,'05:31:31.006489',1978,'xthdc',''),
 ('1980-01-01',NULL,8,'th','2012-02-07 00:00:00',b'00101011001100111001101011010110','hdc',0.0,'22:09:17.054381',2013,'dcprs',''),
 (NULL,'2018-08-03 17:37:14.049040',2,'cprswpj','1990-07-28 07:56:50.026324',b'0000010111011110100100010010011011010010001111011010000010011101','',0.1,NULL,1971,'prswpjx',''),
 ('1984-05-07','2012-05-07 00:00:00',1,'rswp','2030-05-09 07:42:25.003848',b'1001','swp',0.4,'13:27:32.040813',1997,'wpj',''),
 (NULL,'2030-03-22 14:03:46.000742',7,'pjxixm','2022-05-11 00:00:00',b'00111110011001010010001111010001111110010010000111','j',0.8,'00:00:00',1996,'x',NULL),
 ('2000-12-03','2020-08-13 16:03:09.041436',8,'ix','1985-05-19 11:28:09.002728',b'11011010000101000110111111010111','xmvfwm',0.9,'19:06:00.002417',1976,'m',''),
 ('1990-03-13','2035-09-08 21:29:04.011731',5,'vfwmsys','2029-11-03 04:28:54.058532',b'0110001011001010100','fw',0.6,'20:30:32.032224',1994,'wmsys',''),
 ('2035-06-04','2027-06-07 11:27:21.038934',8,'ms','1987-09-02 00:00:00',b'001101101101111110010110110011','syse',0.1,'01:10:53.060943',2027,'yse',''),
 (NULL,'1993-06-06 07:29:56.029103',NULL,'','1971-06-08 23:51:55.054403',b'11001110001111111001001010101110111011000100111010','se',0.2,'10:24:53.013058',1995,'eb',''),
 ('2018-06-12','2020-08-06 23:47:35.060301',5,NULL,NULL,b'110011110111010111','blwc',0.6,NULL,1971,'lwc',''),
 ('2005-02-03','2016-10-11 00:00:00',0,'w','2005-03-25 00:00:00',b'0101001001001','',0.2,'21:56:26.025743',1971,'c',''),
 ('2025-10-10',NULL,1,'vumvyv','2034-05-06 18:17:26.004829',b'10110101101100100001000011001111100100111101100','um',0.3,'19:42:29.005509',1992,'m',''),
 ('2014-01-27','1980-01-01 00:00:00',2,'vyvb','1975-04-08 10:13:06.052060',b'111011110111111010011111011011101111','yv',0.4,'15:13:32.059509',2011,NULL,''),
 ('2016-08-04','2008-12-03 01:55:41.030042',6,'vb','1993-09-08 00:01:40.016566',b'000101110101100111001101010110','b',0.5,'08:39:05.055786',1993,'it',''),
 ('1980-01-01','1980-01-01 00:00:00',9,'tx','1991-07-19 05:37:43.056696',b'110110111010111101010001100010111100110011111010100100100','k',0.9,'00:00:00',1999,'h',''),
 (NULL,'2007-06-14 15:47:29.017306',9,'foo',NULL,b'0100111000001011111000111010000011011000011000101010','qjxdzd',0.3,'13:52:31.035851',2001,'jxdzd',''),
 (NULL,'2022-04-28 18:27:19.060240',4,'foo','2018-02-28 02:49:16.013066',b'10000100000000110011011110101110100001100011101110011011100','d',0.0,'15:01:48.022368',1987,'zdytun',''),
 ('1987-10-10','1975-06-02 02:47:57.012240',9,'dy','1980-06-10 00:00:00',b'110000111','',0.7,'08:13:44.003967',1981,'',''),
 ('2015-09-28',NULL,4,'d','2005-08-09 08:35:04.039832',b'00','b',0.1,'00:47:43.048164',1973,'yt',''),
 ('2030-01-18','2011-12-09 00:00:00',6,NULL,'1982-05-13 00:00:00',b'111110100001000010001110110010111100001011010','v',0.2,'00:39:24.001557',2027,'tun',''),
 ('1980-06-28','2016-04-07 00:00:00',8,'y','1980-01-01 00:00:00',b'01011100100001010110101110111110110','unq',0.7,'00:00:00',2016,'foo',''),
 ('2018-09-16','1975-01-03 00:00:00',NULL,'qv','2028-05-10 00:00:00',b'0001100101001011100110','vv',0.0,'23:54:42.064230',2000,'vr',''),
 ('2033-12-11','2021-07-23 12:20:17.025201',3,'rm','1996-07-16 00:00:00',b'1011001110','mpyx',0.7,'04:04:01.055956',2009,'pyx',''),
 ('1982-12-18','1996-09-16 00:00:00',9,'yx','2003-10-14 03:54:44.012072',b'11001011011000001111011000101111101110100101','x',0.9,NULL,1981,'rencqh',''),
 ('1980-01-01','2009-01-17 12:11:34.030449',7,'encqh','1980-01-01 00:00:00',b'1101001101001000101010001100100100','ncq',0.4,'23:44:22.012217',2030,'b',''),
 ('2015-05-03','1987-03-19 17:37:53.053429',6,'','2012-08-03 00:00:00',b'0','cq',0.0,'17:16:43.030750',2035,'qh',NULL),
 (NULL,'1980-01-01 00:00:00',1,'huyr','2012-02-11 14:15:13.004778',b'110011001100010100001101011001011110010000011001110101','',0.7,'09:33:00.034425',2024,'uyr',''),
 ('2019-06-05','2020-08-05 23:53:07.028129',1,'yr',NULL,b'1001011110101010001111101000011001011111100','rflu',0.3,NULL,2016,'fluezqe',''),
 ('1980-01-10','2025-05-12 08:22:39.039097',1,'lu','1975-07-24 00:00:00',b'10100111001111101001110000110011','',0.1,'23:58:28.031575',2005,NULL,''),
 ('2008-03-17','1982-05-27 11:44:53.038339',NULL,'uezqe','2024-10-12 02:16:04.063095',b'1001010110101101000101011011000011','e',0.9,'19:37:29.063243',1987,'zqekmq','')
;
--enable_query_log
--disable_warnings
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t1 ADD INDEX(col_enum,vcol_int);
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t1 ADD INDEX(col_year);
--enable_warnings
DROP TABLE t1;

#
# MDEV-11750 Assertion `vfield' failed in TABLE::update_virtual_fields after crash recovery on corrupted MyISAM table
#
create table t1 (
  pk int primary key auto_increment,
  b bit default null,
  key(b)
) engine=myisam;

insert into t1 values (null, 0);
repair table t1 extended;
drop table t1;

#
# MDEV-18486 Database crash on a table with indexed virtual column
#
create table t1 ( id int primary key,
        hexid  varchar(10) generated always as (hex(id)) stored,
        key (hexid)) engine=myisam;
insert into t1 (id) select 100;
select * from t1;
drop table t1;


--echo #
--echo #  MDEV-15881 Assertion `is_valid_value_slow()' failed in Datetime::Datetime or corrupt data after ALTER with indexed persistent column
--echo #

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 INTO OUTFILE 'load_t1' FROM t1;
DELETE FROM t1;
LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2);
SELECT * FROM t1 WHERE d2 < d1;
DROP TABLE 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;
LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2);
SELECT * FROM t1 WHERE d2 < d1;
DROP TABLE t1;
# Cleanup
--let $datadir= `SELECT @@datadir`
--remove_file $datadir/test/load_t1


--echo #
--echo # MDEV-20015 Assertion `!in_use->is_error()' failed in TABLE::update_virtual_field
--echo #
create or replace table t1 (a int);
insert into t1 (a) values (1), (1);
create or replace table t2 (pk int, b int, c int as (b) virtual, primary key (pk), key(c));
--error ER_DUP_ENTRY
insert into t2 (pk) select a from t1;
drop tables t1, t2;