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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 ' ... COMPRESSED...' is deprecated and will be removed in a future release. Please use ' COMPRESSED... ...' 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 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd 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 #