~drizzle-trunk/drizzle/development : contents of tests/r/insert.result at revision 807

~drizzle-trunk/drizzle/development : (revision 807)

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, 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);
ERROR 23000: Column 'id' cannot be null
select * from t1;
id
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(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);
replace into t1 (a,a) select 100, 'hundred';
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);
select row_count();
row_count()
0
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 (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);
insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150);
select * from t1 order by id;
id	data
1	100
2	110
3	120
4	10
5	20
6	90
10	130
11	140
12	150
drop table t1;
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) 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-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.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.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;
End of 5.0 tests.

1 by brian clean slate	1	drop table if exists t1,t2,t3;
	2	create table t1 (a int not null);
	3	insert into t1 values (1);
	4	insert into t1 values (a+2);
	5	insert into t1 values (a+3),(a+4);
	6	insert into t1 values (5),(a+6);
	7	select * from t1;
	8	a
	9	1
	10	2
	11	3
	12	4
	13	5
	14	6
	15	drop table t1;
	16	create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username));
	17	insert into t1 values (0,"mysql");
	18	insert into t1 values (0,"mysql ab");
	19	insert into t1 values (0,"mysql a");
	20	insert into t1 values (0,"r1manic");
	21	insert into t1 values (0,"r1man");
	22	drop table t1;
	23	create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int);
	24	insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default);
	25	select a,t>0,c,i from t1;
	26	a t>0 c i
	27	1 1 hello NULL
	28	2 1 hello NULL
	29	4 0 a 5
	30	5 1 hello NULL
	31	truncate table t1;
	32	insert into t1 set a=default,t=default,c=default;
	33	insert into t1 set a=default,t=default,c=default,i=default;
	34	insert into t1 set a=4,t=0,c="a",i=5;
	35	insert into t1 set a=5,t=0,c="a",i=null;
	36	insert into t1 set a=default,t=default,c=default,i=default;
	37	select a,t>0,c,i from t1;
	38	a t>0 c i
	39	1 1 hello NULL
	40	2 1 hello NULL
	41	4 0 a 5
	42	5 0 a NULL
	43	6 1 hello NULL
	44	drop table t1;
	45	create table t1 (id int NOT NULL DEFAULT 8);
	46	insert into t1 values(NULL);
	47	ERROR 23000: Column 'id' cannot be null
	48	insert into t1 values (1), (NULL), (2);
	49	ERROR 23000: Column 'id' cannot be null
	50	select * from t1;
	51	id
	52	drop table t1;
	53	create table t1 (email varchar(50));
	54	insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com');
	55	create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2));
	56	insert delayed into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
	57	select * from t2;
	58	id t2
	59	1 mysql.com
	60	2 hotmail.com
	61	3 aol.com
	62	drop table t1,t2;
	63	drop database if exists mysqltest;
	64	create database mysqltest;
65	use mysqltest;
66	create table t1 (c int);
67	insert into mysqltest.t1 set mysqltest.t1.c = '1';
68	drop database mysqltest;
69	use test;
70	create table t1(id1 int not null auto_increment primary key, t char(12));
71	create table t2(id2 int not null, t char(12));
72	create table t3(id3 int not null, t char(12), index(id3));
73	select count(*) from t2;
74	count(*)
75	500
76	insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = t3.id3;
77	select count(*) from t2;
78	count(*)
79	25500
80	drop table t1,t2,t3;
81	create table t1 (a int, b int);
82	insert into t1 (a,b) values (a,b);
83	insert into t1 SET a=1, b=a+1;
84	insert into t1 (a,b) select 1,2;
85	INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
86	replace into t1 (a,a) select 100, 'hundred';
87	ERROR 42000: Column 'a' specified twice
88	insert into t1 (a,b,b) values (1,1,1);
89	ERROR 42000: Column 'b' specified twice
90	insert into t1 (a,a) values (1,1,1);
91	ERROR 21S01: Column count doesn't match value count at row 1
92	insert into t1 (a,a) values (1,1);
93	ERROR 42000: Column 'a' specified twice
94	insert into t1 SET a=1,b=2,a=1;
95	ERROR 42000: Column 'a' specified twice
96	insert into t1 (b,b) select 1,2;
97	ERROR 42000: Column 'b' specified twice
98	INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
99	ERROR 42000: Column 'b' specified twice
100	drop table t1;
101	create table t1 (id int primary key, data int);
102	insert into t1 values (1, 1), (2, 2), (3, 3);
103	select row_count();
104	row_count()
105	3
106	insert ignore into t1 values (1, 1);
107	select row_count();
108	row_count()
109	0
110	replace into t1 values (1, 11);
111	select row_count();
112	row_count()
113	2
114	replace into t1 values (4, 4);
115	select row_count();
116	row_count()
117	1
118	insert into t1 values (2, 2) on duplicate key update data= data + 10;
119	select row_count();
120	row_count()
121	2
122	insert into t1 values (5, 5) on duplicate key update data= data + 10;
123	select row_count();
124	row_count()
125	1
126	drop table t1;
127	create table t1 (id int primary key auto_increment, data int, unique(data));
128	insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
129	insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90);
130	insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150);
131	select * from t1 order by id;
132	id data
133	1 100
134	2 110
135	3 120
136	4 10
137	5 20
138	6 90
201 by Brian Aker Convert default engine to Innodb	139	10 130
	140	11 140
	141	12 150
1 by brian clean slate	142	drop table t1;
	143	CREATE TABLE t1 (
	144	a char(20) NOT NULL,
	145	b char(7) DEFAULT NULL,
	146	c char(4) DEFAULT NULL
	147	);
	148	INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);
	149	INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);
	150	INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);
	151	INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);
	152	INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01);
	153	INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01);
	154	INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05);
	155	INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10);
	156	INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15);
	157	INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0);
	158	INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78);
	159	INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94);
	160	INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203);
	161	INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);
	162	INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);
	163	INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);
	164	INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);
	165	INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);
	166	INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);
	167	INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);
	168	SELECT * FROM t1;
	169	a b c
	170	9.999999 10 10
	171	0.0001225 1.22e-4 NULL
	172	0.1225 0.1225 NULL
	173	0.1225877 0.12259 NULL
	174	12.25 12.25 NULL
	175	12.25 12.25 12.2
	176	122500 122500 NULL
	177	12250000000 1.22e10 NULL
	178	1.225e15 1.22e15 NULL
	179	5000000 5000000 NULL
	180	1.25e78 1.25e78 NULL
	181	1.25e-94 1.2e-94 NULL
	182	1.25e203 1.2e203 NULL
	183	1.25e-175 1e-175 NULL
	184	1.225 NULL 1.23
	185	1.37 NULL 1.37
	186	-1.37 NULL -1.4
	187	-0.0187 NULL 0
	188	5000 NULL 5000
	189	-5000 NULL -5e3
	190	DROP TABLE t1;
	191	CREATE TABLE t1 (
	192	a char(20) NOT NULL,
	193	b char(7) DEFAULT NULL,
	194	c char(5)
	195	);
	196	INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);
	197	INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05);
	198	INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);
	199	INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);
	200	INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);
	201	INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01);
	202	INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01);
	203	INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05);
	204	INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10);
	205	INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15);
206	INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0);
207	INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78);
208	INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94);
209	INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203);
210	INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);
211	INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);
212	INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);
213	INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);
214	INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3);
215	INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);
216	INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);
217	INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);
218	SELECT * FROM t1;
219	a b c
220	9.999999 10 9.999
221	0.00001225 1.22e-5 1e-5
222	0.0001225 1.22e-4 NULL
223	0.1225 0.1225 NULL
224	0.1225877 0.12259 NULL
225	12.25 12.25 NULL
226	12.25 12.25 12.25
227	122500 122500 NULL
228	12250000000 1.22e10 NULL
229	1.225e15 1.22e15 NULL
230	5000000 5000000 NULL
231	1.25e78 1.25e78 NULL
232	1.25e-94 1.2e-94 NULL
233	1.25e203 1.2e203 NULL
234	1.25e-175 1e-175 NULL
235	1.225 NULL 1.225
236	1.37 NULL 1.37
237	-1.37 NULL -1.37
238	0.00187 NULL 0.002
239	-0.0187 NULL -0.02
240	5000 NULL 5000
241	-5000 NULL -5000
242	DROP TABLE t1;
243	CREATE TABLE t (a CHAR(10),b INT);
244	INSERT INTO t VALUES (),(),();
245	INSERT INTO t(a) SELECT rand() FROM t;
246	DROP TABLE t;
247	CREATE TABLE t1 (c1 INT NOT NULL);
248	INSERT INTO t1 VALUES(4188.32999999999992724042385816574096679687500),
249	('4188.32999999999992724042385816574096679687500'), (4188);
250	SELECT * FROM t1;
251	c1
252	4188
253	4188
254	4188
255	CREATE TABLE t2 (c1 BIGINT);
256	INSERT INTO t2 VALUES('15449237462.0000000000');
257	SELECT * FROM t2;
258	c1
259	15449237462
260	DROP TABLE t1, t2;
261	End of 5.0 tests.