You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1021 lines
40 KiB
1021 lines
40 KiB
3 months ago
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
||
|
|
create table t1 (a int not null);
|
||
|
|
insert into t1 values (1);
|
||
|
|
insert into t1 values (a+2);
|
||
|
|
insert into t1 values (a+3),(a+4);
|
||
|
|
insert into t1 values (5),(a+6);
|
||
|
|
select * from t1;
|
||
|
|
a
|
||
|
|
1
|
||
|
|
2
|
||
|
|
3
|
||
|
|
4
|
||
|
|
5
|
||
|
|
6
|
||
|
|
drop table t1;
|
||
|
|
create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username));
|
||
|
|
insert into t1 values (0,"mysql");
|
||
|
|
insert into t1 values (0,"mysql ab");
|
||
|
|
insert into t1 values (0,"mysql a");
|
||
|
|
insert into t1 values (0,"r1manic");
|
||
|
|
insert into t1 values (0,"r1man");
|
||
|
|
drop table t1;
|
||
|
|
create table t1 (a int not null auto_increment, primary key (a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c char(10) default "hello", i int);
|
||
|
|
insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default);
|
||
|
|
select a,t>0,c,i from t1;
|
||
|
|
a t>0 c i
|
||
|
|
1 1 hello NULL
|
||
|
|
2 1 hello NULL
|
||
|
|
4 0 a 5
|
||
|
|
5 1 hello NULL
|
||
|
|
truncate table t1;
|
||
|
|
insert into t1 set a=default,t=default,c=default;
|
||
|
|
insert into t1 set a=default,t=default,c=default,i=default;
|
||
|
|
insert into t1 set a=4,t=0,c="a",i=5;
|
||
|
|
insert into t1 set a=5,t=0,c="a",i=null;
|
||
|
|
insert into t1 set a=default,t=default,c=default,i=default;
|
||
|
|
select a,t>0,c,i from t1;
|
||
|
|
a t>0 c i
|
||
|
|
1 1 hello NULL
|
||
|
|
2 1 hello NULL
|
||
|
|
4 0 a 5
|
||
|
|
5 0 a NULL
|
||
|
|
6 1 hello NULL
|
||
|
|
drop table t1;
|
||
|
|
create table t1 (id int NOT NULL DEFAULT 8);
|
||
|
|
insert into t1 values(NULL);
|
||
|
|
ERROR 23000: Column 'id' cannot be null
|
||
|
|
insert into t1 values (1), (NULL), (2);
|
||
|
|
Warnings:
|
||
|
|
Warning 1048 Column 'id' cannot be null
|
||
|
|
select * from t1;
|
||
|
|
id
|
||
|
|
1
|
||
|
|
0
|
||
|
|
2
|
||
|
|
drop table t1;
|
||
|
|
create table t1 (email varchar(50));
|
||
|
|
insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com');
|
||
|
|
create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2));
|
||
|
|
insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
|
||
|
|
select * from t2;
|
||
|
|
id t2
|
||
|
|
3 aol.com
|
||
|
|
2 hotmail.com
|
||
|
|
1 mysql.com
|
||
|
|
drop table t1,t2;
|
||
|
|
create database mysqltest;
|
||
|
|
use mysqltest;
|
||
|
|
create table t1 (c int);
|
||
|
|
insert into mysqltest.t1 set mysqltest.t1.c = '1';
|
||
|
|
drop database mysqltest;
|
||
|
|
use test;
|
||
|
|
create table t1(number int auto_increment primary key, original_value varchar(50), f_double double, f_float float, f_double_7_2 double(7,2), f_float_4_3 float (4,3), f_double_u double unsigned, f_float_u float unsigned, f_double_15_1_u double(15,1) unsigned, f_float_3_1_u float (3,1) unsigned);
|
||
|
|
Warnings:
|
||
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
||
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
||
|
|
Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
|
||
|
|
Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
|
||
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
||
|
|
Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
|
||
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
||
|
|
Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
|
||
|
|
set @value= "aa";
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'f_double' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 1
|
||
|
|
original_value aa
|
||
|
|
f_double 0
|
||
|
|
f_float 0
|
||
|
|
f_double_7_2 0.00
|
||
|
|
f_float_4_3 0.000
|
||
|
|
f_double_u 0
|
||
|
|
f_float_u 0
|
||
|
|
f_double_15_1_u 0.0
|
||
|
|
f_float_3_1_u 0.0
|
||
|
|
set @value= "1aa";
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'f_double' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 2
|
||
|
|
original_value 1aa
|
||
|
|
f_double 1
|
||
|
|
f_float 1
|
||
|
|
f_double_7_2 1.00
|
||
|
|
f_float_4_3 1.000
|
||
|
|
f_double_u 1
|
||
|
|
f_float_u 1
|
||
|
|
f_double_15_1_u 1.0
|
||
|
|
f_float_3_1_u 1.0
|
||
|
|
set @value= "aa1";
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'f_double' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 3
|
||
|
|
original_value aa1
|
||
|
|
f_double 0
|
||
|
|
f_float 0
|
||
|
|
f_double_7_2 0.00
|
||
|
|
f_float_4_3 0.000
|
||
|
|
f_double_u 0
|
||
|
|
f_float_u 0
|
||
|
|
f_double_15_1_u 0.0
|
||
|
|
f_float_3_1_u 0.0
|
||
|
|
set @value= "1e+1111111111a";
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1264 Out of range value for column 'f_double' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 4
|
||
|
|
original_value 1e+1111111111a
|
||
|
|
f_double 1.7976931348623157e308
|
||
|
|
f_float 3.40282e38
|
||
|
|
f_double_7_2 99999.99
|
||
|
|
f_float_4_3 9.999
|
||
|
|
f_double_u 1.7976931348623157e308
|
||
|
|
f_float_u 3.40282e38
|
||
|
|
f_double_15_1_u 99999999999999.9
|
||
|
|
f_float_3_1_u 99.9
|
||
|
|
set @value= "-1e+1111111111a";
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1264 Out of range value for column 'f_double' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 5
|
||
|
|
original_value -1e+1111111111a
|
||
|
|
f_double -1.7976931348623157e308
|
||
|
|
f_float -3.40282e38
|
||
|
|
f_double_7_2 -99999.99
|
||
|
|
f_float_4_3 -9.999
|
||
|
|
f_double_u 0
|
||
|
|
f_float_u 0
|
||
|
|
f_double_15_1_u 0.0
|
||
|
|
f_float_3_1_u 0.0
|
||
|
|
set @value= 1e+1111111111;
|
||
|
|
ERROR 22007: Illegal double '1e+1111111111' value found during parsing
|
||
|
|
set @value= -1e+1111111111;
|
||
|
|
ERROR 22007: Illegal double '1e+1111111111' value found during parsing
|
||
|
|
set @value= 1e+111;
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1264 Out of range value for column 'f_float' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 6
|
||
|
|
original_value 1e111
|
||
|
|
f_double 1e111
|
||
|
|
f_float 3.40282e38
|
||
|
|
f_double_7_2 99999.99
|
||
|
|
f_float_4_3 9.999
|
||
|
|
f_double_u 1e111
|
||
|
|
f_float_u 3.40282e38
|
||
|
|
f_double_15_1_u 99999999999999.9
|
||
|
|
f_float_3_1_u 99.9
|
||
|
|
set @value= -1e+111;
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1264 Out of range value for column 'f_float' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_7_2' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_4_3' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 7
|
||
|
|
original_value -1e111
|
||
|
|
f_double -1e111
|
||
|
|
f_float -3.40282e38
|
||
|
|
f_double_7_2 -99999.99
|
||
|
|
f_float_4_3 -9.999
|
||
|
|
f_double_u 0
|
||
|
|
f_float_u 0
|
||
|
|
f_double_15_1_u 0.0
|
||
|
|
f_float_3_1_u 0.0
|
||
|
|
set @value= 1;
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 8
|
||
|
|
original_value 1
|
||
|
|
f_double 1
|
||
|
|
f_float 1
|
||
|
|
f_double_7_2 1.00
|
||
|
|
f_float_4_3 1.000
|
||
|
|
f_double_u 1
|
||
|
|
f_float_u 1
|
||
|
|
f_double_15_1_u 1.0
|
||
|
|
f_float_3_1_u 1.0
|
||
|
|
set @value= -1;
|
||
|
|
insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
|
||
|
|
Warnings:
|
||
|
|
Warning 1264 Out of range value for column 'f_double_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1
|
||
|
|
Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1
|
||
|
|
select * from t1 where number =last_insert_id();
|
||
|
|
number 9
|
||
|
|
original_value -1
|
||
|
|
f_double -1
|
||
|
|
f_float -1
|
||
|
|
f_double_7_2 -1.00
|
||
|
|
f_float_4_3 -1.000
|
||
|
|
f_double_u 0
|
||
|
|
f_float_u 0
|
||
|
|
f_double_15_1_u 0.0
|
||
|
|
f_float_3_1_u 0.0
|
||
|
|
drop table t1;
|
||
|
|
create table t1(id1 int not null auto_increment primary key, t char(12));
|
||
|
|
create table t2(id2 int not null, t char(12));
|
||
|
|
create table t3(id3 int not null, t char(12), index(id3));
|
||
|
|
select count(*) from t2;
|
||
|
|
count(*)
|
||
|
|
500
|
||
|
|
insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = t3.id3;
|
||
|
|
select count(*) from t2;
|
||
|
|
count(*)
|
||
|
|
25500
|
||
|
|
drop table t1,t2,t3;
|
||
|
|
CREATE TABLE t1(a INTEGER, b INTEGER);
|
||
|
|
INSERT INTO t1 VALUES(1, 1);
|
||
|
|
INSERT INTO t1 VALUES(1);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 1
|
||
|
|
INSERT INTO t1 VALUES(1, 1, 1);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 1
|
||
|
|
INSERT INTO t1 VALUES(1, 1), (2);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 2
|
||
|
|
INSERT INTO t1 VALUES(1, 1), (2);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 2
|
||
|
|
INSERT INTO t1 VALUES(1, 1), (3, 3, 3);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 2
|
||
|
|
INSERT INTO t1(a, b) VALUES(1);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 1
|
||
|
|
INSERT INTO t1(a, b) VALUES(1, 1, 1);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 1
|
||
|
|
INSERT INTO t1(a, b) VALUES(1, 1), (2);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 2
|
||
|
|
INSERT INTO t1(a, b) VALUES(1, 1), (2);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 2
|
||
|
|
INSERT INTO t1(a, b) VALUES(1, 1), (3, 3, 3);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 2
|
||
|
|
DROP TABLE t1;
|
||
|
|
create table t1 (a int, b int);
|
||
|
|
insert into t1 (a,b) values (a,b);
|
||
|
|
insert into t1 SET a=1, b=a+1;
|
||
|
|
insert into t1 (a,b) select 1,2;
|
||
|
|
INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
|
||
|
|
prepare stmt1 from ' replace into t1 (a,a) select 100, ''hundred'' ';
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
insert into t1 (a,b) values (1,1,1);
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 1
|
||
|
|
insert into t1 (a,b,b) values (1,1,1);
|
||
|
|
ERROR 42000: Column 'b' specified twice
|
||
|
|
insert into t1 (a,a) values (1,1,1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
insert into t1 (a,a) values (1,1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
insert into t1 SET a=1,b=2,a=1;
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
insert into t1 (b,b) select 1,2;
|
||
|
|
ERROR 42000: Column 'b' specified twice
|
||
|
|
INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
|
||
|
|
ERROR 42000: Column 'b' specified twice
|
||
|
|
drop table t1;
|
||
|
|
create table t1 (id int primary key, data int);
|
||
|
|
insert into t1 values (1, 1), (2, 2), (3, 3);
|
||
|
|
select row_count();
|
||
|
|
row_count()
|
||
|
|
3
|
||
|
|
insert ignore into t1 values (1, 1);
|
||
|
|
Warnings:
|
||
|
|
Warning 1062 Duplicate entry '1' for key 'PRIMARY'
|
||
|
|
select row_count();
|
||
|
|
row_count()
|
||
|
|
-1
|
||
|
|
replace into t1 values (1, 11);
|
||
|
|
select row_count();
|
||
|
|
row_count()
|
||
|
|
2
|
||
|
|
replace into t1 values (4, 4);
|
||
|
|
select row_count();
|
||
|
|
row_count()
|
||
|
|
1
|
||
|
|
insert into t1 values (2, 2) on duplicate key update data= data + 10;
|
||
|
|
select row_count();
|
||
|
|
row_count()
|
||
|
|
2
|
||
|
|
insert into t1 values (5, 5) on duplicate key update data= data + 10;
|
||
|
|
select row_count();
|
||
|
|
row_count()
|
||
|
|
1
|
||
|
|
drop table t1;
|
||
|
|
create table t1 (f1 int unique, f2 int);
|
||
|
|
create table t2 (f3 int, f4 int);
|
||
|
|
create view v1 as select * from t1, t2 where f1= f3;
|
||
|
|
insert into t1 values (1,11), (2,22);
|
||
|
|
insert into t2 values (1,12), (2,24);
|
||
|
|
insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10;
|
||
|
|
ERROR HY000: Can not modify more than one base table through a join view 'test.v1'
|
||
|
|
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
|
||
|
|
ERROR HY000: Can not modify more than one base table through a join view 'test.v1'
|
||
|
|
select * from t1;
|
||
|
|
f1 f2
|
||
|
|
1 11
|
||
|
|
2 22
|
||
|
|
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
|
||
|
|
ERROR HY000: Can not modify more than one base table through a join view 'test.v1'
|
||
|
|
select * from t1;
|
||
|
|
f1 f2
|
||
|
|
1 11
|
||
|
|
2 22
|
||
|
|
drop view v1;
|
||
|
|
drop table t1,t2;
|
||
|
|
CREATE TABLE t1 (
|
||
|
|
a char(20) NOT NULL,
|
||
|
|
b char(7) DEFAULT NULL,
|
||
|
|
c char(4) DEFAULT NULL
|
||
|
|
);
|
||
|
|
INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);
|
||
|
|
INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05);
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'c' at row 1
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01);
|
||
|
|
INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15);
|
||
|
|
INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);
|
||
|
|
INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3);
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'c' at row 1
|
||
|
|
INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);
|
||
|
|
INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);
|
||
|
|
SELECT * FROM t1;
|
||
|
|
a b c
|
||
|
|
9.999999 10 10
|
||
|
|
0.00001225 1.22e-5 1e-5
|
||
|
|
0.0001225 1.22e-4 NULL
|
||
|
|
0.1225 0.1225 NULL
|
||
|
|
0.1225877 0.12259 NULL
|
||
|
|
12.25 12.25 NULL
|
||
|
|
12.25 12.25 12.2
|
||
|
|
122500 122500 NULL
|
||
|
|
12250000000 1.22e10 NULL
|
||
|
|
1.225e15 1.22e15 NULL
|
||
|
|
5000000 5000000 NULL
|
||
|
|
1.25e78 1.25e78 NULL
|
||
|
|
1.25e-94 1.2e-94 NULL
|
||
|
|
1.25e203 1.2e203 NULL
|
||
|
|
1.25e-175 1e-175 NULL
|
||
|
|
1.225 NULL 1.23
|
||
|
|
1.37 NULL 1.37
|
||
|
|
-1.37 NULL -1.4
|
||
|
|
0.00187 NULL 2e-3
|
||
|
|
-0.0187 NULL 0
|
||
|
|
5000 NULL 5000
|
||
|
|
-5000 NULL -5e3
|
||
|
|
DROP TABLE t1;
|
||
|
|
CREATE TABLE t1 (
|
||
|
|
a char(20) NOT NULL,
|
||
|
|
b char(7) DEFAULT NULL,
|
||
|
|
c char(5)
|
||
|
|
);
|
||
|
|
INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);
|
||
|
|
INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01);
|
||
|
|
INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15);
|
||
|
|
INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203);
|
||
|
|
INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);
|
||
|
|
INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3);
|
||
|
|
INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);
|
||
|
|
INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);
|
||
|
|
INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);
|
||
|
|
SELECT * FROM t1;
|
||
|
|
a b c
|
||
|
|
9.999999 10 9.999
|
||
|
|
0.00001225 1.22e-5 1e-5
|
||
|
|
0.0001225 1.22e-4 NULL
|
||
|
|
0.1225 0.1225 NULL
|
||
|
|
0.1225877 0.12259 NULL
|
||
|
|
12.25 12.25 NULL
|
||
|
|
12.25 12.25 12.25
|
||
|
|
122500 122500 NULL
|
||
|
|
12250000000 1.22e10 NULL
|
||
|
|
1.225e15 1.22e15 NULL
|
||
|
|
5000000 5000000 NULL
|
||
|
|
1.25e78 1.25e78 NULL
|
||
|
|
1.25e-94 1.2e-94 NULL
|
||
|
|
1.25e203 1.2e203 NULL
|
||
|
|
1.25e-175 1e-175 NULL
|
||
|
|
1.225 NULL 1.225
|
||
|
|
1.37 NULL 1.37
|
||
|
|
-1.37 NULL -1.37
|
||
|
|
0.00187 NULL 0.002
|
||
|
|
-0.0187 NULL -0.02
|
||
|
|
5000 NULL 5000
|
||
|
|
-5000 NULL -5000
|
||
|
|
DROP TABLE t1;
|
||
|
|
SET sql_mode = default;
|
||
|
|
CREATE TABLE t (a CHAR(10),b INT);
|
||
|
|
INSERT INTO t VALUES (),(),();
|
||
|
|
INSERT INTO t(a) SELECT rand() FROM t;
|
||
|
|
DROP TABLE t;
|
||
|
|
CREATE TABLE t1 (c1 INT NOT NULL);
|
||
|
|
INSERT INTO t1 VALUES(4188.32999999999992724042385816574096679687500),
|
||
|
|
('4188.32999999999992724042385816574096679687500'), (4188);
|
||
|
|
SELECT * FROM t1;
|
||
|
|
c1
|
||
|
|
4188
|
||
|
|
4188
|
||
|
|
4188
|
||
|
|
CREATE TABLE t2 (c1 BIGINT);
|
||
|
|
INSERT INTO t2 VALUES('15449237462.0000000000');
|
||
|
|
SELECT * FROM t2;
|
||
|
|
c1
|
||
|
|
15449237462
|
||
|
|
DROP TABLE t1, t2;
|
||
|
|
CREATE TABLE t1(f1 FLOAT);
|
||
|
|
INSERT INTO t1 VALUES (1.23);
|
||
|
|
CREATE TABLE t2(f1 CHAR(1));
|
||
|
|
INSERT INTO t2 SELECT f1 FROM t1;
|
||
|
|
DROP TABLE t1, t2;
|
||
|
|
End of 5.0 tests.
|
||
|
|
#
|
||
|
|
# Bug#34898 "mysql_info() reports 0 warnings while
|
||
|
|
# mysql_warning_count() reports 1"
|
||
|
|
# Check that the number of warnings reported by
|
||
|
|
# mysql_info() is correct.
|
||
|
|
#
|
||
|
|
create table t1 (data varchar(4) not null);
|
||
|
|
#
|
||
|
|
# Demonstrate that the number of warnings matches
|
||
|
|
# the information in mysql_info().
|
||
|
|
#
|
||
|
|
prepare stmt from "insert ignore t1 (data) values ('letter'), (1/0)";
|
||
|
|
affected rows: 0
|
||
|
|
info: Statement prepared
|
||
|
|
execute stmt;
|
||
|
|
affected rows: 2
|
||
|
|
info: Records: 2 Duplicates: 0 Warnings: 3
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'data' at row 1
|
||
|
|
Warning 1365 Division by 0
|
||
|
|
Warning 1048 Column 'data' cannot be null
|
||
|
|
prepare stmt from "update ignore t1 set data='envelope' where 1/0 or 1";
|
||
|
|
affected rows: 0
|
||
|
|
info: Statement prepared
|
||
|
|
Warnings:
|
||
|
|
Warning 1365 Division by 0
|
||
|
|
execute stmt;
|
||
|
|
affected rows: 2
|
||
|
|
info: Rows matched: 2 Changed: 2 Warnings: 2
|
||
|
|
Warnings:
|
||
|
|
Warning 1265 Data truncated for column 'data' at row 1
|
||
|
|
Warning 1265 Data truncated for column 'data' at row 2
|
||
|
|
prepare stmt from "insert ignore t1 (data) values (default), (1/0), ('dead beef')";
|
||
|
|
affected rows: 0
|
||
|
|
info: Statement prepared
|
||
|
|
execute stmt;
|
||
|
|
affected rows: 3
|
||
|
|
info: Records: 3 Duplicates: 0 Warnings: 4
|
||
|
|
Warnings:
|
||
|
|
Warning 1364 Field 'data' doesn't have a default value
|
||
|
|
Warning 1365 Division by 0
|
||
|
|
Warning 1048 Column 'data' cannot be null
|
||
|
|
Warning 1265 Data truncated for column 'data' at row 3
|
||
|
|
drop table t1;
|
||
|
|
#
|
||
|
|
# End of 5.4 tests
|
||
|
|
#
|
||
|
|
#
|
||
|
|
# Bug#54106 assert in Protocol::end_statement,
|
||
|
|
# INSERT IGNORE ... SELECT ... UNION SELECT ...
|
||
|
|
#
|
||
|
|
CREATE TABLE t1 (a INT);
|
||
|
|
INSERT INTO t1 (a, a) VALUES (1, 1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
INSERT IGNORE t1 (a, a) VALUES (1, 1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
INSERT IGNORE t1 (a, a) SELECT 1,1;
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
DROP TABLE t1;
|
||
|
|
#
|
||
|
|
# Worklog #6073: Remove INSERT DELAYED
|
||
|
|
#
|
||
|
|
CREATE TABLE t1( a INT );
|
||
|
|
INSERT DELAYED INTO t1 VALUES ( 1 );
|
||
|
|
Warnings:
|
||
|
|
Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
|
||
|
|
REPLACE DELAYED INTO t1 VALUES ( 1 );
|
||
|
|
Warnings:
|
||
|
|
Warning 3005 REPLACE DELAYED is no longer supported. The statement was converted to REPLACE.
|
||
|
|
DROP TABLE t1;
|
||
|
|
SET GLOBAL delayed_insert_limit = DEFAULT;
|
||
|
|
Warnings:
|
||
|
|
Warning 1287 '@@delayed_insert_limit' is deprecated and will be removed in a future release.
|
||
|
|
SET GLOBAL delayed_insert_timeout = DEFAULT;
|
||
|
|
Warnings:
|
||
|
|
Warning 1287 '@@delayed_insert_timeout' is deprecated and will be removed in a future release.
|
||
|
|
SET GLOBAL delayed_queue_size = DEFAULT;
|
||
|
|
Warnings:
|
||
|
|
Warning 1287 '@@delayed_queue_size' is deprecated and will be removed in a future release.
|
||
|
|
SET GLOBAL max_insert_delayed_threads = DEFAULT;
|
||
|
|
Warnings:
|
||
|
|
Warning 1287 '@@max_insert_delayed_threads' is deprecated and will be removed in a future release.
|
||
|
|
SET GLOBAL max_delayed_threads = DEFAULT;
|
||
|
|
Warnings:
|
||
|
|
Warning 1287 '@@max_delayed_threads' is deprecated and will be removed in a future release.
|
||
|
|
#
|
||
|
|
# Bug#18064775 Assertion failed: fields.elements == values.elements
|
||
|
|
#
|
||
|
|
CREATE TABLE t1(a INTEGER);
|
||
|
|
INSERT INTO t1(a) values();
|
||
|
|
ERROR 21S01: Column count doesn't match value count at row 1
|
||
|
|
DROP TABLE t1;
|
||
|
|
#
|
||
|
|
# Bug#11745889 Traditional: INSERT accepts invalid date from default val
|
||
|
|
#
|
||
|
|
SET sql_mode='';
|
||
|
|
CREATE TABLE default_date(a DATE NOT NULL DEFAULT '0000-00-00');
|
||
|
|
INSERT INTO default_date VALUES();
|
||
|
|
INSERT INTO default_date VALUES(DEFAULT);
|
||
|
|
SET sql_mode=default;
|
||
|
|
INSERT INTO default_date VALUES();
|
||
|
|
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
|
||
|
|
INSERT INTO default_date VALUES(DEFAULT);
|
||
|
|
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
|
||
|
|
INSERT INTO default_date VALUES('0000-00-00');
|
||
|
|
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
|
||
|
|
SELECT * FROM default_date;
|
||
|
|
a
|
||
|
|
0000-00-00
|
||
|
|
0000-00-00
|
||
|
|
DROP TABLE default_date;
|
||
|
|
|
||
|
|
# Bug#11744960 : INSERT IGNORE SHOULD RETURN WARNINGS
|
||
|
|
#
|
||
|
|
CREATE TABLE t (a INT PRIMARY KEY);
|
||
|
|
affected rows: 0
|
||
|
|
INSERT IGNORE INTO t VALUES (1);
|
||
|
|
affected rows: 1
|
||
|
|
INSERT IGNORE INTO t VALUES (1),(1);
|
||
|
|
affected rows: 0
|
||
|
|
info: Records: 2 Duplicates: 2 Warnings: 2
|
||
|
|
Warnings:
|
||
|
|
Warning 1062 Duplicate entry '1' for key 'PRIMARY'
|
||
|
|
Warning 1062 Duplicate entry '1' for key 'PRIMARY'
|
||
|
|
DROP TABLE t;
|
||
|
|
affected rows: 0
|
||
|
|
#
|
||
|
|
# WL#5275 Process subqueries in FROM clause in the same way as view
|
||
|
|
#
|
||
|
|
CREATE TABLE t1(pk INTEGER PRIMARY KEY, a INTEGER);
|
||
|
|
CREATE TABLE t2(pk INTEGER PRIMARY KEY, a INTEGER);
|
||
|
|
CREATE TABLE t3(a INTEGER);
|
||
|
|
CREATE TABLE t4(b INTEGER);
|
||
|
|
INSERT INTO t2 VALUES(1, 10), (2, 20), (3, 30), (4, 40);
|
||
|
|
INSERT INTO t3 VALUES(1), (3);
|
||
|
|
INSERT INTO t4 VALUES(1);
|
||
|
|
CREATE VIEW v1 AS
|
||
|
|
SELECT * FROM t1 WHERE pk IN (SELECT a FROM t3);
|
||
|
|
CREATE VIEW v2 AS
|
||
|
|
SELECT * FROM t2 WHERE pk IN (SELECT a FROM t3);
|
||
|
|
CREATE VIEW v3 AS
|
||
|
|
SELECT t1.pk,t1.a FROM t1 JOIN t4 ON pk IN (SELECT a FROM t3);
|
||
|
|
EXPLAIN INSERT INTO v1
|
||
|
|
SELECT * FROM v2;
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
1 PRIMARY <subquery5> NULL ALL NULL NULL NULL NULL NULL 100.00 Using where
|
||
|
|
1 PRIMARY t2 NULL eq_ref PRIMARY PRIMARY 4 <subquery5>.a 1 100.00 NULL
|
||
|
|
5 MATERIALIZED t3 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
||
|
|
3 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`pk`,`test`.`t1`.`a`) /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`pk` = `<subquery5>`.`a`)
|
||
|
|
INSERT INTO v1
|
||
|
|
SELECT * FROM v2;
|
||
|
|
DELETE FROM t1;
|
||
|
|
EXPLAIN INSERT INTO v3(pk,a)
|
||
|
|
SELECT * FROM v2;
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
1 PRIMARY <subquery5> NULL ALL NULL NULL NULL NULL NULL 100.00 Using where
|
||
|
|
1 PRIMARY t2 NULL eq_ref PRIMARY PRIMARY 4 <subquery5>.a 1 100.00 NULL
|
||
|
|
5 MATERIALIZED t3 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
||
|
|
3 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`pk`,`test`.`t1`.`a`) /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`pk` = `<subquery5>`.`a`)
|
||
|
|
INSERT INTO v3(pk,a)
|
||
|
|
SELECT * FROM v2;
|
||
|
|
DELETE FROM t1;
|
||
|
|
EXPLAIN INSERT INTO v1
|
||
|
|
SELECT dt.pk, v2.a
|
||
|
|
FROM (SELECT * FROM v2) AS dt JOIN v2 ON dt.pk=v2.pk;
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
|
||
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop)
|
||
|
|
1 PRIMARY t2 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 NULL
|
||
|
|
1 PRIMARY t2 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary
|
||
|
|
4 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`pk`,`test`.`t1`.`a`) /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) join `test`.`t2` semi join (`test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t3`.`a`) and (`test`.`t2`.`pk` = `test`.`t3`.`a`) and (`test`.`t2`.`pk` = `test`.`t3`.`a`))
|
||
|
|
INSERT INTO v1
|
||
|
|
SELECT dt.pk, v2.a
|
||
|
|
FROM (SELECT * FROM v2) AS dt JOIN v2 ON dt.pk=v2.pk;
|
||
|
|
DROP VIEW v1, v2, v3;
|
||
|
|
DROP TABLE t1, t2, t3, t4;
|
||
|
|
# Bug#20310257 Explain for insert into a view show wrong table for ins.
|
||
|
|
CREATE TABLE t1(a INTEGER);
|
||
|
|
CREATE TABLE t2(b INTEGER);
|
||
|
|
CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON TRUE;
|
||
|
|
explain INSERT INTO v(a) VALUES(1);
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`a`) values (1)
|
||
|
|
INSERT INTO v(a) VALUES(1);
|
||
|
|
explain INSERT INTO v(b) VALUES(1);
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t2 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t2` (`test`.`t2`.`b`) values (1)
|
||
|
|
INSERT INTO v(b) VALUES(1);
|
||
|
|
explain INSERT INTO v(a) SELECT 2;
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`a`) /* select#1 */ select 2 AS `2` from dual
|
||
|
|
INSERT INTO v(a) SELECT 2;
|
||
|
|
explain INSERT INTO v(b) SELECT 2;
|
||
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
|
|
1 INSERT t2 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
||
|
|
Warnings:
|
||
|
|
Note 1003 insert into `test`.`t2` (`test`.`t2`.`b`) /* select#1 */ select 2 AS `2` from dual
|
||
|
|
INSERT INTO v(b) SELECT 2;
|
||
|
|
SELECT * FROM t1;
|
||
|
|
a
|
||
|
|
1
|
||
|
|
2
|
||
|
|
SELECT * FROM t2;
|
||
|
|
b
|
||
|
|
1
|
||
|
|
2
|
||
|
|
DROP VIEW v;
|
||
|
|
DROP TABLE t1, t2;
|
||
|
|
CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INTEGER);
|
||
|
|
CREATE TABLE t2(a2 INTEGER PRIMARY KEY, b2 INTEGER);
|
||
|
|
CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON TRUE;
|
||
|
|
INSERT INTO v(a1,b1) VALUES (11, 0) ON DUPLICATE KEY UPDATE b1=b1+1;
|
||
|
|
INSERT INTO v(a1,b1) VALUES (11, 0) ON DUPLICATE KEY UPDATE b1=b1+1;
|
||
|
|
INSERT INTO v(a2,b2) VALUES (21, 0) ON DUPLICATE KEY UPDATE b2=b2+1;
|
||
|
|
INSERT INTO v(a2,b2) VALUES (21, 0) ON DUPLICATE KEY UPDATE b2=b2+1;
|
||
|
|
SELECT * FROM v;
|
||
|
|
a1 b1 a2 b2
|
||
|
|
11 1 21 1
|
||
|
|
DELETE FROM t1;
|
||
|
|
DELETE FROM t2;
|
||
|
|
INSERT INTO v(a1,b1) SELECT 11, 0 ON DUPLICATE KEY UPDATE b1=b1+1;
|
||
|
|
INSERT INTO v(a1,b1) SELECT 11, 0 ON DUPLICATE KEY UPDATE b1=b1+1;
|
||
|
|
INSERT INTO v(a2,b2) SELECT 21, 0 ON DUPLICATE KEY UPDATE b2=b2+1;
|
||
|
|
INSERT INTO v(a2,b2) SELECT 21, 0 ON DUPLICATE KEY UPDATE b2=b2+1;
|
||
|
|
SELECT * FROM v;
|
||
|
|
a1 b1 a2 b2
|
||
|
|
11 1 21 1
|
||
|
|
DROP VIEW v;
|
||
|
|
DROP TABLE t1, t2;
|
||
|
|
# Bug#20753569: handle_fatal_signal (sig=11) in
|
||
|
|
# st_select_lex::merge_derived
|
||
|
|
CREATE TABLE t1(a INTEGER);
|
||
|
|
CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;
|
||
|
|
INSERT INTO v1 SELECT 3;
|
||
|
|
REPLACE INTO v1 SELECT 3;
|
||
|
|
INSERT INTO v1 VALUES(3);
|
||
|
|
REPLACE INTO v1 VALUES(3);
|
||
|
|
DROP VIEW v1;
|
||
|
|
DROP TABLE t1;
|
||
|
|
#
|
||
|
|
# Bug#21696206: ASSERTION `TRANSL->ITEM->FIXED' FAILED IN
|
||
|
|
# SELECT_LEX::DELETE_UNUSED_MERGED_COLUMN
|
||
|
|
#
|
||
|
|
CREATE TABLE t1 ( pk INT, PRIMARY KEY (pk));
|
||
|
|
CREATE TABLE t2 LIKE t1;
|
||
|
|
INSERT INTO t1 VALUES (2);
|
||
|
|
INSERT INTO t2 VALUES (2);
|
||
|
|
CREATE VIEW v1 AS SELECT * FROM t2 AS a
|
||
|
|
WHERE a.pk IN ( SELECT pk FROM t1 AS b WHERE b.pk = a.pk );
|
||
|
|
CREATE VIEW v2 AS SELECT * FROM t1 AS a
|
||
|
|
WHERE a.pk IN ( SELECT pk FROM v1 AS b WHERE b.pk = a.pk );
|
||
|
|
PREPARE st1 FROM 'INSERT INTO v2 (pk) VALUES ( 1 )';
|
||
|
|
EXECUTE st1;
|
||
|
|
SELECT * FROM t1;
|
||
|
|
pk
|
||
|
|
1
|
||
|
|
2
|
||
|
|
SELECT * FROM t2;
|
||
|
|
pk
|
||
|
|
2
|
||
|
|
DROP TABLE t1, t2;
|
||
|
|
DROP VIEW v1, v2;
|
||
|
|
#
|
||
|
|
# Bug#21696641: ASSERTION !(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE |
|
||
|
|
# GRANT->PRIVILEGE))'
|
||
|
|
#
|
||
|
|
CREATE TABLE t1 (pk INT, PRIMARY KEY (pk));
|
||
|
|
INSERT INTO t1 VALUES (1);
|
||
|
|
CREATE ALGORITHM = TEMPTABLE VIEW v2 AS
|
||
|
|
SELECT * FROM t1 AS a NATURAL JOIN t1 b WHERE pk BETWEEN 1 AND 2;
|
||
|
|
CREATE ALGORITHM = UNDEFINED VIEW v1 AS
|
||
|
|
SELECT * FROM t1 AS a
|
||
|
|
WHERE a.pk IN ( SELECT pk FROM v2 AS b WHERE b.pk = a.pk );
|
||
|
|
PREPARE st1 FROM "INSERT INTO v1 (pk) VALUES (2)";
|
||
|
|
EXECUTE st1;
|
||
|
|
SELECT * FROM t1;
|
||
|
|
pk
|
||
|
|
1
|
||
|
|
2
|
||
|
|
DROP VIEW v1, v2;
|
||
|
|
DROP TABLE t1;
|
||
|
|
#
|
||
|
|
# BUG#22037930: INSERT IGNORE FAILS TO IGNORE
|
||
|
|
# FOREIGN KEY CONSTRAINT
|
||
|
|
# Setup.
|
||
|
|
CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB;
|
||
|
|
CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1))
|
||
|
|
ENGINE=INNODB;
|
||
|
|
INSERT INTO t1 VALUES(0);
|
||
|
|
INSERT INTO t2 VALUES(0);
|
||
|
|
# Without fix, an error is reported.
|
||
|
|
INSERT IGNORE INTO t2 VALUES(1);
|
||
|
|
Warnings:
|
||
|
|
Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0;
|
||
|
|
Warnings:
|
||
|
|
Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0;
|
||
|
|
Warnings:
|
||
|
|
Warning 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
# Test for multi update.
|
||
|
|
UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3;
|
||
|
|
Warnings:
|
||
|
|
Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3;
|
||
|
|
Warnings:
|
||
|
|
Warning 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
# Reports an error since IGNORE is not used.
|
||
|
|
INSERT INTO t2 VALUES(1);
|
||
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE t2 SET fld2=20 WHERE fld2=0;
|
||
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE t1 SET fld1=20 WHERE fld1=0;
|
||
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3;
|
||
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3;
|
||
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
DROP TABLE t2, t1;
|
||
|
|
#
|
||
|
|
# BUG#22037930: INSERT IGNORE FAILS TO IGNORE FOREIGN
|
||
|
|
# KEY CONSTRAINT
|
||
|
|
CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB;
|
||
|
|
CREATE TABLE t2 (fld1 VARCHAR(10), fld2 INT NOT NULL,
|
||
|
|
CONSTRAINT fk FOREIGN KEY (fld2) REFERENCES t1(fld1)) ENGINE= INNODB;
|
||
|
|
# Without patch, reports incorrect error.
|
||
|
|
INSERT INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def';
|
||
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
REPLACE INTO t2 VALUES('abc', 2);
|
||
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
INSERT IGNORE INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def';
|
||
|
|
Warnings:
|
||
|
|
Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
|
||
|
|
DROP TABLE t2, t1;
|
||
|
|
CREATE DATABASE meow;
|
||
|
|
CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
|
||
|
|
CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
|
||
|
|
CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
|
||
|
|
CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
|
||
|
|
CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
|
||
|
|
CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
|
||
|
|
INSERT INTO table_countries VALUES ('YY','Entenhausen');
|
||
|
|
CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES table_countries (country) ON UPDATE CASCADE);
|
||
|
|
INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
|
||
|
|
CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country)));
|
||
|
|
CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
|
||
|
|
INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00');
|
||
|
|
CREATE USER user20989@localhost;
|
||
|
|
GRANT SELECT ON table_source TO user20989@localhost;
|
||
|
|
GRANT SELECT ON table_countries TO user20989@localhost;
|
||
|
|
GRANT SELECT ON table_stations TO user20989@localhost;
|
||
|
|
GRANT SELECT ON view_stations TO user20989@localhost;
|
||
|
|
GRANT SELECT ON table_target TO user20989@localhost;
|
||
|
|
GRANT SELECT ON table_target2 TO user20989@localhost;
|
||
|
|
GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost;
|
||
|
|
REPLACE INTO table_target
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN table_target AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table 'table_target'
|
||
|
|
REPLACE INTO view_target2
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN view_target2 AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table 'view_target2'
|
||
|
|
REPLACE INTO view_target3
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN view_target3 AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
||
|
|
GRANT INSERT,DELETE ON table_target TO user20989@localhost;
|
||
|
|
GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost;
|
||
|
|
GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost;
|
||
|
|
REPLACE INTO table_target
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN table_target AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
|
||
|
|
ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table 'table_target2'
|
||
|
|
REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00');
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN view_target2 AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
mexs_id messzeit
|
||
|
|
87654321 2006-07-12 07:50:00
|
||
|
|
REPLACE INTO view_target2
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN view_target2 AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
REPLACE INTO view_target3
|
||
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
||
|
|
FROM table_source
|
||
|
|
INNER JOIN view_stations AS stations
|
||
|
|
ON table_source.id = stations.icao
|
||
|
|
LEFT JOIN view_target3 AS old
|
||
|
|
USING (mexs_id);
|
||
|
|
SELECT * FROM table_target;
|
||
|
|
mexs_id messzeit
|
||
|
|
87654321 2006-07-12 07:50:00
|
||
|
|
SELECT * FROM view_target2;
|
||
|
|
mexs_id messzeit
|
||
|
|
12X45Y78 2006-07-12 07:50:00
|
||
|
|
87654321 2006-07-12 07:50:00
|
||
|
|
SELECT * FROM view_target3;
|
||
|
|
mexs_id messzeit
|
||
|
|
87654321 2006-07-12 07:50:00
|
||
|
|
DROP VIEW view_stations;
|
||
|
|
DROP TABLE table_source;
|
||
|
|
DROP TABLE table_stations;
|
||
|
|
DROP TABLE table_countries;
|
||
|
|
DROP TABLE table_target;
|
||
|
|
DROP TABLE table_target2;
|
||
|
|
DROP TABLE table_target3;
|
||
|
|
DROP VIEW view_target2;
|
||
|
|
DROP VIEW view_target3;
|
||
|
|
DROP USER user20989@localhost;
|
||
|
|
DROP DATABASE meow;
|
||
|
|
#
|
||
|
|
# Bug#28836669: Only first set of duplicate columns are reported for INSERT
|
||
|
|
#
|
||
|
|
CREATE TABLE t1(a INT, b INT);
|
||
|
|
INSERT INTO t1(a, a) VALUES (1, 1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
INSERT INTO t1(a, a, b, b) VALUES (1, 1, 2, 2);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
SHOW ERRORS;
|
||
|
|
Level Code Message
|
||
|
|
Error 1110 Column 'a' specified twice
|
||
|
|
Error 1110 Column 'b' specified twice
|
||
|
|
INSERT INTO t1(a, a, a, a) VALUES (1, 1, 1, 1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
SHOW ERRORS;
|
||
|
|
Level Code Message
|
||
|
|
Error 1110 Column 'a' specified twice
|
||
|
|
Error 1110 Column 'a' specified twice
|
||
|
|
Error 1110 Column 'a' specified twice
|
||
|
|
DROP TABLE t1;
|
||
|
|
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);
|
||
|
|
INSERT INTO t1(a,a) VALUES (1,1);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
INSERT INTO t1(a, a, b, c) VALUES (1, 1, DEFAULT, DEFAULT);
|
||
|
|
ERROR 42000: Column 'a' specified twice
|
||
|
|
INSERT INTO t1(b, b) VALUES (DEFAULT, DEFAULT);
|
||
|
|
ERROR 42000: Column 'b' specified twice
|
||
|
|
DROP TABLE t1;
|
||
|
|
#
|
||
|
|
# Bug #29899614: SIG 6 -`M_PSI_BATCH_MODE == PSI_BATCH_MODE_NONE' | SQL/HANDLER.CC
|
||
|
|
#
|
||
|
|
CREATE TABLE t1 ( f1 INTEGER, INDEX ( f1 ) );
|
||
|
|
CREATE TABLE t2 ( f1 INTEGER );
|
||
|
|
INSERT INTO t1 VALUES (10);
|
||
|
|
INSERT INTO t2
|
||
|
|
SELECT STRAIGHT_JOIN *
|
||
|
|
FROM t1 AS alias1
|
||
|
|
WHERE EXISTS (
|
||
|
|
SELECT * FROM (
|
||
|
|
SELECT * FROM t1 JOIN t1 AS alias2 USING ( f1 )
|
||
|
|
) AS alias3
|
||
|
|
WHERE alias1.f1 < 20
|
||
|
|
);
|
||
|
|
FLUSH TABLES;
|
||
|
|
DROP TABLE t1, t2;
|