diff options
Diffstat (limited to 'plugin/type_uuid/mysql-test/type_uuid/type_uuid.test')
| -rw-r--r-- | plugin/type_uuid/mysql-test/type_uuid/type_uuid.test | 1695 |
1 files changed, 1695 insertions, 0 deletions
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.test new file mode 100644 index 00000000..0979582a --- /dev/null +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.test @@ -0,0 +1,1695 @@ +--echo # +--echo # MDEV-4958 Adding datatype UUID +--echo # + +--echo # +--echo # Basic CREATE functionality, defaults, metadata +--echo # + +--error ER_WRONG_FIELD_SPEC +CREATE TABLE t1 (a UUID AUTO_INCREMENT); + +CREATE TABLE t1 (a UUID); +SHOW CREATE TABLE t1; +DESCRIBE t1; +--vertical_results +--replace_column 19 # +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +--horizontal_results +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +--enable_metadata +SELECT * FROM t1; +SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID) AS a; +--disable_metadata +DROP TABLE t1; + + +CREATE TABLE t1 ( + c1 UUID DEFAULT 0x00000000000000000000000000000000, + c2 UUID DEFAULT 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF, + c3 UUID DEFAULT '00000000-0000-0000-0000-000000000000', + c4 UUID DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff', + c5 UUID DEFAULT CAST(X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' AS UUID) +); +SHOW CREATE TABLE t1; +DESCRIBE t1; +--vertical_results +--replace_column 19 # +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +--horizontal_results +DROP TABLE t1; + +--error ER_INVALID_DEFAULT +CREATE TABLE t1 (c1 UUID DEFAULT 0x00); +--error ER_INVALID_DEFAULT +CREATE TABLE t1 (c1 UUID DEFAULT ''); + + +CREATE TABLE t1 (a UUID); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('x'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 VALUES (1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 VALUES (TIME'10:20:30'); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (0x00); +DROP TABLE t1; + +--echo # +--echo # CAST +--echo # + +SELECT CAST('garbage' AS UUID); +SELECT CAST(0x01 AS UUID); +SELECT CAST(REPEAT(0x00,16) AS UUID); +SELECT CAST(REPEAT(0x11,16) AS UUID); + +CREATE TABLE t1 AS SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Text and binary formats, comparison operators +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000001); +INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000002); +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t1 ORDER BY a DESC; +SELECT HEX(a),a FROM t1 ORDER BY a; +SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000'; +SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000001'; +SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000001'; +SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000002'; +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000; +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001; +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001; +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002; +SELECT * FROM t1 WHERE a< '00000000-0000-0000-0000-000000000000'; +SELECT * FROM t1 WHERE a<='00000000-0000-0000-0000-000000000000'; +SELECT * FROM t1 WHERE a>='ffff0000-0000-0000-0000-000000000002'; +SELECT * FROM t1 WHERE a> 'ffff0000-0000-0000-0000-000000000002'; +SELECT * FROM t1 WHERE a IN +( + '00000000-0000-0000-0000-000000000000', + 'ffff0000-0000-0000-0000-000000000001' +) ORDER BY a; +SELECT * FROM t1 WHERE a IN +( + '00000000-0000-0000-0000-000000000000', + 0xffff0000000000000000000000000002 +) ORDER BY a; + +SELECT * FROM t1 WHERE a<'garbage'; +SELECT * FROM t1 WHERE a<='garbage'; +SELECT * FROM t1 WHERE a='garbage'; +SELECT * FROM t1 WHERE a>='garbage'; +SELECT * FROM t1 WHERE a>'garbage'; + +SELECT * FROM t1 WHERE a<0x01; +SELECT * FROM t1 WHERE a<=0x01; +SELECT * FROM t1 WHERE a=0x01; +SELECT * FROM t1 WHERE a>=0x01; +SELECT * FROM t1 WHERE a>0x01; + +SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000'; +SELECT * FROM t1 WHERE a='00-000000-0000-0000-0000-000000000000'; +SELECT * FROM t1 WHERE a='00-00-0000-0000-0000-0000-000000000000'; +SELECT * FROM t1 WHERE a='00-00-00-00-0000-0000-0000-000000000000'; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0.0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0e0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=TIME'10:20:30'; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a IN ('::', 10); + +DROP TABLE t1; + +--echo # +--echo # ORDER BY +--echo # + +CREATE TABLE t1 (a UUID); +DELIMITER $$; +FOR i IN 0..15 +DO + INSERT INTO t1 VALUES (REPLACE('XX000000-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00XX0000-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('0000XX00-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('000000XX-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-XX00-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-00XX-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-XX00-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-00XX-0000-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-XX00-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-00XX-000000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-XX0000000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-00XX00000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-0000XX000000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-000000XX0000','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-00000000XX00','XX',LPAD(HEX(i),2,'0'))); + INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-0000000000XX','XX',LPAD(HEX(i),2,'0'))); +END FOR; +$$ +DELIMITER ;$$ + +--echo # +--echo # Logical ORDER BY +--echo # +SELECT * FROM t1 ORDER BY a; +SELECT COALESCE(NULL, a) FROM t1 ORDER BY a; + +--echo # +--echo # Lexicographical ORDER BY +--echo # + +SELECT * FROM t1 ORDER BY CAST(a AS BINARY(16)); +SELECT * FROM t1 ORDER BY CAST(COALESCE(NULL,a) AS BINARY(16)); + +DROP TABLE t1; + + +--echo # +--echo # cmp_item_uuid: IN for non-constants +--echo # + +CREATE TABLE t1 (a UUID, b UUID); +INSERT INTO t1 VALUES +( + '00000000-0000-0000-0000-000000000001', + '00000000-0000-0000-0000-000000000002' +); +SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000000' IN (a, b); +SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000001' IN (a, b); +SELECT * FROM t1 WHERE '00-000000-0000-0000-0000-000000000001' IN (a, b); +SELECT * FROM t1 WHERE '00-00-0000-0000-0000-0000-000000000001' IN (a, b); +DROP TABLE t1; + + +--echo # +--echo # cmp_item_uuid: DECODE_ORACLE +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +(NULL), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); +SELECT a, DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000002', '01') AS d FROM t1; +SELECT + a, + DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000001', '01') AS d0, + DECODE_ORACLE(a, NULL, '<NULL>', '00000000-0000-0000-0000-000000000001', '01') AS d1, + DECODE_ORACLE(a, 'garbage', '<NULL>', '00000000-0000-0000-0000-000000000001', '01') AS d2 +FROM t1; +DROP TABLE t1; + + +--echo # +--echo # CASE abbreviations +--echo # + +CREATE TABLE t1 ( + c UUID, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext TEXT, + c_longtext LONGTEXT +); +CREATE TABLE t2 AS SELECT + COALESCE(c, c_char), + COALESCE(c, c_varchar), + COALESCE(c, c_tinytext), + COALESCE(c, c_text), + COALESCE(c, c_mediumtext), + COALESCE(c, c_longtext) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT + LEAST(c, c_char), + LEAST(c, c_varchar), + LEAST(c, c_tinytext), + LEAST(c, c_text), + LEAST(c, c_mediumtext), + LEAST(c, c_longtext) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +(NULL), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); + +SELECT COALESCE(a, '00000000-0000-0000-0000-000000000000') FROM t1 ORDER BY a; + +SELECT + a, + LEAST(a,'00000000-0000-0000-0000-000000000000'), + LEAST(a,'00000000-0000-0000-0000-00000000000f') +FROM t1 ORDER BY a; + +SELECT + a, + GREATEST(a,'00000000-0000-0000-0000-000000000000'), + GREATEST(a,'00000000-0000-0000-0000-00000000000f') +FROM t1 ORDER BY a; + +CREATE TABLE t2 AS SELECT + COALESCE(a, '00000000-0000-0000-0000-000000000000'), + LEAST(a,'00000000-0000-0000-0000-000000000000'), + GREATEST(a,'00000000-0000-0000-0000-000000000000') +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a; +SELECT + a, + LEAST(a, 0x00000000000000000000000000000000), + LEAST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; + +SELECT + a, + GREATEST(a, 0x00000000000000000000000000000000), + GREATEST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; + +CREATE TABLE t2 AS SELECT + COALESCE(a, 0x00000000000000000000000000000000), + LEAST(a,0x00000000000000000000000000000000), + GREATEST(a,0x00000000000000000000000000000000) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(a, 10) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(a, 10) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT GREATEST(a, 10) FROM t1; + +DROP TABLE t1; + +SELECT COALESCE('garbage', CAST('::1' AS UUID)); +SELECT COALESCE(0x01, CAST('::1' AS UUID)); + + +--echo # +--echo # Uniqueness +--echo # + +CREATE TABLE t1 (a UUID NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES +('41000000-0000-0000-0000-000000000001'), +('61000000-0000-0000-0000-000000000001'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('41000000-0000-0000-0000-000000000001'); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Indexes +--echo # + +--error ER_WRONG_SUB_KEY +CREATE TABLE t1 (a UUID, KEY(a(1))); + + +--echo # +--echo # Explicit CAST on INSERT +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000001' AS UUID)); +INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000002' AS UUID)); +INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000003' AS UUID)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000001') AS UUID)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000002') AS UUID)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000003') AS UUID)); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Explicit CAST and implicit CAST on ALTER +--echo # + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES +('garbage'), +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000001'), +('ffff0000-0000-0000-0000-000000000001'), +('ffff0000-0000-0000-0000-000000000002'); +SELECT a, CAST(a AS UUID) FROM t1 ORDER BY a; +SELECT a, CAST(a AS UUID) FROM t1 ORDER BY CAST(a AS UUID); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a UUID; +SET sql_mode=''; +ALTER TABLE t1 MODIFY a UUID; +SET sql_mode=DEFAULT; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000002); +SELECT HEX(a), CAST(a AS UUID) FROM t1 ORDER BY a; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # INSERT..SELECT, same data types +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); +CREATE TABLE t2 (a UUID); +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2; +DROP TABLE t1,t2; + + +--echo # +--echo # Implicit CAST on INSERT..SELECT, text format +--echo # + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES +('garbage'), +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000001'), +('ffff0000-0000-0000-0000-000000000001'), +('ffff0000-0000-0000-0000-000000000002'); + +CREATE TABLE t2 (a UUID); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT a FROM t1; +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2 ORDER BY a; +SET sql_mode=DEFAULT; +DROP TABLE t2; + +CREATE TABLE t2 (a UUID NOT NULL); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT a FROM t1; +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2 ORDER BY a; +SET sql_mode=DEFAULT; +DROP TABLE t2; + +DROP TABLE t1; + + +--echo # +--echo # Implicit CAST on INSERT..SELECT, binary format +--echo # + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000002); +CREATE TABLE t2 (a UUID); +INSERT INTO t2 SELECT a FROM t1; +SELECT a FROM t2 ORDER BY a; +DROP TABLE t1,t2; + + +--echo # +--echo # CAST to other data types +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DOUBLE); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS FLOAT); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DECIMAL); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS SIGNED); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS UNSIGNED); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS TIME); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATE); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATETIME); + +SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR); +CREATE TABLE t1 AS SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR) AS a; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff'); +CREATE TABLE t2 AS SELECT + CAST(a AS CHAR), + CAST(a AS CHAR(36)), + CAST(a AS CHAR(530)), + CAST(a AS CHAR(65535)), + CAST(a AS CHAR(66000)), + CAST(a AS CHAR(16777215)), + CAST(a AS CHAR(16777216)) +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff'); +CREATE TABLE t2 AS SELECT + CAST(a AS BINARY(4)) AS cb4, + CAST(a AS BINARY) AS cb, + CAST(a AS BINARY(16)) AS cb16, + CAST(a AS BINARY(32)) AS cb32, + CAST(a AS BINARY(530)) AS cb530, + CAST(a AS BINARY(65535)) AS cb65535, + CAST(a AS BINARY(66000)) AS cb66000, + CAST(a AS BINARY(16777215)) AS cb16777215, + CAST(a AS BINARY(16777216)) AS cb16777216 +FROM t1 LIMIT 0; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CAST(a AS BINARY(4)) AS cb4, + CAST(a AS BINARY) AS cb, + CAST(a AS BINARY(16)) AS cb16, + CAST(a AS BINARY(32)) AS cb32, + CAST(a AS BINARY(530)) AS cb530, + CAST(a AS BINARY(65535)) AS cb65535 +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT + HEX(cb4), + HEX(cb), + HEX(cb16), + HEX(cb32), + LENGTH(cb530), + LENGTH(cb65535) +FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Implicit conversion to other types in INSERT +--echo # + +CREATE TABLE t1 (a INT); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(32,0)); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); +DROP TABLE t1; + + + +--echo # +--echo # Boolean context +--echo # + +SELECT + CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS TRUE, + CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS FALSE, + CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS TRUE, + CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS FALSE; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000001'); +SELECT a, a IS TRUE, a IS FALSE FROM t1 ORDER BY a; +DROP TABLE t1; + +# +# TODO: Error looks like a bug. This should return rows where a<>'00000000-0000-0000-0000-000000000000'. +# The same problem is repeatable with GEOMETRY. +# +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a; +DROP TABLE t1; + + +--echo # +--echo # GROUP BY +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-0000-00000000'); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-0000-00000001'), +('00000000-0000-0000-0000-0000-0000-0001'); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000002'), +('00000000-0000-0000-0000-000000000002'), +('00000000-0000-0000-0000-0000-00000002'), +('00000000-0000-0000-0000-0000-0000-0002'); +SELECT a, COUNT(*) FROM t1 GROUP BY a; +DROP TABLE t1; + +--echo # +--echo # Aggregate functions +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000000'); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-0000-00000001'), +('00000000-0000-0000-0000-0000-0000-0001'); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000002'), +('00000000-0000-0000-0000-000000000002'), +('00000000-0000-0000-0000-000000000002'), +('00000000-0000-0000-0000-000000000002'); +SELECT MIN(a),MAX(a) FROM t1; + +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT AVG(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT AVG(DISTINCT a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT SUM(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT SUM(DISTINCT a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT STDDEV(a) FROM t1; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; +SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; +DROP TABLE t1; + +--echo # +--echo # Window functions +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'), +('00000000-0000-0000-0000-000000000003'), +('00000000-0000-0000-0000-000000000004'); +SELECT + a, + LAG(a) OVER (ORDER BY a), + LEAD(a) OVER (ORDER BY a) +FROM t1 ORDER BY a; + +SELECT + a, + FIRST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), + LAST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Prepared statements +--echo # + +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a' + USING CAST('00000000-0000-0000-0000-000000000000' AS UUID); +SHOW CREATE TABLE t1; + +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' + USING '00000000-0000-0000-0000-000000000001'; + +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' + USING CAST('00000000-0000-0000-0000-000000000002' AS UUID); + +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' + USING 0x00000000000000000000000000000003; + +SELECT a FROM t1 ORDER BY a; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' + USING '00000000-0000-0000-0000-000000000001'; + +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' + USING CAST('00000000-0000-0000-0000-000000000002' AS UUID); + +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' + USING 0x00000000000000000000000000000003; +DROP TABLE t1; + + +--echo # +--echo # Character set and collation aggregation +--echo # + +CREATE TABLE t1 (a UUID); + +CREATE TABLE t2 AS SELECT + CONCAT(a) AS c1, + CONCAT(CAST('00000000-0000-0000-0000-000000000000' AS UUID)) AS c2 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CONCAT(_utf8'1', LEFT(a,35)) AS c1, + CONCAT(_utf8'1', LEFT(CAST('00000000-0000-0000-0000-000000000000' AS UUID),35)) AS c2, + CONCAT(_utf8'1', LEFT(COALESCE(a),35)) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CONCAT(_latin1'1', LEFT(a,35)) AS c1, + CONCAT(_latin1'1', LEFT(CAST('00000000-0000-0000-0000-000000000000' AS UUID),35)) AS c2, + CONCAT(_latin1'1', LEFT(COALESCE(a),35)) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + + +--echo # +--echo # UNION +--echo # + +CREATE TABLE t1 AS + SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c + UNION + SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID); +SHOW CREATE TABLE t1; +SELECT * FROM t1 ORDER BY c; +DROP TABLE t1; + +CREATE TABLE t1 AS + SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c + UNION + SELECT '00000000-0000-0000-0000-000000000001'; +SHOW CREATE TABLE t1; +SELECT * FROM t1 ORDER BY c; +DROP TABLE t1; + +CREATE TABLE t1 AS + SELECT '00000000-0000-0000-0000-000000000000' AS c + UNION + SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID); +SHOW CREATE TABLE t1; +SELECT * FROM t1 ORDER BY c; +DROP TABLE t1; + +CREATE TABLE t1 AS + SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c + UNION + SELECT 0x00000000000000000000000000000001; +SHOW CREATE TABLE t1; +SELECT * FROM t1 ORDER BY c; +DROP TABLE t1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +CREATE TABLE t1 AS + SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c + UNION + SELECT 1; + + +--echo # +--echo # Unary operators +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT -CAST('00000000-0000-0000-0000-000000000000' AS UUID); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ABS(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ROUND(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CEILING(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT FLOOR(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + + +--echo # +--echo # Arithmetic operators +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) + 1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) - 1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) * 1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) / 1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) MOD 1; + + +--echo # +--echo # Misc +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT RAND(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT FROM_UNIXTIME(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT HOUR(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT YEAR(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT RELEASE_LOCK(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +SELECT JSON_LENGTH(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); + +--echo # +--echo # Virtual columns +--echo # + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT, + b UUID GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS UUID)), INDEX(b) +); + +CREATE TABLE t1 ( + a INT, + b UUID GENERATED ALWAYS AS (CAST(CONCAT('00000000-0000-0000-0000-00000000001',HEX(a)) AS UUID)), INDEX(b) +); +INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # VIEW +--echo # + +CREATE TABLE t1 (a INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT * FROM t1 ORDER BY a; +CREATE VIEW v1 AS SELECT (CAST(CONCAT('00000000-0000-0000-0000-00000000001',HEX(a)) AS UUID)) AS c FROM t1; +SELECT * FROM v1 ORDER BY c; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID DEFAULT '00000000-0000-0000-0000-000000000000'); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DESCRIBE v1; +INSERT INTO v1 VALUES +(DEFAULT), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID DEFAULT CAST('00000000-0000-0000-0000-000000000000' AS UUID)); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DESCRIBE v1; +INSERT INTO v1 VALUES +(DEFAULT), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # Subqueries +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES +('00000000-0000-0000-0000-000000000000'), +('00000000-0000-0000-0000-000000000001'), +('00000000-0000-0000-0000-000000000002'); +SELECT * FROM t1 WHERE a=(SELECT MIN(a) FROM t1) ORDER BY a; +SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a; +SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'00000000-0000-0000-0000-000000000000') ORDER BY a; +DROP TABLE t1; + +--echo # +--echo # Stored routines +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a UUID) +BEGIN + DECLARE b UUID DEFAULT CONCAT('1',SUBSTRING(a,2,36)); + SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1('00000000-0000-0000-0000-000000000001'); +CALL p1(CAST('00000000-0000-0000-0000-000000000002' AS UUID)); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE FUNCTION f1(a UUID) RETURNS UUID +BEGIN + RETURN CONCAT('1',SUBSTRING(a,2,36)); +END; +$$ +DELIMITER ;$$ +SELECT f1('00000000-0000-0000-0000-000000000001'); +SELECT f1(CAST('00000000-0000-0000-0000-000000000002' AS UUID)); +DROP FUNCTION f1; + +--echo # +--echo # Anchored data types in SP variables +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE va TYPE OF t1.a; + SELECT MAX(a) INTO va FROM t1; + SELECT va; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a UUID, b UUID); +INSERT INTO t1 VALUES +( + '00000000-0000-0000-0000-00000000000a', + '00000000-0000-0000-0000-00000000000b' +); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE va ROW TYPE OF t1; + SELECT MAX(a), MAX(b) INTO va FROM t1; + SELECT va.a, va.b; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Optimizer: make_const_item_for_comparison +--echo # + +CREATE TABLE t1 (id INT, a UUID); +INSERT INTO t1 VALUES +(1,'00000000-0000-0000-0000-000000000001'), +(2,'00000000-0000-0000-0000-000000000002'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID)) AND id>0; +DROP TABLE t1; + +--echo # +--echo # Optimizer: equal field propagation +--echo # + +CREATE TABLE t1 (id INT, a UUID); +INSERT INTO t1 VALUES +(1,'00000000-0000-0000-0000-000000000001'), +(2,'00000000-0000-0000-0000-000000000002'); +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID)) + AND LENGTH(CONCAT(a,RAND()))>1; +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID)) + AND LENGTH(a)>1; +DROP TABLE t1; + + +--echo # +--echo # Optimizer: equal expression propagation +--echo # + + +CREATE TABLE t1 (id INT, a UUID); +INSERT INTO t1 VALUES +(1,'00000000-0000-0000-0000-000000000001'), +(2,'00000000-0000-0000-0000-000000000002'); +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE COALESCE(a)='00000000-0000-0000-0000-000000000001' AND COALESCE(a)=CONCAT(a); +DROP TABLE t1; + +--echo # +--echo # Subquery materialization +--echo # + +CREATE TABLE t1 (a UUID, b VARCHAR(36), KEY (a), KEY(b)) ; +INSERT INTO t1 VALUES +( + '00000000-0000-0000-0000-00000000000a', + '00000000-0000-0000-0000-00000000000a' +), +( + '00000000-0000-0000-0000-00000000000a', + '00000000-0000-0000-0000-00000000000b' +); +SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +SET @@optimizer_switch=DEFAULT; +DROP TABLE t1; + + +--echo # +--echo # ALTER from UUID to UUID +--echo # + +CREATE TABLE t1 (a UUID, b INT); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001', 1); +ALTER TABLE t1 MODIFY b DECIMAL(10,2); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER to character string data types +--echo # + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS CHAR(36)) FROM t1; +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a VARCHAR(36); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a TINYTEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a TEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a MEDIUMTEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a LONGTEXT; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER from character string data types +--echo # + +CREATE OR REPLACE TABLE t1 (a CHAR(64)); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS UUID) FROM t1; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS UUID) FROM t1; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TINYTEXT); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS UUID) FROM t1; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS UUID) FROM t1; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a MEDIUMTEXT); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS UUID) FROM t1; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a LONGTEXT); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS UUID) FROM t1; +ALTER TABLE t1 MODIFY a UUID; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER to binary string data types +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a BINARY(17); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +--error ER_DATA_TOO_LONG +ALTER TABLE t1 MODIFY a BINARY(15); +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a TINYBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a BLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a MEDIUMBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a LONGBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER from binary string data types +--echo # + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a UUID; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BINARY(17)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a UUID; +DROP TABLE t1; + +CREATE TABLE t1 (a BINARY(15)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a UUID; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a UUID; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a UUID; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a UUID; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a UUID; +SELECT a FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from UUID to UUID +--echo # + +CREATE TABLE t1 (a UUID, b UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +--echo # +--echo # SET from UUID to numeric +--echo # + +CREATE TABLE t1 (a UUID, b INT); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b DOUBLE); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b DECIMAL(32,0)); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b YEAR); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from numeric to UUID +--echo # + +CREATE TABLE t1 (a INT, b UUID); +INSERT INTO t1 VALUES (1, NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE, b UUID); +INSERT INTO t1 VALUES (1, NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(32,0), b UUID); +INSERT INTO t1 VALUES (1, NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a YEAR, b UUID); +INSERT INTO t1 VALUES (1, NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from UUID to temporal +--echo # + +CREATE TABLE t1 (a UUID, b TIME); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b DATE); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b DATETIME); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b TIMESTAMP NULL DEFAULT NULL); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from temporal to UUID +--echo # + +CREATE TABLE t1 (a TIME, b UUID); +INSERT INTO t1 VALUES ('00:00:00', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATE, b UUID); +INSERT INTO t1 VALUES ('2001-01:01', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME, b UUID); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TIMESTAMP, b UUID); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from UUID to character string +--echo # + +CREATE TABLE t1 (a UUID, b CHAR(39)); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b VARCHAR(39)); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b TEXT); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b ENUM('ffffffff-ffff-ffff-ffff-ffffffffffff')); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b SET('ffffffff-ffff-ffff-ffff-ffffffffffff')); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from character string to UUID +--echo # + +CREATE TABLE t1 (a CHAR(36), b UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(36), b UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT, b UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a ENUM('ffffffff-ffff-ffff-ffff-ffffffffffff'), b UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a SET('ffffffff-ffff-ffff-ffff-ffffffffffff'), b UUID); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from UUID to binary +--echo # + +CREATE TABLE t1 (a UUID, b BINARY(16)); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b VARBINARY(39)); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a UUID, b BLOB); +INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from binary to UUID +--echo # + +CREATE TABLE t1 (a BINARY(16), b UUID); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARBINARY(16), b UUID); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB, b UUID); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Limit clause parameter +--echo # TODO: this should fail. +--echo # The test for a valid data type should be moved +--echo # from parse time to fix_fields() time, and performed +--echo # for both Item_splocal and Item_param. +--echo # + +EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('00000000-0000-0000-0000-000000000000' AS UUID); + + +# TODO: +# - Add hooks to run mysql_client_test with pluggable data types +# +# - This should fail with the "illegal data type" error: +#SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) DIV 1; +# +# - This should fail with the "illegal data type" error: +# EXTRACT(MINUTE...) +# + + +--echo # +--echo # ALTER from UUID to CHAR +--echo # + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT CAST(a AS CHAR(36)) FROM t1; +ALTER TABLE t1 MODIFY a CHAR(36); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # ALTER from UUID to BINARY(16) +--echo # + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # CAST(uuid AS BINARY) +--echo # + +CREATE OR REPLACE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); +SELECT HEX(CAST(a AS BINARY)) FROM t1; +SELECT HEX(CAST(a AS BINARY(16))) FROM t1; +DROP TABLE t1; + +--echo # +--echo # CAST from UUID to FLOAT +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(a AS FLOAT) FROM t1; +DROP TABLE t1; + +# TODO: below does not work well +#--echo # +#--echo # Conversion from UUID to other types +#--echo # +# +#CREATE TABLE t1 (a UUID, b INT); +#INSERT INTO t1 (a) VALUES ('00000000-0000-0000-0000-000000000000'); +#--error WARN_DATA_TRUNCATED +#UPDATE t1 SET b=a; +#SELECT * FROM t1; +#DROP TABLE t1; +# +#SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +#CREATE TABLE t1 (a UUID, b TIMESTAMP); +#INSERT INTO t1 (a) VALUES ('00000000-0000-0000-0000-000000000000'); +#--error WARN_DATA_TRUNCATED +#UPDATE t1 SET b=a; +#SELECT * FROM t1; +#DROP TABLE t1; +#SET timestamp=DEFAULT; +# +#CREATE OR REPLACE TABLE t1 (a UUID); +#INSERT INTO t1 (a) VALUES ('00000000-0000-0000-0000-000000000000'); +#--error WARN_DATA_TRUNCATED +#ALTER TABLE t1 MODIFY a DATE; +#DROP TABLE t1; + +--echo # +--echo # CAST(UUID AS BINARY) - metadata +--echo # + +CREATE TABLE t1 (a UUID); +--enable_metadata +SELECT + CAST(a AS BINARY(0)), + CAST(a AS BINARY(1)), + CAST(a AS BINARY(16)), + CAST(a AS BINARY(255)), + CAST(a AS BINARY(256)), + CAST(a AS BINARY(512)), + CAST(a AS BINARY(513)), + CAST(a AS BINARY(65532)), + CAST(a AS BINARY(65533)), + CAST(a AS BINARY(65534)), + CAST(a AS BINARY(65535)), + CAST(a AS BINARY(65536)), + CAST(a AS BINARY(16777215)), + CAST(a AS BINARY(16777216)) +FROM t1; +--disable_metadata +DROP TABLE t1; + +--echo # +--echo # MIN(uuid) with GROUP BY +--echo # + +CREATE TABLE t1 (id INT, a UUID) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1, '00000000-0000-0000-0000-000000000fff'), +(1, '00000000-0000-0000-0000-000000008888'); +SELECT MIN(a), MAX(a) FROM t1 GROUP BY id; +DROP TABLE t1; + + +--echo # +--echo # MDEV-26785 Hyphens inside the value of uuid datatype +--echo # + +CREATE TABLE t1 (a UUID); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('0000000000000000000000000000000'/*31 digits*/); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('000000000000000000000000000000000'/*33 digits*/); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('-00000000000000000000000000000000'/*leading hyphen*/); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('-00000000000000000000000000000000-'/*trailing hyphen*/); + +INSERT INTO t1 VALUES ('00000000000000000000000000000000'); +INSERT INTO t1 VALUES ('0-0000000000000000000000000000011'); +INSERT INTO t1 VALUES ('0--0000000000000000000000000000012'); +INSERT INTO t1 VALUES ('0---0000000000000000000000000000013'); +INSERT INTO t1 VALUES ('0----0000000000000000000000000000014'); +INSERT INTO t1 VALUES ('00-000000000000000000000000000021'); +INSERT INTO t1 VALUES ('00--000000000000000000000000000022'); +INSERT INTO t1 VALUES ('00---000000000000000000000000000023'); +INSERT INTO t1 VALUES ('00----000000000000000000000000000024'); + +INSERT INTO t1 VALUES ('5796dac11a1c11--------------ecab4ef859-713e4be4'); +INSERT INTO t1 VALUES ('5796dac11a1c11---------------ecab4ef859-713e4be4'); +DROP TABLE t1; + +--echo # +--echo # MDEV-26732 Assertion `0' failed in Item::val_native +--echo # + +SELECT uuid() AS f, var_pop('x') FROM dual HAVING f > ''; + + +--echo # +--echo # MDEV-28491 Uuid. "UPDATE/DELETE" not working "WHERE id IN (SELECT id FROM ..)" +--echo # + +CREATE TABLE companies (id uuid, name varchar(10)); +INSERT INTO companies (id) values ('7bc95b06-cc6c-11ec-96c5-0242ac130002'); + +CREATE TABLE divisions (company_id uuid); +INSERT INTO divisions (company_id) values ('7bc95b06-cc6c-11ec-96c5-0242ac130002'); +SELECT * FROM companies WHERE id IN (SELECT company_id FROM divisions); +UPDATE companies SET name = 'value' WHERE id IN (SELECT company_id FROM divisions); +SELECT * FROM companies; +DELETE FROM companies WHERE id IN (SELECT company_id FROM divisions); +SELECT * FROM companies; +DROP TABLE divisions; +DROP TABLE companies; + + +--echo # +--echo # MDEV-27100 Subquery using the ALL keyword on UUID columns produces a wrong result +--echo # + +CREATE TABLE t1 (d UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-111111111111'), ('11111111-0000-0000-0000-000000000000'); +SELECT * FROM t1 ORDER BY d; +SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1); +SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1); +DROP TABLE t1; + + +--echo # +--echo # MDEV-31719 Wrong result of: WHERE inet6_column IN ('','::1') +--echo # + +CREATE TABLE t1 (a UUID); +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000'); +SELECT * FROM t1 WHERE a IN ('','00000000-0000-0000-0000-000000000001'); +SELECT * FROM t1 WHERE a=''; +DROP TABLE t1; |