mirror of
https://github.com/MariaDB/server.git
synced 2025-10-11 10:19:13 +02:00
9b5f36a269
This patch implements basic IDENTITY feature, with the following
syntax:
<col> GENERATED {ALWAYS AS | BY DEFAULT [ON NULL]} identity_props
The following identity_props are recognized and saved in
the table metadata, but ignored
- START WITH num
- INCREMENT BY num
- MAXVALUE num
- MINVALUE num
- ORDER, NOORDER
- CYCLE, NOCYCLE
- NOMINVALUE, NO MINVALUE
- NOMAXVALUE, NO MAXVALUE
The following identity_props are newly added keywords, however they are
not reserved, i.e. can be used as identifiers, column names, etc. just
as before:
- KEEP
- SCALE
- SHARD
- EXTEND
- NOKEEP
- NOSCALE
- NOSHARD
- NOEXTEND
The patch is designed in minimalistic manner, re-using existing
AUTO_INCREMENT implementation, yet utilizing the default values
evaluation mechanism. As a result, it grants usage of CHECK constraints
and other table expressions: vcols, default values with identity column.
Some notes:
* IDENTITY is evaluated as Field's default_value. Even GENERATED ALWAYS
is considered "default", since the IDENTITY expression is not
determistic.
* However, the GENERATED ALWAYS kind cannot be assigned in DMLs in
STRICT SQL_MODE. That is, the behavior for users is like for other
GENERATED ALWAYS columns.
* It honors INSERT_ID and uses it, which is important for
statement-based replication to work.
* START WITH and INCREMENT BY are ignored. auto_increment_increment and
auto_increment_offset are being followed instead, exactly as for
AUTO_INCREMENT.
* Since IDENTITY behaves as DEFAULT, it cannot follow
NO_AUTO_VALUE_ON_ZERO and should accept user-set keyword with zeroes,
even when it is off, otherwise, `default` will not work.
See test "Zeroes and defaults handling".
* IDENTITY column is always replicated and saved in mariadb-dump, which
is guaranteed by using default_value instead of vcol_expr (vcols, even
stored, are usually not replicated).
* In frm, it is loaded and saved as a default expression. For that,
parse_vcol_expr definition was extended to support things like GENERATED
BY DEFAULT, and even GENERATED ALWAYS. That is, no EXTRA2 header had to
be added.
* AUTO_INCREMENT is not transformed into default_value and works
completely as before.
* IDENTITY should work without the key, and moreover should generate new
values without repetition. If no suitable key is created by user, one is
created implicitly.
* New IDENTITY keyword is added as not-reserved.
handler::update_auto_increment: extract a part checking for explicit
value and zeroes. Invoke only for AUTO_INCREMENT. The rest is contained
in handler::update_auto_increment_impl, which will be invoked by
IDENTITY. Add field argument to use instead of next_number_field.
Note that for IDENTITY, field is not always next_number_field
(Item_default_value can create its own field).
* show_create.cc: changes make it possible to output Field's
default_value as GENERATED BY DEFAULT, instead of DEFAULT (expr).
* Item_field::check_vcol_func_processor: allow default on IDENTITY
fields (they still have AUTO_INCREMENT properties, like unireg_check)
* Item_default_value::tie_field: forbid default(x) exprs.
Item_default_value distinguishes `default` and `default(x)` by the
vcol_assignment_ok flag.
* mysql_prepare_create_table_finalize: make sure there is at least
one key where IDENTITY field goes as a first key_part. If there's none -
create one.
Known issues:
* LAST_INSERT_ID() is not working for now.
Limitations:
* default(identity_column), is disabled, because persistent write is
not properly done in that case.
285 lines
7.3 KiB
Text
285 lines
7.3 KiB
Text
create table t(x int generated by default on null as identity, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
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;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t(x) values(default),(default),(default);
|
|
insert t(x) values(-1), (-33);
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
flush table t;
|
|
insert t() values(42);
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
42
|
|
43
|
|
alter table t force;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
42
|
|
43
|
|
44
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|
|
# Next insert id is adjusted to the explicit value
|
|
create table t(x int generated by default as identity, key(x)) AUTO_INCREMENT=10;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t(x) values(default),(20),(default);
|
|
insert t(x) values(default),(21),(default);
|
|
select * from t;
|
|
x
|
|
10
|
|
11
|
|
20
|
|
21
|
|
21
|
|
22
|
|
insert t(x) values(default),(25);
|
|
# We should see AUTO_INCREMENT=26
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|
|
# GENERATED ALWAYS
|
|
create table t(x int generated always as identity, key(x)) AUTO_INCREMENT=100;
|
|
insert into t() values();
|
|
insert into t(x) values(2);
|
|
ERROR HY000: The value specified for generated column 'x' in table 't' has been ignored
|
|
select * from t;
|
|
x
|
|
100
|
|
drop table t;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) NOT NULL GENERATED ALWAYS AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert into t() values();
|
|
select * from t;
|
|
x
|
|
100
|
|
101
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) NOT NULL GENERATED ALWAYS AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
# Expression with IDENTITY
|
|
create table t(x int generated by default as identity, key(x),
|
|
check (x % 2 != 0), y int default(x * 2), z int as (x-1));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
`y` int(11) DEFAULT (`x` * 2),
|
|
`z` int(11) GENERATED ALWAYS AS (`x` - 1) VIRTUAL,
|
|
KEY `x` (`x`),
|
|
CONSTRAINT `CONSTRAINT_1` CHECK (`x` MOD 2 <> 0)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
set statement auto_increment_increment= 2 for
|
|
insert t() values(),(),();
|
|
select * from t;
|
|
x y z
|
|
1 2 0
|
|
3 6 2
|
|
5 10 4
|
|
alter table t add check(x < 5);
|
|
ERROR 23000: CONSTRAINT `CONSTRAINT_2` failed for `test`.`t`
|
|
alter table t add check(x < 6);
|
|
drop table t;
|
|
# Zeroes and defaults handling
|
|
# default(col) is disabled for identity
|
|
create table t(x int generated by default as identity, y int default(default(x)));
|
|
ERROR HY000: Incorrect arguments to DEFAULT()
|
|
create table t(x int generated by default as identity, y int default(x));
|
|
insert t(x) values (1), (0), (default);
|
|
select * from t;
|
|
x y
|
|
1 1
|
|
0 0
|
|
2 2
|
|
select default(x) from t;
|
|
ERROR HY000: Incorrect arguments to DEFAULT()
|
|
insert t() values ();
|
|
insert t(x) values (default);
|
|
select * from t;
|
|
x y
|
|
1 1
|
|
0 0
|
|
2 2
|
|
3 3
|
|
4 4
|
|
insert t(x) values (default(x));
|
|
ERROR HY000: Incorrect arguments to DEFAULT()
|
|
update t set x= default;
|
|
update t set x= default(x);
|
|
ERROR HY000: Incorrect arguments to DEFAULT()
|
|
select * from t;
|
|
x y
|
|
5 1
|
|
6 0
|
|
7 2
|
|
8 3
|
|
9 4
|
|
update t, t as t1 set t.x=default where t.x = t1.x order by t.x;
|
|
select * from t;
|
|
x y
|
|
10 1
|
|
11 0
|
|
12 2
|
|
13 3
|
|
14 4
|
|
# Ensure that 'update t set x= default' saved the new value persistently.
|
|
flush table t;
|
|
insert t(x) values (default);
|
|
select * from t;
|
|
x y
|
|
10 1
|
|
11 0
|
|
12 2
|
|
13 3
|
|
14 4
|
|
15 15
|
|
# Values aren't changed, zeroes are preserved.
|
|
alter table t add z int, algorithm=copy;
|
|
select * from t;
|
|
x y z
|
|
10 1 NULL
|
|
11 0 NULL
|
|
12 2 NULL
|
|
13 3 NULL
|
|
14 4 NULL
|
|
15 15 NULL
|
|
drop table t;
|
|
# Syntax
|
|
create table t(x int generated by default as identity increment by -1 minvalue 9, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
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;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
create table t(x int generated by default as identity nominvalue nomaxvalue nocache, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
# 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;
|
|
# 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;
|
|
x
|
|
1
|
|
2
|
|
3
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|