innodb/r/xtradb_compressed_columns_virtual.result

SET @saved_innodb_compressed_columns_zip_level = @@global.innodb_compressed_columns_zip_level;
CREATE COMPRESSION_DICTIONARY numbers('one' 'two' 'three');
SET GLOBAL innodb_compressed_columns_zip_level = 0;
SET GLOBAL innodb_compressed_columns_zip_level = DEFAULT;
CREATE TABLE t1(
id INT,
a BLOB,
g BLOB GENERATED ALWAYS AS (a) COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers
) ENGINE=InnoDB;
ERROR HY000: Can not define column 'g' in compressed format
CREATE TABLE t1(
id INT,
a BLOB,
g BLOB GENERATED ALWAYS AS (a) VIRTUAL COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers
) ENGINE=InnoDB;
ERROR HY000: Can not define column 'g' in compressed format
CREATE TABLE t1(
id INT,
a BLOB,
g BLOB GENERATED ALWAYS AS (a) STORED COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers
) ENGINE=InnoDB;
SET @long_value = REPEAT('a', 1024);
INSERT INTO t1 VALUES (1, @long_value, DEFAULT);
include/assert.inc [the initial value must not be corrupted]
ALTER TABLE t1 MODIFY g BLOB GENERATED ALWAYS AS (a) STORED COLUMN_FORMAT DEFAULT, ALGORITHM = INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
ALTER TABLE t1 MODIFY g BLOB GENERATED ALWAYS AS (a) VIRTUAL;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
ALTER TABLE t1 MODIFY g BLOB GENERATED ALWAYS AS (a) STORED COLUMN_FORMAT DEFAULT;
include/assert.inc [the value after removing COMPRESSED attribute must not be corrupted]
ALTER TABLE t1 MODIFY g BLOB GENERATED ALWAYS AS (a) STORED COLUMN_FORMAT COMPRESSED;
include/assert.inc [the value after adding COMPRESSED attribute back must not be corrupted]
ALTER TABLE t1 ADD another_g BLOB GENERATED ALWAYS AS (a) VIRTUAL COLUMN_FORMAT COMPRESSED;
ERROR HY000: Can not define column 'another_g' in compressed format
ALTER TABLE t1 ADD another_g BLOB GENERATED ALWAYS AS (a) VIRTUAL;
ALTER TABLE t1 MODIFY another_g BLOB GENERATED ALWAYS AS (a) VIRTUAL COLUMN_FORMAT COMPRESSED;
ERROR HY000: Can not define column 'another_g' in compressed format
ALTER TABLE t1 MODIFY another_g BLOB GENERATED ALWAYS AS (a) STORED COLUMN_FORMAT COMPRESSED;
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
ALTER TABLE t1 DROP another_g;
SET @long_value = REPEAT('z', 1024);
UPDATE t1 SET a = @long_value WHERE id = 1;
include/assert.inc [the value after updating existing record must not be corrupted]
DELETE FROM t1 WHERE id = 1;
include/assert.inc [the table must be empty after removing existing record]
DROP TABLE t1;
SET GLOBAL innodb_compressed_columns_zip_level = @saved_innodb_compressed_columns_zip_level;
CREATE TABLE t1(
f1 INT PRIMARY KEY,
f2 BLOB COLUMN_FORMAT COMPRESSED,
f3 BLOB GENERATED ALWAYS AS (f2) VIRTUAL,
INDEX(f3(200))
) ROW_FORMAT=COMPRESSED, ENGINE=InnoDB;
INSERT INTO t1 (f1, f2) VALUES (1, REPEAT('a', 96));
ALTER TABLE t1 ROW_FORMAT=COMPACT;
DELETE FROM t1 WHERE f1 = 1;
DROP TABLE t1;
CREATE TABLE t (
a BLOB COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers,
c BLOB GENERATED ALWAYS AS (a) VIRTUAL,
INDEX(c(100))
) ENGINE=InnoDB;
INSERT INTO t VALUES (REPEAT('a', 16000), DEFAULT);
SET GLOBAL innodb_purge_stop_now = 1;
SET GLOBAL debug="+d,ib_purge_virtual_index_callback";
UPDATE t SET a = REPEAT('m', 16000) WHERE a LIKE "aaa%";
SET GLOBAL innodb_purge_run_now = 1;
SET GLOBAL innodb_purge_stop_now = 1;
SET GLOBAL debug="-d,ib_purge_virtual_index_callback";
SET GLOBAL innodb_purge_run_now = 1;
DROP TABLE t;
SET GLOBAL innodb_purge_stop_now = 1;
CREATE TABLE t1(
id INT not null,
a BLOB NOT NULL COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers,
vchar CHAR(2) AS (SUBSTR(a, 2, 2)) VIRTUAL,
INDEX(vchar)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, REPEAT('a',8000), DEFAULT);
UPDATE t1 SET id = 5 where id = 1;
DELETE FROM t1 WHERE id = 5;
SET GLOBAL innodb_purge_run_now = 1;
SET GLOBAL innodb_fast_shutdown = 0;
# restart
SET GLOBAL innodb_purge_stop_now = 1;
DROP TABLE t1;
SET GLOBAL innodb_purge_run_now = 1;
DROP COMPRESSION_DICTIONARY numbers;