797 lines
31 KiB
Text
797 lines
31 KiB
Text
################################################################################
|
|
# #
|
|
# WL13784 - Create auto_increment PK on table creation when no PK is specified #
|
|
# #
|
|
################################################################################
|
|
|
|
# CREATE TABLE SELECT statements creating GIPKs are disallowed in SBR mode
|
|
--source include/have_binlog_format_row.inc
|
|
|
|
SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key;
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test cases to verify CREATE TABLE with sql_generate_invisible_primary_key = OFF.
|
|
--echo ##################################################################################
|
|
SET SESSION sql_generate_invisible_primary_key=OFF;
|
|
--echo # Test case to check if table is created without PK in this case.
|
|
CREATE TABLE t1(f1 INT, f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test cases to verify PK generation with sql_generate_invisible_primary_key = ON.
|
|
--echo ##################################################################################
|
|
SET SESSION sql_generate_invisible_primary_key=ON;
|
|
--echo # Test case to check table creation with an explicit primary key.
|
|
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to check temporary table creation with an explicit primary key.
|
|
CREATE TEMPORARY TABLE t1 (f1 INT PRIMARY KEY, f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify partition table creation with a primary key.
|
|
CREATE TABLE t1 (f1 INT, f2 INT PRIMARY KEY) PARTITION BY RANGE (f2)
|
|
(PARTITION p0 VALUES LESS THAN (2011),
|
|
PARTITION p1 VALUES LESS THAN (2022));
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify primary key generation on non-InnoDB engine table.
|
|
--echo # Primary key generation is supported for only InnoDB engine tables.
|
|
CREATE TABLE t1 (f1 INT, f2 INT) ENGINE = MyISAM;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify primary key generation on table with column name "my_row_id".
|
|
--error ER_GIPK_COLUMN_EXISTS
|
|
CREATE TABLE t1 (my_row_id INT, f2 INT);
|
|
|
|
--echo # Test case to verify primary key generation on table with auto_increment
|
|
--echo # column.
|
|
--error ER_GIPK_FAILED_AUTOINC_COLUMN_EXISTS
|
|
CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT, f2 INT, key(f1));
|
|
|
|
--echo # Test case to verify primary key generation on PK-less partitioned table.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
CREATE TABLE t1 (f1 INT, f2 DATE) PARTITION BY KEY(f2) PARTITIONS 2;
|
|
|
|
--echo # Test case to verify partition table creation with user defined unique
|
|
--echo # key based implicit primary key.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
CREATE TABLE t1 (f1 INT, f2 INT NOT NULL UNIQUE) PARTITION BY RANGE (f2)
|
|
(PARTITION p0 VALUES LESS THAN (2011),
|
|
PARTITION p1 VALUES LESS THAN (2022));
|
|
|
|
--echo # Test case to verify PK generation on InnoDB PK-less user table.
|
|
--echo # PK should be generated in this case.
|
|
CREATE TABLE t1(f1 INT);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify PK generation on InnoDB PK-less temporary table.
|
|
--echo # PK should be generated in this case.
|
|
CREATE TEMPORARY TABLE t1(f1 INT);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify PK generation on InnoDB table with unique key
|
|
--echo # based implicit primary key. PK should be generated in this case.
|
|
CREATE TABLE t1(f1 INT NOT NULL UNIQUE);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify PK generation on InnoDB temporary table with unique key
|
|
--echo # based implicit primary key. PK should be generated in this case.
|
|
CREATE TEMPORARY TABLE t1 (f1 INT NOT NULL UNIQUE);
|
|
SHOW CREATE TABLE t1;
|
|
SHOW KEYS FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify PK generation on InnoDB table with if not exists
|
|
--echo # clause.
|
|
CREATE TABLE t1(f1 INT);
|
|
CREATE TABLE IF NOT EXISTS t1 (f1 INT, f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TEMPORARY TABLE t2 (f1 INT);
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (f1 INT, f2 INT);
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
CREATE VIEW t1 AS SELECT 1;
|
|
CREATE TABLE IF NOT EXISTS t1 (f1 INT);
|
|
SHOW CREATE VIEW t1;
|
|
DROP VIEW t1;
|
|
|
|
--echo # Test case to verify behavior with CREATE TABLE LIKE.
|
|
--echo # If source table doesn't have primary key then primary key is *not* generated
|
|
--echo # for a target table.
|
|
SET SESSION sql_generate_invisible_primary_key=OFF;
|
|
CREATE TABLE t1(f1 INT);
|
|
SHOW CREATE TABLE t1;
|
|
SET SESSION sql_generate_invisible_primary_key=ON;
|
|
CREATE TABLE t2 LIKE t1;
|
|
SHOW CREATE TABLE t2;
|
|
|
|
--echo # With sql_require_primary_key=ON, table creation fails as primary key
|
|
--echo # is not generated for CREATE TABLE LIKE.
|
|
SET SESSION sql_require_primary_key=ON;
|
|
--error ER_TABLE_WITHOUT_PK
|
|
CREATE TABLE t3 LIKE t1;
|
|
SET SESSION sql_require_primary_key=OFF;
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case to check primary key generation with CREATE TABLE ... SELECT
|
|
CREATE TABLE t1 (f1 INT);
|
|
SHOW CREATE TABLE t1;
|
|
--echo # Primary key is generated in this case.
|
|
CREATE TABLE t2 AS SELECT * FROM t1;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
--echo # Primary key is generated in this case.
|
|
CREATE TABLE t2 (f1 INT NOT NULL UNIQUE) AS SELECT * FROM t1;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
--echo # Primary key is *not* generated in this case.
|
|
CREATE TABLE t2 (f1 INT, PRIMARY KEY(f1)) AS SELECT * FROM t1;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
--echo # Test case to verify CREATE TABLE ... SELECT with my_row_id in SELECT
|
|
--echo # column list.
|
|
--error ER_GIPK_COLUMN_EXISTS
|
|
CREATE TABLE t2 AS SELECT f1, my_row_id FROM t1;
|
|
|
|
--echo # Test case to verify CREATE TABLE ... SELECT with my_row_id in
|
|
--echo # SELECT column list and primary key on my_row_id.
|
|
CREATE TABLE t2(my_row_id INT PRIMARY KEY) AS SELECT f1, my_row_id FROM t1;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t1, t2;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify DROP COLUMN / DROP PRIMARY KEY with
|
|
--echo # sql_generate_invisible_primary_key = OFF.
|
|
--echo ##################################################################################
|
|
CREATE TABLE t1 (f1 INT);
|
|
CREATE TABLE t2 (f1 INT, f2 INT);
|
|
SET SESSION sql_generate_invisible_primary_key = OFF;
|
|
--echo # Test case to verify DROP COLUMN operation on generated primary key column.
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify DROP PRIMARY KEY operation on generated primary key. Dropping
|
|
--echo # key is allowed only if generated primary key column is dropped in the same ALTER
|
|
--echo # statement.
|
|
--error ER_DROP_PK_COLUMN_TO_DROP_GIPK
|
|
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f2);
|
|
ALTER TABLE t2 DROP PRIMARY KEY, DROP COLUMN my_row_id, ADD PRIMARY KEY (f2);
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify DROP COLUMN / DROP PRIMARY KEY with
|
|
--echo # sql_generate_invisible_primary_key = ON.
|
|
--echo ##################################################################################
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
CREATE TABLE t1 (f1 INT);
|
|
CREATE TABLE t2 (f1 INT);
|
|
CREATE TABLE t3 (f1 INT);
|
|
CREATE TEMPORARY TABLE t4 (f1 INT);
|
|
|
|
--echo # Test case to verify DROP COLUMN operation on generated primary key column. Dropping
|
|
--echo # generated primary key without adding a new primary key is not supported.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
ALTER TABLE t1 DROP COLUMN my_row_id, ADD PRIMARY KEY(f1);
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case to verify DROP PRIMARY KEY operation on generated primary key. Dropping
|
|
--echo # generated primary key without adding a new primary key is not supported.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t2 DROP PRIMARY KEY;
|
|
--echo # Test case to verify DROP PRIMARY KEY operation on generated primary key. Dropping
|
|
--echo # key is allowed only if generated primary key column is dropped in the same ALTER
|
|
--error ER_DROP_PK_COLUMN_TO_DROP_GIPK
|
|
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f1);
|
|
ALTER TABLE t2 DROP PRIMARY KEY, DROP COLUMN my_row_id, ADD PRIMARY KEY(f1);
|
|
SHOW CREATE TABLE t2;
|
|
--echo # Dropping primary key without adding a new primary key is not supported.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t2 DROP PRIMARY KEY;
|
|
DROP TABLE t2;
|
|
|
|
--echo # Dropping generated primary key without adding a new primary key is not supported.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
DROP INDEX `PRIMARY` ON t3;
|
|
DROP TABLE t3;
|
|
|
|
--echo # Test case to verify DROP PRIMARY KEY operation on generated primary key. Dropping
|
|
--echo # generated primary key without adding a new primary key is not supported even for
|
|
--echo # temporary table..
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t4 DROP PRIMARY KEY;
|
|
--echo # Test case to verify DROP PRIMARY KEY operation on generated primary key. Dropping
|
|
--echo # key is allowed only if generated primary key column is dropped in the same ALTER
|
|
--echo # for temporary table.
|
|
--error ER_DROP_PK_COLUMN_TO_DROP_GIPK
|
|
ALTER TABLE t4 DROP PRIMARY KEY, ADD PRIMARY KEY(f1);
|
|
ALTER TABLE t4 DROP PRIMARY KEY, DROP COLUMN my_row_id, ADD PRIMARY KEY(f1);
|
|
SHOW CREATE TABLE t4;
|
|
--echo # Dropping primary key without adding a new primary key is not supported.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t4 DROP PRIMARY KEY;
|
|
DROP TABLE t4;
|
|
|
|
--echo # Test case to verify DROP generated primary key with unique key based implicit
|
|
--echo # primary key addition.
|
|
CREATE TABLE t1 (f1 INT);
|
|
CREATE TEMPORARY TABLE t2 (f1 INT);
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN my_row_id, ADD UNIQUE KEY(f1);
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t2 DROP PRIMARY KEY, DROP COLUMN my_row_id, ADD UNIQUE KEY(f1);
|
|
|
|
--echo # Test case to verify ADD primary key *without* dropping generated primary key.
|
|
--error ER_MULTIPLE_PRI_KEY
|
|
ALTER TABLE t1 ADD PRIMARY KEY(f1);
|
|
--error ER_MULTIPLE_PRI_KEY
|
|
ALTER TABLE t2 ADD PRIMARY KEY(f1);
|
|
|
|
--echo # Test case to verify ADD implicit primary key *without* dropping generated primary key.
|
|
ALTER TABLE t1 ADD UNIQUE KEY(f1);
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
ALTER TABLE t2 ADD UNIQUE KEY(f1);
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify CHANGE/MODIFY/ALTER generated primary key column with
|
|
--echo # sql_generate_invisible_primary_key = OFF / ON.
|
|
--echo ##################################################################################
|
|
|
|
--let $json_label = values
|
|
--let $json_keys = value
|
|
--source include/create_json_unpacking_iterator.inc
|
|
let $json_array = [ { "value": "OFF"}, { "value": "ON"} ];
|
|
|
|
--source $json_values_start
|
|
while (!$json_values_done) {
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
CREATE TABLE t1 (f1 INT, f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
|
|
CREATE TEMPORARY TABLE t2 (f1 INT, f2 INT);
|
|
SHOW CREATE TABLE t2;
|
|
|
|
--eval SET SESSION sql_generate_invisible_primary_key = $value
|
|
|
|
--echo # Test case to verify CHANGE COLUMN operation on generated primary key column.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 CHANGE my_row_id id INT NOT NULL AUTO_INCREMENT;
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t2 CHANGE my_row_id id INT NOT NULL AUTO_INCREMENT;
|
|
|
|
--echo # Test case to verify MODIFY COLUMN operation on generated primary key column.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 MODIFY my_row_id INT NOT NULL;
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t2 MODIFY my_row_id INT NOT NULL;
|
|
|
|
--echo # Test case to verify ALTER COLUMN operation on generated primary key column.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 ALTER my_row_id SET DEFAULT 10;
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t2 ALTER my_row_id SET DEFAULT 10;
|
|
|
|
--echo # Test case to verify generated primary key column rename operation.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 RENAME COLUMN my_row_id TO id;
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t2 RENAME COLUMN my_row_id TO id;
|
|
|
|
--echo # Test case to verify generated primary key column position change.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 MODIFY my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE AFTER f2;
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t2 MODIFY my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE AFTER f2;
|
|
|
|
--echo # Altering visibility attribute of my_row_id to VISIBLE is supported.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE;
|
|
SHOW CREATE TABLE t1;
|
|
# Once my_row_id column become visible, it is permitted to change its other
|
|
# properties.
|
|
ALTER TABLE t1 CHANGE my_row_id my_row_id INT NOT NULL AUTO_INCREMENT;
|
|
ALTER TABLE t1 MODIFY my_row_id INT NOT NULL;
|
|
ALTER TABLE t1 RENAME COLUMN my_row_id TO id;
|
|
|
|
ALTER TABLE t2 ALTER my_row_id SET VISIBLE;
|
|
SHOW CREATE TABLE t2;
|
|
# Once my_row_id column become visible, it is permitted to change its other
|
|
# properties.
|
|
ALTER TABLE t2 CHANGE my_row_id my_row_id INT NOT NULL AUTO_INCREMENT;
|
|
ALTER TABLE t2 MODIFY my_row_id INT NOT NULL;
|
|
ALTER TABLE t2 RENAME COLUMN my_row_id TO id;
|
|
DROP TABLE t1, t2;
|
|
|
|
--source $json_values_next
|
|
}
|
|
--source include/destroy_json_functions.inc
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify altering table storage engine.
|
|
--echo ##################################################################################
|
|
CREATE TABLE t1 (f1 INT);
|
|
SHOW CREATE TABLE t1;
|
|
--echo # Altering table engine to engine for which primary key generations is not
|
|
--echo # supported works. But column and key are not considered as a generated primary
|
|
--echo # key.
|
|
ALTER TABLE t1 ENGINE='MyISAM';
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
SHOW CREATE TABLE t1;
|
|
|
|
ALTER TABLE t1 ENGINE='InnoDB';
|
|
ALTER TABLE t1 ENGINE='MYISAM',
|
|
ADD COLUMN my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE FIRST,
|
|
ADD PRIMARY KEY(my_row_id);
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TEMPORARY TABLE t1 (f1 INT);
|
|
ALTER TABLE t1 ENGINE='MyISAM';
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
SHOW CREATE TABLE t1;
|
|
|
|
ALTER TABLE t1 ENGINE='InnoDB';
|
|
ALTER TABLE t1 ENGINE='MYISAM',
|
|
ADD COLUMN my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE FIRST,
|
|
ADD PRIMARY KEY(my_row_id);
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify altering table's storage engine to the engine for which
|
|
--echo # generated invisible primary key is supported.
|
|
--echo ##################################################################################
|
|
CREATE TABLE t1(my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE,
|
|
f1 INT, primary key(my_row_id)) ENGINE = 'MyISAM';
|
|
ALTER TABLE t1 ENGINE='InnoDB';
|
|
# In this case primary key is treated as generated primary key.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
|
|
ALTER TABLE t1 ENGINE='MYISAM', ALTER my_row_id SET VISIBLE;
|
|
ALTER TABLE t1 ENGINE='InnoDB', ALTER my_row_id SET INVISIBLE;
|
|
# In this case primary key is treated as generated primary key.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TEMPORARY TABLE t1(my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE,
|
|
f1 INT, primary key(my_row_id)) ENGINE = 'MyISAM';
|
|
ALTER TABLE t1 ENGINE='InnoDB';
|
|
# In this case primary key is treated as generated primary key.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
|
|
ALTER TABLE t1 ENGINE='MYISAM', ALTER my_row_id SET VISIBLE;
|
|
ALTER TABLE t1 ENGINE='InnoDB', ALTER my_row_id SET INVISIBLE;
|
|
# In this case primary key is treated as generated primary key.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 DROP COLUMN my_row_id;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify ADD NEW column at first position of a table with
|
|
--echo # generated primary key column.
|
|
--echo ##################################################################################
|
|
CREATE TABLE t1 (f2 INT, f3 INT);
|
|
CREATE TEMPORARY TABLE t2 (f2 INT, f3 INT);
|
|
--echo # Column should be added after generated primary key column.
|
|
ALTER TABLE t1 ADD COLUMN f1 INT FIRST;
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t2 ADD COLUMN f1 INT FIRST;
|
|
SHOW CREATE TABLE t2;
|
|
|
|
--echo # Test case to verify ADD NEW column at first position after setting
|
|
--echo # generated primary column as VISIBLE.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE;
|
|
--echo # Column should be added at the first position.
|
|
ALTER TABLE t1 ADD COLUMN f0 INT FIRST;
|
|
SHOW CREATE TABLE t1;
|
|
--echo # my_row_id column is *not* treated as generated primary key column as it is not
|
|
--echo # a first column.
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE;
|
|
ALTER TABLE t1 MODIFY my_row_id INT NOT NULL AUTO_INCREMENT;
|
|
SHOW CREATE TABLE t1;
|
|
|
|
ALTER TABLE t1 DROP COLUMN f0, MODIFY my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE;
|
|
--echo # my_row_id column is treated as a generated primary key column.
|
|
--echo # Column should be added after primary key column.
|
|
ALTER TABLE t1 ADD COLUMN f0 INT FIRST;
|
|
SHOW CREATE TABLE t1;
|
|
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE;
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE;
|
|
--echo # my_row_id column is treated as a generated primary key column.
|
|
--echo # Column should be added after primary key column.
|
|
ALTER TABLE t1 ADD COLUMN f INT FIRST;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
ALTER TABLE t2 ALTER my_row_id SET VISIBLE;
|
|
--echo # Column should be added at first position.
|
|
ALTER TABLE t2 ADD COLUMN f0 INT FIRST;
|
|
SHOW CREATE TABLE t2;
|
|
--echo # my_row_id column is *not* treated as generated primary key column as it is not
|
|
--echo # a first column.
|
|
ALTER TABLE t2 ALTER my_row_id SET INVISIBLE;
|
|
ALTER TABLE t2 MODIFY my_row_id INT NOT NULL AUTO_INCREMENT;
|
|
SHOW CREATE TABLE t2;
|
|
ALTER TABLE t2 DROP COLUMN f0, MODIFY my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE;
|
|
--echo # my_row_id column is treated as a generated primary key column. Column should
|
|
--echo # be added after primary key column.
|
|
ALTER TABLE t2 ADD COLUMN f0 INT FIRST;
|
|
SHOW CREATE TABLE t2;
|
|
|
|
ALTER TABLE t2 ALTER my_row_id SET VISIBLE;
|
|
ALTER TABLE t2 ALTER my_row_id SET INVISIBLE;
|
|
--echo # my_row_id column is treated as a generated primary key column. Column should
|
|
--echo # be added after primary key column.
|
|
ALTER TABLE t2 ADD COLUMN f INT FIRST;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
|
|
--echo # Test case to verify ADD NEW column at first position with generated primary
|
|
--echo # key DROP operation in the same ALTER.
|
|
CREATE TABLE t1(f2 INT);
|
|
ALTER TABLE t1 ADD COLUMN f1 INT PRIMARY KEY FIRST, DROP PRIMARY KEY,
|
|
DROP COLUMN my_row_id;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TEMPORARY TABLE t1(f2 INT);
|
|
ALTER TABLE t1 ADD COLUMN f1 INT PRIMARY KEY FIRST, DROP PRIMARY KEY,
|
|
DROP COLUMN my_row_id;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify creation of primary key and column with same properties as
|
|
--echo # generated invisible primary key.
|
|
--echo ##################################################################################
|
|
SET SESSION sql_generate_invisible_primary_key = OFF;
|
|
CREATE TABLE t1(f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t1 ADD COLUMN my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE FIRST,
|
|
ADD PRIMARY KEY(my_row_id);
|
|
SHOW CREATE TABLE t1;
|
|
|
|
--echo # user added column my_row_id is treated as generated invisible primary key column
|
|
--echo # with sql_generate_invisible_primary_key = OFF / ON.
|
|
--echo # Column f1 is added after my_row_id.
|
|
ALTER TABLE t1 ADD COLUMN f1 INT FIRST;
|
|
SHOW CREATE TABLE t1;
|
|
--echo # my_row_id column alter is not permitted.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 MODIFY my_row_id INT;
|
|
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
--echo # Column f0 is added after my_row_id.
|
|
ALTER TABLE t1 ADD COLUMN f0 INT FIRST;
|
|
SHOW CREATE TABLE t1;
|
|
--echo # my_row_id column alter is not permitted.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 MODIFY my_row_id INT;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify partitioning table with generated invisible primary key.
|
|
--echo ##################################################################################
|
|
CREATE TABLE t1 (f1 INT);
|
|
SHOW CREATE TABLE t1;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 PARTITION BY KEY(my_row_id) PARTITIONS 10;
|
|
SET SESSION sql_generate_invisible_primary_key = OFF;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 PARTITION BY KEY(my_row_id) PARTITIONS 10;
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify creation of primary key and column with same properties as
|
|
--echo # generated invisible primary key after removing partitioning for the table.
|
|
--echo ##################################################################################
|
|
SET SESSION sql_generate_invisible_primary_key = OFF;
|
|
CREATE TABLE t1 (f1 INT) PARTITION BY RANGE(f1) (PARTITION p1 VALUES LESS THAN (1991));
|
|
SHOW CREATE TABLE t1;
|
|
ALTER TABLE t1 REMOVE PARTITIONING;
|
|
ALTER TABLE t1 ADD COLUMN my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE FIRST,
|
|
ADD PRIMARY KEY(my_row_id);
|
|
|
|
# my_row_id column alter is not permitted.
|
|
--error ER_GIPK_COLUMN_ALTER_NOT_ALLOWED
|
|
ALTER TABLE t1 MODIFY my_row_id INT;
|
|
# table has generated primary key now, table partitioning is not supported.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER TABLE t1 PARTITION BY KEY(my_row_id) PARTITIONS 10;
|
|
|
|
DROP TABLE t1;
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify primary key generation in stored procedures.
|
|
--echo ##################################################################################
|
|
DELIMITER $;
|
|
CREATE PROCEDURE p1() BEGIN
|
|
CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT);
|
|
SHOW CREATE TABLE t1;
|
|
|
|
CREATE TABLE t2 (f1 INT);
|
|
SHOW CREATE TABLE t2;
|
|
|
|
CREATE TABLE t3 AS SELECT * FROM t2;
|
|
SHOW CREATE TABLE t3;
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
end
|
|
$
|
|
DELIMITER ;$
|
|
|
|
CALL p1();
|
|
CALL p1();
|
|
|
|
SET SESSION sql_generate_invisible_primary_key = OFF;
|
|
call p1();
|
|
call p1();
|
|
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
call p1();
|
|
call p1();
|
|
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify primary key generation with prepared statements
|
|
--echo ##################################################################################
|
|
CREATE TABLE t(f1 INT);
|
|
PREPARE stmt1 FROM 'CREATE TABLE t1(f1 INT, f2 INT)';
|
|
PREPARE stmt2 FROM 'CREATE TABLE t2 AS SELECT * FROM t';
|
|
|
|
EXECUTE stmt1;
|
|
EXECUTE stmt2;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SHOW CREATE TABLE t2;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
EXECUTE stmt1;
|
|
EXECUTE stmt2;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t1, t2;
|
|
|
|
SET SESSION sql_generate_invisible_primary_key = OFF;
|
|
EXECUTE stmt1;
|
|
EXECUTE stmt2;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t1, t2;
|
|
|
|
SET SESSION sql_generate_invisible_primary_key = ON;
|
|
EXECUTE stmt1;
|
|
EXECUTE stmt2;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SHOW CREATE TABLE t2;
|
|
|
|
DROP TABLE t, t1, t2;
|
|
DROP PREPARE stmt1;
|
|
DROP PREPARE stmt2;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify INFORMATION_SCHEMA table and SHOW resultset with system
|
|
--echo # variable show_gipk_in_create_table_and_information_schema = OFF/ON.
|
|
--echo ##################################################################################
|
|
DELIMITER $;
|
|
CREATE PROCEDURE run_show_and_i_s_stmts() BEGIN
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
SHOW EXTENDED COLUMNS FROM t1;
|
|
SHOW KEYS FROM t1;
|
|
|
|
-- I_S.TABLES
|
|
SELECT TABLE_NAME, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_NAME='t1';
|
|
-- I_S.COLUMNS
|
|
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, EXTRA
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME='t1' ORDER BY ORDINAL_POSITION;
|
|
-- I_S.COLUMN_EXTENSIONS
|
|
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS_EXTENSIONS
|
|
WHERE TABLE_NAME='t1' ORDER BY COLUMN_NAME;
|
|
-- I_S.STATISTICS
|
|
SELECT INDEX_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_NAME='t1' ORDER BY INDEX_NAME, COLUMN_NAME;
|
|
-- I_S.KEY_COLUMN_USAGE
|
|
SELECT CONSTRAINT_NAME, COLUMN_NAME
|
|
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='t1'
|
|
ORDER BY CONSTRAINT_NAME, COLUMN_NAME;
|
|
-- I_S.TABLE_CONSTRAINTS
|
|
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
|
|
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'
|
|
ORDER BY CONSTRAINT_NAME;
|
|
-- I_S.TABLE_CONSTRAINTS_EXTENSIONS
|
|
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS_EXTENSIONS
|
|
WHERE TABLE_NAME='t1' ORDER BY CONSTRAINT_NAME;
|
|
END;
|
|
$
|
|
DELIMITER ;$
|
|
|
|
--let $json_label = hide_gipk_tests
|
|
--let $json_keys = LABEL, INIT_STMTS, CHECK_STMTS
|
|
--source include/create_json_unpacking_iterator.inc
|
|
let $json_array = [ { "LABEL" : "empty Base Table",
|
|
"INIT_STMTS" : "CREATE TABLE t1(f1 INT, f2 INT UNIQUE)",
|
|
"CHECK_STMTS": "CALL run_show_and_i_s_stmts()"},
|
|
{ "LABEL" : "Base Table",
|
|
"INIT_STMTS" : "CREATE TABLE t1(f1 INT, f2 INT UNIQUE); INSERT INTO t1 VALUES(1, 3)",
|
|
"CHECK_STMTS": "SHOW CREATE TABLE t1; SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'"},
|
|
{ "LABEL" : "empty Temporary Table",
|
|
"INIT_STMTS" : "CREATE TEMPORARY TABLE t1(f1 INT, f2 INT UNIQUE)",
|
|
"CHECK_STMTS": "SHOW CREATE TABLE t1; SHOW COLUMNS FROM t1; SHOW EXTENDED COLUMNS FROM t1; SHOW KEYS FROM t1"},
|
|
{ "LABEL" : "Temporary Table",
|
|
"INIT_STMTS" : "CREATE TEMPORARY TABLE t1(f1 INT, f2 INT UNIQUE); INSERT INTO t1 VALUES(1,3)",
|
|
"CHECK_STMTS": "SHOW CREATE TABLE t1"}
|
|
];
|
|
|
|
--source $json_hide_gipk_tests_start
|
|
while (!$json_hide_gipk_tests_done) {
|
|
--echo # INFORMATION_SCHEMA and SHOW resultset for a $LABEL with
|
|
--echo # show_gipk_in_create_table_and_information_schema = ON/OFF
|
|
--eval $INIT_STMTS
|
|
|
|
--echo # With show_gipk_in_create_table_and_information_schema = ON generated invisible
|
|
--echo # primary key definitions should be listed.
|
|
SET SESSION show_gipk_in_create_table_and_information_schema = ON;
|
|
--eval $CHECK_STMTS
|
|
|
|
--echo # With show_gipk_in_create_table_and_information_schema = OFF generated invisible
|
|
--echo # primary key definitions *not* should be listed.
|
|
SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
|
|
--eval $CHECK_STMTS
|
|
|
|
--echo # With show_gipk_in_create_table_and_information_schema = OFF, after moving table
|
|
--echo # to engine for which feature is not supported. Primary key definitions should be listed.
|
|
ALTER TABLE t1 ENGINE='MyISAM';
|
|
--eval $CHECK_STMTS
|
|
|
|
--echo # With show_gipk_in_create_table_and_information_schema = OFF, after moving table
|
|
--echo # to engine for which feature is supported. Generated invisible primary key definition
|
|
--echo # should *not* be listed.
|
|
ALTER TABLE t1 ENGINE='InnoDB';
|
|
--eval $CHECK_STMTS
|
|
|
|
DROP TABLE t1;
|
|
--source $json_hide_gipk_tests_next
|
|
}
|
|
--source include/destroy_json_functions.inc
|
|
|
|
DROP PROCEDURE run_show_and_i_s_stmts;
|
|
|
|
|
|
--echo ##################################################################################
|
|
--echo # Test case to verify altering table my_row_id column visibility with all
|
|
--echo # alter algorithms.
|
|
--echo ##################################################################################
|
|
SET @saved_session_show_gipk_in_create_table_and_information_schema =
|
|
@@session.show_gipk_in_create_table_and_information_schema;
|
|
|
|
SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
|
|
CREATE TABLE t1 (f1 INT);
|
|
INSERT INTO t1 VALUES (1), (3), (7), (4), (8);
|
|
|
|
--echo # Change my_row_id column visibility to VISIBLE with INSTANT algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE, ALGORITHM=INSTANT;
|
|
--echo # Primary key should be listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
--echo # Change my_row_id column visibility to INVISIBLE with INSTANT algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE, ALGORITHM=INSTANT;
|
|
--echo # Primary key should be *not* listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
|
|
--echo # Change my_row_id column visibility to VISIBLE with allowed instant alter
|
|
--echo # operation column rename using INSTANT algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE, RENAME COLUMN f1 to f2,
|
|
ALGORITHM=INSTANT;
|
|
--echo # Primary key should be listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
--echo # Change my_row_id column visibility to INVISIBLE with allowed instant alter
|
|
--echo # operation column rename using INSTANT algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE, RENAME COLUMN f2 to f1,
|
|
ALGORITHM=INSTANT;
|
|
--echo # Primary key should be *not* listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
|
|
--echo # Change my_row_id column visibility to VISIBLE with INPLACE algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE, ALGORITHM=INPLACE;
|
|
--echo # Primary key should be listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
--echo # Change my_row_id column visibility to INVISIBLE with INPLACE algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE, ALGORITHM=INPLACE;
|
|
--echo # Primary key should be *not* listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
|
|
--echo # Change my_row_id column visibility to VISIBLE with allowed inplace alter
|
|
--echo # operation using INPLACE algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE, ADD INDEX (f1), ALGORITHM=INPLACE;
|
|
--echo # Primary key should be listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
--echo # Change my_row_id column visibility to INVISIBLE with allowed inplace alter
|
|
--echo # operation using INPLACE algorithm.
|
|
ALTER TABLE t1 DROP INDEX f1;
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE, ADD INDEX(f1),
|
|
ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 DROP INDEX f1;
|
|
--echo # Primary key should be *not* listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
|
|
--echo # Change my_row_id column visibility to VISIBLE with COPY algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET VISIBLE, ALGORITHM=COPY;
|
|
--echo # Primary key should be listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
--echo # Change my_row_id column visibility to INVISIBLE with COPY algorithm.
|
|
ALTER TABLE t1 ALTER my_row_id SET INVISIBLE, ALGORITHM=COPY;
|
|
--echo # Primary key should be *not* listed.
|
|
SHOW CREATE TABLE t1;
|
|
SHOW COLUMNS FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
SET SESSION show_gipk_in_create_table_and_information_schema =
|
|
@saved_session_show_gipk_in_create_table_and_information_schema;
|
|
|
|
SET SESSION sql_generate_invisible_primary_key =
|
|
@saved_session_sql_generate_invisible_primary_key;
|