mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
823 lines
21 KiB
Text
823 lines
21 KiB
Text
SET @@session.default_storage_engine = 'InnoDB';
|
|
create table t1 (a int,
|
|
b int generated always as (-a) virtual,
|
|
c int generated always as (-a) stored);
|
|
set sql_warnings = 1;
|
|
#
|
|
# *** INSERT ***
|
|
#
|
|
# INSERT INTO tbl_name VALUES... DEFAULT is specified against gcols
|
|
insert into t1 values (1,default,default);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name VALUES... NULL is specified against gcols
|
|
insert into t1 values (1,null,null);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
|
|
insert ignore into t1 values (1,2,3);
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
|
|
Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name (<non_gcol_list>) VALUES...
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name (<normal+gcols>) VALUES... DEFAULT is specified
|
|
# against gcols
|
|
insert into t1 (a,b) values (1,default), (2,default);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name (<normal+gcols>) VALUES... NULL is specified against gcols
|
|
insert into t1 (a,b) values (1,null), (2,null);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
|
|
# against gcols
|
|
insert ignore into t1 (a,b) values (1,3), (2,4);
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
|
|
Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
drop table t1;
|
|
# Table with UNIQUE non-gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
|
|
# KEY UPDATE <non_gcol>=expr, <gcol>=expr
|
|
create table t1 (a int unique,
|
|
b int generated always as (-a) virtual,
|
|
c int generated always as (-a) stored);
|
|
insert into t1 values (1,default,default);
|
|
insert into t1 values (1,default,default)
|
|
on duplicate key update a=2, b=default;
|
|
select a,b,c from t1;
|
|
a b c
|
|
2 -2 -2
|
|
delete from t1 where b in (1,2);
|
|
select * from t1;
|
|
a b c
|
|
2 -2 -2
|
|
drop table t1;
|
|
# Table with UNIQUE gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
|
|
# KEY UPDATE <non_gcol>=expr, <gcol>=expr
|
|
create table t1 (a int,
|
|
b int generated always as (-a) virtual,
|
|
c int generated always as (-a) stored unique);
|
|
insert into t1 values (1,default,default);
|
|
insert into t1 values (1,default,default)
|
|
on duplicate key update a=2, b=default;
|
|
select a,b,c from t1;
|
|
a b c
|
|
2 -2 -2
|
|
# CREATE new_table ... LIKE old_table
|
|
# INSERT INTO new_table SELECT * from old_table
|
|
create table t2 like t1;
|
|
insert into t2(a) select a from t1;
|
|
select * from t2;
|
|
a b c
|
|
2 -2 -2
|
|
drop table t2;
|
|
# CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-gcols>, <gcols>)
|
|
# SELECT <non-gcols>, <gcols> from old_table
|
|
insert into t1 values (1,default,default);
|
|
select * from t1;
|
|
a b c
|
|
2 -2 -2
|
|
1 -1 -1
|
|
create table t2 like t1;
|
|
insert into t2 (a) select a from t1;
|
|
select * from t2 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
drop table t2;
|
|
drop table t1;
|
|
#
|
|
# *** UPDATE ***
|
|
#
|
|
# UPDATE tbl_name SET non-gcol=expr WHERE non-gcol=expr
|
|
create table t1 (a int,
|
|
b int generated always as (-a) virtual,
|
|
c int generated always as (-a) stored);
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where a=2;
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# UPDATE tbl_name SET gcol=expr WHERE non-gcol=expr
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update ignore t1 set c=3 where a=2;
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# UPDATE tbl_name SET non-gcol=expr WHERE gcol=expr
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where b=-2;
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# UPDATE tbl_name SET gcol=expr WHERE gcol=expr
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update ignore t1 set c=3 where b=-2;
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
drop table t1;
|
|
# INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr WHERE gcol=const
|
|
create table t1 (a int,
|
|
b int generated always as (-a) virtual,
|
|
c int generated always as (-a) stored unique);
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where c=-2;
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where c between -3 and -2;
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# No INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where b between -3 and -2;
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr
|
|
# WHERE gcol=between const1 and const2 ORDER BY gcol
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
update t1 set a=6 where c between -1 and 0
|
|
order by c;
|
|
select * from t1 order by a;
|
|
a b c
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
6 -6 -6
|
|
delete from t1 where c between -6 and 0;
|
|
select * from t1;
|
|
a b c
|
|
# INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr
|
|
# WHERE gcol=between const1 and const2 ORDER BY gcol LIMIT 2
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
update t1 set a=6 where c between -1 and 0
|
|
order by c limit 2;
|
|
select * from t1 order by a;
|
|
a b c
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
6 -6 -6
|
|
delete from t1 where c between -2 and 0 order by c;
|
|
select * from t1 order by a;
|
|
a b c
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
6 -6 -6
|
|
delete from t1;
|
|
# INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr
|
|
# WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
update t1 set a=6 where (c between -2 and 0) and (b=-1);
|
|
select * from t1 order by a;
|
|
a b c
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
6 -6 -6
|
|
delete from t1;
|
|
# INDEX created on gcol
|
|
# UPDATE tbl_name SET non-gcol=expr
|
|
# WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
|
|
# ORDER BY indexed gcol
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
|
|
select * from t1 order by a;
|
|
a b c
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
6 -6 -6
|
|
delete from t1;
|
|
drop table t1;
|
|
#
|
|
# Verify ON UPDATE/DELETE actions of FOREIGN KEYs
|
|
create table t2 (a int primary key, name varchar(10));
|
|
create table t1 (a int primary key, b int generated always as (a % 10) stored);
|
|
insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3');
|
|
insert into t1 (a) values (1),(2),(3);
|
|
select * from t1 order by a;
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
select * from t2 order by a;
|
|
a name
|
|
1 value1
|
|
2 value2
|
|
3 value3
|
|
select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a order by t1.a;
|
|
a b name
|
|
1 1 value1
|
|
2 2 value2
|
|
3 3 value3
|
|
# - ON UPDATE RESTRICT
|
|
alter table t1 add foreign key (b) references t2(a) on update restrict;
|
|
insert into t1 (a) values (4);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`))
|
|
update t2 set a=4 where a=3;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`))
|
|
select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
|
|
a b name
|
|
1 1 value1
|
|
2 2 value2
|
|
3 3 value3
|
|
alter table t1 drop foreign key t1_ibfk_1;
|
|
# - ON DELETE RESTRICT
|
|
alter table t1 add foreign key (b) references t2(a) on delete restrict;
|
|
delete from t2 where a=3;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`))
|
|
select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
|
|
a b name
|
|
1 1 value1
|
|
2 2 value2
|
|
3 3 value3
|
|
select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
|
|
a b name
|
|
1 1 value1
|
|
2 2 value2
|
|
3 3 value3
|
|
alter table t1 drop foreign key t1_ibfk_1;
|
|
# - ON DELETE CASCADE
|
|
alter table t1 add foreign key (b) references t2(a) on delete cascade;
|
|
delete from t2 where a=3;
|
|
select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
|
|
a b name
|
|
1 1 value1
|
|
2 2 value2
|
|
select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
|
|
a b name
|
|
1 1 value1
|
|
2 2 value2
|
|
alter table t1 drop foreign key t1_ibfk_1;
|
|
drop table t1;
|
|
drop table t2;
|
|
#
|
|
# *** REPLACE ***
|
|
#
|
|
# UNIQUE INDEX on gcol
|
|
# REPLACE tbl_name (non-gcols) VALUES (non-gcols);
|
|
create table t1 (a int,
|
|
b int generated always as (-a) virtual,
|
|
c int generated always as (-a) stored unique,
|
|
d varchar(16));
|
|
insert into t1 (a,d) values (1,'a'), (2,'b');
|
|
select * from t1 order by a;
|
|
a b c d
|
|
1 -1 -1 a
|
|
2 -2 -2 b
|
|
replace t1 (a,d) values (1,'c');
|
|
select * from t1 order by a;
|
|
a b c d
|
|
1 -1 -1 c
|
|
2 -2 -2 b
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c d
|
|
set sql_warnings = 0;
|
|
drop table t1;
|
|
Bug#20170778: WL411:FAILING ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
|
|
BITMAP_IS_SET(TABLE->WR
|
|
#
|
|
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT, col5
|
|
INT GENERATED ALWAYS AS (col3 * col2) VIRTUAL, col6 INT GENERATED ALWAYS AS
|
|
(col4 * col1) STORED, col7 INT GENERATED ALWAYS AS (col6 + col6) VIRTUAL,
|
|
col8 INT GENERATED ALWAYS AS (col6 / col5) STORED, col9 TEXT);
|
|
SET @fill_amount = (@@innodb_page_size / 2 ) + 1;
|
|
INSERT INTO t1 (col1,col2,col3,col4,col5,col6,col7,col8,col9) VALUES /* 3 */
|
|
(3, 3 / 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT, DEFAULT ,REPEAT(CAST(3 AS
|
|
CHAR(1)),@fill_amount)) , (3, 3 * 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT,
|
|
DEFAULT ,REPEAT(CAST(3 AS CHAR(1)),@fill_amount));
|
|
UPDATE t1 SET col1 = 2;
|
|
UPDATE t1 SET col7 = DEFAULT;
|
|
UPDATE t1 SET col8 = DEFAULT;
|
|
DROP TABLE t1;
|
|
Bug#20797344: WL#8149: ALLOCATED SPACE FOR INDEXED BLOB VGC CAN BE
|
|
OVERWRITTEN FOR UPDATE
|
|
#
|
|
CREATE TABLE t (a varchar(100), b blob,
|
|
c blob GENERATED ALWAYS AS (concat(a,b)) VIRTUAL,
|
|
d blob GENERATED ALWAYS AS (b) VIRTUAL,
|
|
e int(11) GENERATED ALWAYS AS (10) VIRTUAL,
|
|
h int(11) NOT NULL, PRIMARY KEY (h), key(c(20)));
|
|
INSERT INTO t(a,b,h) VALUES('aaaaaaa','1111111', 11);
|
|
INSERT INTO t(a,b,h) VALUES('bbbbbbb','2222222', 22);
|
|
SELECT c FROM t;
|
|
c
|
|
aaaaaaa1111111
|
|
bbbbbbb2222222
|
|
UPDATE t SET a='ccccccc';
|
|
SELECT c FROM t;
|
|
c
|
|
ccccccc1111111
|
|
ccccccc2222222
|
|
DROP TABLE t;
|
|
# Bug#21081742: ASSERTION !TABLE || (!TABLE->WRITE_SET ||
|
|
# BITMAP_IS_SET(TABLE->WRITE_SET
|
|
#
|
|
CREATE TABLE b (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO b (col_varchar_nokey) VALUES ('v'),('v');
|
|
CREATE TABLE d (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
|
|
PRIMARY KEY (pk)
|
|
) ;
|
|
INSERT INTO d (col_varchar_nokey) VALUES ('q'),('g'),('e'),('l'),(NULL),('v'),('c'),('u'),('x');
|
|
CREATE TABLE bb (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_varchar_nokey VARCHAR(1) /*! NULL */,
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO bb (col_varchar_nokey) VALUES ('j'),('h');
|
|
EXPLAIN UPDATE
|
|
d AS OUTR1, b AS OUTR2
|
|
SET OUTR1.col_varchar_nokey = NULL
|
|
WHERE
|
|
( 't', 'b' ) IN
|
|
(
|
|
SELECT
|
|
INNR1.col_varchar_nokey AS x,
|
|
INNR1.col_varchar_key AS y
|
|
FROM bb AS INNR1
|
|
WHERE OUTR1.pk = 1
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY OUTR1 const PRIMARY PRIMARY 4 const 1
|
|
1 PRIMARY INNR1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR1)
|
|
1 PRIMARY OUTR2 index NULL PRIMARY 4 NULL 2 Using index
|
|
DROP TABLE IF EXISTS b,bb,d;
|
|
#
|
|
# Bug#21216067 ASSERTION FAILED ROW_UPD_SEC_INDEX_ENTRY (INNOBASE/ROW/ROW0UPD.CC:2103)
|
|
#
|
|
CREATE TABLE t (
|
|
x INT, y INT, gc INT GENERATED ALWAYS AS (x+1) STORED
|
|
);
|
|
INSERT INTO t VALUES ();
|
|
UPDATE t t1, t t2 SET t2.y = 1, t1.x = 2;
|
|
SELECT * FROM t;
|
|
x y gc
|
|
2 1 3
|
|
DROP TABLE t;
|
|
CREATE TABLE t (
|
|
x INT, y INT, gc INT GENERATED ALWAYS AS (x+1), KEY (x,gc)
|
|
);
|
|
INSERT INTO t VALUES ();
|
|
UPDATE t t1, t t2 SET t1.x = 1, t2.y = 2;
|
|
SELECT * FROM t;
|
|
x y gc
|
|
1 2 2
|
|
SELECT gc FROM t;
|
|
gc
|
|
2
|
|
CHECK TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
DROP TABLE t;
|
|
# stored
|
|
CREATE TABLE C (
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED
|
|
);
|
|
INSERT INTO C (col_varchar_nokey) VALUES ('c');
|
|
EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
|
|
UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
SELECT * from C;
|
|
col_varchar_nokey col_varchar_key
|
|
a aa
|
|
DROP TABLE C;
|
|
# stored, indexed
|
|
CREATE TABLE C (
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
|
|
KEY (col_varchar_key, col_varchar_nokey)
|
|
);
|
|
INSERT INTO C (col_varchar_nokey) VALUES ('c');
|
|
EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
|
|
UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
SELECT * from C;
|
|
col_varchar_nokey col_varchar_key
|
|
a aa
|
|
DROP TABLE C;
|
|
# virtual
|
|
CREATE TABLE C (
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL
|
|
);
|
|
INSERT INTO C (col_varchar_nokey) VALUES ('c');
|
|
EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
|
|
UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
SELECT * from C;
|
|
col_varchar_nokey col_varchar_key
|
|
a aa
|
|
DROP TABLE C;
|
|
# virtual, indexed
|
|
CREATE TABLE C (
|
|
col_varchar_nokey VARCHAR(1),
|
|
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
|
|
(CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
|
|
KEY (col_varchar_key, col_varchar_nokey)
|
|
);
|
|
INSERT INTO C (col_varchar_nokey) VALUES ('c');
|
|
EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
|
|
UPDATE C AS OUTR1, C AS OUTR2
|
|
SET OUTR1.`col_varchar_nokey` = 'f',
|
|
OUTR2.`col_varchar_nokey` = "a";
|
|
SELECT * from C;
|
|
col_varchar_nokey col_varchar_key
|
|
a aa
|
|
DROP TABLE C;
|
|
#
|
|
# Bug #21530366 CRASH/ASSERTION, CORRUPTION WITH INDEXES +
|
|
# VIRTUAL COLUMNS, BLOB
|
|
#
|
|
CREATE TABLE t (
|
|
a INTEGER,
|
|
b BLOB GENERATED ALWAYS AS (a) VIRTUAL,
|
|
INDEX (b(57))
|
|
);
|
|
INSERT INTO t (a) VALUES (9);
|
|
UPDATE t SET a = 10;
|
|
DELETE FROM t WHERE a = 10;
|
|
DROP TABLE t;
|
|
# Bug#21807818: Generated columns not updated with empty insert list
|
|
CREATE TABLE t (
|
|
a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
|
|
b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL,
|
|
KEY (a(183),b)
|
|
);
|
|
ERROR HY000: Function or expression '''' cannot be used in the GENERATED ALWAYS AS clause of `b`
|
|
CREATE TABLE t (
|
|
a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
|
|
b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL
|
|
);
|
|
INSERT IGNORE INTO t VALUES(), (), ();
|
|
DELETE IGNORE FROM t;
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#22195458:GCOLS: ASSERTION 0 AND CORRUPTION...
|
|
#
|
|
CREATE TABLE t (
|
|
a INT,
|
|
b YEAR GENERATED ALWAYS AS ('a') VIRTUAL,
|
|
c YEAR GENERATED ALWAYS AS ('aaaa') VIRTUAL,
|
|
b1 YEAR GENERATED ALWAYS AS ('a') STORED,
|
|
c1 YEAR GENERATED ALWAYS AS ('aaaa') STORED,
|
|
UNIQUE(b),
|
|
UNIQUE(b1)
|
|
);
|
|
INSERT IGNORE INTO t VALUES();
|
|
SELECT b from t;
|
|
b
|
|
0000
|
|
SELECT b1 from t;
|
|
b1
|
|
0000
|
|
SELECT * from t;
|
|
a b c b1 c1
|
|
NULL 0000 0000 0000 0000
|
|
DELETE FROM t;
|
|
CHECK TABLE t EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
DROP TABLE t;
|
|
# Bug#22195364:GCOLS: FAILING ASSERTION:
|
|
# DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA
|
|
CREATE TABLE t (
|
|
a INT,
|
|
c BLOB GENERATED ALWAYS AS ('') VIRTUAL,
|
|
UNIQUE KEY(c(1),a)
|
|
);
|
|
INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
|
|
SELECT * FROM t;
|
|
a c
|
|
1
|
|
INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
|
|
SELECT * FROM t;
|
|
a c
|
|
2
|
|
SELECT GROUP_CONCAT(c ORDER BY c) FROM t;
|
|
GROUP_CONCAT(c ORDER BY c)
|
|
|
|
DROP TABLE t;
|
|
#Bug#21929967:GCOLS:GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
|
|
CREATE TABLE t(c1 INT GENERATED ALWAYS AS (1) VIRTUAL,
|
|
c2 INT GENERATED ALWAYS AS(2) STORED);
|
|
INSERT INTO t VALUES(DEFAULT, DEFAULT);
|
|
SELECT * FROM t;
|
|
c1 c2
|
|
1 2
|
|
CREATE TABLE t1(c1 INT, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
|
|
INSERT INTO t1(c2) VALUES(DEFAULT);
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
NULL NULL
|
|
CREATE TABLE t2(c1 INT DEFAULT 1, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
|
|
INSERT INTO t2(c2) VALUES(DEFAULT);
|
|
SELECT * FROM t2;
|
|
c1 c2
|
|
1 2
|
|
DROP TABLE t, t1, t2;
|
|
# Bug#22179637: INSERT INTO TABLE FROM SELECT ACCEPTS TO INSERT INTO
|
|
# GENERATED COLUMNS
|
|
CREATE TABLE t1 (
|
|
i1 INTEGER,
|
|
i2 INTEGER GENERATED ALWAYS AS (i1 + i1)
|
|
);
|
|
INSERT INTO t1 (i1) SELECT 5;
|
|
INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
|
|
SELECT * FROM t1;
|
|
i1 i2
|
|
5 10
|
|
5 10
|
|
CREATE TABLE t2 (
|
|
i1 INTEGER,
|
|
i2 INTEGER GENERATED ALWAYS AS (i1 + i1) STORED
|
|
);
|
|
INSERT INTO t2 (i1) SELECT 5;
|
|
INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
|
|
SELECT * FROM t2;
|
|
i1 i2
|
|
5 10
|
|
5 10
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Bug#22070021 GCOL:ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
|
|
# BITMAP_IS_SET(TABLE->WRITE_SET,
|
|
#
|
|
CREATE TABLE t1(
|
|
c1 INT,
|
|
c2 INT GENERATED ALWAYS AS (c1 + c1) VIRTUAL,
|
|
KEY(c2)
|
|
);
|
|
INSERT INTO t1(c1) VALUES(0);
|
|
DELETE O1.* FROM t1 AS O1, t1 AS O2;
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#21944199 SIMPLE DELETE QUERY CAUSES INNODB: FAILING ASSERTION: 0
|
|
# & DATA CORRUPTION
|
|
#
|
|
CREATE TEMPORARY TABLE t1 (
|
|
a INTEGER NOT NULL,
|
|
b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
|
|
);
|
|
INSERT INTO t1 (a) VALUES (0), (0), (0);
|
|
ALTER TABLE t1 ADD INDEX idx (b);
|
|
DELETE FROM t1;
|
|
DROP TEMPORARY TABLE t1;
|
|
#
|
|
# Original test case from MDEV-17890
|
|
#
|
|
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-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols
|
|
#
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
f ENUM('a','b','c'),
|
|
v ENUM('a','b','c') AS (f),
|
|
KEY(v,id)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 (f) VALUES ('a'),('b');
|
|
INSERT IGNORE INTO t1 SELECT * FROM t1;
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
|
|
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
f ENUM('a','b','c'),
|
|
v ENUM('a','b','c') AS (f),
|
|
KEY(v,id)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 (f) VALUES ('a'),('b');
|
|
INSERT IGNORE INTO t1 SELECT * FROM t1;
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
|
|
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-23597 Assertion `marked_for_read()' failed while evaluating DEFAULT
|
|
#
|
|
CREATE TABLE t1 (a INT UNIQUE, b INT DEFAULT (c+1), c int);
|
|
INSERT INTO t1 VALUES (1,1,1);
|
|
UPDATE t1 SET b=DEFAULT;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 2 1
|
|
REPLACE t1 VALUES(1,1,1);
|
|
INSERT INTO t1 VALUES (1,1,1) ON DUPLICATE KEY UPDATE b= DEFAULT;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 2 1
|
|
REPLACE t1 VALUES(1,1,1);
|
|
CREATE TABLE t2 (a INT, b INT DEFAULT (c+1), c int);
|
|
INSERT INTO t2 VALUES (5,5,5);
|
|
UPDATE t1 join t2 set t1.b= DEFAULT, t2.b= DEFAULT;
|
|
SELECT * from t1, t2;
|
|
a b c a b c
|
|
1 2 1 5 6 5
|
|
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;
|