diff options
Diffstat (limited to 'mysql-test/main/column_compression.result')
-rw-r--r-- | mysql-test/main/column_compression.result | 1362 |
1 files changed, 1362 insertions, 0 deletions
diff --git a/mysql-test/main/column_compression.result b/mysql-test/main/column_compression.result new file mode 100644 index 00000000000..243a118a706 --- /dev/null +++ b/mysql-test/main/column_compression.result @@ -0,0 +1,1362 @@ +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 +# 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 +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 +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 +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 +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 +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 +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 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +40 +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 +# 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 +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 +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 +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 +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 +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 +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 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +40 +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 +# 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 +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 +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 +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 +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 +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 +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 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +40 +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 +# 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 +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 +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 +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 +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 +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 +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 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +40 +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 CHARSET ucs2 COMPRESSED, KEY(a(10))); +ERROR HY000: Compressed column 'a' can't be used in key specification +CREATE TABLE t1(a TEXT CHARSET ucs2 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*/ CHARACTER SET ucs2 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# 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 +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 CHARSET ucs2 COMPRESSED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 +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 DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +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 CHARSET ucs2 COMPRESSED 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 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +60 +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 CHARSET ucs2 COMPRESSED) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (c2 TEXT CHARSET ucs2 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 CHARSET ucs2 COMPRESSED) 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 +# 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 +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 +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 +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 +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 +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 +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 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DATA_LENGTH +36 +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: Incorrect column specifier for column 'a' +CREATE TABLE t1(a INT COMPRESSED); +ERROR 42000: Incorrect column specifier for column 'a' +CREATE TABLE t1(a BLOB COMPRESSED=unknown); +ERROR HY000: Unknown compression method: unknown +CREATE TABLE t1(a BLOB COMPRESSED COMPRESSED); +DROP TABLE t1; +CREATE TABLE t1(a INT); +ALTER TABLE t1 MODIFY a INT COMPRESSED; +ERROR 42000: Incorrect column specifier for column 'a' +DROP TABLE t1; +# Test CSV +CREATE TABLE t1(a BLOB NOT NULL COMPRESSED) 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 +"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', 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 +SET column_compression_threshold=DEFAULT; +SELECT a, LENGTH(a) FROM t1; +a LENGTH(a) + 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 +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; |