diff options
Diffstat (limited to 'mysql-test/r')
39 files changed, 324 insertions, 131 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 3da828ebf9c..03d0ebf5433 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -493,3 +493,7 @@ select hex(a) from t1; hex(a) F2E5F1F2 drop table t1; +create table t1 ( a timestamp ); +alter table t1 add unique ( a(1) ); +ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys +drop table t1; diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index d4128fb1bcc..83f70a69622 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -36,16 +36,16 @@ create table t2 select * from t1 where 0=1 procedure analyse(); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `Field_name` varchar(255) NOT NULL default '', - `Min_value` varchar(255) default NULL, - `Max_value` varchar(255) default NULL, + `Field_name` varbinary(255) NOT NULL default '', + `Min_value` varbinary(255) default NULL, + `Max_value` varbinary(255) default NULL, `Min_length` bigint(11) NOT NULL default '0', `Max_length` bigint(11) NOT NULL default '0', `Empties_or_zeros` bigint(11) NOT NULL default '0', `Nulls` bigint(11) NOT NULL default '0', - `Avg_value_or_avg_length` varchar(255) NOT NULL default '', - `Std` varchar(255) default NULL, - `Optimal_fieldtype` varchar(64) NOT NULL default '' + `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', + `Std` varbinary(255) default NULL, + `Optimal_fieldtype` varbinary(64) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1 where 0=1 procedure analyse(); Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype @@ -55,16 +55,16 @@ create table t2 select * from t1 where 0=1 procedure analyse(); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `Field_name` varchar(255) NOT NULL default '', - `Min_value` varchar(255) default NULL, - `Max_value` varchar(255) default NULL, + `Field_name` varbinary(255) NOT NULL default '', + `Min_value` varbinary(255) default NULL, + `Max_value` varbinary(255) default NULL, `Min_length` bigint(11) NOT NULL default '0', `Max_length` bigint(11) NOT NULL default '0', `Empties_or_zeros` bigint(11) NOT NULL default '0', `Nulls` bigint(11) NOT NULL default '0', - `Avg_value_or_avg_length` varchar(255) NOT NULL default '', - `Std` varchar(255) default NULL, - `Optimal_fieldtype` varchar(64) NOT NULL default '' + `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', + `Std` varbinary(255) default NULL, + `Optimal_fieldtype` varbinary(64) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t2; Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype @@ -78,16 +78,16 @@ create table t2 select * from t1 where 0=1 procedure analyse(); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `Field_name` varchar(255) NOT NULL default '', - `Min_value` varchar(255) default NULL, - `Max_value` varchar(255) default NULL, + `Field_name` varbinary(255) NOT NULL default '', + `Min_value` varbinary(255) default NULL, + `Max_value` varbinary(255) default NULL, `Min_length` bigint(11) NOT NULL default '0', `Max_length` bigint(11) NOT NULL default '0', `Empties_or_zeros` bigint(11) NOT NULL default '0', `Nulls` bigint(11) NOT NULL default '0', - `Avg_value_or_avg_length` varchar(255) NOT NULL default '', - `Std` varchar(255) default NULL, - `Optimal_fieldtype` varchar(64) NOT NULL default '' + `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', + `Std` varbinary(255) default NULL, + `Optimal_fieldtype` varbinary(64) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t2; Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index 5679b1d6838..1f2d60fb79a 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -98,10 +98,10 @@ Table Create Table t1 CREATE TABLE `t1` ( `c1` varchar(1) character set latin1 collate latin1_danish_ci NOT NULL default '', `c2` varchar(1) character set latin1 collate latin1_danish_ci NOT NULL default '', - `c3` varchar(1) NOT NULL default '', - `c4` varchar(1) NOT NULL default '', - `c5` varchar(3) NOT NULL default '', - `c6` varchar(3) NOT NULL default '', + `c3` varbinary(1) NOT NULL default '', + `c4` varbinary(1) NOT NULL default '', + `c5` varbinary(3) NOT NULL default '', + `c6` varbinary(3) NOT NULL default '', `c7` double(3,1) NOT NULL default '0.0', `c8` double(3,1) NOT NULL default '0.0', `c9` double(3,1) default NULL @@ -149,8 +149,8 @@ t1 CREATE TABLE `t1` ( `COALESCE(1.0)` double(3,1) NOT NULL default '0.0', `COALESCE('a')` varchar(1) NOT NULL default '', `COALESCE(1,1.0)` double(3,1) NOT NULL default '0.0', - `COALESCE(1,'1')` varchar(1) NOT NULL default '', - `COALESCE(1.1,'1')` varchar(3) NOT NULL default '', + `COALESCE(1,'1')` varbinary(1) NOT NULL default '', + `COALESCE(1.1,'1')` varbinary(3) NOT NULL default '', `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) character set latin1 collate latin1_bin NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index f2e91c36f75..05d1ba026ba 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -204,7 +204,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 drop table t1; SET SESSION storage_engine="gemini"; ERROR 42000: Unknown table engine 'gemini' @@ -216,7 +216,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 SET SESSION storage_engine=default; drop table t1; create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); @@ -361,7 +361,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 drop table t1; SET SESSION storage_engine="gemini"; ERROR 42000: Unknown table engine 'gemini' @@ -373,7 +373,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 SET SESSION storage_engine=default; drop table t1; create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob); diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result index c5e57d996fd..2661ff5e3c9 100755 --- a/mysql-test/r/ctype_cp932.result +++ b/mysql-test/r/ctype_cp932.result @@ -11315,3 +11315,23 @@ DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; +SET collation_connection='cp932_japanese_ci'; +create table t1 select repeat('a',4000) a; +delete from t1; +insert into t1 values ('a'), ('a '), ('a\t'); +select collation(a),hex(a) from t1 order by a; +collation(a) hex(a) +cp932_japanese_ci 6109 +cp932_japanese_ci 61 +cp932_japanese_ci 6120 +drop table t1; +SET collation_connection='cp932_bin'; +create table t1 select repeat('a',4000) a; +delete from t1; +insert into t1 values ('a'), ('a '), ('a\t'); +select collation(a),hex(a) from t1 order by a; +collation(a) hex(a) +cp932_bin 6109 +cp932_bin 61 +cp932_bin 6120 +drop table t1; diff --git a/mysql-test/r/ctype_eucjpms.result b/mysql-test/r/ctype_eucjpms.result index f3c22a55a54..a07ff9b28be 100755 --- a/mysql-test/r/ctype_eucjpms.result +++ b/mysql-test/r/ctype_eucjpms.result @@ -9785,3 +9785,23 @@ DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; +SET collation_connection='eucjpms_japanese_ci'; +create table t1 select repeat('a',4000) a; +delete from t1; +insert into t1 values ('a'), ('a '), ('a\t'); +select collation(a),hex(a) from t1 order by a; +collation(a) hex(a) +eucjpms_japanese_ci 6109 +eucjpms_japanese_ci 61 +eucjpms_japanese_ci 6120 +drop table t1; +SET collation_connection='eucjpms_bin'; +create table t1 select repeat('a',4000) a; +delete from t1; +insert into t1 values ('a'), ('a '), ('a\t'); +select collation(a),hex(a) from t1 order by a; +collation(a) hex(a) +eucjpms_bin 6109 +eucjpms_bin 61 +eucjpms_bin 6120 +drop table t1; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 175a02b63f1..a6ca59fc62b 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -528,7 +528,7 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 User var 1 135 @`v`=_ucs2 0x006100620063 COLLATE ucs2_general_ci master-bin.000001 135 Query 1 218 use `test`; insert into t2 values (@v) /*!40019 SET @@session.max_insert_delayed_threads=0*/; -SET @`v`:=_ucs2 0x006100620063 COLLATE ucs2_general_ci; +SET @`v`:=_ucs2 0x006100620063 COLLATE `ucs2_general_ci`; use test; SET TIMESTAMP=10000; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index f1edcdbdef5..faab013559a 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -413,7 +413,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `c` char(10) character set utf8 default NULL, UNIQUE KEY `a` USING HASH (`c`(1)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); insert into t1 values ('aa'); ERROR 23000: Duplicate entry 'aa' for key 1 @@ -449,7 +449,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `c` char(10) character set utf8 default NULL, UNIQUE KEY `a` USING BTREE (`c`(1)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); insert into t1 values ('aa'); ERROR 23000: Duplicate entry 'aa' for key 1 @@ -571,7 +571,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `c` char(10) character set utf8 collate utf8_bin default NULL, UNIQUE KEY `a` USING HASH (`c`(1)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); insert into t1 values ('aa'); ERROR 23000: Duplicate entry 'aa' for key 1 @@ -607,7 +607,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `c` char(10) character set utf8 collate utf8_bin default NULL, UNIQUE KEY `a` USING BTREE (`c`(1)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); insert into t1 values ('aa'); ERROR 23000: Duplicate entry 'aa' for key 1 @@ -849,3 +849,15 @@ utf8_bin 6109 utf8_bin 61 utf8_bin 6120 drop table t1; +CREATE TABLE t1 ( +user varchar(255) NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES ('one'),('two'); +SELECT CHARSET('a'); +CHARSET('a') +utf8 +SELECT user, CONCAT('<', user, '>') AS c FROM t1; +user c +one <one> +two <two> +DROP TABLE t1; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index dfaf5758699..06c1759bf1d 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -457,6 +457,11 @@ group_concat(distinct b order by b) Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() drop table t1; +CREATE TABLE t1 (id int); +SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; +gc +NULL +DROP TABLE t1; create table t1 (a char(3), b char(20), primary key (a, b)); insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English'); select group_concat(a) from t1 group by b; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index fa603fd16d5..f75fe0d1627 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -157,6 +157,14 @@ a bbbb цццц drop table t1; +create table t1 (a char(10) character set latin1 not null); +insert into t1 values ('a'),('b'),('c'); +select a from t1 where a IN ('a','b','c') order by a; +a +a +b +c +drop table t1; set names latin1; select '1.0' in (1,2); '1.0' in (1,2) diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index f80b0281dd8..6556d2be6ad 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -107,13 +107,16 @@ timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") 46:58:57.999999 select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") --23:59:59.999999 +-24:00:00.000001 select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); timediff("1997-12-31 23:59:59.000001","23:59:59.000001") NULL select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001") -00:00:00.000001 +select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50"); +timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50") +-00:00:00.000001 select maketime(10,11,12); maketime(10,11,12) 10:11:12 @@ -185,7 +188,7 @@ f8 date YES NULL f9 time YES NULL select * from t1; f1 f2 f3 f4 f5 f6 f7 f8 f9 -1997-01-01 1998-01-02 01:01:00 49:01:01 46:58:57 -23:59:59 10:11:12 2001-12-01 01:01:01 1997-12-31 23:59:59 +1997-01-01 1998-01-02 01:01:00 49:01:01 46:58:57 -24:00:00 10:11:12 2001-12-01 01:01:01 1997-12-31 23:59:59 create table test(t1 datetime, t2 time, t3 time, t4 datetime); insert into test values ('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'), diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 2399099b478..1f78e5098f8 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -466,7 +466,7 @@ collation(hex(130)) coercibility(hex(130)) latin1_swedish_ci 3 select collation(char(130)), coercibility(hex(130)); collation(char(130)) coercibility(hex(130)) -binary 3 +latin1_swedish_ci 3 select collation(format(130,10)), coercibility(format(130,10)); collation(format(130,10)) coercibility(format(130,10)) latin1_swedish_ci 3 diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index 4d5aabb8b3a..f79bcf3f1dd 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -266,7 +266,7 @@ t1 CREATE TABLE `t1` ( `v` varchar(10) default NULL, `c` char(10) default NULL, `t` varchar(50) default NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 create table t2 like t1; show create table t2; Table Create Table @@ -274,7 +274,7 @@ t2 CREATE TABLE `t2` ( `v` varchar(10) default NULL, `c` char(10) default NULL, `t` varchar(50) default NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 create table t3 select * from t1; show create table t3; Table Create Table @@ -282,7 +282,7 @@ t3 CREATE TABLE `t3` ( `v` varchar(10) default NULL, `c` char(10) default NULL, `t` varchar(50) default NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 alter table t1 modify c varchar(10); show create table t1; Table Create Table @@ -290,7 +290,7 @@ t1 CREATE TABLE `t1` ( `v` varchar(10) default NULL, `c` varchar(10) default NULL, `t` varchar(50) default NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 alter table t1 modify v char(10); show create table t1; Table Create Table @@ -298,7 +298,7 @@ t1 CREATE TABLE `t1` ( `v` char(10) default NULL, `c` varchar(10) default NULL, `t` varchar(50) default NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 alter table t1 modify t varchar(10); Warnings: Warning 1265 Data truncated for column 't' at row 2 @@ -308,7 +308,7 @@ t1 CREATE TABLE `t1` ( `v` char(10) default NULL, `c` varchar(10) default NULL, `t` varchar(10) default NULL -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+*+*+ * @@ -324,7 +324,7 @@ t1 CREATE TABLE `t1` ( KEY `v` (`v`), KEY `c` (`c`), KEY `t` (`t`(10)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 select count(*) from t1; count(*) 270 @@ -559,7 +559,7 @@ t1 CREATE TABLE `t1` ( KEY `v` USING BTREE (`v`), KEY `c` USING BTREE (`c`), KEY `t` USING BTREE (`t`(10)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 select count(*) from t1; count(*) 270 @@ -650,7 +650,7 @@ t1 CREATE TABLE `t1` ( KEY `v` (`v`(5)), KEY `c` (`c`(5)), KEY `t` (`t`(5)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(65530), key(v(10))); show create table t1; @@ -658,7 +658,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(65530) default NULL, KEY `v` (`v`(10)) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 insert into t1 values(repeat('a',65530)); select length(v) from t1 where v=repeat('a',65530); length(v) diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index d1914844839..4c6be698749 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; create table t1 ( key1 int not null, diff --git a/mysql-test/r/index_merge_ror_cpk.result b/mysql-test/r/index_merge_ror_cpk.result index ba8efe42bd6..c344cef5db9 100644 --- a/mysql-test/r/index_merge_ror_cpk.result +++ b/mysql-test/r/index_merge_ror_cpk.result @@ -26,7 +26,7 @@ primary key (pk1, pk2) ) engine=innodb; explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY,key1 PRIMARY 4 const 1 Using where +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index e6793843c93..9bf4ee28b3b 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -478,7 +478,7 @@ character_sets CREATE TEMPORARY TABLE `character_sets` ( `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' -) ENGINE=HEAP DEFAULT CHARSET=utf8 MAX_ROWS=1818 +) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1818 set names latin2; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table @@ -487,7 +487,7 @@ character_sets CREATE TEMPORARY TABLE `character_sets` ( `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' -) ENGINE=HEAP DEFAULT CHARSET=utf8 MAX_ROWS=1818 +) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1818 set names latin1; create table t1 select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like "latin1"; @@ -676,3 +676,6 @@ variable_name character_set_database collation_database skip_show_database +show global status like "Threads_running"; +Variable_name Value +Threads_running 1 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index bdd5ac0bd63..8d1c4e3fc90 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1326,8 +1326,8 @@ truncate table t1; insert into t1 (a) values (NULL),(NULL); SELECT * from t1; a -3 -4 +1 +2 drop table t1; CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB; CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB; @@ -1690,13 +1690,13 @@ Variable_name Value Innodb_page_size 16384 show status like "Innodb_rows_deleted"; Variable_name Value -Innodb_rows_deleted 2078 +Innodb_rows_deleted 2070 show status like "Innodb_rows_inserted"; Variable_name Value Innodb_rows_inserted 31706 show status like "Innodb_rows_read"; Variable_name Value -Innodb_rows_read 80161 +Innodb_rows_read 80153 show status like "Innodb_rows_updated"; Variable_name Value Innodb_rows_updated 29530 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index b7343065c73..d2879e18726 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -784,32 +784,32 @@ t2.flag_value IS NULL; flag_name flag_value flag2 NULL DROP TABLE t1,t2; -CREATE TABLE invoice ( +CREATE TABLE t1 ( id int(11) unsigned NOT NULL auto_increment, text_id int(10) unsigned default NULL, PRIMARY KEY (id) ); -INSERT INTO invoice VALUES("1", "0"); -INSERT INTO invoice VALUES("2", "10"); -CREATE TABLE text_table ( +INSERT INTO t1 VALUES("1", "0"); +INSERT INTO t1 VALUES("2", "10"); +CREATE TABLE t2 ( text_id char(3) NOT NULL default '', language_id char(3) NOT NULL default '', text_data text, PRIMARY KEY (text_id,language_id) ); -INSERT INTO text_table VALUES("0", "EN", "0-EN"); -INSERT INTO text_table VALUES("0", "SV", "0-SV"); -INSERT INTO text_table VALUES("10", "EN", "10-EN"); -INSERT INTO text_table VALUES("10", "SV", "10-SV"); -SELECT invoice.id, invoice.text_id, text_table.text_data -FROM invoice LEFT JOIN text_table -ON invoice.text_id = text_table.text_id -AND text_table.language_id = 'SV' - WHERE (invoice.id LIKE '%' OR text_table.text_data LIKE '%'); +INSERT INTO t2 VALUES("0", "EN", "0-EN"); +INSERT INTO t2 VALUES("0", "SV", "0-SV"); +INSERT INTO t2 VALUES("10", "EN", "10-EN"); +INSERT INTO t2 VALUES("10", "SV", "10-SV"); +SELECT t1.id, t1.text_id, t2.text_data +FROM t1 LEFT JOIN t2 +ON t1.text_id = t2.text_id +AND t2.language_id = 'SV' + WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%'); id text_id text_data 1 0 0-SV 2 10 10-SV -DROP TABLE invoice, text_table; +DROP TABLE t1, t2; CREATE TABLE t0 (a0 int PRIMARY KEY); CREATE TABLE t1 (a1 int PRIMARY KEY); CREATE TABLE t2 (a2 int); diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 307e918a37b..48c09f369b3 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -1,9 +1,9 @@ drop table if exists t1,t2; select 1, 1.0, -1, "hello", NULL; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def 1 8 1 1 N 32769 0 8 -def 1.0 5 3 3 N 32769 1 8 -def -1 8 2 2 N 32769 0 8 +def 1 8 1 1 N 32897 0 63 +def 1.0 5 3 3 N 32897 1 63 +def -1 8 2 2 N 32897 0 63 def hello 253 5 5 N 1 31 8 def NULL 6 0 0 Y 32896 0 63 1 1.0 -1 hello NULL diff --git a/mysql-test/r/ndb_autodiscover.result b/mysql-test/r/ndb_autodiscover.result index 35820512d39..b89b4ce1f21 100644 --- a/mysql-test/r/ndb_autodiscover.result +++ b/mysql-test/r/ndb_autodiscover.result @@ -353,12 +353,17 @@ drop table t1; use test2; drop table t2; drop database test2; -show databases; -Database -information_schema -mysql -test use test; +drop database if exists test_only_ndb_tables; +create database test_only_ndb_tables; +use test_only_ndb_tables; +create table t1 (a int primary key) engine=ndb; +select * from t1; +a +select * from t1; +ERROR HY000: Can't lock file (errno: 4009) +use test; +drop database test_only_ndb_tables; CREATE TABLE t9 ( a int NOT NULL PRIMARY KEY, b int diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 6744ffa889f..5674fbb9177 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -740,3 +740,41 @@ a b 1 2 1 1 drop table t1; +create table t1 ( +`sid` decimal(8,0) default null, +`wnid` varchar(11) not null default '', +key `wnid14` (`wnid`(4)), +key `wnid` (`wnid`) +) engine=myisam default charset=latin1; +insert into t1 (`sid`, `wnid`) values +('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), +('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), +('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), +('37994','01019090000'),('475','02070000000'),('25253','02071100000'), +('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), +('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), +('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), +('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), +('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), +('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), +('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), +('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), +('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), +('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), +('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); +explain select * from t1 where wnid like '0101%' order by wnid; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range wnid14,wnid wnid 13 NULL 10 Using where +select * from t1 where wnid like '0101%' order by wnid; +sid wnid +10100 01019000000 +37986 01019000000 +37989 01019000000 +37987 01019010000 +37990 01019011000 +37991 01019011000 +37992 01019019000 +37993 01019030000 +39560 01019090000 +37994 01019090000 +drop table t1; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 6df5f633b88..c65d11330ea 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -471,7 +471,7 @@ ERROR HY000: This command is not supported in the prepared statement protocol ye prepare stmt1 from ' explain select a from t1 order by b '; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -479,7 +479,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 14 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort @@ -487,7 +487,7 @@ SET @arg00=1 ; prepare stmt1 from ' explain select a from t1 where a > ? order by b '; execute stmt1 using @arg00; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 5 N 1 31 8 @@ -495,7 +495,7 @@ def possible_keys 253 4096 7 Y 0 31 8 def key 253 64 7 Y 0 31 8 def key_len 253 4096 1 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 27 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 621a7ceeab8..50a74dfad63 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -1145,7 +1145,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -1153,7 +1153,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 4fc3ce54810..209a22e4a9e 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -1145,7 +1145,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -1153,7 +1153,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 5025e847f1a..f0be4d119bc 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -1146,7 +1146,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -1154,7 +1154,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 3dff34e374f..a62d00c71c0 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -1188,7 +1188,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -1196,7 +1196,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 @@ -4197,7 +4197,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -4205,7 +4205,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index 6817c0000c5..9a922d75cdc 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -1145,7 +1145,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -1153,7 +1153,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result index 230da5df00d..5047160bf75 100644 --- a/mysql-test/r/ps_7ndb.result +++ b/mysql-test/r/ps_7ndb.result @@ -1145,7 +1145,7 @@ test_sequence prepare stmt1 from ' explain select * from t9 ' ; execute stmt1; Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr -def id 8 3 1 N 32801 0 8 +def id 8 3 1 N 32929 0 63 def select_type 253 19 6 N 1 31 8 def table 253 64 2 N 1 31 8 def type 253 10 3 N 1 31 8 @@ -1153,7 +1153,7 @@ def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 128 31 63 def ref 253 1024 0 Y 0 31 8 -def rows 8 10 1 N 32801 0 8 +def rows 8 10 1 N 32929 0 63 def Extra 253 255 0 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index de74b4e6216..53882e6be69 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -315,57 +315,57 @@ insert into t2 values (1),(2); insert into t3 values (1,1),(2,2); 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 HEAP 9 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL insert into t1 values (3),(4); insert into t2 values (3),(4); insert into t3 values (3,3),(4,4); 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 HEAP 9 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); 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 HEAP 9 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1 where a=3; delete from t2 where b=3; delete from t3 where a=3; 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 HEAP 9 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1; delete from t2; delete from t3; 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 HEAP 9 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); 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 HEAP 9 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1 where a=5; delete from t2 where b=5; delete from t3 where a=5; 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 HEAP 9 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL -t2 HEAP 9 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL -t3 HEAP 9 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL +t1 MEMORY 9 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL +t2 MEMORY 9 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL +t3 MEMORY 9 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL drop table t1, t2, t3; create database mysqltest; show create database mysqltest; @@ -412,7 +412,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` (`i`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; @@ -420,7 +420,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING HASH (`i`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; @@ -428,7 +428,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING BTREE (`i`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; @@ -459,7 +459,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` (`i`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; @@ -474,5 +474,5 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING BTREE (`i`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 +) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index e01355816c3..5492a7a65fc 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -18,7 +18,7 @@ t1 CREATE TABLE `t1` ( `email` varchar(60) character set latin2 NOT NULL default '', PRIMARY KEY (`a`), UNIQUE KEY `email` USING BTREE (`email`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC set @@sql_mode="ansi_quotes"; show variables like 'sql_mode'; Variable_name Value @@ -31,7 +31,7 @@ t1 CREATE TABLE "t1" ( "email" varchar(60) character set latin2 NOT NULL default '', PRIMARY KEY ("a"), UNIQUE KEY "email" USING BTREE ("email") -) ENGINE=HEAP DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC set @@sql_mode="no_table_options"; show variables like 'sql_mode'; Variable_name Value @@ -57,7 +57,7 @@ t1 CREATE TABLE `t1` ( `email` varchar(60) character set latin2 NOT NULL default '', PRIMARY KEY (`a`), UNIQUE KEY `email` (`email`) -) ENGINE=HEAP DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC set @@sql_mode="no_field_options,mysql323,mysql40"; show variables like 'sql_mode'; Variable_name Value diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index fb228d37da3..f28317ce947 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -768,7 +768,7 @@ INSERT INTO t1 VALUES (-2.2E-307,0),(+1.7E+308,+1.7E+308); INSERT INTO t1 VALUES ('-2.2E-307',0),('+1.7E+308','+1.7E+308'); INSERT INTO t1 (col1) VALUES (-2.2E-330); INSERT INTO t1 (col1) VALUES (+1.7E+309); -ERROR 22007: Illegal double '1.7E+309' value found during parsing +Got one of the listed errors INSERT INTO t1 (col2) VALUES (-1.1E-3); ERROR 22003: Out of range value adjusted for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES ('+1.8E+309'); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1a1a9a256db..5fd2053a3ec 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2160,3 +2160,11 @@ WHERE f1 <> ALL ( SELECT SUM(f1) AS sf1 FROM t2 HAVING sf1 > 10000); f1 NULL 1 +drop table t1,t2; +create table t1 (a1 int); +create table t2 (b1 int); +select * from t1 where a2 > any(select b1 from t2); +ERROR 42S22: Unknown column 'a2' in 'scalar IN/ALL/ANY subquery' +select * from t1 where a1 > any(select b1 from t2); +a1 +drop table t1,t2; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 53d0f63aea2..8aa20a88177 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -45,6 +45,15 @@ alter table t9 rename t8, add column d int not null; alter table t8 rename t7; rename table t7 to t9; drop table t1; +SHOW CREATE TABLE t9; +Table Create Table +t9 CREATE TABLE `t9` ( + `a` int(11) NOT NULL auto_increment, + `b` char(16) NOT NULL, + `c` int(11) NOT NULL, + `d` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='TEST_DIR/var/tmp/' INDEX DIRECTORY='TEST_DIR/var/run/' Got one of the listed errors Got one of the listed errors Got one of the listed errors diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 45f887461e7..88bb04fefba 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -368,3 +368,14 @@ a+0 44 57 drop table t1; +create table t1 (a bit(3), b bit(12)); +insert into t1 values (7,(1<<12)-2), (0x01,0x01ff); +select hex(a),hex(b) from t1; +hex(a) hex(b) +7 FFE +1 1FF +select hex(concat(a)),hex(concat(b)) from t1; +hex(concat(a)) hex(concat(b)) +07 0FFE +01 01FF +drop table t1; diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 449d9b0ea4b..c77b3ad7578 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; SELECT 10,10.0,10.,.1e+2,100.0e-1; 10 10.0 10. .1e+2 100.0e-1 10 10.0 10 10 10 @@ -8,6 +8,9 @@ SELECT 6e-05, -6e-05, --6e-05, -6e-05+1.000000; SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; 1e1 1.e1 1.0e1 1e+1 1.e+1 1.0e+1 1e-1 1.e-1 1.0e-1 10 10 10 10 10 10 0.1 0.1 0.1 +SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01; +0.001e+1 0.001e-1 -0.001e+01 -0.001e-01 +0.01 0.0001 -0.01 -0.0001 create table t1 (f1 float(24),f2 float(52)); show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment @@ -143,6 +146,15 @@ drop table t1; create table t1 (f float(54)); ERROR 42000: Incorrect column specifier for column 'f' drop table if exists t1; +create table t1 (d1 double, d2 double unsigned); +insert into t1 set d1 = -1.0; +update t1 set d2 = d1; +Warnings: +Warning 1264 Out of range value adjusted for column 'd2' at row 1 +select * from t1; +d1 d2 +-1 0 +drop table t1; create table t1 (f float(4,3)); insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11"); Warnings: diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 61c167c6264..1e0b5deaf25 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -426,3 +426,13 @@ max(t) 2004-01-01 01:00:00 2004-02-01 00:00:00 drop table t1; +set sql_mode='maxdb'; +create table t1 (a timestamp, b timestamp(19)); +show create table t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" datetime default NULL, + "b" datetime default NULL +) +set sql_mode=''; +drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 334a2531c7f..a63358ff2bc 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -554,7 +554,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(20) NOT NULL default '' + `a` varbinary(20) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -872,6 +872,22 @@ count(*) show status like 'Slow_queries'; Variable_name Value Slow_queries 7 +flush status; +select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6); +a +4 +5 +3 +6 +7 +8 +9 +10 +1 +2 +show status like 'Slow_queries'; +Variable_name Value +Slow_queries 1 drop table t1; create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM; insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777'); diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index d7d527dd720..69fcc5c45fe 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -172,23 +172,26 @@ SET TIMESTAMP=10000; SET @`a b`='hello'; INSERT INTO t1 VALUES(@`a b`); set @var1= "';aaa"; -insert into t1 values (@var1); +SET @var2=char(ascii('a')); +insert into t1 values (@var1),(@var2); show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 User var 1 136 @`a b`=_latin1 0x68656C6C6F COLLATE latin1_swedish_ci master-bin.000001 136 Query 1 222 use `test`; INSERT INTO t1 VALUES(@`a b`) master-bin.000001 222 User var 1 264 @`var1`=_latin1 0x273B616161 COLLATE latin1_swedish_ci -master-bin.000001 264 Query 1 350 use `test`; insert into t1 values (@var1) +master-bin.000001 264 User var 1 302 @`var2`=_latin1 0x61 COLLATE latin1_swedish_ci +master-bin.000001 302 Query 1 396 use `test`; insert into t1 values (@var1),(@var2) /*!40019 SET @@session.max_insert_delayed_threads=0*/; -SET @`a b`:=_latin1 0x68656C6C6F COLLATE latin1_swedish_ci; +SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`; use test; SET TIMESTAMP=10000; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; SET @@session.sql_mode=0; INSERT INTO t1 VALUES(@`a b`); -SET @`var1`:=_latin1 0x273B616161 COLLATE latin1_swedish_ci; +SET @`var1`:=_latin1 0x273B616161 COLLATE `latin1_swedish_ci`; +SET @`var2`:=_latin1 0x61 COLLATE `latin1_swedish_ci`; SET TIMESTAMP=10000; -insert into t1 values (@var1); +insert into t1 values (@var1),(@var2); drop table t1; set @var= NULL ; select FIELD( @var,'1it','Hit') as my_column; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c4391781e9c..239849ed8d1 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1724,3 +1724,9 @@ a b 301 0 drop view v3; drop tables t1,t2; +create table t1(f1 int); +create view v1 as select f1 from t1; +select * from v1 where F1 = 1; +f1 +drop view v1; +drop table t1; |