diff options
Diffstat (limited to 'mysql-test/r')
35 files changed, 661 insertions, 143 deletions
diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result index 7f606367f39..587c21e9129 100644 --- a/mysql-test/r/alias.result +++ b/mysql-test/r/alias.result @@ -63,7 +63,7 @@ CREATE TABLE t1 ( AUFNR varchar(12) NOT NULL default '', PLNFL varchar(6) NOT NULL default '', VORNR varchar(4) NOT NULL default '', -xstatus_vor smallint(5) unsigned NOT NULL default '0', +xstatus_vor smallint(5) unsigned NOT NULL default '0' ); INSERT INTO t1 VALUES ('40004712','000001','0010',9); INSERT INTO t1 VALUES ('40004712','000001','0020',0); diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index f18b925460c..b51afab5b54 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -31,3 +31,68 @@ EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +create table t1 (a int not null); +create table t2 select * from t1 where 0=1 procedure analyse(); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `Field_name` char(255) NOT NULL default '', + `Min_value` char(255) default NULL, + `Max_value` char(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` char(255) NOT NULL default '', + `Std` char(255) default NULL, + `Optimal_fieldtype` char(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 +insert into t1 values(1); +drop table t2; +create table t2 select * from t1 where 0=1 procedure analyse(); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `Field_name` char(255) NOT NULL default '', + `Min_value` char(255) default NULL, + `Max_value` char(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` char(255) NOT NULL default '', + `Std` char(255) default NULL, + `Optimal_fieldtype` char(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 +insert into t2 select * from t1 procedure analyse(); +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 +test.t1.a 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL +insert into t1 values(2); +drop table t2; +create table t2 select * from t1 where 0=1 procedure analyse(); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `Field_name` char(255) NOT NULL default '', + `Min_value` char(255) default NULL, + `Max_value` char(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` char(255) NOT NULL default '', + `Std` char(255) default NULL, + `Optimal_fieldtype` char(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 +insert into t2 select * from t1 procedure analyse(); +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 +test.t1.a 1 2 1 1 0 0 1.5000 0.5000 ENUM('1','2') NOT NULL +drop table t1,t2; diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index bee40eac30d..a544bbbf0b7 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -203,7 +203,7 @@ a 2 check table t1; Table Op Msg_type Msg_text -test.t1 check error The storage engine for the table doesn't support check +test.t1 check note The storage engine for the table doesn't support check drop table t1; create table t1 (a int,b varchar(20)) engine=bdb; insert into t1 values (1,""), (2,"testing"); diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index ce72c353d20..b5f7da30bb3 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -274,6 +274,12 @@ create database `db1 `; ERROR 42000: Incorrect database name 'db1 ' create table t1(`a ` int); ERROR 42000: Incorrect column name 'a ' +create table t1 (a int,); +ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 +create table t1 (a int,,b int); +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 'b int)' at line 1 +create table t1 (,b int); +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 'b int)' at line 1 create table t1 (a int, key(a)); create table t2 (b int, foreign key(b) references t1(a), key(b)); drop table if exists t1,t2; diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result index b66ede3bed4..cc062094535 100644 --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@ -92,3 +92,22 @@ select @ujis3 = CONVERT(@utf83 USING ujis); select @ujis4 = CONVERT(@utf84 USING ujis); @ujis4 = CONVERT(@utf84 USING ujis) 1 +drop table if exists t1; +Warnings: +Note 1051 Unknown table 't1' +create table t1 (c1 varchar(8)) default character set 'ujis'; +insert into t1 values (0xA4A2),(0xA2A2),(0xA4A2); +select c1 as 'no index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis); +no index +¤¢ +¤¢ +create index idx_c1 on t1(c1); +select c1 as 'using index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis); +using index +¤¢ +¤¢ +select c1 as 'no index' from t1 where c1 like cast(concat('%',0xA4A2, '%') as char character set ujis); +no index +¤¢ +¤¢ +drop table t1; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index f5754bb3332..81e2cd03d1a 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -251,7 +251,7 @@ id select_type table type possible_keys key key_len ref rows Extra drop table t1; CREATE TABLE `t1` ( `N` int(11) unsigned NOT NULL default '0', -`M` tinyint(1) default '0', +`M` tinyint(1) default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0); UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 147d7776e4d..57b109e1ee6 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -221,6 +221,68 @@ select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; max(t1.a2) max(t2.a1) zzz BBB drop table t1,t2; +CREATE TABLE t1 (a int, b int); +select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; +count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +0 NULL NULL NULL NULL NULL 18446744073709551615 0 +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +insert into t1 values (1,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +insert into t1 values (1,null); +insert into t1 values (2,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +insert into t1 values (2,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 +insert into t1 values (3,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 +3 1 1 1.0000 0.0000 1 1 1 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) bit_xor(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 0 +2 1 1 1.0000 0.0000 1 1 1 1 1 +3 1 1 1.0000 0.0000 1 1 1 1 1 +explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort +Warnings: +Note 1003 select high_priority big_result test.t1.a AS `a`,count(test.t1.b) AS `count(b)`,sum(test.t1.b) AS `sum(b)`,avg(test.t1.b) AS `avg(b)`,std(test.t1.b) AS `std(b)`,min(test.t1.b) AS `min(b)`,max(test.t1.b) AS `max(b)`,bit_and(test.t1.b) AS `bit_and(b)`,bit_or(test.t1.b) AS `bit_or(b)`,bit_xor(test.t1.b) AS `bit_xor(b)` from test.t1 group by test.t1.a +drop table t1; +create table t1 (col int); +insert into t1 values (-1), (-2), (-3); +select bit_and(col), bit_or(col) from t1; +bit_and(col) bit_or(col) +18446744073709551612 18446744073709551615 +select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; +bit_and(col) bit_or(col) +18446744073709551613 18446744073709551613 +18446744073709551614 18446744073709551614 +18446744073709551615 18446744073709551615 +drop table t1; +create table t1 (a int); +select avg(2) from t1; +avg(2) +NULL +drop table t1; create table t1( a1 char(3) primary key, a2 smallint, @@ -562,52 +624,6 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index 1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index drop table t1, t2; -CREATE TABLE t1 (a int, b int); -select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; -count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -0 NULL NULL NULL NULL NULL 18446744073709551615 0 -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -insert into t1 values (1,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -insert into t1 values (1,null); -insert into t1 values (2,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -2 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -2 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -insert into t1 values (2,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -2 1 1 1.0000 0.0000 1 1 1 1 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -2 1 1 1.0000 0.0000 1 1 1 1 -insert into t1 values (3,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -2 1 1 1.0000 0.0000 1 1 1 1 -3 1 1 1.0000 0.0000 1 1 1 1 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) bit_xor(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 0 -2 1 1 1.0000 0.0000 1 1 1 1 1 -3 1 1 1.0000 0.0000 1 1 1 1 1 -explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort -Warnings: -Note 1003 select high_priority big_result test.t1.a AS `a`,count(test.t1.b) AS `count(b)`,sum(test.t1.b) AS `sum(b)`,avg(test.t1.b) AS `avg(b)`,std(test.t1.b) AS `std(b)`,min(test.t1.b) AS `min(b)`,max(test.t1.b) AS `max(b)`,bit_and(test.t1.b) AS `bit_and(b)`,bit_or(test.t1.b) AS `bit_or(b)`,bit_xor(test.t1.b) AS `bit_xor(b)` from test.t1 group by test.t1.a -drop table t1; create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; insert into t1 values (1, 3); select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; @@ -632,14 +648,11 @@ select a from t1 having a=1; a 1 drop table t1; -create table t1 (col int); -insert into t1 values (-1), (-2), (-3); -select bit_and(col), bit_or(col) from t1; -bit_and(col) bit_or(col) -18446744073709551612 18446744073709551615 -select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; -bit_and(col) bit_or(col) -18446744073709551613 18446744073709551613 -18446744073709551614 18446744073709551614 -18446744073709551615 18446744073709551615 +create table t1 (a int); +select variance(2) from t1; +variance(2) +NULL +select stddev(2) from t1; +stddev(2) +NULL drop table t1; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index d3225679b3e..216e2ed26f2 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -84,9 +84,9 @@ bbbb bb bbbbbbbb aaaa bbbb select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') this is a REAL test -select soundex(''),soundex('he'),soundex('hello all folks'); -soundex('') soundex('he') soundex('hello all folks') - H000 H4142 +select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); +soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') + H000 H4142 I51231 select 'mood' sounds like 'mud'; 'mood' sounds like 'mud' 1 @@ -281,7 +281,7 @@ CREATE TABLE t1 ( wid int(10) unsigned NOT NULL auto_increment, data_podp date default NULL, status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', -PRIMARY KEY(wid), +PRIMARY KEY(wid) ); INSERT INTO t1 VALUES (8,NULL,'real'); INSERT INTO t1 VALUES (9,NULL,'nowy'); @@ -639,3 +639,21 @@ name aaaaaaaaaaccccc bbbbbbbbbbddddd drop table t1, t2; +create table t1 (c1 INT, c2 INT UNSIGNED); +insert into t1 values ('21474836461','21474836461'); +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +Warning 1265 Data truncated for column 'c2' at row 1 +insert into t1 values ('-21474836461','-21474836461'); +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +Warning 1265 Data truncated for column 'c2' at row 1 +show warnings; +Level Code Message +Warning 1265 Data truncated for column 'c1' at row 1 +Warning 1265 Data truncated for column 'c2' at row 1 +select * from t1; +c1 c2 +2147483647 4294967295 +-2147483648 0 +drop table t1; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b3726d58c96..6a705f8b5cf 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -352,6 +352,36 @@ extract(SECOND FROM "1999-01-02 10:11:12") select extract(MONTH FROM "2001-02-00"); extract(MONTH FROM "2001-02-00") 2 +SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; +"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND +1968-01-20 03:14:08 +SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; +"1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND +1968-01-20 03:15:07 +SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND; +"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND +8895-03-27 22:11:40 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; +"1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND +6255-04-08 15:04:32 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; +"1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE +5983-01-24 02:08:00 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; +"1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR +2019-08-15 16:00:00 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; +"1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND +NULL +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; +"1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE +NULL +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; +"1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR +NULL +SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; +"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND +NULL create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index ab5338d383b..b66ef6d6a31 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -750,3 +750,10 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK drop table t1; +CREATE TABLE t1 ( +fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +g GEOMETRY NOT NULL, +SPATIAL KEY(g) +) ENGINE=MyISAM; +INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('LineString(1 2, 2 4)')); +drop table t1; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 2c17373bd00..8b3948e093f 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -76,6 +76,8 @@ delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; +show grants for mysqltest_1@localhost; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host 'localhost' create table t1 (a int); GRANT select,update,insert on t1 to mysqltest_1@localhost; GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; diff --git a/mysql-test/r/have_ndb.require b/mysql-test/r/have_ndb.require new file mode 100644 index 00000000000..f0402b72c6a --- /dev/null +++ b/mysql-test/r/have_ndb.require @@ -0,0 +1,2 @@ +Variable_name Value +have_ndbcluster YES diff --git a/mysql-test/r/innodb-lock.result b/mysql-test/r/innodb-lock.result new file mode 100644 index 00000000000..cf00adb30ae --- /dev/null +++ b/mysql-test/r/innodb-lock.result @@ -0,0 +1,22 @@ +drop table if exists t1; +create table t1 (id integer, x integer) engine=INNODB; +insert into t1 values(0, 0); +set autocommit=0; +SELECT * from t1 where id = 0 FOR UPDATE; +id x +0 0 +set autocommit=0; +lock table t1 write; +update t1 set x=1 where id = 0; +select * from t1; +id x +0 1 +commit; +update t1 set x=2 where id = 0; +commit; +unlock tables; +select * from t1; +id x +0 2 +commit; +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 5e513061821..3c36571a8c2 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -467,7 +467,7 @@ ERROR 23000: Duplicate entry 'test2' for key 2 select * from t1; id ggid email passwd 1 this will work -3 test2 this will work +4 test2 this will work select * from t1 where id=1; id ggid email passwd 1 this will work diff --git a/mysql-test/r/isam.result b/mysql-test/r/isam.result index 4c698075150..2c7b3a4a568 100644 --- a/mysql-test/r/isam.result +++ b/mysql-test/r/isam.result @@ -49,14 +49,14 @@ test.t1 optimize status OK check table t1,t2; Table Op Msg_type Msg_text test.t1 check status OK -test.t2 check error The storage engine for the table doesn't support check +test.t2 check note The storage engine for the table doesn't support check repair table t1,t2; Table Op Msg_type Msg_text test.t1 repair status OK -test.t2 repair error The storage engine for the table doesn't support repair +test.t2 repair note The storage engine for the table doesn't support repair check table t2,t1; Table Op Msg_type Msg_text -test.t2 check error The storage engine for the table doesn't support check +test.t2 check note The storage engine for the table doesn't support check test.t1 check status OK lock tables t1 write; check table t2,t1; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 115f15bacb6..28824de94ce 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -206,3 +206,32 @@ select i from t1 where b=repeat(_utf8 'b',310); i 1 drop table t1; +CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam; +insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); +explain select 1 from t1 where id =2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +explain select 1 from t1 where id =2 or id=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +explain select name from t1 where id =2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id); +explain select 1 from t1 where id =2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref id id 4 const 1 Using where; Using index +drop table t1; +CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); +INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); +SELECT numeropost FROM t1 WHERE numreponse='1'; +numeropost +1 +EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const numreponse numreponse 4 const 1 Using index +FLUSH TABLES; +SELECT numeropost FROM t1 WHERE numreponse='1'; +numeropost +1 +drop table t1; diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index b83a226776d..1580f51975e 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -85,6 +85,12 @@ select @@key_cache_block_size; set global keycache1.key_buffer_size=1024*1024; create table t1 (p int primary key, a char(10)) delay_key_write=1; create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); +show status like 'key_blocks_used'; +Variable_name Value +Key_blocks_used 0 +show status like 'key_blocks_unused'; +Variable_name Value +Key_blocks_unused KEY_BLOCKS_UNUSED insert into t1 values (1, 'qqqq'), (11, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), (3, 1, 'yyyy'), (4, 3, 'zzzz'); @@ -100,6 +106,12 @@ p i a 4 3 zzzz update t1 set p=2 where p=1; update t2 set i=2 where i=1; +show status like 'key_blocks_used'; +Variable_name Value +Key_blocks_used 4 +show status like 'key_blocks_unused'; +Variable_name Value +Key_blocks_unused KEY_BLOCKS_UNUSED cache index t1 key (`primary`) in keycache1; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK @@ -256,6 +268,12 @@ Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK test.t2 assign_to_keycache status OK drop table t1,t2,t3; +show status like 'key_blocks_used'; +Variable_name Value +Key_blocks_used 4 +show status like 'key_blocks_unused'; +Variable_name Value +Key_blocks_unused KEY_BLOCKS_UNUSED set global keycache2.key_buffer_size=0; set global keycache3.key_buffer_size=100; set global keycache3.key_buffer_size=0; diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result index 14ca90b3dd2..7726a8e1d63 100644 --- a/mysql-test/r/key_primary.result +++ b/mysql-test/r/key_primary.result @@ -13,7 +13,7 @@ t1 AB% describe select * from t1 where t1="ABC"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const PRIMARY PRIMARY 3 const 1 +1 SIMPLE t1 const PRIMARY PRIMARY 3 const 1 Using index describe select * from t1 where t1="ABCD"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result index c29d52ffffc..d43a18c6a96 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -68,7 +68,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) default NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin +) ENGINE=InnoDB DEFAULT CHARSET=latin1 RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 10d07e51565..4668f1262f9 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3; +drop database if exists mysqltest; create table t1(id1 int not null auto_increment primary key, t char(12)); create table t2(id2 int not null, t char(12)); create table t3(id3 int not null, t char(12), index(id3)); @@ -323,6 +324,7 @@ a b 7 7 8 8 9 9 +update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10); drop table t1,t2; CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); @@ -402,7 +404,7 @@ DELETE t1 FROM t1, t2 AS t3; DELETE t4 FROM t1, t1 AS t4; DELETE t3 FROM t1 AS t3, t1 AS t4; DELETE t1 FROM t1 AS t3, t2 AS t4; -ERROR 42000: Not unique table/alias: 't1' +ERROR 42S02: Unknown table 't1' in MULTI DELETE INSERT INTO t1 values (1),(2); INSERT INTO t2 values (1),(2); DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1; @@ -435,6 +437,23 @@ select * from t2; c2_id c2_p_id c2_note c2_active 1 1 A Note 1 drop table t1, t2; +create database mysqltest; +create table mysqltest.t1 (a int, b int, primary key (a)); +create table mysqltest.t2 (a int, b int, primary key (a)); +create table mysqltest.t3 (a int, b int, primary key (a)); +grant select on mysqltest.* to mysqltest_1@localhost; +grant update on mysqltest.t1 to mysqltest_1@localhost; +update t1, t2 set t1.b=1 where t1.a=t2.a; +update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; +create table t1 (a int, primary key (a)); +create table t2 (a int, primary key (a)); +create table t3 (a int, primary key (a)); +delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a); +ERROR 42S02: Unknown table 't3' in MULTI DELETE +drop table t1, t2, t3; set @ttype_save=@@storage_engine; set @@storage_engine=innodb; create table t1 ( c char(8) not null ); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index d210048a5c7..9a123729c4b 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -504,3 +504,28 @@ test.t1 968604391 test.t2 968604391 test.t3 NULL drop table t1,t2; +create table t1 (a int, key (a)); +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +alter table t1 disable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +create table t2 (a int); +insert t1 select * from t2; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table t1 enable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A 1000 NULL NULL YES BTREE +alter table t1 engine=heap; +alter table t1 disable keys; +Warnings: +Note 1031 Table storage engine for 't1' doesn't have this option +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a NULL NULL NULL NULL YES HASH +drop table t1,t2; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index b0a15d9a021..01114104088 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -21,6 +21,38 @@ DROP TABLE t1; CREATE TABLE t1 (a decimal(240, 20)); INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), ("0987654321098765432109876543210987654321"); +CREATE TABLE `t1` ( + `a` decimal(240,20) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES ('1234567890123456789012345678901234567890.00000000000000000000'),('0987654321098765432109876543210987654321.00000000000000000000'); +DROP TABLE t1; +CREATE TABLE t1 (a double); +INSERT INTO t1 VALUES (-9e999999); +CREATE TABLE `t1` ( + `a` double default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (RES); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT); +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +INSERT INTO t1 VALUES ("1.2345", 2.3456); +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES'; +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +INSERT INTO t1 VALUES ("1.2345", 2.3456); +ERROR 42S22: Unknown column '1.2345' in 'field list' +SET SQL_MODE=@OLD_SQL_MODE; +CREATE TABLE `t1` ( + `a` decimal(10,5) default NULL, + `b` float default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES ('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456); +CREATE TABLE `t1` ( + `a` decimal(10,5) default NULL, + `b` float default NULL +); +INSERT INTO `t1` VALUES ('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -28,13 +60,14 @@ INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( - `a` decimal(240,20) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; + `a` decimal(10,5) default NULL, + `b` float default NULL +); /*!40000 ALTER TABLE `t1` DISABLE KEYS */; LOCK TABLES `t1` WRITE; -INSERT INTO `t1` VALUES ("1234567890123456789012345678901234567890.00000000000000000000"),("0987654321098765432109876543210987654321.00000000000000000000"); +INSERT INTO `t1` VALUES ('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456); UNLOCK TABLES; /*!40000 ALTER TABLE `t1` ENABLE KEYS */; @@ -43,30 +76,19 @@ UNLOCK TABLES; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; -DROP TABLE t1; -CREATE TABLE t1 (a double); -INSERT INTO t1 VALUES (-9e999999); - -/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; -DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( - `a` double default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - + `a` decimal(10,5) default NULL, + `b` float default NULL +); -/*!40000 ALTER TABLE `t1` DISABLE KEYS */; -LOCK TABLES `t1` WRITE; -INSERT INTO `t1` VALUES (RES); -UNLOCK TABLES; -/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +INSERT INTO `t1` VALUES ('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); @@ -180,27 +202,9 @@ UNLOCK TABLES; DROP TABLE t1; create table ```a` (i int); - -/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; -DROP TABLE IF EXISTS ```a`; CREATE TABLE ```a` ( `i` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; - - -/*!40000 ALTER TABLE ```a` DISABLE KEYS */; -LOCK TABLES ```a` WRITE; -UNLOCK TABLES; -/*!40000 ALTER TABLE ```a` ENABLE KEYS */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; - drop table ```a`; create table t1(a int); diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result new file mode 100644 index 00000000000..ac550937146 --- /dev/null +++ b/mysql-test/r/ndb_basic.result @@ -0,0 +1,82 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL +) ENGINE=ndbcluster; +INSERT INTO t1 VALUES (9410,9412); +SELECT pk1 FROM t1; +pk1 +9410 +SELECT * FROM t1; +pk1 attr1 +9410 9412 +SELECT t1.* FROM t1; +pk1 attr1 +9410 9412 +UPDATE t1 SET attr1=1 WHERE pk1=9410; +SELECT * FROM t1; +pk1 attr1 +9410 1 +UPDATE t1 SET pk1=2 WHERE attr1=1; +ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version +SELECT * FROM t1; +pk1 attr1 +9410 1 +DELETE FROM t1; +SELECT * FROM t1; +pk1 attr1 +INSERT INTO t1 VALUES (9410,9412); +DELETE FROM t1 WHERE pk1 = 9410; +SELECT * FROM t1; +pk1 attr1 +INSERT INTO t1 VALUES (9410,9412), (9411, 9413), (9408, 8765); +DELETE FROM t1; +SELECT * FROM t1; +pk1 attr1 +INSERT INTO t1 values (1, 4), (2, 4), (3, 5), (4, 4), (5, 5); +DELETE FROM t1 WHERE attr1=4; +SELECT * FROM t1 order by pk1; +pk1 attr1 +3 5 +5 5 +DELETE FROM t1; +INSERT INTO t1 VALUES (9410,9412), (9411, 9413); +DELETE FROM t1 WHERE pk1 = 9410; +SELECT * FROM t1; +pk1 attr1 +9411 9413 +DROP TABLE t1; +CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster; +INSERT INTO t1 values(3456, 7890); +SELECT * FROM t1; +id id2 +3456 7890 +UPDATE t1 SET id=2 WHERE id2=12; +SELECT * FROM t1; +id id2 +3456 7890 +UPDATE t1 SET id=1234 WHERE id2=7890; +SELECT * FROM t1; +id id2 +1234 7890 +DELETE FROM t1; +INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890); +SELECT * FROM t1; +id id2 +3456 7890 +3456 7890 +3456 7890 +DELETE FROM t1 WHERE id = 3456; +DROP TABLE t1; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL +) ENGINE=NDBCLUSTER; +INSERT INTO t1 values(1, 9999); +DROP TABLE t1; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL +) ENGINE=NDB; +INSERT INTO t1 values(1, 9999); +DROP TABLE t1; diff --git a/mysql-test/r/ndb_cache.result b/mysql-test/r/ndb_cache.result new file mode 100644 index 00000000000..714e1831267 --- /dev/null +++ b/mysql-test/r/ndb_cache.result @@ -0,0 +1,43 @@ +set GLOBAL query_cache_size=1355776; +reset query cache; +flush status; +drop table if exists t1,t2; +CREATE TABLE t1 (a int) ENGINE=ndbcluster; +CREATE TABLE t2 (a int); +select * from t1; +a +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +select * from t2; +a +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +select * from t1; +a +select * from t2; +a +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +drop table t1, t2; +SET GLOBAL query_cache_size=0; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 4dfd17991cc..e57ac321f46 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -12,7 +12,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index explain select * from t1 where a=2 and b = 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const a,b a 9 const,const 1 +1 SIMPLE t1 const a,b a 9 const,const 1 Using index explain select * from t1 where a<=>b limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index diff --git a/mysql-test/r/raid.result b/mysql-test/r/raid.result index 690ef24d809..459da1cdf49 100644 --- a/mysql-test/r/raid.result +++ b/mysql-test/r/raid.result @@ -4,6 +4,14 @@ create database test_$1; create table test_$1.r1 (i int) raid_type=1; create table test_$1.r2 (i int) raid_type=1 raid_chunks=32; drop database test_$1; +create database test_$1; +create table test_$1.r2 (i int) raid_type=1 raid_chunks=257; +show create table test_$1.r2; +Table Create Table +r2 CREATE TABLE `r2` ( + `i` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 RAID_TYPE=striped RAID_CHUNKS=255 RAID_CHUNKSIZE=256 +drop database test_$1; CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, c char(255) not null diff --git a/mysql-test/r/repair_part1.result b/mysql-test/r/repair.result index 6dcec409ea6..dbca5c39a6c 100644 --- a/mysql-test/r/repair_part1.result +++ b/mysql-test/r/repair.result @@ -7,7 +7,7 @@ test.t1 repair status OK alter table t1 ENGINE=HEAP; repair table t1 use_frm; Table Op Msg_type Msg_text -test.t1 repair error The storage engine for the table doesn't support repair +test.t1 repair note The storage engine for the table doesn't support repair drop table t1; create table t1(id int PRIMARY KEY, st varchar(10), KEY st_key(st)); insert into t1 values(1, "One"); @@ -28,3 +28,12 @@ repair table t1 use_frm; Table Op Msg_type Msg_text test.t1 repair error Table 'test.t1' doesn't exist create table t1 engine=myisam SELECT 1,"table 1"; +flush tables; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair error Can't open file: 't1.MYI' (errno: 130) +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair warning Number of rows changed from 0 to 1 +test.t1 repair status OK +drop table t1; diff --git a/mysql-test/r/repair_part2.result b/mysql-test/r/repair_part2.result deleted file mode 100644 index c9cf6c019f7..00000000000 --- a/mysql-test/r/repair_part2.result +++ /dev/null @@ -1,8 +0,0 @@ -repair table t1; -Table Op Msg_type Msg_text -test.t1 repair error Can't open file: 't1.MYI' (errno: 130) -repair table t1 use_frm; -Table Op Msg_type Msg_text -test.t1 repair warning Number of rows changed from 0 to 1 -test.t1 repair status OK -drop table t1; diff --git a/mysql-test/r/rpl_multi_delete2.result b/mysql-test/r/rpl_multi_delete2.result new file mode 100644 index 00000000000..c6c088111fc --- /dev/null +++ b/mysql-test/r/rpl_multi_delete2.result @@ -0,0 +1,21 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (a int); +create table t2 (a int); +insert into t1 values (1); +insert into t2 values (1); +delete t1.* from t1, t2 where t1.a = t2.a; +select * from t1; +a +select * from t2; +a +1 +select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +select * from t2; +ERROR 42S02: Table 'test.t2' doesn't exist +drop table t1,t2; diff --git a/mysql-test/r/rpl_multi_update.result b/mysql-test/r/rpl_multi_update.result index 65587d25f63..34f99746c7d 100644 --- a/mysql-test/r/rpl_multi_update.result +++ b/mysql-test/r/rpl_multi_update.result @@ -6,7 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, -b int unsigned, +b int unsigned ) ENGINE=MyISAM; CREATE TABLE t2 ( a int unsigned not null auto_increment primary key, diff --git a/mysql-test/r/rpl_server_id1.result b/mysql-test/r/rpl_server_id1.result new file mode 100644 index 00000000000..8c383802de4 --- /dev/null +++ b/mysql-test/r/rpl_server_id1.result @@ -0,0 +1,22 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (n int); +reset master; +stop slave; +change master to master_port=SLAVE_PORT; +show slave status; +Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master + 127.0.0.1 root SLAVE_PORT 1 4 slave-relay-bin.000001 4 No No # 0 0 0 4 None 0 No NULL +start slave; +insert into t1 values (1); +show status like "slave_running"; +Variable_name Value +Slave_running ON +select * from t1; +n +1 +drop table t1; diff --git a/mysql-test/r/rpl_server_id2.result b/mysql-test/r/rpl_server_id2.result new file mode 100644 index 00000000000..fde7251497a --- /dev/null +++ b/mysql-test/r/rpl_server_id2.result @@ -0,0 +1,24 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +drop table if exists t1; +Warnings: +Note 1051 Unknown table 't1' +create table t1 (n int); +reset master; +stop slave; +change master to master_port=SLAVE_PORT; +show slave status; +Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master + 127.0.0.1 root SLAVE_PORT 1 4 slave-relay-bin.000001 4 No No # 0 0 0 4 None 0 No NULL +start slave; +insert into t1 values (1); +select * from t1; +n +1 +1 +stop slave; +drop table t1; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 9d9b006cdd5..3ea2082873d 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -43,7 +43,7 @@ wait_timeout 28800 show variables like "this_doesn't_exists%"; Variable_name Value show table status from test like "this_doesn't_exists%"; -Name Type 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 +Name Engine 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 show databases; Database mysql @@ -90,8 +90,8 @@ test_set set( 'val1', 'val2', 'val3' ) not null default '', name char(20) default 'O''Brien' comment 'O''Brien as default', c int not null comment 'int column', `c-b` int comment 'name with a minus', -`space 2` int comment 'name with a space', -) comment = 'it\'s a table' ; +`space 2` int comment 'name with a space' + ) comment = 'it\'s a table' ; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -309,7 +309,7 @@ insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1,1),(2,2); show table status; -Name Type 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 +Name Engine 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 Fixed 2 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 2 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 2 9 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL @@ -317,7 +317,7 @@ insert into t1 values (3),(4); insert into t2 values (3),(4); insert into t3 values (3,3),(4,4); show table status; -Name Type 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 +Name Engine 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 Fixed 4 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 4 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 4 9 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL @@ -325,7 +325,7 @@ insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); show table status; -Name Type 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 +Name Engine 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 Fixed 5 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 5 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 5 9 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL @@ -333,7 +333,7 @@ delete from t1 where a=3; delete from t2 where b=3; delete from t3 where a=3; show table status; -Name Type 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 +Name Engine 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 Fixed 4 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 4 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 4 9 # # # 9 NULL NULL NULL NULL latin1_swedish_ci NULL @@ -341,7 +341,7 @@ delete from t1; delete from t2; delete from t3; show table status; -Name Type 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 +Name Engine 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 Fixed 0 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 0 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 0 9 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL @@ -349,7 +349,7 @@ insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); show table status; -Name Type 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 +Name Engine 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 Fixed 1 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 1 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 1 9 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL @@ -357,7 +357,7 @@ delete from t1 where a=5; delete from t2 where b=5; delete from t3 where a=5; show table status; -Name Type 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 +Name Engine 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 Fixed 0 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 0 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 0 9 # # # 9 NULL NULL NULL NULL latin1_swedish_ci NULL diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 19bda2fec44..00aaf0e530d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -266,7 +266,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Warnings: -Note 1003 select high_priority test.t3.a AS `a` from test.t3 where (test.t3.a >= (select min(test.t2.b) from test.t2 limit 1)) +Note 1003 select high_priority test.t3.a AS `a` from test.t3 where (test.t3.a >= (select min(test.t2.b) from test.t2)) select * from t3 where a >= all (select b from t2); a 7 @@ -327,7 +327,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Warnings: Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 -Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq) limit 1) +Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq)) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column: 'a' in field list is ambiguous drop table if exists t1,t2,t3; @@ -357,10 +357,10 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t8 const PRIMARY PRIMARY 35 const 1 -4 SUBQUERY t8 const PRIMARY PRIMARY 35 1 +1 PRIMARY t8 const PRIMARY PRIMARY 35 const 1 Using index +4 SUBQUERY t8 const PRIMARY PRIMARY 35 1 Using index 2 SUBQUERY t8 const PRIMARY PRIMARY 35 const 1 -3 SUBQUERY t8 const PRIMARY PRIMARY 35 1 +3 SUBQUERY t8 const PRIMARY PRIMARY 35 1 Using index Warnings: Note 1003 select high_priority test.t8.pseudo AS `pseudo`,(select test.t8.email AS `email` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce'))) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM @@ -537,7 +537,7 @@ Warnings: Note 1003 select high_priority max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1 where (test.t1.numeropost = _latin1'1') EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select high_priority test.t1.numreponse AS `numreponse` from test.t1 where ((test.t1.numeropost = _latin1'1') and (test.t1.numreponse = 3)) @@ -736,7 +736,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select high_priority test.t2.id AS `id` from test.t2 where <in_optimizer>(test.t2.id,<exists>(select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(1)) limit 1 union select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(3)) limit 1)) +Note 1003 select high_priority test.t2.id AS `id` from test.t2 where <in_optimizer>(test.t2.id,<exists>(select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(1)) union select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(3)))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -899,7 +899,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and ((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having <is_not_null_test>(test.t2.a) limit 1)) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1 +Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and ((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having <is_not_null_test>(test.t2.a))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1 drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -1159,7 +1159,7 @@ CREATE TABLE `t1` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `pseudo` varchar(35) character set latin1 NOT NULL default '', PRIMARY KEY (`id`), -UNIQUE KEY `pseudo` (`pseudo`), +UNIQUE KEY `pseudo` (`pseudo`) ) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (pseudo) VALUES ('test'); SELECT 0 IN (SELECT 1 FROM t1 a); @@ -1170,7 +1170,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1180,7 +1180,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', @@ -1264,7 +1264,7 @@ CREATE TABLE `t1` ( `email` varchar(60) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), -UNIQUE KEY `pseudo` (`pseudo`), +UNIQUE KEY `pseudo` (`pseudo`) ) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1'); SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1); @@ -1312,7 +1312,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index Warnings: -Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1)) +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)))) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1351,7 +1351,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 Using where; Using index 2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using where; Using index Warnings: -Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1)) +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)))) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1491,7 +1491,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a < (select max(test.t2.b) from test.t2 limit 1))) +Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a < (select max(test.t2.b) from test.t2))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1674,7 +1674,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 7 Using where; Using index Warnings: Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 -Note 1003 select high_priority test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null) limit 1)) +Note 1003 select high_priority test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); @@ -1715,3 +1715,15 @@ create table t3(flag int); select (select * from t3 where id not null) from t1, t2; 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 'null) from t1, t2' at line 1 drop table t1,t2,t3; +CREATE TABLE t1 (id INT); +CREATE TABLE t2 (id INT); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1); +SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id); +id c +1 1 +2 0 +SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id; +id c +1 1 +2 0 diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 7c02edb9c78..0379405eaa8 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -924,3 +924,29 @@ a 1 2 2 +set sql_select_limit=1; +select 1 union select 2; +1 +1 +(select 1) union (select 2); +1 +1 +(select 1) union (select 2) union (select 3) limit 2; +1 +1 +2 +set sql_select_limit=default; +create table t1 (a int); +insert into t1 values (100), (1); +create table t2 (a int); +insert into t2 values (100); +select a from t1 union select a from t2 order by a; +a +1 +100 +SET SQL_SELECT_LIMIT=1; +select a from t1 union select a from t2 order by a; +a +1 +drop table t1, t2; +set sql_select_limit=default; |