You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
220 lines
8.5 KiB
220 lines
8.5 KiB
#####################################################################
|
|
# #
|
|
# Tests for uuid_to_bin, bin_to_uuid and is_uuid functions. #
|
|
# #
|
|
#####################################################################
|
|
--echo #
|
|
--echo # WL#8920: Improve usability of UUID manipulations
|
|
--echo #
|
|
|
|
SELECT uuid_to_bin('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}', TRUE) AS a, uuid_to_bin('{e60c88ba-083f-4ceb-be59-f67636d718a2}', TRUE) AS b;
|
|
SELECT uuid_to_bin('c8eb4b15cb0948bbbbb2e6a0b6b4d5c7', TRUE) AS a, uuid_to_bin('e60c88ba083f4cebbe59f67636d718a2', TRUE) AS b;
|
|
SELECT uuid_to_bin('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7', TRUE) AS a, 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 uuid_to_bin(bin_to_uuid(a)), uuid_to_bin(bin_to_uuid(a)) = a, uuid_to_bin(bin_to_uuid(a, TRUE), TRUE), uuid_to_bin(bin_to_uuid(a, TRUE), TRUE) = a 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 * FROM t1 where gcol2=x'12345679123456781234567812345678';
|
|
SELECT * FROM t1 where gcol2=x'12345679123456781234567812345678';
|
|
|
|
-- echo # Gcol expression is recognized and gcol index is used:
|
|
EXPLAIN SELECT * FROM t1 where uuid_to_bin(col1)=x'12345679123456781234567812345678';
|
|
SELECT * 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 * FROM t2 where col1=x'12345679123456781234567812345678';
|
|
SELECT * FROM t2 where col1=x'12345679123456781234567812345678';
|
|
|
|
EXPLAIN SELECT * FROM t2 where gcol2='12345679-1234-5678-1234-567812345678';
|
|
SELECT * FROM t2 where gcol2='12345679-1234-5678-1234-567812345678';
|
|
EXPLAIN SELECT * FROM t2 where bin_to_uuid(col1)='12345679-1234-5678-1234-567812345678';
|
|
SELECT * 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;
|
|
|