mirror of
https://github.com/MariaDB/server.git
synced 2025-02-13 17:05:35 +01:00
673 lines
16 KiB
Text
673 lines
16 KiB
Text
SET @@session.storage_engine = 'InnoDB';
|
|
create table t1 (a int,
|
|
b int as (-a),
|
|
c int as (-a) persistent);
|
|
set sql_warnings = 1;
|
|
#
|
|
# *** INSERT ***
|
|
#
|
|
# INSERT INTO tbl_name VALUES... DEFAULT is specified against vcols
|
|
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 vcols
|
|
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 vcols
|
|
insert ignore into t1 values (1,2,3);
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't1' ignored
|
|
Warning 1906 The value specified for generated column 'c' in table 't1' ignored
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# INSERT INTO tbl_name (<non_vcol_list>) VALUES...
|
|
insert into t1 (a) values (1), (2);
|
|
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+vcols>) VALUES... DEFAULT is specified
|
|
# against vcols
|
|
insert into t1 (a,b) values (1,default), (2,default);
|
|
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+vcols>) VALUES... NULL is specified against vcols
|
|
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+vcols>) VALUES... a non-NULL value is specified
|
|
# against vcols
|
|
insert ignore into t1 (a,b) values (1,3), (2,4);
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't1' ignored
|
|
Warning 1906 The value specified for generated column 'b' in table 't1' 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-vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
|
|
# KEY UPDATE <non_vcol>=expr, <vcol>=expr
|
|
create table t1 (a int unique,
|
|
b int as (-a),
|
|
c int as (-a) persistent);
|
|
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 vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
|
|
# KEY UPDATE <non_vcol>=expr, <vcol>=expr
|
|
create table t1 (a int,
|
|
b int as (-a),
|
|
c int as (-a) persistent 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 ignore into t2 select * from t1;
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't2' ignored
|
|
Warning 1906 The value specified for generated column 'c' in table 't2' ignored
|
|
select * from t1;
|
|
a b c
|
|
2 -2 -2
|
|
drop table t2;
|
|
# CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-vcols>, <vcols>)
|
|
# SELECT <non-vcols>, <vcols> 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 ignore into t2 (a,b) select a,b from t1;
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't2' ignored
|
|
Warning 1906 The value specified for generated column 'b' in table 't2' ignored
|
|
select * from t2;
|
|
a b c
|
|
2 -2 -2
|
|
1 -1 -1
|
|
drop table t2;
|
|
drop table t1;
|
|
#
|
|
# *** UPDATE ***
|
|
#
|
|
# UPDATE tbl_name SET non-vcol=expr WHERE non-vcol=expr
|
|
create table t1 (a int,
|
|
b int as (-a),
|
|
c int as (-a) persistent);
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where a=2;
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# UPDATE tbl_name SET vcol=expr WHERE non-vcol=expr
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
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' ignored
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# UPDATE tbl_name SET non-vcol=expr WHERE vcol=expr
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where b=-2;
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# UPDATE tbl_name SET vcol=expr WHERE vcol=expr
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
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' 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;
|
|
# INDEX created on vcol
|
|
# UPDATE tbl_name SET non-vcol=expr WHERE vcol=const
|
|
create table t1 (a int,
|
|
b int as (-a),
|
|
c int as (-a) persistent unique);
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
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 vcol
|
|
# UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where c between -3 and -2;
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
3 -3 -3
|
|
delete from t1;
|
|
select * from t1;
|
|
a b c
|
|
# No INDEX created on vcol
|
|
# UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 -1
|
|
2 -2 -2
|
|
update t1 set a=3 where b between -3 and -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 vcol
|
|
# UPDATE tbl_name SET non-vcol=expr
|
|
# WHERE vcol=between const1 and const2 ORDER BY vcol
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1;
|
|
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;
|
|
a b c
|
|
6 -6 -6
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
delete from t1 where c between -6 and 0;
|
|
select * from t1;
|
|
a b c
|
|
# INDEX created on vcol
|
|
# UPDATE tbl_name SET non-vcol=expr
|
|
# WHERE vcol=between const1 and const2 ORDER BY vcol LIMIT 2
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1;
|
|
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;
|
|
a b c
|
|
6 -6 -6
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
delete from t1 where c between -2 and 0 order by c;
|
|
select * from t1;
|
|
a b c
|
|
6 -6 -6
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
delete from t1;
|
|
# INDEX created on vcol
|
|
# UPDATE tbl_name SET non-vcol=expr
|
|
# WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1;
|
|
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;
|
|
a b c
|
|
6 -6 -6
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
delete from t1;
|
|
# INDEX created on vcol
|
|
# UPDATE tbl_name SET non-vcol=expr
|
|
# WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3
|
|
# ORDER BY indexed vcol
|
|
insert into t1 (a) values (1), (2), (3), (4), (5);
|
|
select * from t1;
|
|
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;
|
|
a b c
|
|
6 -6 -6
|
|
2 -2 -2
|
|
3 -3 -3
|
|
4 -4 -4
|
|
5 -5 -5
|
|
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 as (a % 10) persistent);
|
|
insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3');
|
|
insert into t1 (a) values (1),(2),(3);
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
select * from t2;
|
|
a name
|
|
1 value1
|
|
2 value2
|
|
3 value3
|
|
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
|
|
# - 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 vcol
|
|
# REPLACE tbl_name (non-vcols) VALUES (non-vcols);
|
|
create table t1 (a int,
|
|
b int as (-a),
|
|
c int as (-a) persistent unique,
|
|
d varchar(16));
|
|
insert into t1 (a,d) values (1,'a'), (2,'b');
|
|
select * from t1;
|
|
a b c d
|
|
1 -1 -1 a
|
|
2 -2 -2 b
|
|
replace t1 (a,d) values (1,'c');
|
|
select * from t1;
|
|
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;
|
|
#
|
|
# MDEV-9093: Persistent computed column is not updated when
|
|
# update query contains join
|
|
#
|
|
CREATE TABLE `t1` (
|
|
`id` bigint(20) NOT NULL,
|
|
`name` varchar(254) DEFAULT NULL,
|
|
`name_hash` varchar(64) AS (sha1(name)) PERSISTENT,
|
|
PRIMARY KEY (`id`)
|
|
);
|
|
insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3');
|
|
create table t2 (id bigint);
|
|
insert into t2 values (2050),(2051),(2041);
|
|
select * from t1;
|
|
id name name_hash
|
|
2041 name3 1aefcd1b0f39da45fa1fd7236f683c907c15ef82
|
|
2050 name1 9b46b0dd3a8083c070c3b9953bb5f3f95c5ab4da
|
|
2051 name2 39ea84acf1fef629fef18a9c6f5799bba32ecc25
|
|
update t1 join t2 using(id) set name = concat(name,
|
|
'+1') where t1.id in (2051,2041);
|
|
select * from t1;
|
|
id name name_hash
|
|
2041 name3+1 93c9096df48221428de46e146abc9f4f94bf7d2e
|
|
2050 name1 9b46b0dd3a8083c070c3b9953bb5f3f95c5ab4da
|
|
2051 name2+1 fd4f236320db3956a5ec073c5ec39707d7f05708
|
|
drop table t1,t2;
|
|
#
|
|
# Test error handling with virtual columns
|
|
#
|
|
CREATE TABLE IF NOT EXISTS t1 (
|
|
f1 DOUBLE,
|
|
f2 DOUBLE NOT NULL DEFAULT '0',
|
|
f3 DOUBLE,
|
|
f4 DOUBLE NOT NULL DEFAULT '0',
|
|
v1 DOUBLE AS ( ( f1 DIV ( f1 ) ) <= f2 ) VIRTUAL,
|
|
v2 DOUBLE AS ( ( f2 DIV ( f2 ) ) <= f2 ) VIRTUAL,
|
|
KEY (v2)
|
|
);
|
|
set sql_mode='strict_all_tables,error_for_division_by_zero';
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0);
|
|
ERROR 22012: Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1);
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
|
|
ERROR 22012: Division by 0
|
|
INSERT IGNORE INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1);
|
|
select v1 from t1;
|
|
v1
|
|
1
|
|
0
|
|
NULL
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2);
|
|
ERROR 22012: Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4);
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0);
|
|
ERROR 22012: Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0);
|
|
INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1;
|
|
ERROR 22012: Division by 0
|
|
select count(*) from t1;
|
|
count(*)
|
|
6
|
|
DELETE FROM t1 WHERE v2 != f1 and f1 < 5;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
select count(*) from t1;
|
|
count(*)
|
|
3
|
|
select * from t1;
|
|
f1 f2 f3 f4 v1 v2
|
|
1 1 1 1 1 1
|
|
1 0 1 1 0 NULL
|
|
6 6 0 0 1 1
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1;
|
|
ERROR 23000: Column 'f2' cannot be null
|
|
INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1 where f2 !=0;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
SELECT * FROM t1;
|
|
f1 f2 f3 f4 v1 v2
|
|
1 1 1 1 1 1
|
|
1 0 1 1 0 NULL
|
|
6 6 0 0 1 1
|
|
1 1 10 10 1 1
|
|
1 1 10 10 1 1
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
TRUNCATE TABLE t1;
|
|
set sql_mode='error_for_division_by_zero';
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0);
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1);
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1);
|
|
select v1 from t1;
|
|
v1
|
|
NULL
|
|
1
|
|
0
|
|
NULL
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2);
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4);
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0);
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0);
|
|
INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
select count(*) from t1;
|
|
count(*)
|
|
22
|
|
DELETE FROM t1 WHERE v2 != f1 and f1 < 5;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
select count(*) from t1;
|
|
count(*)
|
|
15
|
|
select * from t1;
|
|
f1 f2 f3 f4 v1 v2
|
|
0 0 0 0 NULL NULL
|
|
1 1 1 1 1 1
|
|
1 0 1 1 0 NULL
|
|
0 0 0 0 NULL NULL
|
|
5 5 5 5 1 1
|
|
1 0 0 0 0 NULL
|
|
6 6 0 0 1 1
|
|
0 0 0 0 NULL NULL
|
|
1 1 1 1 1 1
|
|
1 1 1 1 1 1
|
|
1 1 1 1 1 1
|
|
0 0 0 0 NULL NULL
|
|
5 5 5 5 1 1
|
|
0 0 0 0 NULL NULL
|
|
0 0 0 0 NULL NULL
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
Warning 1048 Column 'f2' cannot be null
|
|
Warning 1365 Division by 0
|
|
UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
Warning 1365 Division by 0
|
|
drop table t1;
|
|
set sql_mode=@@global.sql_mode;
|