mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
956 lines
30 KiB
Text
956 lines
30 KiB
Text
SET @@session.default_storage_engine = 'InnoDB';
|
|
# - 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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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
|
|
# 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;
|
|
create table t1 (a int, b int generated always as (a+1) virtual,
|
|
foreign key (b) references t2(a));
|
|
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
create table t1 (a int, b int generated always as (a+1) virtual);
|
|
alter table t1 add foreign key (b) references t2(a);
|
|
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
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');
|
|
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
|
|
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 ALL NULL NULL NULL NULL 10 Using where
|
|
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 ALL NULL NULL NULL NULL 10 Using where
|
|
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 ALL NULL NULL NULL NULL 10 Using where
|
|
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 ALL NULL NULL NULL NULL 10 Using where
|
|
# 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 ALL NULL NULL NULL NULL 10 Using where
|
|
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 ALL NULL NULL NULL NULL 18 Using where
|
|
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 NULL NULL NULL NULL 18 Using where
|
|
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 ALL PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
|
|
1 SIMPLE table2 eq_ref PRIMARY PRIMARY 4 test.table1.pk 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 ALL PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
|
|
1 SIMPLE table2 eq_ref PRIMARY PRIMARY 4 test.table1.pk 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 ALL NULL NULL NULL NULL 4 Using where
|
|
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 ALL NULL NULL NULL NULL 4 Using where
|
|
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 t1 ALL NULL NULL NULL NULL 1 Using where
|
|
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 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
|
|
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;
|
|
#
|
|
# BUG#21365158 WL8149:ASSERTION `!TABLE || (!TABLE->WRITE_SET
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_int_nokey INTEGER NOT NULL,
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t1 ( col_int_nokey, col_varchar_nokey)
|
|
VALUES (4, 'b'),(9, 'o'),(4, 'k'),(5, 'a'),(5, 'f'),
|
|
(9, 't'),(3, 'c'),(8, 'c'),(0, 'r'),(98, 'k');
|
|
CREATE TABLE t2 (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_int_nokey INTEGER NOT NULL,
|
|
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),
|
|
UNIQUE KEY (col_varchar_key)
|
|
);
|
|
INSERT INTO t2 ( col_int_nokey, col_varchar_nokey)
|
|
VALUES (1, 'c'),(8, 'm'),(9, 'd'), (6, 'y'),(1, 't'),
|
|
(2, 's'),(4, 'r');
|
|
SELECT
|
|
CONCAT( t2.col_varchar_nokey , t2.col_varchar_nokey ) AS f2,
|
|
t1.col_varchar_key AS f5
|
|
FROM
|
|
t2 LEFT JOIN t1 ON t2.col_int_nokey > t1.col_int_nokey
|
|
ORDER BY f2, f5;
|
|
f2 f5
|
|
cc rr
|
|
dd aa
|
|
dd bb
|
|
dd cc
|
|
dd cc
|
|
dd ff
|
|
dd kk
|
|
dd rr
|
|
mm aa
|
|
mm bb
|
|
mm cc
|
|
mm ff
|
|
mm kk
|
|
mm rr
|
|
rr cc
|
|
rr rr
|
|
ss rr
|
|
tt rr
|
|
yy aa
|
|
yy bb
|
|
yy cc
|
|
yy ff
|
|
yy kk
|
|
yy rr
|
|
DROP TABLE t1,t2;
|
|
#
|
|
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;
|