mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
297 lines
6 KiB
Text
297 lines
6 KiB
Text
drop table if exists t1,s1,s2;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t1,test.s1,test.s2'
|
|
drop view if exists v1;
|
|
Warnings:
|
|
Note 4092 Unknown VIEW: 'test.v1'
|
|
#
|
|
# Test DEFAULT
|
|
#
|
|
CREATE SEQUENCE s1 nocache engine=myisam;
|
|
CREATE table t1 (a int default next value for s1, b int);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT nextval(`test`.`s1`),
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert into t1 SET b=1;
|
|
insert into t1 SET b=2;
|
|
insert into t1 (b) values (3),(4);
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
update t1 set b=5 where a=1;
|
|
delete from t1 where b=1;
|
|
select * from t1;
|
|
a b
|
|
1 5
|
|
2 2
|
|
3 3
|
|
4 4
|
|
#
|
|
# Executing DEFAULT function
|
|
#
|
|
INSERT into t1 values(default(a),10);
|
|
INSERT into t1 values(default(a),default(a));
|
|
update t1 set a=default(a), b=12 where b=2;
|
|
select * from t1;
|
|
a b
|
|
1 5
|
|
8 12
|
|
3 3
|
|
4 4
|
|
5 10
|
|
6 7
|
|
select default(a), a, b from t1;
|
|
default(a) a b
|
|
9 1 5
|
|
10 8 12
|
|
11 3 3
|
|
12 4 4
|
|
13 5 10
|
|
14 6 7
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
15 1 9223372036854775806 1 1 0 0 0
|
|
select * from t1 where default(a) > 0;
|
|
a b
|
|
1 5
|
|
8 12
|
|
3 3
|
|
4 4
|
|
5 10
|
|
6 7
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
16 1 9223372036854775806 1 1 0 0 0
|
|
#
|
|
# View
|
|
#
|
|
create view v1 as select * from t1;
|
|
insert into v1 set b=20;
|
|
select * from v1;
|
|
a b
|
|
1 5
|
|
8 12
|
|
3 3
|
|
4 4
|
|
5 10
|
|
6 7
|
|
16 20
|
|
drop view v1;
|
|
#
|
|
# Alter table
|
|
#
|
|
CREATE SEQUENCE s2 nocache engine=myisam;
|
|
alter table t1 add column c int default next value for s2, add column d int default previous value for s2;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT nextval(`test`.`s1`),
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT nextval(`test`.`s2`),
|
|
`d` int(11) DEFAULT lastval(`test`.`s2`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select * from t1;
|
|
a b c d
|
|
1 5 1 1
|
|
8 12 2 2
|
|
3 3 3 3
|
|
4 4 4 4
|
|
5 10 5 5
|
|
6 7 6 6
|
|
16 20 7 7
|
|
drop sequence s2;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT nextval(`test`.`s1`),
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT nextval(`test`.`s2`),
|
|
`d` int(11) DEFAULT lastval(`test`.`s2`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t1;
|
|
drop sequence s1;
|
|
#
|
|
# LOCK TABLES
|
|
#
|
|
CREATE SEQUENCE s1 nocache engine=myisam;
|
|
CREATE table t1 (a int default next value for s1, b int);
|
|
insert into t1 (b) values (3),(4);
|
|
LOCK TABLE t1 WRITE;
|
|
insert into t1 (b) values (5),(6);
|
|
ERROR HY000: Table 's1' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
LOCK TABLE t1 WRITE, s1 WRITE;
|
|
insert into t1 (b) values (5),(6);
|
|
select default(a) from t1;
|
|
default(a)
|
|
5
|
|
6
|
|
7
|
|
8
|
|
UNLOCK TABLES;
|
|
LOCK TABLE t1 READ;
|
|
insert into t1 (b) values (5),(6);
|
|
ERROR HY000: Table 's1' was not locked with LOCK TABLES
|
|
select default(a) from t1;
|
|
ERROR HY000: Table 's1' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
LOCK TABLE t1 READ, s1 read;
|
|
insert into t1 (b) values (5),(6);
|
|
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
|
|
select default(a) from t1;
|
|
ERROR HY000: Table 's1' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
drop table t1;
|
|
drop sequence s1;
|
|
#
|
|
# Testing prepared statements
|
|
#
|
|
CREATE or replace SEQUENCE s1 nocache engine=myisam;
|
|
CREATE or replace table t1 (a int default next value for s1, b int);
|
|
PREPARE stmt FROM "insert into t1 (b) values(?)";
|
|
execute stmt using 1;
|
|
execute stmt using 2;
|
|
execute stmt using 3;
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
drop table t1,s1;
|
|
deallocate prepare stmt;
|
|
#
|
|
# Wrong usage of default
|
|
#
|
|
CREATE table t1 (a int default next value for s1, b int);
|
|
ERROR 42S02: Table 'test.s1' doesn't exist
|
|
CREATE SEQUENCE s1 nocache engine=myisam;
|
|
CREATE table t1 (a int default next value for s1, b int);
|
|
DROP SEQUENCE s1;
|
|
insert into t1 (b) values (5),(6);
|
|
ERROR 42S02: Table 'test.s1' doesn't exist
|
|
ALTER TABLE t1 add column c int;
|
|
ERROR 42S02: Table 'test.s1' doesn't exist
|
|
CREATE SEQUENCE s1 nocache engine=myisam;
|
|
ALTER TABLE t1 add column c int;
|
|
ALTER TABLE t1 add column d int default next value for s_not_exits;
|
|
ERROR 42S02: Table 'test.s_not_exits' doesn't exist
|
|
drop table t1;
|
|
drop sequence s1;
|
|
#
|
|
# MDEV 22785 Crash with prepared statements and NEXTVAL()
|
|
#
|
|
CREATE SEQUENCE s;
|
|
CREATE TABLE t1 (id int NOT NULL DEFAULT NEXTVAL(s), PRIMARY KEY (id));
|
|
PREPARE stmt FROM " INSERT INTO t1 () values ()";
|
|
INSERT INTO t1 () values ();
|
|
EXECUTE stmt;
|
|
DROP TABLE t1;
|
|
DROP SEQUENCE s;
|
|
#
|
|
# MDEV-29540 Incorrect sequence values in INSERT SELECT
|
|
#
|
|
CREATE SEQUENCE s1;
|
|
CREATE TABLE t1 (
|
|
a BIGINT UNSIGNED NOT NULL PRIMARY KEY
|
|
DEFAULT (NEXT VALUE FOR s1),
|
|
b CHAR(1) NOT NULL
|
|
);
|
|
INSERT INTO t1 (b) VALUES ('a');
|
|
INSERT INTO t1 (b) VALUES ('b'), ('c');
|
|
INSERT INTO t1 (b) VALUES ('d');
|
|
INSERT INTO t1 (b) SELECT c FROM (
|
|
SELECT 'e' as c
|
|
UNION
|
|
SELECT 'f'
|
|
UNION
|
|
SELECT 'g'
|
|
) der;
|
|
SELECT a, b FROM t1;
|
|
a b
|
|
1 a
|
|
2 b
|
|
3 c
|
|
4 d
|
|
5 e
|
|
6 f
|
|
7 g
|
|
ALTER SEQUENCE s1 RESTART;
|
|
INSERT INTO t1 (b) SELECT c FROM (
|
|
SELECT 'a' as c
|
|
UNION
|
|
SELECT 'b'
|
|
UNION
|
|
SELECT 'c'
|
|
UNION
|
|
SELECT 'd'
|
|
UNION
|
|
SELECT 'e'
|
|
UNION
|
|
SELECT 'f'
|
|
UNION
|
|
SELECT 'g'
|
|
) der;
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
ALTER SEQUENCE s1 RESTART;
|
|
INSERT IGNORE INTO t1 (b) SELECT c FROM (
|
|
SELECT 'a' as c
|
|
UNION
|
|
SELECT 'b'
|
|
UNION
|
|
SELECT 'c'
|
|
UNION
|
|
SELECT 'd'
|
|
UNION
|
|
SELECT 'e'
|
|
UNION
|
|
SELECT 'f'
|
|
UNION
|
|
SELECT 'g'
|
|
) der;
|
|
Warnings:
|
|
Warning 1062 Duplicate entry '1' for key 'PRIMARY'
|
|
Warning 1062 Duplicate entry '2' for key 'PRIMARY'
|
|
Warning 1062 Duplicate entry '3' for key 'PRIMARY'
|
|
Warning 1062 Duplicate entry '4' for key 'PRIMARY'
|
|
Warning 1062 Duplicate entry '5' for key 'PRIMARY'
|
|
Warning 1062 Duplicate entry '6' for key 'PRIMARY'
|
|
Warning 1062 Duplicate entry '7' for key 'PRIMARY'
|
|
SELECT a, b FROM t1;
|
|
a b
|
|
1 a
|
|
2 b
|
|
3 c
|
|
4 d
|
|
5 e
|
|
6 f
|
|
7 g
|
|
INSERT IGNORE INTO t1 (b) SELECT c FROM (
|
|
SELECT 'h' as c
|
|
UNION
|
|
SELECT 'i'
|
|
UNION
|
|
SELECT 'j'
|
|
) der;
|
|
SELECT a, b FROM t1;
|
|
a b
|
|
1 a
|
|
2 b
|
|
3 c
|
|
4 d
|
|
5 e
|
|
6 f
|
|
7 g
|
|
8 h
|
|
9 i
|
|
10 j
|
|
DROP TABLE t1;
|
|
DROP SEQUENCE s1;
|
|
#
|
|
# End of 10.3 tests
|
|
#
|