diff options
Diffstat (limited to 'mysql-test/r')
30 files changed, 864 insertions, 110 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index aadb35c32e8..1fe874a211c 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -92,6 +92,25 @@ key (n2, n3, n4, n1), key (n3, n4, n1, n2), key (n4, n1, n2, n3) ); 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 0 n1 1 n1 A 0 NULL NULL BTREE +t1 1 n1_2 1 n1 A NULL NULL NULL BTREE disabled +t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE disabled +t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n2 1 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n2 2 n3 A NULL NULL NULL YES BTREE disabled +t1 1 n2 3 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n2 4 n1 A NULL NULL NULL BTREE disabled +t1 1 n3 1 n3 A NULL NULL NULL YES BTREE disabled +t1 1 n3 2 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n3 3 n1 A NULL NULL NULL BTREE disabled +t1 1 n3 4 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n4 1 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n4 2 n1 A NULL NULL NULL BTREE disabled +t1 1 n4 3 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n4 4 n3 A NULL NULL NULL YES BTREE disabled insert into t1 values(10,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(9,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(8,RAND()*1000,RAND()*1000,RAND()); @@ -103,6 +122,25 @@ insert into t1 values(3,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(2,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(1,RAND()*1000,RAND()*1000,RAND()); 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 0 n1 1 n1 A 10 NULL NULL BTREE +t1 1 n1_2 1 n1 A 10 NULL NULL BTREE +t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE +t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE +t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE +t1 1 n2 1 n2 A 10 NULL NULL YES BTREE +t1 1 n2 2 n3 A 10 NULL NULL YES BTREE +t1 1 n2 3 n4 A 10 NULL NULL YES BTREE +t1 1 n2 4 n1 A 10 NULL NULL BTREE +t1 1 n3 1 n3 A 10 NULL NULL YES BTREE +t1 1 n3 2 n4 A 10 NULL NULL YES BTREE +t1 1 n3 3 n1 A 10 NULL NULL BTREE +t1 1 n3 4 n2 A 10 NULL NULL YES BTREE +t1 1 n4 1 n4 A 10 NULL NULL YES BTREE +t1 1 n4 2 n1 A 10 NULL NULL BTREE +t1 1 n4 3 n2 A 10 NULL NULL YES BTREE +t1 1 n4 4 n3 A 10 NULL NULL YES BTREE drop table t1; create table t1 (i int unsigned not null auto_increment primary key); insert into t1 values (null),(null),(null),(null); @@ -120,5 +158,121 @@ alter table t2 rename t1, add c char(10) comment "no comment"; show columns from t1; Field Type Collation Null Key Default Extra i int(10) unsigned binary PRI NULL auto_increment -c char(10) latin1_swedish_ci YES NULL +c char(10) latin1_swedish_ci YES NULL +drop table t1; +create table t1 (a int, b int); +insert into t1 values(1,100), (2,100), (3, 100); +insert into t1 values(1,99), (2,99), (3, 99); +insert into t1 values(1,98), (2,98), (3, 98); +insert into t1 values(1,97), (2,97), (3, 97); +insert into t1 values(1,96), (2,96), (3, 96); +insert into t1 values(1,95), (2,95), (3, 95); +insert into t1 values(1,94), (2,94), (3, 94); +insert into t1 values(1,93), (2,93), (3, 93); +insert into t1 values(1,92), (2,92), (3, 92); +insert into t1 values(1,91), (2,91), (3, 91); +insert into t1 values(1,90), (2,90), (3, 90); +insert into t1 values(1,89), (2,89), (3, 89); +insert into t1 values(1,88), (2,88), (3, 88); +insert into t1 values(1,87), (2,87), (3, 87); +insert into t1 values(1,86), (2,86), (3, 86); +insert into t1 values(1,85), (2,85), (3, 85); +insert into t1 values(1,84), (2,84), (3, 84); +insert into t1 values(1,83), (2,83), (3, 83); +insert into t1 values(1,82), (2,82), (3, 82); +insert into t1 values(1,81), (2,81), (3, 81); +insert into t1 values(1,80), (2,80), (3, 80); +insert into t1 values(1,79), (2,79), (3, 79); +insert into t1 values(1,78), (2,78), (3, 78); +insert into t1 values(1,77), (2,77), (3, 77); +insert into t1 values(1,76), (2,76), (3, 76); +insert into t1 values(1,75), (2,75), (3, 75); +insert into t1 values(1,74), (2,74), (3, 74); +insert into t1 values(1,73), (2,73), (3, 73); +insert into t1 values(1,72), (2,72), (3, 72); +insert into t1 values(1,71), (2,71), (3, 71); +insert into t1 values(1,70), (2,70), (3, 70); +insert into t1 values(1,69), (2,69), (3, 69); +insert into t1 values(1,68), (2,68), (3, 68); +insert into t1 values(1,67), (2,67), (3, 67); +insert into t1 values(1,66), (2,66), (3, 66); +insert into t1 values(1,65), (2,65), (3, 65); +insert into t1 values(1,64), (2,64), (3, 64); +insert into t1 values(1,63), (2,63), (3, 63); +insert into t1 values(1,62), (2,62), (3, 62); +insert into t1 values(1,61), (2,61), (3, 61); +insert into t1 values(1,60), (2,60), (3, 60); +insert into t1 values(1,59), (2,59), (3, 59); +insert into t1 values(1,58), (2,58), (3, 58); +insert into t1 values(1,57), (2,57), (3, 57); +insert into t1 values(1,56), (2,56), (3, 56); +insert into t1 values(1,55), (2,55), (3, 55); +insert into t1 values(1,54), (2,54), (3, 54); +insert into t1 values(1,53), (2,53), (3, 53); +insert into t1 values(1,52), (2,52), (3, 52); +insert into t1 values(1,51), (2,51), (3, 51); +insert into t1 values(1,50), (2,50), (3, 50); +insert into t1 values(1,49), (2,49), (3, 49); +insert into t1 values(1,48), (2,48), (3, 48); +insert into t1 values(1,47), (2,47), (3, 47); +insert into t1 values(1,46), (2,46), (3, 46); +insert into t1 values(1,45), (2,45), (3, 45); +insert into t1 values(1,44), (2,44), (3, 44); +insert into t1 values(1,43), (2,43), (3, 43); +insert into t1 values(1,42), (2,42), (3, 42); +insert into t1 values(1,41), (2,41), (3, 41); +insert into t1 values(1,40), (2,40), (3, 40); +insert into t1 values(1,39), (2,39), (3, 39); +insert into t1 values(1,38), (2,38), (3, 38); +insert into t1 values(1,37), (2,37), (3, 37); +insert into t1 values(1,36), (2,36), (3, 36); +insert into t1 values(1,35), (2,35), (3, 35); +insert into t1 values(1,34), (2,34), (3, 34); +insert into t1 values(1,33), (2,33), (3, 33); +insert into t1 values(1,32), (2,32), (3, 32); +insert into t1 values(1,31), (2,31), (3, 31); +insert into t1 values(1,30), (2,30), (3, 30); +insert into t1 values(1,29), (2,29), (3, 29); +insert into t1 values(1,28), (2,28), (3, 28); +insert into t1 values(1,27), (2,27), (3, 27); +insert into t1 values(1,26), (2,26), (3, 26); +insert into t1 values(1,25), (2,25), (3, 25); +insert into t1 values(1,24), (2,24), (3, 24); +insert into t1 values(1,23), (2,23), (3, 23); +insert into t1 values(1,22), (2,22), (3, 22); +insert into t1 values(1,21), (2,21), (3, 21); +insert into t1 values(1,20), (2,20), (3, 20); +insert into t1 values(1,19), (2,19), (3, 19); +insert into t1 values(1,18), (2,18), (3, 18); +insert into t1 values(1,17), (2,17), (3, 17); +insert into t1 values(1,16), (2,16), (3, 16); +insert into t1 values(1,15), (2,15), (3, 15); +insert into t1 values(1,14), (2,14), (3, 14); +insert into t1 values(1,13), (2,13), (3, 13); +insert into t1 values(1,12), (2,12), (3, 12); +insert into t1 values(1,11), (2,11), (3, 11); +insert into t1 values(1,10), (2,10), (3, 10); +insert into t1 values(1,9), (2,9), (3, 9); +insert into t1 values(1,8), (2,8), (3, 8); +insert into t1 values(1,7), (2,7), (3, 7); +insert into t1 values(1,6), (2,6), (3, 6); +insert into t1 values(1,5), (2,5), (3, 5); +insert into t1 values(1,4), (2,4), (3, 4); +insert into t1 values(1,3), (2,3), (3, 3); +insert into t1 values(1,2), (2,2), (3, 2); +insert into t1 values(1,1), (2,1), (3, 1); +alter table t1 add unique (a,b), add key (b); +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 a 2 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 100 NULL NULL YES BTREE +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A 3 NULL NULL YES BTREE +t1 0 a 2 b A 300 NULL NULL YES BTREE +t1 1 b 1 b A 100 NULL NULL YES BTREE drop table t1; diff --git a/mysql-test/r/ansi.result b/mysql-test/r/ansi.result new file mode 100644 index 00000000000..f9f96310b73 --- /dev/null +++ b/mysql-test/r/ansi.result @@ -0,0 +1,10 @@ +drop table if exists t1; +SELECT 'A' || 'B'; +'A' || 'B' +AB +CREATE TABLE t1 (id INT, id2 int); +SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id; +id NULL 1 1.1 a +SELECT id FROM t1 GROUP BY id2; +'t1.id' isn't in GROUP BY +drop table t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 76e749ab6e7..520227c0fba 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -27,13 +27,9 @@ The used storage engine can't index column 'b' create table t1 (a int not null auto_increment,primary key (a)) type=heap; create table t1 (a int not null,b text) type=heap; The used table type doesn't support BLOB/TEXT columns -create table t1 (a int ,primary key(a)) type=heap; -All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead drop table if exists t1; create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=heap; Incorrect table definition; There can only be one auto column and it must be defined as a key -create table t1 (ordid int(8), primary key (ordid)); -All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead create table not_existing_database.test (a int); Got one of the listed errors create table `a/a` (a int); @@ -178,6 +174,38 @@ select * from t1; if('2002'='2002','Y','N') Y drop table if exists t1; +SET SESSION table_type="heap"; +SELECT @@table_type; +@@table_type +HEAP +CREATE TABLE t1 (a int not null); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0' +) TYPE=HEAP +drop table t1; +SET SESSION table_type="gemini"; +SELECT @@table_type; +@@table_type +GEMINI +CREATE TABLE t1 (a int not null); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0' +) TYPE=MyISAM +SET SESSION table_type=default; +drop table t1; +create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); +insert into t1 values ("a", 1), ("b", 2); +insert into t1 values ("c", NULL); +Column 'k2' cannot be null +insert into t1 values (NULL, 3); +Column 'k1' cannot be null +insert into t1 values (NULL, NULL); +Column 'k1' cannot be null +drop table t1; 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_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index b79bc67138c..630fef9b679 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -212,3 +212,55 @@ select * from t1 where match a against ("te*" in boolean mode)+0; a test drop table t1; +create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); +update t1 set word2=word; +select word, word=0xdf as t from t1 having t > 0; +word t +ß 1 +select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0; +word t +ss 1 +ß 1 +select * from t1 where word=0xDF; +word word2 +ß ß +select * from t1 where word=CAST(0xDF as CHAR); +word word2 +ss ss +ß ß +select * from t1 where word2=0xDF; +word word2 +ß ß +select * from t1 where word2=CAST(0xDF as CHAR); +word word2 +ss ss +ß ß +select * from t1 where word='ae'; +word word2 +ä ä +ae ae +select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR); +word word2 +ä ä +ae ae +select * from t1 where word between 0xDF and 0xDF; +word word2 +ß ß +select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR); +word word2 +ss ss +ß ß +select * from t1 where word like 'ae'; +word word2 +ae ae +select * from t1 where word like 'AE'; +word word2 +ae ae +select * from t1 where word like 0xDF; +word word2 +ß ß +select * from t1 where word like CAST(0xDF as CHAR); +word word2 +ß ß +drop table t1; diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result new file mode 100644 index 00000000000..223a18f19e9 --- /dev/null +++ b/mysql-test/r/ctype_ujis.result @@ -0,0 +1,8 @@ +drop table if exists t1; +create table t1 (c text); +insert into t1 values (0xa4a2),(0xa4a3); +select hex(left(c,1)) from t1 group by c; +hex(left(c,1)) +A4A2 +A4A3 +drop table t1; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index ae216f9b380..c87fa8fb927 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -46,4 +46,15 @@ NULL d 7 delete from t1 where misc > 5 and bool is null; select * from t1 where misc > 5 and bool is null; bool not_null misc +select count(*) from t1; +count(*) +2 +delete from t1 where 1 > 2; +select count(*) from t1; +count(*) +2 +delete from t1 where 3 > 2; +select count(*) from t1; +count(*) +0 drop table t1; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index c40e9d9bfa2..d87b00dd4d7 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -216,3 +216,14 @@ test.t1 repair status OK select * from t1 where match (a) against ('aaaa'); a drop table t1; +drop table if exists t1; +create table t1 ( ref_mag text not null, fulltext (ref_mag)); +insert into t1 values ('test'); +select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); +ref_mag +test +alter table t1 change ref_mag ref_mag char (255) not null; +select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); +ref_mag +test +drop table t1; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index b7bf3a5cd80..e9fb56a77d6 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -42,6 +42,16 @@ insert into t1 values (null,null,''); select count(distinct a),count(distinct grp) from t1; count(distinct a) count(distinct grp) 6 3 +select sum(all a),count(all a),avg(all a),std(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; +sum(all a) count(all a) avg(all a) std(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) +21 6 3.5000 1.7078 7 0 1 6 E +select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; +grp sum(a) count(a) avg(a) std(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) +NULL NULL 0 NULL NULL 0 0 NULL NULL +1 1 1 1.0000 0.0000 1 1 1 1 a a +2 5 2 2.5000 0.5000 3 2 2 3 b c +3 15 3 5.0000 0.8165 7 4 4 6 C E +select grp, sum(a)+count(a)+avg(a)+std(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; sum(all a) count(all a) avg(all a) std(all a) variance(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) 21 6 3.5000 1.7078 2.9167 7 0 1 6 E @@ -221,6 +231,47 @@ 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 -1 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 -1 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 0 0 +2 0 NULL NULL NULL NULL NULL 0 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 -1 0 +2 0 NULL NULL NULL NULL NULL -1 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 0 0 +2 1 1 1.0000 0.0000 1 1 0 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 -1 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 0 0 +2 1 1 1.0000 0.0000 1 1 0 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) 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 -1 0 +2 1 1 1.0000 0.0000 1 1 1 1 +3 1 1 1.0000 0.0000 1 1 1 1 +drop table t1; create table t1( a1 char(3) primary key, a2 smallint, @@ -561,4 +612,4 @@ select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index 1 SIMPLE t2 index NULL k2 4 NULL 6 Using where; Using index -drop table if exists t1, t2; +drop table t1, t2; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 4eed80c4cc9..8d05adcc1ba 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -10,3 +10,6 @@ inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) NULL 255.255.1.255 0.0.1.255 +select length(format('nan', 2)) > 0; +length(format('nan', 2)) > 0 +1 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 337d5639056..5a5689f0185 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -29,6 +29,8 @@ PRIMARY KEY (userID) INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1'); SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; userid MIN(t1.score) 1 1 @@ -49,8 +51,12 @@ userid MIN(t1.score+0.0) 2 2.0 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; userid MIN(t1.score+0.0) -1 1.0 2 2.0 +1 1.0 +EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary +t2 eq_ref PRIMARY PRIMARY 4 t1.userID 1 Using index drop table t1,t2; CREATE TABLE t1 ( PID int(10) unsigned NOT NULL auto_increment, @@ -244,7 +250,7 @@ score smallint(5) unsigned, key (spid), key (score) ); -INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3); +INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3); explain select userid,count(*) from t1 group by userid desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort @@ -253,16 +259,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary select userid,count(*) from t1 group by userid desc; userid count(*) -3 3 +3 5 2 1 1 2 select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3); userid count(*) -3 3 1 2 select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*)); userid count(*) -3 3 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index @@ -282,12 +286,14 @@ spid count(*) 1 1 explain select sql_big_result spid,sum(userid) from t1 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 select sql_big_result spid,sum(userid) from t1 group by spid desc; spid sum(userid) +7 3 +6 3 5 3 4 3 3 3 @@ -295,13 +301,13 @@ spid sum(userid) 1 1 explain select sql_big_result score,count(*) from t1 group by score desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL score 3 NULL 6 Using index +1 SIMPLE t1 index NULL score 3 NULL 8 Using index explain select sql_big_result score,count(*) from t1 group by score desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL score 3 NULL 6 Using index +1 SIMPLE t1 index NULL score 3 NULL 8 Using index select sql_big_result score,count(*) from t1 group by score desc; score count(*) -3 3 +3 5 2 1 1 2 drop table t1; @@ -536,15 +542,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where drop table t1,t2; create table t1 (a int, b int); -insert into t1 values (1, 4); -insert into t1 values (10, 40); -insert into t1 values (1, 4); -insert into t1 values (10, 43); -insert into t1 values (1, 4); -insert into t1 values (10, 41); -insert into t1 values (1, 4); -insert into t1 values (10, 43); -insert into t1 values (1, 4); +insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a; a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) 1 4 2 diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index bd1bd523964..fc0b3c652ef 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -67,3 +67,18 @@ Fld1 max(Fld2) 1 20 3 50 drop table t1; +create table t1 (id int not null, qty int not null); +insert into t1 values (1,2),(1,3),(2,4),(2,5); +select id, sum(qty) as sqty from t1 group by id having sqty>2; +id sqty +1 5 +2 9 +select sum(qty) as sqty from t1 group by id having count(id) > 0; +sqty +5 +9 +select sum(qty) as sqty from t1 group by id having count(distinct id) > 0; +sqty +5 +9 +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 51669f563de..922f4816e90 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -138,15 +138,6 @@ id parent_id level 1008 102 2 1010 102 2 1015 102 2 -explain select level from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index -explain select level,id from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index -explain select level,id,parent_id from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where select level,id from t1 where level=1; level id 1 1002 @@ -208,7 +199,7 @@ create index skr on t1 (a); insert into t1 values (3,""), (4,"testing"); analyze table t1; Table Op Msg_type Msg_text -test.t1 analyze error The storage enginge for the table doesn't support analyze +test.t1 analyze status OK 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 skr 1 a A 3 NULL NULL YES BTREE @@ -595,9 +586,6 @@ id parent_id level 1009 102 2 1025 102 2 1016 102 2 -explain select level from t1 where level=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 6 Using where; Using index select level,id from t1 where level=1; level id 1 1004 @@ -1141,7 +1129,7 @@ a b drop table t1; CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb; CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12); INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); update t1,t2 set t1.a=t1.a+100; select * from t1; @@ -1155,6 +1143,9 @@ a b 107 7 108 8 109 9 +110 10 +111 11 +112 12 update t1,t2 set t1.a=t1.a+100 where t1.a=101; select * from t1; a b @@ -1167,6 +1158,9 @@ a b 107 7 108 8 109 9 +110 10 +111 11 +112 12 update t1,t2 set t1.b=t1.b+10 where t1.b=2; select * from t1; a b @@ -1178,8 +1172,11 @@ a b 107 7 108 8 109 9 +110 10 +111 11 102 12 -update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5; +112 12 +update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100; select * from t1; a b 201 1 @@ -1190,19 +1187,105 @@ a b 107 7 108 8 109 9 +110 10 +111 11 102 12 +112 12 select * from t2; a b -1 5 -2 5 -3 5 -4 5 -5 5 -6 5 -7 5 -8 5 -9 5 +1 1 +2 2 +6 6 +7 7 +8 8 +9 9 +3 13 +4 14 +5 15 drop table t1,t2; +CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) TYPE=MyISAM; +CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) TYPE=InnoDB; +SET AUTOCOMMIT=0; +INSERT INTO t1 ( B_ID ) VALUES ( 1 ); +INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); +ROLLBACK; +Warning: Some non-transactional changed tables couldn't be rolled back +SELECT * FROM t1; +B_ID +drop table t1,t2; +create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) type = innodb; +insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); +select distinct parent,child from t1 order by parent; +parent child +0 4 +1 2 +1 3 +2 1 +drop table t1; +create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb; +create table t2 (a int not null auto_increment primary key, b int); +insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); +insert into t2 (a) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +select count(*) from t1; +count(*) +29267 +explain select * from t1 where c between 1 and 10000; +table type possible_keys key key_len ref rows Extra +t1 range c c 5 NULL 1 Using where +update t1 set c=a; +explain select * from t1 where c between 1 and 10000; +table type possible_keys key key_len ref rows Extra +t1 ALL c NULL NULL NULL 29537 Using where +drop table t1,t2; +create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; +insert into t1 (id) values (null),(null),(null),(null),(null); +update t1 set fk=69 where fk is null order by id limit 1; +SELECT * from t1; +id fk +2 NULL +3 NULL +4 NULL +5 NULL +1 69 +drop table t1; +create table t1 (a int not null, b int not null, key (a)); +insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3); +SET @tmp=0; +update t1 set b=(@tmp:=@tmp+1) order by a; +update t1 set b=99 where a=1 order by b asc limit 1; +update t1 set b=100 where a=1 order by b desc limit 2; +update t1 set a=a+10+b where a=1 order by b; +select * from t1 order by a,b; +a b +2 4 +2 5 +2 6 +3 7 +3 8 +3 9 +3 10 +3 11 +3 12 +13 2 +111 100 +111 100 CREATE TABLE t1 (col1 int(1))TYPE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP(+0),INDEX stamp_idx (stamp))TYPE=InnoDB; diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result index 4ce104506f1..38c39e2936f 100644 --- a/mysql-test/r/innodb_handler.result +++ b/mysql-test/r/innodb_handler.result @@ -139,4 +139,13 @@ a b alter table t1 type=innodb; handler t2 read first; Unknown table 't2' in HANDLER -drop table if exists t1; +drop table t1; +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)) TYPE=InnoDB; +INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); +HANDLER t1 OPEN; +HANDLER t1 READ `primary` = (1, 1000); +no1 no2 +HANDLER t1 READ `primary` PREV; +no1 no2 +1 275 +DROP TABLE t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 09b819888eb..4bc045aa2f7 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -274,3 +274,101 @@ SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (site rate_code base_rate cust 20 drop table t1,t2; +CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255)); +CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255)); +INSERT INTO t1 VALUES (1, 'A'); +INSERT INTO t2 VALUES (1, 'B'); +SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B'); +ID Value1 ID Value2 +SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B'; +ID Value1 ID Value2 +SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1; +ID Value1 ID Value2 +drop table t1,t2; +create table t1 (i int); +create table t2 (i int); +create table t3 (i int); +insert into t1 values(1),(2); +insert into t2 values(2),(3); +insert into t3 values (2),(4); +select * from t1 natural left join t2; +i i +1 NULL +2 2 +select * from t1 left join t2 on (t1.i=t2.i); +i i +1 NULL +2 2 +select * from t1 natural left join t2 natural left join t3; +i i i +1 NULL NULL +2 2 2 +select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i); +i i i +1 NULL NULL +2 2 2 +select * from t3 natural right join t2; +i i +2 2 +NULL 3 +select * from t3 right join t2 on (t3.i=t2.i); +i i +2 2 +NULL 3 +select * from t3 natural right join t2 natural right join t1; +i i i +NULL NULL 1 +2 2 2 +select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i); +i i i +NULL NULL 1 +2 2 2 +select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +drop table t1,t2,t3; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 9ac44bec377..1412c5e7d10 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -350,12 +350,7 @@ select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) name name id id Antonio Paz El Gato 1 1 Antonio Paz Perrito 2 1 -NULL Happy 3 1 -NULL El Gato 1 2 -NULL Perrito 2 2 -NULL Happy 3 2 -NULL El Gato 1 3 -NULL Perrito 2 3 +NULL NULL NULL 2 Thimble Smith Happy 3 3 select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner; name name id owner id @@ -372,10 +367,10 @@ Thimble Smith Happy 3 3 3 drop table t1,t2; create table t1 (id int not null, str char(10), index(str)); insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar"); -select * from t1 where str is not null; +select * from t1 where str is not null order by id; id str -4 bar 3 foo +4 bar select * from t1 where str is null; id str 1 NULL @@ -651,3 +646,15 @@ fooID barID fooID 20 2 NULL 30 3 30 drop table t1,t2; +drop table if exists t3; +create table t1 (i int); +create table t2 (i int); +create table t3 (i int); +insert into t1 values(1),(2); +insert into t2 values(2),(3); +insert into t3 values(2),(4); +select * from t1 natural left join t2 natural left join t3; +i i i +1 NULL NULL +2 2 2 +drop table t1,t2,t3; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 9e1f743d132..7fe7109699c 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -42,7 +42,6 @@ price area type transityes shopsyes schoolsyes petsyes drop table t1; CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); ALTER TABLE t1 modify program enum('signup','unique','sliding'); -All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead drop table t1; CREATE TABLE t1 ( name varchar(50) DEFAULT '' NOT NULL, diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result new file mode 100644 index 00000000000..d121a4e6c40 --- /dev/null +++ b/mysql-test/r/loaddata.result @@ -0,0 +1,11 @@ +drop table if exists t1; +create table t1 (a date, b date, c date not null, d date); +load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ','; +load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; +SELECT * from t1; +a b c d +0000-00-00 NULL 0000-00-00 0000-00-00 +0000-00-00 0000-00-00 0000-00-00 0000-00-00 +2003-03-03 2003-03-03 2003-03-03 NULL +2003-03-03 2003-03-03 2003-03-03 NULL +drop table t1; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index da1c429df63..2aa7db1599e 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -300,7 +300,7 @@ a b 107 7 108 8 109 9 -update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5; +update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100; select * from t1; a b 201 1 @@ -314,13 +314,21 @@ a b 109 9 select * from t2; a b -1 3 -2 3 -3 3 -4 3 -5 3 -6 3 -7 3 -8 3 -9 3 +1 1 +2 2 +3 13 +4 14 +5 15 +6 6 +7 7 +8 8 +9 9 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)) TYPE=MyISAM; +INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); +create table t1 (A varchar(1)); +insert into t1 values ("A") ,("B"),("C"),("D"); +create table t2(Z varchar(15)); +insert into t2(Z) select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d; +update t2,t3 set Z =param_scenario_costs; +drop table t1,t2,t3; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index ce3af6c37cd..3248d359546 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -84,7 +84,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 5 NULL 5 Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 4 Using where +1 SIMPLE t1 range a,b a 5 NULL 12 Using where explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index bd5b283f26a..a8a6dd6312d 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -516,6 +516,31 @@ SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,des titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug drop table t1,t2; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1, 2); +INSERT INTO t1 VALUES (3, 4); +INSERT INTO t1 VALUES (5, NULL); +SELECT * FROM t1 ORDER BY b; +a b +5 NULL +1 2 +3 4 +SELECT * FROM t1 ORDER BY b DESC; +a b +3 4 +1 2 +5 NULL +SELECT * FROM t1 ORDER BY (a + b); +a b +5 NULL +1 2 +3 4 +SELECT * FROM t1 ORDER BY (a + b) DESC; +a b +3 4 +1 2 +5 NULL +DROP TABLE t1; CREATE TABLE t1 ( FieldKey varchar(36) NOT NULL default '', LongVal bigint(20) default NULL, diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 46fa12dd9bc..48a2a8a5a21 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -238,13 +238,18 @@ a show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 -select sql_cache * from t1; +select sql_cache * from t1 union select * from t1; a 1 2 3 set query_cache_type=2; -select sql_cache * from t1; +select sql_cache * from t1 union select * from t1; +a +1 +2 +3 +select * from t1 union select sql_cache * from t1; a 1 2 @@ -254,7 +259,7 @@ Variable_name Value Qcache_hits 4 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 1 +Qcache_queries_in_cache 2 set query_cache_type=on; reset query cache; show status like "Qcache_queries_in_cache"; @@ -636,3 +641,21 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1; +create table t1 (a int); +insert into t1 values (1),(2); +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +select * from t1; +a +1 +2 +SET OPTION SQL_SELECT_LIMIT=1; +select * from t1; +a +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +SET OPTION SQL_SELECT_LIMIT=DEFAULT; +drop table t1; diff --git a/mysql-test/r/raid.result b/mysql-test/r/raid.result index 41af50851e9..fd47a9451f6 100644 --- a/mysql-test/r/raid.result +++ b/mysql-test/r/raid.result @@ -1,3 +1,6 @@ +create database test_raid; +create table test_raid.r1 (i int) raid_type=1; +drop database test_raid; DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index 8b50f9a92e8..6c2107b2cf3 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -4,4 +4,12 @@ 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 if exists t1; +alter table t1 TYPE=HEAP; +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair error The handler for the table doesn't support repair +drop table t1; +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair error Table 'test.t1' doesn't exist +create table t1 type=myisam SELECT 1,"table 1"; diff --git a/mysql-test/r/repair_part2.result b/mysql-test/r/repair_part2.result new file mode 100644 index 00000000000..77aa98c3da9 --- /dev/null +++ b/mysql-test/r/repair_part2.result @@ -0,0 +1,8 @@ +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/select.result b/mysql-test/r/select.result index 0d1743d72b7..825a94c0ebf 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3411,13 +3411,7 @@ a a a select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); a a a 1 1 1 -2 1 NULL -3 1 NULL -1 2 NULL 2 2 2 -3 2 NULL -1 3 NULL -2 3 NULL 3 3 3 select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; a a a @@ -3473,13 +3467,7 @@ a a a select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); a a a 1 1 1 -2 1 NULL -3 1 NULL -1 2 NULL 2 2 2 -3 2 NULL -1 3 NULL -2 3 NULL 3 3 3 select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; a a a diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index e225a0e3995..3303f19d9c7 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -1,6 +1,6 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int primary key, b char(20)); +create table t1 (a int auto_increment primary key, b char(20)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; a b @@ -30,10 +30,34 @@ You are using safe update mode and you tried to update a table without a WHERE t delete from t1 where a+0=1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; -The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok +The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok update t1 set b="a" limit 1; update t1 set b="a" where b="b" limit 2; delete from t1 where b="test" limit 1; delete from t1 where a+0=1 limit 2; +SET MAX_JOIN_SIZE=2; +SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; +@@max_join_size @@sql_big_selects +2 0 +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +SELECT * from t1; +The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok +SET SQL_BIG_SELECTS=1; +SELECT * from t1; +a b +3 a +2 test2 +4 a +5 a +SET MAX_JOIN_SIZE=2; +SELECT * from t1; +The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok +SET MAX_JOIN_SIZE=DEFAULT; +SELECT * from t1; +a b +3 a +2 test2 +4 a +5 a drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index 4c326957c03..b74765696a2 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -357,3 +357,9 @@ select * from t1; a 99999999999 drop table t1; +CREATE TABLE t1 (a_dec DECIMAL(-1,0)); +Too big column length for column 'a_dec' (max = 255). Use BLOB instead +CREATE TABLE t1 (a_dec DECIMAL(-2,1)); +Too big column length for column 'a_dec' (max = 255). Use BLOB instead +CREATE TABLE t1 (a_dec DECIMAL(-1,1)); +Too big column length for column 'a_dec' (max = 255). Use BLOB instead diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 7b29a8fbcde..d506891b0fb 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); @@ -198,12 +198,68 @@ a 11 set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; +CREATE TABLE t1 ( +cid smallint(5) unsigned NOT NULL default '0', +cv varchar(250) NOT NULL default '', +PRIMARY KEY (cid), +UNIQUE KEY cv (cv) +) ; +INSERT INTO t1 VALUES (8,'dummy'); +CREATE TABLE t2 ( +cid bigint(20) unsigned NOT NULL auto_increment, +cap varchar(255) NOT NULL default '', +PRIMARY KEY (cid), +KEY cap (cap) +) ; +CREATE TABLE t3 ( +gid bigint(20) unsigned NOT NULL auto_increment, +gn varchar(255) NOT NULL default '', +must tinyint(4) default NULL, +PRIMARY KEY (gid), +KEY gn (gn) +) ; +INSERT INTO t3 VALUES (1,'V1',NULL); +CREATE TABLE t4 ( +uid bigint(20) unsigned NOT NULL default '0', +gid bigint(20) unsigned default NULL, +rid bigint(20) unsigned default NULL, +cid bigint(20) unsigned default NULL, +UNIQUE KEY m (uid,gid,rid,cid), +KEY uid (uid), +KEY rid (rid), +KEY cid (cid), +KEY container (gid,rid,cid) +) ; +INSERT INTO t4 VALUES (1,1,NULL,NULL); +CREATE TABLE t5 ( +rid bigint(20) unsigned NOT NULL auto_increment, +rl varchar(255) NOT NULL default '', +PRIMARY KEY (rid), +KEY rl (rl) +) ; +CREATE TABLE t6 ( +uid bigint(20) unsigned NOT NULL auto_increment, +un varchar(250) NOT NULL default '', +uc smallint(5) unsigned NOT NULL default '0', +PRIMARY KEY (uid), +UNIQUE KEY nc (un,uc), +KEY un (un) +) ; +INSERT INTO t6 VALUES (1,'test',8); +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +uid rl g1 cid gg +1 NULL V1 NULL 1 +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +uid rl g1 cid gg +(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"); +uid rl g1 cid gg +1 NULL V1 NULL 1 +drop table t1,t2,t3,t4,t5,t6; select * union select 1; No tables used select 1 as a,(select a union select a); a (select a union select a) 1 1 -drop table if exists t1,t2; CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM; INSERT INTO t1 (id) VALUES("1"); CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) TYPE=MyISAM; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 9978e3cb29c..c308b8fcbcd 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -102,16 +102,67 @@ select status from t1; status 1 drop table t1; -create table t1 (a int not null, b int not null); -insert into t1 values (1,1),(1,2),(1,3); -update t1 set b=4 where a=1 order by b asc limit 1; -update t1 set b=4 where a=1 order by b desc limit 1; -create table t2 (a int not null, b int not null); -insert into t2 values (1,1),(1,2),(1,3); -select * from t1; +create table t1 (a int not null, b int not null, key (a)); +insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3); +SET @tmp=0; +update t1 set b=(@tmp:=@tmp+1) order by a; +update t1 set b=99 where a=1 order by b asc limit 1; +select * from t1 order by a,b; a b -1 4 1 2 -1 4 -update t1 set b=(select distinct 1 from (select * from t2) a); -drop table t1,t2; +1 3 +1 99 +2 4 +2 5 +2 6 +3 7 +3 8 +3 9 +3 10 +3 11 +3 12 +update t1 set b=100 where a=1 order by b desc limit 2; +update t1 set a=a+10+b where a=1 order by b; +select * from t1 order by a,b; +a b +2 4 +2 5 +2 6 +3 7 +3 8 +3 9 +3 10 +3 11 +3 12 +13 2 +111 100 +111 100 +drop table t1; +CREATE TABLE t1 ( +`id_param` smallint(3) unsigned NOT NULL default '0', +`nom_option` char(40) NOT NULL default '', +`valid` tinyint(1) NOT NULL default '0', +KEY `id_param` (`id_param`,`nom_option`) +) TYPE=MyISAM; +INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1); +UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1; +select * from t1; +id_param nom_option valid +185 test 1 +drop table t1; +create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid)); +insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6), +('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2), +('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4), +('2','2','0',1,7); +delete from t1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3); +select * from t1; +F1 F2 F3 cnt groupid +0 0 0 1 6 +0 1 2 1 5 +0 2 0 1 3 +1 0 1 1 2 +1 2 1 1 1 +2 0 1 2 4 +2 2 0 1 7 +drop table t1; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 5e613418ea1..8cf0a676b95 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -108,17 +108,14 @@ show global variables like 'table_type'; Variable_name Value table_type INNODB set GLOBAL query_cache_size=100000; -set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; -show variables like 'myisam_max_sort_file_size'; -Variable_name Value -myisam_max_sort_file_size 10000 +set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; Variable_name Value -myisam_max_sort_file_size 20000 -set myisam_max_sort_file_size=default; +myisam_max_sort_file_size 1048576 +set GLOBAL myisam_max_sort_file_size=default; show variables like 'myisam_max_sort_file_size'; Variable_name Value -myisam_max_sort_file_size 20000 +myisam_max_sort_file_size FILE_SIZE set global net_retry_count=10, session net_retry_count=10; set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; @@ -199,6 +196,10 @@ set @@global.sql_auto_is_null=1; Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL select @@global.sql_auto_is_null; Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +set myisam_max_sort_file_size=100; +Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL +set myisam_max_extra_sort_file_size=100; +Variable 'myisam_max_extra_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL set autocommit=1; set big_tables=1; select @@autocommit, @@big_tables; @@ -252,11 +253,11 @@ select @@max_user_connections; @@max_user_connections 100 set global max_write_lock_count=100; -set myisam_max_extra_sort_file_size=100; +set global myisam_max_extra_sort_file_size=100; select @@myisam_max_extra_sort_file_size; @@myisam_max_extra_sort_file_size 100 -set myisam_max_sort_file_size=100; +set global myisam_max_sort_file_size=100; set myisam_sort_buffer_size=100; set net_buffer_length=100; set net_read_timeout=100; @@ -323,3 +324,8 @@ test.t1 check status OK test.t2 check status OK select max(a) +1, max(a) +2 into @xx,@yy from t1; drop table t1,t2; +select @@xxxxxxxxxx; +Unknown system variable 'xxxxxxxxxx' +select 1; +1 +1 |