Debian Codegraph @

mysql-test/suite/heap/heap_auto_increment.result

raw
1 drop table if exists t1;
2 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=heap auto_increment=3;
3 insert into t1 values (1,1),(NULL,3),(NULL,4);
4 delete from t1 where a=4;
5 insert into t1 values (NULL,5),(NULL,6);
6 select * from t1;
7 a b
8 1 1
9 3 3
10 5 5
11 6 6
12 delete from t1 where a=6;
13 replace t1 values (3,1);
14 ALTER TABLE t1 add c int;
15 replace t1 values (3,3,3);
16 insert into t1 values (NULL,7,7);
17 update t1 set a=8,b=b+1,c=c+1 where a=7;
18 insert into t1 values (NULL,9,9);
19 select * from t1;
20 a b c
21 1 1 NULL
22 3 3 3
23 5 5 NULL
24 8 8 8
25 9 9 9
26 drop table t1;
27 create table t1 (
28 skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
29 sval char(20)
30 ) engine=heap;
31 insert into t1 values (NULL, "hello");
32 insert into t1 values (NULL, "hey");
33 select * from t1;
34 skey sval
35 1 hello
36 2 hey
37 select _rowid,t1._rowid,skey,sval from t1;
38 _rowid _rowid skey sval
39 1 1 1 hello
40 2 2 2 hey
41 drop table t1;
42 #
43 # MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA
44 #
45 CREATE TABLE t1 (
46 id TINYINT NOT NULL AUTO_INCREMENT,
47 name CHAR(30) NOT NULL,
48 PRIMARY KEY (id)
49 ) ENGINE=MEMORY;
50 SHOW CREATE TABLE t1;
51 Table Create Table
52 t1 CREATE TABLE `t1` (
53 `id` tinyint(4) NOT NULL AUTO_INCREMENT,
54 `name` char(30) NOT NULL,
55 PRIMARY KEY (`id`)
56 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
57 INSERT INTO t1 (name) VALUES ('dog');
58 UPDATE t1 SET id=-1 WHERE id=1;
59 INSERT INTO t1 (name) VALUES ('cat');
60 SELECT * FROM t1;
61 id name
62 -1 dog
63 2 cat
64 DROP TABLE t1;
65 #
66 # End of 5.5 tests
67 #
68 #
69 # MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number
70 #
71 SET @engine='MEMORY';
72 CREATE PROCEDURE autoinc_mdev15353_one(engine VARCHAR(64), t VARCHAR(64))
73 BEGIN
74 DECLARE query TEXT DEFAULT 'CREATE TABLE t1 ('
75 ' id TTT NOT NULL AUTO_INCREMENT,'
76 ' name CHAR(30) NOT NULL,'
77 ' PRIMARY KEY (id)) ENGINE=EEE';
78 EXECUTE IMMEDIATE REPLACE(REPLACE(query,'TTT', t), 'EEE', engine);
79 SHOW CREATE TABLE t1;
80 INSERT INTO t1 (name) VALUES ('dog');
81 SELECT * FROM t1;
82 UPDATE t1 SET id=-1 WHERE id=1;
83 SELECT * FROM t1;
84 INSERT INTO t1 (name) VALUES ('cat');
85 SELECT * FROM t1;
86 DROP TABLE t1;
87 END;
88 $$
89 CALL autoinc_mdev15353_one(@engine, 'tinyint');
90 Table Create Table
91 t1 CREATE TABLE `t1` (
92 `id` tinyint(4) NOT NULL AUTO_INCREMENT,
93 `name` char(30) NOT NULL,
94 PRIMARY KEY (`id`)
95 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
96 id name
97 1 dog
98 id name
99 -1 dog
100 id name
101 -1 dog
102 2 cat
103 CALL autoinc_mdev15353_one(@engine, 'smallint');
104 Table Create Table
105 t1 CREATE TABLE `t1` (
106 `id` smallint(6) NOT NULL AUTO_INCREMENT,
107 `name` char(30) NOT NULL,
108 PRIMARY KEY (`id`)
109 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
110 id name
111 1 dog
112 id name
113 -1 dog
114 id name
115 -1 dog
116 2 cat
117 CALL autoinc_mdev15353_one(@engine, 'mediumint');
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
121 `name` char(30) NOT NULL,
122 PRIMARY KEY (`id`)
123 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
124 id name
125 1 dog
126 id name
127 -1 dog
128 id name
129 -1 dog
130 2 cat
131 CALL autoinc_mdev15353_one(@engine, 'int');
132 Table Create Table
133 t1 CREATE TABLE `t1` (
134 `id` int(11) NOT NULL AUTO_INCREMENT,
135 `name` char(30) NOT NULL,
136 PRIMARY KEY (`id`)
137 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
138 id name
139 1 dog
140 id name
141 -1 dog
142 id name
143 -1 dog
144 2 cat
145 CALL autoinc_mdev15353_one(@engine, 'bigint');
146 Table Create Table
147 t1 CREATE TABLE `t1` (
148 `id` bigint(20) NOT NULL AUTO_INCREMENT,
149 `name` char(30) NOT NULL,
150 PRIMARY KEY (`id`)
151 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
152 id name
153 1 dog
154 id name
155 -1 dog
156 id name
157 -1 dog
158 2 cat
159 CALL autoinc_mdev15353_one(@engine, 'float');
160 Table Create Table
161 t1 CREATE TABLE `t1` (
162 `id` float NOT NULL AUTO_INCREMENT,
163 `name` char(30) NOT NULL,
164 PRIMARY KEY (`id`)
165 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
166 id name
167 1 dog
168 id name
169 -1 dog
170 id name
171 -1 dog
172 2 cat
173 CALL autoinc_mdev15353_one(@engine, 'double');
174 Table Create Table
175 t1 CREATE TABLE `t1` (
176 `id` double NOT NULL AUTO_INCREMENT,
177 `name` char(30) NOT NULL,
178 PRIMARY KEY (`id`)
179 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
180 id name
181 1 dog
182 id name
183 -1 dog
184 id name
185 -1 dog
186 2 cat
187 DROP PROCEDURE autoinc_mdev15353_one;
188 #
189 # End of 10.2 tests
190 #