mirror of
https://github.com/MariaDB/server.git
synced 2025-10-29 09:56:12 +01:00
68c6489bc7
Part of MDEV-36274 Implement IDENTITY column AUTO_INCREMENT column has to a column be a part of key in order to work. Moreover, if it's not the first key_part, its behavior is very specific, i.e. it can generate repeated values. IDENTITY should work without the key, and moreover should generate new values without repetition. On the other hand, having at least any key is currently an inevitable requirement. Instead of failing, let's implicitly generate a key if needed.
140 lines
4.1 KiB
Text
140 lines
4.1 KiB
Text
--source include/have_log_bin.inc
|
|
|
|
let $mysqldumpfile = $MYSQLTEST_VARDIR/tmp/mysqldumpfile.sql;
|
|
let $table_name = test.t;
|
|
|
|
create table t(x int generated by default on null as identity, key(x));
|
|
show create table t;
|
|
insert t(x) values(null);
|
|
drop table t;
|
|
|
|
create table t(x int generated by default as identity, key(x));
|
|
insert t() values();
|
|
show create table t;
|
|
insert t(x) values(default),(default),(default);
|
|
insert t(x) values(-1), (-33);
|
|
insert t() values();
|
|
select * from t;
|
|
flush table t;
|
|
insert t() values(42);
|
|
insert t() values();
|
|
select * from t;
|
|
alter table t force;
|
|
show create table t;
|
|
insert t() values();
|
|
select * from t;
|
|
|
|
--exec $MYSQL_DUMP test t > $mysqldumpfile
|
|
--source include/mysqldump.inc
|
|
|
|
create table t(x int generated by default as identity start with 10 increment by 101, key(x));
|
|
show create table t;
|
|
insert t() values(),(),();
|
|
show create table t;
|
|
select * from t;
|
|
insert t(x) values(-1), (-33);
|
|
insert t() values();
|
|
select * from t;
|
|
show create table t;
|
|
|
|
alter table t add y int;
|
|
show create table t;
|
|
insert t() values();
|
|
select * from t;
|
|
|
|
--exec $MYSQL_DUMP test t > $mysqldumpfile
|
|
--source include/mysqldump.inc
|
|
|
|
--echo # Next insert id is adjusted to the explicit value
|
|
create table t(x int generated by default as identity start with 10, key(x));
|
|
show create table t;
|
|
insert t(x) values(default),(20),(default);
|
|
insert t(x) values(default),(21),(default);
|
|
select * from t;
|
|
insert t(x) values(default),(25);
|
|
--echo # We should see STARTS WITH 26
|
|
show create table t;
|
|
--exec $MYSQL_DUMP test t > $mysqldumpfile
|
|
--source include/mysqldump.inc
|
|
|
|
|
|
--echo # GENERATED ALWAYS
|
|
create table t(x int generated always as identity start with 100 increment by 200, key(x));
|
|
insert into t() values();
|
|
--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
|
|
insert into t(x) values(2);
|
|
select * from t;
|
|
--exec $MYSQL_DUMP test t > $mysqldumpfile
|
|
drop table t;
|
|
--exec $MYSQL test < $mysqldumpfile
|
|
insert into t() values();
|
|
select * from t;
|
|
show create table t;
|
|
drop table t;
|
|
|
|
|
|
--echo # Expression with IDENTITY
|
|
create table t(x int generated by default as identity increment by 2, key(x),
|
|
check (x % 2 != 0), y int default(x * 2), z int as (x-1));
|
|
show create table t;
|
|
insert t() values(),(),();
|
|
select * from t;
|
|
--error ER_CONSTRAINT_FAILED
|
|
alter table t add check(x < 5);
|
|
alter table t add check(x < 6);
|
|
drop table t;
|
|
|
|
|
|
--echo # Limitations and syntax
|
|
create table t(x int generated by default as identity increment by -1 minvalue 9, key(x));
|
|
show create table t;
|
|
drop table t;
|
|
|
|
create table t(x int generated by default as identity cache -2 maxvalue -1000 start with 0, key(x));
|
|
show create table t;
|
|
drop table t;
|
|
|
|
create table t(x int generated by default as identity nominvalue nomaxvalue nocache, key(x));
|
|
show create table t;
|
|
drop table t;
|
|
|
|
create table t(x int generated by default as identity
|
|
minvalue -9999999999999999999999999999
|
|
maxvalue 9999999999999999999999999999, key(x));
|
|
show create table t;
|
|
drop table t;
|
|
|
|
create table t(x int generated by default as identity
|
|
minvalue 9999999999999999999999999999
|
|
maxvalue -9999999999999999999999999999, key(x));
|
|
show create table t;
|
|
drop table t;
|
|
|
|
create table t(x int generated by default as identity
|
|
# Some ulonglong with 64-th bit set
|
|
start with 0xd000000000000000, key(x));
|
|
show create table t;
|
|
drop table t;
|
|
|
|
|
|
--echo # Compatibility options that are ignored
|
|
create table t(x int generated by default as identity
|
|
scale extend shard extend keep, key(x));
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
scale noextend shard noextend keep, key(x));
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
scale shard keep, key(x));
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
noscale noshard nokeep, key(x));
|
|
drop table t;
|
|
|
|
|
|
--echo IDENTITY is created without specifying a key
|
|
create table t(x int generated by default as identity start with 10);
|
|
insert t() values(),(),();
|
|
select * from t;
|
|
--exec $MYSQL_DUMP test t > $mysqldumpfile
|
|
--source include/mysqldump.inc
|