diff options
Diffstat (limited to 'mysql-test/r')
39 files changed, 857 insertions, 192 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index d6986f4a956..f0edfc9bb59 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1222,4 +1222,34 @@ ALTER TABLE t1 CHANGE d c varchar(10); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 DROP TABLE t1; +create table t1(f1 int not null, f2 int not null, key (f1), key (f2)); +select index_length into @unpaked_keys_size from +information_schema.tables where table_name='t1'; +alter table t1 pack_keys=1; +select index_length into @paked_keys_size from +information_schema.tables where table_name='t1'; +select (@unpaked_keys_size > @paked_keys_size); +(@unpaked_keys_size > @paked_keys_size) +1 +select max_data_length into @orig_max_data_length from +information_schema.tables where table_name='t1'; +alter table t1 max_rows=100; +select max_data_length into @changed_max_data_length from +information_schema.tables where table_name='t1'; +select (@orig_max_data_length > @changed_max_data_length); +(@orig_max_data_length > @changed_max_data_length) +1 +drop table t1; +CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, +b ENUM('a', 'b', 'c') NOT NULL); +INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a'); +ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; +SELECT * FROM t1; +a b +1 a +2 c +3 b +4 b +5 a +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/binlog_format_basic.result b/mysql-test/r/binlog_format_basic.result index f9b1c2c6e09..0d76d9686e3 100644 --- a/mysql-test/r/binlog_format_basic.result +++ b/mysql-test/r/binlog_format_basic.result @@ -1,3 +1,6 @@ +SELECT @@GLOBAL.binlog_format; +@@GLOBAL.binlog_format +STATEMENT '#---------------------BS_STVARS_002_01----------------------#' SET @start_value= @@global.binlog_format; SELECT COUNT(@@GLOBAL.binlog_format); diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 11e9109439b..ba4645b827e 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -59,7 +59,8 @@ begin select get_lock('test_bug16407', 60); end| "Now if everything is fine the event has compiled and is locked" -select /*1*/ user, host, db, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*1*/ user, host, db, info from information_schema.processlist +where info = 'select get_lock(\'test_bug16407\', 60)'; user host db info root localhost events_test select get_lock('test_bug16407', 60) select release_lock('test_bug16407'); @@ -114,18 +115,18 @@ event_schema event_name sql_mode events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_4 -select /*2*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +select /*2*/ user, host, db, info from information_schema.processlist +where info = 'select get_lock(\'ee_16407_2\', 60)'; user host db info -event_scheduler localhost NULL NULL root localhost events_test select get_lock('ee_16407_2', 60) root localhost events_test select get_lock('ee_16407_2', 60) root localhost events_test select get_lock('ee_16407_2', 60) select release_lock('ee_16407_2'); release_lock('ee_16407_2') 1 -select /*3*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +select /*3*/ user, host, db, info from information_schema.processlist +where info = 'select get_lock(\'ee_16407_2\', 60)'; user host db info -event_scheduler localhost NULL NULL set global event_scheduler= off; select * from events_smode_test order by ev_name, a; ev_name a @@ -164,7 +165,9 @@ select release_lock('ee_16407_5'); call events_test.ee_16407_6_pendant(); end| "Should have 2 locked processes" -select /*4*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +select /*4*/ user, host, db, info from information_schema.processlist +where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') +order by info; user host db info event_scheduler localhost NULL NULL root localhost events_test select get_lock('ee_16407_5', 60) @@ -173,7 +176,9 @@ select release_lock('ee_16407_5'); release_lock('ee_16407_5') 1 "Should have 0 processes locked" -select /*5*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +select /*5*/ user, host, db, info from information_schema.processlist +where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') +order by info; user host db info event_scheduler localhost NULL NULL select * from events_smode_test order by ev_name, a; @@ -566,10 +571,9 @@ SHOW GRANTS FOR CURRENT_USER; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION SET GLOBAL event_scheduler = ON; -CREATE TABLE event_log (id int KEY AUTO_INCREMENT, -ev_nm char(40), ev_cnt int, -ev_tm timestamp) ENGINE=MyISAM; -SET @ev_base_date = 20281224180000; +CREATE TABLE events_test.event_log +(id int KEY AUTO_INCREMENT, ev_nm char(40), ev_cnt int, ev_tm timestamp) +ENGINE=MyISAM; SET autocommit=0; CREATE USER evtest1@localhost; SET PASSWORD FOR evtest1@localhost = password('ev1'); @@ -582,41 +586,40 @@ GRANT USAGE ON *.* TO 'evtest1'@'localhost' IDENTIFIED BY PASSWORD '*3170F3644E3 GRANT SELECT, INSERT ON `test`.* TO 'evtest1'@'localhost' GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO 'evtest1'@'localhost' connection e1; -USE events_test; CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND DO BEGIN SET AUTOCOMMIT = 0; SET @evname = 'ev_sched_1823'; SET @cnt = 0; -SELECT COUNT(*) INTO @cnt FROM test.event_log WHERE ev_nm = @evname; +SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; IF @cnt < 6 THEN -INSERT INTO test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); +INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); COMMIT; END IF; -SELECT COUNT(*) INTO @cnt FROM test.event_log WHERE ev_nm = @evname; +SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; IF @cnt < 6 THEN -INSERT INTO test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); +INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); ROLLBACK; END IF; END;| +Sleep till the first INSERT into events_test.event_log occured +SELECT COUNT(*) > 0 AS "Expect 1" FROM events_test.event_log; +Expect 1 +1 connection default; -DROP EVENT ev_sched_1823; DROP USER evtest1@localhost; -USE test; -===================================================================================== -select id,ev_nm,ev_cnt from event_log order by id; -id ev_nm ev_cnt -1 ev_sched_1823 1 -2 ev_sched_1823 2 -3 ev_sched_1823 3 -4 ev_sched_1823 4 -5 ev_sched_1823 5 -6 ev_sched_1823 6 -DROP TABLE event_log; +Sleep 4 seconds +SELECT COUNT(*) INTO @row_cnt FROM events_test.event_log; +Sleep 4 seconds +SELECT COUNT(*) > @row_cnt AS "Expect 0" FROM events_test.event_log; +Expect 0 +0 +DROP EVENT events_test.ev_sched_1823; +DROP TABLE events_test.event_log; SET GLOBAL event_scheduler = OFF; SET GLOBAL event_scheduler= ON; CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00' - DO BEGIN +DO BEGIN SELECT 1; END;| SET GLOBAL event_scheduler= OFF; diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 2ce8b8c384f..3de741b5efb 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -107,3 +107,78 @@ X X X X X X X X X X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF) DROP TABLE t2; DROP TABLE t1; +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN EXTENDED SELECT 1 +FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +EXPLAIN EXTENDED SELECT 1 +FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +prepare s1 from +'EXPLAIN EXTENDED SELECT 1 + FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1'; +execute s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +prepare s1 from +'EXPLAIN EXTENDED SELECT 1 + FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1'; +execute s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +execute s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +DROP TABLE t1,t2; +# +# Bug#37870: Usage of uninitialized value caused failed assertion. +# +create table t1 (dt datetime not null, t time not null); +create table t2 (dt datetime not null); +insert into t1 values ('2001-01-01 1:1:1', '1:1:1'), +('2001-01-01 1:1:1', '1:1:1'); +insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); +flush tables; +EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where +flush tables; +SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); +dt +flush tables; +EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where +flush tables; +SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); +dt +2001-01-01 01:01:01 +2001-01-01 01:01:01 +drop tables t1, t2; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 062efea839f..48f97aeb428 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1274,6 +1274,36 @@ tt 41 SET sql_mode=@save_sql_mode; DROP TABLE t1, t2; +# +# BUG#38072: Wrong result: HAVING not observed in a query with aggregate +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +int_key int(11) NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY int_key (int_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,5,5, 'h','h'), +(2,1,1, '{','{'), +(3,1,1, 'z','z'), +(4,8,8, 'x','x'), +(5,7,7, 'o','o'), +(6,3,3, 'p','p'), +(7,9,9, 'c','c'), +(8,0,0, 'k','k'), +(9,6,6, 't','t'), +(10,0,0,'c','c'); +explain SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar'; +X +drop table t1; End of 5.0 tests CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 980e539534a..288d86e2db5 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -766,7 +766,6 @@ table_schema table_name column_name information_schema COLUMNS COLUMN_DEFAULT information_schema COLUMNS COLUMN_TYPE information_schema EVENTS EVENT_DEFINITION -information_schema EVENTS SQL_MODE information_schema PARTITIONS PARTITION_EXPRESSION information_schema PARTITIONS SUBPARTITION_EXPRESSION information_schema PARTITIONS PARTITION_DESCRIPTION @@ -776,8 +775,6 @@ information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT -information_schema TRIGGERS SQL_MODE -information_schema TRIGGERS DEFINER information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; @@ -1654,4 +1651,13 @@ drop table t1; drop function f1; select * from information_schema.tables where 1=sleep(100000); select * from information_schema.columns where 1=sleep(100000); +explain select count(*) from information_schema.tables; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +explain select count(*) from information_schema.columns; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE columns ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases +explain select count(*) from information_schema.views; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE views ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases End of 5.1 tests. diff --git a/mysql-test/r/information_schema_part.result b/mysql-test/r/information_schema_part.result index 8455c8e014e..74d52e9521f 100644 --- a/mysql-test/r/information_schema_part.result +++ b/mysql-test/r/information_schema_part.result @@ -119,7 +119,10 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY LINEAR HASH (a) (PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY LINEAR HASH (a) +(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM) */ select SUBPARTITION_METHOD FROM information_schema.partitions WHERE table_schema="test" AND table_name="t1"; SUBPARTITION_METHOD @@ -134,7 +137,9 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION p0 VALUES IN (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) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (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) ENGINE = MyISAM) */ SELECT PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_schema = "test" AND table_name = "t1"; PARTITION_DESCRIPTION diff --git a/mysql-test/r/innodb_data_home_dir_basic.result b/mysql-test/r/innodb_data_home_dir_basic.result index fb9a0b0bca5..e4bdd79b7c3 100644 --- a/mysql-test/r/innodb_data_home_dir_basic.result +++ b/mysql-test/r/innodb_data_home_dir_basic.result @@ -1,7 +1,7 @@ '#---------------------BS_STVARS_025_01----------------------#' SELECT COUNT(@@GLOBAL.innodb_data_home_dir); COUNT(@@GLOBAL.innodb_data_home_dir) -0 +1 1 Expected '#---------------------BS_STVARS_025_02----------------------#' SET @@GLOBAL.innodb_data_home_dir=1; @@ -9,7 +9,7 @@ ERROR HY000: Variable 'innodb_data_home_dir' is a read only variable Expected error 'Read only variable' SELECT COUNT(@@GLOBAL.innodb_data_home_dir); COUNT(@@GLOBAL.innodb_data_home_dir) -0 +1 1 Expected '#---------------------BS_STVARS_025_03----------------------#' SELECT @@GLOBAL.innodb_data_home_dir = VARIABLE_VALUE @@ -20,7 +20,7 @@ NULL 1 Expected SELECT COUNT(@@GLOBAL.innodb_data_home_dir); COUNT(@@GLOBAL.innodb_data_home_dir) -0 +1 1 Expected SELECT COUNT(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -36,7 +36,7 @@ NULL '#---------------------BS_STVARS_025_05----------------------#' SELECT COUNT(@@innodb_data_home_dir); COUNT(@@innodb_data_home_dir) -0 +1 1 Expected SELECT COUNT(@@local.innodb_data_home_dir); ERROR HY000: Variable 'innodb_data_home_dir' is a GLOBAL variable @@ -46,7 +46,7 @@ ERROR HY000: Variable 'innodb_data_home_dir' is a GLOBAL variable Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@GLOBAL.innodb_data_home_dir); COUNT(@@GLOBAL.innodb_data_home_dir) -0 +1 1 Expected SELECT innodb_data_home_dir = @@SESSION.innodb_data_home_dir; ERROR 42S22: Unknown column 'innodb_data_home_dir' in 'field list' diff --git a/mysql-test/r/innodb_flush_method_basic.result b/mysql-test/r/innodb_flush_method_basic.result index 836328c5c9b..8c8924cdd86 100644 --- a/mysql-test/r/innodb_flush_method_basic.result +++ b/mysql-test/r/innodb_flush_method_basic.result @@ -1,7 +1,7 @@ '#---------------------BS_STVARS_029_01----------------------#' SELECT COUNT(@@GLOBAL.innodb_flush_method); COUNT(@@GLOBAL.innodb_flush_method) -0 +1 1 Expected '#---------------------BS_STVARS_029_02----------------------#' SET @@GLOBAL.innodb_flush_method=1; @@ -9,7 +9,7 @@ ERROR HY000: Variable 'innodb_flush_method' is a read only variable Expected error 'Read only variable' SELECT COUNT(@@GLOBAL.innodb_flush_method); COUNT(@@GLOBAL.innodb_flush_method) -0 +1 1 Expected '#---------------------BS_STVARS_029_03----------------------#' SELECT @@GLOBAL.innodb_flush_method = VARIABLE_VALUE @@ -20,7 +20,7 @@ NULL 1 Expected SELECT COUNT(@@GLOBAL.innodb_flush_method); COUNT(@@GLOBAL.innodb_flush_method) -0 +1 1 Expected SELECT COUNT(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -36,7 +36,7 @@ NULL '#---------------------BS_STVARS_029_05----------------------#' SELECT COUNT(@@innodb_flush_method); COUNT(@@innodb_flush_method) -0 +1 1 Expected SELECT COUNT(@@local.innodb_flush_method); ERROR HY000: Variable 'innodb_flush_method' is a GLOBAL variable @@ -46,7 +46,7 @@ ERROR HY000: Variable 'innodb_flush_method' is a GLOBAL variable Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@GLOBAL.innodb_flush_method); COUNT(@@GLOBAL.innodb_flush_method) -0 +1 1 Expected SELECT innodb_flush_method = @@SESSION.innodb_flush_method; ERROR 42S22: Unknown column 'innodb_flush_method' in 'field list' diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 985f4d2b464..2c14b1f2385 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1668,3 +1668,12 @@ explain select a from t2 where a=b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index drop table t1, t2; +SET SESSION BINLOG_FORMAT=STATEMENT; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; +@@session.sql_log_bin 1 +@@session.binlog_format STATEMENT +@@session.tx_isolation READ-COMMITTED +CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +DROP TABLE t1; diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result index 2acf74162a4..caed588acdb 100644 --- a/mysql-test/r/limit.result +++ b/mysql-test/r/limit.result @@ -111,3 +111,6 @@ set @a=-14632475938453979136; execute s using @a, @a; ERROR HY000: Incorrect arguments to EXECUTE End of 5.0 tests +select 1 as a limit 4294967296,10; +a +End of 5.1 tests diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index be78c6cdbeb..28b245a0263 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -1,6 +1,11 @@ -SET @old_general_log_state = @@global.general_log; -SET @old_slow_log_state = @@global.slow_query_log; use mysql; +SET @saved_long_query_time = @@long_query_time; +SET @saved_log_output = @@log_output; +SET @saved_general_log = @@GLOBAL.general_log; +SET @saved_slow_query_log = @@GLOBAL.slow_query_log; +SELECT @saved_long_query_time, @saved_log_output, @saved_general_log, @saved_slow_query_log; +@saved_long_query_time @saved_log_output @saved_general_log @saved_slow_query_log +10 FILE,TABLE 1 1 truncate table general_log; select * from general_log; event_time user_host thread_id server_id command_type argument @@ -138,6 +143,7 @@ sleep(2) select * from mysql.slow_log; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 mysql 0 0 1 select sleep(2) +set @@session.long_query_time = @saved_long_query_time; alter table mysql.general_log engine=myisam; ERROR HY000: You cannot 'ALTER' a log table if logging is enabled alter table mysql.slow_log engine=myisam; @@ -215,6 +221,7 @@ TIMESTAMP USER_HOST THREAD_ID 1 Query truncate table mysql.slow_log TIMESTAMP USER_HOST THREAD_ID 1 Query set session long_query_time=1 TIMESTAMP USER_HOST THREAD_ID 1 Query select sleep(2) TIMESTAMP USER_HOST THREAD_ID 1 Query select * from mysql.slow_log +TIMESTAMP USER_HOST THREAD_ID 1 Query set @@session.long_query_time = @saved_long_query_time TIMESTAMP USER_HOST THREAD_ID 1 Query alter table mysql.general_log engine=myisam TIMESTAMP USER_HOST THREAD_ID 1 Query alter table mysql.slow_log engine=myisam TIMESTAMP USER_HOST THREAD_ID 1 Query drop table mysql.general_log @@ -283,6 +290,7 @@ use mysql; lock tables general_log read local, help_category read local; ERROR HY000: You can't use locks with log tables. unlock tables; +SET SESSION long_query_time = 1000; drop table if exists mysql.renamed_general_log; drop table if exists mysql.renamed_slow_log; drop table if exists mysql.general_log_new; @@ -332,6 +340,7 @@ set global slow_query_log='ON'; ERROR 42S02: Table 'mysql.slow_log' doesn't exist RENAME TABLE general_log2 TO general_log; RENAME TABLE slow_log2 TO slow_log; +SET SESSION long_query_time = @saved_long_query_time; set global general_log='ON'; set global slow_query_log='ON'; flush logs; @@ -356,7 +365,7 @@ slow_log slow_log_new drop table slow_log_new, general_log_new; use test; -SET @my_log_output= @@global.log_output; +SET @saved_log_output= @@global.log_output; SET GLOBAL LOG_OUTPUT = 'TABLE'; SET GLOBAL general_log = 0; FLUSH LOGS; @@ -393,7 +402,6 @@ SET GLOBAL general_log = 0; FLUSH LOGS; ALTER TABLE mysql.general_log DROP COLUMN seq; ALTER TABLE mysql.general_log ENGINE = CSV; -SET @old_long_query_time:=@@long_query_time; SET GLOBAL slow_query_log = 0; FLUSH LOGS; TRUNCATE TABLE mysql.slow_log; @@ -421,12 +429,12 @@ START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow quer START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 SET GLOBAL slow_query_log = 0; -SET SESSION long_query_time =@old_long_query_time; +SET SESSION long_query_time =@saved_long_query_time; FLUSH LOGS; ALTER TABLE mysql.slow_log DROP COLUMN seq; ALTER TABLE mysql.slow_log ENGINE = CSV; -SET GLOBAL general_log = @old_general_log_state; -SET GLOBAL slow_query_log = @old_slow_log_state; +SET GLOBAL general_log = @saved_general_log; +SET GLOBAL slow_query_log = @saved_slow_query_log; drop procedure if exists proc25422_truncate_slow; drop procedure if exists proc25422_truncate_general; drop procedure if exists proc25422_alter_slow; @@ -598,8 +606,6 @@ UNTIL done END REPEAT; CLOSE cur1; TRUNCATE mysql.general_log; END // -SET @old_general_log_state = @@global.general_log; -SET @old_slow_log_state = @@global.slow_query_log; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; select "put something into general_log"; @@ -619,11 +625,10 @@ DROP TABLE `db_17876.general_log_data`; DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`; DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`; DROP DATABASE IF EXISTS `db_17876`; -SET GLOBAL general_log = @old_general_log_state; -SET GLOBAL slow_query_log = @old_slow_log_state; +SET GLOBAL general_log = @saved_general_log; +SET GLOBAL slow_query_log = @saved_slow_query_log; select CONNECTION_ID() into @thread_id; truncate table mysql.general_log; -set @old_general_log_state = @@global.general_log; set global general_log = on; set @lparam = "000 001 002 003 004 005 006 007 008 009" "010 011 012 013 014 015 016 017 018 019" @@ -730,7 +735,6 @@ execute long_query using @lparam; set global general_log = off; select command_type, argument from mysql.general_log where thread_id = @thread_id; command_type argument -Query set @old_general_log_state = @@global.general_log Query set global general_log = on Query set @lparam = "000 001 002 003 004 005 006 007 008 009" "010 011 012 013 014 015 016 017 018 019" @@ -838,13 +842,11 @@ Query execute long_query using @lparam Execute select '000 001 002 003 004 005 006 007 008 009010 011 012 013 014 015 016 017 018 019020 021 022 023 024 025 026 027 028 029030 031 032 033 034 035 036 037 038 039040 041 042 043 044 045 046 047 048 049050 051 052 053 054 055 056 057 058 059060 061 062 063 064 065 066 067 068 069070 071 072 073 074 075 076 077 078 079080 081 082 083 084 085 086 087 088 089090 091 092 093 094 095 096 097 098 099100 101 102 103 104 105 106 107 108 109110 111 112 113 114 115 116 117 118 119120 121 122 123 124 125 126 127 128 129130 131 132 133 134 135 136 137 138 139140 141 142 143 144 145 146 147 148 149150 151 152 153 154 155 156 157 158 159160 161 162 163 164 165 166 167 168 169170 171 172 173 174 175 176 177 178 179180 181 182 183 184 185 186 187 188 189190 191 192 193 194 195 196 197 198 199200 201 202 203 204 205 206 207 208 209210 211 212 213 214 215 216 217 218 219220 221 222 223 224 225 226 227 228 229230 231 232 233 234 235 236 237 238 239240 241 242 243 244 245 246 247 248 249250 251 252 253 254 255 256 257 258 259260 261 262 263 264 265 266 267 268 269270 271 272 273 274 275 276 277 278 279280 281 282 283 284 285 286 287 288 289290 291 292 293 294 295 296 297 298 299300 301 302 303 304 305 306 307 308 309310 311 312 313 314 315 316 317 318 319320 321 322 323 324 325 326 327 328 329330 331 332 333 334 335 336 337 338 339340 341 342 343 344 345 346 347 348 349350 351 352 353 354 355 356 357 358 359360 361 362 363 364 365 366 367 368 369370 371 372 373 374 375 376 377 378 379380 381 382 383 384 385 386 387 388 389390 391 392 393 394 395 396 397 398 399400 401 402 403 404 405 406 407 408 409410 411 412 413 414 415 416 417 418 419420 421 422 423 424 425 426 427 428 429430 431 432 433 434 435 436 437 438 439440 441 442 443 444 445 446 447 448 449450 451 452 453 454 455 456 457 458 459460 461 462 463 464 465 466 467 468 469470 471 472 473 474 475 476 477 478 479480 481 482 483 484 485 486 487 488 489490 491 492 493 494 495 496 497 498 499500 501 502 503 504 505 506 507 508 509510 511 512 513 514 515 516 517 518 519520 521 522 523 524 525 526 527 528 529530 531 532 533 534 535 536 537 538 539540 541 542 543 544 545 546 547 548 549550 551 552 553 554 555 556 557 558 559560 561 562 563 564 565 566 567 568 569570 571 572 573 574 575 576 577 578 579580 581 582 583 584 585 586 587 588 589590 591 592 593 594 595 596 597 598 599600 601 602 603 604 605 606 607 608 609610 611 612 613 614 615 616 617 618 619620 621 622 623 624 625 626 627 628 629630 631 632 633 634 635 636 637 638 639640 641 642 643 644 645 646 647 648 649650 651 652 653 654 655 656 657 658 659660 661 662 663 664 665 666 667 668 669670 671 672 673 674 675 676 677 678 679680 681 682 683 684 685 686 687 688 689690 691 692 693 694 695 696 697 698 699700 701 702 703 704 705 706 707 708 709710 711 712 713 714 715 716 717 718 719720 721 722 723 724 725 726 727 728 729730 731 732 733 734 735 736 737 738 739740 741 742 743 744 745 746 747 748 749750 751 752 753 754 755 756 757 758 759760 761 762 763 764 765 766 767 768 769770 771 772 773 774 775 776 777 778 779780 781 782 783 784 785 786 787 788 789790 791 792 793 794 795 796 797 798 799800 801 802 803 804 805 806 807 808 809810 811 812 813 814 815 816 817 818 819820 821 822 823 824 825 826 827 828 829830 831 832 833 834 835 836 837 838 839840 841 842 843 844 845 846 847 848 849850 851 852 853 854 855 856 857 858 859860 861 862 863 864 865 866 867 868 869870 871 872 873 874 875 876 877 878 879880 881 882 883 884 885 886 887 888 889890 891 892 893 894 895 896 897 898 899900 901 902 903 904 905 906 907 908 909910 911 912 913 914 915 916 917 918 919920 921 922 923 924 925 926 927 928 929930 931 932 933 934 935 936 937 938 939940 941 942 943 944 945 946 947 948 949950 951 952 953 954 955 956 957 958 959960 961 962 963 964 965 966 967 968 969970 971 972 973 974 975 976 977 978 979980 981 982 983 984 985 986 987 988 989990 991 992 993 994 995 996 997 998 999' as long_query Query set global general_log = off deallocate prepare long_query; -set global general_log = @old_general_log_state; +set global general_log = @saved_general_log; DROP TABLE IF EXISTS log_count; DROP TABLE IF EXISTS slow_log_copy; DROP TABLE IF EXISTS general_log_copy; CREATE TABLE log_count (count BIGINT(21)); -SET @old_general_log_state = @@global.general_log; -SET @old_slow_log_state = @@global.slow_query_log; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log; @@ -865,10 +867,9 @@ CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; INSERT INTO general_log_copy SELECT * FROM mysql.general_log; INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); DROP TABLE general_log_copy; -SET GLOBAL general_log = @old_general_log_state; -SET GLOBAL slow_query_log = @old_slow_log_state; +SET GLOBAL general_log = @saved_general_log; +SET GLOBAL slow_query_log = @saved_slow_query_log; DROP TABLE log_count; -SET @old_slow_log_state = @@global.slow_query_log; SET SESSION long_query_time = 0; SET GLOBAL slow_query_log = ON; FLUSH LOGS; @@ -894,6 +895,8 @@ TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3,SLEEP(1.1) FROM t1 TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f1=2 DROP TABLE t1; TRUNCATE TABLE mysql.slow_log; -SET GLOBAL log_output= @my_log_output; -SET GLOBAL slow_query_log = @old_slow_log_state; -SET SESSION long_query_time =@old_long_query_time; +SET GLOBAL log_output= @saved_log_output; +SET GLOBAL slow_query_log = @saved_slow_query_log; +SET GLOBAL general_log=@saved_general_log; +SET SESSION long_query_time =@saved_long_query_time; +SET GLOBAL LOG_OUTPUT = @saved_log_output; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index ef623b343e9..cc05efded02 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -830,7 +830,7 @@ ERROR HY000: Unable to open underlying table which is differently defined or of DROP TABLE t1, t2; CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3); SELECT * FROM t2; -ERROR 42S02: Table 'test.t3' doesn't exist +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t2; CREATE TABLE t1(a INT, b TEXT); CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1); @@ -895,17 +895,19 @@ drop table t2; drop table t1; CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2); SELECT * FROM tm1; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist CHECK TABLE tm1; Table Op Msg_type Msg_text test.tm1 check Error Table 'test.t1' doesn't exist +test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.tm1 check error Corrupt CREATE TABLE t1(a INT); SELECT * FROM tm1; -ERROR 42S02: Table 'test.t2' doesn't exist +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist CHECK TABLE tm1; Table Op Msg_type Msg_text test.tm1 check Error Table 'test.t2' doesn't exist +test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.tm1 check error Corrupt CREATE TABLE t2(a BLOB); SELECT * FROM tm1; @@ -1199,7 +1201,7 @@ c1 3 RENAME TABLE t2 TO t5; SELECT * FROM t3 ORDER BY c1; -ERROR 42S02: Table 'test.t2' doesn't exist +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist RENAME TABLE t5 TO t2; SELECT * FROM t3 ORDER BY c1; c1 @@ -1233,7 +1235,7 @@ UNLOCK TABLES; # 4. Alter table rename. ALTER TABLE t2 RENAME TO t5; SELECT * FROM t3 ORDER BY c1; -ERROR 42S02: Table 'test.t2' doesn't exist +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist ALTER TABLE t5 RENAME TO t2; SELECT * FROM t3 ORDER BY c1; c1 @@ -1317,7 +1319,7 @@ LOCK TABLES t1 WRITE, t2 WRITE; INSERT INTO t1 VALUES (1); DROP TABLE t1; SELECT * FROM t2; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist SELECT * FROM t1; ERROR 42S02: Table 'test.t1' doesn't exist UNLOCK TABLES; @@ -2006,6 +2008,13 @@ test.t1 optimize status OK FLUSH TABLES m1, t1; UNLOCK TABLES; DROP TABLE t1, m1; +CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST; +SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE +TABLE_SCHEMA = 'test' and TABLE_NAME='tm1'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT +NULL test tm1 BASE TABLE NULL NULL NULL # # # # # # # # # # NULL # # Unable to open underlying table which is differently defined or of non-MyISAM ty +DROP TABLE tm1; +End of 5.1 tests CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 4d5a170a55c..6b498e55d85 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -181,4 +181,21 @@ c1 c2 3 3 DROP VIEW v1,v2; DROP TABLE t1,t2; +CREATE TABLE t1 (i INT, d DATE); +INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03'); +SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d), +CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d) +FROM t1 ORDER BY RAND(); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +2008-01-01 2008-01-01 2008-01-01 2008-01-01 2008-01-01 2008-01-01 +2008-01-02 2008-01-02 2008-01-02 2008-01-02 2008-01-02 2008-01-02 +2008-01-03 2008-01-03 2008-01-03 2008-01-03 2008-01-03 2008-01-03 +COALESCE(d, d) IFNULL(d, d) IF(i, d, d) CASE i WHEN i THEN d ELSE d END GREATEST(d, d) LEAST(d, d) +def CASE i WHEN i THEN d ELSE d END CASE i WHEN i THEN d ELSE d END 10 10 10 Y 128 0 63 +def COALESCE(d, d) COALESCE(d, d) 10 10 10 Y 128 0 63 +def GREATEST(d, d) GREATEST(d, d) 10 10 10 Y 128 0 63 +def IF(i, d, d) IF(i, d, d) 10 10 10 Y 128 0 63 +def IFNULL(d, d) IFNULL(d, d) 10 10 10 Y 128 0 63 +def LEAST(d, d) LEAST(d, d) 10 10 10 Y 128 0 63 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index c13624d8d09..be14e282f2a 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -97,7 +97,7 @@ mysql.time_zone_transition_type OK mysql.user OK DROP USER mysqltest1@'%'; Run mysql_upgrade with a non existing server socket -mysqlcheck: Got error: 2003: Can't connect to MySQL server on 'not_existing_host' (errno) when trying to connect +mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect FATAL ERROR: Upgrade failed set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; mtr.global_suppressions OK diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index e70ce75797d..dc29c5ec226 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1428,6 +1428,22 @@ set session max_sort_length= 2180; select * from t1 order by b; ERROR HY001: Out of sort memory; increase server sort buffer size drop table t1; +# +# Bug #39844: Query Crash Mysql Server 5.0.67 +# +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY, b INT); +CREATE TABLE t3 (c INT); +INSERT INTO t1 (a) VALUES (1), (2); +INSERT INTO t2 (a,b) VALUES (1,2), (2,3); +INSERT INTO t3 (c) VALUES (1), (2); +SELECT +(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) +FROM t3; +(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) +2 +NULL +DROP TABLE t1, t2, t3; CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index e76c874324e..adb055dd5e5 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1,4 +1,78 @@ drop table if exists t1, t2; +CREATE TABLE t1 (a INT NOT NULL, KEY(a)) +PARTITION BY RANGE(a) +(PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199); +SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC; +a +60 +70 +90 +SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; +a +60 +70 +90 +INSERT INTO t1 VALUES (200), (250), (210); +SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC; +a +60 +70 +90 +199 +200 +210 +SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC; +a +200 +210 +SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC; +a +90 +70 +60 +SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC; +a +210 +200 +199 +90 +70 +60 +SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC; +a +210 +200 +SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; +a +199 +200 +210 +60 +70 +90 +SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; +a +200 +210 +SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; +a +60 +70 +90 +SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; +a +199 +200 +210 +60 +70 +90 +SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; +a +200 +210 +DROP TABLE t1; CREATE TABLE t1 ( a INT NOT NULL, b MEDIUMINT NOT NULL, @@ -133,7 +207,10 @@ KEY `c1` (`c1`) CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, KEY `c1` (`c1`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (c1) (PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */; +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (c1) +(PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM, +PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */; INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); @@ -299,13 +376,21 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY KEY (a) (PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY KEY (a) +(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM) */ alter table t1 reorganize partition p1 into (partition p1 values less than (3)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY KEY (a) (PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (3) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY KEY (a) +(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (3) ENGINE = MyISAM) */ drop table t1; CREATE TABLE t1 ( a int not null, @@ -323,7 +408,8 @@ t1 CREATE TABLE `t1` ( `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`a`,`b`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) */ drop table t1; CREATE TABLE t1 ( a int not null, @@ -500,7 +586,9 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION x1 VALUES IN (1) ENGINE = MEMORY) */ +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY LIST (a) +(PARTITION x1 VALUES IN (1) ENGINE = MEMORY) */ drop table t1; CREATE TABLE t1 (a int, unique(a)) PARTITION BY LIST (a) @@ -524,7 +612,9 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) PARTITIONS 5 */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (a) +PARTITIONS 5 */ drop table t1; CREATE TABLE t1 (a int) PARTITION BY RANGE (a) @@ -551,7 +641,11 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, + PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM) */ drop table t1; CREATE TABLE t1 (a int, b int) PARTITION BY RANGE (a) @@ -572,7 +666,16 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION x1 VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION x3 VALUES LESS THAN (8) ENGINE = MyISAM, PARTITION x4 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION x5 VALUES LESS THAN (12) ENGINE = MyISAM, PARTITION x6 VALUES LESS THAN (14) ENGINE = MyISAM, PARTITION x7 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION x8 VALUES LESS THAN (18) ENGINE = MyISAM, PARTITION x9 VALUES LESS THAN (20) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION x1 VALUES LESS THAN (6) ENGINE = MyISAM, + PARTITION x3 VALUES LESS THAN (8) ENGINE = MyISAM, + PARTITION x4 VALUES LESS THAN (10) ENGINE = MyISAM, + PARTITION x5 VALUES LESS THAN (12) ENGINE = MyISAM, + PARTITION x6 VALUES LESS THAN (14) ENGINE = MyISAM, + PARTITION x7 VALUES LESS THAN (16) ENGINE = MyISAM, + PARTITION x8 VALUES LESS THAN (18) ENGINE = MyISAM, + PARTITION x9 VALUES LESS THAN (20) ENGINE = MyISAM) */ drop table t1; create table t1 (a int not null, b int not null) partition by LIST (a+b) ( partition p0 values in (12), @@ -626,25 +729,37 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM) */ alter table t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM) */ alter table t1 engine=myisam; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM) */ alter table t1 engine=heap; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MEMORY, + PARTITION p1 ENGINE = MEMORY) */ alter table t1 remove partitioning; show create table t1; Table Create Table @@ -661,7 +776,10 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM) */ alter table t1 add column b int remove partitioning; show create table t1; Table Create Table @@ -678,7 +796,10 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM) */ alter table t1 engine=heap partition by key(a) @@ -688,7 +809,10 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MEMORY, + PARTITION p1 ENGINE = MEMORY) */ alter table t1 engine=myisam, add column c int remove partitioning; show create table t1; Table Create Table @@ -707,7 +831,10 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MEMORY, + PARTITION p1 ENGINE = MEMORY) */ alter table t1 partition by key (a) (partition p0, partition p1); @@ -717,7 +844,10 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MEMORY, + PARTITION p1 ENGINE = MEMORY) */ alter table t1 engine=heap partition by key (a) @@ -728,7 +858,10 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MEMORY, + PARTITION p1 ENGINE = MEMORY) */ alter table t1 partition by key(a) (partition p0, partition p1 engine=heap); @@ -871,14 +1004,23 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) (PARTITION p0 VALUES LESS THAN (100) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION p0 VALUES LESS THAN (100) ENGINE = MyISAM) */ alter table t1 add partition (partition p1 values less than (200) (subpartition subpart21)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) (PARTITION p0 VALUES LESS THAN (100) (SUBPARTITION p0sp0 ENGINE = MyISAM), PARTITION p1 VALUES LESS THAN (200) (SUBPARTITION subpart21 ENGINE = MyISAM)) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION p0 VALUES LESS THAN (100) + (SUBPARTITION p0sp0 ENGINE = MyISAM), + PARTITION p1 VALUES LESS THAN (200) + (SUBPARTITION subpart21 ENGINE = MyISAM)) */ drop table t1; create table t1 (a int) partition by key (a); @@ -886,13 +1028,17 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) */ alter table t1 add partition (partition p1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM) */ drop table t1; create table t1 (a int, b int) partition by range (a) @@ -969,7 +1115,10 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION p1 VALUES IN (1) ENGINE = MyISAM, PARTITION p2 VALUES IN (2) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY LIST (a) +(PARTITION p1 VALUES IN (1) ENGINE = MyISAM, + PARTITION p2 VALUES IN (2) ENGINE = MyISAM) */ drop table t1; create table t1 (a int unsigned not null auto_increment primary key) partition by key(a); @@ -980,7 +1129,8 @@ t2 CREATE TABLE `t2` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `c` char(10) DEFAULT NULL, PRIMARY KEY (`a`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='no comment' /*!50100 PARTITION BY KEY (a) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='no comment' +/*!50100 PARTITION BY KEY (a) */ drop table t2; create table t1 (f1 int) partition by hash (f1) as select 1; drop table t1; @@ -1177,7 +1327,9 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION p0 VALUES IN (NULL) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (NULL) ENGINE = MyISAM) */ DROP TABLE t1; CREATE TABLE t1 (a int) PARTITION BY RANGE(a) @@ -1213,7 +1365,9 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) /*!50100 PARTITION BY KEY (a) (PARTITION p0) */ +) +/*!50100 PARTITION BY KEY (a) +(PARTITION p0) */ set session sql_mode=''; drop table t1; create table t1 (a int) @@ -1227,7 +1381,8 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(1) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) */ drop table t1; CREATE TABLE t1 (a int) ENGINE = MYISAM PARTITION BY KEY(a); INSERT into t1 values (1), (2); @@ -1299,7 +1454,9 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) unsigned NOT NULL, PRIMARY KEY (`a`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 10 */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +PARTITIONS 10 */ insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612); select * from t1; @@ -1475,7 +1632,10 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (b) (PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (20) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (b) +(PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM, + PARTITION p2 VALUES LESS THAN (20) ENGINE = MyISAM) */ drop table t1, t2; create table t1 (s1 timestamp on update current_timestamp, s2 int) @@ -1612,7 +1772,13 @@ t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(25) DEFAULT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) SUBPARTITION BY HASH (id) SUBPARTITIONS 2 (PARTITION pa1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION pa2 VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (id) +SUBPARTITION BY HASH (id) +SUBPARTITIONS 2 +(PARTITION pa1 VALUES LESS THAN (10) ENGINE = MyISAM, + PARTITION pa2 VALUES LESS THAN (20) ENGINE = MyISAM, + PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ drop table t1; CREATE TABLE t1 ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, diff --git a/mysql-test/r/partition_archive.result b/mysql-test/r/partition_archive.result index 5c84b41e81b..de64b09e042 100644 --- a/mysql-test/r/partition_archive.result +++ b/mysql-test/r/partition_archive.result @@ -60,7 +60,8 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) */ +) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (a) */ drop table t1; CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT, f1 VARCHAR(25), @@ -84,7 +85,21 @@ t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `f1` varchar(25) DEFAULT NULL, PRIMARY KEY (`id`) -) ENGINE=ARCHIVE AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) SUBPARTITION BY HASH (id) SUBPARTITIONS 2 (PARTITION pa1 VALUES LESS THAN (10) ENGINE = ARCHIVE, PARTITION pa2 VALUES LESS THAN (20) ENGINE = ARCHIVE, PARTITION pa3 VALUES LESS THAN (30) ENGINE = ARCHIVE, PARTITION pa4 VALUES LESS THAN (40) ENGINE = ARCHIVE, PARTITION pa5 VALUES LESS THAN (50) ENGINE = ARCHIVE, PARTITION pa6 VALUES LESS THAN (60) ENGINE = ARCHIVE, PARTITION pa7 VALUES LESS THAN (70) ENGINE = ARCHIVE, PARTITION pa8 VALUES LESS THAN (80) ENGINE = ARCHIVE, PARTITION pa9 VALUES LESS THAN (90) ENGINE = ARCHIVE, PARTITION pa10 VALUES LESS THAN (100) ENGINE = ARCHIVE, PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE) */ +) ENGINE=ARCHIVE AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (id) +SUBPARTITION BY HASH (id) +SUBPARTITIONS 2 +(PARTITION pa1 VALUES LESS THAN (10) ENGINE = ARCHIVE, + PARTITION pa2 VALUES LESS THAN (20) ENGINE = ARCHIVE, + PARTITION pa3 VALUES LESS THAN (30) ENGINE = ARCHIVE, + PARTITION pa4 VALUES LESS THAN (40) ENGINE = ARCHIVE, + PARTITION pa5 VALUES LESS THAN (50) ENGINE = ARCHIVE, + PARTITION pa6 VALUES LESS THAN (60) ENGINE = ARCHIVE, + PARTITION pa7 VALUES LESS THAN (70) ENGINE = ARCHIVE, + PARTITION pa8 VALUES LESS THAN (80) ENGINE = ARCHIVE, + PARTITION pa9 VALUES LESS THAN (90) ENGINE = ARCHIVE, + PARTITION pa10 VALUES LESS THAN (100) ENGINE = ARCHIVE, + PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE) */ select count(*) from t1; count(*) 100 diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result index 43d9aac13b7..3e4acfdee12 100644 --- a/mysql-test/r/partition_datatype.result +++ b/mysql-test/r/partition_datatype.result @@ -326,7 +326,11 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` bit(27) NOT NULL DEFAULT '\0\0\0\0', PRIMARY KEY (`a`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM, PARTITION p2 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (a) +(PARTITION p0 ENGINE = MyISAM, + PARTITION p1 ENGINE = MyISAM, + PARTITION p2 ENGINE = MyISAM) */ insert into t1 values (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34); select hex(a) from t1 where a = 7; hex(a) diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 6e56f9023eb..3692626f9ac 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -1,3 +1,68 @@ +# Bug#37721, test of ORDER BY on PK and WHERE on INDEX +CREATE TABLE t1 ( +a INT, +b INT, +PRIMARY KEY (a), +INDEX (b)) +ENGINE InnoDB +PARTITION BY HASH(a) +PARTITIONS 3; +INSERT INTO t1 VALUES (0,0),(4,0),(2,0); +SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; +a +0 +2 +4 +SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; +a +4 +2 +0 +ALTER TABLE t1 DROP INDEX b; +SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; +a +0 +2 +4 +SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; +a +4 +2 +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a VARCHAR(600), +b VARCHAR(600), +PRIMARY KEY (a), +INDEX (b)) +ENGINE InnoDB +PARTITION BY KEY(a) +PARTITIONS 3; +INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'1'),repeat('0',257)); +INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'3'),repeat('0',257)); +INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'2'),repeat('0',257)); +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; +right(a,1) +1 +2 +3 +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; +right(a,1) +3 +2 +1 +ALTER TABLE t1 DROP INDEX b; +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; +right(a,1) +1 +2 +3 +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; +right(a,1) +3 +2 +1 +DROP TABLE t1; # Bug#32948 CREATE TABLE t1 (c1 INT, PRIMARY KEY (c1)) ENGINE=INNODB; CREATE TABLE t2 (c1 INT, PRIMARY KEY (c1), @@ -72,7 +137,8 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) */ drop table t1; create table t1 (a int) engine = innodb @@ -84,7 +150,9 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */ +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */ drop table t1; create table t1 ( @@ -160,5 +228,9 @@ Table Create Table t1 CREATE TABLE `t1` ( `int_column` int(11) DEFAULT NULL, `char_column` char(5) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (int_column) SUBPARTITION BY KEY (char_column) SUBPARTITIONS 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (int_column) +SUBPARTITION BY KEY (char_column) +SUBPARTITIONS 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ drop table t1; diff --git a/mysql-test/r/partition_innodb_stmt.result b/mysql-test/r/partition_innodb_stmt.result new file mode 100644 index 00000000000..2735b0d2193 --- /dev/null +++ b/mysql-test/r/partition_innodb_stmt.result @@ -0,0 +1,48 @@ +# connection default +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +CREATE TABLE t1 +( +id SMALLINT NOT NULL, +PRIMARY KEY (id) +) ENGINE=innodb +PARTITION BY RANGE (id) +( +PARTITION p1 VALUES LESS THAN (2), +PARTITION p2 VALUES LESS THAN (4), +PARTITION p3 VALUES LESS THAN (10) +); +INSERT INTO t1 VALUES (1),(2),(3); +# Test READ COMMITTED -> REPEATABLE READ +FLUSH TABLES; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t1; +id +1 +2 +3 +#connection con1 +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +INSERT INTO t1 VALUES(7); +COMMIT; +# connection default +COMMIT; +FLUSH TABLES; +# Test REPEATABLE READ -> READ COMMITTED +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t1; +id +1 +2 +3 +7 +# connection con1 +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +INSERT INTO t1 VALUES(9); +ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' +COMMIT; +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/r/partition_mgm.result b/mysql-test/r/partition_mgm.result index 9ef220028b3..465bddcb3fb 100644 --- a/mysql-test/r/partition_mgm.result +++ b/mysql-test/r/partition_mgm.result @@ -16,14 +16,18 @@ Table Create Table t1 CREATE TABLE `t1` ( `f_date` date DEFAULT NULL, `f_varchar` varchar(30) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) PARTITIONS 2 */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (YEAR(f_date)) +PARTITIONS 2 */ ALTER TABLE t1 COALESCE PARTITION 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_date` date DEFAULT NULL, `f_varchar` varchar(30) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) PARTITIONS 1 */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (YEAR(f_date)) +PARTITIONS 1 */ drop table t1; create table t1 (a int) partition by list (a) @@ -36,3 +40,42 @@ alter table t1 REORGANIZE partition p1 INTO (partition p11 values in (1,2), partition p12 values in (3,4)); drop table t1; +CREATE TABLE t1 (a INT) +/*!50100 PARTITION BY HASH (a) +/* Test +of multi-line +comment */ +PARTITIONS 5 */; +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 '*/' at line 6 +CREATE TABLE t1 (a INT) +/*!50100 PARTITION BY HASH (a) +-- with a single line comment embedded +PARTITIONS 5 */; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (a) +PARTITIONS 5 */ +DROP TABLE t1; +CREATE TABLE t1 (a INT) +/*!50100 PARTITION BY HASH (a) +PARTITIONS 5 */; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (a) +PARTITIONS 5 */ +DROP TABLE t1; +CREATE TABLE t1 (a INT) /*!50100 PARTITION BY HASH (a) PARTITIONS 5 */; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY HASH (a) +PARTITIONS 5 */ +DROP TABLE t1; diff --git a/mysql-test/r/partition_mgm_err.result b/mysql-test/r/partition_mgm_err.result index 764f6fb5632..db89c6ef6e7 100644 --- a/mysql-test/r/partition_mgm_err.result +++ b/mysql-test/r/partition_mgm_err.result @@ -84,6 +84,14 @@ ALTER TABLE t1 DROP PARTITION x1; ERROR HY000: Partition management on a not partitioned table is not possible ALTER TABLE t1 COALESCE PARTITION 1; ERROR HY000: Partition management on a not partitioned table is not possible +ALTER TABLE t1 ANALYZE PARTITION p1; +ERROR HY000: Partition management on a not partitioned table is not possible +ALTER TABLE t1 CHECK PARTITION p1; +ERROR HY000: Partition management on a not partitioned table is not possible +ALTER TABLE t1 OPTIMIZE PARTITION p1; +ERROR HY000: Partition management on a not partitioned table is not possible +ALTER TABLE t1 REPAIR PARTITION p1; +ERROR HY000: Partition management on a not partitioned table is not possible DROP TABLE t1; CREATE TABLE t1 (a int) PARTITION BY KEY (a) @@ -136,7 +144,9 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 2 */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) +PARTITIONS 2 */ DROP TABLE t1; CREATE TABLE t1 (a INT) PARTITION BY HASH(a); ALTER TABLE t1 ADD PARTITION PARTITIONS 4; diff --git a/mysql-test/r/partition_not_windows.result b/mysql-test/r/partition_not_windows.result index c7fdcf6de2b..42dca557b3e 100644 --- a/mysql-test/r/partition_not_windows.result +++ b/mysql-test/r/partition_not_windows.result @@ -24,13 +24,15 @@ data directory='/not/existing' index directory='/not/existing' ); Warnings: -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `i` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (i) (PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (i) +(PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ DROP TABLE t1, t2; set @@sql_mode=@org_mode; CREATE TABLE t1(a INT) diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index 23a38ff3885..e8fc55b759b 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -8,7 +8,9 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ drop table t1; create table t1 (a integer) partition by range (a) @@ -137,7 +139,11 @@ t1 CREATE TABLE `t1` ( `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`a`,`b`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM, PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM, PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM, + PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM, + PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */ ALTER TABLE t1 partition by range (a) partitions 3 @@ -157,7 +163,11 @@ t1 CREATE TABLE `t1` ( `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`a`,`b`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM, PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM, PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM, + PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM, + PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */ drop table if exists t1; CREATE TABLE t1 ( a int not null, @@ -254,7 +264,15 @@ t1 CREATE TABLE `t1` ( `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`a`,`b`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY HASH (a+b) (PARTITION x1 VALUES LESS THAN (1) (SUBPARTITION x11 ENGINE = MyISAM, SUBPARTITION x12 ENGINE = MyISAM), PARTITION x2 VALUES LESS THAN (5) (SUBPARTITION x21 ENGINE = MyISAM, SUBPARTITION x22 ENGINE = MyISAM)) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a+b) +(PARTITION x1 VALUES LESS THAN (1) + (SUBPARTITION x11 ENGINE = MyISAM, + SUBPARTITION x12 ENGINE = MyISAM), + PARTITION x2 VALUES LESS THAN (5) + (SUBPARTITION x21 ENGINE = MyISAM, + SUBPARTITION x22 ENGINE = MyISAM)) */ ALTER TABLE t1 ADD COLUMN d int; show create table t1; Table Create Table @@ -264,7 +282,15 @@ t1 CREATE TABLE `t1` ( `c` int(11) NOT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`a`,`b`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY HASH (a+b) (PARTITION x1 VALUES LESS THAN (1) (SUBPARTITION x11 ENGINE = MyISAM, SUBPARTITION x12 ENGINE = MyISAM), PARTITION x2 VALUES LESS THAN (5) (SUBPARTITION x21 ENGINE = MyISAM, SUBPARTITION x22 ENGINE = MyISAM)) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a+b) +(PARTITION x1 VALUES LESS THAN (1) + (SUBPARTITION x11 ENGINE = MyISAM, + SUBPARTITION x12 ENGINE = MyISAM), + PARTITION x2 VALUES LESS THAN (5) + (SUBPARTITION x21 ENGINE = MyISAM, + SUBPARTITION x22 ENGINE = MyISAM)) */ drop table t1; CREATE TABLE t1 ( a int not null, @@ -487,7 +513,10 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) unsigned DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */ drop table t1; create table t1 (a bigint unsigned) partition by range (a) @@ -497,7 +526,10 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) unsigned DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (2) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (2) ENGINE = MyISAM, + PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */ insert into t1 values (0xFFFFFFFFFFFFFFFF); ERROR HY000: Table has no partition for value 18446744073709551615 drop table t1; diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result index 73844e9e70d..60184d11d9c 100644 --- a/mysql-test/r/partition_symlink.result +++ b/mysql-test/r/partition_symlink.result @@ -101,13 +101,15 @@ data directory='/not/existing' index directory='/not/existing' ); Warnings: -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `i` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (i) (PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (i) +(PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ DROP TABLE t1, t2; set @@sql_mode=@org_mode; create table t1 (a int) diff --git a/mysql-test/r/partition_windows.result b/mysql-test/r/partition_windows.result index c2d4054deeb..756690925f8 100644 --- a/mysql-test/r/partition_windows.result +++ b/mysql-test/r/partition_windows.result @@ -15,16 +15,16 @@ DATA DIRECTORY = 'E:/mysqltest/p2Data' INDEX DIRECTORY = 'F:/mysqltest/p2Index' ); Warnings: -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored INSERT INTO t1 VALUES (NULL, "first", 1); INSERT INTO t1 VALUES (NULL, "second", 2); INSERT INTO t1 VALUES (NULL, "third", 3); ALTER TABLE t1 ADD PARTITION (PARTITION p3 DATA DIRECTORY = 'G:/mysqltest/p3Data' INDEX DIRECTORY = 'H:/mysqltest/p3Index'); Warnings: -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored INSERT INTO t1 VALUES (NULL, "last", 4); DROP TABLE t1; diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result index ebe161f46b3..a298c552806 100644 --- a/mysql-test/r/ps_11bugs.result +++ b/mysql-test/r/ps_11bugs.result @@ -162,4 +162,32 @@ a b 12 NULL drop table t1; drop table t2; +CREATE TABLE t1 (a INT); +PREPARE stmt FROM 'select 1 from `t1` where `a` = any (select (@@tmpdir))'; +EXECUTE stmt; +1 +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t2 VALUES (400000), (400001); +SET @@sort_buffer_size=400000; +CREATE FUNCTION p1(i INT) RETURNS INT +BEGIN +SET @@sort_buffer_size= i; +RETURN i + 1; +END| +SELECT * FROM t2 WHERE a = @@sort_buffer_size AND p1(@@sort_buffer_size + 1) > a - 1; +a +400000 +DROP TABLE t2; +DROP FUNCTION p1; +SELECT CONCAT(@@sort_buffer_size); +CONCAT(@@sort_buffer_size) +400001 +SELECT LEFT("12345", @@ft_boolean_syntax); +LEFT("12345", @@ft_boolean_syntax) + +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '+ -><()~*:""&|' +SET @@sort_buffer_size=DEFAULT; End of 5.0 tests. diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index af3ed8fe5a9..fddca23006d 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1002,8 +1002,8 @@ def TRIGGERS EVENT_OBJECT_TABLE Table 253 192 2 N 1 0 33 def TRIGGERS ACTION_STATEMENT Statement 252 589815 10 N 17 0 33 def TRIGGERS ACTION_TIMING Timing 253 18 6 N 1 0 33 def TRIGGERS CREATED Created 12 19 0 Y 128 0 63 -def TRIGGERS SQL_MODE sql_mode 252 589815 0 N 17 0 33 -def TRIGGERS DEFINER Definer 252 589815 14 N 17 0 33 +def TRIGGERS SQL_MODE sql_mode 253 24576 0 N 1 0 33 +def TRIGGERS DEFINER Definer 253 231 14 N 1 0 33 def TRIGGERS CHARACTER_SET_CLIENT character_set_client 253 96 6 N 1 0 33 def TRIGGERS COLLATION_CONNECTION collation_connection 253 96 6 N 1 0 33 def TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 1 0 33 @@ -1046,8 +1046,8 @@ def TRIGGERS ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_OLD_TABLE 253 192 0 Y def TRIGGERS ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_NEW_TABLE 253 192 0 Y 0 0 33 def TRIGGERS ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_OLD_ROW 253 9 3 N 1 0 33 def TRIGGERS ACTION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW 253 9 3 N 1 0 33 -def TRIGGERS SQL_MODE SQL_MODE 252 589815 0 N 17 0 33 -def TRIGGERS DEFINER DEFINER 252 589815 14 N 17 0 33 +def TRIGGERS SQL_MODE SQL_MODE 253 24576 0 N 1 0 33 +def TRIGGERS DEFINER DEFINER 253 231 14 N 1 0 33 TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW SQL_MODE DEFINER NULL test t1_bi INSERT NULL test t1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW root@localhost ---------------------------------------------------------------- diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 0d966908d64..01ddde56450 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6820,6 +6820,24 @@ ttt 2 drop function func30787; drop table t1; +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +CREATE PROCEDURE test_sp() +SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id; +CALL test_sp(); +id +1 +2 +3 +4 +CALL test_sp(); +id +1 +2 +3 +4 +DROP PROCEDURE test_sp; +DROP TABLE t1; create table t1(c1 INT); create function f1(p1 int) returns varchar(32) return 'aaa'; @@ -6846,6 +6864,19 @@ select substr(`str`, `pos`+ 1 ) into `str`; end $ call `p2`('s s s s s s'); drop procedure `p2`; +drop table if exists t1; +drop procedure if exists p1; +create procedure p1() begin select * from t1; end$ +call p1$ +ERROR 42S02: Table 'test.t1' doesn't exist +create table t1 (a integer)$ +call p1$ +a +alter table t1 add b integer; +call p1$ +a +drop table t1; +drop procedure p1; # ------------------------------------------------------------------ # -- End of 5.0 tests # ------------------------------------------------------------------ diff --git a/mysql-test/r/ssl_capath_basic.result b/mysql-test/r/ssl_capath_basic.result index a2c01aab806..3d161392917 100644 --- a/mysql-test/r/ssl_capath_basic.result +++ b/mysql-test/r/ssl_capath_basic.result @@ -1,7 +1,7 @@ '#---------------------BS_STVARS_046_01----------------------#' SELECT COUNT(@@GLOBAL.ssl_capath); COUNT(@@GLOBAL.ssl_capath) -0 +1 1 Expected '#---------------------BS_STVARS_046_02----------------------#' SET @@GLOBAL.ssl_capath=1; @@ -9,7 +9,7 @@ ERROR HY000: Variable 'ssl_capath' is a read only variable Expected error 'Read only variable' SELECT COUNT(@@GLOBAL.ssl_capath); COUNT(@@GLOBAL.ssl_capath) -0 +1 1 Expected '#---------------------BS_STVARS_046_03----------------------#' SELECT @@GLOBAL.ssl_capath = VARIABLE_VALUE @@ -20,7 +20,7 @@ NULL 1 Expected SELECT COUNT(@@GLOBAL.ssl_capath); COUNT(@@GLOBAL.ssl_capath) -0 +1 1 Expected SELECT COUNT(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -36,7 +36,7 @@ NULL '#---------------------BS_STVARS_046_05----------------------#' SELECT COUNT(@@ssl_capath); COUNT(@@ssl_capath) -0 +1 1 Expected SELECT COUNT(@@local.ssl_capath); ERROR HY000: Variable 'ssl_capath' is a GLOBAL variable @@ -46,7 +46,7 @@ ERROR HY000: Variable 'ssl_capath' is a GLOBAL variable Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@GLOBAL.ssl_capath); COUNT(@@GLOBAL.ssl_capath) -0 +1 1 Expected SELECT ssl_capath = @@SESSION.ssl_capath; ERROR 42S22: Unknown column 'ssl_capath' in 'field list' diff --git a/mysql-test/r/ssl_cipher_basic.result b/mysql-test/r/ssl_cipher_basic.result index 3c7098e1150..df0fc8b5aad 100644 --- a/mysql-test/r/ssl_cipher_basic.result +++ b/mysql-test/r/ssl_cipher_basic.result @@ -1,7 +1,7 @@ '#---------------------BS_STVARS_048_01----------------------#' SELECT COUNT(@@GLOBAL.ssl_cipher); COUNT(@@GLOBAL.ssl_cipher) -0 +1 1 Expected '#---------------------BS_STVARS_048_02----------------------#' SET @@GLOBAL.ssl_cipher=1; @@ -9,7 +9,7 @@ ERROR HY000: Variable 'ssl_cipher' is a read only variable Expected error 'Read only variable' SELECT COUNT(@@GLOBAL.ssl_cipher); COUNT(@@GLOBAL.ssl_cipher) -0 +1 1 Expected '#---------------------BS_STVARS_048_03----------------------#' SELECT @@GLOBAL.ssl_cipher = VARIABLE_VALUE @@ -20,7 +20,7 @@ NULL 1 Expected SELECT COUNT(@@GLOBAL.ssl_cipher); COUNT(@@GLOBAL.ssl_cipher) -0 +1 1 Expected SELECT COUNT(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -36,7 +36,7 @@ NULL '#---------------------BS_STVARS_048_05----------------------#' SELECT COUNT(@@ssl_cipher); COUNT(@@ssl_cipher) -0 +1 1 Expected SELECT COUNT(@@local.ssl_cipher); ERROR HY000: Variable 'ssl_cipher' is a GLOBAL variable @@ -46,7 +46,7 @@ ERROR HY000: Variable 'ssl_cipher' is a GLOBAL variable Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@GLOBAL.ssl_cipher); COUNT(@@GLOBAL.ssl_cipher) -0 +1 1 Expected SELECT ssl_cipher = @@SESSION.ssl_cipher; ERROR 42S22: Unknown column 'ssl_cipher' in 'field list' diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index f4fd041731d..b15ec9dbe4e 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -779,6 +779,22 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE 0); 1 1 DROP TABLE t1, t2; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +int_key INT, +varchar_key VARCHAR(5) UNIQUE, +varchar_nokey VARCHAR(5) +); +INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p'); +SELECT varchar_nokey +FROM t1 +WHERE NULL NOT IN ( +SELECT INNR.pk FROM t1 AS INNR2 +LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key ) +WHERE INNR.varchar_key > 'n{' +); +varchar_nokey +DROP TABLE t1; End of 5.0 tests create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 86985bb6f85..67eed2e41c2 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -79,7 +79,7 @@ drop database mysqltest; create table t1 (a int not null) engine=myisam; alter table t1 data directory="MYSQLTEST_VARDIR/tmp"; Warnings: -Warning 0 DATA DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -88,7 +88,7 @@ t1 CREATE TABLE `t1` ( alter table t1 add b int; alter table t1 data directory="MYSQLTEST_VARDIR/log"; Warnings: -Warning 0 DATA DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -97,7 +97,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 index directory="MYSQLTEST_VARDIR/log"; Warnings: -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <INDEX DIRECTORY> option ignored show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -164,8 +164,8 @@ ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (Errco SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='NO_DIR_IN_CREATE'; CREATE TABLE t1(a INT) DATA DIRECTORY='MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp'; Warnings: -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored DROP TABLE t1; SET @@SQL_MODE=@OLD_SQL_MODE; End of 5.1 tests diff --git a/mysql-test/r/thread_cache_size_func.result b/mysql-test/r/thread_cache_size_func.result index ccca75bd316..f808e28a512 100644 --- a/mysql-test/r/thread_cache_size_func.result +++ b/mysql-test/r/thread_cache_size_func.result @@ -2,67 +2,31 @@ SET @global_thread_cache_size = @@GLOBAL.thread_cache_size; FLUSH STATUS; '# Test1#' SET @@GLOBAL.thread_cache_size=3; -Saving threads cached, threads created values SHOW STATUS LIKE 'Threads_cached'; Variable_name Value Threads_cached 0 0 Expected -'#Old value for thread_cache'# ** Connecting conn1 using username 'root' ** ** Connecting conn2 using username 'root' ** ** Connecting conn3 using username 'root' ** ** Connecting conn4 using username 'root' ** -Saving threads cached, threads created values -Threads Created Difference -4 -4 Expected +** Connection default ** SHOW STATUS LIKE 'Threads_cached'; Variable_name Value Threads_cached 0 0 Expected -** Connection default ** ** Disconnecting conn1,conn2,conn3,conn4 ** -'#new values for thread cache after disconnecting'# -Saving threads cached, threads created values -Threads Created Difference -0 -0 Expected SHOW STATUS LIKE 'Threads_cached'; Variable_name Value Threads_cached 3 3 Expected -SET @@GLOBAL.thread_cache_size=1; -Saving threads cached, threads created values -Threads Created Difference -0 -0 Expected -SHOW STATUS LIKE 'Threads_cached'; -Variable_name Value -Threads_cached 3 -1 Expected -Bug: The number of threads cached should have decreased with cache size decrease. +SET @@GLOBAL.thread_cache_size= 1; ** Connecting conn1 using username 'root' ** ** Connecting conn2 using username 'root' ** -Saving threads cached, threads created values -Threads Created Difference -0 -1 Expected -Bug: The number of threads created should have increased because the cache should have 1 thread only -SHOW STATUS LIKE 'Threads_cached'; -Variable_name Value -Threads_cached 1 -0 Expected -Bug: The number of threads created should have decreased because atleast 1 new connection came in -** Connection default ** +connection default; ** Disconnecting conn1,conn2 ** -'#new status values for thread cache'# -Saving threads cached, threads created values -Threads Created Difference -0 -0 Expected SHOW STATUS LIKE 'Threads_cached'; Variable_name Value Threads_cached 1 1 Expected -Saving threads cached, threads created values SET @@GLOBAL.thread_cache_size = @global_thread_cache_size; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 06bbdcfd175..87afaaa3179 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -158,7 +158,7 @@ explain extended select @@IDENTITY,last_insert_id(), @@identity; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity` +Note 1003 select @@IDENTITY AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,@@identity AS `@@identity` set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; set global concurrent_insert=2; show variables like 'concurrent_insert'; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 9bea52ef769..322e441154e 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3672,6 +3672,8 @@ DROP VIEW v1; # -- End of test case for Bug#35193. +CREATE VIEW v1 AS SELECT 1; +DROP VIEW v1; # ----------------------------------------------------------------- # -- End of 5.0 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/windows.result b/mysql-test/r/windows.result index e58a891838a..d056bde4dca 100644 --- a/mysql-test/r/windows.result +++ b/mysql-test/r/windows.result @@ -9,8 +9,8 @@ drop table nu; drop table if exists t1; CREATE TABLE t1 ( `ID` int(6) ) data directory 'c:/tmp/' index directory 'c:/tmp/' engine=MyISAM; Warnings: -Warning 0 DATA DIRECTORY option ignored -Warning 0 INDEX DIRECTORY option ignored +Warning 1618 <DATA DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored drop table t1; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,1); @@ -18,6 +18,7 @@ EXPLAIN SELECT * FROM t1 WHERE b = (SELECT max(2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +DROP TABLE t1; End of 5.0 tests. drop procedure if exists proc_1; create procedure proc_1() install plugin my_plug soname '\\root\\some_plugin.dll'; diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index 5fb03d2378e..592cf07522b 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -55,3 +55,23 @@ select * from t1; a 20 drop table t1; +drop table if exists t1; +create table t1(a int, b int, c varchar(20), primary key(a)) engine = innodb; +insert into t1 values(1, 1, 'a'); +insert into t1 values(2, 2, 'b'); +xa start 'a','b'; +update t1 set c = 'aa' where a = 1; +xa start 'a','c'; +update t1 set c = 'bb' where a = 2; +update t1 set c = 'bb' where a = 2; +update t1 set c = 'aa' where a = 1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select count(*) from t1; +count(*) +2 +xa end 'a','c'; +ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected +xa rollback 'a','c'; +xa start 'a','c'; +drop table t1; +End of 5.0 tests |