summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/insert.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/insert.result')
-rw-r--r--mysql-test/main/insert.result810
1 files changed, 810 insertions, 0 deletions
diff --git a/mysql-test/main/insert.result b/mysql-test/main/insert.result
new file mode 100644
index 00000000..586dbbff
--- /dev/null
+++ b/mysql-test/main/insert.result
@@ -0,0 +1,810 @@
+drop table if exists t1,t2,t3;
+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 (sid char(20), id int(2) NOT NULL auto_increment, key(sid, id));
+insert into t1 values ('skr',NULL),('skr',NULL),('test',NULL);
+select * from t1;
+sid id
+skr 1
+skr 2
+test 1
+insert into t1 values ('rts',NULL),('rts',NULL),('test',NULL);
+select * from t1;
+sid id
+rts 1
+rts 2
+skr 1
+skr 2
+test 1
+test 2
+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 delayed into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
+select * from t2;
+id t2
+1 mysql.com
+2 hotmail.com
+3 aol.com
+drop table t1,t2;
+drop database if exists mysqltest;
+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);
+set @value= "aa";
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+Warnings:
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_double` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_float` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_double_7_2` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_float_4_3` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_double_u` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_float_u` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`f_double_15_1_u` at row 1
+Warning 1366 Incorrect double value: 'aa' for column `test`.`t1`.`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 ignore 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 ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+Warnings:
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_double` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_float` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_double_7_2` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_float_4_3` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_double_u` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_float_u` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`f_double_15_1_u` at row 1
+Warning 1366 Incorrect double value: 'aa1' for column `test`.`t1`.`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 ignore 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 ignore 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 ignore 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 ignore 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 ignore 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 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'' ';
+execute stmt1;
+ERROR 42000: Column 'a' specified twice
+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 21S01: Column count doesn't match value count at row 1
+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 (id int primary key auto_increment, data int, unique(data));
+insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
+insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90);
+Warnings:
+Warning 1062 Duplicate entry '110' for key 'data'
+Warning 1062 Duplicate entry '120' for key 'data'
+Warning 1062 Duplicate entry '100' for key 'data'
+insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150);
+Warnings:
+Warning 1062 Duplicate entry '110' for key 'data'
+Warning 1062 Duplicate entry '120' for key 'data'
+Warning 1062 Duplicate entry '100' for key 'data'
+select * from t1 order by id;
+id data
+1 100
+2 110
+3 120
+4 10
+5 20
+6 90
+7 130
+8 140
+9 150
+drop table t1;
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+CREATE TABLE t1 (i INT);
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+INSERT INTO t1 VALUES (1);
+RETURN 1;
+END |
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+INSERT DELAYED INTO t1 VALUES (2);
+RETURN 1;
+END |
+SELECT f1();
+f1()
+1
+SELECT f2();
+f2()
+1
+INSERT INTO t1 VALUES (3);
+INSERT DELAYED INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (f1());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+INSERT DELAYED INTO t1 VALUES (f1());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+INSERT INTO t1 VALUES (f2());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+INSERT DELAYED INTO t1 VALUES (f2());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT INTO t1 VALUES (NEW.i);
+INSERT INTO t1 VALUES (1);
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+INSERT DELAYED INTO t1 VALUES (1);
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+SELECT * FROM t1;
+i
+1
+2
+3
+4
+DROP FUNCTION f2;
+DROP FUNCTION f1;
+DROP TABLE t1;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (OLD.i);
+INSERT INTO t1 VALUES (1);
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT * FROM t1;
+i
+1
+2
+UPDATE t1 SET i = 3 WHERE i = 1;
+SELECT * FROM t1;
+i
+3
+2
+DELETE FROM t1 WHERE i = 3;
+SELECT * FROM t1;
+i
+2
+SELECT * FROM t2;
+i
+1
+2
+3
+3
+DROP TABLE t1, t2;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+SET @a= NEW.i;
+SET @a= 0;
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT @a;
+@a
+1
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT @a;
+@a
+2
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+INSERT INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (OLD.i);
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT * FROM t1;
+i
+1
+UPDATE t1 SET i = 2 WHERE i = 1;
+SELECT * FROM t1;
+i
+2
+DELETE FROM t1 WHERE i = 2;
+SELECT * FROM t1;
+i
+SELECT * FROM t2;
+i
+1
+2
+2
+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 IGNORE 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 IGNORE 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;
+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.
+#
+drop table if exists t1;
+create table t1 (data varchar(4) not null);
+set sql_mode='error_for_division_by_zero';
+#
+# Demonstrate that the number of warnings matches
+# the information in mysql_info().
+#
+insert t1 (data) values ('letter'), (1/0);
+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
+update t1 set data='envelope' where 1/0 or 1;
+affected rows: 2
+info: Rows matched: 2 Changed: 2 Warnings: 4
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1265 Data truncated for column 'data' at row 1
+Warning 1265 Data truncated for column 'data' at row 2
+insert t1 (data) values (default), (1/0), ('dead beef');
+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
+set sql_mode=default;
+drop table t1;
+#
+# End of 5.4 tests
+#
+#
+# Bug#54106 assert in Protocol::end_statement,
+# INSERT IGNORE ... SELECT ... UNION SELECT ...
+#
+DROP TABLE IF EXISTS t1;
+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;
+#
+# MDEV-5168: Ensure that we can disable duplicate key warnings
+# from INSERT IGNORE
+#
+create table t1 (f1 int unique, f2 int unique);
+insert into t1 values (1,12);
+insert into t1 values (2,13);
+insert into t1 values (1,12);
+ERROR 23000: Duplicate entry '1' for key 'f1'
+insert ignore into t1 values (1,12);
+Warnings:
+Warning 1062 Duplicate entry '1' for key 'f1'
+set @@old_mode="NO_DUP_KEY_WARNINGS_WITH_IGNORE";
+insert ignore into t1 values (1,12);
+insert ignore into t1 values (1,12) on duplicate key update f2=13;
+set @@old_mode="";
+insert ignore into t1 values (1,12);
+Warnings:
+Warning 1062 Duplicate entry '1' for key 'f1'
+DROP TABLE t1;
+#
+# MDEV-13290 Assertion Assertion `!is_set() || (m_status == DA_OK_BULK
+# && is_bulk_op())' or `! is_set()' failed
+#
+SET @save_mode= @@sql_mode;
+SET sql_mode= 'STRICT_ALL_TABLES';
+CREATE TABLE t1 (f1 INT DEFAULT 0, f2 INT);
+CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION;
+REPLACE INTO v1 SET f2 = 1;
+ERROR 22007: Truncated incorrect DECIMAL value: 'x'
+SELECT * from t1;
+f1 f2
+drop view v1;
+CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = cast('' as decimal) WITH CHECK OPTION;
+REPLACE INTO v1 SET f2 = 1;
+ERROR 22007: Truncated incorrect DECIMAL value: ''
+SELECT * from t1;
+f1 f2
+drop view v1;
+SELECT 0,0 INTO OUTFILE 't1.txt';
+CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION;
+LOAD DATA INFILE 't1.txt' INTO TABLE v1;
+ERROR 22007: Truncated incorrect DECIMAL value: 'x'
+SELECT * from t1;
+f1 f2
+drop view v1;
+drop table t1;
+SET @@sql_mode= @save_mode;
+#
+# MDEV-13861 Assertion `0' failed in Protocol::end_statement
+#
+CREATE TABLE t1 (f INT);
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE f <=> 'foo' WITH CHECK OPTION;
+REPLACE INTO v1 SET f = NULL;
+ERROR 22007: Truncated incorrect DECIMAL value: 'foo'
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
+#
+# MDEV-26412: INSERT CREATE with subquery in ON expression
+#
+create table t1 (a int);
+create table t2 (b int);
+create table t3 (c int);
+create table t4 (d1 int, d2 int);
+insert into t4
+select * from t1 left join t2 on (select t1.i from t3);
+ERROR 42S22: Unknown column 't1.i' in 'field list'
+replace t4
+select * from t1 left join t2 on (select t1.i from t3);
+ERROR 42S22: Unknown column 't1.i' in 'field list'
+drop table t1,t2,t3,t4;
+create table t (a int);
+select 1 in (select count(*) from t t1 join (t t2 join t t3 on (t1.a != 0)));
+ERROR 42S22: Unknown column 't1.a' in 'on clause'
+drop table t;
+#
+# MDEV-28578 Server crashes in Item_field::fix_outer_field after CREATE SELECT
+#
+create table t1 (i int) ;
+create table t2 (j int) ;
+create table t4 select * from t1 join t2 on (select t3.i);
+ERROR 42S22: Unknown column 't3.i' in 'field list'
+drop table t1, t2;
+#
+# End of 10.4 tests
+#
+#
+# outer references in subqueries in INSERT
+#
+create table t1 (a int default 5);
+insert t1 values (1);
+insert t1 values (a);
+insert t1 values ((select a+1));
+insert t1 set a=2;
+insert t1 set a=a+2;
+insert t1 set a=(select a+3);
+select * from t1;
+a
+1
+5
+6
+2
+7
+8
+drop table t1;
+#
+# End of 10.5 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-03 16:36:45 +0000