diff options
Diffstat (limited to 'mysql-test/r')
29 files changed, 937 insertions, 120 deletions
diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index b0033383f00..b91272f7e59 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5379,4 +5379,13 @@ SHOW WARNINGS; Level Code Message Error 1178 The storage engine for the table doesn't support nullable columns Error 1005 Can't create table 'test.t1' (errno: 138) +create table t1 (c1 tinyblob not null) engine=csv; +insert into t1 values("This"); +update t1 set c1="That" where c1="This"; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select * from t1; +c1 +That +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/ctype_big5.result b/mysql-test/r/ctype_big5.result index 9de10a5452b..b88ea14b559 100644 --- a/mysql-test/r/ctype_big5.result +++ b/mysql-test/r/ctype_big5.result @@ -3,6 +3,8 @@ SET @test_character_set= 'big5'; SET @test_collation= 'big5_chinese_ci'; SET @safe_character_set_server= @@character_set_server; SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; SET character_set_server= @test_character_set; SET collation_server= @test_collation; CREATE DATABASE d1; @@ -69,10 +71,27 @@ select 1 from t1 order by cast(a as char(1)); 1 1 drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) default NULL, + `level` smallint(5) unsigned default NULL +) ENGINE=MyISAM DEFAULT CHARSET=big5 +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; DROP DATABASE d1; USE test; SET character_set_server= @safe_character_set_server; SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; SET NAMES big5; SET collation_connection='big5_chinese_ci'; create table t1 select repeat('a',4000) a; diff --git a/mysql-test/r/ctype_cp1250_ch.result b/mysql-test/r/ctype_cp1250_ch.result index 4b02fa2182a..7f0cdf3f17b 100644 --- a/mysql-test/r/ctype_cp1250_ch.result +++ b/mysql-test/r/ctype_cp1250_ch.result @@ -3,6 +3,192 @@ DROP TABLE IF EXISTS t1; SHOW COLLATION LIKE 'cp1250_czech_cs'; Collation Charset Id Default Compiled Sortlen cp1250_czech_cs cp1250 34 Yes 2 +SET @test_character_set= 'cp1250'; +SET @test_collation= 'cp1250_general_ci'; +SET @safe_character_set_server= @@character_set_server; +SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; +SET character_set_server= @test_character_set; +SET collation_server= @test_collation; +CREATE DATABASE d1; +USE d1; +CREATE TABLE t1 (c CHAR(10), KEY(c)); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c char(10) cp1250_general_ci YES MUL NULL +INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); +SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; +want3results +aaa +aaaa +aaaaa +DROP TABLE t1; +CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c1 varchar(15) cp1250_general_ci YES MUL NULL +INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); +SELECT c1 as want3results from t1 where c1 like 'l%'; +want3results +location +loberge +lotre +SELECT c1 as want3results from t1 where c1 like 'lo%'; +want3results +location +loberge +lotre +SELECT c1 as want1result from t1 where c1 like 'loc%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'loca%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locat%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locati%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locatio%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'location%'; +want1result +location +DROP TABLE t1; +create table t1 (a set('a') not null); +insert into t1 values (),(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select cast(a as char(1)) from t1; +cast(a as char(1)) + + +select a sounds like a from t1; +a sounds like a +1 +1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 +drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=cp1250 +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; +DROP DATABASE d1; +USE test; +SET character_set_server= @safe_character_set_server; +SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; +SET @test_character_set= 'cp1250'; +SET @test_collation= 'cp1250_czech_cs'; +SET @safe_character_set_server= @@character_set_server; +SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; +SET character_set_server= @test_character_set; +SET collation_server= @test_collation; +CREATE DATABASE d1; +USE d1; +CREATE TABLE t1 (c CHAR(10), KEY(c)); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c char(10) cp1250_czech_cs YES MUL NULL +INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); +SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; +want3results +aaa +aaaa +aaaaa +DROP TABLE t1; +CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c1 varchar(15) cp1250_czech_cs YES MUL NULL +INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); +SELECT c1 as want3results from t1 where c1 like 'l%'; +want3results +location +loberge +lotre +SELECT c1 as want3results from t1 where c1 like 'lo%'; +want3results +location +loberge +lotre +SELECT c1 as want1result from t1 where c1 like 'loc%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'loca%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locat%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locati%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locatio%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'location%'; +want1result +location +DROP TABLE t1; +create table t1 (a set('a') not null); +insert into t1 values (),(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select cast(a as char(1)) from t1; +cast(a as char(1)) + + +select a sounds like a from t1; +a sounds like a +1 +1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 +drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) COLLATE cp1250_czech_cs DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; +DROP DATABASE d1; +USE test; +SET character_set_server= @safe_character_set_server; +SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; CREATE TABLE t1 (a char(16)) character set cp1250 collate cp1250_czech_cs; INSERT INTO t1 VALUES (''); SELECT a, length(a), a='', a=' ', a=' ' FROM t1; diff --git a/mysql-test/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result index c2a1b70eda9..6490044ea1a 100644 --- a/mysql-test/r/ctype_euckr.result +++ b/mysql-test/r/ctype_euckr.result @@ -3,6 +3,8 @@ SET @test_character_set= 'euckr'; SET @test_collation= 'euckr_korean_ci'; SET @safe_character_set_server= @@character_set_server; SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; SET character_set_server= @test_character_set; SET collation_server= @test_collation; CREATE DATABASE d1; @@ -69,10 +71,27 @@ select 1 from t1 order by cast(a as char(1)); 1 1 drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=euckr +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; DROP DATABASE d1; USE test; SET character_set_server= @safe_character_set_server; SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; SET NAMES euckr; SET collation_connection='euckr_korean_ci'; create table t1 select repeat('a',4000) a; diff --git a/mysql-test/r/ctype_gb2312.result b/mysql-test/r/ctype_gb2312.result index 42f6d6c6e3b..2f789ab5b13 100644 --- a/mysql-test/r/ctype_gb2312.result +++ b/mysql-test/r/ctype_gb2312.result @@ -3,6 +3,8 @@ SET @test_character_set= 'gb2312'; SET @test_collation= 'gb2312_chinese_ci'; SET @safe_character_set_server= @@character_set_server; SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; SET character_set_server= @test_character_set; SET collation_server= @test_collation; CREATE DATABASE d1; @@ -69,10 +71,27 @@ select 1 from t1 order by cast(a as char(1)); 1 1 drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=gb2312 +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; DROP DATABASE d1; USE test; SET character_set_server= @safe_character_set_server; SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; SET NAMES gb2312; SET collation_connection='gb2312_chinese_ci'; create table t1 select repeat('a',4000) a; diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result index 0a326c3bf65..98a6839be4c 100644 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@ -3,6 +3,8 @@ SET @test_character_set= 'gbk'; SET @test_collation= 'gbk_chinese_ci'; SET @safe_character_set_server= @@character_set_server; SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; SET character_set_server= @test_character_set; SET collation_server= @test_collation; CREATE DATABASE d1; @@ -69,10 +71,27 @@ select 1 from t1 order by cast(a as char(1)); 1 1 drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=gbk +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; DROP DATABASE d1; USE test; SET character_set_server= @safe_character_set_server; SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; SET NAMES gbk; SET collation_connection='gbk_chinese_ci'; create table t1 select repeat('a',4000) a; diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index 706e764b281..04727f84ff2 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -2538,6 +2538,8 @@ SET @test_character_set= 'utf8'; SET @test_collation= 'utf8_swedish_ci'; SET @safe_character_set_server= @@character_set_server; SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; SET character_set_server= @test_character_set; SET collation_server= @test_collation; CREATE DATABASE d1; @@ -2604,10 +2606,27 @@ select 1 from t1 order by cast(a as char(1)); 1 1 drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) COLLATE utf8_swedish_ci DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; DROP DATABASE d1; USE test; SET character_set_server= @safe_character_set_server; SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; create table t1 (a varchar(1)) character set utf8 collate utf8_estonian_ci; insert into t1 values ('A'),('B'),('C'),('a'),('b'),('c'); select a, a regexp '[a]' from t1 order by binary a; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index dd9ceeb1450..20dd85834dd 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -1,4 +1,97 @@ DROP TABLE IF EXISTS t1; +SET @test_character_set= 'ucs2'; +SET @test_collation= 'ucs2_general_ci'; +SET @safe_character_set_server= @@character_set_server; +SET @safe_collation_server= @@collation_server; +SET @safe_character_set_client= @@character_set_client; +SET @safe_character_set_results= @@character_set_results; +SET character_set_server= @test_character_set; +SET collation_server= @test_collation; +CREATE DATABASE d1; +USE d1; +CREATE TABLE t1 (c CHAR(10), KEY(c)); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c char(10) ucs2_general_ci YES MUL NULL +INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); +SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; +want3results +aaa +aaaa +aaaaa +DROP TABLE t1; +CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c1 varchar(15) ucs2_general_ci YES MUL NULL +INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); +SELECT c1 as want3results from t1 where c1 like 'l%'; +want3results +location +loberge +lotre +SELECT c1 as want3results from t1 where c1 like 'lo%'; +want3results +location +loberge +lotre +SELECT c1 as want1result from t1 where c1 like 'loc%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'loca%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locat%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locati%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locatio%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'location%'; +want1result +location +DROP TABLE t1; +create table t1 (a set('a') not null); +insert into t1 values (),(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select cast(a as char(1)) from t1; +cast(a as char(1)) + + +select a sounds like a from t1; +a sounds like a +1 +1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 +drop table t1; +set names utf8; +create table t1 ( +name varchar(10), +level smallint unsigned); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) DEFAULT NULL, + `level` smallint(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=ucs2 +insert into t1 values ('string',1); +select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; +concat(name,space(level)) concat(name, repeat(' ',level)) +string string +drop table t1; +DROP DATABASE d1; +USE test; +SET character_set_server= @safe_character_set_server; +SET collation_server= @safe_collation_server; +SET character_set_client= @safe_character_set_client; +SET character_set_results= @safe_character_set_results; SET NAMES latin1; SET character_set_connection=ucs2; select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; diff --git a/mysql-test/r/ctype_ucs2_def.result b/mysql-test/r/ctype_ucs2_def.result index 6fd45428368..005d46062fb 100644 --- a/mysql-test/r/ctype_ucs2_def.result +++ b/mysql-test/r/ctype_ucs2_def.result @@ -21,4 +21,14 @@ INSERT INTO t1 VALUES('A', 'A'), ('B', 'B'), ('C', 'C'); INSERT INTO t1 VALUES('A ', 'A '); ERROR 23000: Duplicate entry '' for key 'key1' DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(255) CHARACTER SET UCS2 COLLATE UCS2_BIN NOT NULL, +KEY(c1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('marshall\'s'); +INSERT INTO t1 VALUES ('marsh'); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 00287338459..21743c3a8a6 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -2066,6 +2066,34 @@ select 1 from t1 order by a; drop table t1; drop table t1; drop view v1; +CREATE TABLE t1 (a INT, b INT, KEY(a,b)); +INSERT INTO t1 VALUES(NULL,1),(1,NULL),(NULL,NULL),(1,1),(2,2); +CREATE TABLE t1 (a INT, b INT, KEY(a,b)) ENGINE=federated +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +SELECT * FROM t1 WHERE a IS NULL; +a b +NULL NULL +NULL 1 +SELECT * FROM t1 WHERE a IS NOT NULL; +a b +1 NULL +1 1 +2 2 +SELECT * FROM t1 WHERE a=1 AND b=1; +a b +1 1 +SELECT * FROM t1 WHERE a IS NULL AND b=1; +a b +NULL 1 +SELECT * FROM t1 WHERE a IS NOT NULL AND b=1; +a b +1 1 +DROP TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT) ENGINE=federated CONNECTION='mysql://@:://'; +DROP TABLE t1; +create server 's1' foreign data wrapper 'mysql' options (port 3306); +drop server 's1'; End of 5.1 tests DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index ec8c187e1f6..a6b912b4e0a 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -249,6 +249,9 @@ select min(a) from t1 group by inet_ntoa(a); min(a) -2 drop table t1; +SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs; +NAME_CONST('var', 'value') COLLATE latin1_general_cs +value End of 5.0 tests select connection_id() > 0; connection_id() > 0 diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 18864148d29..f42b201b8b7 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1633,4 +1633,6 @@ show open tables where f1()=0; show open tables where f1()=0; drop table t1; drop function f1; +select * from information_schema.tables where 1=sleep(100000); +select * from information_schema.columns where 1=sleep(100000); End of 5.1 tests. diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 0a7634df85a..2a4cee9fbbc 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -107,6 +107,17 @@ Database Table In_use Name_locked SET GLOBAL GENERAL_LOG=ON; SET GLOBAL SLOW_QUERY_LOG=ON; truncate table mysql.general_log; +set names binary; +select _koi8r'ΤΕΣΤ' as test; +test +ΤΕΣΤ +select * from mysql.general_log; +event_time user_host thread_id server_id command_type argument +TIMESTAMP USER_HOST THREAD_ID 1 Query set names binary +TIMESTAMP USER_HOST THREAD_ID 1 Query select _koi8r'\xD4\xC5\xD3\xD4' as test +TIMESTAMP USER_HOST THREAD_ID 1 Query select * from mysql.general_log +set names utf8; +truncate table mysql.general_log; set names utf8; create table bug16905 (s char(15) character set utf8 default 'ΠΏΡΡΡΠΎ'); insert into bug16905 values ('Π½ΠΎΠ²ΠΎΠ΅'); diff --git a/mysql-test/r/merge-big.result b/mysql-test/r/merge-big.result index 82fedc1df73..e34ebbd9578 100644 --- a/mysql-test/r/merge-big.result +++ b/mysql-test/r/merge-big.result @@ -24,54 +24,3 @@ UNLOCK TABLES; SET SESSION debug="-d,sleep_open_and_lock_after_open"; # connection default DROP TABLE t1; -# -# Extra tests for Bug#26379 - Combination of FLUSH TABLE and -# REPAIR TABLE corrupts a MERGE table -# -CREATE TABLE t1 (c1 INT); -CREATE TABLE t2 (c1 INT); -CREATE TABLE t3 (c1 INT); -INSERT INTO t1 VALUES (1); -INSERT INTO t2 VALUES (2); -INSERT INTO t3 VALUES (3); -# -# CREATE ... SELECT -# try to access parent from another thread. -# -# connection con1 -SET SESSION debug="+d,sleep_create_select_before_lock"; -CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) -INSERT_METHOD=FIRST SELECT * FROM t3; -# connection default -# Now try to access the parent. -# If 3 is in table, SELECT had to wait. -SELECT * FROM t4 ORDER BY c1; -c1 -1 -2 -3 -# connection con1 -SET SESSION debug="-d,sleep_create_select_before_lock"; -# connection default -# Cleanup for next test. -DROP TABLE t4; -DELETE FROM t1 WHERE c1 != 1; -# -# CREATE ... SELECT -# try to access child from another thread. -# -# connection con1 -SET SESSION debug="+d,sleep_create_select_before_lock"; -CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) -INSERT_METHOD=FIRST SELECT * FROM t3; -# connection default -# Now try to access a child. -# If 3 is in table, SELECT had to wait. -SELECT * FROM t1 ORDER BY c1; -c1 -1 -3 -# connection con1 -SET SESSION debug="-d,sleep_create_select_before_lock"; -# connection default -DROP TABLE t1, t2, t3, t4; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index a709817b7ef..4c05000573c 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -964,6 +964,28 @@ id ref 3 2 4 5 DROP TABLE t1, t2, t3; +CREATE TABLE t1(a INT); +CREATE TABLE m1(a INT) ENGINE=MERGE; +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 +DROP TABLE m1; +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(); +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 +ALTER TABLE m1 UNION=(t1); +ALTER TABLE m1 UNION=(); +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, m1; End of 5.0 tests create table t1 (c1 int, index(c1)); create table t2 (c1 int, index(c1)) engine=merge union=(t1); @@ -1129,7 +1151,7 @@ SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `c1` int(11) DEFAULT NULL -) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=() +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT INTO t4 VALUES (4); ERROR HY000: Table 't4' is read only DROP TABLE t4; @@ -1146,7 +1168,7 @@ SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `c1` int(11) DEFAULT NULL -) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=() +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT INTO t4 VALUES (4); ERROR HY000: Table 't4' is read only DROP TABLE t4; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 264d1567003..112a7b7649b 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -2024,4 +2024,185 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +INSERT INTO t1 VALUES('b', 'b'); +INSERT INTO t1 VALUES('c', 'b'); +DELETE FROM t1 WHERE c1='b'; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1), +KEY (c1) +) ENGINE=MyISAM; +# Insert 100 rows. Query log disabled. +UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +ALTER TABLE t1 ENGINE=MyISAM; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +INSERT INTO t1 VALUES('b', 'b'); +INSERT INTO t1 VALUES('c', 'b'); +DELETE FROM t1 WHERE c1='b'; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1), +KEY (c1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +# Insert 100 rows. Query log disabled. +UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +ALTER TABLE t1 ENGINE=MyISAM; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 VARCHAR(10) NOT NULL, +c2 CHAR(10) DEFAULT NULL, +c3 VARCHAR(10) NOT NULL, +KEY (c1), +KEY (c2) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0; + +MyISAM file: MYSQLTEST_VARDIR/master-data/test/t1 +Record format: Packed +Character set: utf8_general_ci (33) +Data records: 0 Deleted blocks: 0 +Recordlength: 94 + +table description: +Key Start Len Index Type +1 2 30 multip. varchar +2 33 30 multip. char NULL +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index a0719d51670..b13010f8471 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1,4 +1,7 @@ drop table if exists t1; +create table t1 (a int) partition by list ((a/3)*10 div 1) +(partition p0 values in (0), partition p1 values in (1)); +ERROR HY000: This partition function is not allowed CREATE TABLE t1 ( d DATE NOT NULL ) @@ -44,15 +47,6 @@ partition by key(a) partitions 1e+300; ERROR 42000: Only integers allowed as number here near '1e+300' at line 3 create table t1 (a int) -partition by key (a) -(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); -ERROR 42000: Incorrect table name 'part-data' -create table t1 (a int) -partition by key (a) -(partition p0, -partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); -ERROR 42000: Incorrect table name 'part-data' -create table t1 (a int) partition by list (a) (partition p0 values in (1)); create procedure pz() @@ -342,7 +336,7 @@ PARTITION BY LIST (a) (PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); analyze table t1; Table Op Msg_type Msg_text -test.t1 analyze status OK +test.t1 analyze note The storage engine for the table doesn't support analyze drop table t1; create table t1 (a int) @@ -536,11 +530,9 @@ t1 CREATE TABLE `t1` ( alter table t1 partition by key(a) (partition p0, partition p1 engine=heap); -ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL alter table t1 partition by key(a) (partition p0 engine=heap, partition p1); -ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL alter table t1 engine=heap partition by key (a) @@ -1045,9 +1037,7 @@ SHOW TABLE STATUS; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 1 14 14 0 0 7 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned ALTER TABLE t1 OPTIMIZE PARTITION p0; -SHOW TABLE STATUS; -Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 MyISAM 10 Fixed 1 7 7 0 1024 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned +ERROR 42000: The storage engine for the table doesn't support optimize partition DROP TABLE t1; CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a); ALTER TABLE t1 DISABLE KEYS; @@ -1062,6 +1052,12 @@ test.t1 repair note The storage engine for the table doesn't support repair OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note The storage engine for the table doesn't support optimize +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check note The storage engine for the table doesn't support check +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze note The storage engine for the table doesn't support analyze drop table t1; drop procedure if exists mysqltest_1; create table t1 (a int) @@ -1300,6 +1296,14 @@ ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTENDED' at line 1 ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTENDED' at line 1 +ALTER TABLE t1 ANALYZE PARTITION p1; +ERROR 42000: The storage engine for the table doesn't support analyze partition +ALTER TABLE t1 CHECK PARTITION p1; +ERROR 42000: The storage engine for the table doesn't support check partition +ALTER TABLE t1 REPAIR PARTITION p1; +ERROR 42000: The storage engine for the table doesn't support repair partition +ALTER TABLE t1 OPTIMIZE PARTITION p1; +ERROR 42000: The storage engine for the table doesn't support optimize partition DROP TABLE t1; CREATE TABLE t1 (s1 BIGINT UNSIGNED) PARTITION BY RANGE (s1) ( diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result index c6506178b03..43d9aac13b7 100644 --- a/mysql-test/r/partition_datatype.result +++ b/mysql-test/r/partition_datatype.result @@ -1,4 +1,11 @@ drop table if exists t1; +# test with not null +create table t1 (a bit not null) partition by key (a); +insert into t1 values (b'1'); +select hex(a) from t1 where a = b'1'; +hex(a) +1 +drop table t1; create table t1 (a tinyint not null) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; @@ -30,22 +37,22 @@ a 2 drop table t1; create table t1 (a float not null) partition by key (a); -insert into t1 values (2.1); -select * from t1 where a = 2.1; +insert into t1 values (0.5); +select * from t1 where a = 0.5; a +0.5 drop table t1; create table t1 (a double not null) partition by key (a); -insert into t1 values (2.1); -select * from t1 where a = 2.1; +insert into t1 values (0.5); +select * from t1 where a = 0.5; a -2.1 +0.5 drop table t1; -create table t1 (a decimal not null) partition by key (a); +create table t1 (a decimal(4,2) not null) partition by key (a); insert into t1 values (2.1); -Warnings: -Note 1265 Data truncated for column 'a' at row 1 select * from t1 where a = 2.1; a +2.10 drop table t1; create table t1 (a date not null) partition by key (a); insert into t1 values ('2001-01-01'); @@ -125,6 +132,21 @@ select * from t1 where a = 'y'; a y drop table t1; +# test with null allowed +create table t1 (a bit) partition by key (a); +insert into t1 values (b'1'); +insert into t1 values (NULL); +select hex(a) from t1 where a = b'1'; +hex(a) +1 +select hex(a) from t1 where a is NULL; +hex(a) +NULL +select hex(a) from t1 order by a; +hex(a) +NULL +1 +drop table t1; create table t1 (a tinyint) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; @@ -156,22 +178,22 @@ a 2 drop table t1; create table t1 (a float) partition by key (a); -insert into t1 values (2.1); -select * from t1 where a = 2.1; +insert into t1 values (0.5); +select * from t1 where a = 0.5; a +0.5 drop table t1; create table t1 (a double) partition by key (a); -insert into t1 values (2.1); -select * from t1 where a = 2.1; +insert into t1 values (0.5); +select * from t1 where a = 0.5; a -2.1 +0.5 drop table t1; -create table t1 (a decimal) partition by key (a); +create table t1 (a decimal(4,2)) partition by key (a); insert into t1 values (2.1); -Warnings: -Note 1265 Data truncated for column 'a' at row 1 select * from t1 where a = 2.1; a +2.10 drop table t1; create table t1 (a date) partition by key (a); insert into t1 values ('2001-01-01'); @@ -254,25 +276,41 @@ drop table t1; create table t1 (a varchar(65531)) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); -select * from t1 where a = 'aaa%'; +select * from t1 where a = 'aaaa'; a +aaaa select * from t1 where a like 'aaa%'; a aaaa +select * from t1 where a = 'bbbb'; +a +bbbb drop table t1; create table t1 (a varchar(65532)) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); -select * from t1 where a = 'aaa%'; +select * from t1 where a = 'aaaa'; a +aaaa select * from t1 where a like 'aaa%'; a aaaa +select * from t1 where a = 'bbbb'; +a +bbbb drop table t1; create table t1 (a varchar(65533) not null) partition by key (a); +insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); -select * from t1 where a = 'aaa%'; +select * from t1 where a = 'aaaa'; +a +aaaa +select * from t1 where a like 'aaa%'; a +aaaa +select * from t1 where a = 'bbbb'; +a +bbbb drop table t1; create table t1 (a varchar(65533)) partition by key (a); ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs @@ -280,3 +318,17 @@ create table t1 (a varchar(65534) not null) partition by key (a); ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs create table t1 (a varchar(65535)) partition by key (a); ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +create table t1 (a bit(27), primary key (a)) engine=myisam +partition by hash (a) +(partition p0, partition p1, partition p2); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bit(27) NOT NULL DEFAULT '\0\0\0\0', + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM, PARTITION p2 ENGINE = MyISAM) */ +insert into t1 values (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34); +select hex(a) from t1 where a = 7; +hex(a) +7 +drop table t1; diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index 46532cb32ab..585447ff333 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1,4 +1,17 @@ drop table if exists t1; +CREATE TABLE t1 (a INT) +PARTITION BY HASH (a) +( PARTITION p0 ENGINE=MyISAM, +PARTITION p1); +ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL +CREATE TABLE t1 (a INT) +PARTITION BY LIST (a) +SUBPARTITION BY HASH (a) +( PARTITION p0 VALUES IN (0) +( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2), +PARTITION p1 VALUES IN (1) +( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM)); +ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL CREATE TABLE t1 ( a int ) diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 8cb8bc7c13f..6e56f9023eb 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -79,8 +79,7 @@ engine = innodb partition by list (a) (partition p0 values in (0)); alter table t1 engine = x; -Warnings: -Warning 1286 Unknown table engine 'x' +ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -151,9 +150,11 @@ DROP TABLE t1; create table t1 (int_column int, char_column char(5)) PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); -alter table t1 PARTITION BY RANGE (int_column) +alter table t1 +ENGINE = MyISAM +PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 -(PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); +(PARTITION p1 VALUES LESS THAN (5)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/partition_not_windows.result b/mysql-test/r/partition_not_windows.result index 501d3a469a1..44afdea3855 100644 --- a/mysql-test/r/partition_not_windows.result +++ b/mysql-test/r/partition_not_windows.result @@ -3,13 +3,13 @@ partition by range (a) subpartition by hash (a) (partition p0 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' (SUBPARTITION subpart00, SUBPARTITION subpart01)); -Checking if file exists before alter +# Checking if file exists before alter ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (partition p1 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' (SUBPARTITION subpart10, SUBPARTITION subpart11), partition p2 VALUES LESS THAN (2) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' (SUBPARTITION subpart20, SUBPARTITION subpart21)); -Checking if file exists after alter +# Checking if file exists after alter drop table t1; set @org_mode=@@sql_mode; set @@sql_mode='NO_DIR_IN_CREATE'; @@ -23,6 +23,9 @@ partition p01 values less than (1000) data directory='/not/existing' index directory='/not/existing' ); +Warnings: +Warning 0 DATA DIRECTORY option ignored +Warning 0 INDEX DIRECTORY option ignored show create table t2; Table Create Table t2 CREATE TABLE `t2` ( @@ -33,11 +36,11 @@ set @@sql_mode=@org_mode; CREATE TABLE t1(a INT) PARTITION BY KEY (a) (PARTITION p0 DATA DIRECTORY 'TEST_DIR/master-data/test'); -ERROR HY000: Incorrect arguments to DATA DIRECORY +ERROR HY000: Incorrect arguments to DATA DIRECTORY CREATE TABLE t1(a INT) PARTITION BY KEY (a) (PARTITION p0 INDEX DIRECTORY 'TEST_DIR/master-data/test'); -ERROR HY000: Incorrect arguments to INDEX DIRECORY +ERROR HY000: Incorrect arguments to INDEX DIRECTORY CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( @@ -47,7 +50,7 @@ DATA DIRECTORY = 'TEST_DIR/master-data/test', SUBPARTITION s0b DATA DIRECTORY = 'TEST_DIR/master-data/test' )); -ERROR HY000: Incorrect arguments to DATA DIRECORY +ERROR HY000: Incorrect arguments to DATA DIRECTORY CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( @@ -57,7 +60,7 @@ INDEX DIRECTORY = 'TEST_DIR/master-data/test', SUBPARTITION s0b INDEX DIRECTORY = 'TEST_DIR/master-data/test' )); -ERROR HY000: Incorrect arguments to INDEX DIRECORY +ERROR HY000: Incorrect arguments to INDEX DIRECTORY DROP TABLE IF EXISTS `example`; CREATE TABLE `example` ( `ID_EXAMPLE` int(10) unsigned NOT NULL AUTO_INCREMENT, @@ -66,9 +69,27 @@ CREATE TABLE `example` ( PRIMARY KEY (`ID_EXAMPLE`) ) ENGINE = MYISAM PARTITION BY HASH(ID_EXAMPLE)( -PARTITION p0 DATA DIRECTORY = '/build/5.1/data/partitiontest/p0Data', -PARTITION p1 DATA DIRECTORY = '/build/5.1/data/partitiontest/p1Data', -PARTITION p2 DATA DIRECTORY = '/build/5.1/data/partitiontest/p2Data', -PARTITION p3 DATA DIRECTORY = '/build/5.1/data/partitiontest/p3Data' +PARTITION p0 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p0Data' +INDEX DIRECTORY = 'MYSQLTEST_VARDIR/p0Index', +PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p1Data' +INDEX DIRECTORY = 'MYSQLTEST_VARDIR/p1Index', +PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p2Data' +INDEX DIRECTORY = 'MYSQLTEST_VARDIR/p2Index', +PARTITION p3 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p3Data' +INDEX DIRECTORY = 'MYSQLTEST_VARDIR/p3Index' ); -ERROR HY000: Can't create/write to file '/build/5.1/data/partitiontest/p0Data/example#P#p0.MYD' (Errcode: 2) +# Checking that MyISAM .MYD and .MYI are in test db and data/idx dir +DROP TABLE example; +CREATE TABLE `example` ( +`ID_EXAMPLE` int(10) unsigned NOT NULL AUTO_INCREMENT, +`DESCRIPTION` varchar(30) NOT NULL, +`LEVEL` smallint(5) unsigned DEFAULT NULL, +PRIMARY KEY (`ID_EXAMPLE`) +) ENGINE = MYISAM +PARTITION BY HASH(ID_EXAMPLE)( +PARTITION p0 DATA DIRECTORY = '/not/existent/p0Data', +PARTITION p1 DATA DIRECTORY = '/not/existent/p1Data', +PARTITION p2 DATA DIRECTORY = '/not/existent/p2Data', +PARTITION p3 DATA DIRECTORY = '/not/existent/p3Data' +); +Got one of the listed errors diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index 4da79704ec3..b027ef088ae 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -1,4 +1,37 @@ drop table if exists t1, t2; +create table t1 (a int) +partition by range (a) +( partition p0 values less than (maxvalue)); +alter table t1 add partition (partition p1 values less than (100000)); +ERROR HY000: MAXVALUE can only be used in last partition definition +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +drop table t1; +create table t1 (a integer) +partition by range (a) +( partition p0 values less than (4), +partition p1 values less than (100)); +create trigger tr1 before insert on t1 +for each row begin +set @a = 1; +end| +alter table t1 drop partition p0; +drop table t1; +create table t1 (a integer) +partition by range (a) +( partition p0 values less than (4), +partition p1 values less than (100)); +LOCK TABLES t1 WRITE; +alter table t1 drop partition p0; +alter table t1 reorganize partition p1 into +( partition p0 values less than (4), +partition p1 values less than (100)); +alter table t1 add partition ( partition p2 values less than (200)); +UNLOCK TABLES; +drop table t1; create table t1 (a int unsigned) partition by range (a) (partition pnull values less than (0), diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result index 362e97a6262..eb92a4f733d 100644 --- a/mysql-test/r/partition_symlink.result +++ b/mysql-test/r/partition_symlink.result @@ -81,3 +81,41 @@ Got one of the listed errors DROP DATABASE mysqltest2; USE test; DROP USER mysqltest_1@localhost; +create table t2 (i int ) +partition by range (i) +( +partition p01 values less than (1000) +data directory="MYSQLTEST_VARDIR/tmp" + index directory="MYSQLTEST_VARDIR/tmp" +); +set @org_mode=@@sql_mode; +set @@sql_mode='NO_DIR_IN_CREATE'; +select @@sql_mode; +@@sql_mode +NO_DIR_IN_CREATE +create table t1 (i int ) +partition by range (i) +( +partition p01 values less than (1000) +data directory='/not/existing' + index directory='/not/existing' +); +Warnings: +Warning 0 DATA DIRECTORY option ignored +Warning 0 INDEX DIRECTORY option ignored +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (i) (PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ +DROP TABLE t1, t2; +set @@sql_mode=@org_mode; +create table t1 (a int) +partition by key (a) +(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); +ERROR 42000: Incorrect table name 'part-data' +create table t1 (a int) +partition by key (a) +(partition p0, +partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); +ERROR 42000: Incorrect table name 'part-data' diff --git a/mysql-test/r/partition_windows.result b/mysql-test/r/partition_windows.result index 1fc9e2ba254..c2d4054deeb 100644 --- a/mysql-test/r/partition_windows.result +++ b/mysql-test/r/partition_windows.result @@ -1,14 +1,30 @@ -DROP TABLE IF EXISTS `example`; -CREATE TABLE `example` ( -`ID_EXAMPLE` int(10) unsigned NOT NULL AUTO_INCREMENT, -`DESCRIPTION` varchar(30) NOT NULL, -`LEVEL` smallint(5) unsigned DEFAULT NULL, -PRIMARY KEY (`ID_EXAMPLE`) +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +c1 int(10) unsigned NOT NULL AUTO_INCREMENT, +c2 varchar(30) NOT NULL, +c3 smallint(5) unsigned DEFAULT NULL, +PRIMARY KEY (c1) ) ENGINE = MYISAM -PARTITION BY HASH(ID_EXAMPLE)( -PARTITION p0 DATA DIRECTORY = 'C:/build/5.1/data/partitiontest/p0Data', -PARTITION p1 DATA DIRECTORY = 'C:/build/5.1/data/partitiontest/p1Data', -PARTITION p2 DATA DIRECTORY = 'C:/build/5.1/data/partitiontest/p2Data', -PARTITION p3 DATA DIRECTORY = 'C:/build/5.1/data/partitiontest/p3Data' +PARTITION BY HASH(c1)( +PARTITION p0 +DATA DIRECTORY = 'C:/mysqltest/p0Data' + INDEX DIRECTORY = 'D:/mysqltest/p0Index', +PARTITION p1, +PARTITION p2 +DATA DIRECTORY = 'E:/mysqltest/p2Data' + INDEX DIRECTORY = 'F:/mysqltest/p2Index' ); -ERROR HY000: Can't create/write to file 'C:\build\5.1\data\partitiontest\p0Data\example#P#p0.MYD' (Errcode: 2) +Warnings: +Warning 0 DATA DIRECTORY option ignored +Warning 0 INDEX DIRECTORY option ignored +Warning 0 DATA DIRECTORY option ignored +Warning 0 INDEX DIRECTORY option ignored +INSERT INTO t1 VALUES (NULL, "first", 1); +INSERT INTO t1 VALUES (NULL, "second", 2); +INSERT INTO t1 VALUES (NULL, "third", 3); +ALTER TABLE t1 ADD PARTITION (PARTITION p3 DATA DIRECTORY = 'G:/mysqltest/p3Data' INDEX DIRECTORY = 'H:/mysqltest/p3Index'); +Warnings: +Warning 0 DATA DIRECTORY option ignored +Warning 0 INDEX DIRECTORY option ignored +INSERT INTO t1 VALUES (NULL, "last", 4); +DROP TABLE t1; diff --git a/mysql-test/r/plugin_load.result b/mysql-test/r/plugin_load.result new file mode 100644 index 00000000000..33116cc20ab --- /dev/null +++ b/mysql-test/r/plugin_load.result @@ -0,0 +1,3 @@ +SELECT @@global.example_enum_var = 'e2'; +@@global.example_enum_var = 'e2' +1 diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index ea42b6616a1..34c86d6c8c6 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1664,6 +1664,32 @@ set GLOBAL query_cache_type=default; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; +FLUSH STATUS; +SET GLOBAL query_cache_size=10*1024*1024; +SET @save_concurrent_insert= @@concurrent_insert; +SET GLOBAL concurrent_insert= 0; +CREATE TABLE t1 (c1 INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 (c1) VALUES (1), (2); +SHOW GLOBAL VARIABLES LIKE 'concurrent_insert'; +Variable_name Value +concurrent_insert 0 +SHOW STATUS LIKE 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +SELECT * FROM t1; +c1 +1 +2 +SELECT * FROM t1; +c1 +1 +2 +SHOW STATUS LIKE 'Qcache_hits'; +Variable_name Value +Qcache_hits 1 +DROP TABLE t1; +SET GLOBAL concurrent_insert= @save_concurrent_insert; +SET GLOBAL query_cache_size= default; End of 5.0 tests CREATE TABLE t1 (a ENUM('rainbow')); INSERT INTO t1 VALUES (),(),(),(),(); diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index 3efb01fdb94..d15efc6d7d7 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -70,7 +70,7 @@ c 2 show status like 'Qcache_hits'| Variable_name Value -Qcache_hits 0 +Qcache_hits 2 set global query_cache_size = @x| flush status| flush query cache| diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 97bc092aa22..bda0d7d1451 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -22,6 +22,7 @@ insert into t1 (b) select b from t2; insert into t2 (b) select b from t1; insert into t1 (b) select b from t2; drop table t2; +create table t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam data directory="MYSQLTEST_VARDIR/tmp" index directory="MYSQLTEST_VARDIR/run"; insert into t9 select * from t1; check table t9; Table Op Msg_type Msg_text @@ -54,10 +55,16 @@ t9 CREATE TABLE `t9` ( `d` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=16725 DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/' +create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam data directory="tmp"; Got one of the listed errors +create database mysqltest; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam index directory="/this-dir-does-not-exist"; Got one of the listed errors +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam index directory="not-hard-path"; Got one of the listed errors +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam index directory="MYSQLTEST_VARDIR/run"; Got one of the listed errors +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam data directory="MYSQLTEST_VARDIR/tmp"; Got one of the listed errors alter table t9 rename mysqltest.t9; select count(*) from mysqltest.t9; @@ -74,6 +81,7 @@ t9 CREATE TABLE `t9` ( ) ENGINE=MyISAM AUTO_INCREMENT=16725 DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/' drop database mysqltest; create table t1 (a int not null) engine=myisam; +alter table t1 data directory="MYSQLTEST_VARDIR/tmp"; Warnings: Warning 0 DATA DIRECTORY option ignored show create table t1; @@ -82,6 +90,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 add b int; +alter table t1 data directory="MYSQLTEST_VARDIR/log"; Warnings: Warning 0 DATA DIRECTORY option ignored show create table t1; @@ -90,6 +99,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 index directory="MYSQLTEST_VARDIR/log"; Warnings: Warning 0 INDEX DIRECTORY option ignored show create table t1; @@ -102,18 +112,20 @@ drop table t1; CREATE TABLE t1(a INT) DATA DIRECTORY='TEST_DIR/tmp' INDEX DIRECTORY='TEST_DIR/tmp'; -ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17) +Got one of the listed errors CREATE TABLE t2(a INT) DATA DIRECTORY='TEST_DIR/tmp' INDEX DIRECTORY='TEST_DIR/tmp'; RENAME TABLE t2 TO t1; ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17) DROP TABLE t2; +create temporary table t1 (a int) engine=myisam data directory="MYSQLTEST_VARDIR/log" select 9 a; show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/log/' +create temporary table t1 (a int) engine=myisam data directory="MYSQLTEST_VARDIR/log" select 99 a; show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( @@ -140,16 +152,24 @@ DROP TABLE t1; End of 5.0 tests CREATE TABLE t1(a INT) INDEX DIRECTORY='TEST_DIR/master-data/mysql'; -ERROR HY000: Incorrect arguments to INDEX DIRECORY +ERROR HY000: Incorrect arguments to INDEX DIRECTORY CREATE TABLE t1(a INT) DATA DIRECTORY='TEST_DIR/master-data/test'; -ERROR HY000: Incorrect arguments to DATA DIRECORY +ERROR HY000: Incorrect arguments to DATA DIRECTORY CREATE TABLE t1(a INT) DATA DIRECTORY='TEST_DIR/master-data/'; -DROP TABLE t1; +ERROR HY000: Incorrect arguments to DATA DIRECTORY CREATE TABLE t1(a INT) INDEX DIRECTORY='TEST_DIR/master-data'; -DROP TABLE t1; +ERROR HY000: Incorrect arguments to INDEX DIRECTORY CREATE TABLE t1(a INT) INDEX DIRECTORY='TEST_DIR/master-data_var'; ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (Errcode: 2) +SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='NO_DIR_IN_CREATE'; +CREATE TABLE t1(a INT) DATA DIRECTORY='MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp'; +Warnings: +Warning 0 DATA DIRECTORY option ignored +Warning 0 INDEX DIRECTORY option ignored +DROP TABLE t1; +SET @@SQL_MODE=@OLD_SQL_MODE; +End of 5.1 tests diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 659215eda33..e36f21f2996 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -93,3 +93,4 @@ c 1,2,3 64 DROP TABLE t1; +End of 5.0 tests |