mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
1c2a83179d
(Review input addressed)
After this patch, the optimizer can handle virtual column expressions
in WHERE/ON clauses. If the table has an indexed virtual column:
ALTER TABLE t1
ADD COLUMN vcol INT AS (col1+1),
ADD INDEX idx1(vcol);
and the query uses the exact virtual column expression:
SELECT * FROM t1 WHERE col1+1 <= 100
then the optimizer will be able use index idx1 for it.
This is achieved by walking the WHERE/ON clauses and replacing instances
of virtual column expression (like "col1+1" above) with virtual column's
Item_field (like "vcol"). The latter can be processed by the optimizer.
Replacement is considered (and done) only in items that are potentially
usable to the range optimizer.
895 lines
28 KiB
Text
895 lines
28 KiB
Text
SET @@session.default_storage_engine = 'MyISAM';
|
|
# - UNIQUE KEY
|
|
# - INDEX
|
|
# - FULLTEXT INDEX
|
|
# - SPATIAL INDEX (not supported)
|
|
# - FOREIGN INDEX (partially supported)
|
|
# - CHECK (allowed but not used)
|
|
# UNIQUE
|
|
create table t1 (a int, b int generated always as (a*2) virtual unique);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
|
|
UNIQUE KEY `b` (`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES UNI NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored unique);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
|
|
UNIQUE KEY `b` (`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES UNI NULL STORED GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) virtual, unique key (b));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
|
|
UNIQUE KEY `b` (`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES UNI NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored, unique (b));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
|
|
UNIQUE KEY `b` (`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES UNI NULL STORED GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) virtual);
|
|
alter table t1 add unique key (b);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored);
|
|
alter table t1 add unique key (b);
|
|
drop table t1;
|
|
# Testing data manipulation operations involving UNIQUE keys
|
|
# on generated columns can be found in:
|
|
# - gcol_ins_upd.inc
|
|
# - gcol_select.inc
|
|
#
|
|
# INDEX
|
|
create table t1 (a int, b int generated always as (a*2) virtual, index (b));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
|
|
KEY `b` (`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES MUL NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) virtual, index (a,b));
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored, index (b));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
|
|
KEY `b` (`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES MUL NULL STORED GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored, index (a,b));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
|
|
KEY `a` (`a`,`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES MUL NULL
|
|
b int(11) YES NULL STORED GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) virtual);
|
|
alter table t1 add index (b);
|
|
alter table t1 add index (a,b);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored);
|
|
alter table t1 add index (b);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a*2) stored);
|
|
alter table t1 add index (a,b);
|
|
create table t2 like t1;
|
|
drop table t2;
|
|
drop table t1;
|
|
# Testing data manipulation operations involving INDEX
|
|
# on generated columns can be found in:
|
|
# - gcol_select.inc
|
|
#
|
|
# TODO: FULLTEXT INDEX
|
|
# SPATIAL INDEX
|
|
# Error "All parts of a SPATIAL index must be geometrical"
|
|
create table t1 (a int, b int generated always as (a+1) stored, spatial index (b));
|
|
ERROR HY000: Incorrect arguments to SPATIAL INDEX
|
|
create table t1 (a int, b int generated always as (a+1) stored);
|
|
alter table t1 add spatial index (b);
|
|
ERROR HY000: Incorrect arguments to SPATIAL INDEX
|
|
drop table t1;
|
|
# FOREIGN KEY
|
|
# Rejected FK options.
|
|
create table t1 (a int, b int generated always as (a+1) stored,
|
|
foreign key (b) references t2(a) on update set null);
|
|
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
|
|
create table t1 (a int, b int generated always as (a+1) stored,
|
|
foreign key (b) references t2(a) on update cascade);
|
|
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
|
|
create table t1 (a int, b int generated always as (a+1) stored,
|
|
foreign key (b) references t2(a) on delete set null);
|
|
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
|
|
create table t1 (a int, b int generated always as (a+1) stored);
|
|
alter table t1 add foreign key (b) references t2(a) on update set null;
|
|
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
|
|
alter table t1 add foreign key (b) references t2(a) on update cascade;
|
|
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
|
|
alter table t1 add foreign key (b) references t2(a) on delete set null;
|
|
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
|
|
drop table t1;
|
|
# Allowed FK options.
|
|
create table t2 (a int primary key, b char(5));
|
|
create table t1 (a int, b int generated always as (a % 10) stored,
|
|
foreign key (b) references t2(a) on update restrict);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 10) stored,
|
|
foreign key (b) references t2(a) on update no action);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 10) stored,
|
|
foreign key (b) references t2(a) on delete restrict);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 10) stored,
|
|
foreign key (b) references t2(a) on delete cascade);
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 10) stored,
|
|
foreign key (b) references t2(a) on delete no action);
|
|
drop table t1,t2;
|
|
#
|
|
# Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED
|
|
#
|
|
CREATE TABLE c (
|
|
pk integer AUTO_INCREMENT,
|
|
col_datetime_nokey DATETIME /*! NULL */,
|
|
col_time_nokey TIME /*! NULL */,
|
|
col_datetime_key DATETIME GENERATED ALWAYS AS
|
|
(ADDTIME(col_datetime_nokey, col_time_nokey)),
|
|
col_time_key TIME GENERATED ALWAYS AS
|
|
(ADDTIME(col_datetime_nokey, col_time_nokey)),
|
|
col_varchar_nokey VARCHAR(1) /*! NULL */,
|
|
PRIMARY KEY (pk),
|
|
KEY (col_time_key),
|
|
KEY (col_datetime_key));
|
|
INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values
|
|
('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'),
|
|
('01:46:09.016386','2007-10-09 19:53:04.008332', NULL),
|
|
('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'),
|
|
('18:56:33.027423','2003-04-01 00:00:00', 'i');
|
|
insert into c (col_time_nokey,col_datetime_nokey,col_varchar_nokey) select '10:10:10', '2021-12-24 01:50:27', 'z' from seq_1_to_10;
|
|
EXPLAIN SELECT
|
|
outr.col_time_key AS x
|
|
FROM c as outr
|
|
WHERE
|
|
outr.col_varchar_nokey in ('c', 'x', 'i')
|
|
AND (outr.col_time_key IS NULL OR
|
|
outr.col_datetime_key = '2009-09-27');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE outr index_merge col_time_key,col_datetime_key col_time_key,col_datetime_key 4,6 NULL x x
|
|
SELECT
|
|
outr.col_time_key AS x
|
|
FROM c AS outr
|
|
WHERE
|
|
outr.col_varchar_nokey in ('c', 'x', 'i')
|
|
AND (outr.col_time_key IS NULL OR
|
|
outr.col_datetime_key = '2009-09-27');
|
|
x
|
|
DROP TABLE c;
|
|
#
|
|
# Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP |
|
|
# INNOBASE/INCLUDE/DATA0DATA.IC:253
|
|
#
|
|
CREATE TABLE A (
|
|
col_varchar_nokey TEXT ,
|
|
col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
|
|
KEY (col_varchar_key(50))
|
|
);
|
|
INSERT INTO A (col_varchar_nokey) VALUES ('');
|
|
CREATE TABLE D (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_date_nokey BLOB,
|
|
col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL,
|
|
col_datetime_nokey LONGBLOB,
|
|
col_time_nokey LONGTEXT,
|
|
col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
|
|
col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
|
|
col_varchar_nokey TEXT,
|
|
col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key(50)),
|
|
KEY (col_date_key(20)),
|
|
KEY (col_time_key(20)),
|
|
KEY (col_datetime_key(20)),
|
|
KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5))
|
|
);
|
|
INSERT INTO D (
|
|
col_date_nokey,
|
|
col_time_nokey,
|
|
col_datetime_nokey,
|
|
col_varchar_nokey
|
|
) VALUES ('', '', '', ''),('', '', '', '');
|
|
DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON
|
|
( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` );
|
|
DROP TABLE IF EXISTS A,D;
|
|
#
|
|
# Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL |
|
|
# INNOBASE/HANDLER/HANDLER0ALTER.CC
|
|
#
|
|
CREATE TABLE t1 (
|
|
col1 int(11) DEFAULT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) NOT NULL,
|
|
col4 int(11) DEFAULT NULL,
|
|
col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL,
|
|
col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
|
|
col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
|
|
col9 text,
|
|
col6 int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`col3`),
|
|
UNIQUE KEY uidx (`col2`),
|
|
KEY idx (`col5`)
|
|
);
|
|
INSERT INTO t1(col1,col2,col3,col4,col9,col6)
|
|
VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra INT;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#21316860: WL8149:INNODB: FAILING ASSERTION:
|
|
# TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk int(11) NOT NULL,
|
|
col_int_nokey int(11),
|
|
col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
|
|
col_date_nokey date,
|
|
col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL,
|
|
PRIMARY KEY (pk),
|
|
UNIQUE KEY col_int_key (col_int_key)
|
|
);
|
|
ALTER TABLE t1 DROP COLUMN pk;
|
|
DROP TABLE t1;
|
|
# Remove the impact on PK choose by index on virtual generated column
|
|
CREATE TABLE t1 (
|
|
pk int(11) NOT NULL,
|
|
col_int_nokey int(11) DEFAULT NULL,
|
|
col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
|
|
UNIQUE KEY col_int_key (col_int_key)
|
|
);
|
|
ALTER TABLE t1 add unique index idx(pk);
|
|
DESC t1;
|
|
Field Type Null Key Default Extra
|
|
pk int(11) NO PRI NULL
|
|
col_int_nokey int(11) YES NULL
|
|
col_int_key int(11) YES UNI NULL VIRTUAL GENERATED
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
|
|
#
|
|
CREATE TABLE t1 (
|
|
id INTEGER NOT NULL,
|
|
b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL,
|
|
UNIQUE KEY (b)
|
|
);
|
|
INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);
|
|
EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL b 5 NULL 9 Using index
|
|
SELECT b FROM t1 FORCE INDEX(b);
|
|
b
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
11
|
|
EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range b b 5 NULL 3 Using where; Using index
|
|
SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
|
|
b
|
|
3
|
|
4
|
|
5
|
|
DROP TABLE t1;
|
|
|
|
# Testing data manipulation operations involving FOREIGN KEY
|
|
# on generated columns can be found in:
|
|
# - gcol_ins_upd.inc
|
|
# - gcol_select.inc
|
|
#
|
|
# TODO: CHECK
|
|
#
|
|
# Test how optimizer picks indexes defined on a GC
|
|
#
|
|
CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc));
|
|
INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
# Should use index
|
|
SELECT * FROM t1 WHERE f1 + 1 > 7;
|
|
f1 gc
|
|
7 8
|
|
8 9
|
|
9 10
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range gc gc 5 NULL 3 Using index condition
|
|
SELECT * FROM t1 WHERE f1 + 1 = 7;
|
|
f1 gc
|
|
6 7
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const gc gc 5 const 1
|
|
SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
|
|
f1 gc
|
|
4 5
|
|
6 7
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range gc gc 5 NULL 2 Using index condition
|
|
SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
|
|
f1 gc
|
|
4 5
|
|
5 6
|
|
6 7
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range gc gc 5 NULL 3 Using index condition
|
|
# Check that expression isn't transformed for a disabled key
|
|
SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
|
|
f1 gc
|
|
4 5
|
|
5 6
|
|
6 7
|
|
EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
# Check that ORDER BY could be optimized
|
|
SELECT * FROM t1 ORDER BY f1 + 1;
|
|
f1 gc
|
|
0 1
|
|
1 2
|
|
2 3
|
|
3 4
|
|
4 5
|
|
5 6
|
|
6 7
|
|
7 8
|
|
8 9
|
|
9 10
|
|
EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort
|
|
EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) ORDER BY f1 + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort
|
|
# Check that GROUP BY could be optimized
|
|
SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
|
|
f1 + 1 MAX(GC)
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
5 5
|
|
6 6
|
|
7 7
|
|
8 8
|
|
9 9
|
|
10 10
|
|
EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
|
|
EXPLAIN SELECT f1 + 1, MAX(GC)
|
|
FROM t1 IGNORE KEY (gc) GROUP BY f1 + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
|
|
# Shouldn't use index
|
|
SELECT * FROM t1 WHERE f1 + 1 > 7.0;
|
|
f1 gc
|
|
7 8
|
|
8 9
|
|
9 10
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range gc gc 5 NULL 3 Using index condition
|
|
DROP TABLE t1;
|
|
# Pick index with proper type
|
|
CREATE TABLE t1 (f1 int,
|
|
gc_int int AS (f1 + 1) STORED,
|
|
gc_date DATE AS (f1 + 1) STORED,
|
|
KEY gc_int_idx(gc_int),
|
|
KEY gc_date_idx(gc_date));
|
|
INSERT INTO t1(f1) VALUES
|
|
(030303),(040404),
|
|
(050505),(060606),
|
|
(010101),(020202),
|
|
(030303),(040404),
|
|
(050505),(060606),
|
|
(010101),(020202),
|
|
(090909),(101010),
|
|
(010101),(020202),
|
|
(070707),(080808);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT * FROM t1 WHERE f1 + 1 > 070707;
|
|
f1 gc_int gc_date
|
|
101010 101011 2010-10-11
|
|
70707 70708 2007-07-08
|
|
80808 80809 2008-08-09
|
|
90909 90910 2009-09-10
|
|
# INT column & index should be picked
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range gc_int_idx gc_int_idx 5 NULL 4 Using index condition
|
|
SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
|
|
f1 gc_int gc_date
|
|
101010 101011 2010-10-11
|
|
70707 70708 2007-07-08
|
|
80808 80809 2008-08-09
|
|
90909 90910 2009-09-10
|
|
# DATE column & index should be picked
|
|
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL gc_int_idx NULL NULL NULL 18 Using where
|
|
Warnings:
|
|
Note 1105 Cannot use key `gc_int_idx` part[0] for lookup: `test`.`t1`.`gc_int` of type `int` > "cast(70707 as date)" of type `date`
|
|
DROP TABLE t1;
|
|
#
|
|
# BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk int primary key auto_increment,
|
|
col_int_key INTEGER ,
|
|
col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
|
|
KEY col_int_gc_key(col_int_gc_key)
|
|
);
|
|
INSERT INTO t1 ( col_int_key) VALUES (7);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
|
|
FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
|
|
ORDER BY field1, field2;
|
|
field1 field2
|
|
8 7
|
|
EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
|
|
FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
|
|
ORDER BY field1, field2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE table1 system PRIMARY NULL NULL NULL 1
|
|
1 SIMPLE table2 system PRIMARY NULL NULL NULL 1
|
|
SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
|
|
FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
|
|
GROUP BY field1, field2;
|
|
field1 field2
|
|
8 7
|
|
EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
|
|
FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
|
|
GROUP BY field1, field2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE table1 system PRIMARY NULL NULL NULL 1
|
|
1 SIMPLE table2 system PRIMARY NULL NULL NULL 1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX
|
|
# ON VIRTUAL COLUMN
|
|
#
|
|
CREATE TABLE t1 (
|
|
col1 INTEGER NOT NULL,
|
|
col2 INTEGER NOT NULL,
|
|
gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
|
|
col3 INTEGER NOT NULL,
|
|
col4 INTEGER NOT 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,
|
|
col34 INTEGER DEFAULT NULL,
|
|
col35 INTEGER DEFAULT NULL,
|
|
col36 INTEGER DEFAULT NULL,
|
|
col37 INTEGER DEFAULT NULL,
|
|
col38 INTEGER DEFAULT NULL,
|
|
col39 INTEGER DEFAULT NULL,
|
|
col40 INTEGER DEFAULT NULL,
|
|
col41 INTEGER DEFAULT NULL,
|
|
col42 INTEGER DEFAULT NULL,
|
|
col43 INTEGER DEFAULT NULL,
|
|
col44 INTEGER DEFAULT NULL,
|
|
col45 INTEGER DEFAULT NULL,
|
|
col46 INTEGER DEFAULT NULL,
|
|
col47 INTEGER DEFAULT NULL,
|
|
col48 INTEGER DEFAULT NULL,
|
|
col49 INTEGER DEFAULT NULL,
|
|
col50 INTEGER DEFAULT NULL,
|
|
col51 INTEGER DEFAULT NULL,
|
|
col52 INTEGER DEFAULT NULL,
|
|
col53 INTEGER DEFAULT NULL,
|
|
col54 INTEGER DEFAULT NULL,
|
|
col55 INTEGER DEFAULT NULL,
|
|
col56 INTEGER DEFAULT NULL,
|
|
col57 INTEGER DEFAULT NULL,
|
|
col58 INTEGER DEFAULT NULL,
|
|
col59 INTEGER DEFAULT NULL,
|
|
col60 INTEGER DEFAULT NULL,
|
|
col61 INTEGER DEFAULT NULL,
|
|
col62 INTEGER DEFAULT NULL,
|
|
col63 INTEGER DEFAULT NULL,
|
|
col64 INTEGER DEFAULT NULL,
|
|
col65 INTEGER DEFAULT NULL,
|
|
gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
|
|
KEY idx1 (gcol1)
|
|
);
|
|
INSERT INTO t1 (col1, col2, col3, col4)
|
|
VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
|
|
ALTER TABLE t1 ADD COLUMN extra INTEGER;
|
|
SELECT gcol1 FROM t1 FORCE INDEX(idx1);
|
|
gcol1
|
|
2
|
|
4
|
|
6
|
|
8
|
|
10
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
col1 INTEGER NOT NULL,
|
|
col2 INTEGER NOT NULL,
|
|
gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
|
|
col3 INTEGER NOT NULL,
|
|
col4 INTEGER NOT 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,
|
|
col34 INTEGER DEFAULT NULL,
|
|
col35 INTEGER DEFAULT NULL,
|
|
col36 INTEGER DEFAULT NULL,
|
|
col37 INTEGER DEFAULT NULL,
|
|
col38 INTEGER DEFAULT NULL,
|
|
col39 INTEGER DEFAULT NULL,
|
|
col40 INTEGER DEFAULT NULL,
|
|
col41 INTEGER DEFAULT NULL,
|
|
col42 INTEGER DEFAULT NULL,
|
|
col43 INTEGER DEFAULT NULL,
|
|
col44 INTEGER DEFAULT NULL,
|
|
col45 INTEGER DEFAULT NULL,
|
|
col46 INTEGER DEFAULT NULL,
|
|
col47 INTEGER DEFAULT NULL,
|
|
col48 INTEGER DEFAULT NULL,
|
|
col49 INTEGER DEFAULT NULL,
|
|
col50 INTEGER DEFAULT NULL,
|
|
col51 INTEGER DEFAULT NULL,
|
|
col52 INTEGER DEFAULT NULL,
|
|
col53 INTEGER DEFAULT NULL,
|
|
col54 INTEGER DEFAULT NULL,
|
|
col55 INTEGER DEFAULT NULL,
|
|
col56 INTEGER DEFAULT NULL,
|
|
col57 INTEGER DEFAULT NULL,
|
|
col58 INTEGER DEFAULT NULL,
|
|
col59 INTEGER DEFAULT NULL,
|
|
col60 INTEGER DEFAULT NULL,
|
|
col61 INTEGER DEFAULT NULL,
|
|
col62 INTEGER DEFAULT NULL,
|
|
col63 INTEGER DEFAULT NULL,
|
|
col64 INTEGER DEFAULT NULL,
|
|
col65 INTEGER DEFAULT NULL,
|
|
gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
|
|
KEY idx1 (gcol2)
|
|
);
|
|
INSERT INTO t1 (col1, col2, col3, col4)
|
|
VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
|
|
ALTER TABLE t1 ADD COLUMN extra INTEGER;
|
|
SELECT gcol2 FROM t1 FORCE INDEX(idx1);
|
|
gcol2
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
|
|
#
|
|
CREATE TABLE t (a INT,
|
|
b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL,
|
|
c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL);
|
|
INSERT INTO t(a) VALUES (1);
|
|
SELECT * FROM t WHERE c = '0';
|
|
a b c
|
|
1 127 0
|
|
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
|
|
ALTER TABLE t ADD UNIQUE INDEX (c(1));
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'b' at row 1
|
|
SELECT * FROM t WHERE c = '0';
|
|
a b c
|
|
1 127 0
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD()
|
|
# DID NOT RETURN TRUE WITH DIVIDE 0
|
|
#
|
|
CREATE TABLE t (a INT, b INT, h VARCHAR(10));
|
|
INSERT INTO t VALUES (12, 3, "ss");
|
|
INSERT INTO t VALUES (13, 4, "ss");
|
|
INSERT INTO t VALUES (14, 0, "ss");
|
|
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
|
|
ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
|
|
CREATE INDEX idx ON t(c);
|
|
ERROR 22012: Division by 0
|
|
CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed");
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
|
|
# WITH LOGICAL OPERATORS
|
|
#
|
|
CREATE TABLE t (a INT, b INT,
|
|
gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
|
|
gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
|
|
gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
|
|
gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
|
|
gc_case INT GENERATED ALWAYS AS
|
|
(CASE WHEN (a AND b) THEN a ELSE b END) STORED,
|
|
INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
|
|
INDEX(gc_case));
|
|
INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
|
|
ANALYZE TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t analyze status Engine-independent statistics collected
|
|
test.t analyze status OK
|
|
EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE (a AND b) = 1;
|
|
a b
|
|
1 1
|
|
EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE 1 = (a AND b);
|
|
a b
|
|
1 1
|
|
EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
|
|
a b
|
|
1 1
|
|
EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE (a OR b) = 1;
|
|
a b
|
|
0 1
|
|
1 0
|
|
1 1
|
|
EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
|
|
a b
|
|
0 1
|
|
1 0
|
|
1 1
|
|
EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ref gc_xor gc_xor 5 const 2
|
|
SELECT a, b FROM t WHERE (a XOR b) = 1;
|
|
a b
|
|
0 1
|
|
1 0
|
|
EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ref gc_not gc_not 5 const 2
|
|
SELECT a FROM t WHERE (NOT a) = 1;
|
|
a
|
|
0
|
|
0
|
|
EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
|
|
a
|
|
0
|
|
1
|
|
EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE 1 = (b AND a);
|
|
a b
|
|
1 1
|
|
EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
|
|
SELECT a, b FROM t WHERE 1 = (b OR a);
|
|
a b
|
|
0 1
|
|
1 0
|
|
1 1
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#22810883: ASSERTION FAILED:
|
|
# !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
|
|
#
|
|
CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
|
|
a2 INTEGER, KEY (a1));
|
|
INSERT INTO t1 VALUES ();
|
|
CREATE TABLE t2 (b INTEGER);
|
|
INSERT INTO t2 VALUES (1);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
# Used to choose the index on a1 and get wrong results.
|
|
EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
SELECT * FROM t1 WHERE (a2 AND a2) = 0;
|
|
a1 a2
|
|
# Used to get assertion or wrong results.
|
|
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 system NULL NULL NULL NULL 1
|
|
1 SIMPLE t2 system NULL NULL NULL NULL 1
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
|
|
a1 a2 b
|
|
0 NULL 1
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# MDEV-20618 Assertion `btr_validate_index(index, 0, false)' failed
|
|
# in row_upd_sec_index_entry
|
|
#
|
|
CREATE TABLE t1 (A BIT(15), VA BIT(10) GENERATED ALWAYS AS (A),PK INT,
|
|
PRIMARY KEY (PK), UNIQUE KEY (VA));
|
|
INSERT IGNORE INTO t1 VALUES ( '\r1','a',1);
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'VA' in table 't1' has been ignored
|
|
Warning 1264 Out of range value for column 'VA' at row 1
|
|
REPLACE INTO t1 (PK) VALUES (1);
|
|
ERROR 22001: Data too long for column 'VA' at row 1
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-17890 Record in index was not found on update, server crash in
|
|
# row_upd_build_difference_binary or
|
|
# Assertion `0' failed in row_upd_sec_index_entry
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk BIGINT AUTO_INCREMENT,
|
|
b BIT(15),
|
|
v BIT(10) AS (b) VIRTUAL,
|
|
PRIMARY KEY(pk),
|
|
UNIQUE(v)
|
|
);
|
|
INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101');
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'v' at row 1
|
|
SELECT pk, b INTO OUTFILE 'load.data' FROM t1;
|
|
LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b);
|
|
ERROR 22001: Data too long for column 'v' at row 1
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-17834 Server crashes in row_upd_build_difference_binary
|
|
# on LOAD DATA into table with indexed virtual column
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INT,
|
|
i TINYINT,
|
|
ts TIMESTAMP NULL,
|
|
vi TINYINT AS (i+1) PERSISTENT,
|
|
vts TIMESTAMP(5) AS (ts) VIRTUAL,
|
|
PRIMARY KEY(pk),
|
|
UNIQUE(vts)
|
|
);
|
|
INSERT IGNORE INTO t1 (pk,i) VALUES (1,127);
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'vi' at row 1
|
|
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/load.data' REPLACE INTO TABLE t1 (pk,i,ts);
|
|
ERROR 22003: Out of range value for column 'vi' at row 1
|
|
DROP TABLE t1;
|
|
# MDEV-19011 Assertion `file->s->base.reclength < file->s->vreclength'
|
|
# failed in ha_myisam::setup_vcols_for_repair
|
|
CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL);
|
|
ALTER TABLE t1 ADD KEY (a);
|
|
DROP TABLE t1;
|
|
DROP VIEW IF EXISTS v1,v2;
|
|
DROP TABLE IF EXISTS t1,t2,t3;
|
|
DROP PROCEDURE IF EXISTS p1;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
DROP TRIGGER IF EXISTS trg1;
|
|
DROP TRIGGER IF EXISTS trg2;
|
|
set sql_warnings = 0;
|