# 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);
FLUSH TABLE t1;
ALTER TABLE t1 ADD COLUMN extra INTEGER;
DROP TABLE t1;
#
# Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED
#
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;
#
# Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED.
#
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);
Warnings:
Note	1265	Data truncated for column 'gcol_time_key' at row 1
Note	1265	Data truncated for column 'gcol_time_key' at row 2
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');
Warnings:
Note	1265	Data truncated for column 'gcol_time_key' at row 1
Note	1265	Data truncated for column 'gcol_time_key' at row 2
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');
Warnings:
Note	1265	Data truncated for column 'gcol_time_key' at row 1
EXPLAIN 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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
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;
g1
DROP TABLE bb, c, cc;
# 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');
Warnings:
Note	1265	Data truncated for column 'col_time_key' at row 1
Note	1265	Data truncated for column 'col_time_key' at row 2
Note	1265	Data truncated for column 'col_time_key' at row 3
Note	1265	Data truncated for column 'col_time_key' at row 4
Note	1265	Data truncated for column 'col_time_key' at row 5
Note	1265	Data truncated for column 'col_time_key' at row 6
ANALYZE TABLE c;
Table	Op	Msg_type	Msg_text
test.c	analyze	status	Engine-independent statistics collected
test.c	analyze	status	OK
explain 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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	c	index_merge	PRIMARY,col_varchar_key,col_varchar_key_2	col_varchar_key,PRIMARY	11,4	NULL	2	Using union(col_varchar_key,PRIMARY); Using where
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;
x
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '2000-02-06'
DROP TABLE c;
# 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;
field1
2
2
3
8
8
DROP TABLE t1;
# 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;
ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a));
ERROR HY000: Function or expression 'sum()' cannot be used in the GENERATED ALWAYS AS clause of `c`
DROP TABLE t;
#
# Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
#               (II)
#
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;
# Used to cause valgrind warning.
ALTER TABLE t1 ADD INDEX( c );
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
# Make sure the index is correct. That's kinda important.
EXPLAIN
SELECT c FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	c	5	NULL	5	Using index
SELECT c FROM t1;
c
1
2
3
4
5
DROP TABLE t1;
#
# Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED.
#
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;
field1	field2	field3	field4
DROP TABLE C;
DROP VIEW v1;
#
# Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
#
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);
EXPLAIN SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
1
EXPLAIN SELECT (SELECT MAX(c) FROM v);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
SELECT (SELECT MAX(c) FROM v);
(SELECT MAX(c) FROM v)
1
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;
MAX(c)	COUNT(b)
1	1
DROP TABLE v;
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;
MAX(a)
1
DROP TABLE v;
#
# Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS
#               VIRTUAL GENERATED COLUMN
#
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;
a	b
1	1
DROP TRIGGER tr;
SELECT * FROM t;
a	b
1	1
CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t);
SELECT f();
f()
1
DROP FUNCTION f;
SELECT * FROM t;
a	b
1	1
DROP TABLE t;
#
# Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED.
#
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);
Warnings:
Note	1265	Data truncated for column 'c11' at row 1
Note	1265	Data truncated for column 'c13' at row 1
Note	1265	Data truncated for column 'c11' at row 2
Note	1265	Data truncated for column 'c13' at row 2
CREATE VIEW view_C AS SELECT * FROM C;
SELECT /*+ NO_BNL(t1) */ t1.c13 FROM C AS t2 STRAIGHT_JOIN C AS t1 FORCE INDEX(c13);
c13
00:00:00
00:00:00
00:00:01
00:00:01
SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2;
c13
00:00:00
00:00:01
DROP TABLE C;
DROP VIEW view_C;
#
# Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE
# LOCATION
#
CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL);
ALTER TABLE t CHANGE COLUMN q w INT;
ERROR 42S22: Unknown column 'q' in 't'
ALTER TABLE t CHANGE COLUMN q w INT;
ERROR 42S22: Unknown column 'q' in 't'
ALTER TABLE t CHANGE COLUMN q w INT;
ERROR 42S22: Unknown column 'q' in 't'
ALTER TABLE t CHANGE COLUMN q w INT;
ERROR 42S22: Unknown column 'q' in 't'
DROP TABLE t;
#
# Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED
#
CREATE TABLE t(b BLOB);
ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t;
CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL);
ERROR 21000: Operand should contain 1 column(s)
#
# Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
#
CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(1) DEFAULT NULL,
  `b` char(1) DEFAULT NULL,
  `c` char(2) GENERATED ALWAYS AS (`a` <> 0 or `b` <> 0) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 (a,b) VALUES('1','1');
SELECT * FROM t1;
a	b	c
1	1	1
SET SQL_MODE=PIPES_AS_CONCAT;
SELECT * FROM t1;
a	b	c
1	1	1
FLUSH TABLES;
SELECT * FROM t1;
a	b	c
1	1	1
DROP TABLE t1;
CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(1) DEFAULT NULL,
  `b` char(1) DEFAULT NULL,
  `c` char(2) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 (a,b) VALUES('1','1');
SELECT * FROM t1;
a	b	c
1	1	11
SET SQL_MODE=DEFAULT;
SELECT * FROM t1;
a	b	c
1	1	11
FLUSH TABLES;
SELECT * FROM t1;
a	b	c
1	1	11
DROP TABLE t1;
# 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;
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'a'
DROP TABLE t;
CREATE TABLE t (a INTEGER) engine=innodb;
ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1));
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'a'
DROP TABLE t;
set sql_mode= @save_old_sql_mode;
# 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;
c
2001-01-01 00:00:00
SELECT c, a FROM t;
c	a
2001-01-01 00:00:00	2001-01-01 00:00:00
UPDATE t SET b='xyz';
DO (SELECT @c1:= c FROM t);
DO (SELECT (@c2:= c) - a FROM t);
SELECT @c2 - @c1;
@c2 - @c1
0
DROP TABLE t;
#
# Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA
#
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;
1
1
COMMIT;
DROP TABLE t;
#
# Bug #25793677	INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN ....
#
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);
(SELECT MAX(c) FROM v)
1
DROP TABLE v;
#
# MDEV-9255 Add generation_expression to information_schema.columns.
#
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';
table_schema	table_name	column_name	extra	is_generated	generation_expression
test	gcol_t1	sidea		NEVER	NULL
test	gcol_t1	sideb		NEVER	NULL
test	gcol_t1	sidec	VIRTUAL GENERATED	ALWAYS	sqrt(`sidea` * `sidea` + `sideb` * `sideb`)
DROP TABLE gcol_t1;
#
# MDEV-16039 Crash when selecting virtual columns
# generated using functions with DAYNAME()
#
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;
suppliersenttoday	suppliercaptoday
0	Wednesday
0	Wednesday
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;
suppliersenttoday	suppliercaptoday
1	Wednesday
1	Wednesday
DROP TABLE t1;
# (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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
DROP TABLE gafld;
# (duplicate) MDEV-17653 replace into generated columns is unstable
# 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;
# (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;
# 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');
REPLACE INTO t1 (pk,a) VALUES (1,'qux');
SELECT * FROM v1;
pk	a	v
1	foo	x-f
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');
REPLACE INTO t1 (pk) VALUES (1);
ERROR 22001: Data too long for column 'v' at row 1
SELECT * FROM t1 ORDER BY a;
pk	a	v
1	a	v
2	b	v
3	c	v
4	d	v
5	e	v
6	f	v
SET SQL_MODE=DEFAULT;
DROP TABLE t1;
# (duplicate) MDEV-24656
# [FATAL] InnoDB: Data field type 0, len 0, ASAN heap-buffer-overflow
# 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;
LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va);
ERROR 22001: Data too long for column 'va' at row 1
SELECT * FROM t1;
id	a	va
1	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va);
Warnings:
Warning	1062	Duplicate entry '1' for key 'PRIMARY'
DROP TABLE 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;
LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va);
ERROR 22001: Data too long for column 'va' at row 1
SELECT * FROM t1;
id	a	va
1	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va);
Warnings:
Warning	1062	Duplicate entry '1' for key 'PRIMARY'
DROP TABLE t1;
# (duplicate) MDEV-24665
# ASAN errors, assertion failures, corrupt values after failed
# 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');
Warnings:
Warning	1265	Data truncated for column 'vc' at row 1
SELECT id, ts, vc INTO OUTFILE 'load_t1' FROM t1;
LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id, ts, vc);
INSERT IGNORE INTO t1 (id) VALUES (2);
DROP TABLE t1;
#
# MDEV-28566 Assertion `!expr->is_fixed()' failed in bool
#            Virtual_column_info::fix_session_expr(THD*)
#
CREATE TABLE t1 (c1 CHAR(1));
FLUSH TABLES WITH READ LOCK;
UPDATE t1 SET c1=1;
ERROR HY000: Can't execute the query because you have a conflicting read lock
unlock tables;
SELECT * FROM t1;
c1
DROP TABLE t1;
CREATE TABLE t1 (c1 CHAR AS (CONCAT (0,DAYNAME (0))));
FLUSH TABLES WITH READ LOCK;
UPDATE t1 SET c1=1;
ERROR HY000: Can't execute the query because you have a conflicting read lock
unlock tables;
UPDATE t1 SET c1=1;
SELECT * FROM t1;
c1
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;
UPDATE t1 SET c1=1;
ERROR HY000: Can't execute the query because you have a conflicting read lock
UPDATE t1 SET a=2;
ERROR HY000: Can't execute the query because you have a conflicting read lock
unlock tables;
UPDATE t1 SET a=2;
UPDATE t1 SET c1=1;
ERROR HY000: The value specified for generated column 'c1' in table 't1' has been ignored
SELECT * FROM t1;
a	c1
2	NULL
Warnings:
Warning	1292	Incorrect datetime value: '0'
DROP TABLE t1;