1set @save_max_allowed_packet=@@max_allowed_packet; 2set global max_allowed_packet=1048576; 3connect conn1,localhost,root,,; 4connection conn1; 5select @test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '; 6@test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ' 7string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8select length(@test_compress_string); 9length(@test_compress_string) 10117 11select uncompress(compress(@test_compress_string)); 12uncompress(compress(@test_compress_string)) 13string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 14explain extended select uncompress(compress(@test_compress_string)); 15id select_type table type possible_keys key key_len ref rows filtered Extra 161 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 17Warnings: 18Note 1003 select uncompress(compress(@`test_compress_string`)) AS `uncompress(compress(@test_compress_string))` 19select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string); 20uncompressed_length(compress(@test_compress_string))=length(@test_compress_string) 211 22explain extended select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string); 23id select_type table type possible_keys key key_len ref rows filtered Extra 241 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 25Warnings: 26Note 1003 select uncompressed_length(compress(@`test_compress_string`)) = octet_length(@`test_compress_string`) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)` 27select uncompressed_length(compress(@test_compress_string)); 28uncompressed_length(compress(@test_compress_string)) 29117 30select length(compress(@test_compress_string))<length(@test_compress_string); 31length(compress(@test_compress_string))<length(@test_compress_string) 321 33create table t1 (a text, b char(255), c char(4)) engine=myisam; 34insert into t1 (a,b,c) values (compress(@test_compress_string),compress(@test_compress_string),'d '); 35select uncompress(a) from t1; 36uncompress(a) 37string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 38select uncompress(b) from t1; 39uncompress(b) 40string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 41select concat('|',c,'|') from t1; 42concat('|',c,'|') 43|d| 44drop table t1; 45select compress(""); 46compress("") 47 48select uncompress(""); 49uncompress("") 50 51select uncompress(compress("")); 52uncompress(compress("")) 53 54select uncompressed_length(""); 55uncompressed_length("") 560 57create table t1 (a text); 58insert t1 values (compress(null)), ('A\0\0\0BBBBBBBB'), (compress(space(50000))), (space(50000)); 59select length(a) from t1; 60length(a) 61NULL 6212 6376 6450000 65select length(uncompress(a)) from t1; 66length(uncompress(a)) 67NULL 68NULL 6950000 70NULL 71Warnings: 72Warning 1259 ZLIB: Input data corrupted 73Warning 1256 Uncompressed data size too large; the maximum size is 1048576 (probably, length of uncompressed data was corrupted) 74drop table t1; 75set @@global.max_allowed_packet=1048576*100; 76connect newconn, localhost, root,,; 77select compress(repeat('aaaaaaaaaa', IF(XXX, 10, 10000000))) is null; 78compress(repeat('aaaaaaaaaa', IF(XXX, 10, 10000000))) is null 790 80Warnings: 81Warning 1292 Truncated incorrect DOUBLE value: XXX 82Warning 1292 Truncated incorrect DOUBLE value: XXX 83disconnect newconn; 84connection default; 85set @@global.max_allowed_packet=@save_max_allowed_packet; 86create table t1(a blob); 87insert into t1 values(NULL), (compress('a')); 88select uncompress(a), uncompressed_length(a) from t1; 89uncompress(a) uncompressed_length(a) 90NULL NULL 91a 1 92drop table t1; 93create table t1(a blob); 94insert into t1 values ('0'), (NULL), ('0'); 95select compress(a), compress(a) from t1; 96select compress(a) is null from t1; 97compress(a) is null 980 991 1000 101drop table t1; 102End of 4.1 tests 103create table t1 (a varchar(32) not null); 104insert into t1 values ('foo'); 105explain select * from t1 where uncompress(a) is null; 106id select_type table type possible_keys key key_len ref rows Extra 1071 SIMPLE t1 system NULL NULL NULL NULL 1 108Warnings: 109Warning 1259 ZLIB: Input data corrupted 110select * from t1 where uncompress(a) is null; 111a 112foo 113Warnings: 114Warning 1259 ZLIB: Input data corrupted 115explain select *, uncompress(a) from t1; 116id select_type table type possible_keys key key_len ref rows Extra 1171 SIMPLE t1 system NULL NULL NULL NULL 1 118select *, uncompress(a) from t1; 119a uncompress(a) 120foo NULL 121Warnings: 122Warning 1259 ZLIB: Input data corrupted 123select *, uncompress(a), uncompress(a) is null from t1; 124a uncompress(a) uncompress(a) is null 125foo NULL 1 126Warnings: 127Warning 1259 ZLIB: Input data corrupted 128Warning 1259 ZLIB: Input data corrupted 129drop table t1; 130CREATE TABLE t1 (c1 INT); 131INSERT INTO t1 VALUES (1), (1111), (11111); 132SELECT UNCOMPRESS(c1), UNCOMPRESSED_LENGTH(c1) FROM t1; 133UNCOMPRESS(c1) UNCOMPRESSED_LENGTH(c1) 134NULL NULL 135NULL NULL 136NULL 825307441 137EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s; 138DROP TABLE t1; 139End of 5.0 tests 140# 141# Start of 5.3 tests 142# 143# 144# MDEV-5783 Assertion `0' failed in make_sortkey(SORTPARAM*, uchar*, uchar*) on ORDER BY HEX(UNCOMPRESSED_LENGTH(pk)) 145# 146CREATE TABLE t1 (pk INT PRIMARY KEY); 147INSERT INTO t1 VALUES (1),(2); 148SELECT UNCOMPRESSED_LENGTH(pk) FROM t1; 149UNCOMPRESSED_LENGTH(pk) 150NULL 151NULL 152Warnings: 153Warning 1259 ZLIB: Input data corrupted 154Warning 1259 ZLIB: Input data corrupted 155SELECT * FROM t1 ORDER BY HEX(UNCOMPRESSED_LENGTH(pk)); 156DROP TABLE t1; 157# 158# End of 5.3 tests 159# 160SELECT UNCOMPRESS(CAST(0 AS BINARY(5))); 161UNCOMPRESS(CAST(0 AS BINARY(5))) 162NULL 163Warnings: 164Warning 1259 ZLIB: Input data corrupted 165disconnect conn1; 166connection default; 167set global max_allowed_packet=@save_max_allowed_packet; 168# 169# End of 5.5 tests 170# 171# 172# Start of 10.1 tests 173# 174# 175# MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) 176# 177CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2)); 178INSERT INTO t1 VALUES ('test',compress('test')), ('TEST', compress('TEST')); 179SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST")); 180f1 HEX(f2) 181test 04000000789C2B492D2E0100045D01C1 182TEST 04000000789C0B710D0E0100031D0141 183SELECT f1,HEX(f2) FROM t1 WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST")); 184f1 HEX(f2) 185TEST 04000000789C0B710D0E0100031D0141 186test 04000000789C2B492D2E0100045D01C1 187SELECT f1,HEX(f2) FROM t1 WHERE f1='test' AND (f2= compress("TEST") OR f2= compress("test")); 188f1 HEX(f2) 189TEST 04000000789C0B710D0E0100031D0141 190test 04000000789C2B492D2E0100045D01C1 191DROP TABLE t1; 192# 193# End of 10.1 tests 194# 195# 196# Start of 10.2 tests 197# 198# 199# MDEV-10134 Add full support for DEFAULT 200# 201CREATE TABLE t1 (a TEXT, b BLOB DEFAULT COMPRESS(a), bl INT DEFAULT UNCOMPRESSED_LENGTH(b), a1 TEXT DEFAULT UNCOMPRESS(b)); 202SHOW CREATE TABLE t1; 203Table Create Table 204t1 CREATE TABLE `t1` ( 205 `a` text DEFAULT NULL, 206 `b` blob DEFAULT compress(`a`), 207 `bl` int(11) DEFAULT uncompressed_length(`b`), 208 `a1` text DEFAULT uncompress(`b`) 209) ENGINE=MyISAM DEFAULT CHARSET=latin1 210INSERT INTO t1 (a) VALUES (REPEAT('a',100)); 211SELECT bl, a1 FROM t1; 212bl a1 213100 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 214DROP TABLE t1; 215# 216# End of 10.2 tests 217# 218