mariadb/mysql-test/suite
Nikita Malyavin 9b5f36a269 MDEV-36274 Implement IDENTITY column
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.
2025-06-30 19:51:44 +02:00
..
archive Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
atomic Fix the test: changing charset should be dome when we can not skip the test. 2025-05-09 07:36:15 +02:00
binlog Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
binlog_encryption Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
client MDEV-14978 fix client.client-env-variable test 2024-10-19 16:53:16 +02:00
compat Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
csv MDEV-36050 DATA/INDEX DIRECTORY handling is inconsistent 2025-04-18 09:41:23 +02:00
encryption Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
engines Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
events Merge 11.4 into 11.7 2024-12-02 17:51:17 +02:00
federated Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
funcs_1 cleanup: select ... into tests 2025-02-11 20:31:25 +01:00
funcs_2 Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
galera Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
galera_3nodes Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
galera_3nodes_sr Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
galera_sr Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
gcol MDEV-36274 Implement IDENTITY column 2025-06-30 19:51:44 +02:00
handler Merge 11.4 into 11.7 2025-01-09 09:41:38 +02:00
heap Merge branch '11.4' into 11.7 2025-02-06 16:46:36 +01:00
innodb Merge branch '11.4' into 11.8 2025-05-21 15:57:16 +02:00
innodb_fts Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
innodb_gis Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00
innodb_i_s
innodb_zip fix incorrect merge 15700f54c2 2025-04-18 09:41:24 +02:00
jp
json Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
large_tests
maria MDEV-34189 Unexpected error on WHERE inet6col 2025-01-29 09:08:19 +04:00
mariabackup Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
merge Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
mtr/t Remove dates from all rdiff files 2025-01-05 16:40:11 +02:00
mtr2
multi_source fix for post-test check of multi_source.master_info_file 2025-04-18 09:41:23 +02:00
optimizer_unfixed_bugs
parts MDEV-36050 DATA/INDEX DIRECTORY handling is inconsistent 2025-04-18 09:41:23 +02:00
perfschema Merge 11.4 into 11.8 2025-03-05 20:39:47 +02:00
perfschema_stress
period cleanup: select ... into tests 2025-02-11 20:31:25 +01:00
plugins Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
roles Merge branch '11.7' into 11.8 2025-02-11 20:29:43 +01:00
rpl Merge branch '11.4' into 11.8 2025-05-21 15:57:16 +02:00
s3 Merge branch '11.7' into 11.8 2025-02-11 20:29:43 +01:00
sql_sequence Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
storage_engine
stress
sys_vars Fix version 2025-04-29 08:27:07 +02:00
sysschema MDEV-31736: format_bytes implementation 2024-11-26 13:10:01 +04:00
unit
vcol Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
versioning MDEV-36274 Implement IDENTITY column 2025-06-30 19:51:44 +02:00
wsrep Merge 11.4 into 11.8 2025-04-02 14:07:01 +03:00