diff options
Diffstat (limited to 'mysql-test/main/column_compression.result')
| -rw-r--r-- | mysql-test/main/column_compression.result | 2933 |
1 files changed, 2933 insertions, 0 deletions
diff --git a/mysql-test/main/column_compression.result b/mysql-test/main/column_compression.result new file mode 100644 index 00000000..7d846cae --- /dev/null +++ b/mysql-test/main/column_compression.result @@ -0,0 +1,2933 @@ +SET column_compression_zlib_wrap=true; +FLUSH STATUS; +CREATE TABLE t1(a BLOB COMPRESSED, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a BLOB COMPRESSED); +ALTER TABLE t1 ADD KEY(a(10)); +ERROR HY000: Compressed column 'a' can't be used in key specification +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Make sure column was actually compressed +INSERT INTO t1 VALUES(REPEAT('a', 1000)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 2 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure ALTER TABLE rebuilds table +ALTER TABLE t1 MODIFY COLUMN a BLOB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 3 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +1008 +# Rebuild back +ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 5 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure CREATE TABLE ... LIKE inherits compression +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +# Make sure implicit CREATE TABLE ... SELECT inherits compression +CREATE TABLE t2 SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 7 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +28 +DROP TABLE t2; +# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression +CREATE TABLE t2(a BLOB) SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 8 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +1008 +DROP TABLE t2; +# Make sure engine change works +ALTER TABLE t1 ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 10 +# Make sure online add column works (requires InnoDB) +ALTER TABLE t1 ADD COLUMN b BLOB COMPRESSED DEFAULT "must be visible"; +SELECT LEFT(a, 10), LENGTH(a), b FROM t1; +LEFT(a, 10) LENGTH(a) b +aaaaaaaaaa 1000 must be visible +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 12 +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ENGINE=MyISAM; +TRUNCATE TABLE t1; +# Make sure column_compression_zlib_level works +SET column_compression_zlib_level= 1; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 3 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH < 100 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH < 100 +1 +TRUNCATE TABLE t1; +SET column_compression_zlib_level= 9; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +36 +SET column_compression_zlib_level= DEFAULT; +TRUNCATE TABLE t1; +# No compression, original data shorter than compressed +INSERT INTO t1 VALUES('a'); +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +a 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +20 +# Coverage for store(double) and store(longlong) +INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); +# and for sort_string() +SELECT * FROM t1 ORDER BY a; +a +NULL + +1 +3.14 +9.8 +a +# Coverage for val_real() and val_int() +SELECT a+1 FROM t1 ORDER BY 1; +a+1 +NULL +1 +1 +2 +4.140000000000001 +10.8 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +DROP TABLE t1; +# +# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual +# Field* Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c1 BLOB COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 BLOB COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); +SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); +c1 +foo +bar +DROP TABLE t1, t2; +# +# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' +# failed in Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c BLOB COMPRESSED) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT DISTINCT c FROM t1; +c +foo +bar +DROP TABLE t1; +FLUSH STATUS; +CREATE TABLE t1(a TEXT COMPRESSED, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a TEXT COMPRESSED); +ALTER TABLE t1 ADD KEY(a(10)); +ERROR HY000: Compressed column 'a' can't be used in key specification +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Make sure column was actually compressed +INSERT INTO t1 VALUES(REPEAT('a', 1000)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 2 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure ALTER TABLE rebuilds table +ALTER TABLE t1 MODIFY COLUMN a TEXT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 3 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +1008 +# Rebuild back +ALTER TABLE t1 MODIFY COLUMN a TEXT COMPRESSED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 5 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure CREATE TABLE ... LIKE inherits compression +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +# Make sure implicit CREATE TABLE ... SELECT inherits compression +CREATE TABLE t2 SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 7 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +28 +DROP TABLE t2; +# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression +CREATE TABLE t2(a TEXT) SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 8 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +1008 +DROP TABLE t2; +# Make sure engine change works +ALTER TABLE t1 ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 10 +# Make sure online add column works (requires InnoDB) +ALTER TABLE t1 ADD COLUMN b TEXT COMPRESSED DEFAULT "must be visible"; +SELECT LEFT(a, 10), LENGTH(a), b FROM t1; +LEFT(a, 10) LENGTH(a) b +aaaaaaaaaa 1000 must be visible +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 12 +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ENGINE=MyISAM; +TRUNCATE TABLE t1; +# Make sure column_compression_zlib_level works +SET column_compression_zlib_level= 1; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 3 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH < 100 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH < 100 +1 +TRUNCATE TABLE t1; +SET column_compression_zlib_level= 9; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +36 +SET column_compression_zlib_level= DEFAULT; +TRUNCATE TABLE t1; +# No compression, original data shorter than compressed +INSERT INTO t1 VALUES('a'); +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +a 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +20 +# Coverage for store(double) and store(longlong) +INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); +# and for sort_string() +SELECT * FROM t1 ORDER BY a; +a +NULL + +1 +3.14 +9.8 +a +# Coverage for val_real() and val_int() +SELECT a+1 FROM t1 ORDER BY 1; +a+1 +NULL +1 +1 +2 +4.140000000000001 +10.8 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +DROP TABLE t1; +# +# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual +# Field* Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c1 TEXT COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 TEXT COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); +SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); +c1 +foo +bar +DROP TABLE t1, t2; +# +# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' +# failed in Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c TEXT COMPRESSED) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT DISTINCT c FROM t1; +c +foo +bar +DROP TABLE t1; +FLUSH STATUS; +CREATE TABLE t1(a VARBINARY(10000) COMPRESSED, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a VARBINARY(10000) COMPRESSED); +ALTER TABLE t1 ADD KEY(a(10)); +ERROR HY000: Compressed column 'a' can't be used in key specification +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Make sure column was actually compressed +INSERT INTO t1 VALUES(REPEAT('a', 1000)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 2 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure ALTER TABLE rebuilds table +ALTER TABLE t1 MODIFY COLUMN a VARBINARY(10000); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10000) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 3 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +1008 +# Rebuild back +ALTER TABLE t1 MODIFY COLUMN a VARBINARY(10000) COMPRESSED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 5 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure CREATE TABLE ... LIKE inherits compression +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +# Make sure implicit CREATE TABLE ... SELECT inherits compression +CREATE TABLE t2 SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 7 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +28 +DROP TABLE t2; +# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression +CREATE TABLE t2(a VARBINARY(10000)) SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varbinary(10000) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 8 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +1008 +DROP TABLE t2; +# Make sure engine change works +ALTER TABLE t1 ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 10 +# Make sure online add column works (requires InnoDB) +ALTER TABLE t1 ADD COLUMN b VARBINARY(10000) COMPRESSED DEFAULT "must be visible"; +SELECT LEFT(a, 10), LENGTH(a), b FROM t1; +LEFT(a, 10) LENGTH(a) b +aaaaaaaaaa 1000 must be visible +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 12 +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ENGINE=MyISAM; +TRUNCATE TABLE t1; +# Make sure column_compression_zlib_level works +SET column_compression_zlib_level= 1; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 3 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH < 100 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH < 100 +1 +TRUNCATE TABLE t1; +SET column_compression_zlib_level= 9; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +32 +SET column_compression_zlib_level= DEFAULT; +TRUNCATE TABLE t1; +# No compression, original data shorter than compressed +INSERT INTO t1 VALUES('a'); +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +a 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +20 +# Coverage for store(double) and store(longlong) +INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); +# and for sort_string() +SELECT * FROM t1 ORDER BY a; +a +NULL + +1 +3.14 +9.8 +a +# Coverage for val_real() and val_int() +SELECT a+1 FROM t1 ORDER BY 1; +a+1 +NULL +1 +1 +2 +4.140000000000001 +10.8 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +DROP TABLE t1; +# +# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual +# Field* Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c1 VARBINARY(10000) COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 VARBINARY(10000) COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); +SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); +c1 +foo +bar +DROP TABLE t1, t2; +# +# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' +# failed in Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c VARBINARY(10000) COMPRESSED) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT DISTINCT c FROM t1; +c +foo +bar +DROP TABLE t1; +FLUSH STATUS; +CREATE TABLE t1(a VARCHAR(10000) COMPRESSED, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a VARCHAR(10000) COMPRESSED); +ALTER TABLE t1 ADD KEY(a(10)); +ERROR HY000: Compressed column 'a' can't be used in key specification +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Make sure column was actually compressed +INSERT INTO t1 VALUES(REPEAT('a', 1000)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 2 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure ALTER TABLE rebuilds table +ALTER TABLE t1 MODIFY COLUMN a VARCHAR(10000); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10000) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 3 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +1008 +# Rebuild back +ALTER TABLE t1 MODIFY COLUMN a VARCHAR(10000) COMPRESSED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 5 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +# Make sure CREATE TABLE ... LIKE inherits compression +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +# Make sure implicit CREATE TABLE ... SELECT inherits compression +CREATE TABLE t2 SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 7 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +28 +DROP TABLE t2; +# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression +CREATE TABLE t2(a VARCHAR(10000)) SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(10000) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 8 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +1008 +DROP TABLE t2; +# Make sure engine change works +ALTER TABLE t1 ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 10 +# Make sure online add column works (requires InnoDB) +ALTER TABLE t1 ADD COLUMN b VARCHAR(10000) COMPRESSED DEFAULT "must be visible"; +SELECT LEFT(a, 10), LENGTH(a), b FROM t1; +LEFT(a, 10) LENGTH(a) b +aaaaaaaaaa 1000 must be visible +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 12 +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ENGINE=MyISAM; +TRUNCATE TABLE t1; +# Make sure column_compression_zlib_level works +SET column_compression_zlib_level= 1; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 3 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH < 100 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH < 100 +1 +TRUNCATE TABLE t1; +SET column_compression_zlib_level= 9; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +32 +SET column_compression_zlib_level= DEFAULT; +TRUNCATE TABLE t1; +# No compression, original data shorter than compressed +INSERT INTO t1 VALUES('a'); +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +a 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +20 +# Coverage for store(double) and store(longlong) +INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); +# and for sort_string() +SELECT * FROM t1 ORDER BY a; +a +NULL + +1 +3.14 +9.8 +a +# Coverage for val_real() and val_int() +SELECT a+1 FROM t1 ORDER BY 1; +a+1 +NULL +1 +1 +2 +4.140000000000001 +10.8 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +DROP TABLE t1; +# +# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual +# Field* Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c1 VARCHAR(10000) COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 VARCHAR(10000) COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); +SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); +c1 +foo +bar +DROP TABLE t1, t2; +# +# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' +# failed in Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c VARCHAR(10000) COMPRESSED) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT DISTINCT c FROM t1; +c +foo +bar +DROP TABLE t1; +FLUSH STATUS; +CREATE TABLE t1(a TEXT COMPRESSED CHARSET ucs2, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a TEXT COMPRESSED CHARSET ucs2); +ALTER TABLE t1 ADD KEY(a(10)); +ERROR HY000: Compressed column 'a' can't be used in key specification +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Make sure column was actually compressed +INSERT INTO t1 VALUES(REPEAT('a', 1000)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 2000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 2 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +36 +# Make sure ALTER TABLE rebuilds table +ALTER TABLE t1 MODIFY COLUMN a TEXT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 3 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +1008 +# Rebuild back +ALTER TABLE t1 MODIFY COLUMN a TEXT COMPRESSED CHARSET ucs2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 2000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 5 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +36 +# Make sure CREATE TABLE ... LIKE inherits compression +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +# Make sure implicit CREATE TABLE ... SELECT inherits compression +CREATE TABLE t2 SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 2000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 7 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +36 +DROP TABLE t2; +# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression +CREATE TABLE t2(a TEXT) SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 8 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +1008 +DROP TABLE t2; +# Make sure engine change works +ALTER TABLE t1 ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 2000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 10 +# Make sure online add column works (requires InnoDB) +ALTER TABLE t1 ADD COLUMN b TEXT COMPRESSED CHARSET ucs2 DEFAULT "must be visible"; +SELECT LEFT(a, 10), LENGTH(a), b FROM t1; +LEFT(a, 10) LENGTH(a) b +aaaaaaaaaa 2000 must be visible +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 12 +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ENGINE=MyISAM; +TRUNCATE TABLE t1; +# Make sure column_compression_zlib_level works +SET column_compression_zlib_level= 1; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 3 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH < 100 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH < 100 +1 +TRUNCATE TABLE t1; +SET column_compression_zlib_level= 9; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +44 +SET column_compression_zlib_level= DEFAULT; +TRUNCATE TABLE t1; +# No compression, original data shorter than compressed +INSERT INTO t1 VALUES('a'); +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +a 2 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +20 +# Coverage for store(double) and store(longlong) +INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); +# and for sort_string() +SELECT * FROM t1 ORDER BY a; +a +NULL + +1 +3.14 +9.8 +a +# Coverage for val_real() and val_int() +SELECT a+1 FROM t1 ORDER BY 1; +a+1 +NULL +1 +1 +2 +4.140000000000001 +10.8 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +DROP TABLE t1; +# +# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual +# Field* Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c1 TEXT COMPRESSED CHARSET ucs2) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 TEXT COMPRESSED CHARSET ucs2) ENGINE=MyISAM; +INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); +SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); +c1 +foo +bar +DROP TABLE t1, t2; +# +# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' +# failed in Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c TEXT COMPRESSED CHARSET ucs2) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT DISTINCT c FROM t1; +c +foo +bar +DROP TABLE t1; +SET column_compression_zlib_wrap=DEFAULT; +FLUSH STATUS; +CREATE TABLE t1(a BLOB COMPRESSED, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a BLOB COMPRESSED); +ALTER TABLE t1 ADD KEY(a(10)); +ERROR HY000: Compressed column 'a' can't be used in key specification +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Make sure column was actually compressed +INSERT INTO t1 VALUES(REPEAT('a', 1000)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 2 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +24 +# Make sure ALTER TABLE rebuilds table +ALTER TABLE t1 MODIFY COLUMN a BLOB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 1 +COLUMN_DECOMPRESSIONS 3 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +1008 +# Rebuild back +ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 5 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +24 +# Make sure CREATE TABLE ... LIKE inherits compression +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +# Make sure implicit CREATE TABLE ... SELECT inherits compression +CREATE TABLE t2 SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 7 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +24 +DROP TABLE t2; +# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression +CREATE TABLE t2(a BLOB) SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t2; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 8 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; +DATA_LENGTH +1008 +DROP TABLE t2; +# Make sure engine change works +ALTER TABLE t1 ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 1000 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 10 +# Make sure online add column works (requires InnoDB) +ALTER TABLE t1 ADD COLUMN b BLOB COMPRESSED DEFAULT "must be visible"; +SELECT LEFT(a, 10), LENGTH(a), b FROM t1; +LEFT(a, 10) LENGTH(a) b +aaaaaaaaaa 1000 must be visible +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 2 +COLUMN_DECOMPRESSIONS 12 +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ENGINE=MyISAM; +TRUNCATE TABLE t1; +# Make sure column_compression_zlib_level works +SET column_compression_zlib_level= 1; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 3 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH < 100 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH < 100 +1 +TRUNCATE TABLE t1; +SET column_compression_zlib_level= 9; +INSERT INTO t1 VALUES(REPEAT('ab', 1000)); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +28 +SET column_compression_zlib_level= DEFAULT; +TRUNCATE TABLE t1; +# No compression, original data shorter than compressed +INSERT INTO t1 VALUES('a'); +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +a 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +20 +# Coverage for store(double) and store(longlong) +INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); +# and for sort_string() +SELECT * FROM t1 ORDER BY a; +a +NULL + +1 +3.14 +9.8 +a +# Coverage for val_real() and val_int() +SELECT a+1 FROM t1 ORDER BY 1; +a+1 +NULL +1 +1 +2 +4.140000000000001 +10.8 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); +VARIABLE_NAME VARIABLE_VALUE +COLUMN_COMPRESSIONS 4 +COLUMN_DECOMPRESSIONS 12 +DROP TABLE t1; +# +# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual +# Field* Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c1 BLOB COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 BLOB COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); +SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); +c1 +foo +bar +DROP TABLE t1, t2; +# +# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' +# failed in Field_varstring_compressed::new_key_field +# +CREATE TABLE t1 (c BLOB COMPRESSED) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT DISTINCT c FROM t1; +c +foo +bar +DROP TABLE t1; +CREATE TABLE t1(a CHAR(100) COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1(a CHAR(100) NOT NULL COMPRESSED); +ERROR 42000: Incorrect column specifier for column 'a' +CREATE TABLE t1(a INT COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1(a BLOB COMPRESSED=unknown); +ERROR HY000: Unknown compression method: unknown +CREATE TABLE t1(a BLOB COMPRESSED COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1(a INT); +ALTER TABLE t1 MODIFY a INT COMPRESSED; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED' at line 1 +ALTER TABLE t1 MODIFY a INT NOT NULL COMPRESSED; +ERROR 42000: Incorrect column specifier for column 'a' +DROP TABLE t1; +# Test CSV +CREATE TABLE t1(a BLOB COMPRESSED NOT NULL) ENGINE=CSV; +INSERT INTO t1 VALUES(REPEAT('a', 110)); +SELECT LENGTH(a) FROM t1; +LENGTH(a) +110 +ALTER TABLE t1 ENGINE=MyISAM; +SELECT LENGTH(a) FROM t1; +LENGTH(a) +110 +ALTER TABLE t1 ENGINE=CSV; +SELECT LENGTH(a) FROM t1; +LENGTH(a) +110 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ NOT NULL DEFAULT '' +) ENGINE=CSV DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" +DROP TABLE t1; +# Test fields that don't fit data +CREATE TABLE t1(a VARCHAR(9) COMPRESSED); +INSERT INTO t1 VALUES(REPEAT('a', 10)); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT(' ', 10)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) + 9 +DROP TABLE t1; +CREATE TABLE t1(a TINYTEXT COMPRESSED); +SET column_compression_threshold=300; +INSERT INTO t1 VALUES(REPEAT('a', 254)); +INSERT INTO t1 VALUES(REPEAT(' ', 254)); +INSERT INTO t1 VALUES(REPEAT('a', 255)); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT(' ', 255)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT('a', 256)); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT(' ', 256)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT('a', 257)); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT(' ', 257)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'a' at row 1 +SET column_compression_threshold=DEFAULT; +SELECT LEFT(a, 10), LENGTH(a) FROM t1 ORDER BY 1; +LEFT(a, 10) LENGTH(a) + 254 + 254 + 254 + 254 +aaaaaaaaaa 254 +DROP TABLE t1; +# Corner case: VARCHAR(255) COMPRESSED must have 2 bytes pack length +CREATE TABLE t1(a VARCHAR(255) COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(255) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SET column_compression_threshold=300; +INSERT INTO t1 VALUES(REPEAT('a', 255)); +SET column_compression_threshold=DEFAULT; +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 255 +DROP TABLE t1; +CREATE TABLE t1(a VARCHAR(65531) COMPRESSED); +SET column_compression_threshold=65537; +INSERT INTO t1 VALUES(REPEAT('a', 65530)); +INSERT INTO t1 VALUES(REPEAT(' ', 65530)); +INSERT INTO t1 VALUES(REPEAT('a', 65531)); +INSERT INTO t1 VALUES(REPEAT(' ', 65531)); +INSERT INTO t1 VALUES(REPEAT('a', 65532)); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT(' ', 65532)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT('a', 65533)); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(REPEAT(' ', 65533)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +SET column_compression_threshold=DEFAULT; +SELECT LEFT(a, 10), LENGTH(a) FROM t1 ORDER BY 1, 2; +LEFT(a, 10) LENGTH(a) + 65530 + 65531 + 65531 + 65531 +aaaaaaaaaa 65530 +aaaaaaaaaa 65531 +DROP TABLE t1; +# +# MDEV-14929 - AddressSanitizer: memcpy-param-overlap in +# Field_longstr::compress +# +CREATE TABLE t1(b BLOB COMPRESSED); +INSERT INTO t1 VALUES('foo'),('bar'); +SET SESSION optimizer_switch = 'derived_merge=off'; +SELECT * FROM ( SELECT * FROM t1 ) AS sq ORDER BY b; +b +bar +foo +SET SESSION optimizer_switch=DEFAULT; +DROP TABLE t1; +# +# MDEV-15762 - VARCHAR(0) COMPRESSED crashes the server +# +CREATE TABLE t1(a VARCHAR(0) COMPRESSED); +INSERT INTO t1 VALUES('a'); +ERROR 22001: Data too long for column 'a' at row 1 +INSERT INTO t1 VALUES(' '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +SELECT LENGTH(a) FROM t1; +LENGTH(a) +0 +DROP TABLE t1; +# +# MDEV-15763 - VARCHAR(1) COMPRESSED crashes the server +# +CREATE TABLE t1(a VARCHAR(1) COMPRESSED); +SET column_compression_threshold=0; +INSERT INTO t1 VALUES('a'); +SET column_compression_threshold=DEFAULT; +DROP TABLE t1; +# +# MDEV-15938 - TINYTEXT CHARACTER SET utf8 COMPRESSED truncates data +# +CREATE TABLE t1(a TINYTEXT COMPRESSED, b TINYTEXT) CHARACTER SET utf8; +INSERT INTO t1 VALUES (REPEAT(_latin1'a', 254), REPEAT(_latin1'a', 254)); +SELECT CHAR_LENGTH(a), CHAR_LENGTH(b), LEFT(a, 10), LEFT(b, 10) FROM t1; +CHAR_LENGTH(a) CHAR_LENGTH(b) LEFT(a, 10) LEFT(b, 10) +254 254 aaaaaaaaaa aaaaaaaaaa +DROP TABLE t1; +# +# MDEV-16134 Wrong I_S.COLUMNS.CHARACTER_XXX_LENGTH value for compressed columns +# +CREATE TABLE t1 +( +a VARCHAR(10) COMPRESSED CHARACTER SET latin1, +b VARCHAR(10) COMPRESSED CHARACTER SET utf8 +); +SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH +FROM INFORMATION_SCHEMA.COLUMNS +WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' AND COLUMN_NAME IN ('a','b') +ORDER BY COLUMN_NAME; +COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH +a 10 10 +b 10 30 +DROP TABLE t1; +# +# MDEV-15592 - Column COMPRESSED should select a 'high order' datatype +# +CREATE TABLE t1(a TINYTEXT COMPRESSED); +INSERT INTO t1 VALUES(REPEAT('a', 255)); +SELECT LEFT(a, 10), LENGTH(a) FROM t1; +LEFT(a, 10) LENGTH(a) +aaaaaaaaaa 255 +DROP TABLE t1; +# +# MDEV-16729 VARCHAR COMPRESSED is created with a wrong length for multi-byte character sets +# +CREATE OR REPLACE TABLE t1 (a VARCHAR(1000) COMPRESSED CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(1000) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +COLUMN_TYPE +varchar(1000) /*!100301 COMPRESSED*/ +DROP TABLE t1; +# +# MDEV-17363 - Compressed columns cannot be restored from dump +# +CREATE TABLE t1(a INT NOT NULL COMPRESSED); +ERROR 42000: Incorrect column specifier for column 'a' +SHOW WARNINGS; +Level Code Message +Error 1063 Incorrect column specifier for column 'a' +CREATE TABLE t1( +a JSON COMPRESSED, +b VARCHAR(1000) COMPRESSED BINARY, +c NVARCHAR(1000) COMPRESSED BINARY, +d TINYTEXT COMPRESSED BINARY +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`a`)), + `b` varchar(1000) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `c` varchar(1000) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, + `d` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# VARCHAR and TEXT variants +# +# +# The following statements run without warnings. +# The `compressed opt_binary` grammar sequence is covered. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED UNICODE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10) /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a VARCHAR(10) BINARY COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) ASCII COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) BYTE COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varbinary(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# The `compressed opt_binary` grammar sequence is covered. +# +CREATE TABLE t1 (a TINYTEXT COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED UNICODE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a TINYTEXT BINARY COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT ASCII COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinytext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT BYTE COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# The `compressed opt_binary` grammar sequence is covered. +# +CREATE TABLE t1 (a TEXT COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED BINARY); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED BINARY ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED BYTE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED UNICODE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a TEXT COMPRESSED BYTE DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED BINARY DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED ASCII DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a TEXT BINARY COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT ASCII COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TEXT BYTE COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a TEXT COMPRESSED BYTE COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TEXT COMPRESSED BINARY COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TEXT COMPRESSED ASCII COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a TEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# The `compressed opt_binary` grammar sequence is covered. +# +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED UNICODE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a MEDIUMTEXT BINARY COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT ASCII COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT BYTE COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# The `compressed opt_binary` grammar sequence is covered. +# +CREATE TABLE t1 (a LONGTEXT COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED UNICODE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a LONGTEXT BINARY COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT ASCII COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT BYTE COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# VARBINARY and BLOB variables +# +# +# The following statements run without warnings. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a VARCHAR(10) DEFAULT '' COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) NULL COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a VARCHAR(10) COMPRESSED NULL COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# +CREATE TABLE t1 (a TINYBLOB COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a TINYBLOB COMPRESSED DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYBLOB COMPRESSED NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYBLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a TINYBLOB DEFAULT '' COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a TINYBLOB NULL COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a TINYBLOB COMPRESSED COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a TINYBLOB COMPRESSED DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a TINYBLOB COMPRESSED NULL COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# +CREATE TABLE t1 (a BLOB COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a BLOB COMPRESSED DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a BLOB COMPRESSED NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a BLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a BLOB DEFAULT '' COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a BLOB NULL COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a BLOB COMPRESSED COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a BLOB COMPRESSED DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a BLOB COMPRESSED NULL COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a MEDIUMBLOB DEFAULT '' COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMBLOB NULL COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED NULL COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements run without warnings. +# +CREATE TABLE t1 (a LONGBLOB COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements run without warnings. +# They have extra column attributes (or GENERATED) after COMPRESSED. +# +CREATE TABLE t1 (a LONGBLOB COMPRESSED DEFAULT ''); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB COMPRESSED NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements return deprecated syntax warnings +# +CREATE TABLE t1 (a LONGBLOB DEFAULT '' COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB NULL COMPRESSED); +Warnings: +Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# The following statements fail by the grammar, +# because COMPRESSED immediately follows 'field_type'. +# +CREATE TABLE t1 (a LONGBLOB COMPRESSED COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# The following statements are not prohibited by the *.yy grammar, +# because the sequence `field_type attribute COMPRESSED` is allowed +# (notice there is at least one attribute after `field_type`). +# The first COMPRESSED is parsed inside `field_type`. +# The second COMPRESSED passes through the parser but then is caught +# inside Column_definition::set_compressed_deprecated_with_type_check() +# and a syntax error is raised. +# +CREATE TABLE t1 (a LONGBLOB COMPRESSED DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a LONGBLOB COMPRESSED NULL COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# NVARCHAR +# +CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED BINARY COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED DEFAULT '' COMPRESSED); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 +# +# MDEV-21348 - Memory leak in Storage-Engine Independent Column +# Compression +# +CREATE TABLE t1(a BLOB COMPRESSED); +SET column_compression_threshold=0; +INSERT INTO t1 VALUES('aa'); +SET column_compression_threshold=DEFAULT; +DROP TABLE t1; +# +# MDEV-31724 Compressed varchar values lost on joins when sorting on columns from joined table(s) +# +CREATE TABLE t1 ( +id int(10) unsigned not null, +txt varchar(5000) COMPRESSED NOT NULL DEFAULT '', +PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; +CREATE TABLE t2 ( +id int(10) unsigned not null, +n1 bigint(20) NOT NULL, +n2 bigint(20) NOT NULL, +n3 bigint(20) NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; +INSERT INTO t1 VALUES +(1, 'short string < 100 chars'), +(2, 'long string = 99 chars '), +(3, 'long string = 100 chars !'), +(4, 'long string = 101 chars !'); +INSERT INTO t2 VALUES +(1, 24, 1, 1), +(2, 99, 2, 2), +(3, 100, 3, 3), +(4, 101, 4, 4); +SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id; +txt id n1 n2 n3 +short string < 100 chars 1 24 1 1 +long string = 99 chars 2 99 2 2 +long string = 100 chars ! 3 100 3 3 +long string = 101 chars ! 4 101 4 4 +SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1; +txt id n1 n2 n3 +short string < 100 chars 1 24 1 1 +long string = 99 chars 2 99 2 2 +long string = 100 chars ! 3 100 3 3 +long string = 101 chars ! 4 101 4 4 +SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id; +txt id n1 n2 n3 +short string < 100 chars 1 24 1 1 +long string = 99 chars 2 99 2 2 +long string = 100 chars ! 3 100 3 3 +long string = 101 chars ! 4 101 4 4 +SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1; +txt id n1 n2 n3 +short string < 100 chars 1 24 1 1 +long string = 99 chars 2 99 2 2 +long string = 100 chars ! 3 100 3 3 +long string = 101 chars ! 4 101 4 4 +DROP TABLE t1, t2; +CREATE OR REPLACE TABLE t1 ( +id INT NOT NULL PRIMARY KEY, +txt varchar(5000) COMPRESSED NOT NULL DEFAULT '' +) CHARSET=utf8mb3; +INSERT INTO t1 VALUES +(1, REPEAT('a', 10)), +(2, REPEAT('b', 99)), +(3, REPEAT('c', 100)), +(4, REPEAT('d', 121)); +SELECT txt, sysdate(6) FROM t1 ORDER BY 2; +txt sysdate(6) +aaaaaaaaaa <sysdate> +bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb <sysdate> +cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc <sysdate> +ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd <sysdate> +DROP TABLE t1; +CREATE FUNCTION f1(imax INT, jmax INT) RETURNS TEXT +BEGIN +DECLARE res TEXT DEFAULT 'x'; +FOR i IN 0..imax +DO +FOR j IN 0..jmax +DO +SET res=CONCAT(res, ' ', i, ' ', j); +END FOR; +END FOR; +RETURN res; +END; +$$ +SET @@column_compression_threshold=32; +# VARCHAR1, 8bit, truncation +CREATE TABLE t1 (a VARCHAR(254) COMPRESSED CHARACTER SET latin1); +INSERT INTO t1 VALUES (f1(6,6)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +197 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 6 6 0 6 1 6 2 6 3 6 4 6 5 6 6 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(4) COMPRESSED CHARACTER SET latin1; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 1 +SELECT LENGTH(a), a FROM t1; +LENGTH(a) a +4 x 0 +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(254) COMPRESSED CHARACTER SET latin1); +INSERT INTO t1 VALUES (REPEAT('a',254)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +254 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(4) COMPRESSED CHARACTER SET latin1; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 1 +SELECT LENGTH(a), a FROM t1; +LENGTH(a) a +4 aaaa +DROP TABLE t1; +# VARCHAR1, 8bit, no truncation +CREATE TABLE t1 (a VARCHAR(250) COMPRESSED CHARACTER SET latin1); +INSERT INTO t1 VALUES (f1(6,6)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +197 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 6 6 0 6 1 6 2 6 3 6 4 6 5 6 6 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(254) COMPRESSED CHARACTER SET latin1; +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 0 +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +197 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 6 6 0 6 1 6 2 6 3 6 4 6 5 6 6 +DROP TABLE t1; +# VARCHAR2, 8bit, truncation +CREATE TABLE t1 (a VARCHAR(32000) COMPRESSED CHARACTER SET latin1); +INSERT INTO t1 VALUES (f1(31,31)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +5505 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 31 27 31 28 31 29 31 30 31 31 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(256) COMPRESSED CHARACTER SET latin1; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 1 +Column_decompressions 1 +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +256 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 1 17 1 18 1 19 1 20 1 21 1 22 +DROP TABLE t1; +# VARCHAR2, 8bit, no truncation +CREATE TABLE t1 (a VARCHAR(32000) COMPRESSED CHARACTER SET latin1); +INSERT INTO t1 VALUES (f1(31,31)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a, 30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a, 30) +5505 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 31 27 31 28 31 29 31 30 31 31 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(32001) COMPRESSED CHARACTER SET latin1; +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 0 +SELECT LENGTH(a), LEFT(a,30), RIGHT(a, 30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a, 30) +5505 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 31 27 31 28 31 29 31 30 31 31 +DROP TABLE t1; +# VARCHAR1, multi-byte, truncation +CREATE TABLE t1 (a VARCHAR(80) COMPRESSED CHARACTER SET utf8mb3); +INSERT INTO t1 VALUES (f1(3,3)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +65 x 0 0 0 1 0 2 0 3 1 0 1 1 1 2 0 2 1 2 2 2 3 3 0 3 1 3 2 3 3 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(1) COMPRESSED CHARACTER SET utf8mb3; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 1 +SELECT LENGTH(a), a FROM t1; +LENGTH(a) a +1 x +DROP TABLE t1; +# VARCHAR1, multi-byte, no truncation +CREATE TABLE t1 (a VARCHAR(80) COMPRESSED CHARACTER SET utf8mb3); +INSERT INTO t1 VALUES (f1(3,3)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +65 x 0 0 0 1 0 2 0 3 1 0 1 1 1 2 0 2 1 2 2 2 3 3 0 3 1 3 2 3 3 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(81) COMPRESSED CHARACTER SET utf8mb3; +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 0 +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +65 x 0 0 0 1 0 2 0 3 1 0 1 1 1 2 0 2 1 2 2 2 3 3 0 3 1 3 2 3 3 +DROP TABLE t1; +# VARCHAR2, multi-byte, truncation +CREATE TABLE t1 (a VARCHAR(10000) COMPRESSED CHARACTER SET utf8mb3); +INSERT INTO t1 VALUES (f1(31,31)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +5505 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 31 27 31 28 31 29 31 30 31 31 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(256) COMPRESSED CHARACTER SET utf8mb3; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 1 +Column_decompressions 1 +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +256 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 1 17 1 18 1 19 1 20 1 21 1 22 +DROP TABLE t1; +# VARCHAR2, multi-byte, no truncation +CREATE TABLE t1 (a VARCHAR(10000) COMPRESSED CHARACTER SET utf8mb3); +INSERT INTO t1 VALUES (f1(31,31)); +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +5505 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 31 27 31 28 31 29 31 30 31 31 +FLUSH STATUS; +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(10001) COMPRESSED CHARACTER SET utf8mb3; +SHOW STATUS LIKE 'Column%compressions'; +Variable_name Value +Column_compressions 0 +Column_decompressions 0 +SELECT LENGTH(a), LEFT(a,30), RIGHT(a,30) FROM t1; +LENGTH(a) LEFT(a,30) RIGHT(a,30) +5505 x 0 0 0 1 0 2 0 3 0 4 0 5 0 6 31 27 31 28 31 29 31 30 31 31 +DROP TABLE t1; +SET @@column_compression_threshold=DEFAULT; +DROP FUNCTION f1; +# +# MDEV-24797 Column Compression - ERROR 1265 (01000): Data truncated for column +# +CREATE TABLE t1 (a VARCHAR(500) COMPRESSED CHARACTER SET utf8mb3) ENGINE=MyISAM; +INSERT INTO t1 SET a=REPEAT('x',127); +ALTER TABLE t1 FORCE, ALGORITHM=COPY; +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(500) COMPRESSED CHARACTER SET utf8mb3) ENGINE=InnoDB; +INSERT INTO t1 SET a=REPEAT('x',127); +ALTER TABLE t1 FORCE, ALGORITHM=COPY; +DROP TABLE t1; +# +# End of 10.4 tests +# +# +# MDEV-19727 Add Type_handler::Key_part_spec_init_ft +# +CREATE TABLE t1 (a VARCHAR(1000) COMPRESSED, FULLTEXT INDEX(a)); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1 (a TEXT COMPRESSED, FULLTEXT INDEX(a)); +ERROR HY000: Compressed column 'a' can't be used in key specification +# +# End of 10.5 tests +# |