mirror of
https://github.com/MariaDB/server.git
synced 2025-02-05 05:12:17 +01:00
784 lines
26 KiB
Text
784 lines
26 KiB
Text
# 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 5,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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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 gafld ALL NULL NULL NULL NULL 1 Using 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 gafld ALL NULL NULL NULL NULL 1 Using 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;
|