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
217
218
219
220
221
222
223
224
225
226
227
|
#####################################################################
# #
# Tests for uuid_to_bin, bin_to_uuid and is_uuid functions. #
# #
#####################################################################
--echo #
--echo # WL#8920: Improve usability of UUID manipulations
--echo #
SELECT HEX(uuid_to_bin('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}', TRUE)) AS a,
HEX(uuid_to_bin('{e60c88ba-083f-4ceb-be59-f67636d718a2}', TRUE)) AS b;
SELECT HEX(uuid_to_bin('c8eb4b15cb0948bbbbb2e6a0b6b4d5c7', TRUE)) AS a,
HEX(uuid_to_bin('e60c88ba083f4cebbe59f67636d718a2', TRUE)) AS b;
SELECT HEX(uuid_to_bin('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7', TRUE)) AS a,
HEX(uuid_to_bin('e60c88ba-083f-4ceb-be59-f67636d718a2', TRUE)) AS b;
--echo # UUID too long
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5cc7', TRUE) AS a;
--echo # UUID with invalid characters
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('12345678123456Z81234567812345678', TRUE) AS a;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('12345678-1234-5678-1234-56781234567Z', TRUE) AS a;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('e60c88ba-083f-4ceb-be59-f67636d718aa2', TRUE) AS b;
--echo # UUID too short
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('c8eb4b15-cb09-48bb-bbb2-e6a04d5cc7', TRUE) AS a;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('e60c88ba-083f-4ceb-be59-f676318aa2', TRUE) AS b;
SELECT bin_to_uuid(unhex('7f9d04ae61b34468ac798ffcc984ab68')) AS a, bin_to_uuid(unhex('7f9d04ae61b34468ac898ffcc984ab68'),TRUE) AS b;
--echo # UUID too long
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(unhex('7f9d04ae61b34468ac798ffcc984ab668')) AS a;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(unhex('7f9d04ae61b34468ac898ffcc984ab668'), TRUE) AS b;
-- echo # UUID too short
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(unhex('7f9d04ae61b34468ac798ffcc98468')) AS a;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(unhex('7f9d04ab34468ac898ffcc984ab668'), TRUE) AS b;
SELECT uuid_to_bin('{c8eb4b15-CB09-48bb-bbb2-e6a0b6b4d5c7}') = x'c8eb4b15cb0948bbbbb2e6a0b6b4d5c7';
SELECT uuid_to_bin('{c8eb4b15-CB09-48bb-bbb2-e6a0b6b4d5c7}', TRUE) = x'48bbcb09c8eb4b15bbb2e6a0b6b4d5c7';
SELECT bin_to_uuid(x'7f9d04ae61b34468ac798ffcc984ab68') = '7f9d04ae-61b3-4468-ac79-8ffcc984ab68';
SELECT bin_to_uuid(x'7f9d04ae61b34468ac798ffcc984ab68', TRUE) = '61b34468-04ae-7f9d-ac79-8ffcc984ab68';
-- echo # null VALUES
SELECT bin_to_uuid(NULL) AS a, bin_to_uuid(NULL, TRUE) AS b;
SELECT uuid_to_bin(NULL) AS a, uuid_to_bin(NULL, TRUE) AS b;
SELECT is_uuid(NULL);
-- echo # valid uuids
SELECT is_uuid('{12345678-1234-5678-1234-567812345678}');
SELECT is_uuid('12345678123456781234567812345678');
SELECT is_uuid('12345678-1234-5678-1234-567812345678');
-- echo # shorter uuids
SELECT is_uuid('{2345678-1234-5678-1234-567812345678}');
SELECT is_uuid('2345678123456781234567812345678');
SELECT is_uuid('2345678-1234-5678-1234-567812345678');
-- echo # longer uuids
SELECT is_uuid('{9912345678-1234-5678-1234-567812345678}');
SELECT is_uuid('9912345678123456781234567812345678');
SELECT is_uuid('9912345678-1234-5678-1234-567812345678');
-- echo # uuids with missing dash
SELECT is_uuid('{12345678-12345678-1234-567812345678}');
SELECT is_uuid('1234567812345-6781234567812345678');
SELECT is_uuid('12345678-12345678-1234-567812345678');
-- echo # uuids with dash in wrong place
SELECT is_uuid('{12345678-123456-78-1234-567812345678}');
SELECT is_uuid('12345678-123456-78-1234-567812345678');
-- echo # tests with tables
CREATE TABLE t(a binary(16));
INSERT into t VALUES(unhex('7f9d04ae61b34468ac798ffcc984ab68')),(unhex('d00653b290b940d193c2194456bd4f3d')),(unhex('e60c88ba083f4cebbe59f67636d718a2')),(unhex('c8eb4b15cb0948bbbbb2e6a0b6b4d5c7'));
SELECT bin_to_uuid(a), bin_to_uuid(a,TRUE) FROM t;
SELECT HEX(uuid_to_bin(bin_to_uuid(a))) AS c1,
uuid_to_bin(bin_to_uuid(a)) = a AS c2,
HEX(uuid_to_bin(bin_to_uuid(a, TRUE), TRUE)) AS c3,
uuid_to_bin(bin_to_uuid(a, TRUE), TRUE) = a AS c4
FROM t;
SELECT bin_to_uuid(uuid_to_bin(bin_to_uuid(a))), bin_to_uuid(uuid_to_bin(bin_to_uuid(a, TRUE), TRUE), TRUE) FROM t;
SELECT bin_to_uuid(uuid_to_bin('{12345678-1234-5678-1234-567812345678}')), bin_to_uuid(uuid_to_bin('{12345678-1234-5678-1234-567812345678}')) = '12345678-1234-5678-1234-567812345678';
SELECT bin_to_uuid(uuid_to_bin('12345678123456781234567812345678')),bin_to_uuid(uuid_to_bin('12345678123456781234567812345678')) = '12345678-1234-5678-1234-567812345678';
SELECT bin_to_uuid(uuid_to_bin('12345678-1234-5678-1234-567812345678')), bin_to_uuid(uuid_to_bin('12345678-1234-5678-1234-567812345678')) = '12345678-1234-5678-1234-567812345678';
DROP TABLE t;
CREATE TABLE at(_bin binary(16),
_vbn varbinary(16),
_tbl tinyblob,
_ttx tinytext,
_blb blob);
INSERT into at VALUES(
x'12345678123456781234567812345678',
x'12345678123456781234567812345678',
x'12345678123456781234567812345678',
x'12345678123456781234567812345678',
x'12345678123456781234567812345678');
let $query=
SELECT
bin_to_uuid(_bin),
bin_to_uuid(_vbn),
bin_to_uuid(_tbl),
bin_to_uuid(_ttx),
bin_to_uuid(_blb)
FROM at;
EVAL $query;
-- echo # Output types of bin_to_uuid:
EVAL CREATE TABLE t3 AS $query;
SHOW CREATE TABLE t3;
DROP TABLE t3;
-- echo # Compare with hex:
# Use latin1 charset till Bug#26049833 is fixed
set names latin1;
DELETE FROM at;
INSERT into at(_bin,_blb) VALUES('c8eb4b15cb0948bb','c8eb4b15cb0948bb');
CREATE TABLE t3 AS SELECT hex('c8eb4b15cb0948bb'),hex(_bin),hex(_blb) FROM at;
SHOW CREATE TABLE t3;
DROP TABLE t3;
set names utf8mb4;
-- echo # Output types of uuid_to_bin
CREATE TABLE t3 AS SELECT
uuid_to_bin('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7') AS a,
uuid_to_bin('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7', true) AS b;
SHOW CREATE TABLE t3;
DROP TABLE t3;
-- echo # Compare with unhex:
CREATE TABLE t3 AS SELECT unhex(_bin) FROM at;
SHOW CREATE TABLE t3;
DROP TABLE t3;
DROP TABLE at;
-- echo # Bad arguments
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(2);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin(2);
--Error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT bin_to_uuid();
--Error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT uuid_to_bin();
--Error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT bin_to_uuid(x'12345678123456781234567812345678', true, false);
--Error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT uuid_to_bin('12345678-1234-5678-1234-567812345678', true, false);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(x'');
--error ER_WRONG_VALUE_FOR_TYPE
SELECT bin_to_uuid(x'', true);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('');
--error ER_WRONG_VALUE_FOR_TYPE
SELECT uuid_to_bin('', true);
set @a=uuid();
SELECT bin_to_uuid(uuid_to_bin(@a)) = @a;
-- echo # tests with prepared statements
PREPARE s FROM "SELECT bin_to_uuid(x'7f9d04ae61b34468ac798ffcc984ab68',true)";
EXECUTE s;
EXECUTE s;
PREPARE s2 FROM "SELECT bin_to_uuid(uuid_to_bin('{12345678-1234-5678-1234-567812345678}', true), true)";
EXECUTE s2;
EXECUTE s2;
-- echo # Generated column
CREATE TABLE t1(col1 varchar(100), gcol2 binary(16) AS (uuid_to_bin(col1)) virtual, index(gcol2));
INSERT into t1(col1) VALUES
('{12345678-1234-5678-1234-567812345678}'),
('12345679123456781234567812345678'),
('12345670-1234-5678-1234-567812345678');
EXPLAIN SELECT col1, HEX(gcol2) FROM t1 where gcol2=x'12345679123456781234567812345678';
SELECT col1, HEX(gcol2) FROM t1 where gcol2=x'12345679123456781234567812345678';
-- echo # Gcol expression is recognized and gcol index is used:
EXPLAIN SELECT col1, HEX(gcol2) FROM t1 where uuid_to_bin(col1)=x'12345679123456781234567812345678';
SELECT col1, HEX(gcol2) FROM t1 where uuid_to_bin(col1)=x'12345679123456781234567812345678';
CREATE TABLE t2(col1 binary(16), gcol2 varchar(36) AS (bin_to_uuid(col1)) virtual, index(col1), index(gcol2));
INSERT into t2(col1) VALUES
(x'12345678123456781234567812345678'),
(x'12345679123456781234567812345678'),
(x'12345670123456781234567812345678');
EXPLAIN SELECT HEX(col1), gcol2 FROM t2 where col1=x'12345679123456781234567812345678';
SELECT HEX(col1), gcol2 FROM t2 where col1=x'12345679123456781234567812345678';
EXPLAIN SELECT HEX(col1), gcol2 FROM t2 where gcol2='12345679-1234-5678-1234-567812345678';
SELECT HEX(col1), gcol2 FROM t2 where gcol2='12345679-1234-5678-1234-567812345678';
EXPLAIN SELECT HEX(col1), gcol2 FROM t2 where bin_to_uuid(col1)='12345679-1234-5678-1234-567812345678';
SELECT HEX(col1), gcol2 FROM t2 where bin_to_uuid(col1)='12345679-1234-5678-1234-567812345678';
DROP TABLE t1;
DROP TABLE t2;
-- echo #
-- echo # Bug#26395601 DIFFERENT BEHAVIOR BETWEEN CLASSIC AND NODE SESSION WITH UUID()
-- echo #
CREATE TABLE t as SELECT replace(uuid(), '-', '=') as v from mysql.user;
SELECT COUNT(DISTINCT v) FROM t;
DROP TABLE t;
|
