# # MDEV-4958 Adding datatype UUID # # # Basic CREATE functionality, defaults, metadata # CREATE TABLE t1 (a UUID AUTO_INCREMENT); ERROR 42000: Incorrect column specifier for column 'a' CREATE TABLE t1 (a UUID); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` uuid DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DESCRIBE t1; Field Type Null Key Default Extra a uuid YES NULL SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1 COLUMN_NAME a ORDINAL_POSITION 1 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE uuid CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE uuid COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL DROP TABLE t1; CREATE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); SELECT * FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 a a 254 (type=uuid) 36 36 Y 160 0 8 a 00000000-0000-0000-0000-000000000001 SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID) AS a; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def a 254 (type=uuid) 36 36 N 33 0 8 a 00000000-0000-0000-0000-000000000001 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; Table Create Table t1 CREATE TABLE `t1` ( `c1` uuid DEFAULT '00000000-0000-0000-0000-000000000000', `c2` uuid DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff', `c3` uuid DEFAULT '00000000-0000-0000-0000-000000000000', `c4` uuid DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff', `c5` uuid DEFAULT cast(X'ffffffffffffffffffffffffffffffff' as uuid) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DESCRIBE t1; Field Type Null Key Default Extra c1 uuid YES 00000000-0000-0000-0000-000000000000 c2 uuid YES ffffffff-ffff-ffff-ffff-ffffffffffff c3 uuid YES 00000000-0000-0000-0000-000000000000 c4 uuid YES ffffffff-ffff-ffff-ffff-ffffffffffff c5 uuid YES cast(X'ffffffffffffffffffffffffffffffff' as uuid) SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1 COLUMN_NAME c1 ORDINAL_POSITION 1 COLUMN_DEFAULT '00000000-0000-0000-0000-000000000000' IS_NULLABLE YES DATA_TYPE uuid CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE uuid COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1 COLUMN_NAME c2 ORDINAL_POSITION 2 COLUMN_DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff' IS_NULLABLE YES DATA_TYPE uuid CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE uuid COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1 COLUMN_NAME c3 ORDINAL_POSITION 3 COLUMN_DEFAULT '00000000-0000-0000-0000-000000000000' IS_NULLABLE YES DATA_TYPE uuid CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE uuid COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1 COLUMN_NAME c4 ORDINAL_POSITION 4 COLUMN_DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff' IS_NULLABLE YES DATA_TYPE uuid CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE uuid COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1 COLUMN_NAME c5 ORDINAL_POSITION 5 COLUMN_DEFAULT cast(X'ffffffffffffffffffffffffffffffff' as uuid) IS_NULLABLE YES DATA_TYPE uuid CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE uuid COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL DROP TABLE t1; CREATE TABLE t1 (c1 UUID DEFAULT 0x00); ERROR 42000: Invalid default value for 'c1' CREATE TABLE t1 (c1 UUID DEFAULT ''); ERROR 42000: Invalid default value for 'c1' CREATE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('x'); ERROR 22007: Incorrect uuid value: 'x' for column `test`.`t1`.`a` at row 1 INSERT INTO t1 VALUES (1); ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`a` at row 1 INSERT INTO t1 VALUES (TIME'10:20:30'); ERROR 22007: Incorrect uuid value: '10:20:30' for column `test`.`t1`.`a` at row 1 INSERT INTO t1 VALUES (0x00); ERROR 22007: Incorrect uuid value: '\x00' for column `test`.`t1`.`a` at row 1 DROP TABLE t1; # # CAST # SELECT CAST('garbage' AS UUID); CAST('garbage' AS UUID) NULL Warnings: Warning 1292 Incorrect uuid value: 'garbage' SELECT CAST(0x01 AS UUID); CAST(0x01 AS UUID) NULL Warnings: Warning 1292 Incorrect uuid value: '\x01' SELECT CAST(REPEAT(0x00,16) AS UUID); CAST(REPEAT(0x00,16) AS UUID) 00000000-0000-0000-0000-000000000000 SELECT CAST(REPEAT(0x11,16) AS UUID); CAST(REPEAT(0x11,16) AS UUID) 11111111-1111-1111-1111-111111111111 CREATE TABLE t1 AS SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `CAST('00000000-0000-0000-0000-000000000000' AS UUID)` uuid NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # Text and binary formats, comparison operators # 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; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 SELECT * FROM t1 ORDER BY a DESC; a ffff0000-0000-0000-0000-000000000002 ffff0000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000000 SELECT HEX(a),a FROM t1 ORDER BY a; HEX(a) a 00000000000000000000000000000000 00000000-0000-0000-0000-000000000000 00000000000000000000000000000001 00000000-0000-0000-0000-000000000001 FFFF0000000000000000000000000001 ffff0000-0000-0000-0000-000000000001 FFFF0000000000000000000000000002 ffff0000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000'; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000001'; a 00000000-0000-0000-0000-000000000001 SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000001'; a ffff0000-0000-0000-0000-000000000001 SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000002'; a ffff0000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001; a 00000000-0000-0000-0000-000000000001 SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001; a ffff0000-0000-0000-0000-000000000001 SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002; a ffff0000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE a< '00000000-0000-0000-0000-000000000000'; a SELECT * FROM t1 WHERE a<='00000000-0000-0000-0000-000000000000'; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a>='ffff0000-0000-0000-0000-000000000002'; a ffff0000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE a> 'ffff0000-0000-0000-0000-000000000002'; a SELECT * FROM t1 WHERE a IN ( '00000000-0000-0000-0000-000000000000', 'ffff0000-0000-0000-0000-000000000001' ) ORDER BY a; a 00000000-0000-0000-0000-000000000000 ffff0000-0000-0000-0000-000000000001 SELECT * FROM t1 WHERE a IN ( '00000000-0000-0000-0000-000000000000', 0xffff0000000000000000000000000002 ) ORDER BY a; a 00000000-0000-0000-0000-000000000000 ffff0000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE a<'garbage'; a Warnings: Warning 1292 Incorrect uuid value: 'garbage' SELECT * FROM t1 WHERE a<='garbage'; a Warnings: Warning 1292 Incorrect uuid value: 'garbage' SELECT * FROM t1 WHERE a='garbage'; a Warnings: Warning 1292 Incorrect uuid value: 'garbage' Warning 1292 Incorrect uuid value: 'garbage' SELECT * FROM t1 WHERE a>='garbage'; a Warnings: Warning 1292 Incorrect uuid value: 'garbage' SELECT * FROM t1 WHERE a>'garbage'; a Warnings: Warning 1292 Incorrect uuid value: 'garbage' SELECT * FROM t1 WHERE a<0x01; a Warnings: Warning 1292 Incorrect uuid value: '\x01' SELECT * FROM t1 WHERE a<=0x01; a Warnings: Warning 1292 Incorrect uuid value: '\x01' SELECT * FROM t1 WHERE a=0x01; a Warnings: Warning 1292 Incorrect uuid value: '\x01' Warning 1292 Incorrect uuid value: '\x01' SELECT * FROM t1 WHERE a>=0x01; a Warnings: Warning 1292 Incorrect uuid value: '\x01' SELECT * FROM t1 WHERE a>0x01; a Warnings: Warning 1292 Incorrect uuid value: '\x01' SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000'; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a='00-000000-0000-0000-0000-000000000000'; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a='00-00-0000-0000-0000-0000-000000000000'; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a='00-00-00-00-0000-0000-0000-000000000000'; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a=0; ERROR HY000: Illegal parameter data types uuid and int for operation '=' SELECT * FROM t1 WHERE a=0.0; ERROR HY000: Illegal parameter data types uuid and decimal for operation '=' SELECT * FROM t1 WHERE a=0e0; ERROR HY000: Illegal parameter data types uuid and double for operation '=' SELECT * FROM t1 WHERE a=TIME'10:20:30'; ERROR HY000: Illegal parameter data types uuid and time for operation '=' SELECT * FROM t1 WHERE a IN ('::', 10); ERROR HY000: Illegal parameter data types uuid and int for operation 'in' DROP TABLE t1; # # ORDER BY # CREATE TABLE t1 (a UUID); 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; $$ # # Logical ORDER BY # SELECT * FROM t1 ORDER BY a; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000001-0000-0000-0000-000000000000 00000002-0000-0000-0000-000000000000 00000003-0000-0000-0000-000000000000 00000004-0000-0000-0000-000000000000 00000005-0000-0000-0000-000000000000 00000006-0000-0000-0000-000000000000 00000007-0000-0000-0000-000000000000 00000008-0000-0000-0000-000000000000 00000009-0000-0000-0000-000000000000 0000000a-0000-0000-0000-000000000000 0000000b-0000-0000-0000-000000000000 0000000c-0000-0000-0000-000000000000 0000000d-0000-0000-0000-000000000000 0000000e-0000-0000-0000-000000000000 0000000f-0000-0000-0000-000000000000 00000100-0000-0000-0000-000000000000 00000200-0000-0000-0000-000000000000 00000300-0000-0000-0000-000000000000 00000400-0000-0000-0000-000000000000 00000500-0000-0000-0000-000000000000 00000600-0000-0000-0000-000000000000 00000700-0000-0000-0000-000000000000 00000800-0000-0000-0000-000000000000 00000900-0000-0000-0000-000000000000 00000a00-0000-0000-0000-000000000000 00000b00-0000-0000-0000-000000000000 00000c00-0000-0000-0000-000000000000 00000d00-0000-0000-0000-000000000000 00000e00-0000-0000-0000-000000000000 00000f00-0000-0000-0000-000000000000 00010000-0000-0000-0000-000000000000 00020000-0000-0000-0000-000000000000 00030000-0000-0000-0000-000000000000 00040000-0000-0000-0000-000000000000 00050000-0000-0000-0000-000000000000 00060000-0000-0000-0000-000000000000 00070000-0000-0000-0000-000000000000 00080000-0000-0000-0000-000000000000 00090000-0000-0000-0000-000000000000 000a0000-0000-0000-0000-000000000000 000b0000-0000-0000-0000-000000000000 000c0000-0000-0000-0000-000000000000 000d0000-0000-0000-0000-000000000000 000e0000-0000-0000-0000-000000000000 000f0000-0000-0000-0000-000000000000 01000000-0000-0000-0000-000000000000 02000000-0000-0000-0000-000000000000 03000000-0000-0000-0000-000000000000 04000000-0000-0000-0000-000000000000 05000000-0000-0000-0000-000000000000 06000000-0000-0000-0000-000000000000 07000000-0000-0000-0000-000000000000 08000000-0000-0000-0000-000000000000 09000000-0000-0000-0000-000000000000 0a000000-0000-0000-0000-000000000000 0b000000-0000-0000-0000-000000000000 0c000000-0000-0000-0000-000000000000 0d000000-0000-0000-0000-000000000000 0e000000-0000-0000-0000-000000000000 0f000000-0000-0000-0000-000000000000 00000000-0001-0000-0000-000000000000 00000000-0002-0000-0000-000000000000 00000000-0003-0000-0000-000000000000 00000000-0004-0000-0000-000000000000 00000000-0005-0000-0000-000000000000 00000000-0006-0000-0000-000000000000 00000000-0007-0000-0000-000000000000 00000000-0008-0000-0000-000000000000 00000000-0009-0000-0000-000000000000 00000000-000a-0000-0000-000000000000 00000000-000b-0000-0000-000000000000 00000000-000c-0000-0000-000000000000 00000000-000d-0000-0000-000000000000 00000000-000e-0000-0000-000000000000 00000000-000f-0000-0000-000000000000 00000000-0100-0000-0000-000000000000 00000000-0200-0000-0000-000000000000 00000000-0300-0000-0000-000000000000 00000000-0400-0000-0000-000000000000 00000000-0500-0000-0000-000000000000 00000000-0600-0000-0000-000000000000 00000000-0700-0000-0000-000000000000 00000000-0800-0000-0000-000000000000 00000000-0900-0000-0000-000000000000 00000000-0a00-0000-0000-000000000000 00000000-0b00-0000-0000-000000000000 00000000-0c00-0000-0000-000000000000 00000000-0d00-0000-0000-000000000000 00000000-0e00-0000-0000-000000000000 00000000-0f00-0000-0000-000000000000 00000000-0000-0001-0000-000000000000 00000000-0000-0002-0000-000000000000 00000000-0000-0003-0000-000000000000 00000000-0000-0004-0000-000000000000 00000000-0000-0005-0000-000000000000 00000000-0000-0006-0000-000000000000 00000000-0000-0007-0000-000000000000 00000000-0000-0008-0000-000000000000 00000000-0000-0009-0000-000000000000 00000000-0000-000a-0000-000000000000 00000000-0000-000b-0000-000000000000 00000000-0000-000c-0000-000000000000 00000000-0000-000d-0000-000000000000 00000000-0000-000e-0000-000000000000 00000000-0000-000f-0000-000000000000 00000000-0000-0100-0000-000000000000 00000000-0000-0200-0000-000000000000 00000000-0000-0300-0000-000000000000 00000000-0000-0400-0000-000000000000 00000000-0000-0500-0000-000000000000 00000000-0000-0600-0000-000000000000 00000000-0000-0700-0000-000000000000 00000000-0000-0800-0000-000000000000 00000000-0000-0900-0000-000000000000 00000000-0000-0a00-0000-000000000000 00000000-0000-0b00-0000-000000000000 00000000-0000-0c00-0000-000000000000 00000000-0000-0d00-0000-000000000000 00000000-0000-0e00-0000-000000000000 00000000-0000-0f00-0000-000000000000 00000000-0000-0000-0001-000000000000 00000000-0000-0000-0002-000000000000 00000000-0000-0000-0003-000000000000 00000000-0000-0000-0004-000000000000 00000000-0000-0000-0005-000000000000 00000000-0000-0000-0006-000000000000 00000000-0000-0000-0007-000000000000 00000000-0000-0000-0008-000000000000 00000000-0000-0000-0009-000000000000 00000000-0000-0000-000a-000000000000 00000000-0000-0000-000b-000000000000 00000000-0000-0000-000c-000000000000 00000000-0000-0000-000d-000000000000 00000000-0000-0000-000e-000000000000 00000000-0000-0000-000f-000000000000 00000000-0000-0000-0100-000000000000 00000000-0000-0000-0200-000000000000 00000000-0000-0000-0300-000000000000 00000000-0000-0000-0400-000000000000 00000000-0000-0000-0500-000000000000 00000000-0000-0000-0600-000000000000 00000000-0000-0000-0700-000000000000 00000000-0000-0000-0800-000000000000 00000000-0000-0000-0900-000000000000 00000000-0000-0000-0a00-000000000000 00000000-0000-0000-0b00-000000000000 00000000-0000-0000-0c00-000000000000 00000000-0000-0000-0d00-000000000000 00000000-0000-0000-0e00-000000000000 00000000-0000-0000-0f00-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000005 00000000-0000-0000-0000-000000000006 00000000-0000-0000-0000-000000000007 00000000-0000-0000-0000-000000000008 00000000-0000-0000-0000-000000000009 00000000-0000-0000-0000-00000000000a 00000000-0000-0000-0000-00000000000b 00000000-0000-0000-0000-00000000000c 00000000-0000-0000-0000-00000000000d 00000000-0000-0000-0000-00000000000e 00000000-0000-0000-0000-00000000000f 00000000-0000-0000-0000-000000000100 00000000-0000-0000-0000-000000000200 00000000-0000-0000-0000-000000000300 00000000-0000-0000-0000-000000000400 00000000-0000-0000-0000-000000000500 00000000-0000-0000-0000-000000000600 00000000-0000-0000-0000-000000000700 00000000-0000-0000-0000-000000000800 00000000-0000-0000-0000-000000000900 00000000-0000-0000-0000-000000000a00 00000000-0000-0000-0000-000000000b00 00000000-0000-0000-0000-000000000c00 00000000-0000-0000-0000-000000000d00 00000000-0000-0000-0000-000000000e00 00000000-0000-0000-0000-000000000f00 00000000-0000-0000-0000-000000010000 00000000-0000-0000-0000-000000020000 00000000-0000-0000-0000-000000030000 00000000-0000-0000-0000-000000040000 00000000-0000-0000-0000-000000050000 00000000-0000-0000-0000-000000060000 00000000-0000-0000-0000-000000070000 00000000-0000-0000-0000-000000080000 00000000-0000-0000-0000-000000090000 00000000-0000-0000-0000-0000000a0000 00000000-0000-0000-0000-0000000b0000 00000000-0000-0000-0000-0000000c0000 00000000-0000-0000-0000-0000000d0000 00000000-0000-0000-0000-0000000e0000 00000000-0000-0000-0000-0000000f0000 00000000-0000-0000-0000-000001000000 00000000-0000-0000-0000-000002000000 00000000-0000-0000-0000-000003000000 00000000-0000-0000-0000-000004000000 00000000-0000-0000-0000-000005000000 00000000-0000-0000-0000-000006000000 00000000-0000-0000-0000-000007000000 00000000-0000-0000-0000-000008000000 00000000-0000-0000-0000-000009000000 00000000-0000-0000-0000-00000a000000 00000000-0000-0000-0000-00000b000000 00000000-0000-0000-0000-00000c000000 00000000-0000-0000-0000-00000d000000 00000000-0000-0000-0000-00000e000000 00000000-0000-0000-0000-00000f000000 00000000-0000-0000-0000-000100000000 00000000-0000-0000-0000-000200000000 00000000-0000-0000-0000-000300000000 00000000-0000-0000-0000-000400000000 00000000-0000-0000-0000-000500000000 00000000-0000-0000-0000-000600000000 00000000-0000-0000-0000-000700000000 00000000-0000-0000-0000-000800000000 00000000-0000-0000-0000-000900000000 00000000-0000-0000-0000-000a00000000 00000000-0000-0000-0000-000b00000000 00000000-0000-0000-0000-000c00000000 00000000-0000-0000-0000-000d00000000 00000000-0000-0000-0000-000e00000000 00000000-0000-0000-0000-000f00000000 00000000-0000-0000-0000-010000000000 00000000-0000-0000-0000-020000000000 00000000-0000-0000-0000-030000000000 00000000-0000-0000-0000-040000000000 00000000-0000-0000-0000-050000000000 00000000-0000-0000-0000-060000000000 00000000-0000-0000-0000-070000000000 00000000-0000-0000-0000-080000000000 00000000-0000-0000-0000-090000000000 00000000-0000-0000-0000-0a0000000000 00000000-0000-0000-0000-0b0000000000 00000000-0000-0000-0000-0c0000000000 00000000-0000-0000-0000-0d0000000000 00000000-0000-0000-0000-0e0000000000 00000000-0000-0000-0000-0f0000000000 SELECT COALESCE(NULL, a) FROM t1 ORDER BY a; COALESCE(NULL, a) 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000001-0000-0000-0000-000000000000 00000002-0000-0000-0000-000000000000 00000003-0000-0000-0000-000000000000 00000004-0000-0000-0000-000000000000 00000005-0000-0000-0000-000000000000 00000006-0000-0000-0000-000000000000 00000007-0000-0000-0000-000000000000 00000008-0000-0000-0000-000000000000 00000009-0000-0000-0000-000000000000 0000000a-0000-0000-0000-000000000000 0000000b-0000-0000-0000-000000000000 0000000c-0000-0000-0000-000000000000 0000000d-0000-0000-0000-000000000000 0000000e-0000-0000-0000-000000000000 0000000f-0000-0000-0000-000000000000 00000100-0000-0000-0000-000000000000 00000200-0000-0000-0000-000000000000 00000300-0000-0000-0000-000000000000 00000400-0000-0000-0000-000000000000 00000500-0000-0000-0000-000000000000 00000600-0000-0000-0000-000000000000 00000700-0000-0000-0000-000000000000 00000800-0000-0000-0000-000000000000 00000900-0000-0000-0000-000000000000 00000a00-0000-0000-0000-000000000000 00000b00-0000-0000-0000-000000000000 00000c00-0000-0000-0000-000000000000 00000d00-0000-0000-0000-000000000000 00000e00-0000-0000-0000-000000000000 00000f00-0000-0000-0000-000000000000 00010000-0000-0000-0000-000000000000 00020000-0000-0000-0000-000000000000 00030000-0000-0000-0000-000000000000 00040000-0000-0000-0000-000000000000 00050000-0000-0000-0000-000000000000 00060000-0000-0000-0000-000000000000 00070000-0000-0000-0000-000000000000 00080000-0000-0000-0000-000000000000 00090000-0000-0000-0000-000000000000 000a0000-0000-0000-0000-000000000000 000b0000-0000-0000-0000-000000000000 000c0000-0000-0000-0000-000000000000 000d0000-0000-0000-0000-000000000000 000e0000-0000-0000-0000-000000000000 000f0000-0000-0000-0000-000000000000 01000000-0000-0000-0000-000000000000 02000000-0000-0000-0000-000000000000 03000000-0000-0000-0000-000000000000 04000000-0000-0000-0000-000000000000 05000000-0000-0000-0000-000000000000 06000000-0000-0000-0000-000000000000 07000000-0000-0000-0000-000000000000 08000000-0000-0000-0000-000000000000 09000000-0000-0000-0000-000000000000 0a000000-0000-0000-0000-000000000000 0b000000-0000-0000-0000-000000000000 0c000000-0000-0000-0000-000000000000 0d000000-0000-0000-0000-000000000000 0e000000-0000-0000-0000-000000000000 0f000000-0000-0000-0000-000000000000 00000000-0001-0000-0000-000000000000 00000000-0002-0000-0000-000000000000 00000000-0003-0000-0000-000000000000 00000000-0004-0000-0000-000000000000 00000000-0005-0000-0000-000000000000 00000000-0006-0000-0000-000000000000 00000000-0007-0000-0000-000000000000 00000000-0008-0000-0000-000000000000 00000000-0009-0000-0000-000000000000 00000000-000a-0000-0000-000000000000 00000000-000b-0000-0000-000000000000 00000000-000c-0000-0000-000000000000 00000000-000d-0000-0000-000000000000 00000000-000e-0000-0000-000000000000 00000000-000f-0000-0000-000000000000 00000000-0100-0000-0000-000000000000 00000000-0200-0000-0000-000000000000 00000000-0300-0000-0000-000000000000 00000000-0400-0000-0000-000000000000 00000000-0500-0000-0000-000000000000 00000000-0600-0000-0000-000000000000 00000000-0700-0000-0000-000000000000 00000000-0800-0000-0000-000000000000 00000000-0900-0000-0000-000000000000 00000000-0a00-0000-0000-000000000000 00000000-0b00-0000-0000-000000000000 00000000-0c00-0000-0000-000000000000 00000000-0d00-0000-0000-000000000000 00000000-0e00-0000-0000-000000000000 00000000-0f00-0000-0000-000000000000 00000000-0000-0001-0000-000000000000 00000000-0000-0002-0000-000000000000 00000000-0000-0003-0000-000000000000 00000000-0000-0004-0000-000000000000 00000000-0000-0005-0000-000000000000 00000000-0000-0006-0000-000000000000 00000000-0000-0007-0000-000000000000 00000000-0000-0008-0000-000000000000 00000000-0000-0009-0000-000000000000 00000000-0000-000a-0000-000000000000 00000000-0000-000b-0000-000000000000 00000000-0000-000c-0000-000000000000 00000000-0000-000d-0000-000000000000 00000000-0000-000e-0000-000000000000 00000000-0000-000f-0000-000000000000 00000000-0000-0100-0000-000000000000 00000000-0000-0200-0000-000000000000 00000000-0000-0300-0000-000000000000 00000000-0000-0400-0000-000000000000 00000000-0000-0500-0000-000000000000 00000000-0000-0600-0000-000000000000 00000000-0000-0700-0000-000000000000 00000000-0000-0800-0000-000000000000 00000000-0000-0900-0000-000000000000 00000000-0000-0a00-0000-000000000000 00000000-0000-0b00-0000-000000000000 00000000-0000-0c00-0000-000000000000 00000000-0000-0d00-0000-000000000000 00000000-0000-0e00-0000-000000000000 00000000-0000-0f00-0000-000000000000 00000000-0000-0000-0001-000000000000 00000000-0000-0000-0002-000000000000 00000000-0000-0000-0003-000000000000 00000000-0000-0000-0004-000000000000 00000000-0000-0000-0005-000000000000 00000000-0000-0000-0006-000000000000 00000000-0000-0000-0007-000000000000 00000000-0000-0000-0008-000000000000 00000000-0000-0000-0009-000000000000 00000000-0000-0000-000a-000000000000 00000000-0000-0000-000b-000000000000 00000000-0000-0000-000c-000000000000 00000000-0000-0000-000d-000000000000 00000000-0000-0000-000e-000000000000 00000000-0000-0000-000f-000000000000 00000000-0000-0000-0100-000000000000 00000000-0000-0000-0200-000000000000 00000000-0000-0000-0300-000000000000 00000000-0000-0000-0400-000000000000 00000000-0000-0000-0500-000000000000 00000000-0000-0000-0600-000000000000 00000000-0000-0000-0700-000000000000 00000000-0000-0000-0800-000000000000 00000000-0000-0000-0900-000000000000 00000000-0000-0000-0a00-000000000000 00000000-0000-0000-0b00-000000000000 00000000-0000-0000-0c00-000000000000 00000000-0000-0000-0d00-000000000000 00000000-0000-0000-0e00-000000000000 00000000-0000-0000-0f00-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000005 00000000-0000-0000-0000-000000000006 00000000-0000-0000-0000-000000000007 00000000-0000-0000-0000-000000000008 00000000-0000-0000-0000-000000000009 00000000-0000-0000-0000-00000000000a 00000000-0000-0000-0000-00000000000b 00000000-0000-0000-0000-00000000000c 00000000-0000-0000-0000-00000000000d 00000000-0000-0000-0000-00000000000e 00000000-0000-0000-0000-00000000000f 00000000-0000-0000-0000-000000000100 00000000-0000-0000-0000-000000000200 00000000-0000-0000-0000-000000000300 00000000-0000-0000-0000-000000000400 00000000-0000-0000-0000-000000000500 00000000-0000-0000-0000-000000000600 00000000-0000-0000-0000-000000000700 00000000-0000-0000-0000-000000000800 00000000-0000-0000-0000-000000000900 00000000-0000-0000-0000-000000000a00 00000000-0000-0000-0000-000000000b00 00000000-0000-0000-0000-000000000c00 00000000-0000-0000-0000-000000000d00 00000000-0000-0000-0000-000000000e00 00000000-0000-0000-0000-000000000f00 00000000-0000-0000-0000-000000010000 00000000-0000-0000-0000-000000020000 00000000-0000-0000-0000-000000030000 00000000-0000-0000-0000-000000040000 00000000-0000-0000-0000-000000050000 00000000-0000-0000-0000-000000060000 00000000-0000-0000-0000-000000070000 00000000-0000-0000-0000-000000080000 00000000-0000-0000-0000-000000090000 00000000-0000-0000-0000-0000000a0000 00000000-0000-0000-0000-0000000b0000 00000000-0000-0000-0000-0000000c0000 00000000-0000-0000-0000-0000000d0000 00000000-0000-0000-0000-0000000e0000 00000000-0000-0000-0000-0000000f0000 00000000-0000-0000-0000-000001000000 00000000-0000-0000-0000-000002000000 00000000-0000-0000-0000-000003000000 00000000-0000-0000-0000-000004000000 00000000-0000-0000-0000-000005000000 00000000-0000-0000-0000-000006000000 00000000-0000-0000-0000-000007000000 00000000-0000-0000-0000-000008000000 00000000-0000-0000-0000-000009000000 00000000-0000-0000-0000-00000a000000 00000000-0000-0000-0000-00000b000000 00000000-0000-0000-0000-00000c000000 00000000-0000-0000-0000-00000d000000 00000000-0000-0000-0000-00000e000000 00000000-0000-0000-0000-00000f000000 00000000-0000-0000-0000-000100000000 00000000-0000-0000-0000-000200000000 00000000-0000-0000-0000-000300000000 00000000-0000-0000-0000-000400000000 00000000-0000-0000-0000-000500000000 00000000-0000-0000-0000-000600000000 00000000-0000-0000-0000-000700000000 00000000-0000-0000-0000-000800000000 00000000-0000-0000-0000-000900000000 00000000-0000-0000-0000-000a00000000 00000000-0000-0000-0000-000b00000000 00000000-0000-0000-0000-000c00000000 00000000-0000-0000-0000-000d00000000 00000000-0000-0000-0000-000e00000000 00000000-0000-0000-0000-000f00000000 00000000-0000-0000-0000-010000000000 00000000-0000-0000-0000-020000000000 00000000-0000-0000-0000-030000000000 00000000-0000-0000-0000-040000000000 00000000-0000-0000-0000-050000000000 00000000-0000-0000-0000-060000000000 00000000-0000-0000-0000-070000000000 00000000-0000-0000-0000-080000000000 00000000-0000-0000-0000-090000000000 00000000-0000-0000-0000-0a0000000000 00000000-0000-0000-0000-0b0000000000 00000000-0000-0000-0000-0c0000000000 00000000-0000-0000-0000-0d0000000000 00000000-0000-0000-0000-0e0000000000 00000000-0000-0000-0000-0f0000000000 # # Lexicographical ORDER BY # SELECT * FROM t1 ORDER BY CAST(a AS BINARY(16)); a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000005 00000000-0000-0000-0000-000000000006 00000000-0000-0000-0000-000000000007 00000000-0000-0000-0000-000000000008 00000000-0000-0000-0000-000000000009 00000000-0000-0000-0000-00000000000a 00000000-0000-0000-0000-00000000000b 00000000-0000-0000-0000-00000000000c 00000000-0000-0000-0000-00000000000d 00000000-0000-0000-0000-00000000000e 00000000-0000-0000-0000-00000000000f 00000000-0000-0000-0000-000000000100 00000000-0000-0000-0000-000000000200 00000000-0000-0000-0000-000000000300 00000000-0000-0000-0000-000000000400 00000000-0000-0000-0000-000000000500 00000000-0000-0000-0000-000000000600 00000000-0000-0000-0000-000000000700 00000000-0000-0000-0000-000000000800 00000000-0000-0000-0000-000000000900 00000000-0000-0000-0000-000000000a00 00000000-0000-0000-0000-000000000b00 00000000-0000-0000-0000-000000000c00 00000000-0000-0000-0000-000000000d00 00000000-0000-0000-0000-000000000e00 00000000-0000-0000-0000-000000000f00 00000000-0000-0000-0000-000000010000 00000000-0000-0000-0000-000000020000 00000000-0000-0000-0000-000000030000 00000000-0000-0000-0000-000000040000 00000000-0000-0000-0000-000000050000 00000000-0000-0000-0000-000000060000 00000000-0000-0000-0000-000000070000 00000000-0000-0000-0000-000000080000 00000000-0000-0000-0000-000000090000 00000000-0000-0000-0000-0000000a0000 00000000-0000-0000-0000-0000000b0000 00000000-0000-0000-0000-0000000c0000 00000000-0000-0000-0000-0000000d0000 00000000-0000-0000-0000-0000000e0000 00000000-0000-0000-0000-0000000f0000 00000000-0000-0000-0000-000001000000 00000000-0000-0000-0000-000002000000 00000000-0000-0000-0000-000003000000 00000000-0000-0000-0000-000004000000 00000000-0000-0000-0000-000005000000 00000000-0000-0000-0000-000006000000 00000000-0000-0000-0000-000007000000 00000000-0000-0000-0000-000008000000 00000000-0000-0000-0000-000009000000 00000000-0000-0000-0000-00000a000000 00000000-0000-0000-0000-00000b000000 00000000-0000-0000-0000-00000c000000 00000000-0000-0000-0000-00000d000000 00000000-0000-0000-0000-00000e000000 00000000-0000-0000-0000-00000f000000 00000000-0000-0000-0000-000100000000 00000000-0000-0000-0000-000200000000 00000000-0000-0000-0000-000300000000 00000000-0000-0000-0000-000400000000 00000000-0000-0000-0000-000500000000 00000000-0000-0000-0000-000600000000 00000000-0000-0000-0000-000700000000 00000000-0000-0000-0000-000800000000 00000000-0000-0000-0000-000900000000 00000000-0000-0000-0000-000a00000000 00000000-0000-0000-0000-000b00000000 00000000-0000-0000-0000-000c00000000 00000000-0000-0000-0000-000d00000000 00000000-0000-0000-0000-000e00000000 00000000-0000-0000-0000-000f00000000 00000000-0000-0000-0000-010000000000 00000000-0000-0000-0000-020000000000 00000000-0000-0000-0000-030000000000 00000000-0000-0000-0000-040000000000 00000000-0000-0000-0000-050000000000 00000000-0000-0000-0000-060000000000 00000000-0000-0000-0000-070000000000 00000000-0000-0000-0000-080000000000 00000000-0000-0000-0000-090000000000 00000000-0000-0000-0000-0a0000000000 00000000-0000-0000-0000-0b0000000000 00000000-0000-0000-0000-0c0000000000 00000000-0000-0000-0000-0d0000000000 00000000-0000-0000-0000-0e0000000000 00000000-0000-0000-0000-0f0000000000 00000000-0000-0000-0001-000000000000 00000000-0000-0000-0002-000000000000 00000000-0000-0000-0003-000000000000 00000000-0000-0000-0004-000000000000 00000000-0000-0000-0005-000000000000 00000000-0000-0000-0006-000000000000 00000000-0000-0000-0007-000000000000 00000000-0000-0000-0008-000000000000 00000000-0000-0000-0009-000000000000 00000000-0000-0000-000a-000000000000 00000000-0000-0000-000b-000000000000 00000000-0000-0000-000c-000000000000 00000000-0000-0000-000d-000000000000 00000000-0000-0000-000e-000000000000 00000000-0000-0000-000f-000000000000 00000000-0000-0000-0100-000000000000 00000000-0000-0000-0200-000000000000 00000000-0000-0000-0300-000000000000 00000000-0000-0000-0400-000000000000 00000000-0000-0000-0500-000000000000 00000000-0000-0000-0600-000000000000 00000000-0000-0000-0700-000000000000 00000000-0000-0000-0800-000000000000 00000000-0000-0000-0900-000000000000 00000000-0000-0000-0a00-000000000000 00000000-0000-0000-0b00-000000000000 00000000-0000-0000-0c00-000000000000 00000000-0000-0000-0d00-000000000000 00000000-0000-0000-0e00-000000000000 00000000-0000-0000-0f00-000000000000 00000000-0000-0001-0000-000000000000 00000000-0000-0002-0000-000000000000 00000000-0000-0003-0000-000000000000 00000000-0000-0004-0000-000000000000 00000000-0000-0005-0000-000000000000 00000000-0000-0006-0000-000000000000 00000000-0000-0007-0000-000000000000 00000000-0000-0008-0000-000000000000 00000000-0000-0009-0000-000000000000 00000000-0000-000a-0000-000000000000 00000000-0000-000b-0000-000000000000 00000000-0000-000c-0000-000000000000 00000000-0000-000d-0000-000000000000 00000000-0000-000e-0000-000000000000 00000000-0000-000f-0000-000000000000 00000000-0000-0100-0000-000000000000 00000000-0000-0200-0000-000000000000 00000000-0000-0300-0000-000000000000 00000000-0000-0400-0000-000000000000 00000000-0000-0500-0000-000000000000 00000000-0000-0600-0000-000000000000 00000000-0000-0700-0000-000000000000 00000000-0000-0800-0000-000000000000 00000000-0000-0900-0000-000000000000 00000000-0000-0a00-0000-000000000000 00000000-0000-0b00-0000-000000000000 00000000-0000-0c00-0000-000000000000 00000000-0000-0d00-0000-000000000000 00000000-0000-0e00-0000-000000000000 00000000-0000-0f00-0000-000000000000 00000000-0001-0000-0000-000000000000 00000000-0002-0000-0000-000000000000 00000000-0003-0000-0000-000000000000 00000000-0004-0000-0000-000000000000 00000000-0005-0000-0000-000000000000 00000000-0006-0000-0000-000000000000 00000000-0007-0000-0000-000000000000 00000000-0008-0000-0000-000000000000 00000000-0009-0000-0000-000000000000 00000000-000a-0000-0000-000000000000 00000000-000b-0000-0000-000000000000 00000000-000c-0000-0000-000000000000 00000000-000d-0000-0000-000000000000 00000000-000e-0000-0000-000000000000 00000000-000f-0000-0000-000000000000 00000000-0100-0000-0000-000000000000 00000000-0200-0000-0000-000000000000 00000000-0300-0000-0000-000000000000 00000000-0400-0000-0000-000000000000 00000000-0500-0000-0000-000000000000 00000000-0600-0000-0000-000000000000 00000000-0700-0000-0000-000000000000 00000000-0800-0000-0000-000000000000 00000000-0900-0000-0000-000000000000 00000000-0a00-0000-0000-000000000000 00000000-0b00-0000-0000-000000000000 00000000-0c00-0000-0000-000000000000 00000000-0d00-0000-0000-000000000000 00000000-0e00-0000-0000-000000000000 00000000-0f00-0000-0000-000000000000 00000001-0000-0000-0000-000000000000 00000002-0000-0000-0000-000000000000 00000003-0000-0000-0000-000000000000 00000004-0000-0000-0000-000000000000 00000005-0000-0000-0000-000000000000 00000006-0000-0000-0000-000000000000 00000007-0000-0000-0000-000000000000 00000008-0000-0000-0000-000000000000 00000009-0000-0000-0000-000000000000 0000000a-0000-0000-0000-000000000000 0000000b-0000-0000-0000-000000000000 0000000c-0000-0000-0000-000000000000 0000000d-0000-0000-0000-000000000000 0000000e-0000-0000-0000-000000000000 0000000f-0000-0000-0000-000000000000 00000100-0000-0000-0000-000000000000 00000200-0000-0000-0000-000000000000 00000300-0000-0000-0000-000000000000 00000400-0000-0000-0000-000000000000 00000500-0000-0000-0000-000000000000 00000600-0000-0000-0000-000000000000 00000700-0000-0000-0000-000000000000 00000800-0000-0000-0000-000000000000 00000900-0000-0000-0000-000000000000 00000a00-0000-0000-0000-000000000000 00000b00-0000-0000-0000-000000000000 00000c00-0000-0000-0000-000000000000 00000d00-0000-0000-0000-000000000000 00000e00-0000-0000-0000-000000000000 00000f00-0000-0000-0000-000000000000 00010000-0000-0000-0000-000000000000 00020000-0000-0000-0000-000000000000 00030000-0000-0000-0000-000000000000 00040000-0000-0000-0000-000000000000 00050000-0000-0000-0000-000000000000 00060000-0000-0000-0000-000000000000 00070000-0000-0000-0000-000000000000 00080000-0000-0000-0000-000000000000 00090000-0000-0000-0000-000000000000 000a0000-0000-0000-0000-000000000000 000b0000-0000-0000-0000-000000000000 000c0000-0000-0000-0000-000000000000 000d0000-0000-0000-0000-000000000000 000e0000-0000-0000-0000-000000000000 000f0000-0000-0000-0000-000000000000 01000000-0000-0000-0000-000000000000 02000000-0000-0000-0000-000000000000 03000000-0000-0000-0000-000000000000 04000000-0000-0000-0000-000000000000 05000000-0000-0000-0000-000000000000 06000000-0000-0000-0000-000000000000 07000000-0000-0000-0000-000000000000 08000000-0000-0000-0000-000000000000 09000000-0000-0000-0000-000000000000 0a000000-0000-0000-0000-000000000000 0b000000-0000-0000-0000-000000000000 0c000000-0000-0000-0000-000000000000 0d000000-0000-0000-0000-000000000000 0e000000-0000-0000-0000-000000000000 0f000000-0000-0000-0000-000000000000 SELECT * FROM t1 ORDER BY CAST(COALESCE(NULL,a) AS BINARY(16)); a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000005 00000000-0000-0000-0000-000000000006 00000000-0000-0000-0000-000000000007 00000000-0000-0000-0000-000000000008 00000000-0000-0000-0000-000000000009 00000000-0000-0000-0000-00000000000a 00000000-0000-0000-0000-00000000000b 00000000-0000-0000-0000-00000000000c 00000000-0000-0000-0000-00000000000d 00000000-0000-0000-0000-00000000000e 00000000-0000-0000-0000-00000000000f 00000000-0000-0000-0000-000000000100 00000000-0000-0000-0000-000000000200 00000000-0000-0000-0000-000000000300 00000000-0000-0000-0000-000000000400 00000000-0000-0000-0000-000000000500 00000000-0000-0000-0000-000000000600 00000000-0000-0000-0000-000000000700 00000000-0000-0000-0000-000000000800 00000000-0000-0000-0000-000000000900 00000000-0000-0000-0000-000000000a00 00000000-0000-0000-0000-000000000b00 00000000-0000-0000-0000-000000000c00 00000000-0000-0000-0000-000000000d00 00000000-0000-0000-0000-000000000e00 00000000-0000-0000-0000-000000000f00 00000000-0000-0000-0000-000000010000 00000000-0000-0000-0000-000000020000 00000000-0000-0000-0000-000000030000 00000000-0000-0000-0000-000000040000 00000000-0000-0000-0000-000000050000 00000000-0000-0000-0000-000000060000 00000000-0000-0000-0000-000000070000 00000000-0000-0000-0000-000000080000 00000000-0000-0000-0000-000000090000 00000000-0000-0000-0000-0000000a0000 00000000-0000-0000-0000-0000000b0000 00000000-0000-0000-0000-0000000c0000 00000000-0000-0000-0000-0000000d0000 00000000-0000-0000-0000-0000000e0000 00000000-0000-0000-0000-0000000f0000 00000000-0000-0000-0000-000001000000 00000000-0000-0000-0000-000002000000 00000000-0000-0000-0000-000003000000 00000000-0000-0000-0000-000004000000 00000000-0000-0000-0000-000005000000 00000000-0000-0000-0000-000006000000 00000000-0000-0000-0000-000007000000 00000000-0000-0000-0000-000008000000 00000000-0000-0000-0000-000009000000 00000000-0000-0000-0000-00000a000000 00000000-0000-0000-0000-00000b000000 00000000-0000-0000-0000-00000c000000 00000000-0000-0000-0000-00000d000000 00000000-0000-0000-0000-00000e000000 00000000-0000-0000-0000-00000f000000 00000000-0000-0000-0000-000100000000 00000000-0000-0000-0000-000200000000 00000000-0000-0000-0000-000300000000 00000000-0000-0000-0000-000400000000 00000000-0000-0000-0000-000500000000 00000000-0000-0000-0000-000600000000 00000000-0000-0000-0000-000700000000 00000000-0000-0000-0000-000800000000 00000000-0000-0000-0000-000900000000 00000000-0000-0000-0000-000a00000000 00000000-0000-0000-0000-000b00000000 00000000-0000-0000-0000-000c00000000 00000000-0000-0000-0000-000d00000000 00000000-0000-0000-0000-000e00000000 00000000-0000-0000-0000-000f00000000 00000000-0000-0000-0000-010000000000 00000000-0000-0000-0000-020000000000 00000000-0000-0000-0000-030000000000 00000000-0000-0000-0000-040000000000 00000000-0000-0000-0000-050000000000 00000000-0000-0000-0000-060000000000 00000000-0000-0000-0000-070000000000 00000000-0000-0000-0000-080000000000 00000000-0000-0000-0000-090000000000 00000000-0000-0000-0000-0a0000000000 00000000-0000-0000-0000-0b0000000000 00000000-0000-0000-0000-0c0000000000 00000000-0000-0000-0000-0d0000000000 00000000-0000-0000-0000-0e0000000000 00000000-0000-0000-0000-0f0000000000 00000000-0000-0000-0001-000000000000 00000000-0000-0000-0002-000000000000 00000000-0000-0000-0003-000000000000 00000000-0000-0000-0004-000000000000 00000000-0000-0000-0005-000000000000 00000000-0000-0000-0006-000000000000 00000000-0000-0000-0007-000000000000 00000000-0000-0000-0008-000000000000 00000000-0000-0000-0009-000000000000 00000000-0000-0000-000a-000000000000 00000000-0000-0000-000b-000000000000 00000000-0000-0000-000c-000000000000 00000000-0000-0000-000d-000000000000 00000000-0000-0000-000e-000000000000 00000000-0000-0000-000f-000000000000 00000000-0000-0000-0100-000000000000 00000000-0000-0000-0200-000000000000 00000000-0000-0000-0300-000000000000 00000000-0000-0000-0400-000000000000 00000000-0000-0000-0500-000000000000 00000000-0000-0000-0600-000000000000 00000000-0000-0000-0700-000000000000 00000000-0000-0000-0800-000000000000 00000000-0000-0000-0900-000000000000 00000000-0000-0000-0a00-000000000000 00000000-0000-0000-0b00-000000000000 00000000-0000-0000-0c00-000000000000 00000000-0000-0000-0d00-000000000000 00000000-0000-0000-0e00-000000000000 00000000-0000-0000-0f00-000000000000 00000000-0000-0001-0000-000000000000 00000000-0000-0002-0000-000000000000 00000000-0000-0003-0000-000000000000 00000000-0000-0004-0000-000000000000 00000000-0000-0005-0000-000000000000 00000000-0000-0006-0000-000000000000 00000000-0000-0007-0000-000000000000 00000000-0000-0008-0000-000000000000 00000000-0000-0009-0000-000000000000 00000000-0000-000a-0000-000000000000 00000000-0000-000b-0000-000000000000 00000000-0000-000c-0000-000000000000 00000000-0000-000d-0000-000000000000 00000000-0000-000e-0000-000000000000 00000000-0000-000f-0000-000000000000 00000000-0000-0100-0000-000000000000 00000000-0000-0200-0000-000000000000 00000000-0000-0300-0000-000000000000 00000000-0000-0400-0000-000000000000 00000000-0000-0500-0000-000000000000 00000000-0000-0600-0000-000000000000 00000000-0000-0700-0000-000000000000 00000000-0000-0800-0000-000000000000 00000000-0000-0900-0000-000000000000 00000000-0000-0a00-0000-000000000000 00000000-0000-0b00-0000-000000000000 00000000-0000-0c00-0000-000000000000 00000000-0000-0d00-0000-000000000000 00000000-0000-0e00-0000-000000000000 00000000-0000-0f00-0000-000000000000 00000000-0001-0000-0000-000000000000 00000000-0002-0000-0000-000000000000 00000000-0003-0000-0000-000000000000 00000000-0004-0000-0000-000000000000 00000000-0005-0000-0000-000000000000 00000000-0006-0000-0000-000000000000 00000000-0007-0000-0000-000000000000 00000000-0008-0000-0000-000000000000 00000000-0009-0000-0000-000000000000 00000000-000a-0000-0000-000000000000 00000000-000b-0000-0000-000000000000 00000000-000c-0000-0000-000000000000 00000000-000d-0000-0000-000000000000 00000000-000e-0000-0000-000000000000 00000000-000f-0000-0000-000000000000 00000000-0100-0000-0000-000000000000 00000000-0200-0000-0000-000000000000 00000000-0300-0000-0000-000000000000 00000000-0400-0000-0000-000000000000 00000000-0500-0000-0000-000000000000 00000000-0600-0000-0000-000000000000 00000000-0700-0000-0000-000000000000 00000000-0800-0000-0000-000000000000 00000000-0900-0000-0000-000000000000 00000000-0a00-0000-0000-000000000000 00000000-0b00-0000-0000-000000000000 00000000-0c00-0000-0000-000000000000 00000000-0d00-0000-0000-000000000000 00000000-0e00-0000-0000-000000000000 00000000-0f00-0000-0000-000000000000 00000001-0000-0000-0000-000000000000 00000002-0000-0000-0000-000000000000 00000003-0000-0000-0000-000000000000 00000004-0000-0000-0000-000000000000 00000005-0000-0000-0000-000000000000 00000006-0000-0000-0000-000000000000 00000007-0000-0000-0000-000000000000 00000008-0000-0000-0000-000000000000 00000009-0000-0000-0000-000000000000 0000000a-0000-0000-0000-000000000000 0000000b-0000-0000-0000-000000000000 0000000c-0000-0000-0000-000000000000 0000000d-0000-0000-0000-000000000000 0000000e-0000-0000-0000-000000000000 0000000f-0000-0000-0000-000000000000 00000100-0000-0000-0000-000000000000 00000200-0000-0000-0000-000000000000 00000300-0000-0000-0000-000000000000 00000400-0000-0000-0000-000000000000 00000500-0000-0000-0000-000000000000 00000600-0000-0000-0000-000000000000 00000700-0000-0000-0000-000000000000 00000800-0000-0000-0000-000000000000 00000900-0000-0000-0000-000000000000 00000a00-0000-0000-0000-000000000000 00000b00-0000-0000-0000-000000000000 00000c00-0000-0000-0000-000000000000 00000d00-0000-0000-0000-000000000000 00000e00-0000-0000-0000-000000000000 00000f00-0000-0000-0000-000000000000 00010000-0000-0000-0000-000000000000 00020000-0000-0000-0000-000000000000 00030000-0000-0000-0000-000000000000 00040000-0000-0000-0000-000000000000 00050000-0000-0000-0000-000000000000 00060000-0000-0000-0000-000000000000 00070000-0000-0000-0000-000000000000 00080000-0000-0000-0000-000000000000 00090000-0000-0000-0000-000000000000 000a0000-0000-0000-0000-000000000000 000b0000-0000-0000-0000-000000000000 000c0000-0000-0000-0000-000000000000 000d0000-0000-0000-0000-000000000000 000e0000-0000-0000-0000-000000000000 000f0000-0000-0000-0000-000000000000 01000000-0000-0000-0000-000000000000 02000000-0000-0000-0000-000000000000 03000000-0000-0000-0000-000000000000 04000000-0000-0000-0000-000000000000 05000000-0000-0000-0000-000000000000 06000000-0000-0000-0000-000000000000 07000000-0000-0000-0000-000000000000 08000000-0000-0000-0000-000000000000 09000000-0000-0000-0000-000000000000 0a000000-0000-0000-0000-000000000000 0b000000-0000-0000-0000-000000000000 0c000000-0000-0000-0000-000000000000 0d000000-0000-0000-0000-000000000000 0e000000-0000-0000-0000-000000000000 0f000000-0000-0000-0000-000000000000 DROP TABLE t1; # # cmp_item_uuid: IN for non-constants # 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); a b SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000001' IN (a, b); a b 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE '00-000000-0000-0000-0000-000000000001' IN (a, b); a b 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE '00-00-0000-0000-0000-0000-000000000001' IN (a, b); a b 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 DROP TABLE t1; # # cmp_item_uuid: DECODE_ORACLE # 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; a d NULL NULL 00000000-0000-0000-0000-000000000001 NULL 00000000-0000-0000-0000-000000000002 01 SELECT a, DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000001', '01') AS d0, DECODE_ORACLE(a, NULL, '', '00000000-0000-0000-0000-000000000001', '01') AS d1, DECODE_ORACLE(a, 'garbage', '', '00000000-0000-0000-0000-000000000001', '01') AS d2 FROM t1; a d0 d1 d2 NULL NULL 00000000-0000-0000-0000-000000000001 01 01 01 00000000-0000-0000-0000-000000000002 NULL NULL NULL Warnings: Warning 1292 Incorrect uuid value: 'garbage' Warning 1292 Incorrect uuid value: 'garbage' Warning 1292 Incorrect uuid value: 'garbage' DROP TABLE t1; # # CASE abbreviations # 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; Table Create Table t2 CREATE TABLE `t2` ( `COALESCE(c, c_char)` uuid DEFAULT NULL, `COALESCE(c, c_varchar)` uuid DEFAULT NULL, `COALESCE(c, c_tinytext)` uuid DEFAULT NULL, `COALESCE(c, c_text)` uuid DEFAULT NULL, `COALESCE(c, c_mediumtext)` uuid DEFAULT NULL, `COALESCE(c, c_longtext)` uuid DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; Table Create Table t2 CREATE TABLE `t2` ( `LEAST(c, c_char)` uuid DEFAULT NULL, `LEAST(c, c_varchar)` uuid DEFAULT NULL, `LEAST(c, c_tinytext)` uuid DEFAULT NULL, `LEAST(c, c_text)` uuid DEFAULT NULL, `LEAST(c, c_mediumtext)` uuid DEFAULT NULL, `LEAST(c, c_longtext)` uuid DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; COALESCE(a, '00000000-0000-0000-0000-000000000000') 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 SELECT a, LEAST(a,'00000000-0000-0000-0000-000000000000'), LEAST(a,'00000000-0000-0000-0000-00000000000f') FROM t1 ORDER BY a; a LEAST(a,'00000000-0000-0000-0000-000000000000') LEAST(a,'00000000-0000-0000-0000-00000000000f') NULL NULL NULL 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000002 SELECT a, GREATEST(a,'00000000-0000-0000-0000-000000000000'), GREATEST(a,'00000000-0000-0000-0000-00000000000f') FROM t1 ORDER BY a; a GREATEST(a,'00000000-0000-0000-0000-000000000000') GREATEST(a,'00000000-0000-0000-0000-00000000000f') NULL NULL NULL 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-00000000000f 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-00000000000f 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; Table Create Table t2 CREATE TABLE `t2` ( `COALESCE(a, '00000000-0000-0000-0000-000000000000')` uuid DEFAULT NULL, `LEAST(a,'00000000-0000-0000-0000-000000000000')` uuid DEFAULT NULL, `GREATEST(a,'00000000-0000-0000-0000-000000000000')` uuid DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a; COALESCE(a, 0x00000000000000000000000000000000) 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 SELECT a, LEAST(a, 0x00000000000000000000000000000000), LEAST(a, 0x0000000000000000000000000000000f) FROM t1 ORDER BY a; a LEAST(a, 0x00000000000000000000000000000000) LEAST(a, 0x0000000000000000000000000000000f) NULL NULL NULL 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000002 SELECT a, GREATEST(a, 0x00000000000000000000000000000000), GREATEST(a, 0x0000000000000000000000000000000f) FROM t1 ORDER BY a; a GREATEST(a, 0x00000000000000000000000000000000) GREATEST(a, 0x0000000000000000000000000000000f) NULL NULL NULL 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-00000000000f 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-00000000000f CREATE TABLE t2 AS SELECT COALESCE(a, 0x00000000000000000000000000000000), LEAST(a,0x00000000000000000000000000000000), GREATEST(a,0x00000000000000000000000000000000) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `COALESCE(a, 0x00000000000000000000000000000000)` uuid DEFAULT NULL, `LEAST(a,0x00000000000000000000000000000000)` uuid DEFAULT NULL, `GREATEST(a,0x00000000000000000000000000000000)` uuid DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; SELECT COALESCE(a, 10) FROM t1; ERROR HY000: Illegal parameter data types uuid and int for operation 'coalesce' SELECT LEAST(a, 10) FROM t1; ERROR HY000: Illegal parameter data types uuid and int for operation 'least' SELECT GREATEST(a, 10) FROM t1; ERROR HY000: Illegal parameter data types uuid and int for operation 'greatest' DROP TABLE t1; SELECT COALESCE('garbage', CAST('::1' AS UUID)); COALESCE('garbage', CAST('::1' AS UUID)) NULL Warnings: Warning 1292 Incorrect uuid value: 'garbage' Warning 1292 Incorrect uuid value: '::1' SELECT COALESCE(0x01, CAST('::1' AS UUID)); COALESCE(0x01, CAST('::1' AS UUID)) NULL Warnings: Warning 1292 Incorrect uuid value: '\x01' Warning 1292 Incorrect uuid value: '::1' # # Uniqueness # CREATE TABLE t1 (a UUID NOT NULL PRIMARY KEY); INSERT INTO t1 VALUES ('41000000-0000-0000-0000-000000000001'), ('61000000-0000-0000-0000-000000000001'); INSERT INTO t1 VALUES ('41000000-0000-0000-0000-000000000001'); ERROR 23000: Duplicate entry '41000000-0000-0000-0000-000000000001' for key 'PRIMARY' SELECT * FROM t1; a 41000000-0000-0000-0000-000000000001 61000000-0000-0000-0000-000000000001 DROP TABLE t1; # # Indexes # CREATE TABLE t1 (a UUID, KEY(a(1))); ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys # # Explicit CAST on INSERT # 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; a 10000000-0000-0000-0000-000000000001 20000000-0000-0000-0000-000000000001 10000000-0000-0000-0000-000000000002 20000000-0000-0000-0000-000000000002 10000000-0000-0000-0000-000000000003 20000000-0000-0000-0000-000000000003 DROP TABLE t1; # # Explicit CAST and implicit CAST on ALTER # 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; a CAST(a AS UUID) 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 ffff0000-0000-0000-0000-000000000002 garbage NULL Warnings: Warning 1292 Incorrect uuid value: 'garbage' SELECT a, CAST(a AS UUID) FROM t1 ORDER BY CAST(a AS UUID); a CAST(a AS UUID) garbage NULL 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 ffff0000-0000-0000-0000-000000000002 Warnings: Warning 1292 Incorrect uuid value: 'garbage' Warning 1292 Incorrect uuid value: 'garbage' ALTER TABLE t1 MODIFY a UUID; ERROR 22007: Incorrect uuid value: 'garbage' for column `test`.`t1`.`a` at row 1 SET sql_mode=''; ALTER TABLE t1 MODIFY a UUID; Warnings: Warning 1292 Incorrect uuid value: 'garbage' for column `test`.`t1`.`a` at row 1 SET sql_mode=DEFAULT; SELECT * FROM t1 ORDER BY a; a NULL 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 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; HEX(a) CAST(a AS UUID) 00000000000000000000000000000000 00000000-0000-0000-0000-000000000000 00000000000000000000000000000001 00000000-0000-0000-0000-000000000001 FFFF0000000000000000000000000001 ffff0000-0000-0000-0000-000000000001 FFFF0000000000000000000000000002 ffff0000-0000-0000-0000-000000000002 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1 ORDER BY a; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 DROP TABLE t1; # # INSERT..SELECT, same data types # 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; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 DROP TABLE t1,t2; # # Implicit CAST on INSERT..SELECT, text format # 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); INSERT INTO t2 SELECT a FROM t1; ERROR 22007: Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1 SET sql_mode=''; INSERT INTO t2 SELECT a FROM t1; Warnings: Warning 1292 Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1 SELECT * FROM t2 ORDER BY a; a NULL 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 SET sql_mode=DEFAULT; DROP TABLE t2; CREATE TABLE t2 (a UUID NOT NULL); INSERT INTO t2 SELECT a FROM t1; ERROR 22007: Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1 SET sql_mode=''; INSERT INTO t2 SELECT a FROM t1; Warnings: Warning 1292 Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1 SELECT * FROM t2 ORDER BY a; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 SET sql_mode=DEFAULT; DROP TABLE t2; DROP TABLE t1; # # Implicit CAST on INSERT..SELECT, binary format # 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; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000001 ffff0000-0000-0000-0000-000000000002 DROP TABLE t1,t2; # # CAST to other data types # SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DOUBLE); ERROR HY000: Illegal parameter data type uuid for operation 'double_typecast' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS FLOAT); ERROR HY000: Illegal parameter data type uuid for operation 'float_typecast' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DECIMAL); ERROR HY000: Illegal parameter data type uuid for operation 'decimal_typecast' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS SIGNED); ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_signed' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS UNSIGNED); ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_unsigned' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS TIME); ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_time' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATE); ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_date' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATETIME); ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_datetime' SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR); CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR) 00000000-0000-0000-0000-000000000000 CREATE TABLE t1 AS SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR) AS a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(36) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; Table Create Table t2 CREATE TABLE `t2` ( `CAST(a AS CHAR)` varchar(36) DEFAULT NULL, `CAST(a AS CHAR(36))` varchar(36) DEFAULT NULL, `CAST(a AS CHAR(530))` text DEFAULT NULL, `CAST(a AS CHAR(65535))` text DEFAULT NULL, `CAST(a AS CHAR(66000))` mediumtext DEFAULT NULL, `CAST(a AS CHAR(16777215))` mediumtext DEFAULT NULL, `CAST(a AS CHAR(16777216))` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; CAST(a AS CHAR) ffffffff-ffff-ffff-ffff-ffffffffffff CAST(a AS CHAR(36)) ffffffff-ffff-ffff-ffff-ffffffffffff CAST(a AS CHAR(530)) ffffffff-ffff-ffff-ffff-ffffffffffff CAST(a AS CHAR(65535)) ffffffff-ffff-ffff-ffff-ffffffffffff CAST(a AS CHAR(66000)) ffffffff-ffff-ffff-ffff-ffffffffffff CAST(a AS CHAR(16777215)) ffffffff-ffff-ffff-ffff-ffffffffffff CAST(a AS CHAR(16777216)) ffffffff-ffff-ffff-ffff-ffffffffffff 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; Table Create Table t2 CREATE TABLE `t2` ( `cb4` binary(4) DEFAULT NULL, `cb` binary(16) DEFAULT NULL, `cb16` binary(16) DEFAULT NULL, `cb32` binary(32) DEFAULT NULL, `cb530` varbinary(530) DEFAULT NULL, `cb65535` blob DEFAULT NULL, `cb66000` mediumblob DEFAULT NULL, `cb16777215` mediumblob DEFAULT NULL, `cb16777216` longblob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; Table Create Table t2 CREATE TABLE `t2` ( `cb4` binary(4) DEFAULT NULL, `cb` binary(16) DEFAULT NULL, `cb16` binary(16) DEFAULT NULL, `cb32` binary(32) DEFAULT NULL, `cb530` varbinary(530) DEFAULT NULL, `cb65535` blob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT HEX(cb4), HEX(cb), HEX(cb16), HEX(cb32), LENGTH(cb530), LENGTH(cb65535) FROM t2; HEX(cb4) FFFFFFFF HEX(cb) FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF HEX(cb16) FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF HEX(cb32) FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF00000000000000000000000000000000 LENGTH(cb530) 530 LENGTH(cb65535) 65535 DROP TABLE t2; DROP TABLE t1; # # Implicit conversion to other types in INSERT # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR 01000: Data truncated for column 'a' at row 1 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR 01000: Data truncated for column 'a' at row 1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(32,0)); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR 01000: Data truncated for column 'a' at row 1 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; # # Boolean context # 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; 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 0 1 1 0 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; a a IS TRUE a IS FALSE 00000000-0000-0000-0000-000000000000 0 1 00000000-0000-0000-0000-000000000001 1 0 DROP TABLE t1; 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; ERROR HY000: Illegal parameter data types uuid and bigint for operation '<>' DROP TABLE t1; # # GROUP BY # 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; a COUNT(*) 00000000-0000-0000-0000-000000000000 2 00000000-0000-0000-0000-000000000001 3 00000000-0000-0000-0000-000000000002 4 DROP TABLE t1; # # Aggregate functions # 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; MIN(a) MAX(a) 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000002 CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `MIN(a)` uuid DEFAULT NULL, `MAX(a)` uuid DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; MIN(a) MAX(a) 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000002 DROP TABLE t2; SELECT AVG(a) FROM t1; ERROR HY000: Illegal parameter data type uuid for operation 'avg(' SELECT AVG(DISTINCT a) FROM t1; ERROR HY000: Illegal parameter data type uuid for operation 'avg(distinct ' SELECT SUM(a) FROM t1; ERROR HY000: Illegal parameter data type uuid for operation 'sum(' SELECT SUM(DISTINCT a) FROM t1; ERROR HY000: Illegal parameter data type uuid for operation 'sum(distinct ' SELECT STDDEV(a) FROM t1; ERROR HY000: Illegal parameter data type uuid for operation 'std(' SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; GROUP_CONCAT(a ORDER BY a) 00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002 SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; a GROUP_CONCAT(a ORDER BY a) 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002 DROP TABLE t1; # # Window functions # 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; a LAG(a) OVER (ORDER BY a) LEAD(a) OVER (ORDER BY a) 00000000-0000-0000-0000-000000000001 NULL 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000003 NULL 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; 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) 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000004 00000000-0000-0000-0000-000000000003 00000000-0000-0000-0000-000000000004 DROP TABLE t1; # # Prepared statements # EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a' USING CAST('00000000-0000-0000-0000-000000000000' AS UUID); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` uuid NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000003 EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING '00000000-0000-0000-0000-000000000001'; a 00000000-0000-0000-0000-000000000001 EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING CAST('00000000-0000-0000-0000-000000000002' AS UUID); a 00000000-0000-0000-0000-000000000002 EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 0x00000000000000000000000000000003; a 00000000-0000-0000-0000-000000000003 DROP TABLE t1; # # Character set and collation aggregation # 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; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(36) DEFAULT NULL, `c2` varchar(36) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(36) CHARACTER SET utf8mb3 DEFAULT NULL, `c2` varchar(36) CHARACTER SET utf8mb3 DEFAULT NULL, `c3` varchar(36) CHARACTER SET utf8mb3 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(36) DEFAULT NULL, `c2` varchar(36) DEFAULT NULL, `c3` varchar(36) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; DROP TABLE t1; # # UNION # 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; Table Create Table t1 CREATE TABLE `t1` ( `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c; c 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 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; Table Create Table t1 CREATE TABLE `t1` ( `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c; c 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 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; Table Create Table t1 CREATE TABLE `t1` ( `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c; c 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 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; Table Create Table t1 CREATE TABLE `t1` ( `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c; c 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 DROP TABLE t1; CREATE TABLE t1 AS SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c UNION SELECT 1; ERROR HY000: Illegal parameter data types uuid and int for operation 'UNION' # # Unary operators # SELECT -CAST('00000000-0000-0000-0000-000000000000' AS UUID); ERROR HY000: Illegal parameter data type uuid for operation '-' SELECT ABS(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'abs' SELECT ROUND(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'round' SELECT CEILING(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'ceiling' SELECT FLOOR(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'floor' # # Arithmetic operators # SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) + 1; ERROR HY000: Illegal parameter data types uuid and int for operation '+' SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) - 1; ERROR HY000: Illegal parameter data types uuid and int for operation '-' SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) * 1; ERROR HY000: Illegal parameter data types uuid and int for operation '*' SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) / 1; ERROR HY000: Illegal parameter data types uuid and int for operation '/' SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) MOD 1; ERROR HY000: Illegal parameter data types uuid and int for operation 'MOD' # # Misc # SELECT RAND(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'rand' SELECT FROM_UNIXTIME(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'from_unixtime' SELECT HOUR(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'hour' SELECT YEAR(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'year' SELECT RELEASE_LOCK(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); ERROR HY000: Illegal parameter data type uuid for operation 'release_lock' SELECT JSON_LENGTH(CAST('00000000-0000-0000-0000-000000000000' AS UUID)); JSON_LENGTH(CAST('00000000-0000-0000-0000-000000000000' AS UUID)) 1 # # Virtual columns # CREATE TABLE t1 ( a INT, b UUID GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS UUID)), INDEX(b) ); ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `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; a b 0 00000000-0000-0000-0000-000000000010 1 00000000-0000-0000-0000-000000000011 2 00000000-0000-0000-0000-000000000012 3 00000000-0000-0000-0000-000000000013 4 00000000-0000-0000-0000-000000000014 5 00000000-0000-0000-0000-000000000015 6 00000000-0000-0000-0000-000000000016 7 00000000-0000-0000-0000-000000000017 8 00000000-0000-0000-0000-000000000018 9 00000000-0000-0000-0000-000000000019 10 00000000-0000-0000-0000-00000000001a 11 00000000-0000-0000-0000-00000000001b 12 00000000-0000-0000-0000-00000000001c 13 00000000-0000-0000-0000-00000000001d 14 00000000-0000-0000-0000-00000000001e 15 00000000-0000-0000-0000-00000000001f DROP TABLE t1; # # VIEW # 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; a 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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; c 00000000-0000-0000-0000-000000000010 00000000-0000-0000-0000-000000000011 00000000-0000-0000-0000-000000000012 00000000-0000-0000-0000-000000000013 00000000-0000-0000-0000-000000000014 00000000-0000-0000-0000-000000000015 00000000-0000-0000-0000-000000000016 00000000-0000-0000-0000-000000000017 00000000-0000-0000-0000-000000000018 00000000-0000-0000-0000-000000000019 00000000-0000-0000-0000-00000000001a 00000000-0000-0000-0000-00000000001b 00000000-0000-0000-0000-00000000001c 00000000-0000-0000-0000-00000000001d 00000000-0000-0000-0000-00000000001e 00000000-0000-0000-0000-00000000001f 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; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci DESCRIBE v1; Field Type Null Key Default Extra a uuid YES 00000000-0000-0000-0000-000000000000 INSERT INTO v1 VALUES (DEFAULT), ('00000000-0000-0000-0000-000000000001'), ('00000000-0000-0000-0000-000000000002'); SELECT * FROM t1; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 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; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci DESCRIBE v1; Field Type Null Key Default Extra a uuid YES cast('00000000-0000-0000-0000-000000000000' as uuid) INSERT INTO v1 VALUES (DEFAULT), ('00000000-0000-0000-0000-000000000001'), ('00000000-0000-0000-0000-000000000002'); SELECT * FROM t1; a 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 DROP VIEW v1; DROP TABLE t1; # # Subqueries # 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; a 00000000-0000-0000-0000-000000000000 SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a; a 00000000-0000-0000-0000-000000000002 SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'00000000-0000-0000-0000-000000000000') ORDER BY a; a 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 DROP TABLE t1; # # Stored routines # CREATE PROCEDURE p1(a UUID) BEGIN DECLARE b UUID DEFAULT CONCAT('1',SUBSTRING(a,2,36)); SELECT a, b; END; $$ CALL p1('00000000-0000-0000-0000-000000000001'); a b 00000000-0000-0000-0000-000000000001 10000000-0000-0000-0000-000000000001 CALL p1(CAST('00000000-0000-0000-0000-000000000002' AS UUID)); a b 00000000-0000-0000-0000-000000000002 10000000-0000-0000-0000-000000000002 DROP PROCEDURE p1; CREATE FUNCTION f1(a UUID) RETURNS UUID BEGIN RETURN CONCAT('1',SUBSTRING(a,2,36)); END; $$ SELECT f1('00000000-0000-0000-0000-000000000001'); f1('00000000-0000-0000-0000-000000000001') 10000000-0000-0000-0000-000000000001 SELECT f1(CAST('00000000-0000-0000-0000-000000000002' AS UUID)); f1(CAST('00000000-0000-0000-0000-000000000002' AS UUID)) 10000000-0000-0000-0000-000000000002 DROP FUNCTION f1; # # Anchored data types in SP variables # CREATE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); CREATE PROCEDURE p1() BEGIN DECLARE va TYPE OF t1.a; SELECT MAX(a) INTO va FROM t1; SELECT va; END; $$ CALL p1; va 00000000-0000-0000-0000-000000000001 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' ); 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; $$ CALL p1; va.a va.b 00000000-0000-0000-0000-00000000000a 00000000-0000-0000-0000-00000000000b DROP PROCEDURE p1; DROP TABLE t1; # # Optimizer: make_const_item_for_comparison # 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-000000000001' and `test`.`t1`.`id` > 0 DROP TABLE t1; # # Optimizer: equal field propagation # 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-000000000001' and octet_length(concat(UUID'00000000-0000-0000-0000-000000000001',rand())) > 1 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID)) AND LENGTH(a)>1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-000000000001' DROP TABLE t1; # # Optimizer: equal expression propagation # 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); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00000000-0000-0000-0000-000000000001' and concat(`test`.`t1`.`a`) = '00000000-0000-0000-0000-000000000001' DROP TABLE t1; # # Subquery materialization # 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 index NULL a 17 NULL 2 Using index EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t1 index_subquery a a 17 func 2 Using index; Using where SET @@optimizer_switch=DEFAULT; DROP TABLE t1; # # ALTER from UUID to UUID # 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; a b 00000000-0000-0000-0000-000000000001 1.00 DROP TABLE t1; # # ALTER to character string data types # CREATE OR REPLACE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); SELECT CAST(a AS CHAR(36)) FROM t1; CAST(a AS CHAR(36)) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a CHAR(39); SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 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; a 00000000-0000-0000-0000-000000000001 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; a 00000000-0000-0000-0000-000000000001 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; a 00000000-0000-0000-0000-000000000001 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; a 00000000-0000-0000-0000-000000000001 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; a 00000000-0000-0000-0000-000000000001 DROP TABLE t1; # # ALTER from character string data types # CREATE OR REPLACE TABLE t1 (a CHAR(64)); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); SELECT CAST(a AS UUID) FROM t1; CAST(a AS UUID) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 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; CAST(a AS UUID) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 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; CAST(a AS UUID) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 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; CAST(a AS UUID) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 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; CAST(a AS UUID) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 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; CAST(a AS UUID) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a UUID; SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 DROP TABLE t1; # # ALTER to binary string data types # 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; HEX(a) 00000000000000000000000000000001 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; HEX(a) 0000000000000000000000000000000100 DROP TABLE t1; CREATE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); ALTER TABLE t1 MODIFY a BINARY(15); ERROR 22001: Data too long for column 'a' at row 1 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; HEX(a) 00000000000000000000000000000001 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; HEX(a) 00000000000000000000000000000001 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; HEX(a) 00000000000000000000000000000001 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; HEX(a) 00000000000000000000000000000001 DROP TABLE t1; # # ALTER from binary string data types # CREATE TABLE t1 (a BINARY(16)); INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); ALTER TABLE t1 MODIFY a UUID; SELECT a FROM t1; a 20010db8-0000-0000-0000-ff0000428329 DROP TABLE t1; CREATE TABLE t1 (a BINARY(17)); INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900'); ALTER TABLE t1 MODIFY a UUID; ERROR 22007: Incorrect uuid value: ' \x01\x0D\xB8\x00\x00\x00\x00\x00\x00\xFF\x00\x00B\x83)\x00' for column `test`.`t1`.`a` at row 1 DROP TABLE t1; CREATE TABLE t1 (a BINARY(15)); INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283'); ALTER TABLE t1 MODIFY a UUID; ERROR 22007: Incorrect uuid value: ' \x01\x0D\xB8\x00\x00\x00\x00\x00\x00\xFF\x00\x00B\x83' for column `test`.`t1`.`a` at row 1 DROP TABLE t1; CREATE TABLE t1 (a TINYBLOB); INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); ALTER TABLE t1 MODIFY a UUID; SELECT a FROM t1; a 20010db8-0000-0000-0000-ff0000428329 DROP TABLE t1; CREATE TABLE t1 (a BLOB); INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); ALTER TABLE t1 MODIFY a UUID; SELECT a FROM t1; a 20010db8-0000-0000-0000-ff0000428329 DROP TABLE t1; CREATE TABLE t1 (a MEDIUMBLOB); INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); ALTER TABLE t1 MODIFY a UUID; SELECT a FROM t1; a 20010db8-0000-0000-0000-ff0000428329 DROP TABLE t1; CREATE TABLE t1 (a BLOB); INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); ALTER TABLE t1 MODIFY a UUID; SELECT a FROM t1; a 20010db8-0000-0000-0000-ff0000428329 DROP TABLE t1; # # SET from UUID to UUID # 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; b ffffffff-ffff-ffff-ffff-ffffffffffff DROP TABLE t1; # # SET from UUID to numeric # CREATE TABLE t1 (a UUID, b INT); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect integer value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a UUID, b DOUBLE); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect double value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a UUID, b DECIMAL(32,0)); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect decimal value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a UUID, b YEAR); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect integer value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; # # SET from numeric to UUID # CREATE TABLE t1 (a INT, b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(32,0), b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a YEAR, b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '2001' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; # # SET from UUID to temporal # CREATE TABLE t1 (a UUID, b TIME); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect time value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a UUID, b DATE); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect date value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a UUID, b DATETIME); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect datetime value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a UUID, b TIMESTAMP NULL DEFAULT NULL); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect datetime value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; # # SET from temporal to UUID # CREATE TABLE t1 (a TIME, b UUID); INSERT INTO t1 VALUES ('00:00:00', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '00:00:00' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a DATE, b UUID); INSERT INTO t1 VALUES ('2001-01:01', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '2001-01-01' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b UUID); INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '2001-01-01 10:20:30' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP, b UUID); INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); UPDATE t1 SET b=a; ERROR 22007: Incorrect uuid value: '2001-01-01 10:20:30' for column `test`.`t1`.`b` at row 1 SELECT b FROM t1; b NULL DROP TABLE t1; # # SET from UUID to character string # 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff DROP TABLE t1; # # SET from character string to UUID # 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff 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; b ffffffff-ffff-ffff-ffff-ffffffffffff DROP TABLE t1; # # SET from UUID to binary # 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; HEX(b) FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 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; HEX(b) FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 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; HEX(b) FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF DROP TABLE t1; # # SET from binary to UUID # 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; b ffff0000-0000-0000-0000-00000000ffff 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; b ffff0000-0000-0000-0000-00000000ffff 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; b ffff0000-0000-0000-0000-00000000ffff DROP TABLE t1; # # Limit clause parameter # TODO: this should fail. # The test for a valid data type should be moved # from parse time to fix_fields() time, and performed # for both Item_splocal and Item_param. # EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('00000000-0000-0000-0000-000000000000' AS UUID); 1 # # ALTER from UUID to CHAR # CREATE OR REPLACE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); SELECT CAST(a AS CHAR(36)) FROM t1; CAST(a AS CHAR(36)) 00000000-0000-0000-0000-000000000001 ALTER TABLE t1 MODIFY a CHAR(36); SELECT * FROM t1; a 00000000-0000-0000-0000-000000000001 DROP TABLE t1; # # ALTER from UUID to BINARY(16) # 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; HEX(a) 00000000000000000000000000000001 DROP TABLE t1; # # CAST(uuid AS BINARY) # CREATE OR REPLACE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001'); SELECT HEX(CAST(a AS BINARY)) FROM t1; HEX(CAST(a AS BINARY)) 00000000000000000000000000000001 SELECT HEX(CAST(a AS BINARY(16))) FROM t1; HEX(CAST(a AS BINARY(16))) 00000000000000000000000000000001 DROP TABLE t1; # # CAST from UUID to FLOAT # CREATE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000'); SELECT CAST(a AS FLOAT) FROM t1; ERROR HY000: Illegal parameter data type uuid for operation 'float_typecast' DROP TABLE t1; # # CAST(UUID AS BINARY) - metadata # CREATE TABLE t1 (a UUID); 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; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def CAST(a AS BINARY(0)) 254 0 0 Y 128 0 63 def CAST(a AS BINARY(1)) 254 1 0 Y 128 0 63 def CAST(a AS BINARY(16)) 254 16 0 Y 128 0 63 def CAST(a AS BINARY(255)) 254 255 0 Y 128 0 63 def CAST(a AS BINARY(256)) 253 256 0 Y 128 0 63 def CAST(a AS BINARY(512)) 253 512 0 Y 128 0 63 def CAST(a AS BINARY(513)) 253 513 0 Y 128 0 63 def CAST(a AS BINARY(65532)) 253 65532 0 Y 128 0 63 def CAST(a AS BINARY(65533)) 252 65533 0 Y 128 0 63 def CAST(a AS BINARY(65534)) 252 65534 0 Y 128 0 63 def CAST(a AS BINARY(65535)) 252 65535 0 Y 128 0 63 def CAST(a AS BINARY(65536)) 250 65536 0 Y 128 0 63 def CAST(a AS BINARY(16777215)) 250 16777215 0 Y 128 0 63 def CAST(a AS BINARY(16777216)) 251 16777216 0 Y 128 0 63 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)) DROP TABLE t1; # # MIN(uuid) with GROUP BY # 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; MIN(a) MAX(a) 00000000-0000-0000-0000-000000000fff 00000000-0000-0000-0000-000000008888 DROP TABLE t1;