summaryrefslogtreecommitdiff
path: root/mysql-test/main/func_compress.result
blob: 60ddd86650758e0294b0c66fe660d5b19f4ded1b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
set global max_allowed_packet=1048576;
connect  conn1,localhost,root,,;
connection conn1;
select @test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ';
@test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '
string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
select length(@test_compress_string);
length(@test_compress_string)
117
select uncompress(compress(@test_compress_string));
uncompress(compress(@test_compress_string))
string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
explain extended select uncompress(compress(@test_compress_string));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select uncompress(compress(@`test_compress_string`)) AS `uncompress(compress(@test_compress_string))`
select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string);
uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)
1
explain extended select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select uncompressed_length(compress(@`test_compress_string`)) = octet_length(@`test_compress_string`) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)`
select uncompressed_length(compress(@test_compress_string));
uncompressed_length(compress(@test_compress_string))
117
select length(compress(@test_compress_string))<length(@test_compress_string);
length(compress(@test_compress_string))<length(@test_compress_string)
1
create table t1 (a text, b char(255), c char(4)) engine=myisam;
insert into t1 (a,b,c) values (compress(@test_compress_string),compress(@test_compress_string),'d ');
select uncompress(a) from t1;
uncompress(a)
string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
select uncompress(b) from t1;
uncompress(b)
string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
select concat('|',c,'|') from t1;
concat('|',c,'|')
|d|
drop table t1;
select compress("");
compress("")

select uncompress("");
uncompress("")

select uncompress(compress(""));
uncompress(compress(""))

select uncompressed_length("");
uncompressed_length("")
0
create table t1 (a text);
insert t1 values (compress(null)), ('A\0\0\0BBBBBBBB'), (compress(space(50000))), (space(50000));
select length(a) from t1;
length(a)
NULL
12
76
50000
select length(uncompress(a)) from t1;
length(uncompress(a))
NULL
NULL
50000
NULL
Warnings:
Warning	1259	ZLIB: Input data corrupted
Warning	1256	Uncompressed data size too large; the maximum size is 1048576 (probably, length of uncompressed data was corrupted)
drop table t1;
set @@global.max_allowed_packet=1048576*100;
connect  newconn, localhost, root,,;
select compress(repeat('aaaaaaaaaa', IF(XXX, 10, 10000000))) is null;
compress(repeat('aaaaaaaaaa', IF(XXX, 10, 10000000))) is null
0
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: XXX
Warning	1292	Truncated incorrect DOUBLE value: XXX
disconnect newconn;
connection default;
set @@global.max_allowed_packet=default;
create table t1(a blob);
insert into t1 values(NULL), (compress('a'));
select uncompress(a), uncompressed_length(a) from t1;
uncompress(a)	uncompressed_length(a)
NULL	NULL
a	1
drop table t1;
create table t1(a blob);
insert into t1 values ('0'), (NULL), ('0');
select compress(a), compress(a) from t1;
select compress(a) is null from t1;
compress(a) is null
0
1
0
drop table t1;
End of 4.1 tests
create table t1 (a varchar(32) not null);
insert into t1 values ('foo');
explain select * from t1 where uncompress(a) is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
Warnings:
Warning	1259	ZLIB: Input data corrupted
select * from t1 where uncompress(a) is null;
a
foo
Warnings:
Warning	1259	ZLIB: Input data corrupted
explain select *, uncompress(a) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
select *, uncompress(a) from t1;
a	uncompress(a)
foo	NULL
Warnings:
Warning	1259	ZLIB: Input data corrupted
select *, uncompress(a), uncompress(a) is null from t1;
a	uncompress(a)	uncompress(a) is null
foo	NULL	1
Warnings:
Warning	1259	ZLIB: Input data corrupted
Warning	1259	ZLIB: Input data corrupted
drop table t1;
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1), (1111), (11111);
SELECT UNCOMPRESS(c1), UNCOMPRESSED_LENGTH(c1) FROM t1;
UNCOMPRESS(c1)	UNCOMPRESSED_LENGTH(c1)
NULL	NULL
NULL	NULL
NULL	825307441
EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s;
DROP TABLE t1;
End of 5.0 tests
#
# Start of 5.3 tests
#
#
# MDEV-5783 Assertion `0' failed in make_sortkey(SORTPARAM*, uchar*, uchar*) on ORDER BY HEX(UNCOMPRESSED_LENGTH(pk))
#
CREATE TABLE t1 (pk INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2);
SELECT UNCOMPRESSED_LENGTH(pk) FROM t1;
UNCOMPRESSED_LENGTH(pk)
NULL
NULL
Warnings:
Warning	1259	ZLIB: Input data corrupted
Warning	1259	ZLIB: Input data corrupted
SELECT * FROM t1 ORDER BY HEX(UNCOMPRESSED_LENGTH(pk));
DROP TABLE t1;
#
# End of 5.3 tests
#
SELECT UNCOMPRESS(CAST(0 AS BINARY(5)));
UNCOMPRESS(CAST(0 AS BINARY(5)))
NULL
Warnings:
Warning	1259	ZLIB: Input data corrupted
disconnect conn1;
connection default;
set global max_allowed_packet=default;
#
# End of 5.5 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST'))
#
CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2));
INSERT INTO t1 VALUES ('test',compress('test')), ('TEST', compress('TEST'));
SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST"));
f1	HEX(f2)
test	04000000789C2B492D2E0100045D01C1
TEST	04000000789C0B710D0E0100031D0141
SELECT f1,HEX(f2) FROM t1                  WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST"));
f1	HEX(f2)
TEST	04000000789C0B710D0E0100031D0141
test	04000000789C2B492D2E0100045D01C1
SELECT f1,HEX(f2) FROM t1                  WHERE f1='test' AND (f2= compress("TEST") OR f2= compress("test"));
f1	HEX(f2)
TEST	04000000789C0B710D0E0100031D0141
test	04000000789C2B492D2E0100045D01C1
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-10134 Add full support for DEFAULT
#
CREATE TABLE t1 (a TEXT, b BLOB DEFAULT COMPRESS(a), bl INT DEFAULT UNCOMPRESSED_LENGTH(b), a1 TEXT DEFAULT UNCOMPRESS(b));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` text DEFAULT NULL,
  `b` blob DEFAULT compress(`a`),
  `bl` int(11) DEFAULT uncompressed_length(`b`),
  `a1` text DEFAULT uncompress(`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 (a) VALUES (REPEAT('a',100));
SELECT bl, a1 FROM t1;
bl	a1
100	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
DROP TABLE t1;
#
# End of 10.2 tests
#