mirror of
https://github.com/MariaDB/server.git
synced 2025-10-07 16:29:16 +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.
138 lines
4 KiB
Text
138 lines
4 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
|
|
|
|
--echo # 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;
|
|
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 AUTO_INCREMENT=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, key(x)) AUTO_INCREMENT=100;
|
|
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
|
|
show create table t;
|
|
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, key(x),
|
|
check (x % 2 != 0), y int default(x * 2), z int as (x-1));
|
|
show create table t;
|
|
set statement auto_increment_increment= 2 for
|
|
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 # Zeroes and defaults handling
|
|
|
|
--echo # default(col) is disabled for identity
|
|
--error ER_WRONG_ARGUMENTS
|
|
create table t(x int generated by default as identity, y int default(default(x)));
|
|
|
|
create table t(x int generated by default as identity, y int default(x));
|
|
insert t(x) values (1), (0), (default);
|
|
select * from t;
|
|
--error ER_WRONG_ARGUMENTS
|
|
select default(x) from t;
|
|
insert t() values ();
|
|
insert t(x) values (default);
|
|
select * from t;
|
|
--error ER_WRONG_ARGUMENTS
|
|
insert t(x) values (default(x));
|
|
update t set x= default;
|
|
--error ER_WRONG_ARGUMENTS
|
|
update t set x= default(x);
|
|
select * from t;
|
|
update t, t as t1 set t.x=default where t.x = t1.x order by t.x;
|
|
select * from t;
|
|
--echo # Ensure that 'update t set x= default' saved the new value persistently.
|
|
flush table t;
|
|
insert t(x) values (default);
|
|
select * from t;
|
|
|
|
--echo # Values aren't changed, zeroes are preserved.
|
|
alter table t add z int, algorithm=copy;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
--echo # 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;
|
|
|
|
|
|
--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
|