diff options
Diffstat (limited to 'mysql-test/r')
87 files changed, 4602 insertions, 382 deletions
diff --git a/mysql-test/r/adddate_454.result b/mysql-test/r/adddate_454.result new file mode 100644 index 00000000000..0993cdce32c --- /dev/null +++ b/mysql-test/r/adddate_454.result @@ -0,0 +1,10 @@ +create table t1 (d date); +insert into t1 values ('2012-00-00'); +select * from t1; +d +2012-00-00 +update t1 set d = adddate(d, interval 1 day); +select * from t1; +d +NULL +drop table t1; diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 30b4ff11227..12cbf294b69 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -150,7 +150,7 @@ select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; -ERROR 23000: Duplicate entry '255' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -162,8 +162,7 @@ select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; -Warnings: -Warning 1264 Out of range value for column 'i' at row 1 +ERROR 22003: Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -487,7 +486,7 @@ SELECT @@SESSION.AUTO_INCREMENT_OFFSET; @@SESSION.AUTO_INCREMENT_OFFSET 1 INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -ERROR 22003: Out of range value for column 't1' at row 167 +ERROR 22003: Out of range value for column 'c1' at row 2 SELECT * FROM t1; c1 1 diff --git a/mysql-test/r/auto_increment_ranges_innodb.result b/mysql-test/r/auto_increment_ranges_innodb.result new file mode 100644 index 00000000000..fb936ddfd2b --- /dev/null +++ b/mysql-test/r/auto_increment_ranges_innodb.result @@ -0,0 +1,266 @@ +set default_storage_engine=innodb; +drop table if exists t1; +# +# Testing ranges with smallint +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32767); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(32767-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +32766 +32767 +truncate table t1; +insert into t1 values(32767),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(32767-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32767+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +32767 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned smallint +# +create table t1 (a smallint unsigned primary key auto_increment); +insert into t1 values(65535); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(65535-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +65534 +65535 +truncate table t1; +insert into t1 values(65535),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(65535-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(65535+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +65535 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with integer +# +create table t1 (a int primary key auto_increment); +insert into t1 values(2147483647); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(2147483647-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +2147483646 +2147483647 +truncate table t1; +insert into t1 values(2147483647),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(2147483647-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(2147483647+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +2147483647 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned integer +# +create table t1 (a int unsigned primary key auto_increment); +insert into t1 values(4294967295); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(4294967295-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +4294967294 +4294967295 +truncate table t1; +insert into t1 values(4294967295),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(4294967295-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(4294967295+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +4294967295 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with bigint +# +create table t1 (a bigint primary key auto_increment); +insert into t1 values(cast(9223372036854775807 as unsigned)); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775806 +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775807 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned bigint +# +create table t1 (a bigint unsigned primary key auto_increment); +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +truncate table t1; +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551614 +truncate table t1; +insert into t1 values(18446744073709551615),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +truncate table t1; +insert into t1 values(18446744073709551615-1),(NULL),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +drop table t1; +# +# Test IGNORE and strict mode +# +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +select * from t1; +a +1 +32766 +32767 +truncate table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32766),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +set @@sql_mode=@org_mode; +drop table t1; +# +# Test auto increment with negative numbers +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +-5 +1 +2 +3 +5 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +a +-5 +1 +DROP TABLE t1; +# +# Test inserting a value out-of-range into an auto increment column +# +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +2 +32767 +DROP TABLE t1; +# +# Test old behaviour +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; diff --git a/mysql-test/r/auto_increment_ranges_myisam.result b/mysql-test/r/auto_increment_ranges_myisam.result new file mode 100644 index 00000000000..5101321c864 --- /dev/null +++ b/mysql-test/r/auto_increment_ranges_myisam.result @@ -0,0 +1,272 @@ +set default_storage_engine=MYISAM; +drop table if exists t1; +# +# Testing ranges with smallint +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32767); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(32767-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +32766 +32767 +truncate table t1; +insert into t1 values(32767),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +32767 +truncate table t1; +insert into t1 values(32767-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32767+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +32767 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned smallint +# +create table t1 (a smallint unsigned primary key auto_increment); +insert into t1 values(65535); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(65535-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +65534 +65535 +truncate table t1; +insert into t1 values(65535),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +65535 +truncate table t1; +insert into t1 values(65535-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(65535+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +65535 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with integer +# +create table t1 (a int primary key auto_increment); +insert into t1 values(2147483647); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(2147483647-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +2147483646 +2147483647 +truncate table t1; +insert into t1 values(2147483647),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +2147483647 +truncate table t1; +insert into t1 values(2147483647-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(2147483647+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +2147483647 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned integer +# +create table t1 (a int unsigned primary key auto_increment); +insert into t1 values(4294967295); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(4294967295-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +4294967294 +4294967295 +truncate table t1; +insert into t1 values(4294967295),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +4294967295 +truncate table t1; +insert into t1 values(4294967295-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(4294967295+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +4294967295 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with bigint +# +create table t1 (a bigint primary key auto_increment); +insert into t1 values(cast(9223372036854775807 as unsigned)); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775806 +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775807 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned bigint +# +create table t1 (a bigint unsigned primary key auto_increment); +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +truncate table t1; +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551614 +truncate table t1; +insert into t1 values(18446744073709551615),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551615 +truncate table t1; +insert into t1 values(18446744073709551615-1),(NULL),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +drop table t1; +# +# Test IGNORE and strict mode +# +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +select * from t1; +a +1 +32766 +32767 +truncate table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32766),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +set @@sql_mode=@org_mode; +drop table t1; +# +# Test auto increment with negative numbers +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +-5 +1 +2 +3 +4 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +a +-5 +1 +DROP TABLE t1; +# +# Test inserting a value out-of-range into an auto increment column +# +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +2 +32767 +DROP TABLE t1; +# +# Test old behaviour +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index a95975f1097..3852da5d4b0 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -220,6 +220,10 @@ a d 3 11120436154190595086 drop table t1, t2; End of 5.0 tests +CREATE TABLE t1(a YEAR); +SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END; +1 +DROP TABLE t1; create table t1 (f1 time); insert t1 values ('00:00:00'),('00:01:00'); select case t1.f1 when '00:00:00' then 1 end from t1; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 3e3d75f66e4..af198edc4ca 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -529,6 +529,8 @@ SUCCESS # 13. Read-write statement: INSERT IGNORE, change 0 rows. # insert ignore t1 set a=2; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' call p_verify_status_increment(2, 2, 1, 0); SUCCESS diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result index 6ee01349022..cdd234f1478 100644 --- a/mysql-test/r/compare.result +++ b/mysql-test/r/compare.result @@ -96,3 +96,7 @@ SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00'; a DROP TABLE t1; End of 5.0 tests +CREATE TABLE t1(a INT ZEROFILL); +SELECT 1 FROM t1 WHERE t1.a IN (1, t1.a) AND t1.a=2; +1 +DROP TABLE t1; diff --git a/mysql-test/r/create-big.result b/mysql-test/r/create-big.result index 34293d7e5cd..4cce5d8618c 100644 --- a/mysql-test/r/create-big.result +++ b/mysql-test/r/create-big.result @@ -53,8 +53,8 @@ set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go'; create table t1 select 1 as i;; set debug_sync='now WAIT_FOR parked'; alter table t3 rename to t1; -ERROR 42S01: Table 't1' already exists set debug_sync='now SIGNAL go'; +ERROR 42S01: Table 't1' already exists show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -65,8 +65,8 @@ set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go'; create table t1 select 1 as i;; set debug_sync='now WAIT_FOR parked'; alter table t3 rename to t1, add k int; -ERROR 42S01: Table 't1' already exists set debug_sync='now SIGNAL go'; +ERROR 42S01: Table 't1' already exists show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0a6dff6d9a3..ba52959be84 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2396,6 +2396,8 @@ a b drop table t1; create table if not exists t1 (a int unique, b int) ignore select 1 as a, 1 as b union select 1 as a, 2 as b; +Warnings: +Warning 1062 Duplicate entry '1' for key 'a' select * from t1; a b 1 1 diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 1cb4b48ed69..c897108f793 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -2039,6 +2039,8 @@ hex(concat(a)) a 30303030303030303030303030303031303030302E31 00000000000000010000.1 drop table t1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); select hex(concat(a)) from t1; hex(concat(a)) @@ -2352,6 +2354,8 @@ hex(a) drop table t1; drop view v1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); create view v1(a) as select concat(a) from t1; show columns from v1; diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 3d695a87701..5323469dff3 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -2431,6 +2431,8 @@ hex(concat(a)) a 30303030303030303030303030303031303030302E31 00000000000000010000.1 drop table t1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); select hex(concat(a)) from t1; hex(concat(a)) @@ -2744,6 +2746,8 @@ hex(a) drop table t1; drop view v1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); create view v1(a) as select concat(a) from t1; show columns from v1; diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index e2169e4e4e2..ae459fce8eb 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -2458,6 +2458,8 @@ hex(concat(a)) a 30303030303030303030303030303031303030302E31 00000000000000010000.1 drop table t1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); select hex(concat(a)) from t1; hex(concat(a)) @@ -2771,6 +2773,8 @@ hex(a) drop table t1; drop view v1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); create view v1(a) as select concat(a) from t1; show columns from v1; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 501b9cb611b..d463ce87c92 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -3341,6 +3341,8 @@ hex(concat(a)) a 00300030003000300030003000300030003000300030003000300030003000310030003000300030002E0031 00000000000000010000.1 drop table t1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); select hex(concat(a)) from t1; hex(concat(a)) @@ -3654,6 +3656,8 @@ hex(a) drop table t1; drop view v1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); create view v1(a) as select concat(a) from t1; show columns from v1; diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index 03f60261450..6cf24d768f7 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -1182,5 +1182,11 @@ a 256 Warnings: Warning 1260 Row 1 was cut by GROUP_CONCAT() # +# incorrect charset for val_str_ascii +# +SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second; +'2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second +2010-10-10 10:10:10 +# # End of 5.5 tests # diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 4dd9d787528..69e32977103 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -4197,6 +4197,8 @@ hex(concat(a)) a 30303030303030303030303030303031303030302E31 00000000000000010000.1 drop table t1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); select hex(concat(a)) from t1; hex(concat(a)) @@ -4510,6 +4512,8 @@ hex(a) drop table t1; drop view v1; create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (1); create view v1(a) as select concat(a) from t1; show columns from v1; diff --git a/mysql-test/r/datetime_456.result b/mysql-test/r/datetime_456.result new file mode 100644 index 00000000000..ba020a250b7 --- /dev/null +++ b/mysql-test/r/datetime_456.result @@ -0,0 +1,8 @@ +create table t1 (d datetime); +insert t1 values (addtime('9999-12-31 23:59:59', '00:00:01')), +(from_days(3652499)); +select * from t1; +d +NULL +NULL +drop table t1; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index ba01db4a66f..5446406924f 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1687,7 +1687,6 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; b c a -8 c c EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1702,7 +1701,6 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a -8 c c INSERT INTO t3 VALUES (100), (200); EXPLAIN EXTENDED SELECT t.b, t.c, t1.a @@ -1718,7 +1716,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; b c a -8 c c +NULL NULL c EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1733,7 +1731,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a -8 c c +NULL NULL c SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result index 8560e6176c4..23c77d3978c 100644 --- a/mysql-test/r/errors.result +++ b/mysql-test/r/errors.result @@ -160,3 +160,11 @@ ERROR 42S22: Unknown column '' in 'VALUES() function' INSERT INTO t2(a,b) VALUES (1,0) ON DUPLICATE KEY UPDATE b=(SELECT VALUES(a)+2 FROM t1); DROP TABLE t1, t2; +# +# MDEV-492: incorrect error check before sending OK in mysql_update +# +CREATE TABLE t1 (a CHAR(3), b BLOB); +UPDATE t1 SET a = 'new' +WHERE COLUMN_CREATE( 1, 'v', 1, 'w' ) IS NULL; +ERROR 22007: Illegal value used as argument of dynamic column function +drop table t1; diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index e081b56e6ce..919e29f185e 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -287,7 +287,7 @@ WHERE t1.f1 GROUP BY t1.f1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a @@ -297,12 +297,12 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 @@ -313,12 +313,12 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests. diff --git a/mysql-test/r/features.result b/mysql-test/r/features.result new file mode 100644 index 00000000000..7b6a352ab0c --- /dev/null +++ b/mysql-test/r/features.result @@ -0,0 +1,140 @@ +drop table if exists t1; +show status like "feature%"; +Variable_name Value +Feature_dynamic_columns 0 +Feature_fulltext 0 +Feature_gis 0 +Feature_locale 0 +Feature_subquery 0 +Feature_timezone 0 +Feature_trigger 0 +Feature_xml 0 +# +# Feature GIS +# +CREATE TABLE t1 (g POINT); +SHOW FIELDS FROM t1; +Field Type Null Key Default Extra +g point YES NULL +INSERT INTO t1 VALUES +(PointFromText('POINT(10 10)')), +(PointFromText('POINT(20 10)')), +(PointFromText('POINT(20 20)')), +(PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); +drop table t1; +show status like "feature_gis"; +Variable_name Value +Feature_gis 3 +# +# Feature dynamic columns +# +set @a= COLUMN_CREATE(1, 1212 AS int); +set @b= column_add(@a, 2, 1212 as integer); +select column_get(@b, 2 as integer); +column_get(@b, 2 as integer) +1212 +show status like "feature_dynamic_columns"; +Variable_name Value +Feature_dynamic_columns 2 +# +# Feature fulltext +# +CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) engine=myisam; +INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), +('Full-text indexes', 'are called collections'), +('Only MyISAM tables','support collections'), +('Function MATCH ... AGAINST()','is used to do a search'), +('Full-text search in MySQL', 'implements vector space model'); +select * from t1 where MATCH(a,b) AGAINST ("collections"); +a b +Only MyISAM tables support collections +Full-text indexes are called collections +select * from t1 where MATCH(a,b) AGAINST ("indexes"); +a b +Full-text indexes are called collections +drop table t1; +show status like "feature_fulltext"; +Variable_name Value +Feature_fulltext 2 +# +# Feature locale +# +SET lc_messages=sr_RS; +SET lc_messages=en_US; +show status like "feature_locale"; +Variable_name Value +Feature_locale 2 +# +# Feature subquery +# +select (select 2); +(select 2) +2 +SELECT (SELECT 1) UNION SELECT (SELECT 2); +(SELECT 1) +1 +2 +create table t1 (a int); +insert into t1 values (2); +select (select a from t1 where t1.a=t2.a), a from t1 as t2; +(select a from t1 where t1.a=t2.a) a +2 2 +drop table t1; +show status like "feature_subquery"; +Variable_name Value +Feature_subquery 4 +# +# Feature timezone +# +SELECT FROM_UNIXTIME(unix_timestamp()) > "1970-01-01"; +FROM_UNIXTIME(unix_timestamp()) > "1970-01-01" +1 +set time_zone="+03:00"; +SELECT FROM_UNIXTIME(unix_timestamp()) > "1970-01-01"; +FROM_UNIXTIME(unix_timestamp()) > "1970-01-01" +1 +set time_zone= @@global.time_zone; +show status like "feature_timezone"; +Variable_name Value +Feature_timezone 1 +# +# Feature triggers +# +create table t1 (i int); +# let us test some very simple trigger +create trigger trg before insert on t1 for each row set @a:=1; +set @a:=0; +select @a; +@a +0 +insert into t1 values (1),(2); +select @a; +@a +1 +SHOW TRIGGERS IN test like 't1'; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg INSERT t1 set @a:=1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +drop trigger trg; +drop table t1; +show status like "%trigger%"; +Variable_name Value +Com_create_trigger 1 +Com_drop_trigger 1 +Com_show_create_trigger 0 +Com_show_triggers 1 +Executed_triggers 2 +Feature_trigger 2 +# +# Feature xml +# +SET @xml='<a aa1="aa1" aa2="aa2">a1<b ba1="ba1">b1<c>c1</c>b2</b>a2</a>'; +SELECT extractValue(@xml,'/a'); +extractValue(@xml,'/a') +a1 a2 +select updatexml('<div><div><span>1</span><span>2</span></div></div>', +'/','<tr><td>1</td><td>2</td></tr>') as upd1; +upd1 +<tr><td>1</td><td>2</td></tr> +show status like "feature_xml"; +Variable_name Value +Feature_xml 2 diff --git a/mysql-test/r/filesort_debug.result b/mysql-test/r/filesort_debug.result index 6d6eb817259..13912ac8756 100644 --- a/mysql-test/r/filesort_debug.result +++ b/mysql-test/r/filesort_debug.result @@ -4,7 +4,7 @@ SET @old_debug= @@session.debug; # CREATE TABLE t1(f0 int auto_increment primary key, f1 int); INSERT INTO t1(f1) VALUES (0),(1),(2),(3),(4),(5); -SET session debug_dbug= '+d,make_sort_keys_alloc_fail'; +SET session debug_dbug= '+d,alloc_sort_buffer_fail'; CALL mtr.add_suppression("Out of sort memory"); SELECT * FROM t1 ORDER BY f1 ASC, f0; ERROR HY001: Out of sort memory, consider increasing server sort buffer size diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index d3b3cd16210..b64351045bf 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -489,3 +489,10 @@ UNLOCK TABLES; # Switching to connection 'default'. COMMIT; DROP TABLE t1; +# +# Test flushing slave or relay logs twice +# +flush relay logs,relay logs; +ERROR HY000: Incorrect usage of FLUSH and RELAY LOGS +flush slave,slave; +ERROR HY000: Incorrect usage of FLUSH and SLAVE diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index c38201f6d9f..65323257300 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1527,6 +1527,8 @@ DROP TABLE t1; # Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) # create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (98,1998,19980101,"1998-01-01 00:00:00"), (00,2000,20000101,"2000-01-01 00:00:01"), @@ -1838,7 +1840,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 range a a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<expr_cache><<cache>(1),<cache>(2)>(<in_optimizer>(<cache>((1,2)),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10)) +Note 1003 select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<cache>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10)) SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10; MAX(a) NULL diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result index 6485793d0e1..67ea44f187a 100644 --- a/mysql-test/r/func_group_innodb.result +++ b/mysql-test/r/func_group_innodb.result @@ -146,19 +146,6 @@ count(*) min(7) max(7) 0 NULL NULL drop table t1m, t1i, t2m, t2i; # -# Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN -# -CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT, -KEY(b, c, a(765))) ENGINE=INNODB; -INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0); -SELECT MIN(c) FROM t1 GROUP BY b; -MIN(c) -0 -EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL b 263 NULL 4 Using index for group-by -DROP TABLE t1; -# # Bug #57954: BIT_AND function returns incorrect results when # semijoin=on CREATE TABLE c ( @@ -230,4 +217,18 @@ SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date = member_id_to COUNT(*) 518491 2 DROP TABLE t1; +# End of test BUG#12713907 +# +# Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN +# +CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT, +KEY(b, c, a(765))) ENGINE=INNODB; +INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0); +SELECT MIN(c) FROM t1 GROUP BY b; +MIN(c) +0 +EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL b 263 NULL 4 Using index for group-by +DROP TABLE t1; End of 5.5 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index bfc6b72caa1..98854db4812 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1023,11 +1023,11 @@ MAKETIME(CAST(-1 AS UNSIGNED), 0, 0) Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00' -SELECT EXTRACT(HOUR FROM '100000:02:03'); -EXTRACT(HOUR FROM '100000:02:03') +SELECT EXTRACT(HOUR FROM '10000:02:03'); +EXTRACT(HOUR FROM '10000:02:03') 838 Warnings: -Warning 1292 Truncated incorrect time value: '100000:02:03' +Warning 1292 Truncated incorrect time value: '10000:02:03' CREATE TABLE t1(f1 TIME); INSERT INTO t1 VALUES('916:00:00 a'); Warnings: @@ -1922,3 +1922,5 @@ cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)) select microsecond('12:00:00.123456'), microsecond('2009-12-31 23:59:59.000010'); microsecond('12:00:00.123456') microsecond('2009-12-31 23:59:59.000010') 123456 10 +select now(258); +ERROR 42000: Too big precision 258 specified for 'now'. Maximum is 6. diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index ca48177b1b5..30385323502 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1428,6 +1428,29 @@ WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; count(*) 1 DROP DATABASE gis_ogs; +# +# BUG #1043845 st_distance() results are incorrect depending on variable order +# +select st_distance(geomfromtext('LINESTRING(-95.9673005697771 36.13509598461, + -95.9673057475387 36.1344478941074, + -95.9673063519371 36.134484524621, + -95.9673049102515 36.1343976584193)'), +geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)')) ; +st_distance(geomfromtext('LINESTRING(-95.9673005697771 36.13509598461, + -95.9673057475387 36.1344478941074, + -95.9673063519371 36.134484524621, + +0.008148695928146028 +select st_distance(geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)'), +geomfromtext('LINESTRING(-95.9673005697771 36.13509598461, + -95.9673057475387 36.1344478941074, + -95.9673063519371 36.134484524621, + -95.9673049102515 36.1343976584193) ')) ; +st_distance(geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)'), +geomfromtext('LINESTRING(-95.9673005697771 36.13509598461, + -95.9673057475387 36.1344478941074, + -95.9673063519371 36. +0.008148695928146028 USE test; # # BUG#12414917 - ISCLOSED() CRASHES ON 64-BIT BUILDS diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 85ec79d94a1..72c53697e61 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2149,3 +2149,48 @@ f1 MIN(f2) MAX(f2) 4 00:25:00 00:25:00 DROP TABLE t1; #End of test#49771 +# +# Bug #58782 +# Missing rows with SELECT .. WHERE .. IN subquery +# with full GROUP BY and no aggr +# +CREATE TABLE t1 ( +pk INT NOT NULL, +col_int_nokey INT, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (10,7); +INSERT INTO t1 VALUES (11,1); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (13,3); +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2; +field1 field2 +10 7 +11 1 +12 5 +13 3 +CREATE TABLE where_subselect +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2 +; +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2 +); +field1 field2 +10 7 +11 1 +12 5 +13 3 +DROP TABLE t1; +DROP TABLE where_subselect; +# End of Bug #58782 diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 95caa6853b8..ce0793b70a6 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2403,7 +2403,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra @@ -2421,7 +2421,7 @@ AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; id select_type table type possible_keys key key_len ref rows Extra @@ -2757,7 +2757,7 @@ EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra x x x x x x x x x Impossible WHERE noticed after reading const tables -x x x x x x x x x Using index +x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL @@ -2829,7 +2829,7 @@ EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra x x x x x x x x x Impossible WHERE noticed after reading const tables -x x x x x x x x x Using index +x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL @@ -2908,7 +2908,7 @@ EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra x x x x x x x x x Impossible WHERE noticed after reading const tables -x x x x x x x x x Using index +x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index b8eda092291..b93d15f7bef 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -313,7 +313,7 @@ alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where +1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL REF Using sort_union(i3,i2); Using where select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); key1 key2 key3 31 31 31 @@ -777,4 +777,19 @@ commit; select * from t1 where t1.zone_id=830 AND modified=9; pk zone_id modified drop table t0, t1; +# +# MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join +# +CREATE TABLE t1 ( +a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d'); +SELECT ta.* FROM t1 AS ta, t1 AS tb +WHERE ( tb.b != ta.b OR tb.a = ta.a ) +AND ( tb.b = ta.c OR tb.b = ta.b ); +a b c +8 v v +8 m m +9 d d +DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index bf151a872cf..0b6959d15a9 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1146,7 +1146,7 @@ alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where +1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL REF Using sort_union(i3,i2); Using where select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); key1 key2 key3 31 31 31 diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 655303be7f4..7110f541fb7 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -327,9 +327,11 @@ select row_count(); row_count() 3 insert ignore into t1 values (1, 1); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' select row_count(); row_count() -0 +-1 replace into t1 values (1, 11); select row_count(); row_count() @@ -371,7 +373,15 @@ drop table t1,t2; create table t1 (id int primary key auto_increment, data int, unique(data)); insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); +Warnings: +Warning 1062 Duplicate entry '110' for key 'data' +Warning 1062 Duplicate entry '120' for key 'data' +Warning 1062 Duplicate entry '100' for key 'data' insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150); +Warnings: +Warning 1062 Duplicate entry '110' for key 'data' +Warning 1062 Duplicate entry '120' for key 'data' +Warning 1062 Duplicate entry '100' for key 'data' select * from t1 order by id; id data 1 100 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index c952b914167..8bfc4e9215e 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -6,6 +6,10 @@ insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1; insert into t2 (payoutID) SELECT payoutID+10 FROM t1; ERROR 23000: Duplicate entry '16' for key 'PRIMARY' insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; +Warnings: +Warning 1062 Duplicate entry '16' for key 'PRIMARY' +Warning 1062 Duplicate entry '16' for key 'PRIMARY' +Warning 1062 Duplicate entry '22' for key 'PRIMARY' select * from t2; payoutID 1 diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index c3acea5d11f..1987c5c0559 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -172,11 +172,15 @@ DROP TABLE t2; create table t1 (a int not null unique) engine=myisam; insert into t1 values (1),(2); insert ignore into t1 select 1 on duplicate key update a=2; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' select * from t1; a 1 2 insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' select * from t1; a 1 diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index e215caadb1c..e63afeab126 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -599,7 +599,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; RES diff --git a/mysql-test/r/last_value.result b/mysql-test/r/last_value.result new file mode 100644 index 00000000000..6222eacd4f0 --- /dev/null +++ b/mysql-test/r/last_value.result @@ -0,0 +1,73 @@ +drop table if exists t1; +drop database if exists mysqltest; +CREATE TABLE t1 (a INT, b INT, c INT, d INT); +INSERT INTO t1 VALUES (1,3,0,NULL),(2,2,0,NULL),(3,4,0,NULL),(4,2,0,NULL); +SELECT * FROM t1; +a b c d +1 3 0 NULL +2 2 0 NULL +3 4 0 NULL +4 2 0 NULL +UPDATE t1 SET c=LAST_VALUE(@last_a:=a,@last_b:=b,@last_c:=c,1), d=4211 WHERE c=0 ORDER BY b DESC LIMIT 1; +SELECT @last_a, @last_b, @last_c; +@last_a @last_b @last_c +3 4 0 +SELECT * FROM t1; +a b c d +1 3 0 NULL +2 2 0 NULL +3 4 1 4211 +4 2 0 NULL +DROP TABLE t1; +SELECT LAST_VALUE(@last_a:=1,@last_b:=1); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def LAST_VALUE(@last_a:=1,@last_b:=1) 8 1 1 N 32897 0 63 +LAST_VALUE(@last_a:=1,@last_b:=1) +1 +select @last_b; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def @last_b 8 20 1 Y 32896 0 63 +@last_b +1 +SELECT LAST_VALUE(@last_a:=1,@last_b:=1.0); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def LAST_VALUE(@last_a:=1,@last_b:=1.0) 246 4 3 N 32897 1 63 +LAST_VALUE(@last_a:=1,@last_b:=1.0) +1.0 +select @last_b; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def @last_b 246 83 3 Y 32896 30 63 +@last_b +1.0 +SELECT LAST_VALUE(@last_a:=1,@last_b:="hello"); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def LAST_VALUE(@last_a:=1,@last_b:="hello") 253 5 5 N 1 31 8 +LAST_VALUE(@last_a:=1,@last_b:="hello") +hello +select @last_b; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def @last_b 250 16777215 5 Y 0 31 8 +@last_b +hello +SELECT date(LAST_VALUE(@last_a:=1,@last_b:="2001-02-03")); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def date(LAST_VALUE(@last_a:=1,@last_b:="2001-02-03")) 10 10 10 Y 128 0 63 +date(LAST_VALUE(@last_a:=1,@last_b:="2001-02-03")) +2001-02-03 +select @last_b; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def @last_b 250 16777215 10 Y 0 31 8 +@last_b +2001-02-03 +SELECT LAST_VALUE(@last_a:=1,@last_b:="2001-02-03",NULL); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def LAST_VALUE(@last_a:=1,@last_b:="2001-02-03",NULL) 6 0 0 Y 32896 0 63 +LAST_VALUE(@last_a:=1,@last_b:="2001-02-03",NULL) +NULL +select @last_b; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def @last_b 250 16777215 10 Y 0 31 8 +@last_b +2001-02-03 +SELECT LAST_VALUE(); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index 3a5212818ef..f4242f17a14 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -679,7 +679,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Distinct 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) SELECT DISTINCT a AS field1 FROM t1, t2 WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index ee0e63706a1..c4c8216c14a 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -5,8 +5,8 @@ Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 Warning 1265 Data truncated for column 'd' at row 1 -Warning 1264 Out of range value for column 'a' at row 2 -Warning 1264 Out of range value for column 'b' at row 2 +Warning 1265 Data truncated for column 'a' at row 2 +Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 2 load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; SELECT * from t1; @@ -20,7 +20,7 @@ load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated Warnings: Warning 1265 Data truncated for column 'c' at row 1 Warning 1265 Data truncated for column 'd' at row 1 -Warning 1264 Out of range value for column 'b' at row 2 +Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 2 SELECT * from t1; a b c d diff --git a/mysql-test/r/mdev375.result b/mysql-test/r/mdev375.result new file mode 100644 index 00000000000..32580804686 --- /dev/null +++ b/mysql-test/r/mdev375.result @@ -0,0 +1,14 @@ +SET GLOBAL log_warnings=4; +SET GLOBAL max_connections=2; +SELECT 1; +1 +1 +SELECT 2; +2 +2 +ERROR HY000: Too many connections +SELECT 0; +0 +0 +SET GLOBAL log_warnings=default; +SET GLOBAL max_connections=default; diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e5f5857d2c9..6f90555325b 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -702,6 +702,8 @@ id 2 99 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); +Warnings: +Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' commit; select id,id3 from t1; id id3 diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index 03d212dbc04..bd50df7c40e 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -278,6 +278,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); diff --git a/mysql-test/r/mysqlbinlog-innodb.result b/mysql-test/r/mysqlbinlog-innodb.result index fed2c8a8651..41d15bc32d3 100644 --- a/mysql-test/r/mysqlbinlog-innodb.result +++ b/mysql-test/r/mysqlbinlog-innodb.result @@ -34,7 +34,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; INSERT INTO t1 VALUES (1) /*!*/; @@ -65,7 +65,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use foo/*!*/; +use `foo`/*!*/; SET TIMESTAMP=1000000000/*!*/; INSERT INTO t1 VALUES (1) /*!*/; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 43226436b6d..63b0ea2a71a 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -18,7 +18,7 @@ flush logs; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -111,7 +111,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`word`) /*!*/; @@ -202,7 +202,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; insert into t1 values ("Alas") /*!*/; @@ -219,7 +219,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -312,7 +312,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`word`) /*!*/; @@ -403,7 +403,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; insert into t1 values ("Alas") /*!*/; @@ -427,7 +427,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1108844555/*!*/; insert t1 values (1) /*!*/; @@ -445,7 +445,7 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1108844555/*!*/; insert t1 values (1) /*!*/; @@ -498,7 +498,7 @@ ERROR 42000: PROCEDURE test.p1 does not exist /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -548,7 +548,7 @@ flush logs; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -725,7 +725,7 @@ FLUSH LOGS; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1253783037/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -825,7 +825,7 @@ BEGIN SET TIMESTAMP=1266652094/*!*/; SavePoint mixed_cases /*!*/; -use db1/*!*/; +use `db1`/*!*/; SET TIMESTAMP=1266652094/*!*/; INSERT INTO db1.t2 VALUES("in savepoint mixed_cases") /*!*/; @@ -892,6 +892,7 @@ DROP DATABASE test1; FLUSH LOGS; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000002 # Binlog_checkpoint # # master-bin.000002 master-bin.000002 # Query # # CREATE DATABASE test1 master-bin.000002 # Query # # use `test1`; CREATE TABLE t1(id int) master-bin.000002 # Query # # DROP DATABASE test1 diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result index 52a589ca7c1..bf65bab602d 100644 --- a/mysql-test/r/mysqlbinlog2.result +++ b/mysql-test/r/mysqlbinlog2.result @@ -19,7 +19,7 @@ insert into t1 values(null, "f"); /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -103,7 +103,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=1/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; insert into t1 values(null, "a") /*!*/; @@ -172,7 +172,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -209,7 +209,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -263,7 +263,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -289,7 +289,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -326,7 +326,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -369,7 +369,7 @@ flush logs; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -445,7 +445,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -474,7 +474,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=1/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; insert into t1 values(null, "a") /*!*/; @@ -535,7 +535,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -564,7 +564,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -605,7 +605,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -622,7 +622,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -697,7 +697,6 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -SET INSERT_ID=6/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -720,7 +719,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -761,7 +760,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -778,7 +777,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -820,7 +819,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -904,7 +903,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=1/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; insert into t1 values(null, "a") /*!*/; @@ -972,7 +971,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -1009,7 +1008,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -1062,7 +1061,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -1088,7 +1087,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -1125,7 +1124,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -1167,7 +1166,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -1243,7 +1242,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -1272,7 +1271,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=1/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; insert into t1 values(null, "a") /*!*/; @@ -1333,7 +1332,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -1361,7 +1360,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -1402,7 +1401,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -1419,7 +1418,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -1483,17 +1482,6 @@ COMMIT /*!*/; DELIMITER ; DELIMITER /*!*/; -SET TIMESTAMP=1579609943/*!*/; -SET @@session.pseudo_thread_id=999999999/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; -SET @@session.sql_mode=0/*!*/; -SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; -/*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; -SET @@session.lc_time_names=0/*!*/; -SET @@session.collation_database=DEFAULT/*!*/; -BEGIN -/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -1516,7 +1504,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=3/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; @@ -1557,7 +1545,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET INSERT_ID=6/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609943/*!*/; insert into t1 values(null, "f") /*!*/; @@ -1574,7 +1562,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -1616,7 +1604,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1579609942/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row.result b/mysql-test/r/mysqlbinlog_row.result index 7ae1ea36899..6361cdadb32 100644 --- a/mysql-test/r/mysqlbinlog_row.result +++ b/mysql-test/r/mysqlbinlog_row.result @@ -335,8 +335,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_innodb.result b/mysql-test/r/mysqlbinlog_row_innodb.result index 00a3eb79b64..4be266d586c 100644 --- a/mysql-test/r/mysqlbinlog_row_innodb.result +++ b/mysql-test/r/mysqlbinlog_row_innodb.result @@ -2252,8 +2252,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -3875,8 +3877,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -4242,8 +4246,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -4803,8 +4809,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_myisam.result b/mysql-test/r/mysqlbinlog_row_myisam.result index 7b0112d0a91..d4062565320 100644 --- a/mysql-test/r/mysqlbinlog_row_myisam.result +++ b/mysql-test/r/mysqlbinlog_row_myisam.result @@ -2252,8 +2252,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -3897,8 +3899,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -4270,8 +4274,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; @@ -4841,8 +4847,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_trans.result b/mysql-test/r/mysqlbinlog_row_trans.result index 5c96db7d19b..85106ab9684 100644 --- a/mysql-test/r/mysqlbinlog_row_trans.result +++ b/mysql-test/r/mysqlbinlog_row_trans.result @@ -131,8 +131,10 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 2b34c4da8af..5b8e6ea3348 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -355,8 +355,7 @@ The following options may be given as the first argument: --max-binlog-cache-size=# Sets the total size of the transactional cache --max-binlog-size=# Binary log will be rotated automatically when the size - exceeds this value. Will also apply to relay logs if - max_relay_log_size is 0 + exceeds this value. --max-binlog-stmt-cache-size=# Sets the total size of the statement cache --max-connect-errors=# @@ -382,9 +381,9 @@ The following options may be given as the first argument: --max-prepared-stmt-count=# Maximum number of prepared statements in the server --max-relay-log-size=# - If non-zero: relay log will be rotated automatically when - the size exceeds this value; if zero: when the size - exceeds max_binlog_size + relay log will be rotated automatically when the size + exceeds this value. If 0 are startup, it's set to + max_binlog_size --max-seeks-for-key=# Limit assumed max number of seeks when looking up rows based on a key @@ -765,7 +764,7 @@ The following options may be given as the first argument: partial matching --rpl-recovery-rank=# Unused, will be removed - --safe-mode Skip some optimize stages (for testing). + --safe-mode Skip some optimize stages (for testing). Deprecated. --safe-user-create Don't allow new user creation by the user who has no write privileges to the mysql.user table. --secure-auth Disallow authentication for accounts that have old @@ -805,8 +804,11 @@ The following options may be given as the first argument: --slave-load-tmpdir=name The location where the slave should put its temporary files when replicating a LOAD DATA INFILE command + --slave-max-allowed-packet=# + The maximum packet length to sent successfully from the + master to slave. --slave-net-timeout=# - Number of seconds to wait for more data from a + Number of seconds to wait for more data from any master/slave connection before aborting the read --slave-skip-errors=name Tells the slave thread to continue replication when a @@ -991,7 +993,6 @@ key-cache-age-threshold 300 key-cache-block-size 1024 key-cache-division-limit 100 key-cache-segments 0 -language MYSQL_SHAREDIR/ large-pages FALSE lc-messages en_US lc-messages-dir MYSQL_SHAREDIR/ @@ -1034,7 +1035,7 @@ max-join-size 18446744073709551615 max-length-for-sort-data 1024 max-long-data-size 1048576 max-prepared-stmt-count 16382 -max-relay-log-size 0 +max-relay-log-size 1073741824 max-seeks-for-key 18446744073709551615 max-sort-length 1024 max-sp-recursion-depth 0 @@ -1101,7 +1102,7 @@ performance-schema-max-rwlock-instances 1000000 performance-schema-max-socket-classes 10 performance-schema-max-socket-instances 1000 performance-schema-max-stage-classes 150 -performance-schema-max-statement-classes 170 +performance-schema-max-statement-classes 173 performance-schema-max-table-handles 10000 performance-schema-max-table-instances 1000 performance-schema-max-thread-classes 50 @@ -1155,6 +1156,7 @@ skip-show-database FALSE skip-slave-start FALSE slave-compressed-protocol FALSE slave-exec-mode STRICT +slave-max-allowed-packet 1073741824 slave-net-timeout 3600 slave-skip-errors (No default value) slave-sql-verify-checksum TRUE diff --git a/mysql-test/r/mysqldump-max.result b/mysql-test/r/mysqldump-max.result index 6722f308358..fb0be20c119 100644 --- a/mysql-test/r/mysqldump-max.result +++ b/mysql-test/r/mysqldump-max.result @@ -332,12 +332,12 @@ a b 2 1 DROP TABLE t1; DROP TABLE t2; -SHOW BINLOG EVENTS LIMIT 6,3; +SHOW BINLOG EVENTS LIMIT 7,3; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 663 Query 1 731 BEGIN -master-bin.000001 731 Query 1 828 use `test`; INSERT INTO t2 VALUES (1,0), (2,0) -master-bin.000001 828 Xid 1 855 COMMIT /* XID */ --- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=855; +master-bin.000001 704 Query 1 772 BEGIN +master-bin.000001 772 Query 1 869 use `test`; INSERT INTO t2 VALUES (1,0), (2,0) +master-bin.000001 869 Xid 1 896 COMMIT /* XID */ +-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=896; SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 6f1b43b7b2b..b58a904bc1f 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2073,7 +2073,7 @@ DROP TABLE IF EXISTS `v2`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( - `a` varchar(30) + `a` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE IF EXISTS `v2`*/; @@ -2167,7 +2167,7 @@ DROP TABLE IF EXISTS `v1`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( - `a` int(11) + `a` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE IF EXISTS `v1`*/; @@ -2241,7 +2241,7 @@ DROP TABLE IF EXISTS `v2`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( - `a` varchar(30) + `a` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE IF EXISTS `v2`*/; @@ -2355,9 +2355,9 @@ DROP TABLE IF EXISTS `v1`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( - `a` int(11), - `b` int(11), - `c` varchar(30) + `a` tinyint NOT NULL, + `b` tinyint NOT NULL, + `c` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v2`; @@ -2365,7 +2365,7 @@ DROP TABLE IF EXISTS `v2`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( - `a` int(11) + `a` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v3`; @@ -2373,9 +2373,9 @@ DROP TABLE IF EXISTS `v3`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v3` ( - `a` int(11), - `b` int(11), - `c` varchar(30) + `a` tinyint NOT NULL, + `b` tinyint NOT NULL, + `c` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE IF EXISTS `v1`*/; @@ -3112,9 +3112,9 @@ DROP TABLE IF EXISTS `v0`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v0` ( - `a` int(11), - `b` varchar(32), - `c` varchar(32) + `a` tinyint NOT NULL, + `b` tinyint NOT NULL, + `c` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v1`; @@ -3122,9 +3122,9 @@ DROP TABLE IF EXISTS `v1`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( - `a` int(11), - `b` varchar(32), - `c` varchar(32) + `a` tinyint NOT NULL, + `b` tinyint NOT NULL, + `c` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v2`; @@ -3132,9 +3132,9 @@ DROP TABLE IF EXISTS `v2`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( - `a` int(11), - `b` varchar(32), - `c` varchar(32) + `a` tinyint NOT NULL, + `b` tinyint NOT NULL, + `c` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; @@ -3514,7 +3514,7 @@ DROP TABLE IF EXISTS `v1`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( - `id` int(11) + `id` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; @@ -3574,7 +3574,7 @@ USE `mysqldump_views`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `nasishnasifu` ( - `id` bigint(20) unsigned + `id` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; @@ -3967,7 +3967,7 @@ DROP TABLE IF EXISTS `v2`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( - `c` int(11) + `c` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE IF EXISTS `v2`*/; @@ -4384,7 +4384,7 @@ DROP TABLE IF EXISTS `v1`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( - `id` int(11) + `id` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index 25345227d59..d08ffd96a04 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -232,7 +232,7 @@ mysqltest: At line 2: Spurious text after `query` expression mysqltest: At line 1: Missing argument(s) to 'error' mysqltest: At line 1: Missing argument(s) to 'error' mysqltest: At line 1: The sqlstate definition must start with an uppercase S -mysqltest: At line 1: The error name definition must start with an uppercase E +mysqltest: At line 1: The error name definition must start with an uppercase E or W mysqltest: At line 1: Invalid argument to error: '9eeeee' - the errno may only consist of digits[0-9] mysqltest: At line 1: Invalid argument to error: '1sssss' - the errno may only consist of digits[0-9] mysqltest: At line 1: The sqlstate must be exactly 5 chars long @@ -690,6 +690,7 @@ Got one of the listed errors insert into t1 values ("Abcd"); Got one of the listed errors garbage; +SELECT * FROM non_existing_table; drop table t2; create table t1 ( f1 char(10)); insert into t1 values ("Abcd"); diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 35c6ebe1442..ba79011f53e 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -1,6 +1,8 @@ drop table if exists t1,t2; create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' explain select * from t1 where a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 const 3 Using where; Using index diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index d320b5b669f..1ded1e90314 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1439,6 +1439,7 @@ CALL mtr.add_suppression("Out of sort memory"); select * from t1 order by b; ERROR HY001: Out of sort memory, consider increasing server sort buffer size drop table t1; +set session sort_buffer_size= 30000; # # Bug #39844: Query Crash Mysql Server 5.0.67 # @@ -1533,6 +1534,890 @@ ppfcz1 DE ppfcz1 14 8 57.5 ppfcz1 DE ppfcz1 14 9 59.5 ppfcz1 DE ppfcz1 14 10 61.5 DROP TABLE t1,t2,t3; +# +# WL#1393 - Optimizing filesort with small limit +# +CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); +INSERT INTO t1(f1, f2) VALUES +(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), +(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), +(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), +(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), +(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), +(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), +(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), +(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), +(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), +(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), +(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), +(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), +(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), +(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), +(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), +(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), +(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), +(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), +(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), +(96,"96"),(97,"97"),(98,"98"),(99,"99"); +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; +f0 f1 f2 +1 0 0 +2 1 1 +3 2 2 +4 3 3 +5 4 4 +6 5 5 +7 6 6 +8 7 7 +9 8 8 +10 9 9 +11 10 10 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +32 31 31 +33 32 32 +34 33 33 +35 34 34 +36 35 35 +37 36 36 +38 37 37 +39 38 38 +40 39 39 +41 40 40 +42 41 41 +43 42 42 +44 43 43 +45 44 44 +46 45 45 +47 46 46 +48 47 47 +49 48 48 +50 49 49 +51 50 50 +52 51 51 +53 52 52 +54 53 53 +55 54 54 +56 55 55 +57 56 56 +58 57 57 +59 58 58 +60 59 59 +61 60 60 +62 61 61 +63 62 62 +64 63 63 +65 64 64 +66 65 65 +67 66 66 +68 67 67 +69 68 68 +70 69 69 +71 70 70 +72 71 71 +73 72 72 +74 73 73 +75 74 74 +76 75 75 +77 76 76 +78 77 77 +79 78 78 +80 79 79 +81 80 80 +82 81 81 +83 82 82 +84 83 83 +85 84 84 +86 85 85 +87 86 86 +88 87 87 +89 88 88 +90 89 89 +91 90 90 +92 91 91 +93 92 92 +94 93 93 +95 94 94 +96 95 95 +97 96 96 +98 97 97 +99 98 98 +100 99 99 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +2 1 1 +3 2 2 +4 3 3 +5 4 4 +6 5 5 +7 6 6 +8 7 7 +9 8 8 +10 9 9 +11 10 10 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; +f0 f1 f2 +100 99 99 +99 98 98 +98 97 97 +97 96 96 +96 95 95 +95 94 94 +94 93 93 +93 92 92 +92 91 91 +91 90 90 +10 9 9 +90 89 89 +89 88 88 +88 87 87 +87 86 86 +86 85 85 +85 84 84 +84 83 83 +83 82 82 +82 81 81 +81 80 80 +9 8 8 +80 79 79 +79 78 78 +78 77 77 +77 76 76 +76 75 75 +75 74 74 +74 73 73 +73 72 72 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +set sort_buffer_size= 32768; +CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); +INSERT INTO tmp SELECT f1, f2 FROM t1; +INSERT INTO t1(f1, f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1, f2 FROM t1; +INSERT INTO t1(f1, f2) SELECT * FROM tmp; +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; +f0 f1 f2 +100 99 99 +200 99 99 +300 99 99 +400 99 99 +500 99 99 +99 98 98 +199 98 98 +299 98 98 +399 98 98 +499 98 98 +98 97 97 +198 97 97 +298 97 97 +398 97 97 +498 97 97 +97 96 96 +197 96 96 +297 96 96 +397 96 96 +497 96 96 +96 95 95 +196 95 95 +296 95 95 +396 95 95 +496 95 95 +95 94 94 +195 94 94 +295 94 94 +395 94 94 +495 94 94 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +set sort_buffer_size= 32768; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 +ORDER BY f1, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +SELECT FOUND_ROWS(); +FOUND_ROWS() +500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 +ORDER BY f1, f0 LIMIT 0; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +set sort_buffer_size= 327680; +SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30; +f0 f1 f2 f1 f2 +1 0 0 0 0 +1 0 0 0 0 +1 0 0 0 0 +101 0 0 0 0 +101 0 0 0 0 +101 0 0 0 0 +201 0 0 0 0 +201 0 0 0 0 +201 0 0 0 0 +301 0 0 0 0 +301 0 0 0 0 +301 0 0 0 0 +401 0 0 0 0 +401 0 0 0 0 +401 0 0 0 0 +2 1 1 1 1 +2 1 1 1 1 +2 1 1 1 1 +102 1 1 1 1 +102 1 1 1 1 +102 1 1 1 1 +202 1 1 1 1 +202 1 1 1 1 +202 1 1 1 1 +302 1 1 1 1 +302 1 1 1 1 +302 1 1 1 1 +402 1 1 1 1 +402 1 1 1 1 +402 1 1 1 1 +SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +3 2 2 2 2 +3 2 2 2 2 +3 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +4 3 3 3 3 +4 3 3 3 3 +4 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +3 2 2 2 2 +3 2 2 2 2 +3 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +4 3 3 3 3 +4 3 3 3 3 +4 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 +WHERE t1.f2>20 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +24 23 23 23 23 +24 23 23 23 23 +24 23 23 23 23 +124 23 23 23 23 +124 23 23 23 23 +124 23 23 23 23 +224 23 23 23 23 +224 23 23 23 23 +224 23 23 23 23 +324 23 23 23 23 +324 23 23 23 23 +324 23 23 23 23 +424 23 23 23 23 +424 23 23 23 23 +424 23 23 23 23 +25 24 24 24 24 +25 24 24 24 24 +25 24 24 24 24 +125 24 24 24 24 +125 24 24 24 24 +125 24 24 24 24 +225 24 24 24 24 +225 24 24 24 24 +225 24 24 24 24 +325 24 24 24 24 +325 24 24 24 24 +325 24 24 24 24 +425 24 24 24 24 +425 24 24 24 24 +425 24 24 24 24 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1185 +CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; +SELECT * FROM v1; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +drop view v1; +CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; +SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +11 10 10 +111 10 10 +211 10 10 +311 10 10 +411 10 10 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; +SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 +LIMIT 30; +f0 f1 f2 f0 f1 f2 +1 0 0 1 0 0 +1 0 0 101 0 0 +1 0 0 201 0 0 +1 0 0 301 0 0 +1 0 0 401 0 0 +101 0 0 1 0 0 +101 0 0 101 0 0 +101 0 0 201 0 0 +101 0 0 301 0 0 +101 0 0 401 0 0 +201 0 0 1 0 0 +201 0 0 101 0 0 +201 0 0 201 0 0 +201 0 0 301 0 0 +201 0 0 401 0 0 +301 0 0 1 0 0 +301 0 0 101 0 0 +301 0 0 201 0 0 +301 0 0 301 0 0 +301 0 0 401 0 0 +401 0 0 1 0 0 +401 0 0 101 0 0 +401 0 0 201 0 0 +401 0 0 301 0 0 +401 0 0 401 0 0 +2 1 1 2 1 1 +2 1 1 102 1 1 +2 1 1 202 1 1 +2 1 1 302 1 1 +2 1 1 402 1 1 +SELECT floor(f1/10) f3, count(f2) FROM t1 +GROUP BY 1 ORDER BY 2,1 LIMIT 5; +f3 count(f2) +0 50 +1 50 +2 50 +3 50 +4 50 +SELECT floor(f1/10) f3, count(f2) FROM t1 +GROUP BY 1 ORDER BY 2,1 LIMIT 0; +f3 count(f2) +CREATE PROCEDURE wl1393_sp_test() +BEGIN +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 15 OFFSET 15; +SELECT FOUND_ROWS(); +SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; +END| +CALL wl1393_sp_test()| +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +f0 f1 f2 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +f0 f1 f2 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +FOUND_ROWS() +445 +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +11 10 10 +111 10 10 +211 10 10 +311 10 10 +411 10 10 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +DROP PROCEDURE wl1393_sp_test| +SELECT d1.f1, d1.f2 FROM t1 +LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 +ORDER BY d1.f2 DESC LIMIT 30; +f1 f2 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +4 4 +4 4 +4 4 +4 4 +4 4 +SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1, tmp; +DROP VIEW v1, v2; +# end of WL#1393 - Optimizing filesort with small limit +# +# Bug #58761 +# Crash in Field::is_null in field.h on subquery in WHERE clause +# +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +col_int_key INT DEFAULT NULL, +col_varchar_key VARCHAR(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key,col_int_key) +); +INSERT INTO t1 VALUES (27,7,'x'); +INSERT INTO t1 VALUES (28,6,'m'); +INSERT INTO t1 VALUES (29,4,'c'); +CREATE TABLE where_subselect +SELECT DISTINCT `pk` AS field1 , `pk` AS field2 +FROM t1 AS alias1 +WHERE alias1 . `col_int_key` > 229 +OR alias1 . `col_varchar_key` IS NOT NULL +GROUP BY field1, field2 +; +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( +SELECT DISTINCT `pk` AS field1 , `pk` AS field2 +FROM t1 AS alias1 +WHERE alias1 . `col_int_key` > 229 +OR alias1 . `col_varchar_key` IS NOT NULL +GROUP BY field1, field2 +); +field1 field2 +27 27 +28 28 +29 29 +DROP TABLE t1; +DROP TABLE where_subselect; +# End of Bug #58761 CREATE TABLE t1 ( id1 INT NULL, id2 INT NOT NULL, @@ -1813,3 +2698,135 @@ Warnings: Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b` drop table t1,t2; End of 5.2 tests +# +# Bug mdev-449: ORDER BY with small sort_buffer_size +# +CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); +INSERT INTO t1(f1, f2) VALUES +(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), +(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), +(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), +(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), +(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), +(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), +(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), +(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), +(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), +(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), +(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), +(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), +(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), +(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), +(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), +(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), +(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), +(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), +(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), +(96,"96"),(97,"97"),(98,"98"),(99,"99"); +set @save_sort_buffer_size= @@sort_buffer_size; +set sort_buffer_size= 2000; +SELECT * FROM t1 ORDER BY f1 DESC, f0; +f0 f1 f2 +100 99 99 +99 98 98 +98 97 97 +97 96 96 +96 95 95 +95 94 94 +94 93 93 +93 92 92 +92 91 91 +91 90 90 +90 89 89 +89 88 88 +88 87 87 +87 86 86 +86 85 85 +85 84 84 +84 83 83 +83 82 82 +82 81 81 +81 80 80 +80 79 79 +79 78 78 +78 77 77 +77 76 76 +76 75 75 +75 74 74 +74 73 73 +73 72 72 +72 71 71 +71 70 70 +70 69 69 +69 68 68 +68 67 67 +67 66 66 +66 65 65 +65 64 64 +64 63 63 +63 62 62 +62 61 61 +61 60 60 +60 59 59 +59 58 58 +58 57 57 +57 56 56 +56 55 55 +55 54 54 +54 53 53 +53 52 52 +52 51 51 +51 50 50 +50 49 49 +49 48 48 +48 47 47 +47 46 46 +46 45 45 +45 44 44 +44 43 43 +43 42 42 +42 41 41 +41 40 40 +40 39 39 +39 38 38 +38 37 37 +37 36 36 +36 35 35 +35 34 34 +34 33 33 +33 32 32 +32 31 31 +31 30 30 +30 29 29 +29 28 28 +28 27 27 +27 26 26 +26 25 25 +25 24 24 +24 23 23 +23 22 22 +22 21 21 +21 20 20 +20 19 19 +19 18 18 +18 17 17 +17 16 16 +16 15 15 +15 14 14 +14 13 13 +13 12 12 +12 11 11 +11 10 10 +10 9 9 +9 8 8 +8 7 7 +7 6 6 +6 5 5 +5 4 4 +4 3 3 +3 2 2 +2 1 1 +1 0 0 +set sort_buffer_size= @save_sort_buffer_size; +DROP TABLE t1; +End of 5.3 tests diff --git a/mysql-test/r/order_by_sortkey.result b/mysql-test/r/order_by_sortkey.result new file mode 100644 index 00000000000..717780f0af2 --- /dev/null +++ b/mysql-test/r/order_by_sortkey.result @@ -0,0 +1,159 @@ +CREATE TABLE t1( +f0 int auto_increment PRIMARY KEY, +f1 int, +f2 varchar(200) +); +INSERT INTO t1(f1, f2) VALUES +(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), +(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), +(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), +(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), +(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), +(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), +(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), +(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), +(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), +(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), +(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), +(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), +(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), +(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), +(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), +(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), +(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), +(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), +(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), +(96,"96"),(97,"97"),(98,"98"),(99,"99"); +CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +set sort_buffer_size= 32768; +FLUSH STATUS; +SHOW SESSION STATUS LIKE 'Sort%'; +Variable_name Value +Sort_merge_passes 0 +Sort_range 0 +Sort_rows 0 +Sort_scan 0 +SELECT * FROM t1 ORDER BY f2 LIMIT 100; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +501 0 0 +601 0 0 +701 0 0 +801 0 0 +901 0 0 +1001 0 0 +1101 0 0 +1201 0 0 +1301 0 0 +1401 0 0 +1501 0 0 +1601 0 0 +1701 0 0 +1801 0 0 +1901 0 0 +2001 0 0 +2101 0 0 +2201 0 0 +2301 0 0 +2401 0 0 +2501 0 0 +2601 0 0 +2701 0 0 +2801 0 0 +2901 0 0 +3001 0 0 +3101 0 0 +3201 0 0 +3301 0 0 +3401 0 0 +3501 0 0 +3601 0 0 +3701 0 0 +3801 0 0 +3901 0 0 +4001 0 0 +4101 0 0 +4201 0 0 +4301 0 0 +4401 0 0 +4501 0 0 +4601 0 0 +4701 0 0 +4801 0 0 +4901 0 0 +5001 0 0 +5101 0 0 +5201 0 0 +5301 0 0 +5401 0 0 +5501 0 0 +5601 0 0 +5701 0 0 +5801 0 0 +5901 0 0 +6001 0 0 +6101 0 0 +6201 0 0 +6301 0 0 +6401 0 0 +6501 0 0 +6601 0 0 +6701 0 0 +6801 0 0 +6901 0 0 +7001 0 0 +7101 0 0 +7201 0 0 +7301 0 0 +7401 0 0 +7501 0 0 +7601 0 0 +7701 0 0 +7801 0 0 +7901 0 0 +8001 0 0 +8101 0 0 +8201 0 0 +8301 0 0 +8401 0 0 +8501 0 0 +8601 0 0 +8701 0 0 +8801 0 0 +8901 0 0 +9001 0 0 +9101 0 0 +9201 0 0 +9301 0 0 +9401 0 0 +9501 0 0 +9601 0 0 +9701 0 0 +9801 0 0 +9901 0 0 +SHOW SESSION STATUS LIKE 'Sort%'; +Variable_name Value +Sort_merge_passes 0 +Sort_range 0 +Sort_rows 100 +Sort_scan 1 +DROP TABLE t1, tmp; diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result index 10700d0ba73..54378f16d49 100644 --- a/mysql-test/r/parser.result +++ b/mysql-test/r/parser.result @@ -441,7 +441,7 @@ select master_pos_wait(); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' select master_pos_wait(1); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' -select master_pos_wait(1, 2, 3, 4); +select master_pos_wait(1, 2, 3, 4, 5); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' select rand(1, 2, 3); ERROR 42000: Incorrect parameter count in the call to native function 'rand' diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 431c5dda116..40586b8d54b 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2473,3 +2473,17 @@ SELECT * FROM vtmp; 1 DROP VIEW vtmp; DROP TABLE t1; +# +# MDEV-365 "Got assertion when doing alter table on a partition" +# +CREATE TABLE t1 ( i INT ) ENGINE=Aria PARTITION BY HASH(i) PARTITIONS 2; +INSERT INTO t1 VALUES (1),(2),(2),(3),(4); +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +SELECT * from t1 order by i; +i +1 +2 +2 +3 +4 +DROP TABLE t1; diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result index ac2da9bb78a..cd579d00952 100644 --- a/mysql-test/r/partition_cache.result +++ b/mysql-test/r/partition_cache.result @@ -27,7 +27,7 @@ a 3 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 1 drop table t1; commit; create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; @@ -50,7 +50,7 @@ a 2 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 @@ -69,7 +69,7 @@ a 2 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 6 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 @@ -93,14 +93,14 @@ a 2 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 2 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 0 +Qcache_hits 1 commit; show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 2 drop table t3,t2,t1; CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 3; select count(*) from t1; @@ -164,7 +164,7 @@ count(*) 2 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 1 connection connection1 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; count(*) @@ -197,9 +197,9 @@ count(*) 2 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 1 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 0 +Qcache_hits 1 set @@global.query_cache_size = @save_query_cache_size; drop table t2; diff --git a/mysql-test/r/partition_cache_innodb.result b/mysql-test/r/partition_cache_innodb.result new file mode 100644 index 00000000000..0d0abbb096c --- /dev/null +++ b/mysql-test/r/partition_cache_innodb.result @@ -0,0 +1,151 @@ +SET SESSION STORAGE_ENGINE = innodb; +drop table if exists t1; +set @save_query_cache_size = @@global.query_cache_size; +# Test that partitions works with query cache +flush query cache; +SET GLOBAL query_cache_size=1024*1024*512; +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`created_at` datetime NOT NULL, +`cool` tinyint default 0 +); +ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) ( +PARTITION month_2010_4 VALUES LESS THAN (734258), +PARTITION month_2010_5 VALUES LESS THAN (734289), +PARTITION month_max VALUES LESS THAN MAXVALUE +); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `created_at` datetime NOT NULL, + `cool` tinyint(4) DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (TO_DAYS(created_at)) +(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB, + PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB, + PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ +INSERT INTO t1 VALUES (1, now(), 0); +flush status; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +drop table t1; +# Test that sub-partitions works with query cache +flush query cache; +SET GLOBAL query_cache_size=1024*1024*512; +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`created_at` datetime NOT NULL, +`cool` tinyint default 0 +) +PARTITION BY RANGE (TO_DAYS(created_at)) +subpartition by hash(cool) subpartitions 3 ( +PARTITION month_2010_4 VALUES LESS THAN (734258), +PARTITION month_2010_5 VALUES LESS THAN (734289), +PARTITION month_max VALUES LESS THAN MAXVALUE +); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `created_at` datetime NOT NULL, + `cool` tinyint(4) DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (TO_DAYS(created_at)) +SUBPARTITION BY HASH (cool) +SUBPARTITIONS 3 +(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB, + PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB, + PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ +INSERT INTO t1 VALUES (1, now(), 0); +flush status; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +drop table t1; +# +# MySQL bug#53775 Query on partitioned table returns cached result +# from previous transaction +# +flush query cache; +flush status; +SET GLOBAL query_cache_size=1024*1024*512; +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`created_at` datetime NOT NULL, +`cool` tinyint default 0 +); +ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) ( +PARTITION month_2010_4 VALUES LESS THAN (734258), +PARTITION month_2010_5 VALUES LESS THAN (734289), +PARTITION month_max VALUES LESS THAN MAXVALUE +); +INSERT INTO t1 VALUES (1, now(), 0); +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +BEGIN; +UPDATE `t1` SET `cool` = 1 WHERE `id` = 1; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +1 +ROLLBACK; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +BEGIN; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +ROLLBACK; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +drop table t1; +set @@global.query_cache_size = @save_query_cache_size; diff --git a/mysql-test/r/partition_cache_myisam.result b/mysql-test/r/partition_cache_myisam.result new file mode 100644 index 00000000000..0b617c03590 --- /dev/null +++ b/mysql-test/r/partition_cache_myisam.result @@ -0,0 +1,153 @@ +SET SESSION STORAGE_ENGINE = myisam; +drop table if exists t1; +set @save_query_cache_size = @@global.query_cache_size; +# Test that partitions works with query cache +flush query cache; +SET GLOBAL query_cache_size=1024*1024*512; +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`created_at` datetime NOT NULL, +`cool` tinyint default 0 +); +ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) ( +PARTITION month_2010_4 VALUES LESS THAN (734258), +PARTITION month_2010_5 VALUES LESS THAN (734289), +PARTITION month_max VALUES LESS THAN MAXVALUE +); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `created_at` datetime NOT NULL, + `cool` tinyint(4) DEFAULT '0' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (TO_DAYS(created_at)) +(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM, + PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM, + PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +INSERT INTO t1 VALUES (1, now(), 0); +flush status; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +drop table t1; +# Test that sub-partitions works with query cache +flush query cache; +SET GLOBAL query_cache_size=1024*1024*512; +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`created_at` datetime NOT NULL, +`cool` tinyint default 0 +) +PARTITION BY RANGE (TO_DAYS(created_at)) +subpartition by hash(cool) subpartitions 3 ( +PARTITION month_2010_4 VALUES LESS THAN (734258), +PARTITION month_2010_5 VALUES LESS THAN (734289), +PARTITION month_max VALUES LESS THAN MAXVALUE +); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `created_at` datetime NOT NULL, + `cool` tinyint(4) DEFAULT '0' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (TO_DAYS(created_at)) +SUBPARTITION BY HASH (cool) +SUBPARTITIONS 3 +(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM, + PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM, + PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +INSERT INTO t1 VALUES (1, now(), 0); +flush status; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +drop table t1; +# +# MySQL bug#53775 Query on partitioned table returns cached result +# from previous transaction +# +flush query cache; +flush status; +SET GLOBAL query_cache_size=1024*1024*512; +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`created_at` datetime NOT NULL, +`cool` tinyint default 0 +); +ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) ( +PARTITION month_2010_4 VALUES LESS THAN (734258), +PARTITION month_2010_5 VALUES LESS THAN (734289), +PARTITION month_max VALUES LESS THAN MAXVALUE +); +INSERT INTO t1 VALUES (1, now(), 0); +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +BEGIN; +UPDATE `t1` SET `cool` = 1 WHERE `id` = 1; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +1 +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +1 +BEGIN; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +1 +ROLLBACK; +SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1; +cool +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +drop table t1; +set @@global.query_cache_size = @save_query_cache_size; diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 4d48b70d26a..ec7fd798d4c 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -787,7 +787,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +1 SIMPLE t1 pNULL ref a a 4 const 1 Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1116,7 +1116,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +1 SIMPLE t1 pNULL ref a a 4 const 1 Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1445,7 +1445,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +1 SIMPLE t1 pNULL ref a a 4 const 1 Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 7629775b892..95217d9716a 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -3056,7 +3056,7 @@ DROP TABLE t1; End of 5.1 tests. # -# LP bug#1001500 Crash on the second execution of the PS for +# lp:1001500 Crash on the second execution of the PS for # a query with degenerated conjunctive condition # (see also mysql bug#12582849) # @@ -3996,108 +3996,4 @@ Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 deallocate prepare st; drop table t1; -# -# LP bug#993459 Execution of PS for a query with GROUP BY -# returns wrong result (see also mysql bug#13805127) -# -PREPARE s1 FROM -" -SELECT c1, t2.c2, count(c3) -FROM - ( - SELECT 3 as c2 FROM dual WHERE @x = 1 - UNION - SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 - ) AS t1, - ( - SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual - UNION - SELECT '2012-03-01 02:00:00', 3, 2 FROM dual - UNION - SELECT '2012-03-01 01:00:00', 2, 1 FROM dual - ) AS t2 -WHERE t2.c2 = t1.c2 -GROUP BY c1, c2 -"; - -SET @x = 1; -SELECT c1, t2.c2, count(c3) -FROM -( -SELECT 3 as c2 FROM dual WHERE @x = 1 -UNION -SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 -) AS t1, -( -SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual -UNION -SELECT '2012-03-01 02:00:00', 3, 2 FROM dual -UNION -SELECT '2012-03-01 01:99345900:00', 2, 1 FROM dual -) AS t2 -WHERE t2.c2 = t1.c2 -GROUP BY c1, c2; -c1 c2 count(c3) -2012-03-01 01:00:00 3 1 -2012-03-01 01:99345900:00 2 1 -2012-03-01 02:00:00 3 1 - -EXECUTE s1; -c1 c2 count(c3) -2012-03-01 01:00:00 2 1 -2012-03-01 01:00:00 3 1 -2012-03-01 02:00:00 3 1 - -SET @x = 2; -SELECT c1, t2.c2, count(c3) -FROM -( -SELECT 3 as c2 FROM dual WHERE @x = 1 -UNION -SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 -) AS t1, -( -SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual -UNION -SELECT '2012-03-01 02:00:00', 3, 2 FROM dual -UNION -SELECT '2012-03-01 01:00:00', 2, 1 FROM dual -) AS t2 -WHERE t2.c2 = t1.c2 -GROUP BY c1, c2; -c1 c2 count(c3) -2012-03-01 01:00:00 2 1 - -EXECUTE s1; -c1 c2 count(c3) -2012-03-01 01:00:00 2 1 - -SET @x = 1; -SELECT c1, t2.c2, count(c3) -FROM -( -SELECT 3 as c2 FROM dual WHERE @x = 1 -UNION -SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 -) AS t1, -( -SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual -UNION -SELECT '2012-03-01 02:00:00', 3, 2 FROM dual -UNION -SELECT '2012-03-01 01:00:00', 2, 1 FROM dual -) AS t2 -WHERE t2.c2 = t1.c2 -GROUP BY c1, c2; -c1 c2 count(c3) -2012-03-01 01:00:00 2 1 -2012-03-01 01:00:00 3 1 -2012-03-01 02:00:00 3 1 - -EXECUTE s1; -c1 c2 count(c3) -2012-03-01 01:00:00 2 1 -2012-03-01 01:00:00 3 1 -2012-03-01 02:00:00 3 1 -DEALLOCATE PREPARE s1; # End of 5.3 tests diff --git a/mysql-test/r/replace.result b/mysql-test/r/replace.result index 842302c89ac..59b8565f671 100644 --- a/mysql-test/r/replace.result +++ b/mysql-test/r/replace.result @@ -13,9 +13,9 @@ drop table t1; create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value"); insert into t1 values (126,"first"),(63, "middle"),(0,"last"); insert into t1 values (0,"error"); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 replace into t1 values (0,"error"); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 replace into t1 values (126,"first updated"); replace into t1 values (63,default); select * from t1; diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result new file mode 100644 index 00000000000..bf96ad88f23 --- /dev/null +++ b/mysql-test/r/show_explain.result @@ -0,0 +1,1078 @@ +drop table if exists t0, t1, t2, t3, t4; +drop view if exists v1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int); +insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; +alter table t1 add b int, add c int, add filler char(32); +update t1 set b=a, c=a, filler='fooo'; +alter table t1 add key(a), add key(b); +show explain for 2000000000; +ERROR HY000: Unknown thread id: 2000000000 +show explain for (select max(a) from t0); +ERROR HY000: You may only use constant expressions in this statement +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +show explain for $thr1; +ERROR HY000: Target is not running an EXPLAINable command +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select count(*) from t1 where a < 100000; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index +Warnings: +Note 1003 select count(*) from t1 where a < 100000 +count(*) +1000 +select max(c) from t1 where a < 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition +Warnings: +Note 1003 select max(c) from t1 where a < 10 +max(c) +9 +# We can catch EXPLAIN, too. +set @show_expl_tmp= @@optimizer_switch; +set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; +explain select max(c) from t1 where a < 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan +Warnings: +Note 1003 explain select max(c) from t1 where a < 10 +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan +set optimizer_switch= @show_expl_tmp; +# UNION, first branch +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +explain select a from t0 A union select a+1 from t0 B; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 explain select a from t0 A union select a+1 from t0 B +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +# UNION, second branch +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +explain select a from t0 A union select a+1 from t0 B; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 explain select a from t0 A union select a+1 from t0 B +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +# Uncorrelated subquery, select +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 B) from t0 A where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +Warnings: +Note 1003 select a, (select max(a) from t0 B) from t0 A where a<1 +a (select max(a) from t0 B) +0 9 +# Uncorrelated subquery, explain +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +explain select a, (select max(a) from t0 B) from t0 A where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +Warnings: +Note 1003 explain select a, (select max(a) from t0 B) from t0 A where a<1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +# correlated subquery, select +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, select, while inside the subquery +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain, while inside the subquery +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain, while inside the subquery +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# Try to do SHOW EXPLAIN for a query that runs a SET command: +# I've found experimentally that select_id==2 here... +# +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @foo= (select max(a) from t0 where sin(a) >0); +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +# +# Attempt SHOW EXPLAIN for an UPDATE +# +create table t2 as select a as a, a as dummy from t0 limit 2; +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +drop table t2; +# +# Attempt SHOW EXPLAIN for a DELETE +# +create table t2 as select a as a, a as dummy from t0 limit 2; +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +drop table t2; +# +# Multiple SHOW EXPLAIN calls for one select +# +create table t2 as select a as a, a as dummy from t0 limit 3; +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +a SUBQ +0 0 +1 0 +2 0 +drop table t2; +# +# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort" +# +explain select * from t0 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @show_explain_probe_select_id=1; +select * from t0 order by a; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort +Warnings: +Note 1003 select * from t0 order by a +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +# +# SHOW EXPLAIN for SELECT ... with "Using temporary" +# +explain select distinct a from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @show_explain_probe_select_id=1; +select distinct a from t0; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +Warnings: +Note 1003 select distinct a from t0 +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +# +# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort" +# +explain select distinct a from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @show_explain_probe_select_id=1; +select distinct a from t0; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +Warnings: +Note 1003 select distinct a from t0 +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +set debug_dbug=''; +# +# MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY +# +CREATE TABLE t2 ( a INT ); +INSERT INTO t2 VALUES (1),(2),(1),(4),(2); +explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +set debug_dbug='d,show_explain_in_find_all_keys'; +SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a; +# FIXED by "conservative assumptions about when QEP is available" fix: +# NOTE: current code will not show "Using join buffer": +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a +1 +2 +4 +set debug_dbug=''; +DROP TABLE t2; +# +# MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with +# SHOW EXPLAIN over EXPLAIN EXTENDED +# +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1),(2),(1),(4),(2); +EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a` +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +Warnings: +Note 1003 EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a` +set debug_dbug=''; +DROP TABLE t2; +# +# MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in +# JOIN::print_explain on query with a JOIN, TEMPTABLE view, +# +CREATE TABLE t3 (a INT); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (8); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9); +explain SELECT * FROM v1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 +2 DERIVED t3 system NULL NULL NULL NULL 1 +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_end'; +SELECT * FROM v1, t2; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a b +8 4 +8 5 +8 6 +8 7 +8 8 +8 9 +set debug_dbug=''; +DROP VIEW v1; +DROP TABLE t2, t3; +# +# MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select sleep(1); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select sleep(1) +sleep(1) +0 +set debug_dbug=''; +# +# Same as above, but try another reason for JOIN to be degenerate +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select * from t0 where 1>10; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a +set debug_dbug=''; +# +# Same as above, but try another reason for JOIN to be degenerate (2) +# +create table t3(a int primary key); +insert into t3 select a from t0; +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select * from t0,t3 where t3.a=112233; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a a +set debug_dbug=''; +drop table t3; +# +# MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with +# select tables optimized away +# +CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1), +(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ; +explain SELECT * FROM t2 WHERE a = +(SELECT MAX(a) FROM t2 +WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using where +2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using where +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT * FROM t2 WHERE a = +(SELECT MAX(a) FROM t2 +WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) +); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using where +2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using where +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +Warnings: +Note 1003 SELECT * FROM t2 WHERE a = +(SELECT MAX(a) FROM t2 +WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) +) +pk a +3 7 +6 7 +7 7 +9 7 +set debug_dbug=''; +drop table t2; +# +# MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE +# +CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(4),(6),(7),(1),(0),(7),(7),(1),(7),(1), +(5),(2),(0),(1),(8),(1),(1),(9),(1),(5); +CREATE TABLE t3 (b1 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(4),(5),(8),(4),(8),(2),(9),(6),(4),(8), +(3),(5),(9),(6),(8),(3),(2),(6),(3),(1), +(4),(3),(1),(7),(0),(0),(9),(5),(9),(0), +(2),(2),(5),(9),(1),(4),(8),(6),(5),(5), +(1),(7),(2),(8),(9),(3),(2),(6),(6),(5), +(4),(3),(2),(7),(4),(6),(0),(8),(5),(8), +(2),(9),(7),(5),(7),(0),(4),(3),(1),(0), +(6),(2),(8),(3),(7),(3),(5),(5),(1),(2), +(1),(7),(1),(9),(9),(8),(3); +CREATE TABLE t4 (c1 INT) ENGINE=MyISAM; +EXPLAIN +SELECT count(*) FROM t2, t3 +WHERE a1 < ALL ( +SELECT a1 FROM t2 +WHERE a1 IN ( SELECT a1 FROM t2, t4 ) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL a1 5 NULL 20 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join) +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT count(*) FROM t2, t3 +WHERE a1 < ALL ( +SELECT a1 FROM t2 +WHERE a1 IN ( SELECT a1 FROM t2, t4 ) +); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL a1 5 NULL 20 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join) +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +Warnings: +Note 1003 SELECT count(*) FROM t2, t3 +WHERE a1 < ALL ( +SELECT a1 FROM t2 +WHERE a1 IN ( SELECT a1 FROM t2, t4 ) +) +count(*) +1740 +set debug_dbug=''; +drop table t2, t3, t4; +# +# MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function +# +CREATE TABLE t2 ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`)) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43), +(11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`); +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +pk a1 +set debug_dbug=''; +DROP TABLE t2; +DROP TABLE t1; +# +# MDEV-305: SHOW EXPLAIN: ref returned by SHOW EXPLAIN is different from the normal EXPLAIN ('const' vs empty string) +# +CREATE TABLE t1(a INT, KEY(a)); +INSERT INTO t1 VALUES (3),(1),(5),(1); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT 'test' FROM t1 WHERE a=1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 5 const 1 Using index +Warnings: +Note 1003 SELECT 'test' FROM t1 WHERE a=1 +test +test +test +set debug_dbug=''; +DROP TABLE t1; +# +# MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution +# +create table t1 (key1 int, col1 int, col2 int, filler char(100), key(key1)); +insert into t1 select A.a+ 10 * B.a, 10, 10, 'filler-data' from t0 A, t0 B; +update t1 set col1=3, col2=10 where key1=1; +update t1 set col1=3, col2=1000 where key1=2; +update t1 set col1=3, col2=10 where key1=3; +update t1 set col1=3, col2=1000 where key1=4; +set @tmp_mdev299_jcl= @@join_cache_level; +set join_cache_level=0; +explain select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_test_if_quick_select'; +select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +count(*) +212 +set debug_dbug=''; +drop table t1; +# +# MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while +# executing SHOW INDEX and SHOW EXPLAIN in parallel +# +CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c)); +INSERT INTO t1 (a) VALUES (3),(1),(5),(1); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SHOW INDEX FROM t1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE STATISTICS ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +Warnings: +Note 1003 SHOW INDEX FROM t1 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 c 1 c A NULL NULL NULL YES BTREE +set debug_dbug=''; +DROP TABLE t1; +# +# MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view +# loses 'DERIVED' line on the way without saying that the plan was already deleted +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +EXPLAIN SELECT a + 1 FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 +set debug_dbug='d,show_explain_probe_join_tab_preread'; +set @show_explain_probe_select_id=1; +SELECT a + 1 FROM v1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 SELECT a + 1 FROM v1 +a + 1 +2 +3 +set debug_dbug=''; +DROP VIEW v1; +DROP TABLE t1; +# +# MDEV-323: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses +# 'UNION RESULT' line on the way without saying that the plan was already deleted +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (4),(6); +EXPLAIN +SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +set debug_dbug='d,show_explain_probe_union_read'; +SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ) +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ) +a +set debug_dbug=''; +DROP TABLE t1; +# +# MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN +# and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY' +# +CREATE TABLE t1 (a INT) ENGINE=Aria; +INSERT INTO t1 VALUES +(4),(6),(3),(5),(3),(246),(2),(9),(3),(8), +(1),(8),(8),(5),(7),(5),(1),(6),(2),(9); +CREATE TABLE t2 (b INT) ENGINE=Aria; +INSERT INTO t2 VALUES +(1),(7),(4),(7),(0),(2),(9),(4),(0),(9), +(1),(3),(8),(8),(18),(84),(6),(3),(6),(6); +EXPLAIN +SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias +WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias +WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where +Warnings: +Note 1003 SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias +WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ) +a b +set debug_dbug=''; +DROP TABLE t1, t2; +# +# Test that SHOW EXPLAIN will print 'Distinct'. +# +CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); +CREATE TABLE t3 (a int(10) unsigned, key(A), b text); +INSERT INTO t3 VALUES (1,'1'),(2,'2'); +create temporary table t4 select * from t3; +insert into t3 select * from t4; +insert into t4 select * from t3; +insert into t3 select * from t4; +insert into t4 select * from t3; +insert into t3 select * from t4; +insert into t4 select * from t3; +insert into t3 select * from t4; +explain select distinct t1.a from t1,t3 where t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary +1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select distinct t1.a from t1,t3 where t1.a=t3.a; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary +1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct +Warnings: +Note 1003 select distinct t1.a from t1,t3 where t1.a=t3.a +a +1 +2 +set debug_dbug=''; +drop table t1,t3,t4; +# +# ---------- SHOW EXPLAIN and permissions ----------------- +# +grant ALL on test.* to test2@localhost; +grant super on *.* to test2@localhost; +# +# First, make sure that user 'test2' cannot do SHOW EXPLAIN on us +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where a < 3; +show explain for $thr2; +ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where a < 3 +a +0 +1 +2 +set debug_dbug=''; +# +# Check that user test2 can do SHOW EXPLAIN on its own queries +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where a < 3; +show explain for $thr_con2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where a < 3 +a +0 +1 +2 +# +# Now, grant test2 a PROCESSLIST permission, and see that he's able to observe us +# +grant process on *.* to test2@localhost; +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where a < 3; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where a < 3 +a +0 +1 +2 +set debug_dbug=''; +revoke all privileges on test.* from test2@localhost; +drop user test2@localhost; +# +# Test that it is possible to KILL a SHOW EXPLAIN command that's waiting +# on its target thread +# +create table t1 (pk int primary key, data char(64)) engine=innodb; +insert into t1 select A.a + 10 * B.a + 100 * C.a, 'data1' from t0 A, t0 B, t0 C; +# Lock two threads +set autocommit=0; +select * from t1 where pk between 10 and 20 for update; +pk data +10 data1 +11 data1 +12 data1 +13 data1 +14 data1 +15 data1 +16 data1 +17 data1 +18 data1 +19 data1 +20 data1 +set autocommit=0; +select * from t1 where pk between 10 and 20 for update; +# do: send_eval show explain for thr2; +kill query $thr_default; +ERROR 70100: Query execution was interrupted +rollback; +pk data +10 data1 +11 data1 +12 data1 +13 data1 +14 data1 +15 data1 +16 data1 +17 data1 +18 data1 +19 data1 +20 data1 +drop table t1; +# +# Check that the I_S table is invisible +# +select table_name from information_schema.tables where table_schema='information_schema' and table_name like '%explain%'; +table_name +# +# MDEV-325: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN is different from standard EXPLAIN: type ALL vs 'index_merge'.. +# +CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(8,0),(128,5050),(5372,8),(234,7596),(2,0),(2907,8930),(1,0), +(0,5224),(8,7638),(960,5),(9872,1534),(0,2295),(3408,9809), +(7,0),(1168,0),(2089,5570),(0,205),(88,1018),(0,26528), +(0,0),(4,5567),(1444,145),(6,0),(1,7535),(7793,534),(70,9), +(178,1),(44,5),(189,0),(3,0); +EXPLAIN +SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort +Warnings: +Note 1003 SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b +a+SLEEP(0.01) +0 +5372 +70 +0 +0 +0 +0 +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort +Warnings: +Note 1003 SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b +a+SLEEP(0.01) +0 +5372 +70 +0 +0 +0 +0 +set debug_dbug=''; +drop table t1; +# +# MDEV-298: SHOW EXPLAIN: Plan returned by SHOW EXPLAIN only contains +# 'Using temporary' while the standard EXPLAIN says 'Using temporary; Using filesort' +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), +(10),(11),(12),(13),(14),(15),(16); +INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12, t1 t13; +EXPLAIN SELECT a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary; Using filesort +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT a FROM t1 GROUP BY a; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary; Using filesort +Warnings: +Note 1003 SELECT a FROM t1 GROUP BY a +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +set debug_dbug=''; +drop table t1; +# +# MDEV-408: SHOW EXPLAIN: Some values are chopped off in SHOW EXPLAIN output +# +CREATE TABLE t1 (a INT, b VARCHAR(35)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (3989,'Abilene'),(3873,'Akron'); +CREATE TABLE t2 (c INT, d VARCHAR(52) PRIMARY KEY, KEY(c)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (86,'English'),(87,'Russian'); +explain SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's'; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 index_subquery PRIMARY,c c 5 func 1 Using index; Using where +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's'; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 index_subquery PRIMARY,c c 5 func 1 Using index; Using where +Warnings: +Note 1003 SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's' +SUM(a + SLEEP(0.1)) +7862 +set debug_dbug=''; +drop table t1, t2; +# +# MDEV-412: SHOW EXPLAIN: Server crashes in JOIN::print_explain on a query with inner join and ORDER BY the same column twice +# +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(3), KEY(b)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(3795,'USA'),(3913,'USA'),(3846,'ITA'),(4021,'USA'),(4005,'RUS'),(4038,'USA'), +(3825,'USA'),(3840,'USA'),(3987,'USA'),(3807,'USA'),(3896,'USA'),(4052,'USA'), +(3973,'USA'),(3982,'ITA'),(3965,'USA'),(3852,'RUS'),(4006,'USA'),(3800,'USA'), +(4020,'USA'),(4040,'USA'),(3916,'USA'),(3817,'USA'),(3885,'USA'),(3802,'USA'), +(4009,'ITA'),(3895,'USA'),(3963,'RUS'),(4045,'USA'),(3988,'USA'),(3815,'USA'), +(4063,'USA'),(3978,'USA'),(4019,'USA'),(3954,'USA'),(3950,'USA'),(3974,'ITA'), +(4054,'USA'),(4061,'RUS'),(3976,'USA'),(3966,'USA'),(3957,'USA'),(3981,'USA'), +(3923,'USA'),(3876,'USA'),(3819,'USA'),(3877,'USA'),(3829,'ITA'),(3964,'USA'), +(4053,'RUS'),(3917,'USA'),(3874,'USA'),(4023,'USA'),(4001,'USA'),(3872,'USA'), +(3890,'USA'),(3962,'USA'),(3886,'USA'),(4026,'ITA'),(3869,'USA'),(3937,'RUS'), +(3975,'USA'),(3944,'USA'),(3908,'USA'),(3867,'USA'),(3947,'USA'),(3838,'USA'), +(3796,'USA'),(3893,'USA'),(3920,'ITA'),(3994,'USA'),(3875,'RUS'),(4011,'USA'), +(4013,'USA'),(3810,'USA'),(3834,'USA'),(3968,'USA'),(3931,'USA'),(3839,'USA'), +(4042,'USA'),(4039,'ITA'),(3811,'USA'),(3837,'RUS'),(4041,'USA'),(3884,'USA'), +(3894,'USA'),(3879,'USA'),(3942,'USA'),(3959,'USA'),(3814,'USA'),(4044,'USA'), +(3971,'ITA'),(3823,'USA'),(3793,'RUS'),(3855,'USA'),(3905,'USA'),(3865,'USA'), +(4046,'USA'),(3990,'USA'),(4022,'USA'),(3833,'USA'),(3918,'USA'),(4064,'ITA'), +(3821,'USA'),(3836,'RUS'),(3921,'USA'),(3914,'USA'),(3888,'USA'); +CREATE TABLE t2 (c VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('USA'); +CREATE TABLE t3 (d VARCHAR(3), e VARCHAR(52), PRIMARY KEY (d,e)) ENGINE=MyISAM; +INSERT INTO t3 VALUES +('JPN','Japanese'),('KOR','Korean'),('POL','Polish'),('PRT','Portuguese'), +('ESP','Spanish'),('FRA','French'),('VNM','Vietnamese'); +explain +SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t1 index b b 6 NULL 107 Using where; Using index +1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t1 index b b 6 NULL 107 Using where; Using index +1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index +Warnings: +Note 1003 SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2 +field1 field2 +set debug_dbug=''; +DROP TABLE t1,t2,t3; +# +# MDEV-423: SHOW EXPLAIN: 'Using where' for a subquery is shown in EXPLAIN, but not in SHOW EXPLAIN output +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5); +CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8), +(4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9); +create table t3 like t2; +insert into t3 select * from t2; +explain +SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias +WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 20 Using where +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias +WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias +WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10 +max(a+b+c) +279 +set debug_dbug=''; +DROP TABLE t1,t2,t3; +# +# MDEV-416: Server crashes in SQL_SELECT::cleanup on EXPLAIN with SUM ( DISTINCT ) in a non-correlated subquery (5.5-show-explain tree) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (8),(9); +EXPLAIN SELECT * FROM t1 +WHERE ( 8, 89 ) IN ( SELECT b, SUM( DISTINCT b ) FROM t2 GROUP BY b ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using filesort +DROP TABLE t1,t2; +# +# Check if queries in non-default charsets work. +# +set names cp1251; +select charset('ãû'); +charset('ãû') +cp1251 +select hex('ãû'); +hex('ãû') +E3FB +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where length('ãû') = a; +set names utf8; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where length('гы') = a +set names default; +a +2 +set debug_dbug=''; +set names default; +# +# MDEV-462: SHOW EXPLAIN: Assertion `table_list->table' fails in find_field_in_table_ref if FOR contains a non-numeric value +# +show explain for foo; +ERROR HY000: You may only use constant expressions in this statement +# End +drop table t0; diff --git a/mysql-test/r/show_explain_ps.result b/mysql-test/r/show_explain_ps.result new file mode 100644 index 00000000000..f9b82c85f01 --- /dev/null +++ b/mysql-test/r/show_explain_ps.result @@ -0,0 +1,29 @@ +drop table if exists t0, t1; +select * from performance_schema.setup_instruments where name like '%show_explain%'; +NAME ENABLED TIMED +wait/synch/cond/sql/show_explain YES YES +stage/sql/show explain YES YES +statement/sql/show_explain YES YES +# We've got no instances +select * from performance_schema.cond_instances where name like '%show_explain%'; +NAME OBJECT_INSTANCE_BEGIN +# Check out if our cond is hit. +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select count(*) from t0 where a < 100000; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select count(*) from t0 where a < 100000 +count(*) +10 +set debug_dbug=''; +select event_name +from performance_schema.events_waits_history_long +where event_name='wait/synch/cond/sql/show_explain'; +event_name +wait/synch/cond/sql/show_explain +drop table t0; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index d49c67a35fd..293400ece2f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7811,9 +7811,12 @@ DROP FUNCTION f1; # -- End of 5.1 tests # ------------------------------------------------------------------ # -# LP bug#993459 Execution of PS for a query with GROUP BY +# lp:993459 Execution of PS for a query with GROUP BY # returns wrong result (see also mysql bug#13805127) # + +# Bug#13805127: Stored program cache produces wrong result in same THD + CREATE PROCEDURE p1(x INT UNSIGNED) BEGIN SELECT c1, t2.c2, count(c3) @@ -7850,7 +7853,7 @@ c1 c2 count(c3) 2012-03-01 02:00:00 3 1 DROP PROCEDURE p1; # -# LP bug#1002157 : testing stored function +# lp:1002157 : testing stored function # bug#62125 result for null incorrectly yields 1292 warning. # DROP FUNCTION IF EXISTS f1; diff --git a/mysql-test/r/str_to_datetime_457.result b/mysql-test/r/str_to_datetime_457.result new file mode 100644 index 00000000000..4fd0d00691c --- /dev/null +++ b/mysql-test/r/str_to_datetime_457.result @@ -0,0 +1,51 @@ +select cast('01:02:03 ' as time), cast('01:02:03 ' as time); +cast('01:02:03 ' as time) cast('01:02:03 ' as time) +01:02:03 00:00:00 +select cast('2002-011-012' as date), cast('2002.11.12' as date), cast('2002.011.012' as date); +cast('2002-011-012' as date) cast('2002.11.12' as date) cast('2002.011.012' as date) +2002-11-12 2002-11-12 2002-11-12 +select cast('2012103123595912' as datetime(6)), cast('20121031235959123' as datetime(6)); +cast('2012103123595912' as datetime(6)) cast('20121031235959123' as datetime(6)) +2012-10-31 23:59:59.000000 2012-10-31 23:59:59.000000 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2012103123595912' +Warning 1292 Truncated incorrect datetime value: '20121031235959123' +select cast(0 as date), cast('0000-00-00' as date), cast('0' as date); +cast(0 as date) cast('0000-00-00' as date) cast('0' as date) +0000-00-00 0000-00-00 NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +select extract(hour from '100000:02:03'), extract(hour from '100000:02:03 '); +extract(hour from '100000:02:03') extract(hour from '100000:02:03 ') +NULL NULL +Warnings: +Warning 1292 Truncated incorrect time value: '100000:02:03' +Warning 1292 Truncated incorrect time value: '100000:02:03 ' +# +# backward compatibility craziness +# +select cast('12:00:00.12.34.56' as time); +cast('12:00:00.12.34.56' as time) +12:00:00 +Warnings: +Warning 1292 Truncated incorrect time value: '12:00:00.12.34.56' +select cast('12:00:00 12.34.56' as time); +cast('12:00:00 12.34.56' as time) +12:34:56 +select cast('12:00:00-12.34.56' as time); +cast('12:00:00-12.34.56' as time) +12:00:00 +Warnings: +Warning 1292 Truncated incorrect time value: '12:00:00-12.34.56' +select cast('12:00:00.12.34.56' as datetime); +cast('12:00:00.12.34.56' as datetime) +2012-00-00 12:34:56 +select cast('12:00:00-12.34.56' as datetime); +cast('12:00:00-12.34.56' as datetime) +2012-00-00 12:34:56 +select cast('12:00:00 12.34.56' as datetime); +cast('12:00:00 12.34.56' as datetime) +2012-00-00 12:34:56 +select cast('12:00:00.123456' as time); +cast('12:00:00.123456' as time) +12:00:00 diff --git a/mysql-test/r/strict_autoinc_1myisam.result b/mysql-test/r/strict_autoinc_1myisam.result index afcccb1c40f..b22540f295b 100644 --- a/mysql-test/r/strict_autoinc_1myisam.result +++ b/mysql-test/r/strict_autoinc_1myisam.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value 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 index e534286e2a2..1cf720da12a 100644 --- a/mysql-test/r/strict_autoinc_2innodb.result +++ b/mysql-test/r/strict_autoinc_2innodb.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value 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 index 0a31da04460..21eea537af5 100644 --- a/mysql-test/r/strict_autoinc_3heap.result +++ b/mysql-test/r/strict_autoinc_3heap.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value 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 1863cd09c38..f775336299b 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); 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 -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -5966,7 +5966,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6127,7 +6127,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # @@ -6612,7 +6612,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U min_a a drop table t1; # -# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +# MDEV-367: Different results with and without subquery_cache on +# a query with a constant NOT IN condition +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3); +set @mdev367_optimizer_switch = @@optimizer_switch; +set optimizer_switch = 'subquery_cache=on'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +set optimizer_switch = 'subquery_cache=off'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +DROP TABLE t1; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); @@ -6768,5 +6794,68 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); a drop table t1,t2; -# return optimizer switch changed in the beginning of this test -set optimizer_switch=@subselect_tmp; +# +# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used +# +CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('USA'); +CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); +CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (12),(22),(9),(45); +create table t4 like t3; +insert into t4 select * from t3; +# This should not show range access for table t2 +explain +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @tmp_mdev410=@@global.userstat; +set global userstat=on; +flush table_statistics; +flush index_statistics; +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +MIN(b) +NULL +# The following shows that t2 was indeed scanned with a full scan. +show table_statistics; +Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes +test t1 2 0 0 +test t2 3 0 0 +show index_statistics; +Table_schema Table_name Index_name Rows_read +test t2 b 1 +set global userstat=@tmp_mdev410; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-430: Server crashes in select_describe on EXPLAIN with +# materialization+semijoin, 2 nested subqueries, aggregate functions +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; +# +# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 41c445329cb..ed00e4ef684 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -161,18 +161,18 @@ SET optimizer_switch='materialization=on,in_to_exists=on'; EXPLAIN SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 2 Using index -1 PRIMARY t3 index b b 5 NULL 4 Using where; Using index; Using join buffer (flat, BNL join) -2 MATERIALIZED t1 index PRIMARY,a a 5 NULL 2 Using index +1 PRIMARY t2 index a a 5 NULL 2 Using where; Using index +1 PRIMARY t3 ref b b 5 test.t2.a 2 Using index +2 SUBQUERY t1 index_subquery PRIMARY,a a 5 const 0 Using index; Using where SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; pk a b 0 4 4 EXPLAIN SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 2 Using index -1 PRIMARY t3 index b b 5 NULL 4 Using where; Using index; Using join buffer (flat, BNL join) -2 MATERIALIZED t1 index PRIMARY,a a 5 NULL 2 Using index +1 PRIMARY t2 index a a 5 NULL 2 Using where; Using index +1 PRIMARY t3 ref b b 5 test.t2.a 2 Using index +2 SUBQUERY t1 index_subquery PRIMARY,a a 5 const 0 Using index; Using where SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; pk a b 0 4 4 diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 72b4f34d9de..7f290fb5b03 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -206,6 +206,165 @@ default(a) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DROP TABLE t; # +# LP BUG#1009187, MDEV-373, MYSQL bug#58628 +# Wrong result for a query with [NOT] IN subquery predicate if +# the left part of the predicate is explicit NULL +# +CREATE TABLE t1 (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t1 VALUES (0,10), (1,20), (2,30), (3,40); +CREATE TABLE t2a (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO t2a VALUES (0,0), (1,1), (2,2), (3,3); +CREATE TABLE t2b (pk INT, i INT); +INSERT INTO t2b VALUES (0,0), (1,1), (2,2), (3,3); +CREATE TABLE t2c (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t2c VALUES (0,0), (1,1), (2,2), (3,3); +create index it2c on t2c (i,pk); +CREATE TABLE t2d (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i)); +INSERT INTO t2d VALUES (0,0), (1,1), (2,2), (3,3); +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2a unique_subquery PRIMARY PRIMARY 8 const,test.t1.pk 1 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE 1+NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2c index_subquery it2c it2c 8 const,test.t1.pk 2 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2d const PRIMARY PRIMARY 4 const 1 Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2a eq_ref PRIMARY PRIMARY 8 const,test.t1.pk 1 Using where; Using index; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk) +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk) +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2c ref it2c it2c 8 const,test.t1.pk 2 Using where; Using index; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk) +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2d const PRIMARY PRIMARY 4 const 1 Using where; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk) +1 +NULL +1 +1 +drop table t1, t2a, t2b, t2c, t2d; +# # End of 5.1 tests. # # @@ -627,51 +786,51 @@ SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; not_in NULL EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; not_in NULL @@ -679,7 +838,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; not_in 1 @@ -687,7 +846,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; not_in NULL @@ -695,7 +854,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; not_in NULL @@ -703,7 +862,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; not_in NULL @@ -718,51 +877,51 @@ SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; not_in NULL EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; not_in NULL @@ -770,7 +929,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; not_in 1 @@ -778,7 +937,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; not_in NULL @@ -786,7 +945,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; not_in NULL @@ -794,7 +953,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; not_in NULL @@ -813,21 +972,21 @@ SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); f1 f2 @@ -842,14 +1001,14 @@ NULL EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); f1 f2 @@ -904,21 +1063,21 @@ SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); f1 f2 @@ -933,14 +1092,14 @@ NULL EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); f1 f2 diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index a0f05a26a46..6c1e52a112e 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -248,6 +248,47 @@ NULL drop procedure p1; drop tables t1,t2,t3; # +# Bug #58756 +# Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT +# +CREATE TABLE t1 ( +col_time_key time DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_varchar_nokey varchar(1) DEFAULT NULL, +KEY col_time_key (col_time_key), +KEY col_datetime_key (col_datetime_key) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES ('17:53:30','2005-11-10 12:40:29','h'); +INSERT INTO t1 VALUES ('11:35:49','2009-04-25 00:00:00','b'); +INSERT INTO t1 VALUES (NULL,'2002-11-27 00:00:00','s'); +INSERT INTO t1 VALUES ('06:01:40','2004-01-26 20:32:32','e'); +INSERT INTO t1 VALUES ('05:45:11','2007-10-26 11:41:40','j'); +INSERT INTO t1 VALUES ('00:00:00','2005-10-07 00:00:00','e'); +INSERT INTO t1 VALUES ('00:00:00','2000-07-15 05:00:34','f'); +INSERT INTO t1 VALUES ('06:11:01','2000-04-03 16:33:32','v'); +INSERT INTO t1 VALUES ('13:02:46',NULL,'x'); +INSERT INTO t1 VALUES ('21:44:25','2001-04-25 01:26:12','m'); +INSERT INTO t1 VALUES ('22:43:58','2000-12-27 00:00:00','c'); +CREATE TABLE t2 ( +col_time_key time DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_varchar_nokey varchar(1) DEFAULT NULL, +KEY col_time_key (col_time_key), +KEY col_datetime_key (col_datetime_key) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES ('11:28:45','2004-10-11 18:13:16','w'); +SELECT col_time_key, col_datetime_key +FROM +( SELECT * FROM t1 ) AS table1 +HAVING ( 'r' , 'e' ) IN +( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 ) +ORDER BY col_datetime_key +LIMIT 10; +col_time_key col_datetime_key +DROP TABLE t1; +DROP TABLE t2; +# End of Bug #58756 +# # Bug#60085 crash in Item::save_in_field() with time data type # CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb; @@ -333,7 +374,7 @@ WHERE (SELECT DISTINCT b FROM t3) > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 0 Using temporary +3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary SELECT * FROM t1 WHERE t1.a = ( @@ -386,7 +427,7 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +3 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); 1 1 diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 45d0b9ee519..9bb26e8a6e0 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2183,7 +2183,7 @@ NULL EXPLAIN SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 5a38fe7ca72..8c95c8637aa 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -148,7 +148,7 @@ FROM t2 GROUP BY f1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 system NULL NULL NULL NULL 1 -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort drop table t1, t2, t3; # # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed @@ -162,7 +162,7 @@ EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table insert into t2 values (1),(2); diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 78ab87dbfd6..e72d25fdafa 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -4200,7 +4200,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); 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 -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -5967,7 +5967,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6126,7 +6126,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # @@ -6611,7 +6611,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U min_a a drop table t1; # -# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +# MDEV-367: Different results with and without subquery_cache on +# a query with a constant NOT IN condition +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3); +set @mdev367_optimizer_switch = @@optimizer_switch; +set optimizer_switch = 'subquery_cache=on'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +set optimizer_switch = 'subquery_cache=off'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +DROP TABLE t1; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); @@ -6766,8 +6792,71 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); a drop table t1,t2; -# return optimizer switch changed in the beginning of this test -set optimizer_switch=@subselect_tmp; +# +# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used +# +CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('USA'); +CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); +CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (12),(22),(9),(45); +create table t4 like t3; +insert into t4 select * from t3; +# This should not show range access for table t2 +explain +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @tmp_mdev410=@@global.userstat; +set global userstat=on; +flush table_statistics; +flush index_statistics; +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +MIN(b) +NULL +# The following shows that t2 was indeed scanned with a full scan. +show table_statistics; +Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes +test t1 2 0 0 +test t2 3 0 0 +show index_statistics; +Table_schema Table_name Index_name Rows_read +test t2 b 1 +set global userstat=@tmp_mdev410; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-430: Server crashes in select_describe on EXPLAIN with +# materialization+semijoin, 2 nested subqueries, aggregate functions +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; +# +# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index f822a4500f4..9030265356b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); 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 -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -5963,7 +5963,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # @@ -6607,7 +6607,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U min_a a drop table t1; # -# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +# MDEV-367: Different results with and without subquery_cache on +# a query with a constant NOT IN condition +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3); +set @mdev367_optimizer_switch = @@optimizer_switch; +set optimizer_switch = 'subquery_cache=on'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +set optimizer_switch = 'subquery_cache=off'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +DROP TABLE t1; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); @@ -6763,6 +6789,69 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); a drop table t1,t2; -# return optimizer switch changed in the beginning of this test -set optimizer_switch=@subselect_tmp; +# +# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used +# +CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('USA'); +CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); +CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (12),(22),(9),(45); +create table t4 like t3; +insert into t4 select * from t3; +# This should not show range access for table t2 +explain +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @tmp_mdev410=@@global.userstat; +set global userstat=on; +flush table_statistics; +flush index_statistics; +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +MIN(b) +NULL +# The following shows that t2 was indeed scanned with a full scan. +show table_statistics; +Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes +test t1 2 0 0 +test t2 3 0 0 +show index_statistics; +Table_schema Table_name Index_name Rows_read +test t2 b 1 +set global userstat=@tmp_mdev410; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-430: Server crashes in select_describe on EXPLAIN with +# materialization+semijoin, 2 nested subqueries, aggregate functions +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; +# +# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index cdad363313d..e68f5990c08 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -4202,7 +4202,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); 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 -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -5972,7 +5972,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6133,7 +6133,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # @@ -6618,7 +6618,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U min_a a drop table t1; # -# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +# MDEV-367: Different results with and without subquery_cache on +# a query with a constant NOT IN condition +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3); +set @mdev367_optimizer_switch = @@optimizer_switch; +set optimizer_switch = 'subquery_cache=on'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +set optimizer_switch = 'subquery_cache=off'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +DROP TABLE t1; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); @@ -6774,8 +6800,71 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); a drop table t1,t2; -# return optimizer switch changed in the beginning of this test -set optimizer_switch=@subselect_tmp; +# +# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used +# +CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('USA'); +CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); +CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (12),(22),(9),(45); +create table t4 like t3; +insert into t4 select * from t3; +# This should not show range access for table t2 +explain +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @tmp_mdev410=@@global.userstat; +set global userstat=on; +flush table_statistics; +flush index_statistics; +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +MIN(b) +NULL +# The following shows that t2 was indeed scanned with a full scan. +show table_statistics; +Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes +test t1 2 0 0 +test t2 3 0 0 +show index_statistics; +Table_schema Table_name Index_name Rows_read +test t2 b 1 +set global userstat=@tmp_mdev410; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-430: Server crashes in select_describe on EXPLAIN with +# materialization+semijoin, 2 nested subqueries, aggregate functions +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; +# +# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 8e6d4bfccc2..f0ce541294a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); 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 -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -5963,7 +5963,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 +2 SUBQUERY t1 ref a a 5 const 1 Using index DROP TABLE t1; # @@ -6607,7 +6607,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U min_a a drop table t1; # -# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +# MDEV-367: Different results with and without subquery_cache on +# a query with a constant NOT IN condition +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3); +set @mdev367_optimizer_switch = @@optimizer_switch; +set optimizer_switch = 'subquery_cache=on'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +set optimizer_switch = 'subquery_cache=off'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +DROP TABLE t1; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); @@ -6763,7 +6789,70 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); a drop table t1,t2; -# return optimizer switch changed in the beginning of this test -set optimizer_switch=@subselect_tmp; +# +# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used +# +CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('USA'); +CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); +CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (12),(22),(9),(45); +create table t4 like t3; +insert into t4 select * from t3; +# This should not show range access for table t2 +explain +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @tmp_mdev410=@@global.userstat; +set global userstat=on; +flush table_statistics; +flush index_statistics; +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +MIN(b) +NULL +# The following shows that t2 was indeed scanned with a full scan. +show table_statistics; +Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes +test t1 2 0 0 +test t2 3 0 0 +show index_statistics; +Table_schema Table_name Index_name Rows_read +test t2 b 1 +set global userstat=@tmp_mdev410; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-430: Server crashes in select_describe on EXPLAIN with +# materialization+semijoin, 2 nested subqueries, aggregate functions +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; +# +# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 63193bdedbc..3310209df58 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -165,6 +165,8 @@ select @log; (BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1)) set @log:= ""; insert ignore t1 values (1, 2); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' select @log; @log (BEFORE_INSERT: new=(id=1, data=2)) diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 40325376fc0..ff4ebd6b15c 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -878,6 +878,8 @@ ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT CREATE TABLE b15776 (a char(4294967296)); ERROR 42000: Display width out of range for 'a' (max = 4294967295) CREATE TABLE b15776 (a year(4294967295)); +Warnings: +Note 1287 'YEAR(4294967295)' is deprecated and will be removed in a future release. Please use YEAR(4) instead INSERT INTO b15776 VALUES (42); SELECT * FROM b15776; a @@ -886,6 +888,8 @@ DROP TABLE b15776; CREATE TABLE b15776 (a year(4294967296)); ERROR 42000: Display width out of range for 'a' (max = 4294967295) CREATE TABLE b15776 (a year(0)); +Warnings: +Note 1287 'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead DROP TABLE b15776; CREATE TABLE b15776 (a year(-2)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2))' at line 1 diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 41f590400ea..8a85cd53b2a 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -209,15 +209,11 @@ a SET SQL_MODE=TRADITIONAL; EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where; Using index -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +1 SIMPLE t1 ref i i 4 const 1 Using index SELECT * FROM t1 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 SELECT * FROM t2 WHERE a = '0000-00-00'; a 0000-00-00 @@ -242,15 +238,11 @@ a SET SQL_MODE=TRADITIONAL; EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where; Using index -Warnings: -Warning 1265 Data truncated for column 'a' at row 1 +1 SIMPLE t1 ref i i 4 const 1 Using index SELECT * FROM t1 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 -Warnings: -Warning 1265 Data truncated for column 'a' at row 1 SELECT * FROM t2 WHERE a = '1000-00-00'; a 1000-00-00 diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index 5d89a1ee407..4b9bf6f433f 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -1,5 +1,7 @@ drop table if exists t1; create table t1 (y year,y2 year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); select * from t1; y y2 @@ -50,6 +52,8 @@ End of 5.0 tests # Bug #49480: WHERE using YEAR columns returns unexpected results # CREATE TABLE t2(yy YEAR(2), c2 CHAR(4)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4)); INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069); INSERT INTO t4 (c4) SELECT c2 FROM t2; @@ -358,9 +362,22 @@ total_rows min_value MAX(c1+0) 3 0 2155 DROP TABLE t1; # +# WL#6219: Deprecate and remove YEAR(2) type +# +CREATE TABLE t1 (c1 YEAR(2), c2 YEAR(4)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +DROP TABLE t1; +# End of 5.1 tests create function y2k() returns int deterministic return 2000; create table t1 (a year(2), b int); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead insert t1 values (0,2000); select a from t1 where a=2000; a diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result index 86f4ba25ab1..27a0d7f0a1b 100644 --- a/mysql-test/r/user_var-binlog.result +++ b/mysql-test/r/user_var-binlog.result @@ -34,7 +34,7 @@ SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/; -use test/*!*/; +use `test`/*!*/; SET TIMESTAMP=10000/*!*/; INSERT INTO t1 VALUES(@`a b`) /*!*/; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 008f8f41ada..5af20fd0c3c 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4674,6 +4674,62 @@ INSERT INTO t2 VALUES (1); DROP TRIGGER tr; DROP VIEW v1; DROP TABLE t1,t2,t3; +# +# LP bug#1007622 Server crashes in handler::increment_statistics on +# inserting into a view over a view +# +flush status; +CREATE TABLE t1 (a INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.* FROM t1 AS a1, t1 AS a2; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1; +INSERT INTO v2 (a) VALUES (1) ; +select * from t1; +a +1 +drop view v2,v1; +drop table t1; +show status like '%view%'; +Variable_name Value +Com_create_view 2 +Com_drop_view 1 +Opened_views 3 +show status like 'Opened_table%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 3 +# +# MDEV-486 LP BUG#1010116 Incorrect query results in +# view and derived tables +# +SELECT +`Derived1`.`id`, +`Derived2`.`Val1` +FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT +2 as `id`, +1 AS `Val1` +FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`; +id Val1 +30631 NULL +create table t1 ( id int ); +insert into t1 values (30631); +create table t2 ( id int ); +insert into t2 values (30631); +create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2; +select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; +id id val1 +30631 NULL NULL +drop view v2; +drop table t1,t2; +create table t1 ( id int ); +insert into t1 values (30631); +create table t2 ( id int ); +insert into t2 values (30631); +create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd, 1 as val1 from t2; +select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; +id id bbb iddqd val1 +30631 NULL NULL NULL NULL +drop view v2; +drop table t1,t2; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/xa_binlog.result b/mysql-test/r/xa_binlog.result index 3ce64953902..395f0dc62a4 100644 --- a/mysql-test/r/xa_binlog.result +++ b/mysql-test/r/xa_binlog.result @@ -18,7 +18,7 @@ a 1 2 3 -SHOW BINLOG EVENTS LIMIT 1,9; +SHOW BINLOG EVENTS LIMIT 2,9; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # BEGIN master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (1) diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result index 069d3c25826..15b750d2fd3 100644 --- a/mysql-test/r/xtradb_mrr.result +++ b/mysql-test/r/xtradb_mrr.result @@ -276,6 +276,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); |