diff options
Diffstat (limited to 'mysql-test')
68 files changed, 2003 insertions, 325 deletions
diff --git a/mysql-test/include/strict_autoinc.inc b/mysql-test/include/strict_autoinc.inc new file mode 100644 index 00000000000..6960440f3a7 --- /dev/null +++ b/mysql-test/include/strict_autoinc.inc @@ -0,0 +1,28 @@ +# +# Test for strict-mode autoincrement +# + +set @org_mode=@@sql_mode; +eval create table t1 +( + `a` tinyint(4) NOT NULL auto_increment, + primary key (`a`) +) engine = $type ; +set @@sql_mode='strict_all_tables'; +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t1 values(1000); +select count(*) from t1; + +set auto_increment_increment=1000; +set auto_increment_offset=700; +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t1 values(null); +select count(*) from t1; + +set @@sql_mode=@org_mode; +insert into t1 values(null); +select * from t1; + +drop table t1; + +# End of test diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 819406bcc41..06dd3864212 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -134,7 +134,6 @@ our @mysqld_src_dirs= our $glob_win32= 0; # OS and native Win32 executables our $glob_win32_perl= 0; # ActiveState Win32 Perl our $glob_cygwin_perl= 0; # Cygwin Perl -our $glob_cygwin_shell= undef; our $glob_mysql_test_dir= undef; our $glob_mysql_bench_dir= undef; our $glob_hostname= undef; @@ -465,10 +464,7 @@ sub initial_setup () { { # Windows programs like 'mysqld' needs Windows paths $glob_mysql_test_dir= `cygpath -m "$glob_mysql_test_dir"`; - my $shell= $ENV{'SHELL'} || "/bin/bash"; - $glob_cygwin_shell= `cygpath -w "$shell"`; # The Windows path c:\... chomp($glob_mysql_test_dir); - chomp($glob_cygwin_shell); } $glob_basedir= dirname($glob_mysql_test_dir); $glob_mysql_bench_dir= "$glob_basedir/mysql-bench"; # FIXME make configurable @@ -1117,7 +1113,9 @@ sub executable_setup () { $path_ndb_tools_dir= mtr_path_exists("$glob_basedir/ndb/tools"); $exe_ndb_mgm= "$glob_basedir/ndb/src/mgmclient/ndb_mgm"; $lib_udf_example= - mtr_file_exists("$glob_basedir/sql/.libs/udf_example.so"); + mtr_file_exists("$glob_basedir/sql/.libs/udf_example.so", + "$glob_basedir/sql/release/udf_example.dll", + "$glob_basedir/sql/debug/udf_example.dll"); } else { diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 3f8083a0e20..5f885ad199b 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -773,3 +773,5 @@ Warnings: Warning 1071 Specified key was too long; max key length is 765 bytes insert into t1 values('aaa'); drop table t1; +create table t1 (upgrade int); +drop table t1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 51f361349e6..bf271437fac 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1340,19 +1340,6 @@ select a from t1 group by a; a e drop table t1; -set names utf8; -grant select on test.* to юзер_юзер@localhost; -user() -юзер_юзер@localhost -revoke all on test.* from юзер_юзер@localhost; -drop user юзер_юзер@localhost; -create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; -use имя_базы_в_кодировке_утф8_длиной_больше_чем_45; -select database(); -database() -имя_базы_в_кодировке_утф8_длиной_больше_чем_45 -drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; -use test; CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa'); SELECT id FROM t1; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 0946dc8f809..9d337a1ed34 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -172,6 +172,10 @@ a 0 2 DROP TABLE t1; +create table t1 (a int); +delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; +delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; +drop table t1; CREATE TABLE t1 (a int not null,b int not null); CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index dc09a68682c..6617ccc671e 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -566,14 +566,14 @@ COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') DROP TABLE t1,t2; select * from (select group_concat('c') from DUAL) t; group_concat('c') -NULL +c create table t1 ( a int not null default 0); select * from (select group_concat(a) from t1) t2; group_concat(a) NULL select group_concat('x') UNION ALL select 1; group_concat('x') -NULL +x 1 drop table t1; CREATE TABLE t1 (id int, a varchar(9)); diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index f57b4ad6ce9..98eb9e9e2b3 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -856,6 +856,22 @@ EXPLAIN SELECT MAX(b) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(2,3); +SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; +(SELECT COUNT(DISTINCT t1.b)) +0 +2 +SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; +(SELECT COUNT(DISTINCT 12)) +1 +1 +SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12), +COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2), +GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2); +AVG(2) BIT_AND(2) BIT_OR(2) BIT_XOR(2) COUNT(*) COUNT(12) COUNT(DISTINCT 12) MIN(2) MAX(2) STD(2) VARIANCE(2) SUM(2) GROUP_CONCAT(2) GROUP_CONCAT(DISTINCT 2) +2.00000 2 2 2 1 1 1 2 2 0.00000 0.00000 2 2 2 +DROP TABLE t1; create table t2 (ff double); insert into t2 values (2.2); select cast(sum(distinct ff) as decimal(5,2)) from t2; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 14da630f61e..00642e1a570 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1113,4 +1113,39 @@ conv("18383815659218730760",10,10) + 0 select "18383815659218730760" + 0; "18383815659218730760" + 0 1.8383815659219e+19 +CREATE TABLE t1 (code varchar(10)); +INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13'); +SELECT ASCII(code), code FROM t1 WHERE code='A12'; +ASCII(code) code +97 a12 +65 A12 +SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65; +ASCII(code) code +65 A12 +INSERT INTO t1 VALUES ('a12 '), ('A12 '); +SELECT LENGTH(code), code FROM t1 WHERE code='A12'; +LENGTH(code) code +3 a12 +3 A12 +4 a12 +5 A12 +SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5; +LENGTH(code) code +5 A12 +ALTER TABLE t1 ADD INDEX (code); +CREATE TABLE t2 (id varchar(10) PRIMARY KEY); +INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14'); +SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id +WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +code id +A12 a12 +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON code=id +WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref code code 13 const 3 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 12 const 1 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5)) +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index dc6a4561531..9fea857e4e5 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -840,39 +840,38 @@ drop table t1; create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); -select f1 from t1 where f1 between "2006-1-1" and 20060101; +select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date); f1 2006-01-01 -select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; +select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 -select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; +select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 -select f2 from t1 where f2 between "12:1:2" and "12:2:2"; +select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 -select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; +select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 -select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); f3 2006-01-01 12:01:01 -select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); f3 2006-01-01 12:01:01 -select f1 from t1 where "2006-1-1" between f1 and f3; +select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; f1 2006-01-01 -select f1 from t1 where "2006-1-1" between date(f1) and date(f3); +select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); f1 2006-01-01 -select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); f1 Warnings: -Warning 1292 Incorrect date value: 'zzz' for column 'f1' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: 'zzz' -Warning 1292 Truncated incorrect DOUBLE value: 'zzz' +Warning 1292 Truncated incorrect datetime value: 'zzz' +Warning 1292 Truncated incorrect datetime value: 'zzz' select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); f1 2006-01-01 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 57cb09fe44c..ef057e04d1a 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -807,8 +807,8 @@ explain SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 where t2.b=v1.a GROUP BY t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index b b 2 NULL 10 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1 +1 SIMPLE t2 index b b 2 NULL 10 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1 SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 where t2.b=v1.a GROUP BY t2.b; a b real_b diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result index e0835bbf8d6..80abcebbfea 100644 --- a/mysql-test/r/heap_hash.result +++ b/mysql-test/r/heap_hash.result @@ -354,7 +354,7 @@ t3 1 a 2 b NULL 13 NULL NULL HASH explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref heap_idx heap_idx 22 const 7 Using where -1 SIMPLE t3 ref a a 44 const,const 7 Using where +1 SIMPLE t3 ref a a 44 func,const 7 Using where drop table t1, t2, t3; create temporary table t1 ( a int, index (a) ) engine=memory; insert into t1 values (1),(2),(3),(4),(5); diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index 3a69f56cbd3..3f3360e2da0 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -424,3 +424,34 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where drop table t3; drop table t0, t1, t2; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1); +CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); +INSERT INTO t2(a,b) VALUES +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(1,2); +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t2(a,b) VALUES(1,2); +SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1; +a +1 +1 +UNLOCK TABLES; +DROP TABLE t1, t2; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 652af1c8387..407f8a040b7 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -337,7 +337,7 @@ mysql test explain select * from v0; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY # ALL NULL NULL NULL NULL 2 +1 SIMPLE # ALL NULL NULL NULL NULL 2 create view v1 (c) as select table_name from information_schema.tables where table_name="v1"; select * from v1; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index e7d097a1d2f..b4101e037f2 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -118,7 +118,7 @@ min(7) NULL select min(7) from DUAL; min(7) -NULL +7 explain select min(7) from t2m join t1m; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away @@ -133,7 +133,7 @@ max(7) NULL select max(7) from DUAL; max(7) -NULL +7 explain select max(7) from t2m join t1m; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away @@ -172,7 +172,7 @@ min(7) NULL select min(7) from DUAL; min(7) -NULL +7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 @@ -188,7 +188,7 @@ max(7) NULL select max(7) from DUAL; max(7) -NULL +7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 89ac863b8d2..0af48d27cd5 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -695,6 +695,16 @@ CREATE TABLE t2 (z int, y int); CREATE TABLE t3 (a int, b int); INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1); DROP TABLE IF EXISTS t1,t2,t3; +CREATE DATABASE bug21774_1; +CREATE DATABASE bug21774_2; +CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); +CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); +CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255)); +INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1; +use bug21774_1; +INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; +DROP DATABASE bug21774_1; +DROP DATABASE bug21774_2; CREATE DATABASE meow; CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index dbe5d600a95..6be37450835 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -63,9 +63,9 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUES(a)` from `test`.`t1` explain extended select * from t1 where values(a); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where values(`test`.`t1`.`a`) DROP TABLE t1; create table t1(a int primary key, b int); insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5); @@ -197,3 +197,25 @@ PRIMARY KEY (a) ) ENGINE=MyISAM; INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; +CREATE TABLE t1 +( +a BIGINT UNSIGNED, +b BIGINT UNSIGNED, +PRIMARY KEY (a) +); +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 1 +INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 2 +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 2 +DROP TABLE t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 2d9652ff0e3..89bb26c4b3f 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1126,7 +1126,7 @@ a b a b 7 8 7 5 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index c7d8f5c128d..0021e6717ee 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -515,6 +515,34 @@ select c1 from t1 order by c1 limit 1; c1 a drop table t1; +create table t1 (a int not null, primary key(a)); +create table t2 (a int not null, b int not null, primary key(a,b)); +insert into t1 values (1),(2),(3),(4),(5),(6); +insert into t2 values (1,1),(2,1); +lock tables t1 read local, t2 read local; +select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; +a a b +1 1 1 +2 2 1 +insert into t2 values(2,0); +select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; +a a b +1 1 1 +2 2 1 +drop table t1,t2; +CREATE TABLE t1 (c1 varchar(250) NOT NULL); +CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)); +INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003'); +INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004'); +LOCK TABLES t1 READ LOCAL, t2 READ LOCAL; +SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 +WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; +t1c1 t2c1 +INSERT INTO t2 VALUES ('test000001'), ('test000005'); +SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 +WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; +t1c1 t2c1 +DROP TABLE t1,t2; CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM; Got one of the listed errors create table t1 (a int, b varchar(200), c text not null) checksum=1; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 99633f5e12a..7dbff4beca5 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -85,6 +85,12 @@ c_cp932 | NULL | NULL | Τη γλώσσα | | NULL | NULL | ᛖᚴ ᚷᛖᛏ | +------+------+---------------------------+ +i j k +NULL 1 NULL +Field Type Null Key Default Extra +i int(11) YES NULL +j int(11) NO +k int(11) YES NULL +------+---+------+ | i | j | k | +------+---+------+ @@ -97,6 +103,10 @@ c_cp932 | j | int(11) | NO | | | | | k | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ +i s1 +1 x +2 NULL +3 +------+------+ | i | s1 | +------+------+ @@ -104,6 +114,13 @@ c_cp932 | 2 | NULL | | 3 | | +------+------+ +unhex('zz') +NULL ++-------------+ +| unhex('zz') | ++-------------+ +| NULL | ++-------------+ create table t1(a int, b varchar(255), c int); Field Type Null Key Default Extra a int(11) YES NULL diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 1d131c67c73..ee50fc42203 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -22,6 +22,9 @@ INSERT INTO t1 VALUES (1), (2); </database> </mysqldump> DROP TABLE t1; +# +# Bug #2005 +# CREATE TABLE t1 (a decimal(64, 20)); INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), ("0987654321098765432109876543210987654321"); @@ -30,6 +33,9 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `t1` VALUES ('1234567890123456789012345678901234567890.00000000000000000000'),('987654321098765432109876543210987654321.00000000000000000000'); DROP TABLE t1; +# +# Bug #2055 +# CREATE TABLE t1 (a double); INSERT INTO t1 VALUES ('-9e999999'); Warnings: @@ -39,6 +45,9 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `t1` VALUES (RES); DROP TABLE t1; +# +# Bug #3361 mysqldump quotes DECIMAL values inconsistently +# 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); @@ -136,6 +145,9 @@ INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); </database> </mysqldump> DROP TABLE t1; +# +# Bug #1707 +# CREATE TABLE t1 (`a"b"` char(2)); INSERT INTO t1 VALUES ("1\""), ("\"2"); <?xml version="1.0"?> @@ -155,6 +167,10 @@ INSERT INTO t1 VALUES ("1\""), ("\"2"); </database> </mysqldump> DROP TABLE t1; +# +# Bug #1994 +# Bug #4261 +# CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'), (NULL); @@ -190,6 +206,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# Bug #2634 +# CREATE TABLE t1 (a int) ENGINE=MYISAM; INSERT INTO t1 VALUES (1), (2); /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; @@ -239,11 +258,17 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# Bug #2592 'mysqldump doesn't quote "tricky" names correctly' +# create table ```a` (i int); CREATE TABLE ```a` ( `i` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; drop table ```a`; +# +# Bug #2591 "mysqldump quotes names inconsistently" +# create table t1(a int); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; @@ -352,6 +377,9 @@ UNLOCK TABLES; set global sql_mode=''; drop table t1; +# +# Bug #2705 'mysqldump --tab extra output' +# create table t1(a int); insert into t1 values (1),(2),(3); @@ -380,6 +408,9 @@ CREATE TABLE `t1` ( 2 3 drop table t1; +# +# Bug #6101: create database problem +# /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -432,6 +463,12 @@ USE `mysqldump_test_db`; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; drop database mysqldump_test_db; +# +# Bug #7020 +# Check that we don't dump in UTF8 in compatible mode by default, +# but use the default compiled values, or the values given in +# --default-character-set=xxx. However, we should dump in UTF8 +# if it is explicitely set. CREATE TABLE t1 (a CHAR(10)); INSERT INTO t1 VALUES (_latin1 ''); @@ -465,6 +502,13 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; +# +# Bug#8063: make test mysqldump [ fail ] +# We cannot tes this command because its output depends +# on --default-character-set incompiled into "mysqldump" program. +# If the future we can move this command into a separate test with +# checking that "mysqldump" is compiled with "latin1" +# /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -535,6 +579,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# WL #2319: Exclude Tables from dump +# CREATE TABLE t1 (a int); CREATE TABLE t2 (a int); INSERT INTO t1 VALUES (1),(2),(3); @@ -572,6 +619,9 @@ UNLOCK TABLES; DROP TABLE t1; DROP TABLE t2; +# +# Bug #8830 +# CREATE TABLE t1 (`b` blob); INSERT INTO `t1` VALUES (0x602010000280100005E71A); @@ -606,6 +656,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# Test for --insert-ignore +# CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t1 VALUES (4),(5),(6); @@ -670,6 +723,10 @@ INSERT DELAYED IGNORE INTO `t1` VALUES (1),(2),(3),(4),(5),(6); /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# Bug #10286: mysqldump -c crashes on table that has many fields with long +# names +# create table t1 ( F_c4ca4238a0b923820dcc509a6f75849b int, F_c81e728d9d4c2f636f067f89cc14862c int, @@ -1363,6 +1420,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; drop table t1; +# +# Test for --add-drop-database +# CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1),(2),(3); @@ -1403,6 +1463,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# Bug #9558 mysqldump --no-data db t1 t2 format still dumps data +# CREATE DATABASE mysqldump_test_db; USE mysqldump_test_db; CREATE TABLE t1 ( a INT ); @@ -1491,6 +1554,11 @@ CREATE TABLE `t2` ( </mysqldump> DROP TABLE t1, t2; DROP DATABASE mysqldump_test_db; +# +# Testing with tables and databases that don't exists +# or contains illegal characters +# (Bug #9358 mysqldump crashes if tablename starts with \) +# create database mysqldump_test_db; use mysqldump_test_db; create table t1(a varchar(30) primary key, b int not null); @@ -1529,6 +1597,9 @@ mysqldump: Got error: 1102: Incorrect database name 'mysqld\ump_test_db' when se drop table t1, t2, t3; drop database mysqldump_test_db; use test; +# +# Bug #9657 mysqldump xml ( -x ) does not format NULL fields correctly +# create table t1 (a int(10)); create table t2 (pk int primary key auto_increment, a int(10), b varchar(30), c datetime, d blob, e text); @@ -1585,6 +1656,9 @@ insert into t2 (a, b) values (NULL, NULL),(10, NULL),(NULL, "twenty"),(30, "thir </database> </mysqldump> drop table t1, t2; +# +# BUG #12123 +# create table t1 (a text character set utf8, b text character set latin1); insert t1 values (0x4F736E616272C3BC636B, 0x4BF66C6E); select * from t1; @@ -1595,7 +1669,13 @@ select * from t1; a b Osnabrck Kln drop table t1; +# +# BUG#15328 Segmentation fault occured if my.cnf is invalid for escape sequence +# --fields-optionally-enclosed-by=" +# +# BUG #19025 mysqldump doesn't correctly dump "auto_increment = [int]" +# create table `t1` ( t1_name varchar(255) default null, t1_id int(10) unsigned not null auto_increment, @@ -1633,6 +1713,9 @@ t1 CREATE TABLE `t1` ( KEY `t1_name` (`t1_name`) ) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 drop table `t1`; +# +# Bug #18536: wrong table order +# create table t1(a int); create table t2(a int); create table t3(a int); @@ -1670,6 +1753,9 @@ CREATE TABLE `t2` ( /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; drop table t1, t2, t3; +# +# Bug #21288: mysqldump segmentation fault when using --where +# create table t1 (a int); mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` WHERE xx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx': 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 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' at line 1 (1064) mysqldump: Got error: 1064: 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 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' at line 1 when retrieving data from server @@ -1701,6 +1787,9 @@ CREATE TABLE `t1` ( drop table t1; End of 4.1 tests +# +# Bug #10213 mysqldump crashes when dumping VIEWs(on MacOS X) +# create database db1; use db1; CREATE TABLE t2 ( @@ -1760,6 +1849,9 @@ drop table t2; drop view v2; drop database db1; use test; +# +# Bug 10713 mysqldump includes database in create view and referenced tables +# create database db2; use db2; create table t1 (a int); @@ -1833,6 +1925,9 @@ DROP TABLE IF EXISTS `v1`; drop view v1; drop table t1; +# +# Bug #10213 mysqldump crashes when dumping VIEWs(on MacOS X) +# create database mysqldump_test_db; use mysqldump_test_db; CREATE TABLE t2 ( @@ -1892,6 +1987,9 @@ drop table t2; drop view v2; drop database mysqldump_test_db; use test; +# +# Bug #9756 +# CREATE TABLE t1 (a char(10)); INSERT INTO t1 VALUES ('\''); @@ -1926,6 +2024,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# Bug #10927 mysqldump: Can't reload dump with view that consist of other view +# create table t1(a int, b int, c varchar(30)); insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three"); create view v3 as @@ -2003,6 +2104,9 @@ DROP TABLE IF EXISTS `v3`; drop view v1, v2, v3; drop table t1; +# +# Test for dumping triggers +# CREATE TABLE t1 (a int, b bigint default NULL); CREATE TABLE t2 (a int); create trigger trg1 before insert on t1 for each row @@ -2201,8 +2305,14 @@ set @fired:= "No"; end if; end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost DROP TABLE t1, t2; +# +# Bugs #9136, #12917: problems with --defaults-extra-file option +# --port=1234 --port=1234 +# +# Test of fix to BUG 12597 +# DROP TABLE IF EXISTS `test1`; Warnings: Note 1051 Unknown table 'test1' @@ -2234,6 +2344,9 @@ a2 DROP TRIGGER testref; DROP TABLE test1; DROP TABLE test2; +# +# BUG#9056 - mysqldump does not dump routines +# DROP TABLE IF EXISTS t1; DROP FUNCTION IF EXISTS bug9056_func1; DROP FUNCTION IF EXISTS bug9056_func2; @@ -2330,6 +2443,9 @@ DROP PROCEDURE bug9056_proc1; DROP PROCEDURE bug9056_proc2; DROP PROCEDURE `a'b`; drop table t1; +# +# BUG# 13052 - mysqldump timestamp reloads broken +# drop table if exists t1; create table t1 (`d` timestamp, unique (`d`)); set time_zone='+00:00'; @@ -2416,6 +2532,9 @@ UNLOCK TABLES; drop table t1; set global time_zone=default; set time_zone=default; +# +# Test of fix to BUG 13146 - ansi quotes break loading of triggers +# DROP TABLE IF EXISTS `t1 test`; DROP TABLE IF EXISTS `t2 test`; CREATE TABLE `t1 test` ( @@ -2479,6 +2598,9 @@ UNLOCK TABLES; DROP TRIGGER `test trig`; DROP TABLE `t1 test`; DROP TABLE `t2 test`; +# +# BUG# 12838 mysqldump -x with views exits with error +# drop table if exists t1; create table t1 (a int, b varchar(32), c varchar(32)); insert into t1 values (1, 'first value', 'xxxx'); @@ -2571,6 +2693,10 @@ drop view v2; drop view v0; drop view v1; drop table t1; +# +# BUG#14554 - mysqldump does not separate words "ROW" and "BEGIN" +# for tables with trigger created in the IGNORE_SPACE sql mode. +# SET @old_sql_mode = @@SQL_MODE; SET SQL_MODE = IGNORE_SPACE; CREATE TABLE t1 (a INT); @@ -2626,6 +2752,9 @@ DELIMITER ; DROP TRIGGER tr1; DROP TABLE t1; +# +# Bug #13318: Bad result with empty field and --hex-blob +# create table t1 (a binary(1), b blob); insert into t1 values ('',''); @@ -2693,6 +2822,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; drop table t1; +# +# Bug 14871 Invalid view dump output +# create table t1 (a int); insert into t1 values (289), (298), (234), (456), (789); create definer = CURRENT_USER view v1 as select * from t1; @@ -2719,6 +2851,9 @@ a 789 drop table t1; drop view v1, v2, v3, v4, v5; +# +# Bug #16878 dump of trigger +# create table t1 (a int, created datetime); create table t2 (b int, created datetime); create trigger tr1 before insert on t1 for each row set @@ -2741,6 +2876,9 @@ end AFTER # root@localhost drop trigger tr1; drop trigger tr2; drop table t1, t2; +# +# Bug#18462 mysqldump does not dump view structures correctly +# create table t (qty int, price int); insert into t values(3, 50); insert into t values(5, 51); @@ -2760,6 +2898,10 @@ mysqldump { drop view v1; drop view v2; drop table t; +# +# Bug#14857 Reading dump files with single statement stored routines fails. +# fixed by patch for bug#16878 +# /*!50003 CREATE FUNCTION `f`() RETURNS bigint(20) return 42 */| /*!50003 CREATE PROCEDURE `p`() @@ -2774,6 +2916,9 @@ p CREATE DEFINER=`root`@`localhost` PROCEDURE `p`() select 42 drop function f; drop procedure p; +# +# Bug #17371 Unable to dump a schema with invalid views +# create table t1 ( id serial ); create view v1 as select * from t1; drop table t1; @@ -2783,6 +2928,9 @@ mysqldump { } mysqldump drop view v1; +# BUG#17201 Spurious 'DROP DATABASE' in output, +# also confusion between tables and views. +# Example code from Markus Popp create database mysqldump_test_db; use mysqldump_test_db; create table t1 (id int); @@ -2843,6 +2991,9 @@ USE `mysqldump_test_db`; drop view v1; drop table t1; drop database mysqldump_test_db; +# +# Bug21014 Segmentation fault of mysqldump on view +# create database mysqldump_tables; use mysqldump_tables; create table basetable ( id serial, tag varchar(64) ); @@ -2876,6 +3027,9 @@ drop view nasishnasifu; drop database mysqldump_views; drop table mysqldump_tables.basetable; drop database mysqldump_tables; +# +# Bug20221 Dumping of multiple databases containing view(s) yields maleformed dumps +# create database mysqldump_dba; use mysqldump_dba; create table t1 (f1 int, f2 int); @@ -2908,6 +3062,9 @@ drop view v1; drop table t1; drop database mysqldump_dbb; use test; +# +# Bug#21215 mysqldump creating incomplete backups without warning +# create user mysqltest_1@localhost; create table t1(a int, b varchar(34)); reset master; @@ -2924,19 +3081,120 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1; drop table t1; drop user mysqltest_1@localhost; +# +# Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the +# information_schema database. +# +# Bug #21424 mysqldump failing to export/import views +# create database mysqldump_myDB; use mysqldump_myDB; create user myDB_User; -grant create view, select on mysqldump_myDB.* to myDB_User@localhost; +grant create, create view, select, insert on mysqldump_myDB.* to myDB_User@localhost; create table t1 (c1 int); insert into t1 values (3); use mysqldump_myDB; +create table u1 (f1 int); +insert into u1 values (4); create view v1 (c1) as select * from t1; use mysqldump_myDB; drop view v1; drop table t1; +drop table u1; +revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; +drop user myDB_User; +drop database mysqldump_myDB; +flush privileges; +# Bug #21424 continues from here. +# Restore. Flush Privileges test ends. +# +use mysqldump_myDB; +select * from mysqldump_myDB.v1; +c1 +3 +select * from mysqldump_myDB.u1; +f1 +4 +use mysqldump_myDB; +drop view v1; +drop table t1; +drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; drop user myDB_User; drop database mysqldump_myDB; use test; -End of 5.0 tests +# +# BUG#13926: --order-by-primary fails if PKEY contains quote character +# +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( +`a b` INT, +`c"d` INT, +`e``f` INT, +PRIMARY KEY (`a b`, `c"d`, `e``f`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +insert into t1 values (0815, 4711, 2006); +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!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,ANSI' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS "t1"; +CREATE TABLE "t1" ( + "a b" int(11) NOT NULL default '0', + "c""d" int(11) NOT NULL default '0', + "e`f" int(11) NOT NULL default '0', + PRIMARY KEY ("a b","c""d","e`f") +); + +LOCK TABLES "t1" WRITE; +/*!40000 ALTER TABLE "t1" DISABLE KEYS */; +INSERT INTO "t1" VALUES (815,4711,2006); +/*!40000 ALTER TABLE "t1" ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!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' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a b` int(11) NOT NULL default '0', + `c"d` int(11) NOT NULL default '0', + `e``f` int(11) NOT NULL default '0', + PRIMARY KEY (`a b`,`c"d`,`e``f`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +INSERT INTO `t1` VALUES (815,4711,2006); +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!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 */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +DROP TABLE `t1`; +# +# End of 5.0 tests +# diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 28c1dc59540..7cdd5e1b152 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -611,7 +611,7 @@ C NULL EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a int(11) NOT NULL); diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index a735b52a26f..5224280e134 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -947,24 +947,24 @@ COUNT(*) Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: '20050328 invalid' -Warning 1292 Truncated incorrect DOUBLE value: '20050328 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 3edf56496fe..add95613d62 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -750,13 +750,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index EXPLAIN SELECT a,b FROM t1 WHERE a < 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index EXPLAIN SELECT a,b FROM v1 WHERE a < 2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT a,b FROM t1 WHERE a < 2 and b=3; a b 1 3 @@ -799,13 +799,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from v1 where a in (3,4) and b in (1,2,3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from t1 where a between 3 and 4 and b between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from v1 where a between 3 and 4 and b between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index drop view v1; drop table t1; create table t3 (a int); diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 1762587415d..5b5f8b7b954 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -181,3 +181,128 @@ SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NUL select row(NULL,1)=(2,0); row(NULL,1)=(2,0) 0 +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3); +EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 Using index +EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 Using index +SELECT * FROM t1 WHERE a=3 and b=2; +a b +3 2 +SELECT * FROM t1 WHERE (a,b)=(3,2); +a b +3 2 +CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); +INSERT INTO t2 VALUES +(1,1,2), (3,1,3), (1,2,2), (4,4,2), +(1,1,1), (3,1,1), (1,2,1); +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 8 test.t1.a,test.t1.b 1 Using index +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 8 test.t1.a,test.t1.b 1 Using index +SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b; +a b a b c +1 1 1 1 1 +1 1 1 1 2 +1 2 1 2 1 +1 2 1 2 2 +3 1 3 1 1 +3 1 3 1 3 +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); +a b a b c +1 1 1 1 1 +1 1 1 1 2 +1 2 1 2 1 +1 2 1 2 2 +3 1 3 1 1 +3 1 3 1 3 +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 5 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 5 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b; +a b a b c +1 1 1 1 2 +1 1 3 1 3 +1 2 1 2 2 +1 1 1 1 1 +1 1 3 1 1 +1 2 1 2 1 +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); +a b a b c +1 2 1 1 1 +1 2 1 1 2 +1 2 1 2 1 +1 2 1 2 2 +3 2 3 1 1 +3 2 3 1 3 +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); +a b a b c +1 2 1 1 1 +1 2 1 1 2 +3 2 3 1 1 +3 2 3 1 3 +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 index NULL PRIMARY 12 NULL 7 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t1`.`a` - 1) = (`test`.`t2`.`a` - 1)) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) +SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); +a b a b c +1 2 1 1 2 +3 2 3 1 3 +1 2 1 1 1 +3 2 3 1 1 +EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 8 const,const 1 Using index +EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 8 const,const 1 Using index +SELECT * FROM t2 WHERE a=3 and b=2; +a b c +SELECT * FROM t2 WHERE (a,b)=(3,2); +a b c +EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.a,const,const 1 Using index +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.a,const,const 1 Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) +SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); +a b a b c +1 1 1 2 1 +1 2 1 2 1 +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.a,const,const 1 Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) +SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); +a b a b c +1 1 1 2 1 +1 2 1 2 1 +DROP TABLE t1,t2; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 3aa82bf1d63..e425cce9508 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -1,9 +1,20 @@ +# +# Setup +# 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; +use test; +drop table if exists t1, t2, t3; +# +# See if queries that use both auto_increment and LAST_INSERT_ID() +# are replicated well +# +# We also check how the foreign_key_check variable is replicated +# create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); insert into t1 values (1),(2),(3); @@ -38,6 +49,9 @@ select * from t2; b c 5 0 6 11 +# +# check if INSERT SELECT in auto_increment is well replicated (bug #490) +# drop table t2; drop table t1; create table t1(a int auto_increment, key(a)); @@ -68,12 +82,19 @@ b c 9 13 drop table t1; drop table t2; +# +# Bug#8412: Error codes reported in binary log for CHARACTER SET, +# FOREIGN_KEY_CHECKS +# SET TIMESTAMP=1000000000; CREATE TABLE t1 ( a INT UNIQUE ); SET FOREIGN_KEY_CHECKS=0; INSERT INTO t1 VALUES (1),(1); ERROR 23000: Duplicate entry '1' for key 1 drop table t1; +# +# Bug#14553: NULL in WHERE resets LAST_INSERT_ID +# create table t1(a int auto_increment, key(a)); create table t2(a int); insert into t1 (a) values (null); @@ -87,6 +108,9 @@ a 1 drop table t1; drop table t2; +# +# End of 4.1 tests +# drop function if exists bug15728; drop function if exists bug15728_insert; drop table if exists t1, t2; @@ -210,3 +234,6 @@ n b 2 100 3 350 drop table t1; + +# End of 5.0 tests + diff --git a/mysql-test/r/rpl_max_relay_size.result b/mysql-test/r/rpl_max_relay_size.result index fbe3b89828a..1fa49db6013 100644 --- a/mysql-test/r/rpl_max_relay_size.result +++ b/mysql-test/r/rpl_max_relay_size.result @@ -5,9 +5,15 @@ reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; stop slave; +# +# Generate a big enough master's binlog to cause relay log rotations +# create table t1 (a int); drop table t1; reset slave; +# +# Test 1 +# set global max_binlog_size=8192; set global max_relay_log_size=8192-1; select @@global.max_relay_log_size; @@ -15,47 +21,251 @@ select @@global.max_relay_log_size; 4096 start slave; 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 MASTER_PORT 1 master-bin.000001 72952 # # master-bin.000001 Yes Yes 0 0 72952 # None 0 No # +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 72952 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 72952 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +# +# Test 2 +# stop slave; reset slave; set global max_relay_log_size=(5*4096); select @@global.max_relay_log_size; -@@global.max_relay_log_size -20480 +@@global.max_relay_log_size 20480 start slave; 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 MASTER_PORT 1 master-bin.000001 72952 # # master-bin.000001 Yes Yes 0 0 72952 # None 0 No # +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 72952 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 72952 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +# +# Test 3: max_relay_log_size = 0 +# stop slave; reset slave; set global max_relay_log_size=0; select @@global.max_relay_log_size; -@@global.max_relay_log_size -0 +@@global.max_relay_log_size 0 start slave; 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 MASTER_PORT 1 master-bin.000001 72952 # # master-bin.000001 Yes Yes 0 0 72952 # None 0 No # +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 72952 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 72952 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +# +# Test 4: Tests below are mainly to ensure that we have not coded with wrong assumptions +# stop slave; reset slave; flush logs; 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 MASTER_PORT 1 4 # # No No 0 0 0 # None 0 No # +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File +Read_Master_Log_Pos 4 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File +Slave_IO_Running No +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 0 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +# +# Test 5 +# reset slave; start slave; flush logs; create table t1 (a int); 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 MASTER_PORT 1 master-bin.000001 73038 # # master-bin.000001 Yes Yes 0 0 73038 # None 0 No # +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 73038 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 73038 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +# +# Test 6: one more rotation, to be sure Relay_Log_Space is correctly updated +# flush logs; drop table t1; 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 MASTER_PORT 1 master-bin.000001 73114 # # master-bin.000001 Yes Yes 0 0 73114 # None 0 No # +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 73114 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 73114 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # flush logs; show master status; -File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000002 98 +File master-bin.000002 +Position 98 +Binlog_Do_DB +Binlog_Ignore_DB +# +# End of 4.1 tests +# diff --git a/mysql-test/r/rpl_view.result b/mysql-test/r/rpl_view.result index 5a101defe38..601f3d9cd14 100644 --- a/mysql-test/r/rpl_view.result +++ b/mysql-test/r/rpl_view.result @@ -91,3 +91,19 @@ c ---> Cleaning up... DROP VIEW v1; DROP TABLE t1; +create table t1(a int, b int); +insert into t1 values (1, 1), (1, 2), (1, 3); +create view v1(a, b) as select a, sum(b) from t1 group by a; +explain v1; +Field Type Null Key Default Extra +a int(11) YES NULL +b decimal(32,0) YES NULL +show create table v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,sum(`t1`.`b`) AS `b` from `t1` group by `t1`.`a` +select * from v1; +a b +1 6 +drop table t1; +drop view v1; +End of 5.0 tests diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index b773bee1ac8..31aa96ab05d 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5409,4 +5409,66 @@ INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_да call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) drop database това_е_дълго_име_за_база_данни_нали| +CREATE TABLE t3 ( +Member_ID varchar(15) NOT NULL, +PRIMARY KEY (Member_ID) +)| +CREATE TABLE t4 ( +ID int(10) unsigned NOT NULL auto_increment, +Member_ID varchar(15) NOT NULL default '', +Action varchar(12) NOT NULL, +Action_Date datetime NOT NULL, +Track varchar(15) default NULL, +User varchar(12) default NULL, +Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update +CURRENT_TIMESTAMP, +PRIMARY KEY (ID), +KEY Action (Action), +KEY Action_Date (Action_Date) +)| +INSERT INTO t3(Member_ID) VALUES +('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')| +INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES +('111111', 'Disenrolled', '2006-03-01', 'CAD' ), +('111111', 'Enrolled', '2006-03-01', 'CAD' ), +('111111', 'Disenrolled', '2006-07-03', 'CAD' ), +('222222', 'Enrolled', '2006-03-07', 'CAD' ), +('222222', 'Enrolled', '2006-03-07', 'CHF' ), +('222222', 'Disenrolled', '2006-08-02', 'CHF' ), +('333333', 'Enrolled', '2006-03-01', 'CAD' ), +('333333', 'Disenrolled', '2006-03-01', 'CAD' ), +('444444', 'Enrolled', '2006-03-01', 'CAD' ), +('555555', 'Disenrolled', '2006-03-01', 'CAD' ), +('555555', 'Enrolled', '2006-07-21', 'CAD' ), +('555555', 'Disenrolled', '2006-03-01', 'CHF' ), +('666666', 'Enrolled', '2006-02-09', 'CAD' ), +('666666', 'Enrolled', '2006-05-12', 'CHF' ), +('666666', 'Disenrolled', '2006-06-01', 'CAD' )| +DROP FUNCTION IF EXISTS bug21493| +CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45) +BEGIN +DECLARE tracks VARCHAR(45); +SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4 +WHERE Member_ID=paramMember AND Action='Enrolled' AND +(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4 +WHERE Member_ID=paramMember GROUP BY Track); +RETURN tracks; +END| +SELECT bug21493('111111')| +bug21493('111111') +NULL +SELECT bug21493('222222')| +bug21493('222222') +CAD +SELECT bug21493(Member_ID) FROM t3| +bug21493(Member_ID) +NULL +CAD +CAD +CAD +CAD +CHF +DROP FUNCTION bug21493| +DROP TABLE t3,t4| +End of 5.0 tests drop table t1,t2; diff --git a/mysql-test/r/strict_autoinc_1myisam.result b/mysql-test/r/strict_autoinc_1myisam.result new file mode 100644 index 00000000000..5d3c2698cda --- /dev/null +++ b/mysql-test/r/strict_autoinc_1myisam.result @@ -0,0 +1,27 @@ +set @org_mode=@@sql_mode; +create table t1 +( +`a` tinyint(4) NOT NULL auto_increment, +primary key (`a`) +) engine = 'MYISAM' ; +set @@sql_mode='strict_all_tables'; +insert into t1 values(1000); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t1 values(null); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set @@sql_mode=@org_mode; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select * from t1; +a +127 +drop table t1; diff --git a/mysql-test/r/strict_autoinc_2innodb.result b/mysql-test/r/strict_autoinc_2innodb.result new file mode 100644 index 00000000000..f1936ff4de3 --- /dev/null +++ b/mysql-test/r/strict_autoinc_2innodb.result @@ -0,0 +1,27 @@ +set @org_mode=@@sql_mode; +create table t1 +( +`a` tinyint(4) NOT NULL auto_increment, +primary key (`a`) +) engine = 'InnoDB' ; +set @@sql_mode='strict_all_tables'; +insert into t1 values(1000); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t1 values(null); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set @@sql_mode=@org_mode; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select * from t1; +a +127 +drop table t1; diff --git a/mysql-test/r/strict_autoinc_3heap.result b/mysql-test/r/strict_autoinc_3heap.result new file mode 100644 index 00000000000..aa0be270ac5 --- /dev/null +++ b/mysql-test/r/strict_autoinc_3heap.result @@ -0,0 +1,27 @@ +set @org_mode=@@sql_mode; +create table t1 +( +`a` tinyint(4) NOT NULL auto_increment, +primary key (`a`) +) engine = 'MEMORY' ; +set @@sql_mode='strict_all_tables'; +insert into t1 values(1000); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t1 values(null); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set @@sql_mode=@org_mode; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select * from t1; +a +127 +drop table t1; diff --git a/mysql-test/r/strict_autoinc_4bdb.result b/mysql-test/r/strict_autoinc_4bdb.result new file mode 100644 index 00000000000..73683b645e2 --- /dev/null +++ b/mysql-test/r/strict_autoinc_4bdb.result @@ -0,0 +1,27 @@ +set @org_mode=@@sql_mode; +create table t1 +( +`a` tinyint(4) NOT NULL auto_increment, +primary key (`a`) +) engine = 'BDB' ; +set @@sql_mode='strict_all_tables'; +insert into t1 values(1000); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t1 values(null); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set @@sql_mode=@org_mode; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select * from t1; +a +127 +drop table t1; diff --git a/mysql-test/r/strict_autoinc_5ndb.result b/mysql-test/r/strict_autoinc_5ndb.result new file mode 100644 index 00000000000..d0d62d05b32 --- /dev/null +++ b/mysql-test/r/strict_autoinc_5ndb.result @@ -0,0 +1,27 @@ +set @org_mode=@@sql_mode; +create table t1 +( +`a` tinyint(4) NOT NULL auto_increment, +primary key (`a`) +) engine = 'NDB' ; +set @@sql_mode='strict_all_tables'; +insert into t1 values(1000); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t1 values(null); +ERROR 22003: Out of range value adjusted for column 'a' at row 1 +select count(*) from t1; +count(*) +0 +set @@sql_mode=@org_mode; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select * from t1; +a +127 +drop table t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4ee6d3088a5..c6a31856a24 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1011,7 +1011,7 @@ INSERT INTO t1 VALUES (1); UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); select * from t1; i -1 +2 drop table t1; CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; @@ -1203,7 +1203,7 @@ UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); ERROR 42S22: Unknown column 't.i' in 'field list' select * from t1; i -1 +3 drop table t1; CREATE TABLE t1 ( id int(11) default NULL @@ -2937,6 +2937,35 @@ retailerID statusID changed 0048 1 2006-01-06 12:37:50 0059 1 2006-01-06 12:37:50 drop table t1; +create table t1(a int, primary key (a)); +insert into t1 values (10); +create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); +insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +a a b +10 3 35989 +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +a a b +10 1 359 +drop table t1,t2; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); @@ -3368,3 +3397,28 @@ ORDER BY t1.t DESC LIMIT 1); i1 i2 t i1 i2 t 24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40 DROP TABLE t1, t2; +CREATE TABLE t1 (i INT); +(SELECT i FROM t1) UNION (SELECT i FROM t1); +i +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS +( +(SELECT i FROM t1) UNION +(SELECT i FROM t1) +); +i +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); +i +explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) +from t1; +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 'union (select t12.i from t1 t12)) +from t1' at line 1 +explain select * from t1 where not exists +((select t11.i from t1 t11) union (select t12.i from t1 t12)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +4 UNION t12 system NULL NULL NULL NULL 0 const row not found +NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 2281ed44e3f..f0ac00cedfa 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -572,4 +572,34 @@ def test t1 t1 a a 16 7 1 Y 0 0 63 a ` drop table t1; +create table bug15583(b BIT(8), n INT); +insert into bug15583 values(128, 128); +insert into bug15583 values(null, null); +insert into bug15583 values(0, 0); +insert into bug15583 values(255, 255); +select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583; +hex(b) bin(b) oct(b) hex(n) bin(n) oct(n) +80 10000000 200 80 10000000 200 +NULL NULL NULL NULL NULL NULL +0 0 0 0 0 0 +FF 11111111 377 FF 11111111 377 +select hex(b)=hex(n) as should_be_onetrue, bin(b)=bin(n) as should_be_onetrue, oct(b)=oct(n) as should_be_onetrue from bug15583; +should_be_onetrue should_be_onetrue should_be_onetrue +1 1 1 +NULL NULL NULL +1 1 1 +1 1 1 +select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583; +hex(b + 0) bin(b + 0) oct(b + 0) hex(n) bin(n) oct(n) +80 10000000 200 80 10000000 200 +NULL NULL NULL NULL NULL NULL +0 0 0 0 0 0 +FF 11111111 377 FF 11111111 377 +select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583; +conv(b, 10, 2) conv(b + 0, 10, 2) +10000000 10000000 +NULL NULL +0 0 +11111111 11111111 +drop table bug15583; End of 5.0 tests diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index e88eebffb55..ed15293bb45 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -27,12 +27,12 @@ INSERT INTO t1 VALUES ( "2000-1-2" ); INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4"; +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); datum 2000-01-02 2000-01-03 2000-01-04 -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; datum DROP TABLE t1; CREATE TABLE t1 ( @@ -104,3 +104,9 @@ SELECT * FROM t1; y 0000 DROP TABLE t1; +create table t1(start_date date, end_date date); +insert into t1 values ('2000-01-01','2000-01-02'); +select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date; +1 +1 +drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 42a3874db08..12463658bf9 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1365,7 +1365,7 @@ drop table t1, t2; (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)); avg(1) -NULL +1.0000 select _utf8'12' union select _latin1'12345'; 12 12 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index f70547cd4a8..9c05aba168c 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -49,7 +49,7 @@ select v1.b from v1; ERROR 42S22: Unknown column 'v1.b' in 'field list' explain extended select c from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1` create algorithm=temptable view v2 (c) as select b+1 from t1; @@ -83,7 +83,7 @@ c 12 explain extended select c from v3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1` create algorithm=temptable view v4 (c) as select c+1 from v2; @@ -376,7 +376,7 @@ c 30 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where Warnings: Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3) update v1 set c=c+1; @@ -1391,9 +1391,9 @@ a a b 4 NULL NULL explain extended select * from t3 left join v3 on (t3.a = v3.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 create view v1 (a) as select a from t1; @@ -1406,9 +1406,9 @@ a a b 4 NULL NULL explain extended select * from t3 left join v4 on (t3.a = v4.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; @@ -2321,12 +2321,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v1 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index -1 PRIMARY t2 ref a a 10 const,test.t1.b 2 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where DROP VIEW v1,v2; DROP TABLE t1,t2,t3; create table t1 (f1 int); @@ -2409,7 +2409,7 @@ insert into t1 values (1),(2); create view v1 as select * from t1; explain select id from v1 order by id; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index drop view v1; drop table t1; create table t1(f1 int, f2 int); @@ -2480,7 +2480,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT MAX(a) FROM v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away SELECT MIN(a) FROM t1; MIN(a) 0 @@ -2492,7 +2492,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT MIN(a) FROM v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (x varchar(10)); @@ -2586,13 +2586,13 @@ INSERT INTO t1 VALUES (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; +SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); id td 2 2005-01-02 3 2005-01-02 4 2005-01-03 5 2005-01-04 -SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; +SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); id td 2 2005-01-02 3 2005-01-02 @@ -2916,4 +2916,23 @@ DROP FUNCTION f1; DROP FUNCTION f2; DROP VIEW v1, v2; DROP TABLE t1; +CREATE TABLE t1 (s1 int); +CREATE VIEW v1 AS SELECT * FROM t1; +EXPLAIN SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +INSERT INTO t1 VALUES (1), (3), (2); +EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 7f63d790fb8..35e7afc0a7b 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -103,7 +103,7 @@ ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for tabl grant select on mysqltest.t1 to mysqltest_1@localhost; explain select c from mysqltest.v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v1; ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' explain select c from mysqltest.v2; @@ -123,7 +123,7 @@ ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for tabl grant show view on mysqltest.* to mysqltest_1@localhost; explain select c from mysqltest.v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` @@ -136,7 +136,7 @@ View Create View v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` explain select c from mysqltest.v3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v3; View Create View v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index fbe1ee2b8c8..0e9e141f6d8 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -133,8 +133,6 @@ select min(a), min(case when 1=1 then a else NULL end), from t1 where b=3 group by b; drop table t1; -# End of 4.1 tests - # # Tests for bug #9939: conversion of the arguments for COALESCE and IFNULL @@ -154,3 +152,4 @@ SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; DROP TABLE t1,t2; +# End of 4.1 tests diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 07edbf206fe..db73782fdbf 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -674,4 +674,10 @@ create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb insert into t1 values('aaa'); drop table t1; +# +# Bug#21772: can not name a column 'upgrade' when create a table +# +create table t1 (upgrade int); +drop table t1; + # End of 5.0 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index b6137d5f084..39928f18f2e 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1069,23 +1069,6 @@ explain select a from t1 group by a; select a from t1 group by a; drop table t1; -# -# Bug#20393: User name truncation in mysql client -# Bug#21432: Database/Table name limited to 64 bytes, not chars, problems with multi-byte -# -set names utf8; -#create user юзер_юзер@localhost; -grant select on test.* to юзер_юзер@localhost; ---exec $MYSQL --default-character-set=utf8 --user=юзер_юзер -e "select user()" -revoke all on test.* from юзер_юзер@localhost; -drop user юзер_юзер@localhost; - -create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; -use имя_базы_в_кодировке_утф8_длиной_больше_чем_45; -select database(); -drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; -use test; - # End of 4.1 tests # diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 677ffaa2860..865e1746fd3 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -153,6 +153,16 @@ DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1; SELECT * FROM t1; DROP TABLE t1; +# +# Bug #21392: multi-table delete with alias table name fails with +# 1003: Incorrect table name +# + +create table t1 (a int); +delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; +delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index c2dd77e662a..079d107fad8 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -566,6 +566,19 @@ INSERT INTO t1 VALUES SELECT MAX(b) FROM t1; EXPLAIN SELECT MAX(b) FROM t1; DROP TABLE t1; +# +# Bug #16792 query with subselect, join, and group not returning proper values +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(2,3); + +SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; +SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; +# an attempt to test all aggregate function with no table. +SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12), + COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2), + GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2); +DROP TABLE t1; # End of 4.1 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 8753db0ebe1..45415882ac7 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -753,4 +753,31 @@ select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)); select conv("18383815659218730760",10,10) + 0; select "18383815659218730760" + 0; +# +# Bug #21698: substitution of a string field for a constant under a function +# + +CREATE TABLE t1 (code varchar(10)); +INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13'); + +SELECT ASCII(code), code FROM t1 WHERE code='A12'; +SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65; + +INSERT INTO t1 VALUES ('a12 '), ('A12 '); + +SELECT LENGTH(code), code FROM t1 WHERE code='A12'; +SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5; + +ALTER TABLE t1 ADD INDEX (code); +CREATE TABLE t2 (id varchar(10) PRIMARY KEY); +INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14'); + +SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id + WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON code=id + WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00'); + +DROP TABLE t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index f2ff47704c9..0ea89cd0913 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -108,9 +108,6 @@ select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3; select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3; -# End of 4.1 tests - -# # Bug#6726: NOT BETWEEN parse failure # create table t1 (a int, b int); @@ -127,3 +124,5 @@ SELECT GREATEST(1,NULL) FROM DUAL; SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL; SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL; SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL; + +# End of 4.1 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index be09b00ad46..7303be31e03 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -419,20 +419,20 @@ drop table t1; # # Bug#16377 result of DATE/TIME functions were compared as strings which # can lead to a wrong result. -# +# Now wrong dates should be compared only with CAST() create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); -select f1 from t1 where f1 between "2006-1-1" and 20060101; -select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; -select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; -select f2 from t1 where f2 between "12:1:2" and "12:2:2"; -select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; -select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; -select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; -select f1 from t1 where "2006-1-1" between f1 and f3; -select f1 from t1 where "2006-1-1" between date(f1) and date(f3); -select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date); +select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); +select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); +select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time); +select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time); +select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); +select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); +select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; +select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); +select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); drop table t1; diff --git a/mysql-test/t/index_merge.test b/mysql-test/t/index_merge.test index 3da5711bf7a..30eb0b40fca 100644 --- a/mysql-test/t/index_merge.test +++ b/mysql-test/t/index_merge.test @@ -383,3 +383,35 @@ explain select * from t3 where a=1 and b=1; drop table t3; drop table t0, t1, t2; + +# +# BUG#20256 - LOCK WRITE - MyISAM +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1); +CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); +INSERT INTO t2(a,b) VALUES +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), +(1,2); +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t2(a,b) VALUES(1,2); +SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1; +UNLOCK TABLES; +DROP TABLE t1, t2; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index b6b94d07e87..6f86ed897ac 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -249,6 +249,24 @@ INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1); DROP TABLE IF EXISTS t1,t2,t3; # +# Bug #21774: Column count doesn't match value count at row x +# +CREATE DATABASE bug21774_1; +CREATE DATABASE bug21774_2; + +CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); +CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); +CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255)); + +INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1; + +use bug21774_1; +INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; + +DROP DATABASE bug21774_1; +DROP DATABASE bug21774_2; + +# # Bug #20989: View '(null).(null)' references invalid table(s)... on # SQL SECURITY INVOKER # diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index eda768be1bc..b3813864464 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -115,4 +115,27 @@ INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; +# +# Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES +# + + # End of 4.1 tests +CREATE TABLE t1 +( + a BIGINT UNSIGNED, + b BIGINT UNSIGNED, + PRIMARY KEY (a) +); + +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 7dee5ebdf41..5ec4edea8c7 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -489,6 +489,42 @@ select c1 from t1 order by c1 limit 1; drop table t1; # +# Bug #14400 Join could miss concurrently inserted row +# +# Partial key. +create table t1 (a int not null, primary key(a)); +create table t2 (a int not null, b int not null, primary key(a,b)); +insert into t1 values (1),(2),(3),(4),(5),(6); +insert into t2 values (1,1),(2,1); +lock tables t1 read local, t2 read local; +select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; +connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +insert into t2 values(2,0); +disconnect root; +connection default; +select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; +drop table t1,t2; +# +# Full key. +CREATE TABLE t1 (c1 varchar(250) NOT NULL); +CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)); +INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003'); +INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004'); +LOCK TABLES t1 READ LOCAL, t2 READ LOCAL; +SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 + WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; +connect (con1,localhost,root,,); +connection con1; +INSERT INTO t2 VALUES ('test000001'), ('test000005'); +disconnect con1; +connection default; +SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 + WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; +DROP TABLE t1,t2; + +# End of 4.0 tests + +# # Test RTREE index # --error 1235, 1289 diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index cf4e6f4047c..9e3eabf474b 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -70,14 +70,21 @@ drop table t1; # # "DESCRIBE" commands may return strange NULLness flags. # +--exec $MYSQL --default-character-set utf8 test -e "create table t1 (i int, j int not null, k int); insert into t1 values (null, 1, null); select * from t1; describe t1; drop table t1;" --exec $MYSQL -t --default-character-set utf8 test -e "create table t1 (i int, j int not null, k int); insert into t1 values (null, 1, null); select * from t1; describe t1; drop table t1;" # # Bug#19564: mysql displays NULL instead of space # +--exec $MYSQL test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;" --exec $MYSQL -t test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;" # +# Bug#21618: NULL shown as empty string in client +# +--exec $MYSQL test -e "select unhex('zz');" +--exec $MYSQL -t test -e "select unhex('zz');" + # Bug#19265 describe command does not work from mysql prompt # diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 9766ee8d9c8..6dfb24c2e75 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -16,9 +16,9 @@ INSERT INTO t1 VALUES (1), (2); --exec $MYSQL_DUMP --skip-create --skip-comments -X test t1 DROP TABLE t1; -# -# Bug #2005 -# +--echo # +--echo # Bug #2005 +--echo # CREATE TABLE t1 (a decimal(64, 20)); INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), @@ -26,9 +26,9 @@ INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), --exec $MYSQL_DUMP --compact test t1 DROP TABLE t1; -# -# Bug #2055 -# +--echo # +--echo # Bug #2055 +--echo # CREATE TABLE t1 (a double); INSERT INTO t1 VALUES ('-9e999999'); @@ -38,9 +38,9 @@ INSERT INTO t1 VALUES ('-9e999999'); --exec $MYSQL_DUMP --compact test t1 DROP TABLE t1; -# -# Bug #3361 mysqldump quotes DECIMAL values inconsistently -# +--echo # +--echo # Bug #3361 mysqldump quotes DECIMAL values inconsistently +--echo # CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT); @@ -69,28 +69,28 @@ INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); --exec $MYSQL_DUMP --skip-create --compact -X test t1 DROP TABLE t1; -# -# Bug #1707 -# +--echo # +--echo # Bug #1707 +--echo # CREATE TABLE t1 (`a"b"` char(2)); INSERT INTO t1 VALUES ("1\""), ("\"2"); --exec $MYSQL_DUMP --compact --skip-create -X test t1 DROP TABLE t1; -# -# Bug #1994 -# Bug #4261 -# +--echo # +--echo # Bug #1994 +--echo # Bug #4261 +--echo # CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'), (NULL); --exec $MYSQL_DUMP --skip-comments --skip-extended-insert test t1 DROP TABLE t1; -# -# Bug #2634 -# +--echo # +--echo # Bug #2634 +--echo # CREATE TABLE t1 (a int) ENGINE=MYISAM; INSERT INTO t1 VALUES (1), (2); @@ -98,17 +98,17 @@ INSERT INTO t1 VALUES (1), (2); --exec $MYSQL_DUMP --skip-comments --compatible=mysql323 test t1 DROP TABLE t1; -# -# Bug #2592 'mysqldump doesn't quote "tricky" names correctly' -# +--echo # +--echo # Bug #2592 'mysqldump doesn't quote "tricky" names correctly' +--echo # create table ```a` (i int); --exec $MYSQL_DUMP --compact test drop table ```a`; -# -# Bug #2591 "mysqldump quotes names inconsistently" -# +--echo # +--echo # Bug #2591 "mysqldump quotes names inconsistently" +--echo # create table t1(a int); --exec $MYSQL_DUMP --comments=0 test @@ -119,9 +119,9 @@ set global sql_mode='ANSI_QUOTES'; set global sql_mode=''; drop table t1; -# -# Bug #2705 'mysqldump --tab extra output' -# +--echo # +--echo # Bug #2705 'mysqldump --tab extra output' +--echo # create table t1(a int); insert into t1 values (1),(2),(3); @@ -135,9 +135,9 @@ insert into t1 values (1),(2),(3); --exec rm $MYSQLTEST_VARDIR/tmp/t1.txt drop table t1; -# -# Bug #6101: create database problem -# +--echo # +--echo # Bug #6101: create database problem +--echo # --exec $MYSQL_DUMP --skip-comments --databases test @@ -145,32 +145,34 @@ create database mysqldump_test_db character set latin2 collate latin2_bin; --exec $MYSQL_DUMP --skip-comments --databases mysqldump_test_db drop database mysqldump_test_db; -# -# Bug #7020 -# Check that we don't dump in UTF8 in compatible mode by default, -# but use the default compiled values, or the values given in -# --default-character-set=xxx. However, we should dump in UTF8 -# if it is explicitely set. +--echo # +--echo # Bug #7020 +--echo # Check that we don't dump in UTF8 in compatible mode by default, +--echo # but use the default compiled values, or the values given in +--echo # --default-character-set=xxx. However, we should dump in UTF8 +--echo # if it is explicitely set. CREATE TABLE t1 (a CHAR(10)); INSERT INTO t1 VALUES (_latin1 ''); --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments test t1 -# -# Bug#8063: make test mysqldump [ fail ] -# We cannot tes this command because its output depends -# on --default-character-set incompiled into "mysqldump" program. -# If the future we can move this command into a separate test with -# checking that "mysqldump" is compiled with "latin1" -# + +--echo # +--echo # Bug#8063: make test mysqldump [ fail ] +--echo # We cannot tes this command because its output depends +--echo # on --default-character-set incompiled into "mysqldump" program. +--echo # If the future we can move this command into a separate test with +--echo # checking that "mysqldump" is compiled with "latin1" +--echo # + #--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --compatible=mysql323 test t1 --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --compatible=mysql323 --default-character-set=cp850 test t1 --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --default-character-set=cp850 --compatible=mysql323 test t1 --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --default-character-set=utf8 --compatible=mysql323 test t1 DROP TABLE t1; -# -# WL #2319: Exclude Tables from dump -# +--echo # +--echo # WL #2319: Exclude Tables from dump +--echo # CREATE TABLE t1 (a int); CREATE TABLE t2 (a int); @@ -180,18 +182,18 @@ INSERT INTO t2 VALUES (4),(5),(6); DROP TABLE t1; DROP TABLE t2; -# -# Bug #8830 -# +--echo # +--echo # Bug #8830 +--echo # CREATE TABLE t1 (`b` blob); INSERT INTO `t1` VALUES (0x602010000280100005E71A); --exec $MYSQL_DUMP --skip-extended-insert --hex-blob test --skip-comments t1 DROP TABLE t1; -# -# Test for --insert-ignore -# +--echo # +--echo # Test for --insert-ignore +--echo # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1),(2),(3); @@ -200,10 +202,10 @@ INSERT INTO t1 VALUES (4),(5),(6); --exec $MYSQL_DUMP --skip-comments --insert-ignore --delayed-insert test t1 DROP TABLE t1; -# -# Bug #10286: mysqldump -c crashes on table that has many fields with long -# names -# +--echo # +--echo # Bug #10286: mysqldump -c crashes on table that has many fields with long +--echo # names +--echo # create table t1 ( F_c4ca4238a0b923820dcc509a6f75849b int, F_c81e728d9d4c2f636f067f89cc14862c int, @@ -539,18 +541,18 @@ insert into t1 (F_8d3bba7425e7c98c50f52ca1b52d3735) values (1); --exec $MYSQL_DUMP --skip-comments -c test drop table t1; -# -# Test for --add-drop-database -# +--echo # +--echo # Test for --add-drop-database +--echo # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1),(2),(3); --exec $MYSQL_DUMP --add-drop-database --skip-comments --databases test DROP TABLE t1; -# -# Bug #9558 mysqldump --no-data db t1 t2 format still dumps data -# +--echo # +--echo # Bug #9558 mysqldump --no-data db t1 t2 format still dumps data +--echo # CREATE DATABASE mysqldump_test_db; USE mysqldump_test_db; @@ -565,11 +567,11 @@ INSERT INTO t2 VALUES (1), (2); DROP TABLE t1, t2; DROP DATABASE mysqldump_test_db; -# -# Testing with tables and databases that don't exists -# or contains illegal characters -# (Bug #9358 mysqldump crashes if tablename starts with \) -# +--echo # +--echo # Testing with tables and databases that don't exists +--echo # or contains illegal characters +--echo # (Bug #9358 mysqldump crashes if tablename starts with \) +--echo # create database mysqldump_test_db; use mysqldump_test_db; create table t1(a varchar(30) primary key, b int not null); @@ -629,9 +631,9 @@ drop database mysqldump_test_db; use test; -# -# Bug #9657 mysqldump xml ( -x ) does not format NULL fields correctly -# +--echo # +--echo # Bug #9657 mysqldump xml ( -x ) does not format NULL fields correctly +--echo # create table t1 (a int(10)); create table t2 (pk int primary key auto_increment, @@ -641,9 +643,10 @@ insert into t2 (a, b) values (NULL, NULL),(10, NULL),(NULL, "twenty"),(30, "thir --exec $MYSQL_DUMP --skip-comments --xml --no-create-info test drop table t1, t2; -# -# BUG #12123 -# +--echo # +--echo # BUG #12123 +--echo # + create table t1 (a text character set utf8, b text character set latin1); insert t1 values (0x4F736E616272C3BC636B, 0x4BF66C6E); select * from t1; @@ -654,15 +657,16 @@ select * from t1; drop table t1; -# -# BUG#15328 Segmentation fault occured if my.cnf is invalid for escape sequence -# +--echo # +--echo # BUG#15328 Segmentation fault occured if my.cnf is invalid for escape sequence +--echo # --exec $MYSQL_MY_PRINT_DEFAULTS --config-file=$MYSQL_TEST_DIR/std_data/bug15328.cnf mysqldump -# -# BUG #19025 mysqldump doesn't correctly dump "auto_increment = [int]" -# +--echo # +--echo # BUG #19025 mysqldump doesn't correctly dump "auto_increment = [int]" +--echo # + create table `t1` ( t1_name varchar(255) default null, t1_id int(10) unsigned not null auto_increment, @@ -689,9 +693,9 @@ show create table `t1`; drop table `t1`; -# -# Bug #18536: wrong table order -# +--echo # +--echo # Bug #18536: wrong table order +--echo # create table t1(a int); create table t2(a int); @@ -700,9 +704,10 @@ create table t3(a int); --exec $MYSQL_DUMP --skip-comments --force --no-data test t3 t1 non_existing t2 drop table t1, t2, t3; -# -# Bug #21288: mysqldump segmentation fault when using --where -# +--echo # +--echo # Bug #21288: mysqldump segmentation fault when using --where +--echo # + create table t1 (a int); --error 2 --exec $MYSQL_DUMP --skip-comments --force test t1 --where='xx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' 2>&1 @@ -710,9 +715,9 @@ drop table t1; --echo End of 4.1 tests -# -# Bug #10213 mysqldump crashes when dumping VIEWs(on MacOS X) -# +--echo # +--echo # Bug #10213 mysqldump crashes when dumping VIEWs(on MacOS X) +--echo # create database db1; use db1; @@ -734,9 +739,9 @@ drop view v2; drop database db1; use test; -# -# Bug 10713 mysqldump includes database in create view and referenced tables -# +--echo # +--echo # Bug 10713 mysqldump includes database in create view and referenced tables +--echo # # create table and views in db2 create database db2; @@ -779,9 +784,9 @@ create view v1 as select * from t1; drop view v1; drop table t1; -# -# Bug #10213 mysqldump crashes when dumping VIEWs(on MacOS X) -# +--echo # +--echo # Bug #10213 mysqldump crashes when dumping VIEWs(on MacOS X) +--echo # create database mysqldump_test_db; use mysqldump_test_db; @@ -803,18 +808,18 @@ drop view v2; drop database mysqldump_test_db; use test; -# -# Bug #9756 -# +--echo # +--echo # Bug #9756 +--echo # CREATE TABLE t1 (a char(10)); INSERT INTO t1 VALUES ('\''); --exec $MYSQL_DUMP --skip-comments test t1 DROP TABLE t1; -# -# Bug #10927 mysqldump: Can't reload dump with view that consist of other view -# +--echo # +--echo # Bug #10927 mysqldump: Can't reload dump with view that consist of other view +--echo # create table t1(a int, b int, c varchar(30)); @@ -834,9 +839,9 @@ select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1; drop view v1, v2, v3; drop table t1; -# -# Test for dumping triggers -# +--echo # +--echo # Test for dumping triggers +--echo # CREATE TABLE t1 (a int, b bigint default NULL); CREATE TABLE t2 (a int); @@ -884,9 +889,9 @@ show tables; show triggers; DROP TABLE t1, t2; -# -# Bugs #9136, #12917: problems with --defaults-extra-file option -# +--echo # +--echo # Bugs #9136, #12917: problems with --defaults-extra-file option +--echo # --system echo '[mysqltest1]' > $MYSQLTEST_VARDIR/tmp/tmp.cnf --system echo 'port=1234' >> $MYSQLTEST_VARDIR/tmp/tmp.cnf @@ -894,9 +899,9 @@ DROP TABLE t1, t2; --exec $MYSQL_MY_PRINT_DEFAULTS -e $MYSQLTEST_VARDIR/tmp/tmp.cnf mysqltest1 mysqltest1 --system rm $MYSQLTEST_VARDIR/tmp/tmp.cnf -# -# Test of fix to BUG 12597 -# +--echo # +--echo # Test of fix to BUG 12597 +--echo # DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( @@ -932,9 +937,9 @@ DROP TRIGGER testref; DROP TABLE test1; DROP TABLE test2; -# -# BUG#9056 - mysqldump does not dump routines -# +--echo # +--echo # BUG#9056 - mysqldump does not dump routines +--echo # --disable_warnings DROP TABLE IF EXISTS t1; @@ -981,9 +986,10 @@ DROP PROCEDURE bug9056_proc2; DROP PROCEDURE `a'b`; drop table t1; -# -# BUG# 13052 - mysqldump timestamp reloads broken -# +--echo # +--echo # BUG# 13052 - mysqldump timestamp reloads broken +--echo # + --disable_warnings drop table if exists t1; --enable_warnings @@ -1003,9 +1009,10 @@ drop table t1; set global time_zone=default; set time_zone=default; -# -# Test of fix to BUG 13146 - ansi quotes break loading of triggers -# +--echo # +--echo # Test of fix to BUG 13146 - ansi quotes break loading of triggers +--echo # + --disable_warnings DROP TABLE IF EXISTS `t1 test`; DROP TABLE IF EXISTS `t2 test`; @@ -1036,9 +1043,9 @@ DROP TRIGGER `test trig`; DROP TABLE `t1 test`; DROP TABLE `t2 test`; -# -# BUG# 12838 mysqldump -x with views exits with error -# +--echo # +--echo # BUG# 12838 mysqldump -x with views exits with error +--echo # --disable_warnings drop table if exists t1; @@ -1060,10 +1067,10 @@ drop view v0; drop view v1; drop table t1; -# -# BUG#14554 - mysqldump does not separate words "ROW" and "BEGIN" -# for tables with trigger created in the IGNORE_SPACE sql mode. -# +--echo # +--echo # BUG#14554 - mysqldump does not separate words "ROW" and "BEGIN" +--echo # for tables with trigger created in the IGNORE_SPACE sql mode. +--echo # SET @old_sql_mode = @@SQL_MODE; SET SQL_MODE = IGNORE_SPACE; @@ -1084,18 +1091,19 @@ SET SQL_MODE = @old_sql_mode; DROP TRIGGER tr1; DROP TABLE t1; -# -# Bug #13318: Bad result with empty field and --hex-blob -# +--echo # +--echo # Bug #13318: Bad result with empty field and --hex-blob +--echo # + create table t1 (a binary(1), b blob); insert into t1 values ('',''); --exec $MYSQL_DUMP --skip-comments --skip-extended-insert --hex-blob test t1 --exec $MYSQL_DUMP --skip-comments --hex-blob test t1 drop table t1; -# -# Bug 14871 Invalid view dump output -# +--echo # +--echo # Bug 14871 Invalid view dump output +--echo # create table t1 (a int); insert into t1 values (289), (298), (234), (456), (789); @@ -1122,9 +1130,9 @@ select * from v3 order by a; drop table t1; drop view v1, v2, v3, v4, v5; -# -# Bug #16878 dump of trigger -# +--echo # +--echo # Bug #16878 dump of trigger +--echo # create table t1 (a int, created datetime); create table t2 (b int, created datetime); @@ -1152,11 +1160,10 @@ drop trigger tr1; drop trigger tr2; drop table t1, t2; +--echo # +--echo # Bug#18462 mysqldump does not dump view structures correctly +--echo # -# -# Bug#18462 mysqldump does not dump view structures correctly -# -# create table t (qty int, price int); insert into t values(3, 50); insert into t values(5, 51); @@ -1173,11 +1180,11 @@ drop view v2; drop table t; -# -# Bug#14857 Reading dump files with single statement stored routines fails. -# fixed by patch for bug#16878 -# -# +--echo # +--echo # Bug#14857 Reading dump files with single statement stored routines fails. +--echo # fixed by patch for bug#16878 +--echo # + DELIMITER |; /*!50003 CREATE FUNCTION `f`() RETURNS bigint(20) return 42 */| @@ -1189,10 +1196,10 @@ show create procedure p; drop function f; drop procedure p; -# -# Bug #17371 Unable to dump a schema with invalid views -# -# +--echo # +--echo # Bug #17371 Unable to dump a schema with invalid views +--echo # + create table t1 ( id serial ); create view v1 as select * from t1; drop table t1; @@ -1203,9 +1210,9 @@ drop table t1; --echo } mysqldump drop view v1; -# BUG#17201 Spurious 'DROP DATABASE' in output, -# also confusion between tables and views. -# Example code from Markus Popp +--echo # BUG#17201 Spurious 'DROP DATABASE' in output, +--echo # also confusion between tables and views. +--echo # Example code from Markus Popp create database mysqldump_test_db; use mysqldump_test_db; @@ -1220,7 +1227,9 @@ drop view v1; drop table t1; drop database mysqldump_test_db; -# Bug21014 Segmentation fault of mysqldump on view +--echo # +--echo # Bug21014 Segmentation fault of mysqldump on view +--echo # create database mysqldump_tables; use mysqldump_tables; @@ -1237,7 +1246,9 @@ drop database mysqldump_views; drop table mysqldump_tables.basetable; drop database mysqldump_tables; -# Bug20221 Dumping of multiple databases containing view(s) yields maleformed dumps +--echo # +--echo # Bug20221 Dumping of multiple databases containing view(s) yields maleformed dumps +--echo # create database mysqldump_dba; use mysqldump_dba; @@ -1276,9 +1287,9 @@ drop table t1; drop database mysqldump_dbb; use test; -# -# Bug#21215 mysqldump creating incomplete backups without warning -# +--echo # +--echo # Bug#21215 mysqldump creating incomplete backups without warning +--echo # # Create user without sufficient privs to perform the requested operation create user mysqltest_1@localhost; @@ -1318,35 +1329,89 @@ grant REPLICATION CLIENT on *.* to mysqltest_1@localhost; drop table t1; drop user mysqltest_1@localhost; -# -# Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the -# information_schema database. -# +--echo # +--echo # Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the +--echo # information_schema database. +--echo # +--echo # Bug #21424 mysqldump failing to export/import views +--echo # + +# Do as root connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; create database mysqldump_myDB; use mysqldump_myDB; create user myDB_User; -grant create view, select on mysqldump_myDB.* to myDB_User@localhost; +grant create, create view, select, insert on mysqldump_myDB.* to myDB_User@localhost; create table t1 (c1 int); insert into t1 values (3); +# Do as a user connect (user1,localhost,myDB_User,,mysqldump_myDB,$MASTER_MYPORT,$MASTER_MYSOCK); connection user1; use mysqldump_myDB; +create table u1 (f1 int); +insert into u1 values (4); create view v1 (c1) as select * from t1; -# Backup should not fail. ---exec $MYSQL_DUMP --all-databases --add-drop-table > $MYSQLTEST_VARDIR/tmp/bug21527.sql +# Backup should not fail for Bug #21527. Flush priviliges test begins. +--exec $MYSQL_DUMP --skip-comments --add-drop-table --flush-privileges --ignore-table=mysql.general_log --ignore-table=mysql.slow_log --databases mysqldump_myDB mysql > $MYSQLTEST_VARDIR/tmp/bug21527.sql # Clean up connection root; use mysqldump_myDB; drop view v1; drop table t1; +drop table u1; +revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; +drop user myDB_User; +drop database mysqldump_myDB; +flush privileges; + +--echo # Bug #21424 continues from here. +--echo # Restore. Flush Privileges test ends. +--echo # + +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21527.sql; + +# Do as a user +connection user1; +use mysqldump_myDB; + +# Ultimate test for correct data. +select * from mysqldump_myDB.v1; +select * from mysqldump_myDB.u1; + +#Final cleanup. +connection root; +use mysqldump_myDB; +drop view v1; +drop table t1; +drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; drop user myDB_User; drop database mysqldump_myDB; use test; ---echo End of 5.0 tests +--echo # +--echo # BUG#13926: --order-by-primary fails if PKEY contains quote character +--echo # + +--disable_warnings +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a b` INT, + `c"d` INT, + `e``f` INT, + PRIMARY KEY (`a b`, `c"d`, `e``f`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +insert into t1 values (0815, 4711, 2006); + +--exec $MYSQL_DUMP --skip-comments --compatible=ansi --order-by-primary test t1 +--exec $MYSQL_DUMP --skip-comments --order-by-primary test t1 +DROP TABLE `t1`; +--enable_warnings + +--echo # +--echo # End of 5.0 tests +--echo # diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 6301cc0f584..63c611e6be6 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -92,3 +92,50 @@ SELECT ROW(NULL,10) <=> ROW(3,NULL); # SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ; select row(NULL,1)=(2,0); + +# +# Bug #16081: row equalities are to be used for query optimizations +# + +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3); + +EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2; +EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2); +SELECT * FROM t1 WHERE a=3 and b=2; +SELECT * FROM t1 WHERE (a,b)=(3,2); + +CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); +INSERT INTO t2 VALUES + (1,1,2), (3,1,3), (1,2,2), (4,4,2), + (1,1,1), (3,1,1), (1,2,1); + +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); +SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b; +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); + +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2; +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); +SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b; +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); + +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); + +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); +SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); + +EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2; +EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2); +SELECT * FROM t2 WHERE a=3 and b=2; +SELECT * FROM t2 WHERE (a,b)=(3,2); + +EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1; +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); +SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); + +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); +SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); + +DROP TABLE t1,t2; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index fa66306aaa6..3c46a5b4ca1 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -1,10 +1,21 @@ -# See if queries that use both auto_increment and LAST_INSERT_ID() -# are replicated well - -# We also check how the foreign_key_check variable is replicated +--echo # +--echo # Setup +--echo # source include/master-slave.inc; source include/have_innodb.inc; +use test; +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings + +--echo # +--echo # See if queries that use both auto_increment and LAST_INSERT_ID() +--echo # are replicated well +--echo # +--echo # We also check how the foreign_key_check variable is replicated +--echo # + connection master; create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); @@ -39,7 +50,9 @@ select * from t1; select * from t2; connection master; -# check if INSERT SELECT in auto_increment is well replicated (bug #490) +--echo # +--echo # check if INSERT SELECT in auto_increment is well replicated (bug #490) +--echo # drop table t2; drop table t1; @@ -62,10 +75,11 @@ save_master_pos; connection slave; sync_with_master; -# -# Bug#8412: Error codes reported in binary log for CHARACTER SET, -# FOREIGN_KEY_CHECKS -# +--echo # +--echo # Bug#8412: Error codes reported in binary log for CHARACTER SET, +--echo # FOREIGN_KEY_CHECKS +--echo # + connection master; SET TIMESTAMP=1000000000; CREATE TABLE t1 ( a INT UNIQUE ); @@ -78,9 +92,10 @@ connection master; drop table t1; sync_slave_with_master; -# -# Bug#14553: NULL in WHERE resets LAST_INSERT_ID -# +--echo # +--echo # Bug#14553: NULL in WHERE resets LAST_INSERT_ID +--echo # + connection master; create table t1(a int auto_increment, key(a)); create table t2(a int); @@ -95,8 +110,10 @@ connection master; drop table t1; drop table t2; sync_slave_with_master; -# End of 4.1 tests +--echo # +--echo # End of 4.1 tests +--echo # # # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0 @@ -227,7 +244,10 @@ select * from t1 order by n; connection master; drop table t1; +sync_slave_with_master; + +--echo +--echo # End of 5.0 tests +--echo -# End of 5.0 tests -sync_slave_with_master; diff --git a/mysql-test/t/rpl_max_relay_size.test b/mysql-test/t/rpl_max_relay_size.test index 9b6b06e0b14..be1fbf172fc 100644 --- a/mysql-test/t/rpl_max_relay_size.test +++ b/mysql-test/t/rpl_max_relay_size.test @@ -7,7 +7,11 @@ source include/master-slave.inc; connection slave; stop slave; connection master; -# Generate a big enough master's binlog to cause relay log rotations + +--echo # +--echo # Generate a big enough master's binlog to cause relay log rotations +--echo # + create table t1 (a int); let $1=800; disable_query_log; @@ -23,6 +27,11 @@ drop table t1; save_master_pos; connection slave; reset slave; + +--echo # +--echo # Test 1 +--echo # + set global max_binlog_size=8192; set global max_relay_log_size=8192-1; # mapped to 4096 select @@global.max_relay_log_size; @@ -30,7 +39,13 @@ start slave; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # +--vertical_results show slave status; + +--echo # +--echo # Test 2 +--echo # + stop slave; reset slave; set global max_relay_log_size=(5*4096); @@ -39,7 +54,13 @@ start slave; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # +--vertical_results show slave status; + +--echo # +--echo # Test 3: max_relay_log_size = 0 +--echo # + stop slave; reset slave; set global max_relay_log_size=0; @@ -48,9 +69,12 @@ start slave; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # +--vertical_results show slave status; -# Tests below are mainly to ensure that we have not coded with wrong assumptions +--echo # +--echo # Test 4: Tests below are mainly to ensure that we have not coded with wrong assumptions +--echo # stop slave; reset slave; @@ -59,8 +83,13 @@ reset slave; flush logs; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # +--vertical_results show slave status; +--echo # +--echo # Test 5 +--echo # + reset slave; start slave; sync_with_master; @@ -75,8 +104,13 @@ connection slave; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # +--vertical_results show slave status; -# one more rotation, to be sure Relay_Log_Space is correctly updated + +--echo # +--echo # Test 6: one more rotation, to be sure Relay_Log_Space is correctly updated +--echo # + flush logs; connection master; drop table t1; @@ -85,6 +119,7 @@ connection slave; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # +--vertical_results show slave status; connection master; @@ -92,5 +127,6 @@ connection master; flush logs; show master status; -# End of 4.1 tests -# +--echo # +--echo # End of 4.1 tests +--echo # diff --git a/mysql-test/t/rpl_view.test b/mysql-test/t/rpl_view.test index d0990b4fbee..812e5d44d58 100644 --- a/mysql-test/t/rpl_view.test +++ b/mysql-test/t/rpl_view.test @@ -129,3 +129,24 @@ DROP TABLE t1; --sync_with_master --connection master +# +# BUG#19419: "VIEW: View that the column name is different +# by master and slave is made". +# +connection master; +create table t1(a int, b int); +insert into t1 values (1, 1), (1, 2), (1, 3); +create view v1(a, b) as select a, sum(b) from t1 group by a; + +sync_slave_with_master; +explain v1; +show create table v1; +select * from v1; + +connection master; +drop table t1; +drop view v1; + +sync_slave_with_master; + +--echo End of 5.0 tests diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index c93dcf2c2bc..f19a64df0f4 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6320,6 +6320,17 @@ drop procedure bug21416| # +# BUG#21416: SP: Recursion level higher than zero needed for non-recursive call +# +--disable_warnings +drop procedure if exists bug21416| +--enable_warnings +create procedure bug21416() show create procedure bug21416| +call bug21416()| +drop procedure bug21416| + + +# # BUG#21414: SP: Procedure undroppable, to some extent # --disable_warnings @@ -6352,6 +6363,77 @@ INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_да call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| drop database това_е_дълго_име_за_база_данни_нали| + +# +# BUG#21493: Crash on the second call of a procedure containing +# a select statement that uses an IN aggregating subquery +# + +CREATE TABLE t3 ( + Member_ID varchar(15) NOT NULL, + PRIMARY KEY (Member_ID) +)| + +CREATE TABLE t4 ( + ID int(10) unsigned NOT NULL auto_increment, + Member_ID varchar(15) NOT NULL default '', + Action varchar(12) NOT NULL, + Action_Date datetime NOT NULL, + Track varchar(15) default NULL, + User varchar(12) default NULL, + Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update + CURRENT_TIMESTAMP, + PRIMARY KEY (ID), + KEY Action (Action), + KEY Action_Date (Action_Date) +)| + + +INSERT INTO t3(Member_ID) VALUES + ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')| + +INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES + ('111111', 'Disenrolled', '2006-03-01', 'CAD' ), + ('111111', 'Enrolled', '2006-03-01', 'CAD' ), + ('111111', 'Disenrolled', '2006-07-03', 'CAD' ), + ('222222', 'Enrolled', '2006-03-07', 'CAD' ), + ('222222', 'Enrolled', '2006-03-07', 'CHF' ), + ('222222', 'Disenrolled', '2006-08-02', 'CHF' ), + ('333333', 'Enrolled', '2006-03-01', 'CAD' ), + ('333333', 'Disenrolled', '2006-03-01', 'CAD' ), + ('444444', 'Enrolled', '2006-03-01', 'CAD' ), + ('555555', 'Disenrolled', '2006-03-01', 'CAD' ), + ('555555', 'Enrolled', '2006-07-21', 'CAD' ), + ('555555', 'Disenrolled', '2006-03-01', 'CHF' ), + ('666666', 'Enrolled', '2006-02-09', 'CAD' ), + ('666666', 'Enrolled', '2006-05-12', 'CHF' ), + ('666666', 'Disenrolled', '2006-06-01', 'CAD' )| + +--disable_warnings +DROP FUNCTION IF EXISTS bug21493| +--enable_warnings + +CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45) +BEGIN +DECLARE tracks VARCHAR(45); +SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4 + WHERE Member_ID=paramMember AND Action='Enrolled' AND + (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4 + WHERE Member_ID=paramMember GROUP BY Track); +RETURN tracks; +END| + +SELECT bug21493('111111')| +SELECT bug21493('222222')| + +SELECT bug21493(Member_ID) FROM t3| + +DROP FUNCTION bug21493| +DROP TABLE t3,t4| + +--echo End of 5.0 tests + + # # BUG#NNNN: New bug synopsis # diff --git a/mysql-test/t/strict_autoinc_1myisam.test b/mysql-test/t/strict_autoinc_1myisam.test new file mode 100644 index 00000000000..d9ecce30974 --- /dev/null +++ b/mysql-test/t/strict_autoinc_1myisam.test @@ -0,0 +1,8 @@ +# +# Bug#20573 Strict mode auto-increment +# + +let $type= 'MYISAM' ; +--source include/strict_autoinc.inc + +# end of test diff --git a/mysql-test/t/strict_autoinc_2innodb.test b/mysql-test/t/strict_autoinc_2innodb.test new file mode 100644 index 00000000000..83dfe950938 --- /dev/null +++ b/mysql-test/t/strict_autoinc_2innodb.test @@ -0,0 +1,10 @@ +-- source include/have_innodb.inc + +# +# Bug#20573 Strict mode auto-increment +# + +let $type= 'InnoDB' ; +--source include/strict_autoinc.inc + +# end of test diff --git a/mysql-test/t/strict_autoinc_3heap.test b/mysql-test/t/strict_autoinc_3heap.test new file mode 100644 index 00000000000..f266ecdfda2 --- /dev/null +++ b/mysql-test/t/strict_autoinc_3heap.test @@ -0,0 +1,8 @@ +# +# Bug#20573 Strict mode auto-increment +# + +let $type= 'MEMORY' ; +--source include/strict_autoinc.inc + +# end of test diff --git a/mysql-test/t/strict_autoinc_4bdb.test b/mysql-test/t/strict_autoinc_4bdb.test new file mode 100644 index 00000000000..10d6bfd41e7 --- /dev/null +++ b/mysql-test/t/strict_autoinc_4bdb.test @@ -0,0 +1,10 @@ +-- source include/have_bdb.inc + +# +# Bug#20573 Strict mode auto-increment +# + +let $type= 'BDB' ; +--source include/strict_autoinc.inc + +# end of test diff --git a/mysql-test/t/strict_autoinc_5ndb.test b/mysql-test/t/strict_autoinc_5ndb.test new file mode 100644 index 00000000000..9e2090fddef --- /dev/null +++ b/mysql-test/t/strict_autoinc_5ndb.test @@ -0,0 +1,10 @@ +-- source include/have_ndb.inc + +# +# Bug#20573 Strict mode auto-increment +# + +let $type= 'NDB' ; +--source include/strict_autoinc.inc + +# end of test diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ed122e9ff5a..a4e535ac418 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1892,6 +1892,32 @@ select * from t1 r1 group by r2.retailerId); drop table t1; +# +# Bug #21180: Subselect with index for both WHERE and ORDER BY +# produces empty result +# +create table t1(a int, primary key (a)); +insert into t1 values (10); + +create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); +insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); + +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; + +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; + +drop table t1,t2; + # End of 4.1 tests # @@ -2280,3 +2306,29 @@ SELECT * FROM t1,t2 ORDER BY t1.t DESC LIMIT 1); DROP TABLE t1, t2; + +# +# Bug#14654 : Cannot select from the same table twice within a UNION +# statement +# +CREATE TABLE t1 (i INT); + +(SELECT i FROM t1) UNION (SELECT i FROM t1); +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS + ( + (SELECT i FROM t1) UNION + (SELECT i FROM t1) + ); + +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); + +#TODO:not supported +--error 1064 +explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) + from t1; +#supported +explain select * from t1 where not exists + ((select t11.i from t1 t11) union (select t12.i from t1 t12)); + +DROP TABLE t1; diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index e028dbc51d9..998f8f18fbe 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -238,4 +238,19 @@ select * from t1; --disable_metadata drop table t1; +# +# Bug#15583: BIN()/OCT()/CONV() do not work with BIT values +# +create table bug15583(b BIT(8), n INT); +insert into bug15583 values(128, 128); +insert into bug15583 values(null, null); +insert into bug15583 values(0, 0); +insert into bug15583 values(255, 255); +select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583; +select hex(b)=hex(n) as should_be_onetrue, bin(b)=bin(n) as should_be_onetrue, oct(b)=oct(n) as should_be_onetrue from bug15583; +select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583; +select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583; +drop table bug15583; + + --echo End of 5.0 tests diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 78bdd9b8a80..c6050753943 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -36,8 +36,8 @@ INSERT INTO t1 VALUES ( "2000-1-2" ); INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4"; -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; DROP TABLE t1; # @@ -115,4 +115,11 @@ INSERT INTO t1 VALUES ('abc'); SELECT * FROM t1; DROP TABLE t1; +# +# Bug#21677: Wrong result when comparing a DATE and a DATETIME in BETWEEN +# +create table t1(start_date date, end_date date); +insert into t1 values ('2000-01-01','2000-01-02'); +select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date; +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index b2a9728de00..644ca506eba 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -144,9 +144,6 @@ select @@version; --replace_column 1 # select @@global.version; -# End of 4.1 tests - -# # Bug #6598: problem with cast(NULL as signed integer); # @@ -212,3 +209,5 @@ insert into t1 values (1,2),(2,3),(3,1); select @var:=f2 from t1 group by f1 order by f2 desc limit 1; select @var; drop table t1; + +# End of 4.1 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index edff38274c4..4745804e847 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2449,8 +2449,8 @@ INSERT INTO t1 VALUES CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; -SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; +SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); +SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); DROP VIEW v1; DROP TABLE t1; @@ -2833,5 +2833,21 @@ DROP FUNCTION f2; DROP VIEW v1, v2; DROP TABLE t1; +# +# Bug #5500: wrong select_type in EXPLAIN output for queries over views +# + +CREATE TABLE t1 (s1 int); +CREATE VIEW v1 AS SELECT * FROM t1; + +EXPLAIN SELECT * FROM t1; +EXPLAIN SELECT * FROM v1; +INSERT INTO t1 VALUES (1), (3), (2); + +EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); + +DROP VIEW v1; +DROP TABLE t1; --echo End of 5.0 tests. |