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;
#
# 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;