summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/heap/heap_auto_increment.result
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/heap/heap_auto_increment.result
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/heap/heap_auto_increment.result')
-rw-r--r--mysql-test/suite/heap/heap_auto_increment.result190
1 files changed, 190 insertions, 0 deletions
diff --git a/mysql-test/suite/heap/heap_auto_increment.result b/mysql-test/suite/heap/heap_auto_increment.result
new file mode 100644
index 00000000..0501de22
--- /dev/null
+++ b/mysql-test/suite/heap/heap_auto_increment.result
@@ -0,0 +1,190 @@
+drop table if exists t1;
+create table t1 (a int not null auto_increment,b int, primary key (a)) engine=heap auto_increment=3;
+insert into t1 values (1,1),(NULL,3),(NULL,4);
+delete from t1 where a=4;
+insert into t1 values (NULL,5),(NULL,6);
+select * from t1;
+a b
+1 1
+3 3
+5 5
+6 6
+delete from t1 where a=6;
+replace t1 values (3,1);
+ALTER TABLE t1 add c int;
+replace t1 values (3,3,3);
+insert into t1 values (NULL,7,7);
+update t1 set a=8,b=b+1,c=c+1 where a=7;
+insert into t1 values (NULL,9,9);
+select * from t1;
+a b c
+1 1 NULL
+3 3 3
+5 5 NULL
+8 8 8
+9 9 9
+drop table t1;
+create table t1 (
+skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
+sval char(20)
+) engine=heap;
+insert into t1 values (NULL, "hello");
+insert into t1 values (NULL, "hey");
+select * from t1;
+skey sval
+1 hello
+2 hey
+select _rowid,t1._rowid,skey,sval from t1;
+_rowid _rowid skey sval
+1 1 1 hello
+2 2 2 hey
+drop table t1;
+#
+# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA
+#
+CREATE TABLE t1 (
+id TINYINT NOT NULL AUTO_INCREMENT,
+name CHAR(30) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MEMORY;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 (name) VALUES ('dog');
+UPDATE t1 SET id=-1 WHERE id=1;
+INSERT INTO t1 (name) VALUES ('cat');
+SELECT * FROM t1;
+id name
+-1 dog
+2 cat
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
+#
+# MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number
+#
+SET @engine='MEMORY';
+CREATE PROCEDURE autoinc_mdev15353_one(engine VARCHAR(64), t VARCHAR(64))
+BEGIN
+DECLARE query TEXT DEFAULT 'CREATE TABLE t1 ('
+ ' id TTT NOT NULL AUTO_INCREMENT,'
+ ' name CHAR(30) NOT NULL,'
+ ' PRIMARY KEY (id)) ENGINE=EEE';
+EXECUTE IMMEDIATE REPLACE(REPLACE(query,'TTT', t), 'EEE', engine);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (name) VALUES ('dog');
+SELECT * FROM t1;
+UPDATE t1 SET id=-1 WHERE id=1;
+SELECT * FROM t1;
+INSERT INTO t1 (name) VALUES ('cat');
+SELECT * FROM t1;
+DROP TABLE t1;
+END;
+$$
+CALL autoinc_mdev15353_one(@engine, 'tinyint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'smallint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` smallint(6) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'mediumint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` mediumint(9) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'int');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'bigint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` bigint(20) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'float');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` float NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'double');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` double NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+DROP PROCEDURE autoinc_mdev15353_one;
+#
+# End of 10.2 tests
+#