1
by brian
clean slate |
1 |
#
|
2 |
# Test of auto_increment; The test for BDB tables is in bdb.test |
|
3 |
#
|
|
4 |
--disable_warnings
|
|
5 |
drop table if exists t1; |
|
6 |
drop table if exists t2; |
|
7 |
--enable_warnings
|
|
8 |
SET SQL_WARNINGS=1; |
|
9 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
10 |
create TEMPORARY table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3; |
1
by brian
clean slate |
11 |
insert into t1 values (1,1),(NULL,3),(NULL,4); |
12 |
delete from t1 where a=4; |
|
13 |
insert into t1 values (NULL,5),(NULL,6); |
|
14 |
select * from t1; |
|
15 |
delete from t1 where a=6; |
|
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
16 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 #
|
1273.19.12
by Brian Aker
Enabled more tests. |
17 |
show table status like "t1"; |
1
by brian
clean slate |
18 |
replace t1 values (3,1); |
19 |
ALTER TABLE t1 add c int; |
|
20 |
replace t1 values (3,3,3); |
|
21 |
insert into t1 values (NULL,7,7); |
|
22 |
update t1 set a=8,b=b+1,c=c+1 where a=7; |
|
23 |
insert into t1 values (NULL,9,9); |
|
24 |
select * from t1; |
|
25 |
drop table t1; |
|
26 |
||
27 |
create table t1 ( |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
28 |
skey int NOT NULL auto_increment PRIMARY KEY, |
1
by brian
clean slate |
29 |
sval char(20) |
30 |
);
|
|
31 |
insert into t1 values (NULL, "hello"); |
|
32 |
insert into t1 values (NULL, "hey"); |
|
33 |
select * from t1; |
|
34 |
select _rowid,t1._rowid,skey,sval from t1; |
|
35 |
drop table t1; |
|
36 |
||
37 |
#
|
|
38 |
# Test auto_increment on sub key |
|
39 |
#
|
|
642.1.71
by Lee
merge with latest from the trunk |
40 |
#Drizzle does not support auto_increment on sub key. |
41 |
#create table t1 (a char(10) not null, b int not null auto_increment, primary key(b)); |
|
42 |
#insert into t1 values ("a",1),("b",2),("a",2),("c",1); |
|
43 |
#insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); |
|
44 |
#insert into t1 (a) values ("a"),("b"),("c"),("d"); |
|
45 |
#insert into t1 (a) values ('k'),('d'); |
|
46 |
#insert into t1 (a) values ("a"); |
|
47 |
#insert into t1 values ("d",last_insert_id()); |
|
48 |
#select * from t1; |
|
49 |
#drop table t1; |
|
50 |
||
51 |
#create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid)); |
|
52 |
#insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj'); |
|
53 |
#select * from t1; |
|
54 |
#drop table t1; |
|
55 |
||
56 |
#create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)); |
|
57 |
#insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc'); |
|
58 |
#select * from t1; |
|
59 |
#drop table t1; |
|
60 |
||
61 |
#create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id)); |
|
62 |
#create table t2 (sid char(20), id int(2)); |
|
63 |
#insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL); |
|
64 |
#insert into t1 select * from t2; |
|
65 |
#select * from t1; |
|
66 |
#drop table t1,t2; |
|
1
by brian
clean slate |
67 |
|
68 |
#
|
|
69 |
# Test of auto_increment columns when they are set to 0 |
|
70 |
#
|
|
71 |
||
72 |
create table t1 (a int not null primary key auto_increment); |
|
73 |
insert into t1 values (0); |
|
74 |
update t1 set a=0; |
|
75 |
select * from t1; |
|
76 |
check table t1; |
|
77 |
drop table t1; |
|
78 |
||
79 |
#
|
|
80 |
# Test negative values (Bug #1366) |
|
81 |
#
|
|
82 |
||
83 |
create table t1 (a int not null auto_increment primary key); |
|
84 |
insert into t1 values (NULL); |
|
85 |
insert into t1 values (-1); |
|
86 |
select last_insert_id(); |
|
87 |
insert into t1 values (NULL); |
|
88 |
select * from t1; |
|
89 |
drop table t1; |
|
90 |
||
1233.2.1
by Monty Taylor
Renamed instances of HEAP engine to MEMORY. Removed the alias. |
91 |
create temporary table t1 (a int not null auto_increment primary key) /*!40102 engine=MEMORY */; |
1
by brian
clean slate |
92 |
insert into t1 values (NULL); |
93 |
insert into t1 values (-1); |
|
94 |
select last_insert_id(); |
|
95 |
insert into t1 values (NULL); |
|
96 |
select * from t1; |
|
97 |
drop table t1; |
|
98 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
99 |
create table t1 (i int not null auto_increment, key (i)); |
1
by brian
clean slate |
100 |
insert into t1 set i = 254; |
101 |
insert into t1 set i = null; |
|
102 |
select last_insert_id(); |
|
103 |
insert into t1 set i = null; |
|
104 |
select last_insert_id(); |
|
105 |
drop table t1; |
|
106 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
107 |
create table t1 (i int not null auto_increment primary key, b int, unique (b)); |
1
by brian
clean slate |
108 |
insert into t1 values (NULL, 10); |
109 |
select last_insert_id(); |
|
110 |
insert into t1 values (NULL, 15); |
|
111 |
select last_insert_id(); |
|
112 |
--error ER_DUP_ENTRY
|
|
113 |
insert into t1 values (NULL, 10); |
|
114 |
select last_insert_id(); |
|
115 |
||
116 |
drop table t1; |
|
117 |
||
118 |
create table t1(a int auto_increment,b int null,primary key(a)); |
|
119 |
insert into t1(a,b)values(NULL,1); |
|
120 |
insert into t1(a,b)values(200,2); |
|
121 |
insert into t1(a,b)values(0,3); |
|
122 |
insert into t1(b)values(4); |
|
123 |
insert into t1(b)values(5); |
|
124 |
insert into t1(b)values(6); |
|
125 |
insert into t1(b)values(7); |
|
126 |
select * from t1 order by b; |
|
642.1.71
by Lee
merge with latest from the trunk |
127 |
#mediumint is not supported. changed to b int. |
128 |
alter table t1 modify b int; |
|
1
by brian
clean slate |
129 |
select * from t1 order by b; |
130 |
create table t2 (a int); |
|
131 |
insert t2 values (1),(2); |
|
132 |
alter table t2 add b int auto_increment primary key; |
|
133 |
select * from t2; |
|
134 |
drop table t2; |
|
135 |
delete from t1 where a=0; |
|
136 |
update t1 set a=0 where b=5; |
|
137 |
select * from t1 order by b; |
|
138 |
delete from t1 where a=0; |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
139 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
140 |
update t1 set a=NULL where b=6; |
141 |
update t1 set a=300 where b=7; |
|
142 |
insert into t1(a,b)values(NULL,8); |
|
143 |
insert into t1(a,b)values(400,9); |
|
144 |
insert into t1(a,b)values(0,10); |
|
145 |
insert into t1(b)values(11); |
|
146 |
insert into t1(b)values(12); |
|
147 |
insert into t1(b)values(13); |
|
148 |
insert into t1(b)values(14); |
|
149 |
select * from t1 order by b; |
|
150 |
delete from t1 where a=0; |
|
151 |
update t1 set a=0 where b=12; |
|
152 |
select * from t1 order by b; |
|
153 |
delete from t1 where a=0; |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
154 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
155 |
update t1 set a=NULL where b=13; |
156 |
update t1 set a=500 where b=14; |
|
157 |
select * from t1 order by b; |
|
158 |
drop table t1; |
|
159 |
||
160 |
#
|
|
161 |
# Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is |
|
162 |
# converted to AUTO_INCREMENT column |
|
163 |
#
|
|
164 |
create table t1 (a bigint); |
|
165 |
insert into t1 values (1), (2), (3), (NULL), (NULL); |
|
166 |
alter table t1 modify a bigint not null auto_increment primary key; |
|
167 |
select * from t1; |
|
168 |
drop table t1; |
|
169 |
||
170 |
create table t1 (a bigint); |
|
171 |
insert into t1 values (1), (2), (3), (0), (0); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
172 |
--error ER_DUP_ENTRY
|
1
by brian
clean slate |
173 |
alter table t1 modify a bigint not null auto_increment primary key; |
174 |
select * from t1; |
|
175 |
drop table t1; |
|
176 |
||
177 |
# We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode |
|
1008.3.2
by Stewart Smith
Make sql_mode=NO_AUTO_VALUE_ON_ZERO default for Drizzle. |
178 |
create table t1 (a bigint); |
179 |
insert into t1 values (0), (1), (2), (3); |
|
642.1.71
by Lee
merge with latest from the trunk |
180 |
|
181 |
# Bug314567 - ALTER TABLE causes auto_increment resequencing, |
|
182 |
# resulting in duplicate entry since sql_mode=NO_AUTO_VALUE_ON_ZERO |
|
183 |
#is not supported. |
|
1008.3.2
by Stewart Smith
Make sql_mode=NO_AUTO_VALUE_ON_ZERO default for Drizzle. |
184 |
# NO_AUTO_VALUE_ON_ZERO is now default for Drizzle |
642.1.71
by Lee
merge with latest from the trunk |
185 |
|
1008.3.2
by Stewart Smith
Make sql_mode=NO_AUTO_VALUE_ON_ZERO default for Drizzle. |
186 |
alter table t1 modify a bigint not null auto_increment primary key; |
642.1.71
by Lee
merge with latest from the trunk |
187 |
#set sql_mode= ''; |
1008.3.2
by Stewart Smith
Make sql_mode=NO_AUTO_VALUE_ON_ZERO default for Drizzle. |
188 |
select * from t1; |
189 |
drop table t1; |
|
1
by brian
clean slate |
190 |
|
191 |
# It also sensible to preserve zeroes if we are converting auto_increment |
|
192 |
# column to auto_increment column (or not touching it at all, which is more |
|
193 |
# common case probably) |
|
194 |
create table t1 (a int auto_increment primary key , b int null); |
|
642.1.71
by Lee
merge with latest from the trunk |
195 |
#sql_mode is not supported. |
196 |
#set sql_mode=NO_AUTO_VALUE_ON_ZERO; |
|
1008.3.2
by Stewart Smith
Make sql_mode=NO_AUTO_VALUE_ON_ZERO default for Drizzle. |
197 |
insert into t1 values (0,1),(1,2),(2,3); |
198 |
select * from t1; |
|
642.1.71
by Lee
merge with latest from the trunk |
199 |
#set sql_mode= ''; |
1
by brian
clean slate |
200 |
alter table t1 modify b varchar(255); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
201 |
--error ER_DUP_ENTRY
|
1
by brian
clean slate |
202 |
insert into t1 values (0,4); |
203 |
select * from t1; |
|
204 |
drop table t1; |
|
205 |
||
206 |
#
|
|
207 |
# BUG #10045: Problem with composite AUTO_INCREMENT + BLOB key |
|
208 |
||
209 |
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10))); |
|
210 |
INSERT INTO t1 (b) VALUES ('aaaa'); |
|
211 |
CHECK TABLE t1; |
|
212 |
INSERT INTO t1 (b) VALUES (''); |
|
213 |
CHECK TABLE t1; |
|
214 |
INSERT INTO t1 (b) VALUES ('bbbb'); |
|
215 |
CHECK TABLE t1; |
|
216 |
DROP TABLE IF EXISTS t1; |
|
217 |
||
218 |
# BUG #19025: |
|
642.1.71
by Lee
merge with latest from the trunk |
219 |
#changed syntax of int(10) to int |
220 |
CREATE TABLE t1 ( |
|
1
by brian
clean slate |
221 |
t1_name VARCHAR(255) DEFAULT NULL, |
642.1.71
by Lee
merge with latest from the trunk |
222 |
t1_id INT not null AUTO_INCREMENT, |
1
by brian
clean slate |
223 |
KEY (t1_name), |
224 |
PRIMARY KEY (t1_id) |
|
225 |
) AUTO_INCREMENT = 1000; |
|
226 |
||
227 |
INSERT INTO t1 (t1_name) VALUES('MySQL'); |
|
228 |
INSERT INTO t1 (t1_name) VALUES('MySQL'); |
|
229 |
INSERT INTO t1 (t1_name) VALUES('MySQL'); |
|
230 |
||
231 |
SELECT * from t1; |
|
232 |
||
233 |
SHOW CREATE TABLE `t1`; |
|
234 |
||
235 |
DROP TABLE `t1`; |
|
236 |
||
237 |
#
|
|
238 |
# Bug #6880: LAST_INSERT_ID() within a statement |
|
239 |
#
|
|
240 |
||
241 |
create table t1(a int not null auto_increment primary key); |
|
242 |
create table t2(a int not null auto_increment primary key, t1a int); |
|
243 |
insert into t1 values(NULL); |
|
244 |
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); |
|
245 |
insert into t1 values (NULL); |
|
246 |
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), |
|
247 |
(NULL, LAST_INSERT_ID()); |
|
248 |
insert into t1 values (NULL); |
|
249 |
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), |
|
250 |
(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); |
|
251 |
select * from t2; |
|
252 |
drop table t1, t2; |
|
253 |
||
254 |
--echo End of 4.1 tests
|
|
255 |
||
256 |
#
|
|
257 |
# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error |
|
258 |
#
|
|
642.1.71
by Lee
merge with latest from the trunk |
259 |
#changed syntax of int (11) to int |
260 |
CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)); |
|
1
by brian
clean slate |
261 |
insert into t1 (b) values (1); |
262 |
replace into t1 (b) values (2), (1), (3); |
|
263 |
select * from t1; |
|
264 |
truncate table t1; |
|
265 |
insert into t1 (b) values (1); |
|
266 |
replace into t1 (b) values (2); |
|
267 |
replace into t1 (b) values (1); |
|
268 |
replace into t1 (b) values (3); |
|
269 |
select * from t1; |
|
270 |
drop table t1; |
|
271 |
||
272 |
create table t1 (rowid int not null auto_increment, val int not null,primary |
|
273 |
key (rowid), unique(val)); |
|
274 |
replace into t1 (val) values ('1'),('2'); |
|
275 |
replace into t1 (val) values ('1'),('2'); |
|
276 |
--error ER_DUP_ENTRY
|
|
277 |
insert into t1 (val) values ('1'),('2'); |
|
278 |
select * from t1; |
|
279 |
drop table t1; |
|
280 |
||
281 |
#
|
|
282 |
# Test that update changes internal auto-increment value |
|
642.1.71
by Lee
merge with latest from the trunk |
283 |
# Bug 314570 |
1
by brian
clean slate |
284 |
|
642.1.71
by Lee
merge with latest from the trunk |
285 |
#create table t1 (a int not null auto_increment primary key, val int); |
286 |
#insert into t1 (val) values (1); |
|
287 |
#update t1 set a=2 where a=1; |
|
288 |
#insert into t1 (val) values (1); |
|
289 |
#select * from t1; |
|
290 |
#drop table t1; |
|
1
by brian
clean slate |
291 |
|
292 |
#
|
|
293 |
# Test key duplications with auto-increment in ALTER TABLE |
|
294 |
# bug #14573 |
|
295 |
#
|
|
642.1.71
by Lee
merge with latest from the trunk |
296 |
#changed syntax of int(11) to int. |
297 |
CREATE TABLE t1 (t1 INT PRIMARY KEY, t2 INT); |
|
1
by brian
clean slate |
298 |
INSERT INTO t1 VALUES(0, 0); |
299 |
INSERT INTO t1 VALUES(1, 1); |
|
642.1.71
by Lee
merge with latest from the trunk |
300 |
ALTER TABLE t1 CHANGE t1 t1 INT auto_increment; |
1008.3.2
by Stewart Smith
Make sql_mode=NO_AUTO_VALUE_ON_ZERO default for Drizzle. |
301 |
--error ER_DUP_ENTRY
|
302 |
INSERT INTO t1 VALUES(0,0); |
|
1
by brian
clean slate |
303 |
DROP TABLE t1; |
304 |
||
305 |
# Test of REPLACE when it does INSERT+DELETE and not UPDATE: |
|
306 |
# see if it sets LAST_INSERT_ID() ok |
|
307 |
create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c)); |
|
308 |
insert into t1 values(null,1,1,now()); |
|
309 |
insert into t1 values(null,0,0,null); |
|
310 |
# this will delete two rows |
|
311 |
replace into t1 values(null,1,0,null); |
|
312 |
select last_insert_id(); |
|
313 |
||
314 |
drop table t1; |