diff options
Diffstat (limited to 'plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result')
-rw-r--r-- | plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result | 203 |
1 files changed, 203 insertions, 0 deletions
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result new file mode 100644 index 00000000000..d769f1a1374 --- /dev/null +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result @@ -0,0 +1,203 @@ +# +# Start of 10.7 tests +# +# +# MDEV-4958 Adding datatype UUID +# +SET default_storage_engine=InnoDB; +# +# Range optimizer +# +CREATE TABLE t1 (a UUID, INDEX(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` uuid DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +FOR i IN 0..255 +DO +INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00, 15),CHAR(i USING BINARY))); +END FOR +$$ +SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff'; +a +00000000-0000-0000-0000-0000000000ff +EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +SELECT * FROM t1 WHERE a='garbage'; +a +Warnings: +Warning 1292 Incorrect uuid value: 'garbage' +EXPLAIN SELECT * FROM t1 WHERE a='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect uuid value: 'garbage' +SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe'; +a +00000000-0000-0000-0000-0000000000fe +00000000-0000-0000-0000-0000000000ff +EXPLAIN SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a>='garbage'; +a +EXPLAIN SELECT * FROM t1 WHERE a>='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE a IN +( +'00000000-0000-0000-0000-000000000080', +'00000000-0000-0000-0000-0000000000a0', +'00000000-0000-0000-0000-0000000000f0' +); +a +00000000-0000-0000-0000-000000000080 +00000000-0000-0000-0000-0000000000a0 +00000000-0000-0000-0000-0000000000f0 +EXPLAIN SELECT * FROM t1 WHERE a IN +( +'00000000-0000-0000-0000-000000000080', +'00000000-0000-0000-0000-0000000000a0', +'00000000-0000-0000-0000-0000000000f0' +); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a IN +( +'00000000-0000-0000-0000-000000000080', +'00000000-0000-0000-0000-0000000000a0', +'garbage' +); +a +00000000-0000-0000-0000-000000000080 +00000000-0000-0000-0000-0000000000a0 +Warnings: +Warning 1292 Incorrect uuid value: 'garbage' +EXPLAIN SELECT * FROM t1 WHERE a IN +( +'00000000-0000-0000-0000-000000000080', +'00000000-0000-0000-0000-0000000000a0', +'garbage' +); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +Warnings: +Warning 1292 Incorrect uuid value: 'garbage' +SELECT * FROM t1 WHERE a BETWEEN +'00000000-0000-0000-0000-000000000080' AND +'00000000-0000-0000-0000-000000000081'; +a +00000000-0000-0000-0000-000000000080 +00000000-0000-0000-0000-000000000081 +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN +'00000000-0000-0000-0000-000000000080' AND +'00000000-0000-0000-0000-000000000081'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a BETWEEN +'00000000-0000-0000-0000-000000000080' AND +'garbage'; +a +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN +'00000000-0000-0000-0000-000000000080' AND +'garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID); +a +00000000-0000-0000-0000-0000000000ff +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref a a 17 const 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-0000000000ff' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a UUID,KEY(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` uuid DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +BEGIN; +FOR i IN 0..255 +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; +$$ +COMMIT; +EXPLAIN SELECT * FROM t1 WHERE a='ff000000-0000-0000-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00ff0000-0000-0000-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='0000ff00-0000-0000-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='000000ff-0000-0000-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-ff00-0000-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-00ff-0000-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-ff00-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-00ff-0000-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-ff00-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-00ff-000000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-ff0000000000'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +DROP TABLE t1; +# +# MDEV-26742 Assertion `field->type_handler() == this' failed in FixedBinTypeBundle<NATIVE_LEN, MAX_CHAR_LEN>::Type_handler_fbt::stored_field_cmp_to_item +# +CREATE TABLE t1 ( pk uuid, c text) engine=myisam; +INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000',1); +CREATE TABLE t2 ( d text, KEY (d)) engine=innodb ; +Warnings: +Note 1071 Specified key was too long; max key length is 3072 bytes +INSERT INTO t2 VALUES (2); +SELECT * FROM t2 JOIN t1 ON ( t1.pk > t2.d); +d pk c +Warnings: +Warning 1292 Incorrect uuid value: '2' +EXPLAIN SELECT * FROM t2 JOIN t1 ON ( t1.pk > t2.d); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 ALL d NULL NULL NULL 1 Using where +UPDATE t2 JOIN t1 ON ( t1.pk > t2.d) SET t1.c = 1; +ERROR 22007: Incorrect uuid value: '2' +DROP TABLE t1, t2; +# +# End of 10.7 tests +# |