diff options
Diffstat (limited to 'mysql-test')
25 files changed, 728 insertions, 9 deletions
diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index f8737d8082b..fd1b0a1bb86 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -134,3 +134,16 @@ test.t1.product Computer TV 2 8 0 0 4.2500 NULL ENUM('Computer','Phone','TV') NO sum(profit) 10 6900 2 4 0 0 1946 2868 ENUM('10','275','600','6900') NOT NULL avg(profit) 10.0000 1380.0000 7 9 0 0 394.6875 570.2003 ENUM('10.0000','68.7500','120.0000','1380.0000') NOT NULL drop table t1,t2; +create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5)); +insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555); +select f1 from t1 procedure analyse(1, 1); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.f1 5.99900 9.55500 7 7 0 0 7.77700 1.77800 FLOAT(4,3) NOT NULL +select f2 from t1 procedure analyse(1, 1); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.f2 5.9999 9.5555 6 6 0 0 6.0000 NULL FLOAT(5,4) UNSIGNED NOT NULL +select f3 from t1 procedure analyse(1, 1); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.f3 5.99999 9.55555 7 7 0 0 7.77777 1.77778 FLOAT(6,5) NOT NULL +drop table t1; +End of 4.1 tests diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 5a175ba1713..fdf21a50a02 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1155,6 +1155,81 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +set names utf8; +create table t1 (s1 char(5) character set utf8); +insert into t1 values +('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%'; +before_delete_general_ci +ペテルグル +delete from t1 where s1 = 'Y'; +select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%'; +after_delete_general_ci +ペテルグル +drop table t1; +set names utf8; +create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci); +insert into t1 values +('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%'; +before_delete_unicode_ci +ペテルグル +delete from t1 where s1 = 'Y'; +select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%'; +after_delete_unicode_ci +ペテルグル +drop table t1; +set names utf8; +create table t1 (s1 char(5) character set utf8 collate utf8_bin); +insert into t1 values +('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_bin from t1 where s1 like 'ペテ%'; +before_delete_bin +ペテルグル +delete from t1 where s1 = 'Y'; +select s1 as after_delete_bin from t1 where s1 like 'ペテ%'; +after_delete_bin +ペテルグル +drop table t1; +set names utf8; +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8 collate utf8_general_ci; +insert into t1 values ('あいうえおかきくけこさしすせそ'); +insert into t1 values ('さしすせそかきくけこあいうえお'); +select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; +gci1 +さしすせそかきくけこあいうえお +select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; +gci2 +あいうえおかきくけこさしすせそ +drop table t1; +set names utf8; +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8 collate utf8_unicode_ci; +insert into t1 values ('あいうえおかきくけこさしすせそ'); +insert into t1 values ('さしすせそかきくけこあいうえお'); +select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; +uci1 +さしすせそかきくけこあいうえお +select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; +uci2 +あいうえおかきくけこさしすせそ +drop table t1; +set names utf8; +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8 collate utf8_bin; +insert into t1 values ('あいうえおかきくけこさしすせそ'); +insert into t1 values ('さしすせそかきくけこあいうえお'); +select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%'; +bin1 +さしすせそかきくけこあいうえお +select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ'; +bin2 +あいうえおかきくけこさしすせそ +drop table t1; SET NAMES utf8; CREATE TABLE t1 (id int PRIMARY KEY, a varchar(16) collate utf8_unicode_ci NOT NULL default '', @@ -1340,3 +1415,15 @@ select a from t1 group by a; a e drop table t1; +create table t1(a char(10)) default charset utf8; +insert into t1 values ('123'), ('456'); +explain +select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where +select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; +substr(Z.a,-1) a +3 123 +6 456 +drop table t1; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index db0125b7d4f..d4a46bfd79f 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -74,11 +74,6 @@ grp group_concat(c order by 1) 1 a 2 b,c 3 C,D,d,d,D,E -select grp,group_concat(c order by "c") from t1 group by grp; -grp group_concat(c order by "c") -1 a -2 b,c -3 C,D,d,d,D,E select grp,group_concat(distinct c order by c) from t1 group by grp; grp group_concat(distinct c order by c) 1 a diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 97dd8e243b2..6f41855f609 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -694,6 +694,18 @@ t1 CREATE TABLE `t1` ( `from_unixtime(1) + 0` double(23,6) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +SET NAMES latin1; +SET character_set_results = NULL; +SHOW VARIABLES LIKE 'character_set_results'; +Variable_name Value +character_set_results +CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY); +INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd'); +SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868; +fmtddate field2 +Sep-4 12:00AM abcd +DROP TABLE testBug8868; +SET NAMES DEFAULT; (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H); diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 3fcae8843c0..bdb3de4de75 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -857,3 +857,14 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +CREATE TABLE t1 (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,1))); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,0))); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,1))); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0))); +SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); +1 +1 +1 +1 +DROP TABLE t1; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 4ad28091164..61b73dc7005 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -773,3 +773,48 @@ select sql_buffer_result max(f1)+1 from t1; max(f1)+1 3 drop table t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT a FROM t1 GROUP BY 'a'; +a +1 +SELECT a FROM t1 GROUP BY "a"; +a +1 +SELECT a FROM t1 GROUP BY `a`; +a +1 +2 +set sql_mode=ANSI_QUOTES; +SELECT a FROM t1 GROUP BY "a"; +a +1 +2 +SELECT a FROM t1 GROUP BY 'a'; +a +1 +SELECT a FROM t1 GROUP BY `a`; +a +1 +2 +set sql_mode=''; +SELECT a FROM t1 HAVING 'a' > 1; +a +SELECT a FROM t1 HAVING "a" > 1; +a +SELECT a FROM t1 HAVING `a` > 1; +a +2 +SELECT a FROM t1 ORDER BY 'a' DESC; +a +1 +2 +SELECT a FROM t1 ORDER BY "a" DESC; +a +1 +2 +SELECT a FROM t1 ORDER BY `a` DESC; +a +2 +1 +DROP TABLE t1; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 05f72b22ed1..b34c127595f 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -797,6 +797,132 @@ a b xxxxxxxxx bbbbbb xxxxxxxxx bbbbbb DROP TABLE t1; +SET @@myisam_repair_threads=2; +SHOW VARIABLES LIKE 'myisam_repair%'; +Variable_name Value +myisam_repair_threads 2 +CREATE TABLE t1 ( +`_id` int(11) NOT NULL default '0', +`url` text, +`email` text, +`description` text, +`loverlap` int(11) default NULL, +`roverlap` int(11) default NULL, +`lneighbor_id` int(11) default NULL, +`rneighbor_id` int(11) default NULL, +`length_` int(11) default NULL, +`sequence` mediumtext, +`name` text, +`_obj_class` text NOT NULL, +PRIMARY KEY (`_id`), +UNIQUE KEY `sequence_name_index` (`name`(50)), +KEY (`length_`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES +(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), +(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), +(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), +(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), +(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), +(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), +(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), +(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), +(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); +SELECT _id FROM t1; +_id +1 +2 +3 +4 +5 +6 +7 +8 +9 +DELETE FROM t1 WHERE _id < 8; +SHOW TABLE STATUS LIKE 't1'; +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 9 Dynamic 2 # # # # 140 # # # # # # +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +SHOW TABLE STATUS LIKE 't1'; +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 9 Dynamic 2 # # # # 0 # # # # # # +SELECT _id FROM t1; +_id +8 +9 +DROP TABLE t1; +CREATE TABLE t1 ( +`_id` int(11) NOT NULL default '0', +`url` text, +`email` text, +`description` text, +`loverlap` int(11) default NULL, +`roverlap` int(11) default NULL, +`lneighbor_id` int(11) default NULL, +`rneighbor_id` int(11) default NULL, +`length_` int(11) default NULL, +`sequence` mediumtext, +`name` text, +`_obj_class` text NOT NULL, +PRIMARY KEY (`_id`), +UNIQUE KEY `sequence_name_index` (`name`(50)), +KEY (`length_`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES +(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), +(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), +(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), +(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), +(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), +(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), +(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), +(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), +(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); +SELECT _id FROM t1; +_id +1 +2 +3 +4 +5 +6 +7 +8 +9 +DELETE FROM t1 WHERE _id < 8; +SHOW TABLE STATUS LIKE 't1'; +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 9 Dynamic 2 # # # # 140 # # # # # # +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1 QUICK; +Table Op Msg_type Msg_text +test.t1 repair status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +SHOW TABLE STATUS LIKE 't1'; +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 9 Dynamic 2 # # # # 140 # # # # # # +SELECT _id FROM t1; +_id +8 +9 +DROP TABLE t1; +SET @@myisam_repair_threads=1; +SHOW VARIABLES LIKE 'myisam_repair%'; +Variable_name Value +myisam_repair_threads 1 show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index fef990297d9..74b7570ea2a 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -541,3 +541,18 @@ a max(b) NULL 2 a 1 drop table t1; +create table t1 (a varchar(22) not null , b int); +insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10); +select left(a,10), a, sum(b) from t1 group by 1,2 with rollup; +left(a,10) a sum(b) +2006-07-01 2006-07-01 21:30 1 +2006-07-01 2006-07-01 23:30 10 +2006-07-01 NULL 11 +NULL NULL 11 +select left(a,10) x, a, sum(b) from t1 group by x,a with rollup; +x a sum(b) +2006-07-01 2006-07-01 21:30 1 +2006-07-01 2006-07-01 23:30 10 +2006-07-01 NULL 11 +NULL NULL 11 +drop table t1; diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index c069824e9f0..bf46d19777f 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -48,3 +48,38 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 1 a 1 a A 5 NULL NULL YES BTREE SET myisam_repair_threads=@@global.myisam_repair_threads; DROP TABLE t1; +CREATE TABLE t1(a INT); +USE mysql; +REPAIR TABLE test.t1 USE_FRM; +Table Op Msg_type Msg_text +test.t1 repair status OK +USE test; +DROP TABLE t1; +CREATE TABLE t1(a CHAR(255), KEY(a)); +SET myisam_sort_buffer_size=4096; +INSERT INTO t1 VALUES +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'); +SET myisam_repair_threads=2; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair error sort_buffer_size is to small +test.t1 repair warning Number of rows changed from 0 to 157 +test.t1 repair status OK +SET myisam_repair_threads=@@global.myisam_repair_threads; +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 0b6bc48c350..80d4e8cc06d 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -108,3 +108,20 @@ d c bar 2 foo 1 drop table t1, t2; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (i INT); +LOCK TABLE t1 WRITE; +CREATE TEMPORARY TABLE t1 (i INT); +The following command should not block +DROP TEMPORARY TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); +DROP TEMPORARY TABLE t2, t1; +ERROR 42S02: Unknown table 't1' +SELECT * FROM t2; +ERROR 42S02: Table 'test.t2' doesn't exist +SELECT * FROM t1; +i +DROP TABLE t1; +End of 4.1 tests. diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index ab3c441a7e2..0fe3f674fba 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1745,3 +1745,12 @@ create table t1 (a set('x','y') default 'x'); alter table t1 alter a set default 'z'; ERROR 42000: Invalid default value for 'a' drop table t1; +create table t1 (f1 int); +alter table t1 add f2 enum(0xFFFF); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` enum('') default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index e8daeb08526..95050163787 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -272,3 +272,10 @@ desc t3; Field Type Null Key Default Extra a double 0 drop table t1,t2,t3; +select 1e-308, 1.00000001e-300, 100000000e-300; +1e-308 1.00000001e-300 100000000e-300 +0 1.00000001e-300 1e-292 +select 10e307; +10e307 +1e+308 +End of 4.1 tests diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index dfca8f575a4..88fe8dc55e7 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -82,4 +82,16 @@ create table t2 (country_id int primary key, country char(20) not null); insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland'); select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse(); drop table t1,t2; -# End of 4.1 tests + +# +# Bug #20305 PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D) +# + +create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5)); +insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555); +select f1 from t1 procedure analyse(1, 1); +select f2 from t1 procedure analyse(1, 1); +select f3 from t1 procedure analyse(1, 1); +drop table t1; + +--echo End of 4.1 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index eb395680cc9..af40121852f 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -945,6 +945,76 @@ check table t1; drop table t1; # +# Bug#20471 LIKE search fails with indexed utf8 char column +# +set names utf8; +create table t1 (s1 char(5) character set utf8); +insert into t1 values +('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%'; +delete from t1 where s1 = 'Y'; +select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%'; +drop table t1; + +set names utf8; +create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci); +insert into t1 values +('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%'; +delete from t1 where s1 = 'Y'; +select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%'; +drop table t1; + +set names utf8; +create table t1 (s1 char(5) character set utf8 collate utf8_bin); +insert into t1 values +('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_bin from t1 where s1 like 'ペテ%'; +delete from t1 where s1 = 'Y'; +select s1 as after_delete_bin from t1 where s1 like 'ペテ%'; +drop table t1; + +# additional tests from duplicate bug#20744 MySQL return no result + +set names utf8; +--disable_warnings +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8 collate utf8_general_ci; +--enable_warnings +insert into t1 values ('あいうえおかきくけこさしすせそ'); +insert into t1 values ('さしすせそかきくけこあいうえお'); +select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; +select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; +drop table t1; + +set names utf8; +--disable_warnings +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8 collate utf8_unicode_ci; +--enable_warnings +insert into t1 values ('あいうえおかきくけこさしすせそ'); +insert into t1 values ('さしすせそかきくけこあいうえお'); +select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; +select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; +drop table t1; + +set names utf8; +--disable_warnings +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8 collate utf8_bin; +--enable_warnings +insert into t1 values ('あいうえおかきくけこさしすせそ'); +insert into t1 values ('さしすせそかきくけこあいうえお'); +select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%'; +select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ'; +drop table t1; + + + +# # Bug#14896: Comparison with a key in a partial index over mb chararacter field # @@ -1072,4 +1142,15 @@ explain select a from t1 group by a; select a from t1 group by a; drop table t1; + +# +# Bug #20204: "order by" changes the results returned +# + +create table t1(a char(10)) default charset utf8; +insert into t1 values ('123'), ('456'); +explain + select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; +select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 8f50690dd8b..d51d88d50ef 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -32,7 +32,6 @@ select grp,group_concat(d order by a desc) from t1 group by grp; select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp; select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp; select grp,group_concat(c order by 1) from t1 group by grp; -select grp,group_concat(c order by "c") from t1 group by grp; select grp,group_concat(distinct c order by c) from t1 group by grp; select grp,group_concat(distinct c order by c desc) from t1 group by grp; explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 04bfc741d1c..714379d0fb3 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -369,6 +369,25 @@ show create table t1; drop table t1; # +# 21913: DATE_FORMAT() Crashes mysql server if I use it through +# mysql-connector-j driver. +# + +SET NAMES latin1; +SET character_set_results = NULL; +SHOW VARIABLES LIKE 'character_set_results'; + +CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY); +INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd'); + +SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868; + +DROP TABLE testBug8868; + +SET NAMES DEFAULT; + + +# # Bug #19844 time_format in Union truncates values # diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index eba53a8a9c5..cdd8d1f3f0f 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -232,4 +232,14 @@ INSERT INTO t1 (c1) VALUES ( CHECK TABLE t1 EXTENDED; DROP TABLE t1; +# +# Bug #21888: Query on GEOMETRY field using PointFromWKB() results in lost connection +# +CREATE TABLE t1 (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,1))); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,0))); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,1))); +INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0))); +SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); +DROP TABLE t1; # End of 4.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index f14fab2d30e..064d46aa0c0 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -610,4 +610,27 @@ select sql_buffer_result max(f1) is null from t1; select sql_buffer_result max(f1)+1 from t1; drop table t1; +# +# BUG#14019-4.1-opt +# +CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2); + +SELECT a FROM t1 GROUP BY 'a'; +SELECT a FROM t1 GROUP BY "a"; +SELECT a FROM t1 GROUP BY `a`; + +set sql_mode=ANSI_QUOTES; +SELECT a FROM t1 GROUP BY "a"; +SELECT a FROM t1 GROUP BY 'a'; +SELECT a FROM t1 GROUP BY `a`; +set sql_mode=''; + +SELECT a FROM t1 HAVING 'a' > 1; +SELECT a FROM t1 HAVING "a" > 1; +SELECT a FROM t1 HAVING `a` > 1; + +SELECT a FROM t1 ORDER BY 'a' DESC; +SELECT a FROM t1 ORDER BY "a" DESC; +SELECT a FROM t1 ORDER BY `a` DESC; +DROP TABLE t1; # End of 4.1 tests diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 7f7b573e7ee..883315994fe 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -3,6 +3,7 @@ # i.e. lower_case_filesystem=OFF # -- source include/have_case_sensitive_file_system.inc +-- source include/not_embedded.inc connect (master,localhost,root,,); connection master; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index a62a6487882..745e3a2e377 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -763,6 +763,97 @@ SELECT * FROM t1; DROP TABLE t1; # +# Bug#8283 - OPTIMIZE TABLE causes data loss +# +SET @@myisam_repair_threads=2; +SHOW VARIABLES LIKE 'myisam_repair%'; +# +# Test OPTIMIZE. This creates a new data file. +CREATE TABLE t1 ( + `_id` int(11) NOT NULL default '0', + `url` text, + `email` text, + `description` text, + `loverlap` int(11) default NULL, + `roverlap` int(11) default NULL, + `lneighbor_id` int(11) default NULL, + `rneighbor_id` int(11) default NULL, + `length_` int(11) default NULL, + `sequence` mediumtext, + `name` text, + `_obj_class` text NOT NULL, + PRIMARY KEY (`_id`), + UNIQUE KEY `sequence_name_index` (`name`(50)), + KEY (`length_`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +# +INSERT INTO t1 VALUES + (1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), + (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), + (3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), + (4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), + (5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), + (6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), + (7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), + (8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), + (9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); +# +SELECT _id FROM t1; +DELETE FROM t1 WHERE _id < 8; +--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 # +SHOW TABLE STATUS LIKE 't1'; +CHECK TABLE t1 EXTENDED; +OPTIMIZE TABLE t1; +CHECK TABLE t1 EXTENDED; +--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 # +SHOW TABLE STATUS LIKE 't1'; +SELECT _id FROM t1; +DROP TABLE t1; +# +# Test REPAIR QUICK. This retains the old data file. +CREATE TABLE t1 ( + `_id` int(11) NOT NULL default '0', + `url` text, + `email` text, + `description` text, + `loverlap` int(11) default NULL, + `roverlap` int(11) default NULL, + `lneighbor_id` int(11) default NULL, + `rneighbor_id` int(11) default NULL, + `length_` int(11) default NULL, + `sequence` mediumtext, + `name` text, + `_obj_class` text NOT NULL, + PRIMARY KEY (`_id`), + UNIQUE KEY `sequence_name_index` (`name`(50)), + KEY (`length_`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +# +INSERT INTO t1 VALUES + (1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), + (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), + (3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), + (4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), + (5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), + (6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), + (7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), + (8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), + (9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); +# +SELECT _id FROM t1; +DELETE FROM t1 WHERE _id < 8; +--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 # +SHOW TABLE STATUS LIKE 't1'; +CHECK TABLE t1 EXTENDED; +REPAIR TABLE t1 QUICK; +CHECK TABLE t1 EXTENDED; +--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 # +SHOW TABLE STATUS LIKE 't1'; +SELECT _id FROM t1; +DROP TABLE t1; +# +SET @@myisam_repair_threads=1; +SHOW VARIABLES LIKE 'myisam_repair%'; # Bug#8706 - temporary table with data directory option fails # connect (session1,localhost,root,,); diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 4f9790b0de6..683e1402678 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -272,4 +272,13 @@ select a, max(b) from t1 group by a with rollup; select distinct a, max(b) from t1 group by a with rollup; drop table t1; +# +# Bug #20825: rollup puts non-equal values together +# +create table t1 (a varchar(22) not null , b int); +insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10); +select left(a,10), a, sum(b) from t1 group by 1,2 with rollup; +select left(a,10) x, a, sum(b) from t1 group by x,a with rollup; +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test index f086d5b0c2a..35e5e485cb9 100644 --- a/mysql-test/t/repair.test +++ b/mysql-test/t/repair.test @@ -45,4 +45,42 @@ SHOW INDEX FROM t1; SET myisam_repair_threads=@@global.myisam_repair_threads; DROP TABLE t1; +# +# BUG#22562 - REPAIR TABLE .. USE_FRM causes server crash on Windows and +# server hangs on Linux +# +CREATE TABLE t1(a INT); +USE mysql; +REPAIR TABLE test.t1 USE_FRM; +USE test; +DROP TABLE t1; + +# +# BUG#23175 - MYISAM crash/repair failed during repair +# +CREATE TABLE t1(a CHAR(255), KEY(a)); +SET myisam_sort_buffer_size=4096; +INSERT INTO t1 VALUES +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'); +SET myisam_repair_threads=2; +REPAIR TABLE t1; +SET myisam_repair_threads=@@global.myisam_repair_threads; +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index 309855d0b2d..69082840988 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -99,4 +99,50 @@ insert into t2 values (NULL, 'foo'), (NULL, 'bar'); select d, c from t1 left join t2 on b = c where a = 3 order by d; drop table t1, t2; -# End of 4.1 tests + +# +# BUG#21096: locking issue ; temporary table conflicts. +# +# The problem was that on DROP TEMPORARY table name lock was acquired, +# which should not be done. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT); + +LOCK TABLE t1 WRITE; + +connect (conn1, localhost, root,,); + +CREATE TEMPORARY TABLE t1 (i INT); + +--echo The following command should not block +DROP TEMPORARY TABLE t1; + +disconnect conn1; +connection default; + +DROP TABLE t1; + +# +# Check that it's not possible to drop a base table with +# DROP TEMPORARY statement. +# +CREATE TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); + +--error 1051 +DROP TEMPORARY TABLE t2, t1; + +# Table t2 should have been dropped. +--error 1146 +SELECT * FROM t2; +# But table t1 should still be there. +SELECT * FROM t1; + +DROP TABLE t1; + + +--echo End of 4.1 tests. diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index 0d479f312cd..68f5664c36d 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -127,4 +127,13 @@ create table t1 (a set('x','y') default 'x'); alter table t1 alter a set default 'z'; drop table t1; + +# +# Bug#20922 mysql removes a name of first column in a table +# +create table t1 (f1 int); +alter table t1 add f2 enum(0xFFFF); +show create table t1; +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index 75723d2a0ff..8a484f7bcd0 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -179,4 +179,13 @@ show warnings; desc t3; drop table t1,t2,t3; -# End of 4.1 tests +# +# Bug #22129: A small double precision number becomes zero +# +# check if underflows are detected correctly +select 1e-308, 1.00000001e-300, 100000000e-300; + +# check if overflows are detected correctly +select 10e307; + +--echo End of 4.1 tests |