mariadb/mysql-test/suite/gcol/t/gcol_bugfixes.test

################################################################################
# t/gcol_bugfixes.test                                                         #
#                                                                              #
# Purpose:                                                                     #
#  Bug fixes that only need one storage engine                                 #
#                                                                              #
################################################################################

--source include/have_innodb.inc
--echo # Bug#21230709: Alter table statement fails with division by zero

CREATE TABLE t1 (
  col1 INTEGER NOT NULL,
  col2 INTEGER NOT NULL,
  col3 INTEGER NOT NULL,
  gcol1 INTEGER GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
  col4 INTEGER DEFAULT NULL,
  col5 INTEGER DEFAULT NULL,
  col6 INTEGER DEFAULT NULL,
  col7 INTEGER DEFAULT NULL,
  col8 INTEGER DEFAULT NULL,
  col9 INTEGER DEFAULT NULL,
  col10 INTEGER DEFAULT NULL,
  col11 INTEGER DEFAULT NULL,
  col12 INTEGER DEFAULT NULL,
  col13 INTEGER DEFAULT NULL,
  col14 INTEGER DEFAULT NULL,
  col15 INTEGER DEFAULT NULL,
  col16 INTEGER DEFAULT NULL,
  col17 INTEGER DEFAULT NULL,
  col18 INTEGER DEFAULT NULL,
  col19 INTEGER DEFAULT NULL,
  col20 INTEGER DEFAULT NULL,
  col21 INTEGER DEFAULT NULL,
  col22 INTEGER DEFAULT NULL,
  col23 INTEGER DEFAULT NULL,
  col24 INTEGER DEFAULT NULL,
  col25 INTEGER DEFAULT NULL,
  col26 INTEGER DEFAULT NULL,
  col27 INTEGER DEFAULT NULL,
  col28 INTEGER DEFAULT NULL,
  col29 INTEGER DEFAULT NULL,
  col30 INTEGER DEFAULT NULL,
  col31 INTEGER DEFAULT NULL,
  col32 INTEGER DEFAULT NULL,
  col33 INTEGER DEFAULT NULL,
  gcol2 INTEGER GENERATED ALWAYS AS (col2 + col2) VIRTUAL,
  gcol3 INTEGER GENERATED ALWAYS AS (gcol2 / gcol2) VIRTUAL,
  PRIMARY KEY (col1),
  KEY idx1 (gcol1)
) engine=innodb;

INSERT INTO t1 (col1, col2, col3)
  VALUES (0,1,2), (1,2,3), (2,3,4), (3,4,5), (4,5,6);

# This is likely needed to ensure we allocate a new record buffer that
# contains zero in the mis-used field
FLUSH TABLE t1;

ALTER TABLE t1 ADD COLUMN extra INTEGER;

DROP TABLE t1;

--echo #
--echo # Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED
--echo #

CREATE TABLE t1 (
  c_blob BLOB,
  c_blob_key BLOB GENERATED ALWAYS AS (REPEAT(c_blob,15)) STORED,
  KEY (c_blob_key(200))
);

INSERT  INTO t1 (c_blob) VALUES ('xceks');

DROP TABLE t1;

--echo #
--echo # Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED.
--echo #

CREATE TABLE c (
  pk INTEGER AUTO_INCREMENT,
  col_int_nokey INTEGER,
  gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
  col_date_nokey DATE,
  gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
  col_datetime_nokey DATETIME,
  col_time_nokey TIME,
  gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
  gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
  col_varchar_nokey VARCHAR(1),
  gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
  PRIMARY KEY (pk),
  UNIQUE KEY (gcol_int_key),
  UNIQUE KEY (gcol_varchar_key),
  UNIQUE KEY (gcol_date_key),
  KEY (gcol_time_key),
  KEY (gcol_datetime_key),
  UNIQUE KEY (gcol_int_key, gcol_varchar_key),
  KEY (gcol_int_key, col_int_nokey),
  KEY(gcol_int_key,gcol_date_key),
  KEY(gcol_int_key, gcol_time_key),
  KEY(gcol_int_key, gcol_datetime_key),
  UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key),
  UNIQUE KEY (gcol_varchar_key, col_varchar_nokey),
  UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key)
)  ENGINE=INNODB;

INSERT IGNORE INTO c ( col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey)
VALUES (7, '2004-04-09', '14:03:03.042673', '2001-11-28 00:50:27.051028', 'c'),
       (1, '2006-05-13', '01:46:09.016386', '2007-10-09 19:53:04.008332', NULL);

CREATE TABLE bb (
  pk INTEGER AUTO_INCREMENT,
  col_int_nokey INTEGER,
  gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
  col_date_nokey DATE,
  gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
  col_datetime_nokey DATETIME,
  col_time_nokey TIME,
  gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
  gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
  col_varchar_nokey VARCHAR(1),
  gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
  PRIMARY KEY (pk),
  UNIQUE KEY (gcol_int_key),
  UNIQUE KEY (gcol_varchar_key),
  UNIQUE KEY (gcol_date_key),
  KEY (gcol_time_key),
  KEY (gcol_datetime_key),
  UNIQUE KEY (gcol_int_key, gcol_varchar_key),
  KEY (gcol_int_key, col_int_nokey),
  KEY(gcol_int_key,gcol_date_key),
  KEY(gcol_int_key, gcol_time_key),
  KEY(gcol_int_key, gcol_datetime_key),
  UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key),
  UNIQUE KEY (gcol_varchar_key, col_varchar_nokey),
  UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key)
)  AUTO_INCREMENT=10 ENGINE=INNODB;

INSERT IGNORE INTO bb ( col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey)
 VALUES (0, '2003-08-04', '01:48:05.048577', '2006-11-03 00:00:00', 'p'),
        (2, '2007-11-06', '00:00:00', '2009-11-26 19:28:11.005115', 'n');

CREATE TABLE cc (
  pk INTEGER AUTO_INCREMENT,
  col_int_nokey INTEGER,
  gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
  col_date_nokey DATE,
  gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
  col_datetime_nokey DATETIME,
  col_time_nokey TIME,
  gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
  gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
  col_varchar_nokey VARCHAR(1),
  gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
  PRIMARY KEY (pk),
  UNIQUE KEY (gcol_int_key),
  UNIQUE KEY (gcol_varchar_key),
  UNIQUE KEY (gcol_date_key),
  KEY (gcol_time_key),
  KEY (gcol_datetime_key),
  UNIQUE KEY (gcol_int_key, gcol_varchar_key),
  KEY (gcol_int_key, col_int_nokey),
  KEY(gcol_int_key,gcol_date_key),
  KEY(gcol_int_key, gcol_time_key),
  KEY(gcol_int_key, gcol_datetime_key),
  UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key),
  UNIQUE KEY (gcol_varchar_key, col_varchar_nokey),
  UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key)
)  AUTO_INCREMENT=10 ENGINE=INNODB;

INSERT IGNORE INTO cc (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey)
 VALUES (172, '2009-04-23', '00:00:00', '2000-12-07 10:17:40.013275', 'h'),
        (NULL, '2002-10-06', '00:50:49.017545', NULL, 'm');

let $query=
SELECT
gp1 . gcol_datetime_key AS g1
FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . gcol_datetime_key <> gp1 .
col_time_nokey )
WHERE
gp1 . col_varchar_nokey IN
(
SELECT
DISTINCT p1 . gcol_varchar_key AS p1
FROM bb AS p1 LEFT JOIN bb AS p2
ON ( p1 . gcol_int_key = p2 . pk )
)
AND gp1 . col_varchar_nokey = 'b'
HAVING g1 > 6;

eval EXPLAIN $query;
eval $query;
DROP TABLE bb, c, cc;

--echo # Bug#21284646: Assertion !(table || table->read_set || bitmap_is_set())

CREATE TABLE c (
 pk INTEGER AUTO_INCREMENT,
 col_int_nokey INTEGER NOT NULL,
 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
 col_date_nokey DATE NOT NULL,
 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
 col_datetime_nokey DATETIME NOT NULL,
 col_time_nokey TIME NOT NULL,
 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
 col_varchar_nokey VARCHAR(1) NOT NULL,
 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
 PRIMARY KEY (pk,col_int_nokey),
 UNIQUE KEY (col_int_key),
 UNIQUE KEY (col_varchar_key),
 UNIQUE KEY (col_date_key),
 KEY (col_time_key),
 KEY (col_datetime_key),
 UNIQUE KEY (col_int_key, col_varchar_key),
 KEY (col_int_key, col_int_nokey),
 KEY(col_int_key,col_date_key),
 KEY(col_int_key, col_time_key),
 KEY(col_int_key, col_datetime_key),
 UNIQUE KEY (col_date_key,col_time_key,col_datetime_key),
 UNIQUE KEY (col_varchar_key, col_varchar_nokey),
 UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key)
)  ENGINE=INNODB;

INSERT INTO c (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) VALUES
(1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'),
(4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r');

ANALYZE TABLE c;
let $query=
SELECT COUNT(DISTINCT col_varchar_key) AS x
FROM c
WHERE col_varchar_key IN ('rr', 'rr') OR
      col_int_nokey <> 9 AND
      pk >= 8
HAVING x > '2000-02-06'
ORDER BY col_time_nokey, pk;

eval explain $query;
eval $query;

DROP TABLE c;

--echo # Bug#21341044: Conditional jump at sort_param::make_sort_key

CREATE TABLE t1 (
 pk INTEGER AUTO_INCREMENT,
 col_int_nokey INTEGER,
 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
 col_blob_nokey BLOB,
 col_blob_key BLOB GENERATED ALWAYS AS (REPEAT(col_blob_nokey,15)) VIRTUAL,
 col_longblob_nokey LONGBLOB,
 col_longtext_nokey LONGTEXT,
 col_longblob_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 20)) VIRTUAL,
 col_longtext_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 18)) VIRTUAL,
 col_text_nokey TEXT,
 col_text_key TEXT GENERATED ALWAYS AS (REPEAT(col_text_nokey, 30)) VIRTUAL,
 PRIMARY KEY (pk),
 KEY (col_int_key),
 KEY (col_text_key(50)),
 KEY (col_blob_key(200)),
 KEY (col_longtext_key(200)),
 KEY (col_longblob_key(200)),
 KEY (col_int_key, col_text_key(100)),
 KEY (col_int_key, col_longtext_key(100)),
 KEY (col_int_key, col_blob_key(100)),
 KEY (col_int_key, col_longblob_key(100)),
 KEY (col_longtext_key(10), col_longblob_key(100)),
 KEY (col_int_key, col_text_key(10), col_blob_key(100), col_longtext_key(50), col_longblob_key(50))
) engine=innodb;

INSERT INTO t1 (col_int_nokey,col_blob_nokey,col_longtext_nokey,col_longblob_nokey,col_text_nokey)
VALUES
(0, 'ijcszxw', 'ijcszxw', 'ijcszxw', 'ijcszxw'),
(5, 'jcszxwb', 'jcszxwb', 'jcszxwb', 'jcszxwb'),
(4, 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv'),
(3, 'szxw', 'szxw', 'szxw', 'szxw'),
(7, 'zxwb', 'zxwb', 'zxwb', 'zxwb'),
(42, 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky'),
(142, 'wbjj', 'wbjj', 'wbjj', 'wbjj'),
(5, 'bjjv', 'bjjv', 'bjjv', 'bjjv'),
(0, 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu'),
(3, 'j', 'j', 'j', 'j');
SELECT alias1.pk AS field1
FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2
     ON alias1.col_int_key = alias2.col_int_key
WHERE alias2.col_int_key BETWEEN 8 AND (8 + 1 ) OR
      alias2.col_int_key  BETWEEN 8 AND (8 + 5 ) AND
      alias2.col_int_key != 20 OR
      alias2.col_int_key IN (8, 5, 8) AND
      alias2.col_int_key >= 0 AND
      alias2.col_int_key <= ( 8 + 75 ) AND
      alias1.pk IS NOT NULL
ORDER BY field1;

DROP TABLE t1;
--echo # bug#21487651: gcols: memory leak after failed alter table
CREATE TABLE t(a int);
ALTER TABLE t ADD COLUMN b int GENERATED ALWAYS AS (
date_sub(a,interval a month)) VIRTUAL;
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a));
DROP TABLE t;

--echo #
--echo # Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
--echo #               (II)
--echo #
CREATE TABLE t1( a INT ) ENGINE = INNODB;
INSERT INTO t1( a ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );

ALTER TABLE t1 ADD COLUMN b INT GENERATED ALWAYS AS (a - 1) STORED;
ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS (b + 1) VIRTUAL;

--echo # Used to cause valgrind warning.
ALTER TABLE t1 ADD INDEX( c );

ANALYZE TABLE t1;

--echo # Make sure the index is correct. That's kinda important.
EXPLAIN
SELECT c FROM t1;
SELECT c FROM t1;

DROP TABLE t1;

--echo #
--echo # Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED.
--echo #

CREATE TABLE C (
col_int_1 INT,
col_int_2 INT GENERATED ALWAYS AS (col_int_1 + col_int_1) STORED,
col_int_3 INT GENERATED ALWAYS AS (col_int_2 + col_int_1) VIRTUAL
);

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS
 SELECT
 col_int_2 AS field1, col_int_2 AS field2,
 col_int_3 AS field3, col_int_3 AS field4
 FROM C;

SELECT * FROM v1;

DROP TABLE C;
DROP VIEW v1;

--echo #
--echo # Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
--echo #

CREATE TABLE t (a INT);
CREATE TABLE v (
a INT,
c INT,
b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL,
KEY(c,b(1)));
INSERT INTO v (a,c) VALUES (1,1);

let $query=
SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
eval EXPLAIN $query;
eval $query;

# A similar one:
let $query=
SELECT (SELECT MAX(c) FROM v);
eval EXPLAIN $query;
eval $query;

DROP TABLE t, v;

CREATE TABLE v (
a INT,
c INT,
b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, KEY(c,b(1)));
INSERT INTO v (a,c) VALUES (1,1);
SELECT MAX(c), COUNT(b) FROM v;
DROP TABLE v;

# Using PK suffix of secondary index
CREATE TABLE v (
a INT PRIMARY KEY,
b INT, KEY(b));
INSERT INTO v (a,b) VALUES (1,1);
SELECT MAX(a) FROM v WHERE b=1;
DROP TABLE v;

--echo #
--echo # Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS
--echo #               VIRTUAL GENERATED COLUMN
--echo #
CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL);
CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW BEGIN END;
INSERT INTO t (a) VALUES (1);
SELECT * FROM t;
# DROP TRIGGER used to hit a DBUG_ASSERT.
DROP TRIGGER tr;
SELECT * FROM t;
CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t);
# And this function call hit the same DBUG_ASSERT.
SELECT f();
DROP FUNCTION f;
SELECT * FROM t;
DROP TABLE t;

--echo #
--echo # Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED.
--echo #

CREATE TABLE C(
c1 INT AUTO_INCREMENT,
c8 DATETIME,
c9 TIME,
c11 TIME GENERATED ALWAYS AS(ADDTIME(c8,c9)) VIRTUAL,
c13 TIME GENERATED ALWAYS AS(ADDTIME(c8,c11)) VIRTUAL,
PRIMARY KEY(c1),
UNIQUE KEY(c13)
);

INSERT INTO C (c8,c9) VALUES('1970-01-01',0),('1970-01-01',1);

CREATE VIEW view_C AS SELECT * FROM C;

SELECT t1.c13 FROM C AS t2 STRAIGHT_JOIN C AS t1 FORCE INDEX(c13);
SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2;

DROP TABLE C;
DROP VIEW view_C;

--echo #
--echo # Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE
--echo # LOCATION
--echo #
CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL);
--error ER_BAD_FIELD_ERROR
ALTER TABLE t CHANGE COLUMN q w INT;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t CHANGE COLUMN q w INT;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t CHANGE COLUMN q w INT;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t CHANGE COLUMN q w INT;
DROP TABLE t;

--echo #
--echo # Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED
--echo #

CREATE TABLE t(b BLOB);
--error ER_OPERAND_COLUMNS
ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL;
DROP TABLE t;
--error ER_OPERAND_COLUMNS
CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL);

--echo #
--echo # Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
--echo #

CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
SHOW CREATE TABLE t1;
INSERT INTO t1 (a,b) VALUES('1','1');
SELECT * FROM t1;
SET SQL_MODE=PIPES_AS_CONCAT;
SELECT * FROM t1;
FLUSH TABLES;
SELECT * FROM t1;
DROP TABLE t1;
# The other way around:
CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
SHOW CREATE TABLE t1;
INSERT INTO t1 (a,b) VALUES('1','1');
SELECT * FROM t1;
SET SQL_MODE=DEFAULT;
SELECT * FROM t1;
FLUSH TABLES;
SELECT * FROM t1;
DROP TABLE t1;

--echo # Bug#22018999: gcols: assertion failed: !error

SET @save_old_sql_mode= @@sql_mode;
SET sql_mode="";

CREATE TABLE t (a INTEGER AS (SUBSTR('','a',1))) engine=innodb;
DROP TABLE t;

CREATE TABLE t (a INTEGER) engine=innodb;

ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1));

DROP TABLE t;

set sql_mode= @save_old_sql_mode;

--echo # Bug#21875520 Problems with virtual column indexes

CREATE TABLE t(
  a TIMESTAMP,
  b BLOB,
  c TIMESTAMP GENERATED ALWAYS AS (GREATEST(a, '2000-01-01 00:00:00')) VIRTUAL,
  UNIQUE KEY(c)
);
INSERT INTO t(b) VALUES ('');
UPDATE t SET a='2001-01-01 00:00:00';
SELECT c FROM t;
SELECT c, a FROM t;
UPDATE t SET b='xyz';
DO (SELECT @c1:= c FROM t);
DO (SELECT (@c2:= c) - a FROM t);
SELECT @c2 - @c1;

DROP TABLE t;

--echo #
--echo # Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA
--echo #

CREATE TABLE t (
  a INT,
  b INT GENERATED ALWAYS AS (1) VIRTUAL,
  c INT GENERATED ALWAYS AS (1) VIRTUAL,
  d INT GENERATED ALWAYS AS (1) VIRTUAL,
  KEY (b,d)
) ENGINE=INNODB;
INSERT INTO t VALUES();
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT 1 FROM t WHERE c GROUP BY b;
COMMIT;
DROP TABLE t;

--echo #
--echo # Bug #25793677	INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN ....
--echo #

CREATE TABLE v (
a INT,
c INT,
b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL,
KEY(c,b(1))) charset utf8mb4;
INSERT INTO v (a,c) VALUES (1,1);
SELECT (SELECT MAX(c) FROM v);
DROP TABLE v;

--echo #
--echo # MDEV-9255 Add generation_expression to information_schema.columns.
--echo #

CREATE TABLE gcol_t1 (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);

SELECT table_schema,table_name,column_name,extra,is_generated,generation_expression
FROM information_schema.columns WHERE table_name='gcol_t1';

DROP TABLE gcol_t1;

--echo #
--echo # MDEV-16039 Crash when selecting virtual columns
--echo # generated using functions with DAYNAME()
--echo #

CREATE TABLE t1 (
  suppliersenttoday INT NOT NULL,
  suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05')))
) COLLATE utf8_bin;

INSERT INTO t1 (suppliersenttoday) VALUES (0);
INSERT INTO t1 (suppliersenttoday) VALUES (0);
SELECT * FROM t1;

PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)';

CREATE OR REPLACE TABLE t1 (
  suppliersenttoday INT NOT NULL,
  suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05')))
) COLLATE utf8_bin;

EXECUTE STMT;
EXECUTE STMT;
SELECT * FROM t1;

DROP TABLE t1;

--echo # (duplicate) MDEV-20380 Server crash during update
CREATE TABLE gafld (
  nuigafld INTEGER NOT NULL,
  ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL
           DEFAULT SUBSTRING_INDEX(USER(),'@',1)
);
EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10;
EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10;
DROP TABLE gafld;

--echo # (duplicate) MDEV-17653 replace into generated columns is unstable
--echo # Some columns are snipped from the MDEV test
CREATE TABLE t (
  c0 TIMESTAMP NOT NULL DEFAULT current_timestamp()
                        ON UPDATE current_timestamp(),
  c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH('2020-02-05')),
  c4 TIME NOT NULL,
  c8 SMALLINT(6) GENERATED ALWAYS AS
                 (CONCAT_WS(CONVERT(C1 USING CP932),
                            '900') <> (c4 = 1)),
  PRIMARY KEY (c4)
) DEFAULT CHARSET=latin1;

REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55';
REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55';
REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55';

DROP TABLE t;

--echo # (duplicate) MDEV-17986 crash when I insert on a table
CREATE OR REPLACE TABLE t2 (
  number BIGINT(20) NOT NULL,
  lrn BIGINT(20) NOT NULL DEFAULT 0,
  source VARCHAR(15) NOT NULL
                    DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))),
  PRIMARY KEY (number)
);

REPLACE t2(number) VALUES('1');
REPLACE t2(number) VALUES('1');

DROP TABLE t2;

--echo # MDEV-24583 SELECT aborts after failed REPLACE into table with vcol

CREATE TABLE t1 (pk INT, a VARCHAR(3), v VARCHAR(3) AS (CONCAT('x-',a)),
                 PRIMARY KEY(pk)) ENGINE=MyISAM;
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1 (pk, a) VALUES (1,'foo');
SET sql_mode=CONCAT(@@sql_mode,',STRICT_ALL_TABLES');
--error 0,ER_DATA_TOO_LONG
REPLACE INTO t1 (pk,a) VALUES (1,'qux');
SELECT * FROM v1;

# Cleanup
DROP VIEW v1;
DROP TABLE t1;

CREATE TABLE t1 (
  pk INT,
  a VARCHAR(1),
  v VARCHAR(1) AS (CONCAT('virt-',a)) VIRTUAL,
  PRIMARY KEY (pk)
) ENGINE=InnoDB;

INSERT INTO t1 (pk,a) VALUES
(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f');

 --error ER_DATA_TOO_LONG
REPLACE INTO t1 (pk) VALUES (1);
SELECT * FROM t1 ORDER BY a;

SET SQL_MODE=DEFAULT;
DROP TABLE t1;

--echo # (duplicate) MDEV-24656
--echo # [FATAL] InnoDB: Data field type 0, len 0, ASAN heap-buffer-overflow
--echo # upon LOAD DATA with virtual columns

CREATE TABLE t1 (id INT PRIMARY KEY, a VARCHAR(2333),
                 va VARCHAR(171) AS (a)) ENGINE=InnoDB;
INSERT INTO t1 (id,a) VALUES (1,REPEAT('x',200));
SELECT id, va INTO OUTFILE 'load_t1' FROM t1;
--error ER_DATA_TOO_LONG
LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va);
SELECT * FROM t1;
LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va);

DROP TABLE t1;
--let $datadir= `select @@datadir`
--remove_file $datadir/test/load_t1

CREATE TABLE t1 (id BIGINT PRIMARY KEY, a VARCHAR(2333),
                 va VARCHAR(171) AS (a)) ENGINE=InnoDB;
INSERT INTO t1 (id,a) VALUES (1,REPEAT('x',200));
SELECT id, va INTO OUTFILE 'load_t1' FROM t1;
--error ER_DATA_TOO_LONG
LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va);
SELECT * FROM t1;
LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va);

# Cleanup
DROP TABLE t1;
--let $datadir= `select @@datadir`
--remove_file $datadir/test/load_t1


--echo # (duplicate) MDEV-24665
--echo # ASAN errors, assertion failures, corrupt values after failed
--echo # LOAD DATA into table with virtual/stored column

CREATE TABLE t1 (id INT PRIMARY KEY,
                 ts TIMESTAMP DEFAULT '1971-01-01 00:00:00',
                 c VARBINARY(8) DEFAULT '', vc VARCHAR(3) AS (c) STORED);
INSERT IGNORE INTO t1 (id,c) VALUES (1,'foobar');
SELECT id, ts, vc INTO OUTFILE 'load_t1' FROM t1;
--error 0,ER_DATA_TOO_LONG
LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id, ts, vc);
INSERT IGNORE INTO t1 (id) VALUES (2);

# Cleanup
DROP TABLE t1;
--let $datadir= `select @@datadir`
--remove_file $datadir/test/load_t1


--echo #
--echo # MDEV-28566 Assertion `!expr->is_fixed()' failed in bool
--echo #            Virtual_column_info::fix_session_expr(THD*)
--echo #

CREATE TABLE t1 (c1 CHAR(1));
FLUSH TABLES WITH READ LOCK;
--error ER_CANT_UPDATE_WITH_READLOCK
UPDATE t1 SET c1=1;
unlock tables;
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 CHAR AS (CONCAT (0,DAYNAME (0))));
FLUSH TABLES WITH READ LOCK;
--error ER_CANT_UPDATE_WITH_READLOCK
UPDATE t1 SET c1=1;
unlock tables;
UPDATE t1 SET c1=1;
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a int primary key, c1 CHAR AS (CONCAT (0,DAYNAME (0))));
insert into t1 (a) values (1);
FLUSH TABLES WITH READ LOCK;
--error ER_CANT_UPDATE_WITH_READLOCK
UPDATE t1 SET c1=1;
--error ER_CANT_UPDATE_WITH_READLOCK
UPDATE t1 SET a=2;
unlock tables;
UPDATE t1 SET a=2;
--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
UPDATE t1 SET c1=1;
SELECT * FROM t1;
DROP TABLE t1;