summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/gcol/r/gcol_keys_innodb.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/gcol/r/gcol_keys_innodb.result')
-rw-r--r--mysql-test/suite/gcol/r/gcol_keys_innodb.result956
1 files changed, 956 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result
new file mode 100644
index 00000000..0228f9be
--- /dev/null
+++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result
@@ -0,0 +1,956 @@
+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 0
+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;
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-02 20:38:11 +0000