diff options
Diffstat (limited to 'mysql-test')
93 files changed, 1982 insertions, 489 deletions
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 1bbcfde9aa3..ee5c75cf0dd 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -739,8 +739,7 @@ sub run_test_server ($$$) { # Repeat test $opt_repeat number of times my $repeat= $result->{repeat} || 1; - # Don't repeat if test was skipped - if ($repeat < $opt_repeat && $result->{'result'} ne 'MTR_RES_SKIPPED') + if ($repeat < $opt_repeat) { $result->{retries}= 0; $result->{rep_failures}++ if $result->{failures}; @@ -4559,8 +4558,8 @@ sub check_warnings ($) { my $timeout= start_timer(check_timeout($tinfo)); + my $result= 0; while (1){ - my $result= 0; my $proc= My::SafeProcess->wait_any_timeout($timeout); mtr_report("Got $proc"); @@ -5779,7 +5778,7 @@ sub debugger_arguments { $$exe= $debugger; } - elsif ( $debugger =~ /windbg/ ) + elsif ( $debugger =~ /windbg|vsjitdebugger/ ) { # windbg exe arg1 .. argn diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index f7f87b810ce..13fbe00f2e3 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2228,12 +2228,11 @@ alter table t1 drop column a; ERROR 42S22: Unknown column 'a' in 'CHECK' alter table t1 drop column b, add column b bigint first; ERROR 42S22: Unknown column 'b' in 'CHECK' +alter table t1 drop column a, drop constraint constraint_1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL, - `b` int(11) DEFAULT NULL, - CONSTRAINT `CONSTRAINT_1` CHECK (`a` > `b`) + `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int, b int, check(a>0)); @@ -2265,12 +2264,11 @@ drop table t1; create table t1 (a int, b int, c int, unique(a,b)); alter table t1 drop column a; ERROR 42000: Key column 'a' doesn't exist in table +alter table t1 drop column a, drop index a; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, - `c` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`,`b`) + `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result index 308b6a2fc2b..525140d96e7 100644 --- a/mysql-test/r/check_constraint.result +++ b/mysql-test/r/check_constraint.result @@ -142,3 +142,13 @@ create table t1 (a int check (@b in (select user from mysql.user))); ERROR HY000: Function or expression 'select ...' cannot be used in the CHECK clause of `a` create table t1 (a int check (a > @b)); ERROR HY000: Function or expression '@b' cannot be used in the CHECK clause of `a` +create table t1 (a int check (a = 1)); +insert t1 values (1); +insert t1 values (2); +ERROR 23000: CONSTRAINT `a` failed for `test`.`t1` +insert t1 values (NULL); +select * from t1; +a +1 +NULL +drop table t1; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 258926e8c4b..ade8a4f7549 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -230,7 +230,7 @@ insert into t2 (a) values (1023); do (f2(23)); Warnings: Error 1062 Duplicate entry '23' for key 'a' -Note 4091 At line 4 in test.f2 +Note 4092 At line 4 in test.f2 select * from t2; a 1023 diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result index d55a232c715..760b2710586 100644 --- a/mysql-test/r/count_distinct.result +++ b/mysql-test/r/count_distinct.result @@ -106,3 +106,28 @@ count(distinct user_id) 17 drop table t1; set @@tmp_table_size = default; +create table t1 ( +a VARCHAR(1020), +b int +); +insert into t1 values +( 0 , 1 ), +( 1 , 2 ), +( 2 , 3 ), +( 3 , 4 ), +( 4 , 5 ), +( 5 , 6 ), +( 6 , 7 ), +( 7 , 8 ), +( 8 , 9 ), +( 9 , 10 ), +( 0 , 11 ), +( 1 , 12 ), +( 2 , 13 ), +( 3 , 14 ); +set @@tmp_table_size=1024; +select count(distinct a) from t1; +count(distinct a) +10 +drop table t1; +set @@tmp_table_size = default; diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result index 82133cd8e1d..79e0bdf5e20 100644 --- a/mysql-test/r/create_drop_binlog.result +++ b/mysql-test/r/create_drop_binlog.result @@ -160,7 +160,7 @@ Note 1050 Table 'v1' already exists DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' SHOW BINLOG EVENTS; Log_name Pos Event_type Server_id End_log_pos Info # # Format_desc 1 # VER diff --git a/mysql-test/r/create_drop_view.result b/mysql-test/r/create_drop_view.result index c7185cff7d0..d23b9b713ad 100644 --- a/mysql-test/r/create_drop_view.result +++ b/mysql-test/r/create_drop_view.result @@ -55,5 +55,5 @@ id DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' DROP TABLE t1; diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 8439c40fbf9..16dbdf7969c 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -558,3 +558,18 @@ SET NAMES latin1; # # End of 5.1 tests # +select time_format('2001-01-01 02:02:02', '%d.%m.%Y'); +time_format('2001-01-01 02:02:02', '%d.%m.%Y') +NULL +select time_format('2001-01-01 02:02:02', '%d %T'); +time_format('2001-01-01 02:02:02', '%d %T') +NULL +select time_format('01 02:02:02', '%d %T'); +time_format('01 02:02:02', '%d %T') +NULL +select time_format('01 02:02:02', '%T'); +time_format('01 02:02:02', '%T') +26:02:02 +select time_format('2001-01-01 02:02:02', '%T'); +time_format('2001-01-01 02:02:02', '%T') +02:02:02 diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 08e6a19a9a3..3fd5370f470 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -209,10 +209,10 @@ Note 1051 Unknown table 'test.table1' Note 1051 Unknown table 'test.table2' DROP VIEW IF EXISTS view1,view2,view3,view4; Warnings: -Note 4089 Unknown VIEW: 'test.view1' -Note 4089 Unknown VIEW: 'test.view2' -Note 4089 Unknown VIEW: 'test.view3' -Note 4089 Unknown VIEW: 'test.view4' +Note 4090 Unknown VIEW: 'test.view1' +Note 4090 Unknown VIEW: 'test.view2' +Note 4090 Unknown VIEW: 'test.view3' +Note 4090 Unknown VIEW: 'test.view4' # Test error message when trigger does not find table CREATE TABLE table1(a int); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index c99d7e62285..7f2ed1c2ff0 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -838,6 +838,11 @@ select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1); select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'); CRC32(NULL) CRC32('') CRC32('MySQL') CRC32('mysql') CRC32('01234567') CRC32('012345678') NULL 0 3259397556 2501908538 763378421 939184570 +explain extended select (3-2)+1, (3/2)*1, 3-(2+1), 3/(2*1); +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 3 - 2 + 1 AS `(3-2)+1`,3 / 2 * 1 AS `(3/2)*1`,3 - (2 + 1) AS `3-(2+1)`,3 / (2 * 1) AS `3/(2*1)` # # Start of 10.3 tests # diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 39f8f41d1e7..299b6344192 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -1441,6 +1441,23 @@ release_lock('test') # -- Done. # +# MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +# +SET NAMES utf8; +SELECT COERCIBILITY(NAME_CONST('name','test')); +COERCIBILITY(NAME_CONST('name','test')) +2 +SELECT COERCIBILITY(NAME_CONST('name',TIME'00:00:00')); +COERCIBILITY(NAME_CONST('name',TIME'00:00:00')) +5 +SELECT COERCIBILITY(NAME_CONST('name',15)); +COERCIBILITY(NAME_CONST('name',15)) +5 +SELECT CONCAT(NAME_CONST('name',15),'오'); +CONCAT(NAME_CONST('name',15),'오') +15오 +SET NAMES latin1; +# # Start of 10.2 tests # # diff --git a/mysql-test/r/get_diagnostics.result b/mysql-test/r/get_diagnostics.result index 01fed8c846b..a75b775297c 100644 --- a/mysql-test/r/get_diagnostics.result +++ b/mysql-test/r/get_diagnostics.result @@ -590,7 +590,7 @@ DROP PROCEDURE p1; SHOW WARNINGS; Level Code Message Error 54321 MESSAGE_TEXT text -Note 4091 At line 16 in test.p1 +Note 4092 At line 16 in test.p1 CREATE PROCEDURE p1() BEGIN DECLARE var INT; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 258b8e84eb4..5b239f09172 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1428,7 +1428,7 @@ Warnings: Note 1305 FUNCTION test.test_function does not exist drop view if exists v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' create table test (col1 varchar(30)); create function test_function() returns varchar(30) begin diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index c9e3dc7b253..8e572c5fede 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -717,3 +717,31 @@ insert ignore into t1 values (1,12); Warnings: Warning 1062 Duplicate entry '1' for key 'f1' DROP TABLE t1; +# +# MDEV-13290 Assertion Assertion `!is_set() || (m_status == DA_OK_BULK +# && is_bulk_op())' or `! is_set()' failed +# +SET @save_mode= @@sql_mode; +SET sql_mode= 'STRICT_ALL_TABLES'; +CREATE TABLE t1 (f1 INT DEFAULT 0, f2 INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; +REPLACE INTO v1 SET f2 = 1; +ERROR 22007: Truncated incorrect DOUBLE value: 'x' +SELECT * from t1; +f1 f2 +drop view v1; +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = cast('' as decimal) WITH CHECK OPTION; +REPLACE INTO v1 SET f2 = 1; +ERROR 22007: Truncated incorrect DECIMAL value: '' +SELECT * from t1; +f1 f2 +drop view v1; +SELECT 0,0 INTO OUTFILE 't1.txt'; +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; +LOAD DATA INFILE 't1.txt' INTO TABLE v1; +ERROR 22007: Truncated incorrect DOUBLE value: 'x' +SELECT * from t1; +f1 f2 +drop view v1; +drop table t1; +SET @@sql_mode= @save_mode; diff --git a/mysql-test/r/locale.result b/mysql-test/r/locale.result index 95c1f51fd87..a02e80ed21e 100644 --- a/mysql-test/r/locale.result +++ b/mysql-test/r/locale.result @@ -223,5 +223,21 @@ SELECT * FROM non_existent; ERROR 42S02: Table 'test.non_existent' doesn't exist SET lc_time_names=@old_50915_lc_time_names; # -# End of 5.6 tests +# End of 10.0 tests +# +# +# End of 10.1 tests +# +create view v1 as select +format(123456789,2) as b, +format(123456789,2,'rm_CH') as b1; +select * from v1; +b b1 +123,456,789.00 123'456'789,00 +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 format(123456789,2) AS `b`,format(123456789,2,'rm_CH') AS `b1` utf8 utf8_general_ci +drop view v1; +# +# End of 10.2 tests # diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result index 0a33510fe77..392a1bce234 100644 --- a/mysql-test/r/log_tables-big.result +++ b/mysql-test/r/log_tables-big.result @@ -1,3 +1,4 @@ +set @log_output.saved = @@global.log_output; set @@global.log_output = 'TABLE'; connect con1,localhost,root,,; connect con2,localhost,root,,; @@ -11,21 +12,21 @@ set session long_query_time=1; select get_lock('bug27638', 2); get_lock('bug27638', 2) 0 -select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:00:01', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 2)'; qt sql_text OK select get_lock('bug27638', 2) select get_lock('bug27638', 60); get_lock('bug27638', 60) 0 -select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:00:59', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 60)'; qt sql_text OK select get_lock('bug27638', 60) select get_lock('bug27638', 101); get_lock('bug27638', 101) 0 -select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:01:40', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 101)'; qt sql_text OK select get_lock('bug27638', 101) @@ -36,4 +37,4 @@ release_lock('bug27638') connection default; disconnect con1; disconnect con2; -set @@global.log_output=default; +set @@global.log_output = @log_output.saved; diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result index 474dd5adf02..90048eb3438 100644 --- a/mysql-test/r/partition_symlink.result +++ b/mysql-test/r/partition_symlink.result @@ -33,11 +33,15 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp/' INSERT INTO t1 VALUES (0), (1), (2); ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; -ERROR HY000: Tables have different definitions ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2; ERROR HY000: Tables have different definitions +SELECT * FROM t1; +a +1 +2 SELECT * FROM t2; a +0 DROP TABLE t1, t2; # Creating two non colliding tables mysqltest2.t1 and test.t1 # test.t1 have partitions in mysqltest2-directory! diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index f7568c1b0f7..9644a8afe8d 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -415,7 +415,7 @@ select @@profiling; drop table if exists t1, t2, t3; drop view if exists v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' drop function if exists f1; set session profiling = OFF; set global profiling_history_size= @start_value; diff --git a/mysql-test/r/range_interrupted-13751.result b/mysql-test/r/range_interrupted-13751.result new file mode 100644 index 00000000000..f4f995721ad --- /dev/null +++ b/mysql-test/r/range_interrupted-13751.result @@ -0,0 +1,16 @@ +CREATE TABLE t1 (i INT AUTO_INCREMENT, c VARCHAR(1), KEY(i), KEY(c,i)) ENGINE=MyISAM; +INSERT INTO t1 (c) VALUES ('a'),('b'),('c'),('d'); +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +set @old_dbug=@@session.debug_dbug; +set debug_dbug="+d,kill_join_init_read_record"; +SELECT 1 FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.c = alias2.c OR alias1.i <= 1 +; +ERROR 70100: Query execution was interrupted +set debug_dbug=@old_dbug; +DROP TABLE t1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 5859bd0fae6..c7156ddae91 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4784,7 +4784,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + 1 + 1) +Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + (1 + 1)) SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; b a 2 3 diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index c9d582bb75a..92be057c62c 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -4795,7 +4795,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + 1 + 1) +Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + (1 + 1)) SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; b a 2 3 diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 5859bd0fae6..c7156ddae91 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -4784,7 +4784,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + 1 + 1) +Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + (1 + 1)) SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; b a 2 3 diff --git a/mysql-test/r/signal.result b/mysql-test/r/signal.result index 9b140b5b33b..671df4b7f17 100644 --- a/mysql-test/r/signal.result +++ b/mysql-test/r/signal.result @@ -1715,7 +1715,7 @@ show warnings $$ Level Code Message Warning 1012 Raising a warning Error 5555 RESIGNAL to not found -Note 4091 At line 9 in test.test_resignal +Note 4092 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1740,7 +1740,7 @@ show warnings $$ Level Code Message Warning 1012 Raising a warning Error 5555 RESIGNAL to error -Note 4091 At line 9 in test.test_resignal +Note 4092 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1789,7 +1789,7 @@ show warnings $$ Level Code Message Error 1012 Raising a not found Error 5555 RESIGNAL to not found -Note 4091 At line 9 in test.test_resignal +Note 4092 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1814,7 +1814,7 @@ show warnings $$ Level Code Message Error 1012 Raising a not found Error 5555 RESIGNAL to error -Note 4091 At line 9 in test.test_resignal +Note 4092 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1863,7 +1863,7 @@ show warnings $$ Level Code Message Error 1012 Raising an error Error 5555 RESIGNAL to not found -Note 4091 At line 9 in test.test_resignal +Note 4092 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1888,7 +1888,7 @@ show warnings $$ Level Code Message Error 1012 Raising an error Error 5555 RESIGNAL to error -Note 4091 At line 9 in test.test_resignal +Note 4092 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1931,7 +1931,7 @@ show warnings $$ Level Code Message Warning 1264 Out of range value for column 'a' at row 1 Error 5555 RESIGNAL to a not found -Note 4091 At line 8 in test.test_resignal +Note 4092 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1953,7 +1953,7 @@ show warnings $$ Level Code Message Warning 1264 Out of range value for column 'a' at row 1 Error 5555 RESIGNAL to an error -Note 4091 At line 8 in test.test_resignal +Note 4092 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2004,7 +2004,7 @@ show warnings $$ Level Code Message Error 1329 No data - zero rows fetched, selected, or processed Error 5555 RESIGNAL to a not found -Note 4091 At line 10 in test.test_resignal +Note 4092 At line 10 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2030,7 +2030,7 @@ show warnings $$ Level Code Message Error 1329 No data - zero rows fetched, selected, or processed Error 5555 RESIGNAL to an error -Note 4091 At line 10 in test.test_resignal +Note 4092 At line 10 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2073,7 +2073,7 @@ show warnings $$ Level Code Message Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to a not found -Note 4091 At line 8 in test.test_resignal +Note 4092 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2095,7 +2095,7 @@ show warnings $$ Level Code Message Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to an error -Note 4091 At line 8 in test.test_resignal +Note 4092 At line 8 in test.test_resignal drop procedure test_resignal $$ # # More complex cases @@ -2142,7 +2142,7 @@ ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message Error 9999 Hi, I am a useless error message -Note 4091 At line 7 in test.peter_p2 +Note 4092 At line 7 in test.peter_p2 drop procedure peter_p1 $$ drop procedure peter_p2 $$ CREATE PROCEDURE peter_p1 () @@ -2198,16 +2198,16 @@ Level Code Message Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' -Note 4091 At line 8 in test.peter_p1 +Note 4092 At line 8 in test.peter_p1 ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' -Note 4091 At line 8 in test.peter_p1 +Note 4092 At line 8 in test.peter_p1 Error 9999 Hi, I am a useless error message -Note 4091 At line 10 in test.peter_p2 +Note 4092 At line 10 in test.peter_p2 drop procedure peter_p1 $$ drop procedure peter_p2 $$ drop procedure if exists peter_p3 $$ @@ -2225,7 +2225,7 @@ show warnings $$ Level Code Message Error 1 Original Error 2 Original -Note 4091 At line 4 in test.peter_p3 +Note 4092 At line 4 in test.peter_p3 drop procedure peter_p3 $$ drop table t_warn; drop table t_cursor; diff --git a/mysql-test/r/signal_demo3.result b/mysql-test/r/signal_demo3.result index 2e1943b546e..a98d587937c 100644 --- a/mysql-test/r/signal_demo3.result +++ b/mysql-test/r/signal_demo3.result @@ -79,23 +79,23 @@ show warnings; Level Code Message Error 1051 Unknown table 'demo.oops_it_is_not_here' Error 1644 Oops in proc_9 -Note 4091 At line 4 in demo.proc_9 +Note 4092 At line 4 in demo.proc_9 Error 1644 Oops in proc_8 -Note 4091 At line 4 in demo.proc_8 +Note 4092 At line 4 in demo.proc_8 Error 1644 Oops in proc_7 -Note 4091 At line 4 in demo.proc_7 +Note 4092 At line 4 in demo.proc_7 Error 1644 Oops in proc_6 -Note 4091 At line 4 in demo.proc_6 +Note 4092 At line 4 in demo.proc_6 Error 1644 Oops in proc_5 -Note 4091 At line 4 in demo.proc_5 +Note 4092 At line 4 in demo.proc_5 Error 1644 Oops in proc_4 -Note 4091 At line 4 in demo.proc_4 +Note 4092 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4091 At line 4 in demo.proc_3 +Note 4092 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4091 At line 4 in demo.proc_2 +Note 4092 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4091 At line 4 in demo.proc_1 +Note 4092 At line 4 in demo.proc_1 SET @@session.max_error_count = 5; SELECT @@session.max_error_count; @@session.max_error_count @@ -104,11 +104,11 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4091 At line 4 in demo.proc_3 +Note 4092 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4091 At line 4 in demo.proc_2 +Note 4092 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4091 At line 4 in demo.proc_1 +Note 4092 At line 4 in demo.proc_1 SET @@session.max_error_count = 7; SELECT @@session.max_error_count; @@session.max_error_count @@ -117,13 +117,13 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4091 At line 4 in demo.proc_4 +Note 4092 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4091 At line 4 in demo.proc_3 +Note 4092 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4091 At line 4 in demo.proc_2 +Note 4092 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4091 At line 4 in demo.proc_1 +Note 4092 At line 4 in demo.proc_1 SET @@session.max_error_count = 9; SELECT @@session.max_error_count; @@session.max_error_count @@ -132,15 +132,15 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4091 At line 4 in demo.proc_5 +Note 4092 At line 4 in demo.proc_5 Error 1644 Oops in proc_4 -Note 4091 At line 4 in demo.proc_4 +Note 4092 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4091 At line 4 in demo.proc_3 +Note 4092 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4091 At line 4 in demo.proc_2 +Note 4092 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4091 At line 4 in demo.proc_1 +Note 4092 At line 4 in demo.proc_1 drop database demo; SET @@global.max_error_count = @start_global_value; SELECT @@global.max_error_count; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index e3a02503ad0..f0bc1874850 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1990,8 +1990,8 @@ Warning 1264 Out of range value for column 'a' at row 1 Note 1292 Truncated incorrect INTEGER value: '222222 ' Warning 1264 Out of range value for column 'b' at row 1 Error 1048 Column 'c' cannot be null -Note 4091 At line 6 in test.t1_bi -Note 4091 At line 2 in test.p1 +Note 4092 At line 6 in test.t1_bi +Note 4092 At line 2 in test.p1 DROP TABLE t1; DROP TABLE t2; diff --git a/mysql-test/r/sp-group.result b/mysql-test/r/sp-group.result index 3ed3f812267..535e67046d8 100644 --- a/mysql-test/r/sp-group.result +++ b/mysql-test/r/sp-group.result @@ -3,7 +3,7 @@ Warnings: Note 1051 Unknown table 'test.t1' drop view if exists view_t1; Warnings: -Note 4089 Unknown VIEW: 'test.view_t1' +Note 4090 Unknown VIEW: 'test.view_t1' SET sql_mode=ONLY_FULL_GROUP_BY; CREATE TABLE t1 ( pk INT, diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index a1af26cdda0..cb2237699fb 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -3211,7 +3211,7 @@ drop procedure bug10961| DROP PROCEDURE IF EXISTS bug6866| DROP VIEW IF EXISTS tv| Warnings: -Note 4089 Unknown VIEW: 'test.tv' +Note 4090 Unknown VIEW: 'test.tv' DROP TABLE IF EXISTS tt1,tt2,tt3| Warnings: Note 1051 Unknown table 'test.tt1' @@ -7823,7 +7823,7 @@ ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show warnings; Level Code Message Error 1062 Duplicate entry '2' for key 'PRIMARY' -Note 4091 At line 5 in test.p1 +Note 4092 At line 5 in test.p1 select * from t1; id 1 diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 1764ea6a7dd..e8063ee736c 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -237,3 +237,15 @@ DROP DATABASE x; CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM DATA DIRECTORY "MYSQLTEST_VARDIR/tmp"; DROP TABLE test.t1; +use test; +create table t1(c1 int, c2 int, c3 varchar(100)) engine=MyISAM data directory='MYSQL_TMP_DIR' index directory = 'MYSQL_TMP_DIR'; +insert t1 values (1,2,3), (2,3,4), (3,4,5), (4,5,6), (5,6,7), (6,7,8), (7,8,9); +alter online table t1 delay_key_write=1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` varchar(100) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 DATA DIRECTORY='MYSQL_TMP_DIR/' INDEX DIRECTORY='MYSQL_TMP_DIR/' +drop table t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 36abece7743..24c669308cd 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5236,7 +5236,7 @@ CREATE TABLE t4 (i4 INT); INSERT INTO t4 VALUES (1),(2); DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 ); CREATE VIEW v2 AS select v1_field1 from t4 join v1; prepare my_stmt from "select v1_field1 from v2"; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 400256f2ab7..5d805ac572a 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -353,7 +353,7 @@ ERROR 23000: Duplicate entry '11' for key 'a' SHOW WARNINGS; Level Code Message -Note 4091 At line 4 in test.f1 +Note 4092 At line 4 in test.f1 Error 1062 Duplicate entry '11' for key 'a' DROP TABLE t1; diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 743513092e2..6434b26dba1 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3186,9 +3186,6 @@ fld 2 DROP TABLE t1; # -# Start of 10.3 tests -# -# # MDEV-13240 Wrong warning with MAX(datetime_field) OVER (...) # CREATE TABLE t1 (dt DATETIME); @@ -3197,3 +3194,85 @@ SELECT MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) NULL DROP TABLE t1; +# +# MDEV-13358 FIRST_VALUE throws SQL Error (1292): Incorrect datetime value +# +CREATE TABLE IF NOT EXISTS `fv_test` ( +`SOME_DATE` datetime NOT NULL +); +INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56'); +CREATE TABLE fv_result +SELECT +FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate +FROM fv_test; +SHOW CREATE TABLE fv_result; +Table Create Table +fv_result CREATE TABLE `fv_result` ( + `somedate` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM fv_result; +somedate +2017-07-20 12:47:56 +DROP TABLE fv_test, fv_result; +# +# MDEV-13649: Server crashes in set_field_to_null_with_conversions or in Field::set_notnull +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0),(1),(2); +SELECT LEAD(a) OVER (PARTITION BY a) as lead, +a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +FROM t1; +lead a_and_lead_part +NULL 0 +NULL NULL +NULL NULL +SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order +FROM t1 +ORDER BY a; +a_or_lead_order +1 +1 +1 +SELECT a AND LEAD(a) OVER (ORDER BY a) AS a_and_lead_order +FROM t1 +ORDER BY a; +a_and_lead_order +0 +1 +NULL +SELECT a XOR LEAD(a) OVER (ORDER BY a) AS a_xor_lead_order +FROM t1 +ORDER BY a; +a_xor_lead_order +1 +0 +NULL +SELECT NOT LEAD(a) OVER (ORDER BY a) AS not_lead_order +FROM t1 +ORDER BY a; +not_lead_order +0 +0 +NULL +SELECT LEAD(a) OVER (ORDER BY a) is not null AS is_not_null_lead_order +FROM t1 +ORDER BY a; +is_not_null_lead_order +1 +1 +0 +drop table t1; +# +# MDEV-13354: Server crashes in find_field_in_tables upon PS with window function and subquery +# +CREATE TABLE t1 (i INT, a char); +INSERT INTO t1 VALUES (1, 'a'),(2, 'b'); +PREPARE stmt FROM "SELECT row_number() over (partition by i order by i), i FROM (SELECT * from t1) as sq"; +EXECUTE stmt; +row_number() over (partition by i order by i) i +1 1 +1 2 +DROP TABLE t1; +# +# Start of 10.3 tests +# diff --git a/mysql-test/r/win_as_arg_to_aggregate_func.result b/mysql-test/r/win_as_arg_to_aggregate_func.result new file mode 100644 index 00000000000..e3f0091d6d2 --- /dev/null +++ b/mysql-test/r/win_as_arg_to_aggregate_func.result @@ -0,0 +1,114 @@ +create table t1 (i int); +insert into t1 values (5),(6),(0); +# +# Try out all set functions with window functions as arguments. +# Any such usage should return an error. +# +select MIN( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select MIN(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select MAX( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select MAX(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select SUM( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select SUM(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select AVG( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select AVG(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select COUNT( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select COUNT(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select BIT_AND( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select BIT_OR( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select BIT_XOR( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STD( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STDDEV( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STDDEV_POP( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STDDEV_SAMP(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select VARIANCE(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select VAR_POP(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select VAR_SAMP(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select GROUP_CONCAT(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +# +# Test that partition instead of order by in over doesn't change result. +# +select SUM( SUM(i) OVER (PARTITION BY i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +# +# Test that no arguments in OVER() clause lead to crash in this case. +# +select SUM( SUM(i) OVER () ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +drop table t1; +# +# MDEV-13774: Server Crash on Execuate of SQL Statement +# +create table t1 (i int); +insert into t1 values (5),(6),(0); +select SUM( +IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0 +AND +SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0, +1, +0) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +# +# A way to get the aggregation result. +# +select i, IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1 +order by i; +i if_col +0 0 +5 1 +6 1 +select sum(if_col) +from (select IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1) tmp; +sum(if_col) +2 +drop table t1; diff --git a/mysql-test/r/win_insert_select.result b/mysql-test/r/win_insert_select.result index e04088bd96d..bb25d052840 100644 --- a/mysql-test/r/win_insert_select.result +++ b/mysql-test/r/win_insert_select.result @@ -1,16 +1,16 @@ CREATE TABLE t1 (c1 INT, c2 VARCHAR(30)); PREPARE populate_table FROM "INSERT into t1 values (1, 'manual_insert_1'), (4, 'manual_insert_2')"; -INSERT INTO t1 SELECT row_number() over(), "should_have_NULL" FROM t1; -INSERT INTO t1 SELECT 1 + row_number() over(), "should_have_2" FROM t1; +INSERT INTO t1 SELECT row_number() over(), "should_not_add_any_rows" FROM t1; +INSERT INTO t1 SELECT 1 + row_number() over(), "should_not_add_any_rows" FROM t1; EXECUTE populate_table; -INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 4 times [11-14]" FROM t1; +INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 2 times [11-12]" FROM t1; SELECT c1, c2 FROM t1 ORDER BY c2, c1; c1 c2 1 manual_insert_1 4 manual_insert_2 -11 should repeat 4 times [11-14] -12 should repeat 4 times [11-14] +11 should repeat 2 times [11-12] +12 should repeat 2 times [11-12] DELETE FROM t1; EXECUTE populate_table; INSERT INTO t1 diff --git a/mysql-test/suite/encryption/r/innodb-spatial-index.result b/mysql-test/suite/encryption/r/innodb-spatial-index.result index 852be0b9a73..c2a41ac4c2e 100644 --- a/mysql-test/suite/encryption/r/innodb-spatial-index.result +++ b/mysql-test/suite/encryption/r/innodb-spatial-index.result @@ -9,10 +9,14 @@ ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong creat DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB; -CREATE SPATIAL INDEX b on t1(coordinate); -ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options") -ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate); +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY; ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options") +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE; +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED' +ALTER TABLE t1 ADD SPATIAL INDEX(coordinate); +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED' +CREATE SPATIAL INDEX b on t1(coordinate); +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED' DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=DEFAULT ENGINE=INNODB; diff --git a/mysql-test/suite/encryption/t/innodb-spatial-index.test b/mysql-test/suite/encryption/t/innodb-spatial-index.test index de78461c765..6b6191c69cb 100644 --- a/mysql-test/suite/encryption/t/innodb-spatial-index.test +++ b/mysql-test/suite/encryption/t/innodb-spatial-index.test @@ -31,12 +31,17 @@ DROP TABLE t1; # CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB; +# FIXME: MDEV-13851 Encrypted table refuses some form of ALGORITHM=COPY, +# but allows rebuild by FORCE --replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/ --error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY; +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE; +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 ADD SPATIAL INDEX(coordinate); +--error ER_ILLEGAL_HA_CREATE_OPTION CREATE SPATIAL INDEX b on t1(coordinate); ---replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate); DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, @@ -69,4 +74,4 @@ INSERT INTO t2 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)') SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0; SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; -DROP TABLE t1, t2;
\ No newline at end of file +DROP TABLE t1, t2; diff --git a/mysql-test/suite/federated/net_thd_crash-12951.result b/mysql-test/suite/federated/net_thd_crash-12951.result new file mode 100644 index 00000000000..573ac96efff --- /dev/null +++ b/mysql-test/suite/federated/net_thd_crash-12951.result @@ -0,0 +1,11 @@ +set global query_cache_size= 16*1024*1024; +set global query_cache_type= 1; +create table t1 (i int) engine=innodb; +create table t2 (i int) engine=federated +CONNECTION="mysql://root@localhost:MASTER_MYPORT/test/t1"; +select * from t2; +i +drop table t2; +drop table t1; +set global query_cache_type= default; +set global query_cache_size= default; diff --git a/mysql-test/suite/federated/net_thd_crash-12951.test b/mysql-test/suite/federated/net_thd_crash-12951.test new file mode 100644 index 00000000000..81cd826686e --- /dev/null +++ b/mysql-test/suite/federated/net_thd_crash-12951.test @@ -0,0 +1,23 @@ +# +# MDEV-12951 Server crash [mysqld got exception 0xc0000005] +# + +--source include/have_innodb.inc + +set global query_cache_size= 16*1024*1024; +set global query_cache_type= 1; + +create table t1 (i int) engine=innodb; +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create table t2 (i int) engine=federated + CONNECTION="mysql://root@localhost:$MASTER_MYPORT/test/t1"; + +select * from t2; + +source include/restart_mysqld.inc; + +drop table t2; +drop table t1; + +set global query_cache_type= default; +set global query_cache_size= default; diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 8b861011dd3..5a27cb65b3d 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -4314,7 +4314,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7566,7 +7566,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21312,7 +21312,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index 082c8aeb5f3..a2af9082c72 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -4315,7 +4315,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7567,7 +7567,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21314,7 +21314,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index 949aaabf864..3290b3dd36a 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -4784,7 +4784,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -8387,7 +8387,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -22989,7 +22989,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/innodb/r/innodb_bug46000.result b/mysql-test/suite/innodb/r/innodb_bug46000.result index 7c5ef13f3dc..86e8766fb8f 100644 --- a/mysql-test/suite/innodb/r/innodb_bug46000.result +++ b/mysql-test/suite/innodb/r/innodb_bug46000.result @@ -6,7 +6,7 @@ show warnings; Level Code Message Warning 1280 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. Error 1280 Incorrect index name 'GEN_CLUST_INDEX' -Warning 1030 Got error 124 "Wrong index given to function" from storage engine InnoDB +Warning 1030 Got error 124 "Wrong index given to a function" from storage engine InnoDB create table bug46000(id int) engine=innodb; create index GEN_CLUST_INDEX on bug46000(id); ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX' diff --git a/mysql-test/suite/innodb/r/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result index 2bc8769092f..a2d5bbef8df 100644 --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@ -67,7 +67,7 @@ SELECT COUNT(*) FROM t3; COUNT(*) 0 RENAME TABLE t1 TO tee_one; -ERROR HY000: Error on rename of './test/t1' to './test/tee_one' (errno: 155 "The table does not exist in engine") +ERROR HY000: Error on rename of './test/t1' to './test/tee_one' (errno: 155 "The table does not exist in the storage engine") DROP TABLE t1; Warnings: Warning 1932 Table 'test.t1' doesn't exist in engine diff --git a/mysql-test/suite/innodb/t/log_data_file_size.test b/mysql-test/suite/innodb/t/log_data_file_size.test index 7928fc45520..f01e013ddfa 100644 --- a/mysql-test/suite/innodb/t/log_data_file_size.test +++ b/mysql-test/suite/innodb/t/log_data_file_size.test @@ -64,6 +64,9 @@ truncate(FILE, $page_size * 4); close FILE; open(FILE, "+<", "$ENV{'MYSQLD_DATADIR'}test/ibd4f.ibd") or die; truncate(FILE, $page_size * 4 + 1234); +# Work around MDEV-12699 and ensure that the truncated page is all-zero. +sysseek(FILE, $page_size * 4, 0); +syswrite(FILE, chr(0) x 1234); close FILE; open(FILE, "+<", "$ENV{'MYSQLD_DATADIR'}test/ibd5.ibd") or die; truncate(FILE, $page_size * 5); diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result index abc3c5b5f05..17f1f7e1b06 100644 --- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result @@ -47,10 +47,20 @@ VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140, ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) @@ -140,8 +150,14 @@ c1 ST_Astext(c2) ST_Astext(c4) 1 POINT(1000 1000) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -166,8 +182,14 @@ tab 1 idx4 1 c44 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c44 A # 10 NULL BTREE ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -210,7 +232,11 @@ DELETE FROM tab WHERE MBREquals(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) ALTER TABLE tab DROP PRIMARY KEY; +affected rows: 4 +info: Records: 4 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD PRIMARY KEY(c2) ; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) @@ -254,7 +280,11 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) OR c1 ST_Astext(c2) ST_Astext(c4) INSERT INTO tab SELECT * FROM tab1; ALTER TABLE tab DROP PRIMARY KEY; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE tab DROP INDEX idx2; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2; ERROR HY000: Illegal parameter data types int and geometry for operation '=' @@ -309,8 +339,14 @@ tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE DELETE FROM tab; ALTER TABLE tab ADD PRIMARY KEY(c2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 CREATE SPATIAL INDEX idx2 ON tab(c2 ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -342,9 +378,17 @@ ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); DELETE FROM tab; ALTER TABLE tab DROP PRIMARY KEY ; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab DROP KEY const_1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD PRIMARY KEY(c5(10)); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10)); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -422,13 +466,25 @@ ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status OK ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=COPY +ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -462,6 +518,8 @@ DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c2) ST_Astext(c4) ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -510,8 +568,14 @@ test.tab analyze status OK SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'); SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)'); ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL; +affected rows: 8 +info: Records: 8 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL; +affected rows: 8 +info: Records: 8 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -588,7 +652,11 @@ DROP TABLE tab; CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB; ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE parent; Table Create Table parent CREATE TABLE `parent` ( @@ -616,7 +684,11 @@ DROP table child,parent; CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB; ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE parent; Table Create Table parent CREATE TABLE `parent` ( @@ -644,11 +716,12 @@ create table t1 (c1 int) engine=innodb; insert into t1 values(NULL); alter table t1 add b geometry, add spatial index(b), algorithm=inplace; ERROR 42000: All parts of a SPATIAL index must be NOT NULL -alter table t1 add b geometry, algorithm=inplace; -update t1 set b = st_geomfromtext('point(0 0)'); -alter table t1 add spatial index(b), algorithm=inplace; -ERROR 42000: All parts of a SPATIAL index must be NOT NULL -delete from t1; +alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=inplace; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 DROP table t1; create table t1 (c1 int) engine=innodb; insert into t1 values(NULL); @@ -656,11 +729,8 @@ alter table t1 add b geometry, add spatial index(b), algorithm=copy; ERROR 42000: All parts of a SPATIAL index must be NOT NULL alter table t1 add b geometry not null, add spatial index(b), algorithm=copy; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -update t1 set b = st_geomfromtext('point(0 0)'); -ERROR 42S22: Unknown column 'b' in 'field list' -alter table t1 add spatial index(b), algorithm=copy; -ERROR 42000: Key column 'b' doesn't exist in table -delete from t1; +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=copy; DROP table t1; # # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED diff --git a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test index 48052da021f..2b834ac69a6 100644 --- a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test +++ b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test @@ -86,6 +86,7 @@ ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); +--enable_info ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); @@ -95,7 +96,7 @@ ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; - +--disable_info # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); @@ -195,22 +196,26 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1); DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); +--enable_info ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL; ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL; ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL; +--disable_info SHOW CREATE TABLE tab; --replace_column 7 # SHOW INDEX FROM tab; +--enable_info ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL; ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL; ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL; +--disable_info SHOW CREATE TABLE tab; @@ -234,9 +239,11 @@ DELETE FROM tab WHERE MBREquals(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); +--enable_info ALTER TABLE tab DROP PRIMARY KEY; ALTER TABLE tab ADD PRIMARY KEY(c2) ; +--disable_info SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); @@ -298,9 +305,11 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) OR INSERT INTO tab SELECT * FROM tab1; +--enable_info ALTER TABLE tab DROP PRIMARY KEY; ALTER TABLE tab DROP INDEX idx2; +--disable_info # Check spatial index on temp tables --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION @@ -346,11 +355,13 @@ SHOW INDEX FROM tab; DELETE FROM tab; +--enable_info ALTER TABLE tab ADD PRIMARY KEY(c2); CREATE SPATIAL INDEX idx2 ON tab(c2 ASC); ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2); +--disable_info SHOW CREATE TABLE tab; @@ -366,6 +377,7 @@ ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); DELETE FROM tab; +--enable_info ALTER TABLE tab DROP PRIMARY KEY ; ALTER TABLE tab DROP KEY const_1; @@ -373,6 +385,7 @@ ALTER TABLE tab DROP KEY const_1; ALTER TABLE tab ADD PRIMARY KEY(c5(10)); ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10)); +--disable_info SHOW CREATE TABLE tab; @@ -449,6 +462,7 @@ ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 ANALYZE TABLE tab; +--enable_info ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); @@ -470,6 +484,7 @@ ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE; +--disable_info SHOW CREATE TABLE tab; @@ -487,10 +502,12 @@ DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; +--enable_info # --error ER_CANT_CREATE_GEOMETRY_OBJECT # ALTER TABLE tab MODIFY COLUMN c2 POLYGON NOT NULL; ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL; +--disable_info SHOW CREATE TABLE tab; @@ -524,11 +541,13 @@ SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)'); # When Point type data exist in the column allow DDL operation +--enable_info ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL; ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL; ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL; +--disable_info SHOW CREATE TABLE tab; @@ -575,6 +594,7 @@ DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); +--enable_info # --error ER_SPATIAL_MUST_HAVE_GEOM_COL --error ER_WRONG_ARGUMENTS ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL; @@ -582,6 +602,7 @@ ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL; # --error ER_SPATIAL_MUST_HAVE_GEOM_COL --error ER_WRONG_ARGUMENTS ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL; +--disable_info # Test InnoDB to Myisam to InnoDB ALTER TABLE tab ENGINE Myisam; @@ -619,9 +640,11 @@ CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB; +--enable_info ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC); ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +--disable_info SHOW CREATE TABLE parent; @@ -650,9 +673,11 @@ CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB; +--enable_info ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ; ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +--disable_info SHOW CREATE TABLE parent; @@ -682,29 +707,13 @@ alter table t1 add b geometry, add spatial index(b), algorithm=inplace; # Add spatial index fail, since there's invalid geo data. # The case has to be commented because it no longer fails and following cases # don't expect the effect of such a statement. -#--error ER_CANT_CREATE_GEOMETRY_OBJECT -# alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; - -# Add a geometry column. -alter table t1 add b geometry, algorithm=inplace; - -# Add spatial index fail, since there's a NULL or invalid geo data. -# The case has to be commented because it no longer fails and following cases -# don't expect the effect of such a statement. -#--error ER_CANT_CREATE_GEOMETRY_OBJECT -#alter table t1 add spatial index(b), algorithm=inplace; - -# Update invalide geo data to point(0 0). -update t1 set b = st_geomfromtext('point(0 0)'); - -# Add spatial index success. ---error ER_SPATIAL_CANT_HAVE_NULL -alter table t1 add spatial index(b), algorithm=inplace; - -# Delete rows. -delete from t1; +--error ER_CANT_CREATE_GEOMETRY_OBJECT + alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; -#cleanup +--enable_info +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=inplace; +--disable_info DROP table t1; # Check add spatial index when table already has rows (copy). @@ -716,32 +725,11 @@ insert into t1 values(NULL); alter table t1 add b geometry, add spatial index(b), algorithm=copy; # Add spatial index fail, since there's a NULL or invalid geo data. -# --error ER_INVALID_USE_OF_NULL --error ER_CANT_CREATE_GEOMETRY_OBJECT alter table t1 add b geometry not null, add spatial index(b), algorithm=copy; -# Add a geometry column. -# --error ER_INVALID_USE_OF_NULL -# alter table t1 add b geometry not null, algorithm=copy; - -# Add spatial index. -# The case has to be commented because it no longer fails and following cases -# don't expect the effect of such a statement. -#--error ER_CANT_CREATE_GEOMETRY_OBJECT -#alter table t1 add spatial index(b), algorithm=copy; - -# Update invalide geo data to point(0 0). ---error ER_BAD_FIELD_ERROR -update t1 set b = st_geomfromtext('point(0 0)'); - -# Add spatial index success. ---error ER_KEY_COLUMN_DOES_NOT_EXITS -alter table t1 add spatial index(b), algorithm=copy; - -# Delete rows. -delete from t1; - -#cleanup +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=copy; DROP table t1; --echo # diff --git a/mysql-test/suite/innodb_zip/r/create_options.result b/mysql-test/suite/innodb_zip/r/create_options.result index 9b9283d5df1..b217388b6dc 100644 --- a/mysql-test/suite/innodb_zip/r/create_options.result +++ b/mysql-test/suite/innodb_zip/r/create_options.result @@ -261,14 +261,14 @@ Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB # can be set to default values during strict mode. CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL, `f1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 -SHOW WARNINGS; -Level Code Message ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; Level Code Message diff --git a/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result b/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result index 1a02c1a711a..d766ecceaac 100644 --- a/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result +++ b/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result @@ -461,10 +461,6 @@ i f c drop table t; set global innodb_file_per_table = 1; call mtr.add_suppression("does not exist in the InnoDB internal"); -Warnings: -Error 145 Table './mtr/test_suppressions' is marked as crashed and should be repaired -Error 1194 Table 'test_suppressions' is marked as crashed and should be repaired -Error 1034 1 client is using or hasn't closed the table properly set global innodb_file_per_table = on; "1. Hit crash point on completing drop of all indexes before creation" " of index is commenced." @@ -520,3 +516,4 @@ check table t; Table Op Msg_type Msg_text test.t check Error Table 'test.t' doesn't exist test.t check status Operation failed +set global innodb_file_per_table = 1; diff --git a/mysql-test/suite/innodb_zip/t/create_options.test b/mysql-test/suite/innodb_zip/t/create_options.test index f91cd7d42dd..fce64060df3 100644 --- a/mysql-test/suite/innodb_zip/t/create_options.test +++ b/mysql-test/suite/innodb_zip/t/create_options.test @@ -214,8 +214,8 @@ SHOW WARNINGS; --echo # can be set to default values during strict mode. CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW CREATE TABLE t1; SHOW WARNINGS; +SHOW CREATE TABLE t1; ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; ALTER TABLE t1 ADD COLUMN f2 INT; diff --git a/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc b/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc index 5ffc5438a3e..7d6441b15ad 100644 --- a/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc +++ b/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc @@ -1,4 +1,3 @@ ---disable_warnings DROP TABLE IF EXISTS t_10; DROP TABLE IF EXISTS t_100; DROP TABLE IF EXISTS t_1000; @@ -11,5 +10,3 @@ DROP TABLE IF EXISTS tsp_03; DROP TABLE IF EXISTS tsp_04; DROP TABLE IF EXISTS t_empty; DROP TABLE IF EXISTS t_null; ---enable_warnings - diff --git a/mysql-test/suite/parts/inc/part_exch_tabs.inc b/mysql-test/suite/parts/inc/part_exch_tabs.inc index 378e0c2278d..482c9d378e7 100644 --- a/mysql-test/suite/parts/inc/part_exch_tabs.inc +++ b/mysql-test/suite/parts/inc/part_exch_tabs.inc @@ -1,51 +1,27 @@ ---disable_warnings -DROP TABLE IF EXISTS t_10; -DROP TABLE IF EXISTS t_100; -DROP TABLE IF EXISTS t_1000; -DROP TABLE IF EXISTS tp; -DROP TABLE IF EXISTS tsp; -DROP TABLE IF EXISTS t_empty; -DROP TABLE IF EXISTS t_null; ---enable_warnings +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_10 (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_100 (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_1000 (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_empty (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_null (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; - -eval CREATE TABLE tp (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_part +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_part PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory, PARTITION p1 VALUES LESS THAN (100) $p_data_directory $p_index_directory, PARTITION p2 VALUES LESS THAN (1000) $p_data_directory $p_index_directory); -eval CREATE TABLE tsp (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_subpart +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_subpart PARTITION BY RANGE (a) SUBPARTITION BY HASH(a) (PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory @@ -53,8 +29,7 @@ SUBPARTITION BY HASH(a) SUBPARTITION sp01, SUBPARTITION sp02, SUBPARTITION sp03, - SUBPARTITION sp04), - PARTITION p1 VALUES LESS THAN (100) + SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) (SUBPARTITION sp10 $p_data_directory $p_index_directory, SUBPARTITION sp11 $p_data_directory $p_index_directory, SUBPARTITION sp12 $p_data_directory $p_index_directory, @@ -99,41 +74,13 @@ INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four") INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); -eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; -eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; -eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; -eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; -eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; - -SHOW CREATE TABLE t_10; -SHOW CREATE TABLE t_100; -SHOW CREATE TABLE t_1000; -SHOW CREATE TABLE tp; -SHOW CREATE TABLE tsp; - ---sorted_result -SELECT * FROM t_10; ---sorted_result -SELECT * FROM t_100; ---sorted_result -SELECT * FROM t_1000; ---sorted_result -SELECT * FROM tp; ---sorted_result -SELECT * FROM tp WHERE a< 10; ---sorted_result -SELECT * FROM tp WHERE a BETWEEN 11 AND 100; ---sorted_result -SELECT * FROM tp WHERE a BETWEEN 101 AND 200; ---sorted_result -SELECT * FROM tsp; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; diff --git a/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result b/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result index 9ff4afcfe35..a2a58c22c42 100644 --- a/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result +++ b/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result @@ -1,3 +1,58 @@ +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB DROP TABLE IF EXISTS t_10; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_14.result b/mysql-test/suite/parts/r/partition_exch_qa_14.result index f6866727184..1420982436a 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_14.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_14.result @@ -1,4 +1,198 @@ -use test; +# === Data/Index directories are identical +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +DROP TABLE IF EXISTS t_10; +DROP TABLE IF EXISTS t_100; +DROP TABLE IF EXISTS t_1000; +DROP TABLE IF EXISTS tp; +DROP TABLE IF EXISTS tsp; +DROP TABLE IF EXISTS tsp_00; +DROP TABLE IF EXISTS tsp_01; +DROP TABLE IF EXISTS tsp_02; +DROP TABLE IF EXISTS tsp_03; +DROP TABLE IF EXISTS tsp_04; +DROP TABLE IF EXISTS t_empty; +DROP TABLE IF EXISTS t_null; +# === partition has directories, the table does not +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +ERROR HY000: Tables have different definitions +DROP TABLE IF EXISTS t_10; +DROP TABLE IF EXISTS t_100; +DROP TABLE IF EXISTS t_1000; +DROP TABLE IF EXISTS tp; +DROP TABLE IF EXISTS tsp; +DROP TABLE IF EXISTS tsp_00; +DROP TABLE IF EXISTS tsp_01; +DROP TABLE IF EXISTS tsp_02; +DROP TABLE IF EXISTS tsp_03; +DROP TABLE IF EXISTS tsp_04; +DROP TABLE IF EXISTS t_empty; +DROP TABLE IF EXISTS t_null; +# === the table has directories, partition does not +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: Tables have different definitions DROP TABLE IF EXISTS t_10; @@ -13,7 +207,62 @@ DROP TABLE IF EXISTS tsp_03; DROP TABLE IF EXISTS tsp_04; DROP TABLE IF EXISTS t_empty; DROP TABLE IF EXISTS t_null; -use test; +# === data directory differs +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: Tables have different definitions DROP TABLE IF EXISTS t_10; @@ -28,7 +277,62 @@ DROP TABLE IF EXISTS tsp_03; DROP TABLE IF EXISTS tsp_04; DROP TABLE IF EXISTS t_empty; DROP TABLE IF EXISTS t_null; -use test; +# === index directory differs +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: Tables have different definitions DROP TABLE IF EXISTS t_10; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_15.result b/mysql-test/suite/parts/r/partition_exch_qa_15.result index 87671a74253..355cf43d886 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_15.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_15.result @@ -1,10 +1,65 @@ use test; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; CREATE TABLE t_11 (a INT, b VARCHAR(55), FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE) ENGINE= InnoDB; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Table to exchange with partition has foreign key references: 't_11' -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; DROP TABLE IF EXISTS t_10; DROP TABLE IF EXISTS t_100; DROP TABLE IF EXISTS t_1000; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_2.result b/mysql-test/suite/parts/r/partition_exch_qa_2.result index ea4983db3dc..956cb0af695 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_2.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_2.result @@ -1,4 +1,59 @@ use test; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; SELECT * FROM t_10; a b 1 One @@ -102,20 +157,20 @@ a b CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Tables have different definitions -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= MYISAM SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Tables have different definitions -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= MYISAM PARTITION BY KEY() AS SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Table to exchange with partition is partitioned: 't_11' -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp; ERROR HY000: Table to exchange with partition is partitioned: 'tsp' ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_3.result b/mysql-test/suite/parts/r/partition_exch_qa_3.result index 9f4043a055a..791757c95f2 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_3.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_3.result @@ -1,4 +1,159 @@ use test; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; +SELECT * FROM t_10; +a b +1 One +3 Three +5 Five +9 Nine +SELECT * FROM t_100; +a b +11 Eleven +13 Thirdteen +15 Fifeteen +19 Nineteen +91 Ninety-one +93 Ninety-three +95 Ninety-five +99 Ninety-nine +SELECT * FROM t_1000; +a b +111 Hundred elven +113 Hundred thirdteen +115 Hundred fiveteen +119 Hundred nineteen +131 Hundred thirty-one +133 Hundred thirty-three +135 Hundred thirty-five +139 Hundred thirty-nine +151 Hundred fifty-one +153 Hundred fifty-three +155 Hundred fity-five +159 Hundred fifty-nine +191 Hundred ninety-one +193 Hundred ninety-three +195 Hundred ninety-five +199 Hundred ninety-nine +SELECT * FROM tp; +a b +112 Hundred twelve +114 Hundred fourteen +116 Hundred sixteen +118 Hundred eightteen +12 twelve +122 Hundred twenty-two +124 Hundred twenty-four +126 Hundred twenty-six +128 Hundred twenty-eight +14 Fourteen +16 Sixteen +162 Hundred sixty-two +164 Hundred sixty-four +166 Hundred sixty-six +168 Hundred sixty-eight +18 Eightteen +182 Hundred eighty-two +184 Hundred eighty-four +186 Hundred eighty-six +188 Hundred eighty-eight +2 Two +4 Four +6 Six +8 Eight +SELECT * FROM tsp; +a b +112 Hundred twelve +114 Hundred fourteen +116 Hundred sixteen +118 Hundred eightteen +12 twelve +122 Hundred twenty-two +124 Hundred twenty-four +126 Hundred twenty-six +128 Hundred twenty-eight +14 Fourteen +16 Sixteen +162 Hundred sixty-two +164 Hundred sixty-four +166 Hundred sixty-six +168 Hundred sixty-eight +18 Eightteen +182 Hundred eight-two +184 Hundred eighty-four +186 Hundred eighty-six +188 Hundred eighty-eight +2 Two +4 Four +6 Six +8 Eight +SELECT * FROM tsp_00; +a b +5 Five +SELECT * FROM tsp_01; +a b +1 One +SELECT * FROM tsp_02; +a b +SELECT * FROM tsp_03; +a b +3 Three +SELECT * FROM tsp_04; +a b +9 Nine ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_6.result b/mysql-test/suite/parts/r/partition_exch_qa_6.result index bef277f695a..6de40ee614a 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_6.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_6.result @@ -1,8 +1,62 @@ CREATE USER test2@localhost; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; GRANT USAGE ON *.* TO test2@localhost; GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost; connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -USE test; SHOW GRANTS FOR CURRENT_USER; Grants for test2@localhost GRANT USAGE ON *.* TO 'test2'@'localhost' @@ -81,7 +135,6 @@ connection default; GRANT CREATE ON test.* TO test2@localhost; REVOKE DROP ON test.* FROM test2@localhost; connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -USE test; SHOW GRANTS FOR CURRENT_USER; Grants for test2@localhost GRANT USAGE ON *.* TO 'test2'@'localhost' diff --git a/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test index fa956f19aec..c625ad93775 100644 --- a/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test +++ b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test @@ -1,21 +1,17 @@ # Author: Horst Hunger # Created: 2010-07-05 ---source include/have_innodb.inc ---source include/have_partition.inc +source include/have_innodb.inc; +source include/have_partition.inc; let $engine_table= MYISAM; let $engine_part= InnoDB; let $engine_subpart= InnoDB; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; ---error 1497 +error ER_MIX_HANDLER_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_14.test b/mysql-test/suite/parts/t/partition_exch_qa_14.test index 7c6699a0a72..8d9f201f1db 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_14.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_14.test @@ -1,94 +1,66 @@ # Author: Horst Hunger # Created: 2010-07-13 ---source include/not_windows.inc ---source include/have_partition.inc ---source include/have_symlink.inc +source include/not_windows.inc; +source include/have_partition.inc; +source include/have_symlink.inc; let $engine_table= MYISAM; let $engine_part= MYISAM; let $engine_subpart= MYISAM; - -# DATA DIRECTORY -# Make directory for partition data -let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; ---mkdir $data_dir_path -let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; -let $data_directory= DATA DIRECTORY = '$data_dir_path'; - -# INDEX DIRECTORY -# Make directory for partition index -let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; ---mkdir $idx_dir_path -let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; -let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; - -use test; - ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log - ---error ER_TABLES_DIFFERENT_METADATA -ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; - ---source suite/parts/inc/part_exch_drop_tabs.inc ---rmdir $data_dir_path ---rmdir $idx_dir_path - -# DATA DIRECTORY -# Make directory for partition data let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; ---mkdir $data_dir_path -let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; - -# INDEX DIRECTORY -# Make directory for partition index let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; ---mkdir $idx_dir_path -let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; - -use test; - ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log - ---error ER_TABLES_DIFFERENT_METADATA -ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; - ---source suite/parts/inc/part_exch_drop_tabs.inc ---rmdir $data_dir_path ---rmdir $idx_dir_path - -# DATA DIRECTORY -# Make directory for partition data -let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; ---mkdir $data_dir_path -let $data_directory= DATA DIRECTORY = '$data_dir_path'; - -# INDEX DIRECTORY -# Make directory for partition index -let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; ---mkdir $idx_dir_path -let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; - -use test; - ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log - ---error ER_TABLES_DIFFERENT_METADATA -ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; - ---source suite/parts/inc/part_exch_drop_tabs.inc ---rmdir $data_dir_path ---rmdir $idx_dir_path - +mkdir $data_dir_path; +mkdir $idx_dir_path; + +echo # === Data/Index directories are identical; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === partition has directories, the table does not; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= ; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= ; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === the table has directories, partition does not; + let $p_data_directory= ; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= ; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === data directory differs; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$idx_dir_path'; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === index directory differs; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= DATA DIRECTORY = '$data_dir_path'; + let $index_directory= DATA DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +rmdir $idx_dir_path; +rmdir $data_dir_path; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_15.test b/mysql-test/suite/parts/t/partition_exch_qa_15.test index 51d09be5ed9..8ea641c8178 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_15.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_15.test @@ -1,8 +1,8 @@ # Author: Horst Hunger # Created: 2010-07-15 ---source include/have_innodb.inc ---source include/have_partition.inc +source include/have_innodb.inc; +source include/have_partition.inc; let $engine_table= InnoDB; let $engine_part= InnoDB; @@ -10,11 +10,7 @@ let $engine_subpart= InnoDB; use test; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; # 21) Foreign Key. # Exchange of partition with table differing in structure. @@ -22,10 +18,8 @@ CREATE TABLE t_11 (a INT, b VARCHAR(55), FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE) ENGINE= InnoDB; #--error ER_TABLES_DIFFERENT_METADATA ---error ER_PARTITION_EXCHANGE_FOREIGN_KEY +error ER_PARTITION_EXCHANGE_FOREIGN_KEY; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings ---source suite/parts/inc/part_exch_drop_tabs.inc +DROP TABLE t_11; +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_2.test b/mysql-test/suite/parts/t/partition_exch_qa_2.test index 83dc0a81fca..1858131ce10 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_2.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_2.test @@ -1,7 +1,7 @@ # Author: Horst Hunger # Created: 2010-07-05 ---source include/have_partition.inc +source include/have_partition.inc; let $engine_table= MYISAM; let $engine_part= MYISAM; @@ -9,72 +9,61 @@ let $engine_subpart= MYISAM; use test; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM t_100; ---sorted_result +sorted_result; SELECT * FROM t_1000; ---sorted_result +sorted_result; SELECT * FROM tp; ---sorted_result +sorted_result; SELECT * FROM tsp; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_01; ---sorted_result +sorted_result; SELECT * FROM tsp_02; ---sorted_result +sorted_result; SELECT * FROM tsp_03; ---sorted_result +sorted_result; SELECT * FROM tsp_04; # 3) Invalid exchanges. # Exchange of partition with table differing in structure. CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10; ---error ER_TABLES_DIFFERENT_METADATA +error ER_TABLES_DIFFERENT_METADATA; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings +DROP TABLE t_11; eval CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table SELECT * FROM t_10; ---error ER_TABLES_DIFFERENT_METADATA +error ER_TABLES_DIFFERENT_METADATA; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10; ---error ER_MIX_HANDLER_ERROR +error ER_MIX_HANDLER_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings +DROP TABLE t_11; # Exchange of partition with partitioned table. eval CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table PARTITION BY KEY() AS SELECT * FROM t_10; ---error ER_PARTITION_EXCHANGE_PART_TABLE +error ER_PARTITION_EXCHANGE_PART_TABLE; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings +DROP TABLE t_11; # Exchange of subpartition with partitioned table. ---error ER_PARTITION_EXCHANGE_PART_TABLE +error ER_PARTITION_EXCHANGE_PART_TABLE; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp; # Exchange of subpartitioned partition with table. ---error ER_PARTITION_INSTEAD_OF_SUBPARTITION +error ER_PARTITION_INSTEAD_OF_SUBPARTITION; ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10; # Exchange of values in partition not fitting the hash. ---error ER_ROW_DOES_NOT_MATCH_PARTITION +error ER_ROW_DOES_NOT_MATCH_PARTITION; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_100; # Exchange of values in subpartition not fitting the hash. ---error ER_ROW_DOES_NOT_MATCH_PARTITION +error ER_ROW_DOES_NOT_MATCH_PARTITION; ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_10; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_3.test b/mysql-test/suite/parts/t/partition_exch_qa_3.test index aa79e97adb6..fc49eb1da90 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_3.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_3.test @@ -1,8 +1,8 @@ # Author: Horst Hunger # Created: 2010-07-05 ---source include/have_partition.inc ---source include/have_innodb.inc +source include/have_partition.inc; +source include/have_innodb.inc; let $engine_table= MYISAM; let $engine_part= InnoDB; @@ -10,38 +10,34 @@ let $engine_subpart= InnoDB; use test; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc +source suite/parts/inc/part_exch_tabs.inc; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM t_100; ---sorted_result +sorted_result; SELECT * FROM t_1000; ---sorted_result +sorted_result; SELECT * FROM tp; ---sorted_result +sorted_result; SELECT * FROM tsp; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_01; ---sorted_result +sorted_result; SELECT * FROM tsp_02; ---sorted_result +sorted_result; SELECT * FROM tsp_03; ---sorted_result +sorted_result; SELECT * FROM tsp_04; ---enable_result_log ---enable_query_log # 5) Exchanges with different engines. ---error ER_MIX_HANDLER_ERROR +error ER_MIX_HANDLER_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---error ER_MIX_HANDLER_ERROR +error ER_MIX_HANDLER_ERROR; ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_6.test b/mysql-test/suite/parts/t/partition_exch_qa_6.test index 69b49a32813..ace13f26921 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_6.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_6.test @@ -1,8 +1,8 @@ # Author: Horst Hunger # Created: 2010-07-06 ---source include/not_embedded.inc ---source include/have_partition.inc +source include/not_embedded.inc; +source include/have_partition.inc; let $engine_table= MYISAM; let $engine_part= MYISAM; @@ -10,41 +10,36 @@ let $engine_subpart= MYISAM; CREATE USER test2@localhost; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; GRANT USAGE ON *.* TO test2@localhost; GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost; connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -USE test; SHOW GRANTS FOR CURRENT_USER; # 9) Exchanges with different owner. # Privilege for ALTER and SELECT ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM tp WHERE a BETWEEN 0 AND 10; # Back to former values. ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM tp WHERE a BETWEEN 0 AND 10; ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp WHERE a BETWEEN 0 AND 10; # Back to former values. ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp WHERE a BETWEEN 0 AND 10; disconnect test2; @@ -55,7 +50,7 @@ connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); USE test; SHOW GRANTS FOR CURRENT_USER; # Privilege for ALTER and SELECT ---error ER_TABLEACCESS_DENIED_ERROR +error ER_TABLEACCESS_DENIED_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; disconnect test2; @@ -66,7 +61,7 @@ REVOKE CREATE ON test.* FROM test2@localhost; connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); USE test; SHOW GRANTS FOR CURRENT_USER; ---error ER_TABLEACCESS_DENIED_ERROR +error ER_TABLEACCESS_DENIED_ERROR; ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; disconnect test2; @@ -75,15 +70,14 @@ GRANT CREATE ON test.* TO test2@localhost; REVOKE DROP ON test.* FROM test2@localhost; connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -USE test; SHOW GRANTS FOR CURRENT_USER; # Privilege for ALTER and SELECT ---error ER_TABLEACCESS_DENIED_ERROR +error ER_TABLEACCESS_DENIED_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; disconnect test2; connection default; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; DROP USER test2@localhost; diff --git a/mysql-test/suite/perfschema/r/misc.result b/mysql-test/suite/perfschema/r/misc.result index 2adf2cba851..f2d40fe90b5 100644 --- a/mysql-test/suite/perfschema/r/misc.result +++ b/mysql-test/suite/perfschema/r/misc.result @@ -42,12 +42,12 @@ AND EVENT_NAME IN WHERE NAME LIKE "wait/synch/%") LIMIT 1; create table test.t1(a int) engine=performance_schema; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type WHERE object_schema='test'; object_schema object_name create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type WHERE object_schema='test'; object_schema object_name diff --git a/mysql-test/suite/perfschema/r/privilege.result b/mysql-test/suite/perfschema/r/privilege.result index 09d32a177fd..7bb7627142e 100644 --- a/mysql-test/suite/perfschema/r/privilege.result +++ b/mysql-test/suite/perfschema/r/privilege.result @@ -155,13 +155,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'setup_instruments' @@ -254,13 +254,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'pfs_user_1'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'pfs_user_1'@'localhost' for table 'setup_instruments' @@ -354,13 +354,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'pfs_user_2'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'pfs_user_2'@'localhost' for table 'setup_instruments' @@ -454,13 +454,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'pfs_user_3'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'pfs_user_3'@'localhost' for table 'setup_instruments' diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_view.result b/mysql-test/suite/rpl/r/rpl_create_drop_view.result index 34f27faf98a..ebbe9efc9df 100644 --- a/mysql-test/suite/rpl/r/rpl_create_drop_view.result +++ b/mysql-test/suite/rpl/r/rpl_create_drop_view.result @@ -99,7 +99,7 @@ DROP VIEW v1; ERROR 42S02: Unknown VIEW: 'test.v1' DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' # Syncing slave with master connection slave; SELECT * FROM v1; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result index 62a5b9c3531..54156685806 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result @@ -113,7 +113,7 @@ SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,dummy_disable_default_dbug_output"; SET GLOBAL debug_dbug="+d,gtid_fail_after_record_gtid"; SET sql_log_bin=0; -CALL mtr.add_suppression('Got error 131 "Command not supported by database" during COMMIT'); +CALL mtr.add_suppression('Got error 131 "Command not supported by the engine" during COMMIT'); SET sql_log_bin=1; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1180] diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result index 25287b70cf6..2849e5b7ae3 100644 --- a/mysql-test/suite/rpl/r/rpl_sp.result +++ b/mysql-test/suite/rpl/r/rpl_sp.result @@ -128,7 +128,7 @@ show warnings; Level Code Message Error 1062 Duplicate entry '20' for key 'a' Warning 1196 Some non-transactional changed tables couldn't be rolled back -Note 4091 At line 4 in mysqltest1.foo4 +Note 4092 At line 4 in mysqltest1.foo4 select * from t2; a 20 @@ -291,7 +291,7 @@ end| do fn1(100); Warnings: Error 1062 Duplicate entry '100' for key 'a' -Note 4091 At line 3 in mysqltest1.fn1 +Note 4092 At line 3 in mysqltest1.fn1 Warning 1196 Some non-transactional changed tables couldn't be rolled back select fn1(20); ERROR 23000: Duplicate entry '20' for key 'a' diff --git a/mysql-test/suite/rpl/r/rpl_sp_variables.result b/mysql-test/suite/rpl/r/rpl_sp_variables.result new file mode 100644 index 00000000000..7e2ba72845c --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_sp_variables.result @@ -0,0 +1,28 @@ +include/master-slave.inc +[connection master] +# +# MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +# +connection master; +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +CREATE PROCEDURE p1() +BEGIN +DECLARE v_id INT DEFAULT 2017; +INSERT INTO test.t1 SELECT CONCAT(v_id, '오'); +END; +$$ +CALL p1; +SELECT * FROM t1; +a +2017오 +connection slave; +SET NAMES utf8; +SELECT * FROM t1; +a +2017오 +connection master; +DROP PROCEDURE p1; +DROP TABLE t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test b/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test index 796f6894f19..c02e2670c92 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test @@ -132,7 +132,7 @@ SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,dummy_disable_default_dbug_output"; SET GLOBAL debug_dbug="+d,gtid_fail_after_record_gtid"; SET sql_log_bin=0; -CALL mtr.add_suppression('Got error 131 "Command not supported by database" during COMMIT'); +CALL mtr.add_suppression('Got error 131 "Command not supported by the engine" during COMMIT'); SET sql_log_bin=1; START SLAVE; --let $slave_sql_errno= 1180 diff --git a/mysql-test/suite/rpl/t/rpl_sp_variables.test b/mysql-test/suite/rpl/t/rpl_sp_variables.test new file mode 100644 index 00000000000..87e9fe194ea --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_sp_variables.test @@ -0,0 +1,28 @@ +source include/master-slave.inc; + +--echo # +--echo # MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +--echo # + +connection master; +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE v_id INT DEFAULT 2017; + INSERT INTO test.t1 SELECT CONCAT(v_id, '오'); +END; +$$ +DELIMITER ;$$ +CALL p1; +SELECT * FROM t1; +sync_slave_with_master; +SET NAMES utf8; +SELECT * FROM t1; +connection master; +DROP PROCEDURE p1; +DROP TABLE t1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result index 283253b585a..0fca8b7c02b 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -212,7 +212,7 @@ ERROR 42S02: 'test.t1' is not a SEQUENCE drop table t1; alter sequence if exists t1 minvalue=100; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t1' alter sequence t1 minvalue=100; ERROR 42S02: Table 'test.t1' doesn't exist create sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 2697da1d8ff..a5eb64802db 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -165,7 +165,7 @@ drop sequence t1; ERROR 42S02: 'test.t1' is not a SEQUENCE drop sequence if exists t1; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t1' create sequence t1 start with 10 maxvalue=9; ERROR HY000: Sequence 'test.t1' values are conflicting create sequence t1 minvalue= 100 maxvalue=10; @@ -377,7 +377,7 @@ key key1 (next_not_cached_value) ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys) drop sequence if exists t1; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t1' create sequence t1; create sequence t2; create table t3 (a int) engine=myisam; @@ -387,8 +387,8 @@ CREATE SEQUENCE s1; drop sequence s1; drop sequence if exists t1,t2,t3,t4; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t3' -Note 4088 Unknown SEQUENCE: 'test.t4' +Note 4089 Unknown SEQUENCE: 'test.t3' +Note 4089 Unknown SEQUENCE: 'test.t4' drop table if exists t1,t2,t3; Warnings: Note 1051 Unknown table 'test.t1' @@ -414,9 +414,9 @@ CREATE TABLE t2 (a int); CREATE SEQUENCE s1; drop sequence if exists t1,t2,s1,s2; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' -Note 4088 Unknown SEQUENCE: 'test.t2' -Note 4088 Unknown SEQUENCE: 'test.s2' +Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t2' +Note 4089 Unknown SEQUENCE: 'test.s2' drop table if exists t1,t2; CREATE TEMPORARY SEQUENCE s1; DROP SEQUENCE s1; diff --git a/mysql-test/suite/vcol/r/innodb_virtual_fk.result b/mysql-test/suite/vcol/r/innodb_virtual_fk.result new file mode 100644 index 00000000000..58db12583e2 --- /dev/null +++ b/mysql-test/suite/vcol/r/innodb_virtual_fk.result @@ -0,0 +1,12 @@ +set default_storage_engine=innodb; +create table t1 (id int primary key, id2 int as (id) virtual, key id2 (id2)); +create table t2 (id int key, constraint fk_id foreign key (id) references t1 (id) on delete cascade); +insert into t1 (id) values (1), (2); +insert into t2 (id) values (1), (2); +delete from t1; +select * from t1; +id id2 +select * from t2; +id +drop table t2; +drop table t1; diff --git a/mysql-test/suite/vcol/r/update.result b/mysql-test/suite/vcol/r/update.result index 95b0093ed71..5c7905cf547 100644 --- a/mysql-test/suite/vcol/r/update.result +++ b/mysql-test/suite/vcol/r/update.result @@ -155,3 +155,13 @@ select * from t; a b c d e 11 11 11 11 11 drop table t, t1, t2; +create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2)); +insert into t (f1,f2) values (1,1),(2,2); +create view v as +select a2.f1, a2.f2, a1.f3 +from t a1, t a2 +where a2.f3 <> 0 +with local check option; +update v set f3 = 52; +drop view v; +drop table t; diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result index 86030a304d4..3fa4f6e1431 100644 --- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result +++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result @@ -2911,16 +2911,31 @@ drop table t1; set sql_warnings = 0; # TIME_FORMAT() set sql_warnings = 1; -create table t1 (a datetime, b varchar(10) as (time_format(a,"%d.%m.%Y"))); +create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S"))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, - `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%d.%m.%Y')) VIRTUAL + `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -insert into t1 values ('2001-01-01 02:02:02',default); +insert into t1 values ('2001-01-01 02:03:04',default); select * from t1; a b -2001-01-01 02:02:02 01.01.2001 +2001-01-01 02:03:04 02.03.04 +drop table t1; +set sql_warnings = 0; +# TIME_FORMAT() STORED +set sql_warnings = 1; +create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")) STORED); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) STORED +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ('2001-01-01 02:03:04',default); +select * from t1; +a b +2001-01-01 02:03:04 02.03.04 drop table t1; set sql_warnings = 0; diff --git a/mysql-test/suite/vcol/t/innodb_virtual_fk.test b/mysql-test/suite/vcol/t/innodb_virtual_fk.test new file mode 100644 index 00000000000..c364adaa613 --- /dev/null +++ b/mysql-test/suite/vcol/t/innodb_virtual_fk.test @@ -0,0 +1,16 @@ +source include/have_innodb.inc; +set default_storage_engine=innodb; + +# +# MDEV-13708 Crash with indexed virtual columns and FK cascading deletes +# + +create table t1 (id int primary key, id2 int as (id) virtual, key id2 (id2)); +create table t2 (id int key, constraint fk_id foreign key (id) references t1 (id) on delete cascade); +insert into t1 (id) values (1), (2); +insert into t2 (id) values (1), (2); +delete from t1; +select * from t1; +select * from t2; +drop table t2; +drop table t1; diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test index 062d9736ed8..1797bdd501e 100644 --- a/mysql-test/suite/vcol/t/update.test +++ b/mysql-test/suite/vcol/t/update.test @@ -111,3 +111,17 @@ check table t; select * from t; update t, t tt set t.b=11, tt.d=11 where t.a=tt.a; check table t; select * from t; drop table t, t1, t2; + +# +# MDEV-13623 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in virtual longlong Field_long::val_int +# +create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2)); +insert into t (f1,f2) values (1,1),(2,2); +create view v as + select a2.f1, a2.f2, a1.f3 + from t a1, t a2 + where a2.f3 <> 0 + with local check option; +update v set f3 = 52; +drop view v; +drop table t; diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc index 4a95ea75534..dafc42098dd 100644 --- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc +++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc @@ -1204,8 +1204,14 @@ let $rows = 1; --source suite/vcol/inc/vcol_supported_sql_funcs.inc --echo # TIME_FORMAT() -let $cols = a datetime, b varchar(10) as (time_format(a,"%d.%m.%Y")); -let $values1 = '2001-01-01 02:02:02',default; +let $cols = a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")); +let $values1 = '2001-01-01 02:03:04',default; +let $rows = 1; +--source suite/vcol/inc/vcol_supported_sql_funcs.inc + +--echo # TIME_FORMAT() STORED +let $cols = a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")) STORED; +let $values1 = '2001-01-01 02:03:04',default; let $rows = 1; --source suite/vcol/inc/vcol_supported_sql_funcs.inc diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index ca2f73db5ca..487990b61cd 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1852,6 +1852,7 @@ create table t1 (a int, b int, check(a>b)); alter table t1 drop column a; --error ER_BAD_FIELD_ERROR alter table t1 drop column b, add column b bigint first; +alter table t1 drop column a, drop constraint constraint_1; show create table t1; drop table t1; @@ -1873,5 +1874,6 @@ drop table t1; create table t1 (a int, b int, c int, unique(a,b)); --error ER_KEY_COLUMN_DOES_NOT_EXITS alter table t1 drop column a; +alter table t1 drop column a, drop index a; show create table t1; drop table t1; diff --git a/mysql-test/t/check_constraint.test b/mysql-test/t/check_constraint.test index 43b4417cfa3..f72ce38087e 100644 --- a/mysql-test/t/check_constraint.test +++ b/mysql-test/t/check_constraint.test @@ -92,3 +92,14 @@ create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or create table t1 (a int check (@b in (select user from mysql.user))); --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED create table t1 (a int check (a > @b)); + +# +# MDEV-13596 CHECK constraints disallow NULL to pass through, violating SQL +# +create table t1 (a int check (a = 1)); +insert t1 values (1); +--error ER_CONSTRAINT_FAILED +insert t1 values (2); +insert t1 values (NULL); +select * from t1; +drop table t1; diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test index a00574b6cba..86045e862e7 100644 --- a/mysql-test/t/count_distinct.test +++ b/mysql-test/t/count_distinct.test @@ -121,5 +121,34 @@ drop table t1; set @@tmp_table_size = default; # +# MDEV-13457: Wrong result for aggregate function with distinct clause when the value for +# tmp_table_size is small +# + +create table t1 ( +a VARCHAR(1020), +b int +); +insert into t1 values +( 0 , 1 ), +( 1 , 2 ), +( 2 , 3 ), +( 3 , 4 ), +( 4 , 5 ), +( 5 , 6 ), +( 6 , 7 ), +( 7 , 8 ), +( 8 , 9 ), +( 9 , 10 ), +( 0 , 11 ), +( 1 , 12 ), +( 2 , 13 ), +( 3 , 14 ); +set @@tmp_table_size=1024; +select count(distinct a) from t1; +drop table t1; +set @@tmp_table_size = default; + +# # End of 5.5 tests # diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 972543aefc2..3bf6fabbf6d 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -366,3 +366,12 @@ SET NAMES latin1; --echo # --echo # End of 5.1 tests --echo # + +# +# TIME_FORMAT and non-time format specifiers +# +select time_format('2001-01-01 02:02:02', '%d.%m.%Y'); +select time_format('2001-01-01 02:02:02', '%d %T'); +select time_format('01 02:02:02', '%d %T'); +select time_format('01 02:02:02', '%T'); +select time_format('2001-01-01 02:02:02', '%T'); diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index ee70102c496..83e345ec890 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -609,6 +609,10 @@ select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1); select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'); +# +# MDEV-13673 Bad result in view +# +explain extended select (3-2)+1, (3/2)*1, 3-(2+1), 3/(2*1); --echo # --echo # Start of 10.3 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index c661819424a..ac983048129 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -1092,6 +1092,17 @@ select release_lock('test'); --echo # -- Done. --echo + +--echo # +--echo # MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +--echo # +SET NAMES utf8; +SELECT COERCIBILITY(NAME_CONST('name','test')); +SELECT COERCIBILITY(NAME_CONST('name',TIME'00:00:00')); +SELECT COERCIBILITY(NAME_CONST('name',15)); +SELECT CONCAT(NAME_CONST('name',15),'오'); +SET NAMES latin1; + --echo # --echo # Start of 10.2 tests --echo # diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 206c5553100..80caefa5a0f 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -573,3 +573,32 @@ insert ignore into t1 values (1,12) on duplicate key update f2=13; set @@old_mode=""; insert ignore into t1 values (1,12); DROP TABLE t1; + +--echo # +--echo # MDEV-13290 Assertion Assertion `!is_set() || (m_status == DA_OK_BULK +--echo # && is_bulk_op())' or `! is_set()' failed +--echo # + +SET @save_mode= @@sql_mode; +SET sql_mode= 'STRICT_ALL_TABLES'; +CREATE TABLE t1 (f1 INT DEFAULT 0, f2 INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; +--error ER_TRUNCATED_WRONG_VALUE +REPLACE INTO v1 SET f2 = 1; +SELECT * from t1; +drop view v1; +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = cast('' as decimal) WITH CHECK OPTION; +--error ER_TRUNCATED_WRONG_VALUE +REPLACE INTO v1 SET f2 = 1; +SELECT * from t1; +drop view v1; +SELECT 0,0 INTO OUTFILE 't1.txt'; +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; +--error ER_TRUNCATED_WRONG_VALUE +LOAD DATA INFILE 't1.txt' INTO TABLE v1; +SELECT * from t1; +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/t1.txt; +drop view v1; +drop table t1; +SET @@sql_mode= @save_mode; diff --git a/mysql-test/t/locale.test b/mysql-test/t/locale.test index b1fbc40f0c8..4570b968ecf 100644 --- a/mysql-test/t/locale.test +++ b/mysql-test/t/locale.test @@ -134,7 +134,22 @@ SELECT * FROM non_existent; SET lc_time_names=@old_50915_lc_time_names; +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # End of 10.1 tests +--echo # + +# Item::print +create view v1 as select + format(123456789,2) as b, + format(123456789,2,'rm_CH') as b1; +select * from v1; +show create view v1; +drop view v1; --echo # ---echo # End of 5.6 tests +--echo # End of 10.2 tests --echo # diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 8936a163d73..fa8810ecd3b 100644 --- a/mysql-test/t/log_tables-big.test +++ b/mysql-test/t/log_tables-big.test @@ -7,6 +7,7 @@ # check that CSV engine was compiled in --source include/have_csv.inc +set @log_output.saved = @@global.log_output; set @@global.log_output = 'TABLE'; connect (con1,localhost,root,,); @@ -21,13 +22,13 @@ select get_lock('bug27638', 1); connection con2; set session long_query_time=1; select get_lock('bug27638', 2); -select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:00:01', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 2)'; select get_lock('bug27638', 60); -select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:00:59', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 60)'; select get_lock('bug27638', 101); -select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:01:40', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 101)'; connection con1; select release_lock('bug27638'); @@ -36,4 +37,4 @@ connection default; disconnect con1; disconnect con2; -set @@global.log_output=default; +set @@global.log_output = @log_output.saved; diff --git a/mysql-test/t/partition_symlink.test b/mysql-test/t/partition_symlink.test index f2e3eba5de6..8f6e837299a 100644 --- a/mysql-test/t/partition_symlink.test +++ b/mysql-test/t/partition_symlink.test @@ -38,11 +38,10 @@ SHOW CREATE TABLE t1; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR SHOW CREATE TABLE t2; INSERT INTO t1 VALUES (0), (1), (2); ---error ER_TABLES_DIFFERENT_METADATA ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; --error ER_TABLES_DIFFERENT_METADATA ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2; ---sorted_result +SELECT * FROM t1; SELECT * FROM t2; DROP TABLE t1, t2; # skipped because of bug#52354 diff --git a/mysql-test/t/range_interrupted-13751.test b/mysql-test/t/range_interrupted-13751.test new file mode 100644 index 00000000000..000a46383e8 --- /dev/null +++ b/mysql-test/t/range_interrupted-13751.test @@ -0,0 +1,25 @@ +source include/have_debug.inc; +# +# MDEV-13751 Interrupted SELECT fails with 1030: 'Got error 1 "Operation not permitted" from storage engine MyISAM' +# +CREATE TABLE t1 (i INT AUTO_INCREMENT, c VARCHAR(1), KEY(i), KEY(c,i)) ENGINE=MyISAM; +INSERT INTO t1 (c) VALUES ('a'),('b'),('c'),('d'); +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; + +set @old_dbug=@@session.debug_dbug; +set debug_dbug="+d,kill_join_init_read_record"; + +--error ER_QUERY_INTERRUPTED +SELECT 1 FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.c = alias2.c OR alias1.i <= 1 +; + +set debug_dbug=@old_dbug; + +DROP TABLE t1; + diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index e17ea07ca3c..cf95d4cb938 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -316,3 +316,28 @@ eval CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM DATA DIRECTORY "$MYSQLTEST_VARDIR/tmp"; DROP TABLE test.t1; +use test; + +# +# End of 5.5 tests +# + +# +# End of 10.0 tests +# + +# +# MDEV-13636 ALTER TABLE ... DELAY_KEY_WRITE=1 creates table copy for MyISAM table with DATA DIRECTORY/INDEX DIRECTORY options +# +replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR; +eval +create table t1(c1 int, c2 int, c3 varchar(100)) engine=MyISAM data directory='$MYSQL_TMP_DIR' index directory = '$MYSQL_TMP_DIR'; +insert t1 values (1,2,3), (2,3,4), (3,4,5), (4,5,6), (5,6,7), (6,7,8), (7,8,9); +alter online table t1 delay_key_write=1; +replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR; +show create table t1; +drop table t1; + +# +# End of 10.1 tests +# diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c2022499acf..c353cd8b599 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1966,10 +1966,6 @@ SELECT i AS fld FROM t1 UNION SELECT COUNT(*) AS fld FROM t1; DROP TABLE t1; --echo # ---echo # Start of 10.3 tests ---echo # - ---echo # --echo # MDEV-13240 Wrong warning with MAX(datetime_field) OVER (...) --echo # @@ -1977,3 +1973,70 @@ CREATE TABLE t1 (dt DATETIME); INSERT INTO t1 VALUES ('2017-05-17'); SELECT MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-13358 FIRST_VALUE throws SQL Error (1292): Incorrect datetime value +--echo # +CREATE TABLE IF NOT EXISTS `fv_test` ( + `SOME_DATE` datetime NOT NULL + ); + +INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56'); + +CREATE TABLE fv_result +SELECT +FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate +FROM fv_test; + +SHOW CREATE TABLE fv_result; + +SELECT * FROM fv_result; + +DROP TABLE fv_test, fv_result; + +--echo # +--echo # MDEV-13649: Server crashes in set_field_to_null_with_conversions or in Field::set_notnull +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0),(1),(2); +SELECT LEAD(a) OVER (PARTITION BY a) as lead, + a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +FROM t1; + +SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order +FROM t1 +ORDER BY a; + +SELECT a AND LEAD(a) OVER (ORDER BY a) AS a_and_lead_order +FROM t1 +ORDER BY a; + +SELECT a XOR LEAD(a) OVER (ORDER BY a) AS a_xor_lead_order +FROM t1 +ORDER BY a; + +SELECT NOT LEAD(a) OVER (ORDER BY a) AS not_lead_order +FROM t1 +ORDER BY a; + +SELECT LEAD(a) OVER (ORDER BY a) is not null AS is_not_null_lead_order +FROM t1 +ORDER BY a; + +drop table t1; + +--echo # +--echo # MDEV-13354: Server crashes in find_field_in_tables upon PS with window function and subquery +--echo # + +CREATE TABLE t1 (i INT, a char); +INSERT INTO t1 VALUES (1, 'a'),(2, 'b'); +PREPARE stmt FROM "SELECT row_number() over (partition by i order by i), i FROM (SELECT * from t1) as sq"; +EXECUTE stmt; + +DROP TABLE t1; + +--echo # +--echo # Start of 10.3 tests +--echo # diff --git a/mysql-test/t/win_as_arg_to_aggregate_func.test b/mysql-test/t/win_as_arg_to_aggregate_func.test new file mode 100644 index 00000000000..93c9238bedf --- /dev/null +++ b/mysql-test/t/win_as_arg_to_aggregate_func.test @@ -0,0 +1,139 @@ +create table t1 (i int); +insert into t1 values (5),(6),(0); + +--echo # +--echo # Try out all set functions with window functions as arguments. +--echo # Any such usage should return an error. +--echo # +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MIN( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MIN(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MAX( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MAX(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM(DISTINCT SUM(i) OVER (order by i) ) +from t1; + + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select AVG( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select AVG(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select COUNT( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select COUNT(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_AND( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_OR( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_XOR( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STD( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV_POP( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV_SAMP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VARIANCE(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VAR_POP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VAR_SAMP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select GROUP_CONCAT(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--echo # +--echo # Test that partition instead of order by in over doesn't change result. +--echo # + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER (PARTITION BY i) ) +from t1; + +--echo # +--echo # Test that no arguments in OVER() clause lead to crash in this case. +--echo # +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER () ) +from t1; +drop table t1; + +-- echo # +-- echo # MDEV-13774: Server Crash on Execuate of SQL Statement +-- echo # +create table t1 (i int); +insert into t1 values (5),(6),(0); + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( + IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0 + AND + SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0, + 1, + 0) ) +from t1; + +--echo # +--echo # A way to get the aggregation result. +--echo # + +select i, IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1 +order by i; + +select sum(if_col) +from (select IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col + from t1) tmp; +drop table t1; diff --git a/mysql-test/t/win_insert_select.test b/mysql-test/t/win_insert_select.test index a9e7e8f322f..6b2e0da4175 100644 --- a/mysql-test/t/win_insert_select.test +++ b/mysql-test/t/win_insert_select.test @@ -3,12 +3,12 @@ CREATE TABLE t1 (c1 INT, c2 VARCHAR(30)); PREPARE populate_table FROM "INSERT into t1 values (1, 'manual_insert_1'), (4, 'manual_insert_2')"; -INSERT INTO t1 SELECT row_number() over(), "should_have_NULL" FROM t1; -INSERT INTO t1 SELECT 1 + row_number() over(), "should_have_2" FROM t1; +INSERT INTO t1 SELECT row_number() over(), "should_not_add_any_rows" FROM t1; +INSERT INTO t1 SELECT 1 + row_number() over(), "should_not_add_any_rows" FROM t1; EXECUTE populate_table; -INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 4 times [11-14]" FROM t1; +INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 2 times [11-12]" FROM t1; SELECT c1, c2 FROM t1 ORDER BY c2, c1; |