diff options
Diffstat (limited to 'mysql-test/r')
25 files changed, 1527 insertions, 103 deletions
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 428629e7e9e..abb21b7cee7 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -116,6 +116,26 @@ select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' 1 1 1 SET CHARACTER SET koi8r; +create table t1 (a varchar(2) character set ucs2 collate ucs2_bin, key(a)); +insert into t1 values ('A'),('A'),('B'),('C'),('D'),('A\t'); +insert into t1 values ('A\0'),('A\0'),('A\0'),('A\0'),('AZ'); +select hex(a) from t1 where a like 'A_' order by a; +hex(a) +00410000 +00410000 +00410000 +00410000 +00410009 +0041005A +select hex(a) from t1 ignore key(a) where a like 'A_' order by a; +hex(a) +00410000 +00410000 +00410000 +00410000 +00410009 +0041005A +drop table t1; CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2); INSERT INTO t1 VALUES (_koi8r'ò',_koi8r'ò'), (X'2004',X'2004'); SELECT hex(word) FROM t1 ORDER BY word; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 668b1b0febe..55c32c6a1d4 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1848,6 +1848,24 @@ select hex(_utf8 B'001111111111'); ERROR HY000: Invalid utf8 character string: 'FF' select (_utf8 X'616263FF'); ERROR HY000: Invalid utf8 character string: 'FF' +# +# Bug#44131 Binary-mode "order by" returns records in incorrect order for UTF-8 strings +# +CREATE TABLE t1 (id int not null primary key, name varchar(10)) character set utf8; +INSERT INTO t1 VALUES +(2,'一二三01'),(3,'一二三09'),(4,'一二三02'),(5,'一二三08'), +(6,'一二三11'),(7,'一二三91'),(8,'一二三21'),(9,'一二三81'); +SELECT * FROM t1 ORDER BY BINARY(name); +id name +2 一二三01 +4 一二三02 +5 一二三08 +3 一二三09 +6 一二三11 +8 一二三21 +9 一二三81 +7 一二三91 +DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065); SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; diff --git a/mysql-test/r/exampledb.result b/mysql-test/r/exampledb.result deleted file mode 100644 index 6eea24e2e1f..00000000000 --- a/mysql-test/r/exampledb.result +++ /dev/null @@ -1,8 +0,0 @@ -drop database if exists events_test; -drop database if exists events_test2; -drop table if exists t1; -CREATE TABLE t1 ( -Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, -Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL -) ENGINE=example; -drop table t1; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 36b1040fbe2..4b651e270e6 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -560,6 +560,20 @@ MATCH (col) AGAINST('findme') DEALLOCATE PREPARE s; DROP TABLE t1; # +# Bug #49250 : spatial btree index corruption and crash +# Part two : fulltext syntax check +# +CREATE TABLE t1(col1 TEXT, +FULLTEXT INDEX USING BTREE (col1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1))' at line 2 +CREATE TABLE t2(col1 TEXT); +CREATE FULLTEXT INDEX USING BTREE ON t2(col); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 +ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1)' at line 1 +DROP TABLE t2; +End of 5.0 tests +# # Bug #47930: MATCH IN BOOLEAN MODE returns too many results # inside subquery # diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 35ce190feb3..64988f9de50 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2561,6 +2561,35 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using join buffer 2 DERIVED t1 ALL NULL NULL NULL NULL 2 drop table t1; +# +# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 +# +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a VARCHAR(20), b INT); +CREATE TABLE t2 (a VARCHAR(20), b INT); +INSERT INTO t1 VALUES ('ABC', 1); +INSERT INTO t2 VALUES ('ABC', 1); +SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a) +FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b; +DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a) +secret +SELECT DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a) +FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b; +DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a) +secret +SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC') +FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b; +DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC') +secret +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1); +INSERT INTO t2 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1); +SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a) +FROM t2 WHERE t2.b = 1 GROUP BY t2.b; +DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a) +secret +DROP TABLE t1, t2; Start of 5.4 tests SELECT format(12345678901234567890.123, 3); format(12345678901234567890.123, 3) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index b40ff6be160..3e28227d542 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -984,6 +984,19 @@ GEOMFROMTEXT( SELECT 1 FROM t1 WHERE a <> (SELECT GEOMETRYCOLLECTIONFROMWKB(b) FROM t1); 1 DROP TABLE t1; +# +# Bug #49250 : spatial btree index corruption and crash +# Part one : spatial syntax check +# +CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL, +SPATIAL INDEX USING BTREE (col1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1))' at line 2 +CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL); +CREATE SPATIAL INDEX USING BTREE ON t2(col); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 +ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1)' at line 1 +DROP TABLE t2; End of 5.0 tests create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); create view v1 as select * from t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index bc77072f67a..dbe8b48d503 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1258,3 +1258,38 @@ SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL; c e d 1 0 NULL DROP TABLE t1,t2; +# +# Bug#47650: using group by with rollup without indexes returns incorrect +# results with where +# +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 ( a INT, b INT ); +INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5); +EXPLAIN +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 LEFT JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 LEFT JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) +1 3 6 3 +NULL 3 6 3 +EXPLAIN +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) +1 3 6 3 +NULL 3 6 3 +DROP TABLE t1, t2; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 16e8a8e5ad1..fb053f32d43 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1855,6 +1855,21 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +# +# Bug #49465: valgrind warnings and incorrect live checksum... +# +CREATE TABLE t1( +a VARCHAR(1), b VARCHAR(1), c VARCHAR(1), +f VARCHAR(1), g VARCHAR(1), h VARCHAR(1), +i VARCHAR(1), j VARCHAR(1), k VARCHAR(1)) CHECKSUM=1; +INSERT INTO t1 VALUES('', '', '', '', '', '', '', '', ''); +CHECKSUM TABLE t1 QUICK; +Table Checksum +test.t1 467455460 +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 467455460 +DROP TABLE t1; End of 5.0 tests create table t1 (a int not null, key `a` (a) key_block_size=1024); show create table t1; diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index b96b579d9b5..394beb042a5 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -127,4 +127,46 @@ mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK set GLOBAL sql_mode=default; +# +# Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table +# but does not set values. +# +CREATE PROCEDURE testproc() BEGIN END; +UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc'; +UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc'; +UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc'; +mtr.global_suppressions OK +mtr.test_suppressions OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.general_log +Error : You can't use locks with log tables. +status : OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.ndb_binlog_index OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.servers OK +mysql.slow_log +Error : You can't use locks with log tables. +status : OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +CALL testproc(); +DROP PROCEDURE testproc; +WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (latin1). Please verify if necessary. +WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (latin1_swedish_ci). Please verify if necessary. +WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary. The --upgrade-system-tables option was used, databases won't be touched. diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 4add29a446f..6827fd0bc76 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1463,6 +1463,15 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; col 1 +# Must use ref-or-null on the a_c index +EXPLAIN +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; +id select_type table type possible_keys key key_len ref rows Extra +x x x ref_or_null a_c,a x x x x x +# Must return 1 row +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; +col +1 DROP TABLE t1; End of 5.0 tests CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index c9c66922a20..0435db628f4 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -24,8 +24,8 @@ a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, b varchar(10), PRIMARY KEY (a) ) -PARTITION BY RANGE (to_days(a)) ( -PARTITION p1 VALUES LESS THAN (733407), +PARTITION BY RANGE (UNIX_TIMESTAMP(a)) ( +PARTITION p1 VALUES LESS THAN (1199134800), PARTITION pmax VALUES LESS THAN MAXVALUE ); INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1'); @@ -37,7 +37,7 @@ a b 2009-07-14 17:35:55 pmax 2009-09-21 17:31:42 pmax ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( -PARTITION p3 VALUES LESS THAN (733969), +PARTITION p3 VALUES LESS THAN (1247688000), PARTITION pmax VALUES LESS THAN MAXVALUE); SELECT * FROM t1; a b @@ -51,9 +51,9 @@ t1 CREATE TABLE `t1` ( `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY RANGE (to_days(a)) -(PARTITION p1 VALUES LESS THAN (733407) ENGINE = MyISAM, - PARTITION p3 VALUES LESS THAN (733969) ENGINE = MyISAM, +/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(a)) +(PARTITION p1 VALUES LESS THAN (1199134800) ENGINE = MyISAM, + PARTITION p3 VALUES LESS THAN (1247688000) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ DROP TABLE t1; create table t1 (a int NOT NULL, b varchar(5) NOT NULL) diff --git a/mysql-test/r/partition_bug18198.result b/mysql-test/r/partition_bug18198.result index 18d7d904bb0..ee7bf514807 100644 --- a/mysql-test/r/partition_bug18198.result +++ b/mysql-test/r/partition_bug18198.result @@ -126,7 +126,7 @@ ERROR HY000: This partition function is not allowed create table t1 (col1 date) partition by range(unix_timestamp(col1)) (partition p0 values less than (10), partition p1 values less than (30)); -ERROR HY000: This partition function is not allowed +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed create table t1 (col1 datetime) partition by range(week(col1)) (partition p0 values less than (10), partition p1 values less than (30)); diff --git a/mysql-test/r/partition_column.result b/mysql-test/r/partition_column.result index 784df3045f0..ddc48b635cf 100644 --- a/mysql-test/r/partition_column.result +++ b/mysql-test/r/partition_column.result @@ -9,6 +9,30 @@ partition by range columns (a,b,c) ( partition p0 values less than (1, maxvalue, 10), partition p1 values less than (1, maxvalue, maxvalue)); ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition +create table t1 (a varchar(5) character set ucs2 collate ucs2_bin) +partition by range columns (a) +(partition p0 values less than (0x0041)); +insert into t1 values (0x00410000); +select hex(a) from t1 where a like 'A_'; +hex(a) +00410000 +explain partitions select hex(a) from t1 where a like 'A_'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 +alter table t1 remove partitioning; +select hex(a) from t1 where a like 'A_'; +hex(a) +00410000 +create index a on t1 (a); +select hex(a) from t1 where a like 'A_'; +hex(a) +00410000 +insert into t1 values ('A_'); +select hex(a) from t1; +hex(a) +00410000 +0041005F +drop table t1; create table t1 (a varchar(1) character set latin1 collate latin1_general_ci) partition by range columns(a) ( partition p0 values less than ('a'), @@ -69,7 +93,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a) +/*!50500 PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN ('''') ENGINE = MyISAM, PARTITION p1 VALUES IN ('\\') ENGINE = MyISAM, PARTITION p2 VALUES IN ('\0') ENGINE = MyISAM) */ @@ -128,7 +152,7 @@ t1 CREATE TABLE `t1` ( `c` varchar(25) DEFAULT NULL, `d` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY RANGE COLUMNS(a,b,c,d) +/*!50500 PARTITION BY RANGE COLUMNS(a,b,c,d) SUBPARTITION BY HASH (to_seconds(d)) SUBPARTITIONS 4 (PARTITION p0 VALUES LESS THAN (1,'0',MAXVALUE,'1900-01-01') ENGINE = MyISAM, @@ -211,7 +235,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a,b) +/*!50500 PARTITION BY LIST COLUMNS(a,b) (PARTITION p0 VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM, PARTITION p1 VALUES IN ((1,1),(2,2)) ENGINE = MyISAM, PARTITION p2 VALUES IN ((3,NULL),(NULL,1)) ENGINE = MyISAM) */ @@ -245,7 +269,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a,b) +/*!50500 PARTITION BY LIST COLUMNS(a,b) (PARTITION p0 VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM, PARTITION p1 VALUES IN ((1,1),(2,2)) ENGINE = MyISAM, PARTITION p2 VALUES IN ((3,NULL),(NULL,1)) ENGINE = MyISAM) */ @@ -299,7 +323,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a) +/*!50500 PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN (2,1) ENGINE = MyISAM, PARTITION p1 VALUES IN (4,NULL,3) ENGINE = MyISAM) */ insert into t1 values (1); @@ -314,7 +338,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a) +/*!50500 PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN (2,1) ENGINE = MyISAM, PARTITION p1 VALUES IN (4,NULL,3) ENGINE = MyISAM) */ drop table t1; @@ -349,7 +373,7 @@ t1 CREATE TABLE `t1` ( `c` varchar(5) DEFAULT NULL, `d` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY RANGE COLUMNS(a,b,c) +/*!50500 PARTITION BY RANGE COLUMNS(a,b,c) SUBPARTITION BY KEY (c,d) SUBPARTITIONS 3 (PARTITION p0 VALUES LESS THAN (1,'abc','abc') ENGINE = MyISAM, @@ -382,7 +406,7 @@ t1 CREATE TABLE `t1` ( `b` varchar(2) DEFAULT NULL, `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY RANGE COLUMNS(a,b,c) +/*!50500 PARTITION BY RANGE COLUMNS(a,b,c) (PARTITION p0 VALUES LESS THAN (1,'A',1) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1,'B',1) ENGINE = MyISAM) */ insert into t1 values (1, 'A', 1); diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index d24c3de3cc0..0a8b7e1b424 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -138,7 +138,7 @@ primary key(a,b)) partition by hash (rand(a)) partitions 2 (partition x1, partition x2); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ') +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ') partitions 2 (partition x1, partition x2)' at line 6 CREATE TABLE t1 ( @@ -149,7 +149,7 @@ primary key(a,b)) partition by range (rand(a)) partitions 2 (partition x1 values less than (0), partition x2 values less than (2)); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ') +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ') partitions 2 (partition x1 values less than (0), partition x2 values less than' at line 6 CREATE TABLE t1 ( @@ -160,7 +160,7 @@ primary key(a,b)) partition by list (rand(a)) partitions 2 (partition x1 values in (1), partition x2 values in (2)); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ') +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ') partitions 2 (partition x1 values in (1), partition x2 values in (2))' at line 6 CREATE TABLE t1 ( @@ -275,7 +275,7 @@ c int not null, primary key (a,b)) partition by key (a) subpartition by hash (rand(a+b)); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')' at line 7 +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')' at line 7 CREATE TABLE t1 ( a int not null, b int not null, @@ -371,7 +371,7 @@ partition by range (3+4) partitions 2 (partition x1 values less than (4) tablespace ts1, partition x2 values less than (8) tablespace ts2); -ERROR HY000: Constant/Random expression in (sub)partitioning function is not allowed +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed CREATE TABLE t1 ( a int not null, b int not null, @@ -540,7 +540,7 @@ partition by list (3+4) partitions 2 (partition x1 values in (4) tablespace ts1, partition x2 values in (8) tablespace ts2); -ERROR HY000: Constant/Random expression in (sub)partitioning function is not allowed +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed CREATE TABLE t1 ( a int not null, b int not null, @@ -631,13 +631,13 @@ partition by range (ascii(v)) ERROR HY000: This partition function is not allowed create table t1 (a int) partition by hash (rand(a)); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')' at line 2 +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')' at line 2 create table t1 (a int) partition by hash(CURTIME() + a); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')' at line 2 +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')' at line 2 create table t1 (a int) partition by hash (NOW()+a); -ERROR 42000: Constant/Random expression in (sub)partitioning function is not allowed near ')' at line 2 +ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')' at line 2 create table t1 (a int) partition by hash (extract(hour from convert_tz(a, '+00:00', '+00:00'))); ERROR HY000: This partition function is not allowed @@ -648,3 +648,295 @@ ERROR HY000: This partition function is not allowed create table t1 (a char(10)) partition by hash (extractvalue(a,'a')); ERROR HY000: This partition function is not allowed +# +# Bug #42849: innodb crash with varying time_zone on partitioned +# timestamp primary key +# +CREATE TABLE old (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (UNIX_TIMESTAMP(a)) ( +PARTITION p VALUES LESS THAN (1219089600), +PARTITION pmax VALUES LESS THAN MAXVALUE); +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (a) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: The PARTITION function returns the wrong type +ALTER TABLE old +PARTITION BY RANGE (a) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: The PARTITION function returns the wrong type +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (a+0) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (a+0) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (a % 2) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (a % 2) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (ABS(a)) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (ABS(a)) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (CEILING(a)) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (CEILING(a)) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (FLOOR(a)) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (FLOOR(a)) ( +PARTITION p VALUES LESS THAN (20080819), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (TO_DAYS(a)) ( +PARTITION p VALUES LESS THAN (733638), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (TO_DAYS(a)) ( +PARTITION p VALUES LESS THAN (733638), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (DAYOFYEAR(a)) ( +PARTITION p VALUES LESS THAN (231), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (DAYOFYEAR(a)) ( +PARTITION p VALUES LESS THAN (231), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (DAYOFMONTH(a)) ( +PARTITION p VALUES LESS THAN (19), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (DAYOFMONTH(a)) ( +PARTITION p VALUES LESS THAN (19), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (DAYOFWEEK(a)) ( +PARTITION p VALUES LESS THAN (3), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (DAYOFWEEK(a)) ( +PARTITION p VALUES LESS THAN (3), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (MONTH(a)) ( +PARTITION p VALUES LESS THAN (8), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (MONTH(a)) ( +PARTITION p VALUES LESS THAN (8), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (HOUR(a)) ( +PARTITION p VALUES LESS THAN (17), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (HOUR(a)) ( +PARTITION p VALUES LESS THAN (17), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (MINUTE(a)) ( +PARTITION p VALUES LESS THAN (55), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (MINUTE(a)) ( +PARTITION p VALUES LESS THAN (55), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (QUARTER(a)) ( +PARTITION p VALUES LESS THAN (3), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (QUARTER(a)) ( +PARTITION p VALUES LESS THAN (3), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (SECOND(a)) ( +PARTITION p VALUES LESS THAN (7), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (SECOND(a)) ( +PARTITION p VALUES LESS THAN (7), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (YEARWEEK(a)) ( +PARTITION p VALUES LESS THAN (200833), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (YEARWEEK(a)) ( +PARTITION p VALUES LESS THAN (200833), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (YEAR(a)) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (YEAR(a)) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (WEEKDAY(a)) ( +PARTITION p VALUES LESS THAN (3), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (WEEKDAY(a)) ( +PARTITION p VALUES LESS THAN (3), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (TIME_TO_SEC(a)) ( +PARTITION p VALUES LESS THAN (64507), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (TIME_TO_SEC(a)) ( +PARTITION p VALUES LESS THAN (64507), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (EXTRACT(DAY FROM a)) ( +PARTITION p VALUES LESS THAN (18), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (EXTRACT(DAY FROM a)) ( +PARTITION p VALUES LESS THAN (18), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL, b TIMESTAMP NOT NULL, PRIMARY KEY(a,b)) +PARTITION BY RANGE (DATEDIFF(a, a)) ( +PARTITION p VALUES LESS THAN (18), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (DATEDIFF(a, a)) ( +PARTITION p VALUES LESS THAN (18), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (YEAR(a + 0)) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (YEAR(a + 0)) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (TO_DAYS(a + '2008-01-01')) ( +PARTITION p VALUES LESS THAN (733638), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (TO_DAYS(a + '2008-01-01')) ( +PARTITION p VALUES LESS THAN (733638), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY) +PARTITION BY RANGE (YEAR(a + '2008-01-01')) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (YEAR(a + '2008-01-01')) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old ADD COLUMN b DATE; +CREATE TABLE new (a TIMESTAMP, b DATE) +PARTITION BY RANGE (YEAR(a + b)) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (YEAR(a + b)) ( +PARTITION p VALUES LESS THAN (2008), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP, b DATE) +PARTITION BY RANGE (TO_DAYS(a + b)) ( +PARTITION p VALUES LESS THAN (733638), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (TO_DAYS(a + b)) ( +PARTITION p VALUES LESS THAN (733638), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP, b date) +PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) ( +PARTITION p VALUES LESS THAN (1219089600), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old +PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) ( +PARTITION p VALUES LESS THAN (1219089600), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +CREATE TABLE new (a TIMESTAMP, b TIMESTAMP) +PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) ( +PARTITION p VALUES LESS THAN (1219089600), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +ALTER TABLE old MODIFY b TIMESTAMP; +ALTER TABLE old +PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) ( +PARTITION p VALUES LESS THAN (1219089600), +PARTITION pmax VALUES LESS THAN MAXVALUE); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +DROP TABLE old; +End of 5.1 tests diff --git a/mysql-test/r/partition_key_cache.result b/mysql-test/r/partition_key_cache.result new file mode 100644 index 00000000000..7fbab34fa41 --- /dev/null +++ b/mysql-test/r/partition_key_cache.result @@ -0,0 +1,417 @@ +DROP TABLE IF EXISTS t1, t2, v, x; +# Actual test of key caches +# Verifing that reads/writes use the key cache correctly +SELECT @org_key_cache_buffer_size:= @@global.default.key_buffer_size; +@org_key_cache_buffer_size:= @@global.default.key_buffer_size +1048576 +# Minimize default key cache (almost disabled). +SET @@global.default.key_buffer_size = 1; +Warnings: +Warning 1292 Truncated incorrect key_buffer_size value: '1' +CREATE TABLE t1 ( +a INT, +b INT, +c INT NOT NULL, +PRIMARY KEY (a), +KEY `inx_b` (b)) +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION p0 VALUES LESS THAN (1167602410) +(SUBPARTITION sp0, +SUBPARTITION sp1), +PARTITION p1 VALUES LESS THAN MAXVALUE +(SUBPARTITION sp2, +SUBPARTITION sp3)); +CREATE TABLE t2 ( +a INT, +b INT, +c INT NOT NULL, +PRIMARY KEY (a), +KEY `inx_b` (b)); +FLUSH TABLES; +FLUSH STATUS; +SET @a:=1167602400; +CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4; +CREATE VIEW x AS SELECT 1 FROM v,v a,v b; +FLUSH STATUS; +INSERT t1 SELECT @a, @a * (1 - ((@a % 2) * 2)) , 1167612400 - (@a:=@a+1) FROM x, x y; +reads vs requests +reads == requests +writes vs requests +writes == requests +# row distribution: +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' and TABLE_NAME='t1'; +PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS +p0 sp0 5 +p0 sp1 5 +p1 sp2 2043 +p1 sp3 2043 +DROP VIEW x; +DROP VIEW v; +FLUSH TABLES; +FLUSH STATUS; +SELECT COUNT(b) FROM t1 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +No! +INSERT t2 SELECT a,b,c FROM t1; +reads vs requests +reads == requests +writes vs requests +writes == requests +FLUSH STATUS; +SELECT COUNT(b) FROM t2 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +No! +FLUSH TABLES; +# Setting the default key cache to 1M +SET GLOBAL key_buffer_size = 1024*1024; +FLUSH STATUS; +# All these have to read the indexes +LOAD INDEX INTO CACHE t1 PARTITION (p1); +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +Zero key reads? +No! +SELECT COUNT(b) FROM t1 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +No! +SELECT COUNT(b) FROM t2 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +No! +# All these should be able to use the key cache +SELECT COUNT(b) FROM t1 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +Yes! +SELECT COUNT(b) FROM t2 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +Yes! +FLUSH TABLES; +LOAD INDEX INTO CACHE t1 PARTITION (p1,p0); +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +Zero key reads? +No! +# should not be zero +SELECT COUNT(b) FROM t1 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +Yes! +LOAD INDEX INTO CACHE t2; +Table Op Msg_type Msg_text +test.t2 preload_keys status OK +Zero key reads? +No! +# should not be zero +SELECT COUNT(b) FROM t2 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +Yes! +FLUSH TABLES; +LOAD INDEX INTO CACHE t1 PARTITION (p1,p0) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +Zero key reads? +No! +# should not be zero +SELECT COUNT(b) FROM t1 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +No! +LOAD INDEX INTO CACHE t2 IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t2 preload_keys status OK +Zero key reads? +No! +# should not be zero +SELECT COUNT(b) FROM t2 WHERE b >= 0; +COUNT(b) +2048 +Zero key reads? +No! +TRUNCATE TABLE t2; +INSERT t2 SELECT a,b,c FROM t1; +reads vs requests +reads != requests +writes vs requests +writes != requests +DROP TABLE t1,t2; +SET GLOBAL hot_cache.key_buffer_size = 1024*1024; +SET GLOBAL warm_cache.key_buffer_size = 1024*1024; +SET @@global.cold_cache.key_buffer_size = 1024*1024; +SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d; +a b c d +1048576 1024 300 100 +SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d; +a b c d +1048576 1024 300 100 +SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d; +a b c d +1048576 1024 300 100 +SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d; +a b c d +1048576 1024 300 100 +CREATE TABLE t1 ( +a INT, +b VARCHAR(257), +c INT NOT NULL, +PRIMARY KEY (a), +KEY `inx_b` (b), +KEY `inx_c`(c)) +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp0, +SUBPARTITION sp1), +PARTITION p1 VALUES LESS THAN MAXVALUE +(SUBPARTITION sp2, +SUBPARTITION sp3)); +CREATE TABLE t2 ( +a INT, +b VARCHAR(257), +c INT NOT NULL, +PRIMARY KEY (a), +KEY `inx_b` (b), +KEY `inx_c`(c)); +SET @a:=1167602400; +CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4; +CREATE VIEW x AS SELECT 1 FROM v,v a,v b; +INSERT t1 SELECT @a, CONCAT('X_', @a, ' MySQL'), 1167612400 - (@a:=@a+1) FROM x, x a; +DROP VIEW x; +DROP VIEW v; +INSERT t2 SELECT a, b, c FROM t1; +SELECT COUNT(*) FROM t1; +COUNT(*) +4096 +SELECT COUNT(*) FROM t2; +COUNT(*) +4096 +FLUSH TABLES; +# Restrict partitioned commands to partitioned tables only +CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; +ERROR HY000: Partition management on a not partitioned table is not possible +CACHE INDEX t2 PARTITION (p0,`p1`) INDEX (`PRIMARY`) IN hot_cache; +ERROR HY000: Partition management on a not partitioned table is not possible +CACHE INDEX t2 PARTITION (`p1`) INDEX (`PRIMARY`,`inx_b`) IN hot_cache; +ERROR HY000: Partition management on a not partitioned table is not possible +CACHE INDEX t2 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN hot_cache; +ERROR HY000: Partition management on a not partitioned table is not possible +# Basic key cache testing +# The manual correctly says: "The syntax of CACHE INDEX enables you to +# specify that only particular indexes from a table should be assigned +# to the cache. The current implementation assigns all the table's +# indexes to the cache, so there is no reason to specify anything +# other than the table name." +# So the most of the test only tests the syntax +CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +CACHE INDEX t2 INDEX (inx_b,`PRIMARY`) IN default; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN cold_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 PARTITIONS (p0) KEY (`inx_b`) IN cold_cache; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITIONS (p0) KEY (`inx_b`) IN cold_cache' at line 1 +# only one table at a time if specifying partitions +CACHE INDEX t1,t2 PARTITION (p0) KEY (`inx_b`) IN cold_cache; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (p0) KEY (`inx_b`) IN cold_cache' at line 1 +CACHE INDEX t1 PARTITION (`p0`,p1) INDEX (`PRIMARY`) IN warm_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 PARTITION (`p1`) INDEX (`PRIMARY`,inx_b) IN hot_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN default; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 PARTITION (ALL) IN hot_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 INDEX (`inx_b`) IN default; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +CACHE INDEX t1 IN hot_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +# Test of non existent key cache: +CACHE INDEX t1 IN non_existent_key_cache; +ERROR HY000: Unknown key cache 'non_existent_key_cache' +# Basic testing of LOAD INDEX +LOAD INDEX INTO CACHE t2; +Table Op Msg_type Msg_text +test.t2 preload_keys status OK +# PRIMARY and secondary keys have different block sizes +LOAD INDEX INTO CACHE t2 ignore leaves; +Table Op Msg_type Msg_text +test.t2 preload_keys error Indexes use different block sizes +test.t2 preload_keys status Operation failed +# Must have INDEX or KEY before the index list +LOAD INDEX INTO CACHE t2 (`PRIMARY`); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`PRIMARY`)' at line 1 +# Test of IGNORE LEAVES +LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`); +Table Op Msg_type Msg_text +test.t2 preload_keys status OK +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t2 preload_keys error Indexes use different block sizes +test.t2 preload_keys status Operation failed +CACHE INDEX t2 IN warm_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +CACHE INDEX t1 IN cold_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t2 preload_keys error Indexes use different block sizes +test.t2 preload_keys status Operation failed +CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t2 preload_keys error Indexes use different block sizes +test.t2 preload_keys status Operation failed +CACHE INDEX t2 IN warm_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t2 preload_keys error Indexes use different block sizes +test.t2 preload_keys status Operation failed +CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`); +Table Op Msg_type Msg_text +test.t2 preload_keys status OK +CACHE INDEX t2 IN default; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache status OK +LOAD INDEX INTO CACHE t2 IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t2 preload_keys error Indexes use different block sizes +test.t2 preload_keys status Operation failed +LOAD INDEX INTO CACHE t2 PARTITION (p1) INDEX (`PRIMARY`); +ERROR HY000: Partition management on a not partitioned table is not possible +LOAD INDEX INTO CACHE t1, t2; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +test.t2 preload_keys status OK +# only one table at a time if specifying partitions +LOAD INDEX INTO CACHE t1 PARTITION (p0), t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' t2' at line 1 +LOAD INDEX INTO CACHE t1 IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys error Indexes use different block sizes +test.t1 preload_keys error Subpartition sp2 returned error +test.t1 preload_keys status Operation failed +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`); +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys error Indexes use different block sizes +test.t1 preload_keys error Subpartition sp2 returned error +test.t1 preload_keys status Operation failed +LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys error Indexes use different block sizes +test.t1 preload_keys error Subpartition sp2 returned error +test.t1 preload_keys status Operation failed +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys error Indexes use different block sizes +test.t1 preload_keys error Subpartition sp2 returned error +test.t1 preload_keys status Operation failed +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`); +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +LOAD INDEX INTO CACHE t1 PARTITION (p1) INDEX (`PRIMARY`); +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +LOAD INDEX INTO CACHE t1 PARTITION (`p1`,p0) KEY (`PRIMARY`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys error Indexes use different block sizes +test.t1 preload_keys error Subpartition sp2 returned error +test.t1 preload_keys status Operation failed +LOAD INDEX INTO CACHE t1 PARTITION (ALL); +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +LOAD INDEX INTO CACHE t1 PARTITIONS ALL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITIONS ALL' at line 1 +LOAD INDEX INTO CACHE t1 PARTITION (p1,`p0`) IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys error Indexes use different block sizes +test.t1 preload_keys error Subpartition sp2 returned error +test.t1 preload_keys status Operation failed +DROP INDEX `inx_b` on t1; +DROP INDEX `inx_b` on t2; +CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; +ERROR HY000: Partition management on a not partitioned table is not possible +CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; +Table Op Msg_type Msg_text +test.t2 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't2' +test.t2 assign_to_keycache status Operation failed +CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache error Subpartition sp0 returned error +test.t1 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't1' +test.t1 assign_to_keycache status Operation failed +CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache error Subpartition sp0 returned error +test.t1 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't1' +test.t1 assign_to_keycache status Operation failed +DROP TABLE t1,t2; +SET GLOBAL hot_cache.key_buffer_size = 0; +SET GLOBAL warm_cache.key_buffer_size = 0; +SET @@global.cold_cache.key_buffer_size = 0; +SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d; +a b c d +1048576 1024 300 100 +SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d; +a b c d +0 1024 300 100 +SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d; +a b c d +0 1024 300 100 +SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d; +a b c d +0 1024 300 100 +SET @@global.default.key_buffer_size = @org_key_cache_buffer_size; diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index 87cb4fba306..0bd7605a5c8 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -1,6 +1,19 @@ drop table if exists t1, t2; create table t1 (a int) partition by range (a) +subpartition by hash(to_seconds(a)) +(partition p0 values less than (1)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50500 PARTITION BY RANGE (a) +SUBPARTITION BY HASH (to_seconds(a)) +(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM) */ +drop table t1; +create table t1 (a int) +partition by range (a) ( partition p0 values less than (NULL), partition p1 values less than (MAXVALUE)); ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN @@ -30,6 +43,14 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t1 where a < '2007-03-07 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50500 PARTITION BY RANGE (TO_SECONDS(a)) +(PARTITION p0 VALUES LESS THAN (63340531200) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (63342604800) ENGINE = MyISAM) */ drop table t1; create table t1 (a date) partition by range(to_seconds(a)) @@ -53,6 +74,14 @@ select * from t1 where a <= '2005-01-01'; a 2003-12-30 2004-12-31 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50500 PARTITION BY RANGE (to_seconds(a)) +(PARTITION p0 VALUES LESS THAN (63240134400) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (63271756800) ENGINE = MyISAM) */ drop table t1; create table t1 (a datetime) partition by range(to_seconds(a)) @@ -75,6 +104,14 @@ id select_type table partitions type possible_keys key key_len ref rows Extra select * from t1 where a <= '2005-01-01'; a 2004-01-01 11:59:29 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50500 PARTITION BY RANGE (to_seconds(a)) +(PARTITION p0 VALUES LESS THAN (63240177600) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (63271800000) ENGINE = MyISAM) */ drop table t1; create table t1 (a int, b char(20)) partition by range columns(a,b) diff --git a/mysql-test/r/partition_utf8.result b/mysql-test/r/partition_utf8.result index 0fae7bb16b6..339871f1f4a 100644 --- a/mysql-test/r/partition_utf8.result +++ b/mysql-test/r/partition_utf8.result @@ -7,7 +7,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) CHARACTER SET cp1250 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a) +/*!50500 PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN (_cp1250 0x81) ENGINE = MyISAM) */ drop table t1; create table t1 (a varchar(2) character set cp1250) @@ -18,7 +18,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) CHARACTER SET cp1250 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a) +/*!50500 PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN ('€') ENGINE = MyISAM) */ drop table t1; create table t1 (a varchar(1500), b varchar(1570)) @@ -45,7 +45,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY LIST COLUMNS(a) +/*!50500 PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN ('†') ENGINE = MyISAM, PARTITION p1 VALUES IN ('') ENGINE = MyISAM) */ insert into t1 values (''); diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 6c7e83134d7..23ba7e14532 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1917,6 +1917,31 @@ execute stmt using @arg; ? -12345.5432100000 deallocate prepare stmt; +# +# Bug#48508: Crash on prepared statement re-execution. +# +create table t1(b int); +insert into t1 values (0); +create view v1 AS select 1 as a from t1 where b; +prepare stmt from "select * from v1 where a"; +execute stmt; +a +execute stmt; +a +deallocate prepare stmt; +drop table t1; +drop view v1; +create table t1(a bigint); +create table t2(b tinyint); +insert into t2 values (null); +prepare stmt from "select 1 from t1 join t2 on a xor b where b > 1 and a =1"; +execute stmt; +1 +execute stmt; +1 +deallocate prepare stmt; +drop table t1,t2; +# End of 5.0 tests. create procedure proc_1() reset query cache; call proc_1(); @@ -2926,6 +2951,25 @@ execute stmt; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation drop table t1; deallocate prepare stmt; +# +# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 +# +prepare encode from "select encode(?, ?) into @ciphertext"; +prepare decode from "select decode(?, ?) into @plaintext"; +set @str="abc", @key="cba"; +execute encode using @str, @key; +execute decode using @ciphertext, @key; +select @plaintext; +@plaintext +abc +set @str="bcd", @key="dcb"; +execute encode using @str, @key; +execute decode using @ciphertext, @key; +select @plaintext; +@plaintext +bcd +deallocate prepare encode; +deallocate prepare decode; End of 5.1 tests. diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index b67fa9322ee..9cde630e4ed 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -176,21 +176,6 @@ a 1 2 3 -select * from t1 union select sql_cache * from t1; -a -1 -2 -3 -select * from t1 where a IN (select sql_cache a from t1); -a -1 -2 -3 -select * from t1 where a IN (select a from t1 union select sql_cache a from t1); -a -1 -2 -3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 4 @@ -207,41 +192,6 @@ a 1 2 3 -select * from t1 union select sql_no_cache * from t1; -a -1 -2 -3 -select * from t1 where a IN (select sql_no_cache a from t1); -a -1 -2 -3 -select * from t1 where a IN (select a from t1 union select sql_no_cache a from t1); -a -1 -2 -3 -select sql_cache sql_no_cache * from t1; -a -1 -2 -3 -select sql_cache * from t1 union select sql_no_cache * from t1; -a -1 -2 -3 -select sql_cache * from t1 where a IN (select sql_no_cache a from t1); -a -1 -2 -3 -select sql_cache * from t1 where a IN (select a from t1 union select sql_no_cache a from t1); -a -1 -2 -3 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 @@ -1490,12 +1440,6 @@ insert into t1 values ('c'); a drop table t1; set GLOBAL query_cache_size= default; -set GLOBAL query_cache_size=1000000; -create table t1 (a char); -insert into t1 values ('c'); -a -drop table t1; -set GLOBAL query_cache_size= default; SET GLOBAL query_cache_size=64*1024*1024; CREATE TABLE t1 (id INT); CREATE PROCEDURE proc29856(IN theUPC TEXT) @@ -1723,4 +1667,55 @@ SELECT 1 FROM t1 GROUP BY 1 DROP TABLE t1; SET GLOBAL query_cache_size= default; +CREATE TABLE t1( a INT ); +SET @v = ( SELECT SQL_CACHE 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 )' at line 1 +SET @v = ( SELECT SQL_NO_CACHE 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 )' at line 1 +SELECT a FROM t1 WHERE a IN ( SELECT SQL_CACHE a FROM t1 ); +ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list' +SELECT a FROM t1 WHERE a IN ( SELECT SQL_NO_CACHE a FROM t1 ); +ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list' +SELECT ( SELECT SQL_CACHE a FROM t1 ); +ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list' +SELECT ( SELECT SQL_NO_CACHE a FROM t1 ); +ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list' +SELECT SQL_CACHE * FROM t1; +a +SELECT SQL_NO_CACHE * FROM t1; +a +SELECT * FROM t1 UNION SELECT SQL_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_CACHE' +SELECT * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE' +SELECT * FROM t1 WHERE a IN (SELECT SQL_CACHE a FROM t1); +ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list' +SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 UNION SELECT SQL_CACHE a FROM t1); +ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list' +SELECT * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE' +SELECT * FROM t1 WHERE a IN (SELECT SQL_NO_CACHE a FROM t1); +ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list' +SELECT * FROM t1 WHERE a IN +(SELECT a FROM t1 UNION SELECT SQL_NO_CACHE a FROM t1); +ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list' +SELECT SQL_CACHE SQL_NO_CACHE * FROM t1; +ERROR HY000: Incorrect usage of SQL_CACHE and SQL_NO_CACHE +SELECT SQL_NO_CACHE SQL_CACHE * FROM t1; +ERROR HY000: Incorrect usage of SQL_NO_CACHE and SQL_CACHE +SELECT SQL_CACHE * FROM t1 UNION SELECT SQL_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_CACHE' +SELECT SQL_CACHE * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE' +SELECT SQL_NO_CACHE * FROM t1 UNION SELECT SQL_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_CACHE' +SELECT SQL_NO_CACHE * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1; +ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE' +SELECT SQL_CACHE * FROM t1 WHERE a IN +(SELECT SQL_NO_CACHE a FROM t1); +ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list' +SELECT SQL_CACHE * FROM t1 WHERE a IN +(SELECT a FROM t1 UNION SELECT SQL_NO_CACHE a FROM t1); +ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list' +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 3bca3fb5438..ff2dd49e725 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4446,6 +4446,91 @@ SELECT 1 FROM t2 JOIN t1 ON 1=1 WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 1 DROP TABLE t1,t2; +# +# Bug #49199: Optimizer handles incorrectly: +# field='const1' AND field='const2' in some cases + +CREATE TABLE t1(a DATETIME NOT NULL); +INSERT INTO t1 VALUES('2001-01-01'); +SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; +a +2001-01-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '2001-01-01 00:00:00' AS `a` from dual where 1 +DROP TABLE t1; +CREATE TABLE t1(a DATE NOT NULL); +INSERT INTO t1 VALUES('2001-01-01'); +SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; +a +2001-01-01 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '2001-01-01' AS `a` from dual where 1 +DROP TABLE t1; +CREATE TABLE t1(a TIMESTAMP NOT NULL); +INSERT INTO t1 VALUES('2001-01-01'); +SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; +a +2001-01-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '2001-01-01 00:00:00' AS `a` from dual where 1 +DROP TABLE t1; +CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); +INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); +SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; +a b +2001-01-01 00:00:00 2001-01-01 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 +DROP TABLE t1; +CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); +INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); +SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; +a b +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; +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 noticed after reading const tables +Warnings: +Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0 +SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; +a b +2001-01-01 00:00:00 2001-01-01 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 +DROP TABLE t1; +CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); +INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); +SELECT x.a, y.a, z.a FROM t1 x +JOIN t1 y ON x.a=y.a +JOIN t1 z ON y.a=z.a +WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; +a a a +2001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00 +EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x +JOIN t1 y ON x.a=y.a +JOIN t1 z ON y.a=z.a +WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x system NULL NULL NULL NULL 1 100.00 +1 SIMPLE y system NULL NULL NULL NULL 1 100.00 +1 SIMPLE z system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a` from dual where 1 +DROP TABLE t1; End of 5.0 tests create table t1(a INT, KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); @@ -4680,4 +4765,29 @@ HAVING v <= 't' ORDER BY pk; v DROP TABLE t1; +# +# Bug#49489 Uninitialized cache led to a wrong result. +# +CREATE TABLE t1(c1 DOUBLE(5,4)); +INSERT INTO t1 VALUES (9.1234); +SELECT * FROM t1 WHERE c1 < 9.12345; +c1 +9.1234 +DROP TABLE t1; +# End of test for bug#49489. +# +# Bug #49517: Inconsistent behavior while using +# NULLable BIGINT and INT columns in comparison +# +CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); +INSERT INTO t1 VALUES(105, NULL, NULL); +SELECT * FROM t1 WHERE b < 102; +a b c +SELECT * FROM t1 WHERE c < 102; +a b c +SELECT * FROM t1 WHERE 102 < b; +a b c +SELECT * FROM t1 WHERE 102 < c; +a b c +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 50384149a26..c6276f319a1 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -737,20 +737,11 @@ View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` binary binary DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE NOW(); -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 sql_no_cache now() AS `NOW()` binary binary -DROP VIEW v1; +ERROR HY000: Incorrect usage of SQL_CACHE and SQL_NO_CACHE CREATE VIEW v1 AS SELECT SQL_NO_CACHE SQL_CACHE NOW(); -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 sql_no_cache now() AS `NOW()` binary binary -DROP VIEW v1; +ERROR HY000: Incorrect usage of SQL_NO_CACHE and SQL_CACHE CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE SQL_CACHE NOW(); -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 sql_no_cache now() AS `NOW()` binary binary -DROP VIEW v1; +ERROR HY000: Incorrect usage of SQL_CACHE and SQL_NO_CACHE CREATE PROCEDURE p1() BEGIN SET @s= 'CREATE VIEW v1 AS SELECT SQL_CACHE 1'; @@ -1446,4 +1437,10 @@ GRANT PROCESS ON *.* TO test_u@localhost; SHOW ENGINE MYISAM MUTEX; SHOW ENGINE MYISAM STATUS; DROP USER test_u@localhost; +# +# Bug #48985: show create table crashes if previous access to the table +# was killed +# +SHOW CREATE TABLE non_existent; +ERROR 70100: Query execution was interrupted End of 5.1 tests diff --git a/mysql-test/r/sp-ucs2.result b/mysql-test/r/sp-ucs2.result index 3806a84a849..5d4289a60c5 100644 --- a/mysql-test/r/sp-ucs2.result +++ b/mysql-test/r/sp-ucs2.result @@ -120,3 +120,29 @@ DECLARE f2 VARCHAR(64) COLLATE ucs2_unicode_ci; RETURN 'str'; END| ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE' +SET NAMES utf8; +DROP FUNCTION IF EXISTS bug48766; +CREATE FUNCTION bug48766 () +RETURNS ENUM( 'w' ) CHARACTER SET ucs2 +RETURN 0; +SHOW CREATE FUNCTION bug48766; +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug48766 CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('w') CHARSET ucs2 +RETURN 0 utf8 utf8_general_ci latin1_swedish_ci +SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_NAME='bug48766'; +DTD_IDENTIFIER +enum('w') CHARSET ucs2 +DROP FUNCTION bug48766; +CREATE FUNCTION bug48766 () +RETURNS ENUM('а','б','в','г') CHARACTER SET ucs2 +RETURN 0; +SHOW CREATE FUNCTION bug48766; +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug48766 CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('а','б','в','г') CHARSET ucs2 +RETURN 0 utf8 utf8_general_ci latin1_swedish_ci +SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_NAME='bug48766'; +DTD_IDENTIFIER +enum('а','б','в','г') CHARSET ucs2 +DROP FUNCTION bug48766; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index c77912e69cb..ccea954c214 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4419,6 +4419,31 @@ WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) NULL 0 DROP TABLE t1, st1, st2; +# +# Bug #48709: Assertion failed in sql_select.cc:11782: +# int join_read_key(JOIN_TAB*) +# +CREATE TABLE t1 (pk int PRIMARY KEY, int_key int); +INSERT INTO t1 VALUES (10,1), (14,1); +CREATE TABLE t2 (pk int PRIMARY KEY, int_key int); +INSERT INTO t2 VALUES (3,3), (5,NULL), (7,3); +# should have eq_ref for t1 +EXPLAIN +SELECT * FROM t2 outr +WHERE outr.int_key NOT IN (SELECT t1.pk FROM t1, t2) +ORDER BY outr.pk; +id select_type table type possible_keys key key_len ref rows Extra +x x outr ALL x x x x x x +x x t1 eq_ref x x x x x x +x x t2 index x x x x x x +# should not crash on debug binaries +SELECT * FROM t2 outr +WHERE outr.int_key NOT IN (SELECT t1.pk FROM t1, t2) +ORDER BY outr.pk; +pk int_key +3 3 +7 3 +DROP TABLE t1,t2; End of 5.0 tests. CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index e52947455c8..56b326327c6 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -46,3 +46,267 @@ a 2001 drop table t1; End of 5.0 tests +# +# Bug #49480: WHERE using YEAR columns returns unexpected results +# +CREATE TABLE t2(yy YEAR(2), c2 CHAR(4)); +CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4)); +INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069); +INSERT INTO t4 (c4) SELECT c2 FROM t2; +UPDATE t2 SET yy = c2; +UPDATE t4 SET yyyy = c4; +SELECT * FROM t2; +yy c2 +NULL NULL +70 1970 +99 1999 +00 2000 +01 2001 +69 2069 +SELECT * FROM t4; +yyyy c4 +NULL NULL +1970 1970 +1999 1999 +2000 2000 +2001 2001 +2069 2069 +# Comparison of YEAR(2) with YEAR(4) +SELECT * FROM t2, t4 WHERE yy = yyyy; +yy c2 yyyy c4 +70 1970 1970 1970 +99 1999 1999 1999 +00 2000 2000 2000 +01 2001 2001 2001 +69 2069 2069 2069 +SELECT * FROM t2, t4 WHERE yy <=> yyyy; +yy c2 yyyy c4 +NULL NULL NULL NULL +70 1970 1970 1970 +99 1999 1999 1999 +00 2000 2000 2000 +01 2001 2001 2001 +69 2069 2069 2069 +SELECT * FROM t2, t4 WHERE yy < yyyy; +yy c2 yyyy c4 +70 1970 1999 1999 +70 1970 2000 2000 +99 1999 2000 2000 +70 1970 2001 2001 +99 1999 2001 2001 +00 2000 2001 2001 +70 1970 2069 2069 +99 1999 2069 2069 +00 2000 2069 2069 +01 2001 2069 2069 +SELECT * FROM t2, t4 WHERE yy > yyyy; +yy c2 yyyy c4 +99 1999 1970 1970 +00 2000 1970 1970 +01 2001 1970 1970 +69 2069 1970 1970 +00 2000 1999 1999 +01 2001 1999 1999 +69 2069 1999 1999 +01 2001 2000 2000 +69 2069 2000 2000 +69 2069 2001 2001 +# Comparison of YEAR(2) with YEAR(2) +SELECT * FROM t2 a, t2 b WHERE a.yy = b.yy; +yy c2 yy c2 +70 1970 70 1970 +99 1999 99 1999 +00 2000 00 2000 +01 2001 01 2001 +69 2069 69 2069 +SELECT * FROM t2 a, t2 b WHERE a.yy <=> b.yy; +yy c2 yy c2 +NULL NULL NULL NULL +70 1970 70 1970 +99 1999 99 1999 +00 2000 00 2000 +01 2001 01 2001 +69 2069 69 2069 +SELECT * FROM t2 a, t2 b WHERE a.yy < b.yy; +yy c2 yy c2 +70 1970 99 1999 +70 1970 00 2000 +99 1999 00 2000 +70 1970 01 2001 +99 1999 01 2001 +00 2000 01 2001 +70 1970 69 2069 +99 1999 69 2069 +00 2000 69 2069 +01 2001 69 2069 +# Comparison of YEAR(4) with YEAR(4) +SELECT * FROM t4 a, t4 b WHERE a.yyyy = b.yyyy; +yyyy c4 yyyy c4 +1970 1970 1970 1970 +1999 1999 1999 1999 +2000 2000 2000 2000 +2001 2001 2001 2001 +2069 2069 2069 2069 +SELECT * FROM t4 a, t4 b WHERE a.yyyy <=> b.yyyy; +yyyy c4 yyyy c4 +NULL NULL NULL NULL +1970 1970 1970 1970 +1999 1999 1999 1999 +2000 2000 2000 2000 +2001 2001 2001 2001 +2069 2069 2069 2069 +SELECT * FROM t4 a, t4 b WHERE a.yyyy < b.yyyy; +yyyy c4 yyyy c4 +1970 1970 1999 1999 +1970 1970 2000 2000 +1999 1999 2000 2000 +1970 1970 2001 2001 +1999 1999 2001 2001 +2000 2000 2001 2001 +1970 1970 2069 2069 +1999 1999 2069 2069 +2000 2000 2069 2069 +2001 2001 2069 2069 +# Comparison with constants: +SELECT * FROM t2 WHERE yy = NULL; +yy c2 +SELECT * FROM t4 WHERE yyyy = NULL; +yyyy c4 +SELECT * FROM t2 WHERE yy <=> NULL; +yy c2 +NULL NULL +SELECT * FROM t4 WHERE yyyy <=> NULL; +yyyy c4 +NULL NULL +SELECT * FROM t2 WHERE yy < NULL; +yy c2 +SELECT * FROM t2 WHERE yy > NULL; +yy c2 +SELECT * FROM t2 WHERE yy = NOW(); +yy c2 +SELECT * FROM t4 WHERE yyyy = NOW(); +yyyy c4 +SELECT * FROM t2 WHERE yy = 99; +yy c2 +99 1999 +SELECT * FROM t2 WHERE 99 = yy; +yy c2 +99 1999 +SELECT * FROM t4 WHERE yyyy = 99; +yyyy c4 +1999 1999 +SELECT * FROM t2 WHERE yy = 'test'; +yy c2 +00 2000 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'test' +SELECT * FROM t4 WHERE yyyy = 'test'; +yyyy c4 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'test' +SELECT * FROM t2 WHERE yy = '1999'; +yy c2 +99 1999 +SELECT * FROM t4 WHERE yyyy = '1999'; +yyyy c4 +1999 1999 +SELECT * FROM t2 WHERE yy = 1999; +yy c2 +99 1999 +SELECT * FROM t4 WHERE yyyy = 1999; +yyyy c4 +1999 1999 +SELECT * FROM t2 WHERE yy = 1999.1; +yy c2 +99 1999 +SELECT * FROM t4 WHERE yyyy = 1999.1; +yyyy c4 +1999 1999 +SELECT * FROM t2 WHERE yy = 1998.9; +yy c2 +99 1999 +SELECT * FROM t4 WHERE yyyy = 1998.9; +yyyy c4 +1999 1999 +# Coverage tests for YEAR with zero/2000 constants: +SELECT * FROM t2 WHERE yy = 0; +yy c2 +00 2000 +SELECT * FROM t2 WHERE yy = '0'; +yy c2 +00 2000 +SELECT * FROM t2 WHERE yy = '0000'; +yy c2 +00 2000 +SELECT * FROM t2 WHERE yy = '2000'; +yy c2 +00 2000 +SELECT * FROM t2 WHERE yy = 2000; +yy c2 +00 2000 +SELECT * FROM t4 WHERE yyyy = 0; +yyyy c4 +SELECT * FROM t4 WHERE yyyy = '0'; +yyyy c4 +2000 2000 +SELECT * FROM t4 WHERE yyyy = '0000'; +yyyy c4 +SELECT * FROM t4 WHERE yyyy = '2000'; +yyyy c4 +2000 2000 +SELECT * FROM t4 WHERE yyyy = 2000; +yyyy c4 +2000 2000 +# Comparison with constants those are out of YEAR range +# (coverage test for backward compatibility) +SELECT COUNT(yy) FROM t2; +COUNT(yy) +5 +SELECT COUNT(yyyy) FROM t4; +COUNT(yyyy) +5 +SELECT COUNT(*) FROM t2 WHERE yy = -1; +COUNT(*) +0 +SELECT COUNT(*) FROM t4 WHERE yyyy > -1; +COUNT(*) +5 +SELECT COUNT(*) FROM t2 WHERE yy > -1000000000000000000; +COUNT(*) +5 +SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000; +COUNT(*) +5 +SELECT COUNT(*) FROM t2 WHERE yy < 2156; +COUNT(*) +5 +SELECT COUNT(*) FROM t4 WHERE yyyy < 2156; +COUNT(*) +5 +SELECT COUNT(*) FROM t2 WHERE yy < 1000000000000000000; +COUNT(*) +5 +SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000; +COUNT(*) +5 +SELECT * FROM t2 WHERE yy < 123; +yy c2 +70 1970 +99 1999 +00 2000 +01 2001 +69 2069 +SELECT * FROM t2 WHERE yy > 123; +yy c2 +SELECT * FROM t4 WHERE yyyy < 123; +yyyy c4 +SELECT * FROM t4 WHERE yyyy > 123; +yyyy c4 +1970 1970 +1999 1999 +2000 2000 +2001 2001 +2069 2069 +DROP TABLE t2, t4; +# +End of 5.1 tests diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 0a999fb18f3..f98872d93b3 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1018,6 +1018,12 @@ ERROR HY000: Variable 'hostname' is a read only variable show variables like 'hostname'; Variable_name Value hostname # +# +# BUG#37408 - Compressed MyISAM files should not require/use mmap() +# +# Test 'myisam_mmap_size' option is not dynamic +SET @@myisam_mmap_size= 500M; +ERROR HY000: Variable 'myisam_mmap_size' is a read only variable End of 5.0 tests set join_buffer_size=1; Warnings: |