diff options
Diffstat (limited to 'mysql-test/main/type_set.result')
| -rw-r--r-- | mysql-test/main/type_set.result | 360 |
1 files changed, 360 insertions, 0 deletions
diff --git a/mysql-test/main/type_set.result b/mysql-test/main/type_set.result new file mode 100644 index 00000000000..ae72619b5ae --- /dev/null +++ b/mysql-test/main/type_set.result @@ -0,0 +1,360 @@ +drop table if exists t1; +create table t1 (a set (' ','a','b') not null); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` set('','a','b') NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a set (' ','a','b ') not null default 'b '); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` set('','a','b') NOT NULL DEFAULT 'b' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +CREATE TABLE t1 ( user varchar(64) NOT NULL default '', path varchar(255) NOT NULL default '', privilege set('select','RESERVED30','RESERVED29','RESERVED28','RESERVED27','RESERVED26', 'RESERVED25','RESERVED24','data.delete','RESERVED22','RESERVED21', 'RESERVED20','data.insert.none','data.insert.approve', 'data.insert.delete','data.insert.move','data.insert.propose', 'data.insert.reject','RESERVED13','RESERVED12','RESERVED11','RESERVED10', 'RESERVED09','data.update','RESERVED07','RESERVED06','RESERVED05', 'RESERVED04','metadata.delete','metadata.put','RESERVED01','RESERVED00') NOT NULL default '', KEY user (user) ) ENGINE=MyISAM CHARSET=utf8; +DROP TABLE t1; +set names latin1; +create table t1 (s set ('a','A') character set latin1 collate latin1_bin); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s` set('a','A') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('a,A'),('A,a'),('A'); +select s from t1 order by s; +s +a +A +a,A +a,A +select s from t1 order by concat(s); +s +A +a +a,A +a,A +drop table t1; +CREATE TABLE t1 (c set('ae','oe','ue','ss') collate latin1_german2_ci); +INSERT INTO t1 VALUES ('ä'),('ö'),('ü'),('ß'); +INSERT INTO t1 VALUES ('ae'),('oe'),('ue'),('ss'); +INSERT INTO t1 VALUES ('ä,ö,ü,ß'); +INSERT INTO t1 VALUES ('ae,oe,ue,ss'); +SELECT c FROM t1 ORDER BY c; +c +ae +ae +oe +oe +ue +ue +ss +ss +ae,oe,ue,ss +ae,oe,ue,ss +SELECT c FROM t1 ORDER BY concat(c); +c +ae +ae +ae,oe,ue,ss +ae,oe,ue,ss +oe +oe +ss +ss +ue +ue +DROP TABLE t1; +create table t1(f1 +set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17', +'18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33', +'34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49', +'50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','128')); +ERROR HY000: Too many strings for column f1 and SET +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +create table t1(f1 +set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17', +'18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33', +'34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49', +'50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','1')); +Warnings: +Note 1291 Column 'f1' has duplicated value '1' in SET +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','1') DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +CREATE TABLE t1(c set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64')); +INSERT INTO t1 VALUES(7); +INSERT INTO t1 VALUES(9223372036854775808); +SELECT * FROM t1; +c +1,2,3 +64 +DROP TABLE t1; +CREATE TABLE t1 ( +set_unique_utf8 set ('a','b','c','d','e','f','g','h','i','j','k','l', +'m','n','o','p','q','r','s','t','u','v','w','x', +'y','z') CHARACTER SET utf8, +unique (set_unique_utf8) +); +INSERT INTO t1 ( set_unique_utf8 ) VALUES ( '' ); +INSERT INTO t1 ( set_unique_utf8 ) VALUES ( '' ); +ERROR 23000: Duplicate entry '' for key 'set_unique_utf8' +DROP TABLE t1; +End of 5.0 tests +# +# Start of 10.0 tests +# +# +# MDEV-6950 Bad results with joins compating DATE and INT/ENUM/VARCHAR columns +# +CREATE TABLE t1 (c1 DATE PRIMARY KEY); +INSERT INTO t1 VALUES ('2001-01-01'); +CREATE TABLE t2 (c1 SET('2001-01-01','2001/01/01')); +INSERT INTO t2 VALUES ('2001-01-01'); +INSERT INTO t2 VALUES ('2001/01/01'); +SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +ALTER TABLE t2 ADD PRIMARY KEY(c1); +SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +# t2 should NOT be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; +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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +DROP TABLE t1, t2; +# +# MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column +# +CREATE TABLE t1 (c1 ENUM('a') CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +ALTER TABLE t2 ADD PRIMARY KEY(c1); +SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +# t2 should NOT be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +ALTER TABLE t2 ADD PRIMARY KEY(c1); +SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +# t2 should NOT be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +ALTER TABLE t2 ADD PRIMARY KEY(c1); +SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +c1 +a +a +# t2 should NOT be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; +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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +DROP TABLE IF EXISTS t1,t2; +# +# MDEV-6993 Bad results with join comparing DECIMAL and ENUM/SET columns +# +CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (c1 SET('a','b')); +INSERT INTO t2 VALUES ('a'),('b'); +SELECT t1.* FROM t1 NATURAL JOIN t2; +c1 +1.0 +2.0 +ALTER TABLE t2 ADD PRIMARY KEY(c1); +SELECT t1.* FROM t1 NATURAL JOIN t2; +c1 +1.0 +2.0 +SELECT t1.* FROM t1 LEFT OUTER JOIN t2 USING (c1); +c1 +1.0 +2.0 +DROP TABLE t1,t2; +CREATE TABLE t1 (a DECIMAL(10,1), b SET('1','2')); +INSERT INTO t1 (a) VALUES (1),(2); +UPDATE t1 SET b=a; +SELECT * FROM t1; +a b +1.0 1 +2.0 2 +ALTER TABLE t1 MODIFY a SET('1','2'); +SELECT * FROM t1; +a b +1 1 +2 2 +DROP TABLE t1; +# +# End of 10.0 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-8729 Wrong result for SELECT..WHERE HEX(enum_column)='61' AND enum_column='a ' +# +CREATE TABLE t1 (a SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT * FROM t1 WHERE a='a '; +a +a +SELECT * FROM t1 WHERE HEX(a)='61'; +a +a +SELECT * FROM t1 WHERE HEX(a)='61' AND a='a '; +a +a +# Can't propagate the equality into HEX(a), because binary collations still ignore trailing spaces +EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)='61' AND a='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`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a ' and hex(`test`.`t1`.`a`) = '61' +DROP TABLE t1; +CREATE TABLE t1 (a SET('a','a ') CHARACTER SET BINARY); +INSERT INTO t1 VALUES ('a'),('a '); +SELECT * FROM t1 WHERE a='a '; +a +a +SELECT * FROM t1 WHERE HEX(a)='61'; +a +a +SELECT * FROM t1 WHERE HEX(a)='61' AND a='a'; +a +a +SELECT * FROM t1 WHERE HEX(a)='61' AND a='a '; +a +# Ok to propagate the equality into HEX(a), because "CHARACTER SET BINARY" does not ignore trailing spaces +EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)='61' AND a='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`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)='61' AND a='a '; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +DROP TABLE t1; +# +# End of 10.1 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-12432 Range optimizer for ENUM and SET does not return "Impossible WHERE" in some case +# +CREATE TABLE t1 (a SET('a','b','c','1'),KEY(a)); +INSERT INTO t1 VALUES ('a'),('b'),('c'),('1'); +EXPLAIN SELECT * FROM t1 WHERE a='xx'; +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 +EXPLAIN SELECT * FROM t1 WHERE a='99999999'; +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 +EXPLAIN SELECT * FROM t1 WHERE a=100.1e0; +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 +EXPLAIN SELECT * FROM t1 WHERE a=100; +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 +EXPLAIN SELECT * FROM t1 WHERE a=100.1; +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 +EXPLAIN SELECT * FROM t1 WHERE a='100'; +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 +EXPLAIN SELECT * FROM t1 WHERE a='1x'; +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 +EXPLAIN SELECT * FROM t1 WHERE a='1.0'; +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 +EXPLAIN SELECT * FROM t1 WHERE a='1.1'; +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 +DROP TABLE t1; +# +# MDEV-11155 Bad error message when creating a SET column with comma and non-ASCII characters +# +SET NAMES utf8; +CREATE TABLE t1 (a SET('a,bü')); +ERROR 22007: Illegal set 'a,bü' value found during parsing |