diff options
Diffstat (limited to 'mysql-test/main')
54 files changed, 3266 insertions, 396 deletions
diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result index cf38810b598..f269aaf540b 100644 --- a/mysql-test/main/analyze_stmt_privileges2.result +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -376,13 +376,13 @@ SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 0.00 100.00 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 #------------------------------------------------------------------------ # I/R/U/D/S on the inner view @@ -491,14 +491,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -598,14 +598,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the table @@ -1591,14 +1591,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything: SELECT access to the column `a` @@ -1708,14 +1708,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant SELECT, INSERT, UPDATE, DELETE on the table @@ -1940,14 +1940,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2048,14 +2048,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL ######################################################################### # Inner view permission tests @@ -2697,14 +2697,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2804,14 +2804,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the inner view @@ -3987,14 +3987,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -4094,14 +4094,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL ######################################################################### # Outer view permission tests @@ -4614,14 +4614,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the outer view @@ -5221,14 +5221,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL disconnect con1; connection default; DROP USER 'privtest'@localhost; diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result index 0b7577dd3ac..24f8417d313 100644 --- a/mysql-test/main/constraints.result +++ b/mysql-test/main/constraints.result @@ -129,6 +129,61 @@ t CREATE TABLE `t` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP table test.t; SET @@SQL_MODE=@OLD_SQL_MODE; +# +# MDEV-16932 - ASAN heap-use-after-free in my_charlen_utf8 / +# my_well_formed_char_length_utf8 on 2nd execution of SP with +# ALTER trying to add bad CHECK +# +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 add column b int; +CALL sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CALL sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0), + CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE sp; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +alter table t1 add column b int, add constraint check (b < 10); +CALL sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`b` < 10), + CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE sp; +DROP TABLE t1; +# End of 10.2 tests create table t1 (a int check (a>10)) select 100 as 'a'; show create table t1; Table Create Table diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test index d7a5b41d708..2f4dadcee9d 100644 --- a/mysql-test/main/constraints.test +++ b/mysql-test/main/constraints.test @@ -116,6 +116,43 @@ SHOW CREATE TABLE t; DROP table test.t; SET @@SQL_MODE=@OLD_SQL_MODE; +--echo # +--echo # MDEV-16932 - ASAN heap-use-after-free in my_charlen_utf8 / +--echo # my_well_formed_char_length_utf8 on 2nd execution of SP with +--echo # ALTER trying to add bad CHECK +--echo # + +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +show create table t1; +alter table t1 add column b int; +CALL sp; +show create table t1; +CALL sp; +show create table t1; +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +--error ER_BAD_FIELD_ERROR +CALL sp; +alter table t1 add column b int, add constraint check (b < 10); +CALL sp; +show create table t1; +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + +--echo # End of 10.2 tests + # # Check that we don't lose constraints as part of CREATE ... SELECT # diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 2556fd4b06b..12a7e2ea789 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1159,9 +1159,9 @@ with cte as union (select a from t1 where a < 2); a -7 -5 4 +5 +7 1 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) @@ -1170,15 +1170,15 @@ union (select a from t1 where a < 2)"; execute stmt; a -7 -5 4 +5 +7 1 execute stmt; a -7 -5 4 +5 +7 1 deallocate prepare stmt; with cte as @@ -1188,9 +1188,9 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a 1 -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) (select a from t1 where a < 2) @@ -1199,15 +1199,15 @@ union execute stmt; a 1 -7 -5 4 +5 +7 execute stmt; a 1 -7 -5 4 +5 +7 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7) diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index 38b157c8bb1..a85b8c965ba 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -2180,13 +2180,13 @@ CREATE TABLE t1 (a TIMESTAMP(3)); INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999'); SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; c1 c2 c2 -20010101102030 20010101102030.999 20010101102031 +20010101102030 20010101102030.999 2001-01-01 10:20:31 DROP TABLE t1; CREATE TABLE t1 (a TIME(3)); INSERT INTO t1 VALUES ('10:20:30.999'); SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; c1 c2 c2 -102030 102030.999 102031 +102030 102030.999 10:20:31 DROP TABLE t1; SELECT CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index 2eaec8a08b4..44ebce66b22 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -1608,7 +1608,8 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1, t2; CREATE TABLE t1( diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 484ee626b98..7e57c916d04 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -558,8 +558,9 @@ where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3); FirstMatch(t1) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) select * from t1 where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index b4572174fcb..2ec3a9ae951 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -732,8 +732,8 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); @@ -745,8 +745,8 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); diff --git a/mysql-test/main/invisible_field_debug.result b/mysql-test/main/invisible_field_debug.result index 32eb3a274d8..582c935333c 100644 --- a/mysql-test/main/invisible_field_debug.result +++ b/mysql-test/main/invisible_field_debug.result @@ -385,3 +385,15 @@ SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; CREATE TABLE t2 LIKE t1; SET debug_dbug= DEFAULT; DROP TABLE t1, t2; +# +# MDEV-20210 +# If you have an INVISIBLE VIRTUAL column, SHOW CREATE TABLE doesn't list it as INVISIBLE +# +CREATE TABLE t1 (i INT, v int GENERATED ALWAYS AS (1) VIRTUAL INVISIBLE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (1) VIRTUAL INVISIBLE +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/main/invisible_field_debug.test b/mysql-test/main/invisible_field_debug.test index 77e65cf7a6b..3e844fc4521 100644 --- a/mysql-test/main/invisible_field_debug.test +++ b/mysql-test/main/invisible_field_debug.test @@ -281,3 +281,12 @@ SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; CREATE TABLE t2 LIKE t1; SET debug_dbug= DEFAULT; DROP TABLE t1, t2; + +--echo # +--echo # MDEV-20210 +--echo # If you have an INVISIBLE VIRTUAL column, SHOW CREATE TABLE doesn't list it as INVISIBLE +--echo # + +CREATE TABLE t1 (i INT, v int GENERATED ALWAYS AS (1) VIRTUAL INVISIBLE); +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index edca2905380..f714785593d 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -2935,6 +2935,332 @@ NULL NULL NULL 9 NULL NULL NULL 5 drop table t1,t2,t3,t4,s1,s2; # +# MDEV-20265: Mix of comma joins with JOIN expressions +# (correction of the fix for MDEV-19421) +# MDEV-20330: duplicate +# +create table t1 (a int); +insert into t1 values (7), (5), (3); +create table t2 (a int); +insert into t2 values (5), (1), (7); +create table t3 (a int); +insert into t3 values (2), (7), (3); +create table t4 (a int); +insert into t4 values (4), (7), (9), (5); +create table t5 (a int); +insert into t5 values (3), (7), (9), (2); +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 left join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`a` = `test`.`t3`.`a`) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 left join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 2 NULL +5 5 2 NULL +3 5 2 NULL +7 1 2 NULL +5 1 2 NULL +3 1 2 NULL +7 7 2 NULL +5 7 2 NULL +3 7 2 NULL +7 5 3 NULL +5 5 3 NULL +3 5 3 NULL +7 1 3 NULL +5 1 3 NULL +3 1 3 NULL +7 7 3 NULL +5 7 3 NULL +3 7 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 right join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t4` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`a` = `test`.`t4`.`a`) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 right join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 NULL NULL 4 +5 NULL NULL 4 +3 NULL NULL 4 +7 NULL NULL 9 +5 NULL NULL 9 +3 NULL NULL 9 +7 NULL NULL 5 +5 NULL NULL 5 +3 NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` left join `test`.`t5` on(`test`.`t5`.`a` = `test`.`t4`.`a`) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a; +t1_a t2_a t3_a t4_a t5_a +7 5 2 7 7 +5 5 2 7 7 +3 5 2 7 7 +7 1 2 7 7 +5 1 2 7 7 +3 1 2 7 7 +7 7 2 7 7 +5 7 2 7 7 +3 7 2 7 7 +7 5 7 7 7 +5 5 7 7 7 +3 5 7 7 7 +7 1 7 7 7 +5 1 7 7 7 +3 1 7 7 7 +7 7 7 7 7 +5 7 7 7 7 +3 7 7 7 7 +7 5 3 7 7 +5 5 3 7 7 +3 5 3 7 7 +7 1 3 7 7 +5 1 3 7 7 +3 1 3 7 7 +7 7 3 7 7 +5 7 3 7 7 +3 7 3 7 7 +7 5 2 9 9 +5 5 2 9 9 +3 5 2 9 9 +7 1 2 9 9 +5 1 2 9 9 +3 1 2 9 9 +7 7 2 9 9 +5 7 2 9 9 +3 7 2 9 9 +7 5 7 9 9 +5 5 7 9 9 +3 5 7 9 9 +7 1 7 9 9 +5 1 7 9 9 +3 1 7 9 9 +7 7 7 9 9 +5 7 7 9 9 +3 7 7 9 9 +7 5 3 9 9 +5 5 3 9 9 +3 5 3 9 9 +7 1 3 9 9 +5 1 3 9 9 +3 1 3 9 9 +7 7 3 9 9 +5 7 3 9 9 +3 7 3 9 9 +7 5 2 4 NULL +5 5 2 4 NULL +3 5 2 4 NULL +7 1 2 4 NULL +5 1 2 4 NULL +3 1 2 4 NULL +7 7 2 4 NULL +5 7 2 4 NULL +3 7 2 4 NULL +7 5 7 4 NULL +5 5 7 4 NULL +3 5 7 4 NULL +7 1 7 4 NULL +5 1 7 4 NULL +3 1 7 4 NULL +7 7 7 4 NULL +5 7 7 4 NULL +3 7 7 4 NULL +7 5 3 4 NULL +5 5 3 4 NULL +3 5 3 4 NULL +7 1 3 4 NULL +5 1 3 4 NULL +3 1 3 4 NULL +7 7 3 4 NULL +5 7 3 4 NULL +3 7 3 4 NULL +7 5 2 5 NULL +5 5 2 5 NULL +3 5 2 5 NULL +7 1 2 5 NULL +5 1 2 5 NULL +3 1 2 5 NULL +7 7 2 5 NULL +5 7 2 5 NULL +3 7 2 5 NULL +7 5 7 5 NULL +5 5 7 5 NULL +3 5 7 5 NULL +7 1 7 5 NULL +5 1 7 5 NULL +3 1 7 5 NULL +7 7 7 5 NULL +5 7 7 5 NULL +3 7 7 5 NULL +7 5 3 5 NULL +5 5 3 5 NULL +3 5 3 5 NULL +7 1 3 5 NULL +5 1 3 5 NULL +3 1 3 5 NULL +7 7 3 5 NULL +5 7 3 5 NULL +3 7 3 5 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t5 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` join `test`.`t5` left join (`test`.`t2` join `test`.`t3` join `test`.`t4`) on(`test`.`t4`.`a` = `test`.`t5`.`a`) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a; +t1_a t2_a t3_a t4_a t5_a +7 5 2 7 7 +5 5 2 7 7 +3 5 2 7 7 +7 1 2 7 7 +5 1 2 7 7 +3 1 2 7 7 +7 7 2 7 7 +5 7 2 7 7 +3 7 2 7 7 +7 5 7 7 7 +5 5 7 7 7 +3 5 7 7 7 +7 1 7 7 7 +5 1 7 7 7 +3 1 7 7 7 +7 7 7 7 7 +5 7 7 7 7 +3 7 7 7 7 +7 5 3 7 7 +5 5 3 7 7 +3 5 3 7 7 +7 1 3 7 7 +5 1 3 7 7 +3 1 3 7 7 +7 7 3 7 7 +5 7 3 7 7 +3 7 3 7 7 +7 5 2 9 9 +5 5 2 9 9 +3 5 2 9 9 +7 1 2 9 9 +5 1 2 9 9 +3 1 2 9 9 +7 7 2 9 9 +5 7 2 9 9 +3 7 2 9 9 +7 5 7 9 9 +5 5 7 9 9 +3 5 7 9 9 +7 1 7 9 9 +5 1 7 9 9 +3 1 7 9 9 +7 7 7 9 9 +5 7 7 9 9 +3 7 7 9 9 +7 5 3 9 9 +5 5 3 9 9 +3 5 3 9 9 +7 1 3 9 9 +5 1 3 9 9 +3 1 3 9 9 +7 7 3 9 9 +5 7 3 9 9 +3 7 3 9 9 +7 NULL NULL NULL 3 +5 NULL NULL NULL 3 +3 NULL NULL NULL 3 +7 NULL NULL NULL 2 +5 NULL NULL NULL 2 +3 NULL NULL NULL 2 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`a` = `test`.`t1`.`a`) join `test`.`t5` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t4`.`a` = `test`.`t5`.`a`) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a; +t1_a t2_a t3_a t4_a t5_a +5 5 2 7 7 +7 7 2 7 7 +3 NULL 2 7 7 +5 5 7 7 7 +7 7 7 7 7 +3 NULL 7 7 7 +5 5 3 7 7 +7 7 3 7 7 +3 NULL 3 7 7 +5 5 2 9 9 +7 7 2 9 9 +3 NULL 2 9 9 +5 5 7 9 9 +7 7 7 9 9 +3 NULL 7 9 9 +5 5 3 9 9 +7 7 3 9 9 +3 NULL 3 9 9 +5 5 NULL NULL 3 +7 7 NULL NULL 3 +3 NULL NULL NULL 3 +5 5 NULL NULL 2 +7 7 NULL NULL 2 +3 NULL NULL NULL 2 +drop table t1,t2,t3,t4,t5; +select a.a +from (select 1 as a) a, +(select 2 as b) b +cross join +(select 3 as c) c +left join +(select 4 as d) d +on 1; +a +1 +# # End of MariaDB 5.5 tests # # diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index b36b0ac8f35..cf24fb04e4a 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1615,6 +1615,65 @@ eval $q; drop table t1,t2,t3,t4,s1,s2; --echo # +--echo # MDEV-20265: Mix of comma joins with JOIN expressions +--echo # (correction of the fix for MDEV-19421) +--echo # MDEV-20330: duplicate +--echo # + +create table t1 (a int); +insert into t1 values (7), (5), (3); +create table t2 (a int); +insert into t2 values (5), (1), (7); +create table t3 (a int); +insert into t3 values (2), (7), (3); +create table t4 (a int); +insert into t4 values (4), (7), (9), (5); +create table t5 (a int); +insert into t5 values (3), (7), (9), (2); + + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 left join t4 on t3.a=t4.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 right join t4 on t3.a=t4.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a; +eval explain extended $q; +eval $q; + +drop table t1,t2,t3,t4,t5; + +select a.a +from (select 1 as a) a, + (select 2 as b) b + cross join + (select 3 as c) c + left join + (select 4 as d) d + on 1; + +--echo # --echo # End of MariaDB 5.5 tests --echo # diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index 8ea6d36c321..a4955b3e7b5 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -1462,4 +1462,19 @@ t1 CREATE TABLE `t1` ( KEY `pk` (`pk`,`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# MDEV-19705: Assertion `tmp >= 0' failed in best_access_path +# +CREATE TABLE t1 (d varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('q'); +CREATE TABLE t2 (f varchar(10), a2 datetime, b int, a1 varchar(1024), pk int NOT NULL, PRIMARY KEY (pk), UNIQUE KEY (f,a1,a2), KEY f2 (f(4),a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('aaa','1985-09-06',-163,'s',1),('bbb','1995-01-05',3,'pucaz',2),('ccc','0000-00-00',NULL,'help',3),('ddd',NULL,618,'v',4),('eee','1995-12-20',410,'m',5),('ffq','1976-06-12 20:02:56',NULL,'POKNC',6),('dddd','0000-00-00',-328,'hgsu',7); +explain +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,f,f2 PRIMARY 4 NULL 1 Using index condition +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +b +drop table t1,t2; set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/long_unique.test b/mysql-test/main/long_unique.test index a6bc68f54dc..c0bd77ca5c9 100644 --- a/mysql-test/main/long_unique.test +++ b/mysql-test/main/long_unique.test @@ -542,4 +542,18 @@ alter table t1 modify a varchar(1000); show create table t1; drop table t1; +--echo # +--echo # MDEV-19705: Assertion `tmp >= 0' failed in best_access_path +--echo # + +CREATE TABLE t1 (d varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('q'); + +CREATE TABLE t2 (f varchar(10), a2 datetime, b int, a1 varchar(1024), pk int NOT NULL, PRIMARY KEY (pk), UNIQUE KEY (f,a1,a2), KEY f2 (f(4),a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('aaa','1985-09-06',-163,'s',1),('bbb','1995-01-05',3,'pucaz',2),('ccc','0000-00-00',NULL,'help',3),('ddd',NULL,618,'v',4),('eee','1995-12-20',410,'m',5),('ffq','1976-06-12 20:02:56',NULL,'POKNC',6),('dddd','0000-00-00',-328,'hgsu',7); +explain +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +drop table t1,t2; + set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index e315b0212f7..13110f0bc73 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2663,6 +2663,11 @@ explain extended select * from t1 where a in (select pk from t10) { ] }, { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { "execution_plan_for_potential_materialization": { "steps": [ { @@ -2705,6 +2710,7 @@ explain extended select * from t1 where a in (select pk from t10) { }, "rows_for_plan": 3, "cost_for_plan": 2.6066, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -2720,7 +2726,27 @@ explain extended select * from t1 where a in (select pk from t10) { ] }, "rows_for_plan": 30, - "cost_for_plan": 10.629 + "cost_for_plan": 10.629, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 10.629 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 5.2786 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 27.129 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ] } ] }, @@ -2739,6 +2765,7 @@ explain extended select * from t1 where a in (select pk from t10) { }, "rows_for_plan": 10, "cost_for_plan": 4.022, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -2746,7 +2773,7 @@ explain extended select * from t1 where a in (select pk from t10) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t10" @@ -4082,6 +4109,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { "execution_plan_for_potential_materialization": { "steps": [ { @@ -4159,6 +4191,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -4175,6 +4208,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1", "t_inner_1"], @@ -4190,7 +4224,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, "rows_for_plan": 27, - "cost_for_plan": 13.815 + "cost_for_plan": 13.815, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 33.867 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 7.2154 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 18.315 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ] } ] }, @@ -4209,6 +4263,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4228,6 +4283,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -4245,6 +4301,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4252,7 +4309,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_1" @@ -4308,11 +4365,11 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 FirstMatch(t_outer_1); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and @@ -4512,6 +4569,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { "execution_plan_for_potential_materialization": { "steps": [] } @@ -4533,6 +4600,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], @@ -4549,6 +4617,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], @@ -4565,6 +4634,21 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 44.759 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.226 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -4580,7 +4664,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 27, - "cost_for_plan": 34.174, + "cost_for_plan": 44.641, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4601,7 +4686,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 52.379, + "cost_for_plan": 62.846, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4623,7 +4709,22 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 200.19 + "cost_for_plan": 210.66, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 424.03 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 324.06 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ] } ] }, @@ -4646,7 +4747,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 243, - "cost_for_plan": 84.79, + "cost_for_plan": 95.256, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4665,7 +4767,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 9, - "cost_for_plan": 30.564, + "cost_for_plan": 41.031, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4686,7 +4789,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 48.779, + "cost_for_plan": 59.246, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4708,8 +4812,17 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 196.59, - "pruned_by_cost": true + "cost_for_plan": 207.06, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 320.46 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ] } ] }, @@ -4732,7 +4845,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 48.779, + "cost_for_plan": 59.246, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4751,7 +4865,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 27, - "cost_for_plan": 34.174, + "cost_for_plan": 44.641, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4771,6 +4886,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], @@ -4787,7 +4903,98 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, - "pruned_by_cost": true + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 285.84 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rows_for_plan": 81, + "cost_for_plan": 304.05, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 729, + "cost_for_plan": 451.86, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 565.26 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 243, + "cost_for_plan": 336.46, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], @@ -4804,6 +5011,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 75.231, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4825,6 +5033,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -4847,6 +5056,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -4866,7 +5076,64 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, - "pruned_by_heuristic": true + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rows_for_plan": 2187, + "cost_for_plan": 611.85, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 81, + "read_time": 2232.8 + }, + { + "chosen_strategy": "FirstMatch" + } + ], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 6561, + "cost_for_plan": 1486.7, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] } ] }, @@ -4885,6 +5152,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 13.815, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], @@ -4901,6 +5169,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 64.431, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4922,6 +5191,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 503.85, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -4944,6 +5214,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 503.85, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -4963,6 +5234,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 64.431, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -4980,6 +5252,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 64.431, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4999,6 +5272,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], @@ -5015,6 +5289,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5036,6 +5311,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 611.85, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5058,6 +5334,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 6561, "cost_for_plan": 1486.7, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5077,6 +5354,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 75.231, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5098,6 +5376,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5120,6 +5399,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5139,6 +5419,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5160,6 +5441,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5177,6 +5459,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5194,6 +5477,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5211,6 +5495,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5230,6 +5515,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5247,6 +5533,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5264,6 +5551,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5281,6 +5569,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5298,75 +5587,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "fix_semijoin_strategies_for_picked_join_order": [ - { - "semi_join_strategy": "firstmatch", - "join_order": [ - { - "table": "t_inner_4" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 162.42, - "chosen": true - } - ] - }, - { - "table": "t_inner_3" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 489.74, - "chosen": true - } - ] - } - ] - }, - { - "semi_join_strategy": "firstmatch", - "join_order": [ - { - "table": "t_inner_1" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 18.046, - "chosen": true - } - ] - }, - { - "table": "t_inner_2" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 54.415, - "chosen": true - } - ] - } - ] - } - ] + "fix_semijoin_strategies_for_picked_join_order": [] }, { "attaching_conditions_to_tables": { @@ -5386,11 +5613,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_outer_2", + "table": "t_inner_4", "attached": null }, { - "table": "t_inner_4", + "table": "t_outer_2", "attached": null }, { @@ -5622,6 +5849,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { "execution_plan_for_potential_materialization": { "steps": [ { @@ -5754,6 +5991,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], @@ -5770,6 +6008,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], @@ -5786,6 +6025,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 44.759 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 8.1256 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.226 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -5802,6 +6061,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 15.541, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5823,6 +6083,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 33.746, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5844,7 +6105,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 181.56 + "cost_for_plan": 181.56, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 394.93 + }, + { + "strategy": "SJ-Materialization", + "records": 27, + "read_time": 22.262 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 294.96 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ] } ] }, @@ -5868,6 +6149,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 66.156, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5887,6 +6169,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 11.931, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5908,6 +6191,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 30.146, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5930,6 +6214,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 30.146, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5949,6 +6234,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 15.541, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5968,6 +6254,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5985,6 +6272,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 13.815, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6002,6 +6290,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -6021,6 +6310,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6038,6 +6328,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6055,6 +6346,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6072,6 +6364,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -6091,6 +6384,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6108,6 +6402,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6125,6 +6420,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6142,6 +6438,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6159,6 +6456,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -6166,7 +6464,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_4" @@ -6177,7 +6475,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_1" @@ -6984,4 +7282,74 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ] ] drop table t0, one_k; +# +# Assertion `to_len >= 8' failed in convert_to_printable +# +CREATE TABLE t1 ( a blob, KEY (a(255))); +insert into t1 values ('foo'), ('bar'); +EXPLAIN SELECT * FROM t1 WHERE a= REPEAT('a', 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 258 const 1 Using where +SELECT * FROM t1 WHERE a= REPEAT('a', 0); +a +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) +[ + + { + "range_scan_alternatives": + [ + + { + "index": "a", + "ranges": + [ + "() <= (a) <= ()" + ], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.4265, + "chosen": true + } + ], + "analyzing_roworder_intersect": + { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": + [ + ] + } +] +DROP TABLE t1; +# +# Test for Semi-Join table pullout element +# +create table t1 (a int primary key, b int); +insert into t1 (a) values (1),(2),(3),(4),(5); +create table t2 (a int primary key, b int); +insert into t2 (a) values (1),(2),(3),(4),(5); +create table t3 (a int); +insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +explain +select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) +[ + + { + "pulled_out_tables": + [ + "t2", + "t1" + ] + } +] +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index e5d635db042..51950f00781 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -536,4 +536,35 @@ explain select * from t0 A, one_k B where A.a=B.b and B.a<800; select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; drop table t0, one_k; +--echo # +--echo # Assertion `to_len >= 8' failed in convert_to_printable +--echo # + +CREATE TABLE t1 ( a blob, KEY (a(255))); +insert into t1 values ('foo'), ('bar'); + +EXPLAIN SELECT * FROM t1 WHERE a= REPEAT('a', 0); +SELECT * FROM t1 WHERE a= REPEAT('a', 0); +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +DROP TABLE t1; + +--echo # +--echo # Test for Semi-Join table pullout element +--echo # +create table t1 (a int primary key, b int); +insert into t1 (a) values (1),(2),(3),(4),(5); + +create table t2 (a int primary key, b int); +insert into t2 (a) values (1),(2),(3),(4),(5); + +create table t3 (a int); +insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); + +explain +select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); + +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index d503dab4d99..5329a9f64be 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -46,12 +46,12 @@ a b 2 5 explain extended select * from t1 where a in (1,2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a in ( @@ -59,12 +59,12 @@ select * from (values (1),(2)) as tvc_0 ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # AND-condition with IN-predicates in WHERE-part select * from t1 where a in (1,2) and @@ -90,15 +90,15 @@ explain extended select * from t1 where a in (1,2) and b in (1,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1 explain extended select * from t1 where a in ( @@ -111,15 +111,15 @@ select * from (values (1),(5)) as tvc_1 ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1 # subquery with IN-predicate select * from t1 where a in @@ -206,12 +206,12 @@ from t1 where a in (1,2) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from ( select * @@ -224,12 +224,12 @@ as tvc_0 ) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # non-recursive CTE with IN-predicate with tvc_0 as ( @@ -265,12 +265,12 @@ where a in (1,2) ) select * from tvc_0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from ( select * @@ -283,12 +283,12 @@ as tvc_0 ) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # VIEW with IN-predicate create view v1 as select * @@ -316,20 +316,20 @@ a b 2 5 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from v2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 drop view v1,v2; # subselect defined by derived table with IN-predicate select * from t1 @@ -519,12 +519,12 @@ a b 1 2 explain extended select * from t1 where (a,b) in ((1,2),(3,4)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` and `test`.`t1`.`b` = `tvc_0`.`_col_2` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1 set @@in_predicate_conversion_threshold= 2; # trasformation works for the one IN predicate and doesn't work for the other set @@in_predicate_conversion_threshold= 5; @@ -538,12 +538,12 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`_col_1` and `test`.`t2`.`c` = `tvc_0`.`_col_2` and (`tvc_0`.`_col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) set @@in_predicate_conversion_threshold= 2; # # mdev-14281: conversion of NOT IN predicate into subquery predicate diff --git a/mysql-test/main/partition_range.result b/mysql-test/main/partition_range.result index 48b1ce87555..2cb9e3f4ab8 100644 --- a/mysql-test/main/partition_range.result +++ b/mysql-test/main/partition_range.result @@ -987,4 +987,31 @@ a MAX(b) SHOW status LIKE 'handler_read_key'; Variable_name Value Handler_read_key 2 +# +# MDEV-18501 Partition pruning doesn't work for historical queries +# +set time_zone= '+00:00'; +create or replace table t1 (d datetime(6)) +partition by range (unix_timestamp(d)) ( +partition p0 values less than (1), +partition p1 values less than (maxvalue)); +ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +# DECIMAL functions are now allowed, partitioning is done by integer part +create or replace table t1 (d timestamp(6)) +partition by range (unix_timestamp(d)) ( +partition p0 values less than (946684801), +partition p1 values less than (maxvalue)); +insert into t1 values +# go to p0 +('2000-01-01 00:00:00'), +('2000-01-01 00:00:00.000001'), +# goes to p1 +('2000-01-01 00:00:01'); +select * from t1 partition (p0); +d +2000-01-01 00:00:00.000000 +2000-01-01 00:00:00.000001 +select * from t1 partition (p1); +d +2000-01-01 00:00:01.000000 DROP TABLE t1, t2; diff --git a/mysql-test/main/partition_range.test b/mysql-test/main/partition_range.test index 7f637f83ed9..37702db052b 100644 --- a/mysql-test/main/partition_range.test +++ b/mysql-test/main/partition_range.test @@ -973,4 +973,31 @@ SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; --echo # Should be no more than 4 reads. SHOW status LIKE 'handler_read_key'; +--echo # +--echo # MDEV-18501 Partition pruning doesn't work for historical queries +--echo # +set time_zone= '+00:00'; +let $ts= `select unix_timestamp('2000-01-01 00:00:00') + 1`; + +--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR +create or replace table t1 (d datetime(6)) +partition by range (unix_timestamp(d)) ( + partition p0 values less than (1), + partition p1 values less than (maxvalue)); + +--echo # DECIMAL functions are now allowed, partitioning is done by integer part +eval create or replace table t1 (d timestamp(6)) +partition by range (unix_timestamp(d)) ( + partition p0 values less than ($ts), + partition p1 values less than (maxvalue)); + +insert into t1 values + # go to p0 + ('2000-01-01 00:00:00'), + ('2000-01-01 00:00:00.000001'), + # goes to p1 + ('2000-01-01 00:00:01'); +select * from t1 partition (p0); +select * from t1 partition (p1); + DROP TABLE t1, t2; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index d83239eb25f..390c7834a0a 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2179,7 +2179,7 @@ count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref b,a b 5 const 59 55.93 Using where +1 SIMPLE t1 ref b,a b 5 const 59 3.30 Using where Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; @@ -2196,7 +2196,7 @@ count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 55.93 Using where; Using rowid filter +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 3.30 Using where; Using rowid filter Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 33c7c9be47a..7db951ccafe 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1637,3 +1637,37 @@ set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; drop table t1; drop function f1; +# +# MDEV-19834 Selectivity of an equality condition discounted twice +# +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables='preferably'; +create table t1 (a int, b int, key (b), key (a)); +insert into t1 +select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; +analyze table t1 ; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +# Check what info the optimizer has about selectivities +explain extended select * from t1 use index () where a in (17,51,5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.90 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5) +explain extended select * from t1 use index () where b=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 5.47 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2 +# Now, the equality is used for ref access, while the range condition +# gives selectivity data +explain extended select * from t1 where a in (17,51,5) and b=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) +drop table t1; +set use_stat_tables= @save_use_stat_tables; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# End of 10.1 tests diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index eb3f6e2893a..d911957ddc7 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1,4 +1,5 @@ --source include/have_stat_tables.inc +--source include/have_sequence.inc --disable_warnings drop table if exists t0,t1,t2,t3; @@ -1104,3 +1105,26 @@ set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectiv drop table t1; drop function f1; +--echo # +--echo # MDEV-19834 Selectivity of an equality condition discounted twice +--echo # +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables='preferably'; +create table t1 (a int, b int, key (b), key (a)); +insert into t1 +select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; +analyze table t1 ; + +--echo # Check what info the optimizer has about selectivities +explain extended select * from t1 use index () where a in (17,51,5); +explain extended select * from t1 use index () where b=2; + +--echo # Now, the equality is used for ref access, while the range condition +--echo # gives selectivity data +explain extended select * from t1 where a in (17,51,5) and b=2; +drop table t1; + +set use_stat_tables= @save_use_stat_tables; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +--echo # End of 10.1 tests + diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 92091f0d6db..16f84ff3402 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1647,6 +1647,40 @@ set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; drop table t1; drop function f1; +# +# MDEV-19834 Selectivity of an equality condition discounted twice +# +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables='preferably'; +create table t1 (a int, b int, key (b), key (a)); +insert into t1 +select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; +analyze table t1 ; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +# Check what info the optimizer has about selectivities +explain extended select * from t1 use index () where a in (17,51,5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.90 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5) +explain extended select * from t1 use index () where b=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 5.47 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2 +# Now, the equality is used for ref access, while the range condition +# gives selectivity data +explain extended select * from t1 where a in (17,51,5) and b=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 2.90 Using where; Using rowid filter +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) +drop table t1; +set use_stat_tables= @save_use_stat_tables; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# End of 10.1 tests set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; set @tmp_oucs= @@optimizer_use_condition_selectivity; diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index fc0049db997..5e446a171fe 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -5703,8 +5703,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); @@ -5714,8 +5714,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index ca151daf146..3c33182b3ef 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -1139,8 +1139,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1276,12 +1276,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) drop table t1,t2; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); @@ -1339,9 +1339,9 @@ insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1) +1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index adb3ec80394..69f720a95b7 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -1149,8 +1149,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1286,12 +1286,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) drop table t1,t2; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); @@ -1348,10 +1348,10 @@ create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1); Using join buffer (incremental, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Start temporary; Using join buffer (flat, BNLH join) +1 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 4e03d9663ef..ee680d40b75 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1510,8 +1510,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2437,8 +2437,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2450,8 +2450,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2507,8 +2507,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 4ebf04b1f55..ecd973da09d 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -5683,9 +5683,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) -1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(ot4) +1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; # # Bug#729039: NULL keys used to evaluate subquery @@ -5712,8 +5712,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index idx idx 5 NULL 3 Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 2 Using index; FirstMatch(t1) DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 71d61e797f3..318ad02d473 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -5709,8 +5709,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); @@ -5720,8 +5720,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 98143246673..7c15cbf721e 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -729,8 +729,8 @@ SELECT int_key FROM ot1 WHERE int_nokey IN (SELECT it2.int_key FROM it1 LEFT JOIN it2 ON it2.datetime_key); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 index NULL int_key 4 NULL 2 Using index 2 MATERIALIZED it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where DROP TABLE ot1, it1, it2; @@ -972,11 +972,11 @@ SELECT `varchar_key` , `varchar_nokey` FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1246,8 +1246,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1757,8 +1757,8 @@ insert into t3 values('three'),( 'four'); insert into t3 values('three'),( 'four'); explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); @@ -1991,12 +1991,13 @@ CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ; explain extended SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY t4 ref f2 f2 5 test.t2.f3 2 100.00 Using index; FirstMatch(t2) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t4 index f2 f2 5 NULL 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); f1 f2 f3 f3 2 0 0 0 @@ -2495,8 +2496,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 func 1 Using index +1 PRIMARY t2 ref a a 5 const 1 Using index +1 PRIMARY t1 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); @@ -2706,8 +2707,8 @@ a 19 explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) drop table t0,t1,t3; set optimizer_switch= @tmp_923246; # @@ -2923,8 +2924,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where -1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary +1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary SELECT * FROM t2 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1 @@ -3264,4 +3265,27 @@ create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; # End of 5.5 test +# +# MDEV-20109: Optimizer ignores distinct key created for materialized +# semi-join subquery when searching for best execution plan +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; +# The following must not use this query plan that does a cross join: +# | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +# | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +# +# Instead, it should use eq_ref on the materialized table. +explain select * from t3 where a in (select a from t4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 +drop table t1, t2, t3, t4; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index b693f7b5b93..13f6bece181 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -2940,5 +2940,30 @@ drop table t1,t2; --echo # End of 5.5 test +--echo # +--echo # MDEV-20109: Optimizer ignores distinct key created for materialized +--echo # semi-join subquery when searching for best execution plan +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; + +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; + +--echo # The following must not use this query plan that does a cross join: +--echo # | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +--echo # | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +--echo # +--echo # Instead, it should use eq_ref on the materialized table. + +explain select * from t3 where a in (select a from t4); + +drop table t1, t2, t3, t4; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index a127c18280e..bab21da8243 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -131,8 +131,9 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b 1 1 @@ -159,8 +160,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -227,8 +228,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -730,8 +731,9 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -757,9 +759,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -810,6 +813,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK # The following must use LooseScan but not join buffering explain SELECT * FROM t3 @@ -1099,6 +1110,14 @@ INSERT INTO t2 VALUES (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( @@ -1107,11 +1126,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1128,11 +1147,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test index 2b4f619a615..8886c42eb55 100644 --- a/mysql-test/main/subselect_sj2.test +++ b/mysql-test/main/subselect_sj2.test @@ -994,6 +994,7 @@ INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; --echo # The following must use LooseScan but not join buffering --replace_column 9 # @@ -1225,6 +1226,8 @@ INSERT INTO t2 VALUES (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +analyze table t2 persistent for all; --replace_column 9 # EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 56c11e8c9af..ac2d12fc5df 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -171,9 +171,9 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 32 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED it ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -239,9 +239,9 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 52 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED it ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -826,6 +826,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK # The following must use LooseScan but not join buffering explain SELECT * FROM t3 @@ -1115,6 +1123,14 @@ INSERT INTO t2 VALUES (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( @@ -1123,11 +1139,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index; Using join buffer (flat, BNL join) -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3); Using join buffer (incremental, BKA join); Key-ordered scan +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) 1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) -1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1144,11 +1160,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index; Using join buffer (flat, BNL join) -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3); Using join buffer (incremental, BKA join); Key-ordered scan +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) 1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) -1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1410,9 +1426,10 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ref b b 4 test.t3.b 1 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); b c @@ -1438,9 +1455,10 @@ EXPLAIN SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t4 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 Using where +2 MATERIALIZED t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); pk a b 1 6 8 diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 73f682755da..e55025f4fa8 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -133,8 +133,9 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b 1 1 @@ -161,8 +162,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -229,8 +230,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -732,8 +733,9 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -759,9 +761,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -812,6 +815,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK # The following must use LooseScan but not join buffering explain SELECT * FROM t3 @@ -1101,6 +1112,14 @@ INSERT INTO t2 VALUES (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( @@ -1109,11 +1128,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1130,11 +1149,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1638,12 +1657,12 @@ set optimizer_switch='materialization=on,semijoin=on'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); i1 7 @@ -1651,12 +1670,12 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 @@ -1748,8 +1767,8 @@ OR ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index -2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8 -2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 +2 MATERIALIZED <subquery3> eq_ref distinct_key distinct_key 67 func 1 3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where SELECT SQL_NO_CACHE t.id FROM t1 t @@ -1914,18 +1933,16 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 12 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t2_3 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary +1 PRIMARY t2_5 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary 1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 Using where -5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where -6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 Using index condition; Using where 2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51 -4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where set optimizer_switch='rowid_filter=default'; drop table t1,t2,t3,t4,t5; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index acfafde6d7e..91892156e76 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -742,8 +742,8 @@ SELECT int_key FROM ot1 WHERE int_nokey IN (SELECT it2.int_key FROM it1 LEFT JOIN it2 ON it2.datetime_key); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 index NULL int_key 4 NULL 2 Using index 2 MATERIALIZED it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) DROP TABLE ot1, it1, it2; @@ -985,11 +985,11 @@ SELECT `varchar_key` , `varchar_nokey` FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1259,8 +1259,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1770,8 +1770,8 @@ insert into t3 values('three'),( 'four'); insert into t3 values('three'),( 'four'); explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); @@ -2509,8 +2509,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 func 1 Using index +1 PRIMARY t2 ref a a 5 const 1 Using index +1 PRIMARY t1 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); @@ -2720,8 +2720,8 @@ a 19 explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) drop table t0,t1,t3; set optimizer_switch= @tmp_923246; # @@ -2937,8 +2937,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where -1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary SELECT * FROM t2 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1 @@ -3278,6 +3278,29 @@ create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; # End of 5.5 test +# +# MDEV-20109: Optimizer ignores distinct key created for materialized +# semi-join subquery when searching for best execution plan +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; +# The following must not use this query plan that does a cross join: +# | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +# | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +# +# Instead, it should use eq_ref on the materialized table. +explain select * from t3 where a in (select a from t4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 +drop table t1, t2, t3, t4; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off @@ -3485,8 +3508,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) @@ -3499,8 +3522,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 203dbee6374..432f6c648fc 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -491,15 +491,15 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and a1 = c1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t3`.`c2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' select * from t1, t3 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (c1, c2) in (select c1, c2 from t3 @@ -1132,11 +1132,11 @@ insert into t3 values (30); explain extended select a from t1 where a in (select c from t2 where d >= 20); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t2`.`c` and `test`.`t2`.`d` >= 20 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1543,8 +1543,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2473,8 +2473,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2486,8 +2486,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2543,8 +2543,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 7fe7000c95d..1518cee174e 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -743,21 +743,21 @@ a b explain extended select * from t1 where a in (values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -978,21 +978,21 @@ a b explain extended select * from t1 where a = any (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); diff --git a/mysql-test/main/timezone2.result b/mysql-test/main/timezone2.result index cf3c6e01e25..b858e761a81 100644 --- a/mysql-test/main/timezone2.result +++ b/mysql-test/main/timezone2.result @@ -588,5 +588,53 @@ ts cts uts ucts DROP TABLE t1,t2; SET time_zone=DEFAULT; # +# MDEV-19961 MIN(timestamp_column) returns a wrong result in a GROUP BY query +# +SET time_zone='Europe/Moscow'; +CREATE OR REPLACE TABLE t1 (i INT, d TIMESTAMP); +SET timestamp=1288477526 /* this is summer time */ ; +INSERT INTO t1 VALUES (3,NULL); +SET timestamp=1288477526+3599 /* this is winter time*/ ; +INSERT INTO t1 VALUES (3,NULL); +SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d; +i d UNIX_TIMESTAMP(d) +3 2010-10-31 02:25:26 1288477526 +3 2010-10-31 02:25:25 1288481125 +SELECT i, MIN(d) FROM t1 GROUP BY i; +i MIN(d) +3 2010-10-31 02:25:26 +SELECT i, MAX(d) FROM t1 GROUP BY i; +i MAX(d) +3 2010-10-31 02:25:25 +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (i INT, d TIMESTAMP(6)); +SET timestamp=1288479599.999999 /* this is the last second in summer time */ ; +INSERT INTO t1 VALUES (1,NULL); +SET timestamp=1288479600.000000 /* this is the first second in winter time */ ; +INSERT INTO t1 VALUES (2,NULL); +SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d; +i d UNIX_TIMESTAMP(d) +1 2010-10-31 02:59:59.999999 1288479599.999999 +2 2010-10-31 02:00:00.000000 1288479600.000000 +CREATE TABLE t2 (i INT, d TIMESTAMP, expected_unix_timestamp INT UNSIGNED); +INSERT INTO t2 SELECT i, ROUND(d) AS d, ROUND(UNIX_TIMESTAMP(d)) FROM t1; +# UNIX_TIMESTAMP(d) and expected_unix_timestamp should return the same value. +# Currently they do not, because ROUND(timestamp) is performed as DATETIME. +# We should fix this eventually. +SELECT i, d, UNIX_TIMESTAMP(d), expected_unix_timestamp FROM t2 ORDER BY i; +i d UNIX_TIMESTAMP(d) expected_unix_timestamp +1 2010-10-31 03:00:00 1288483200 1288479600 +2 2010-10-31 02:00:00 1288476000 1288479600 +DROP TABLE t2; +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; +# # End of 10.4 tests # diff --git a/mysql-test/main/timezone2.test b/mysql-test/main/timezone2.test index e945923da7a..9d364224311 100644 --- a/mysql-test/main/timezone2.test +++ b/mysql-test/main/timezone2.test @@ -535,5 +535,49 @@ DROP TABLE t1,t2; SET time_zone=DEFAULT; --echo # +--echo # MDEV-19961 MIN(timestamp_column) returns a wrong result in a GROUP BY query +--echo # + +SET time_zone='Europe/Moscow'; +CREATE OR REPLACE TABLE t1 (i INT, d TIMESTAMP); +SET timestamp=1288477526 /* this is summer time */ ; +INSERT INTO t1 VALUES (3,NULL); +SET timestamp=1288477526+3599 /* this is winter time*/ ; +INSERT INTO t1 VALUES (3,NULL); +SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d; +SELECT i, MIN(d) FROM t1 GROUP BY i; +SELECT i, MAX(d) FROM t1 GROUP BY i; +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; + +--echo # +--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +--echo # + +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (i INT, d TIMESTAMP(6)); +SET timestamp=1288479599.999999 /* this is the last second in summer time */ ; +INSERT INTO t1 VALUES (1,NULL); +SET timestamp=1288479600.000000 /* this is the first second in winter time */ ; +INSERT INTO t1 VALUES (2,NULL); +SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d; + +CREATE TABLE t2 (i INT, d TIMESTAMP, expected_unix_timestamp INT UNSIGNED); +INSERT INTO t2 SELECT i, ROUND(d) AS d, ROUND(UNIX_TIMESTAMP(d)) FROM t1; + +--echo # UNIX_TIMESTAMP(d) and expected_unix_timestamp should return the same value. +--echo # Currently they do not, because ROUND(timestamp) is performed as DATETIME. +--echo # We should fix this eventually. + +SELECT i, d, UNIX_TIMESTAMP(d), expected_unix_timestamp FROM t2 ORDER BY i; +DROP TABLE t2; + +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; + + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result index a9d17b8eb83..fbf348c1a2f 100644 --- a/mysql-test/main/type_date.result +++ b/mysql-test/main/type_date.result @@ -890,6 +890,37 @@ DROP TABLE t2; DROP VIEW v1; DROP TABLE t1; # +# MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +# +CREATE TABLE t1 (d DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +d +1999-11-04 +DROP TABLE t1; +# +# MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +# +CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11'); +SELECT GREATEST(pk, d) FROM t1; +GREATEST(pk, d) +2018-06-22 +2018-07-11 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +SELECT GREATEST(pk, d) FROM v1; +GREATEST(pk, d) +2018-06-22 +2018-07-11 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.1 tests # # diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test index d795a01fd36..bd874ec51fb 100644 --- a/mysql-test/main/type_date.test +++ b/mysql-test/main/type_date.test @@ -606,6 +606,30 @@ DROP TABLE t2; DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +--echo # + +CREATE TABLE t1 (d DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11'); +SELECT GREATEST(pk, d) FROM t1; +SELECT GREATEST(pk, d) FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + --echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/main/type_datetime.result b/mysql-test/main/type_datetime.result index 0c33ddc3df1..5afb0257757 100644 --- a/mysql-test/main/type_datetime.result +++ b/mysql-test/main/type_datetime.result @@ -1156,6 +1156,48 @@ ExtractValue('foo','bar') i MIN(d) 3 1976-12-14 13:21:07 DROP TABLE t1; # +# MDEV-19034 ASAN unknown-crash in get_date_time_separator with PAD_CHAR_TO_FULL_LENGTH +# +SET SQL_MODE=DEFAULT; +CREATE OR REPLACE TABLE t1 (a CHAR(11)); +CREATE OR REPLACE TABLE t2 (b DATETIME); +INSERT INTO t1 VALUES ('2010-02-19') ; +SET SQL_MODE= 'PAD_CHAR_TO_FULL_LENGTH'; +INSERT INTO t2 SELECT * FROM t1; +DROP TABLE t1, t2; +SET SQL_MODE=DEFAULT; +# +# MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +# +CREATE TABLE t1 (d DATETIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +d +1999-11-04 00:00:00 +DROP TABLE t1; +# +# MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +# +CREATE TABLE t1 (pk INT NOT NULL, d DATETIME NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22 00:00:00'),(2,'2018-07-11 00:00:00'); +SELECT GREATEST(pk, d) FROM t1; +GREATEST(pk, d) +2018-06-22 00:00:00 +2018-07-11 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +SELECT GREATEST(pk, d) FROM v1; +GREATEST(pk, d) +2018-06-22 00:00:00 +2018-07-11 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.1 tests # # diff --git a/mysql-test/main/type_datetime.test b/mysql-test/main/type_datetime.test index cecdda593d0..b4b5fe95bbf 100644 --- a/mysql-test/main/type_datetime.test +++ b/mysql-test/main/type_datetime.test @@ -711,6 +711,43 @@ SELECT ExtractValue('foo','bar'), i, MIN(d) FROM t1 GROUP BY i; DROP TABLE t1; --echo # +--echo # MDEV-19034 ASAN unknown-crash in get_date_time_separator with PAD_CHAR_TO_FULL_LENGTH +--echo # + +SET SQL_MODE=DEFAULT; +CREATE OR REPLACE TABLE t1 (a CHAR(11)); +CREATE OR REPLACE TABLE t2 (b DATETIME); +INSERT INTO t1 VALUES ('2010-02-19') ; +SET SQL_MODE= 'PAD_CHAR_TO_FULL_LENGTH'; +INSERT INTO t2 SELECT * FROM t1; +DROP TABLE t1, t2; +SET SQL_MODE=DEFAULT; + + +--echo # +--echo # MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +--echo # + +CREATE TABLE t1 (d DATETIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, d DATETIME NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22 00:00:00'),(2,'2018-07-11 00:00:00'); +SELECT GREATEST(pk, d) FROM t1; +SELECT GREATEST(pk, d) FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/main/type_datetime_hires.result b/mysql-test/main/type_datetime_hires.result index 38e2c2a5ac8..ebb9c6032b9 100644 --- a/mysql-test/main/type_datetime_hires.result +++ b/mysql-test/main/type_datetime_hires.result @@ -17,8 +17,8 @@ a 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 -select truncate(a, 6) from t1; -truncate(a, 6) +select cast(a AS double(30,6)) from t1; +cast(a AS double(30,6)) 0.000000 20101211002003.120000 20101211010203.457031 @@ -352,3 +352,542 @@ NULL 978307200.0000 DROP TABLE t1; SET @@time_zone=DEFAULT; +# +# Start of 10.4 tests +# +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +CREATE TABLE t1 (a1 DATETIME(6), a2 DATETIME(6) NOT NULL); +CREATE TABLE t2 AS SELECT +ROUND(a1) AS r1, +ROUND(a2) AS r2, +TRUNCATE(a1,0) AS t1, +TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `r1` datetime DEFAULT NULL, + `r2` datetime DEFAULT NULL, + `t1` datetime DEFAULT NULL, + `t2` datetime NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES +('0000-00-00 00:00:00.999999'), +('0000-00-00 23:59:59.999999'), +('0000-00-01 00:00:00.999999'), +('0000-00-01 23:59:59.999999'), +('0000-00-31 23:59:59.999999'), +('0000-01-01 00:00:00.999999'), +('0000-01-01 23:59:59.999999'), +('0000-01-31 23:59:59.999999'), +('0000-02-28 23:59:59.999999'), +('0000-12-31 23:59:59.999999'), +('0001-01-01 00:00:00.999999'), +('0001-02-28 23:59:59.999999'), +('0001-12-31 23:59:59.999999'), +('0004-02-28 23:59:59.999999'), +('0004-02-29 23:59:59.999999'), +('2000-02-29 23:59:59.999999'), +('2000-12-31 23:59:59.999999'), +('9999-12-31 23:59:59.999999'); +SELECT a, TRUNCATE(a,0) FROM t1; +a TRUNCATE(a,0) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +SELECT a, TRUNCATE(a,1) FROM t1; +a TRUNCATE(a,1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.9 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.9 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.9 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.9 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.9 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.9 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.9 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.9 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.9 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.9 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.9 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.9 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.9 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.9 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.9 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9 +SELECT a, TRUNCATE(a,2) FROM t1; +a TRUNCATE(a,2) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.99 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.99 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.99 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.99 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.99 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.99 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.99 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.99 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.99 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.99 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.99 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.99 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.99 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.99 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.99 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99 +SELECT a, TRUNCATE(a,3) FROM t1; +a TRUNCATE(a,3) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999 +SELECT a, TRUNCATE(a,4) FROM t1; +a TRUNCATE(a,4) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.9999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.9999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.9999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.9999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.9999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.9999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.9999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.9999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.9999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.9999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.9999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.9999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.9999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.9999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.9999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9999 +SELECT a, TRUNCATE(a,5) FROM t1; +a TRUNCATE(a,5) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.99999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.99999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.99999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.99999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.99999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.99999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.99999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.99999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.99999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.99999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.99999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.99999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.99999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.99999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.99999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99999 +SELECT a, TRUNCATE(a,6) FROM t1; +a TRUNCATE(a,6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, TRUNCATE(a,7) FROM t1; +a TRUNCATE(a,7) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, TRUNCATE(a,-1) FROM t1; +a TRUNCATE(a,-1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +SELECT a, TRUNCATE(a,-6) FROM t1; +a TRUNCATE(a,-6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +SELECT a, ROUND(a) FROM t1; +a ROUND(a) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,0) FROM t1; +a ROUND(a,0) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,1) FROM t1; +a ROUND(a,1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.0 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.0 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.0 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.0 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.0 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.0 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.0 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.0 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,2) FROM t1; +a ROUND(a,2) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.00 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.00 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.00 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.00 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,3) FROM t1; +a ROUND(a,3) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.000 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.000 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.000 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.000 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.000 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.000 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.000 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.000 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,4) FROM t1; +a ROUND(a,4) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.0000 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.0000 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.0000 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.0000 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.0000 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.0000 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.0000 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.0000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0000 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9999 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,5) FROM t1; +a ROUND(a,5) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.00000 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.00000 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.00000 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.00000 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.00000 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.00000 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.00000 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.00000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00000 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99999 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,6) FROM t1; +a ROUND(a,6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, ROUND(a,7) FROM t1; +a ROUND(a,7) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, ROUND(a,-1) FROM t1; +a ROUND(a,-1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,-6) FROM t1; +a ROUND(a,-6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_datetime_hires.test b/mysql-test/main/type_datetime_hires.test index d220a4601eb..ed9a85bcda3 100644 --- a/mysql-test/main/type_datetime_hires.test +++ b/mysql-test/main/type_datetime_hires.test @@ -79,3 +79,73 @@ INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00'); SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1; DROP TABLE t1; SET @@time_zone=DEFAULT; + + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +--echo # + +CREATE TABLE t1 (a1 DATETIME(6), a2 DATETIME(6) NOT NULL); +CREATE TABLE t2 AS SELECT + ROUND(a1) AS r1, + ROUND(a2) AS r2, + TRUNCATE(a1,0) AS t1, + TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES +('0000-00-00 00:00:00.999999'), +('0000-00-00 23:59:59.999999'), +('0000-00-01 00:00:00.999999'), +('0000-00-01 23:59:59.999999'), +('0000-00-31 23:59:59.999999'), +('0000-01-01 00:00:00.999999'), +('0000-01-01 23:59:59.999999'), +('0000-01-31 23:59:59.999999'), +('0000-02-28 23:59:59.999999'), +('0000-12-31 23:59:59.999999'), +('0001-01-01 00:00:00.999999'), +('0001-02-28 23:59:59.999999'), +('0001-12-31 23:59:59.999999'), +('0004-02-28 23:59:59.999999'), +('0004-02-29 23:59:59.999999'), +('2000-02-29 23:59:59.999999'), +('2000-12-31 23:59:59.999999'), +('9999-12-31 23:59:59.999999'); +SELECT a, TRUNCATE(a,0) FROM t1; +SELECT a, TRUNCATE(a,1) FROM t1; +SELECT a, TRUNCATE(a,2) FROM t1; +SELECT a, TRUNCATE(a,3) FROM t1; +SELECT a, TRUNCATE(a,4) FROM t1; +SELECT a, TRUNCATE(a,5) FROM t1; +SELECT a, TRUNCATE(a,6) FROM t1; +SELECT a, TRUNCATE(a,7) FROM t1; +SELECT a, TRUNCATE(a,-1) FROM t1; +SELECT a, TRUNCATE(a,-6) FROM t1; + +SELECT a, ROUND(a) FROM t1; +SELECT a, ROUND(a,0) FROM t1; +SELECT a, ROUND(a,1) FROM t1; +SELECT a, ROUND(a,2) FROM t1; +SELECT a, ROUND(a,3) FROM t1; +SELECT a, ROUND(a,4) FROM t1; +SELECT a, ROUND(a,5) FROM t1; +SELECT a, ROUND(a,6) FROM t1; +SELECT a, ROUND(a,7) FROM t1; +SELECT a, ROUND(a,-1) FROM t1; +SELECT a, ROUND(a,-6) FROM t1; + +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result index 8edaa25194d..3b1321d9a60 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -1,4 +1,28 @@ # +# Start of 5.5 tests +# +# +# MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT @a := 1 FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +@a := 1 +1 +SELECT COALESCE(1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +COALESCE(1) +1 +SELECT COALESCE(@a:=1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +COALESCE(@a:=1) +1 +SELECT COALESCE(@a) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +COALESCE(@a) +1 +DROP TABLE t1; +# +# End of 5.5 tests +# +# # Start of 10.1 tests # # diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test index 8d96ed70b0b..77c532ff4ac 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -1,4 +1,25 @@ --echo # +--echo # Start of 5.5 tests +--echo # + +--echo # +--echo # MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT @a := 1 FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +SELECT COALESCE(1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +SELECT COALESCE(@a:=1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +SELECT COALESCE(@a) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); +DROP TABLE t1; + +--echo # +--echo # End of 5.5 tests +--echo # + + +--echo # --echo # Start of 10.1 tests --echo # diff --git a/mysql-test/main/type_time_hires.result b/mysql-test/main/type_time_hires.result index ca19e9ed5bf..6122afcfc47 100644 --- a/mysql-test/main/type_time_hires.result +++ b/mysql-test/main/type_time_hires.result @@ -25,8 +25,8 @@ a 03:04:05.789 15:47:11.123 838:59:59.999 -select truncate(a, 6) from t1; -truncate(a, 6) +select cast(a AS double(30,6)) from t1; +cast(a AS double(30,6)) 2003.123000 10203.456000 30405.789062 @@ -359,3 +359,314 @@ drop table t1; select cast(1e-6 as time(6)); cast(1e-6 as time(6)) 00:00:00.000001 +# +# Start of 10.4 tests +# +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +CREATE TABLE t1 (a1 TIME(6), a2 TIME(6) NOT NULL); +CREATE TABLE t2 AS SELECT +ROUND(a1) AS r1, +ROUND(a2) AS r2, +TRUNCATE(a1,0) AS t1, +TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `r1` time DEFAULT NULL, + `r2` time NOT NULL, + `t1` time DEFAULT NULL, + `t2` time NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES +('-838:59:59.999999'), +('-837:59:59.999999'), +('-23:59:59.999999'), +('-00:59:59.999999'), +('-00:00:59.999999'), +('00:00:00.999999'), +('00:00:59.999999'), +('00:59:59.999999'), +('23:59:59.999999'), +('837:59:59.999999'), +('838:59:59.999999'); +SELECT a, TRUNCATE(a,0) FROM t1; +a TRUNCATE(a,0) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -837:59:59 +-23:59:59.999999 -23:59:59 +-00:59:59.999999 -00:59:59 +-00:00:59.999999 -00:00:59 +00:00:00.999999 00:00:00 +00:00:59.999999 00:00:59 +00:59:59.999999 00:59:59 +23:59:59.999999 23:59:59 +837:59:59.999999 837:59:59 +838:59:59.999999 838:59:59 +SELECT a, TRUNCATE(a,1) FROM t1; +a TRUNCATE(a,1) +-838:59:59.999999 -838:59:59.9 +-837:59:59.999999 -837:59:59.9 +-23:59:59.999999 -23:59:59.9 +-00:59:59.999999 -00:59:59.9 +-00:00:59.999999 -00:00:59.9 +00:00:00.999999 00:00:00.9 +00:00:59.999999 00:00:59.9 +00:59:59.999999 00:59:59.9 +23:59:59.999999 23:59:59.9 +837:59:59.999999 837:59:59.9 +838:59:59.999999 838:59:59.9 +SELECT a, TRUNCATE(a,2) FROM t1; +a TRUNCATE(a,2) +-838:59:59.999999 -838:59:59.99 +-837:59:59.999999 -837:59:59.99 +-23:59:59.999999 -23:59:59.99 +-00:59:59.999999 -00:59:59.99 +-00:00:59.999999 -00:00:59.99 +00:00:00.999999 00:00:00.99 +00:00:59.999999 00:00:59.99 +00:59:59.999999 00:59:59.99 +23:59:59.999999 23:59:59.99 +837:59:59.999999 837:59:59.99 +838:59:59.999999 838:59:59.99 +SELECT a, TRUNCATE(a,3) FROM t1; +a TRUNCATE(a,3) +-838:59:59.999999 -838:59:59.999 +-837:59:59.999999 -837:59:59.999 +-23:59:59.999999 -23:59:59.999 +-00:59:59.999999 -00:59:59.999 +-00:00:59.999999 -00:00:59.999 +00:00:00.999999 00:00:00.999 +00:00:59.999999 00:00:59.999 +00:59:59.999999 00:59:59.999 +23:59:59.999999 23:59:59.999 +837:59:59.999999 837:59:59.999 +838:59:59.999999 838:59:59.999 +SELECT a, TRUNCATE(a,4) FROM t1; +a TRUNCATE(a,4) +-838:59:59.999999 -838:59:59.9999 +-837:59:59.999999 -837:59:59.9999 +-23:59:59.999999 -23:59:59.9999 +-00:59:59.999999 -00:59:59.9999 +-00:00:59.999999 -00:00:59.9999 +00:00:00.999999 00:00:00.9999 +00:00:59.999999 00:00:59.9999 +00:59:59.999999 00:59:59.9999 +23:59:59.999999 23:59:59.9999 +837:59:59.999999 837:59:59.9999 +838:59:59.999999 838:59:59.9999 +SELECT a, TRUNCATE(a,5) FROM t1; +a TRUNCATE(a,5) +-838:59:59.999999 -838:59:59.99999 +-837:59:59.999999 -837:59:59.99999 +-23:59:59.999999 -23:59:59.99999 +-00:59:59.999999 -00:59:59.99999 +-00:00:59.999999 -00:00:59.99999 +00:00:00.999999 00:00:00.99999 +00:00:59.999999 00:00:59.99999 +00:59:59.999999 00:59:59.99999 +23:59:59.999999 23:59:59.99999 +837:59:59.999999 837:59:59.99999 +838:59:59.999999 838:59:59.99999 +SELECT a, TRUNCATE(a,6) FROM t1; +a TRUNCATE(a,6) +-838:59:59.999999 -838:59:59.999999 +-837:59:59.999999 -837:59:59.999999 +-23:59:59.999999 -23:59:59.999999 +-00:59:59.999999 -00:59:59.999999 +-00:00:59.999999 -00:00:59.999999 +00:00:00.999999 00:00:00.999999 +00:00:59.999999 00:00:59.999999 +00:59:59.999999 00:59:59.999999 +23:59:59.999999 23:59:59.999999 +837:59:59.999999 837:59:59.999999 +838:59:59.999999 838:59:59.999999 +SELECT a, TRUNCATE(a,7) FROM t1; +a TRUNCATE(a,7) +-838:59:59.999999 -838:59:59.999999 +-837:59:59.999999 -837:59:59.999999 +-23:59:59.999999 -23:59:59.999999 +-00:59:59.999999 -00:59:59.999999 +-00:00:59.999999 -00:00:59.999999 +00:00:00.999999 00:00:00.999999 +00:00:59.999999 00:00:59.999999 +00:59:59.999999 00:59:59.999999 +23:59:59.999999 23:59:59.999999 +837:59:59.999999 837:59:59.999999 +838:59:59.999999 838:59:59.999999 +SELECT a, TRUNCATE(a,-1) FROM t1; +a TRUNCATE(a,-1) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -837:59:59 +-23:59:59.999999 -23:59:59 +-00:59:59.999999 -00:59:59 +-00:00:59.999999 -00:00:59 +00:00:00.999999 00:00:00 +00:00:59.999999 00:00:59 +00:59:59.999999 00:59:59 +23:59:59.999999 23:59:59 +837:59:59.999999 837:59:59 +838:59:59.999999 838:59:59 +SELECT a, TRUNCATE(a,-6) FROM t1; +a TRUNCATE(a,-6) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -837:59:59 +-23:59:59.999999 -23:59:59 +-00:59:59.999999 -00:59:59 +-00:00:59.999999 -00:00:59 +00:00:00.999999 00:00:00 +00:00:59.999999 00:00:59 +00:59:59.999999 00:59:59 +23:59:59.999999 23:59:59 +837:59:59.999999 837:59:59 +838:59:59.999999 838:59:59 +SELECT a, ROUND(a) FROM t1; +a ROUND(a) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -838:00:00 +-23:59:59.999999 -24:00:00 +-00:59:59.999999 -01:00:00 +-00:00:59.999999 -00:01:00 +00:00:00.999999 00:00:01 +00:00:59.999999 00:01:00 +00:59:59.999999 01:00:00 +23:59:59.999999 24:00:00 +837:59:59.999999 838:00:00 +838:59:59.999999 838:59:59 +SELECT a, ROUND(a,0) FROM t1; +a ROUND(a,0) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -838:00:00 +-23:59:59.999999 -24:00:00 +-00:59:59.999999 -01:00:00 +-00:00:59.999999 -00:01:00 +00:00:00.999999 00:00:01 +00:00:59.999999 00:01:00 +00:59:59.999999 01:00:00 +23:59:59.999999 24:00:00 +837:59:59.999999 838:00:00 +838:59:59.999999 838:59:59 +SELECT a, ROUND(a,1) FROM t1; +a ROUND(a,1) +-838:59:59.999999 -838:59:59.9 +-837:59:59.999999 -838:00:00.0 +-23:59:59.999999 -24:00:00.0 +-00:59:59.999999 -01:00:00.0 +-00:00:59.999999 -00:01:00.0 +00:00:00.999999 00:00:01.0 +00:00:59.999999 00:01:00.0 +00:59:59.999999 01:00:00.0 +23:59:59.999999 24:00:00.0 +837:59:59.999999 838:00:00.0 +838:59:59.999999 838:59:59.9 +SELECT a, ROUND(a,2) FROM t1; +a ROUND(a,2) +-838:59:59.999999 -838:59:59.99 +-837:59:59.999999 -838:00:00.00 +-23:59:59.999999 -24:00:00.00 +-00:59:59.999999 -01:00:00.00 +-00:00:59.999999 -00:01:00.00 +00:00:00.999999 00:00:01.00 +00:00:59.999999 00:01:00.00 +00:59:59.999999 01:00:00.00 +23:59:59.999999 24:00:00.00 +837:59:59.999999 838:00:00.00 +838:59:59.999999 838:59:59.99 +SELECT a, ROUND(a,3) FROM t1; +a ROUND(a,3) +-838:59:59.999999 -838:59:59.999 +-837:59:59.999999 -838:00:00.000 +-23:59:59.999999 -24:00:00.000 +-00:59:59.999999 -01:00:00.000 +-00:00:59.999999 -00:01:00.000 +00:00:00.999999 00:00:01.000 +00:00:59.999999 00:01:00.000 +00:59:59.999999 01:00:00.000 +23:59:59.999999 24:00:00.000 +837:59:59.999999 838:00:00.000 +838:59:59.999999 838:59:59.999 +SELECT a, ROUND(a,4) FROM t1; +a ROUND(a,4) +-838:59:59.999999 -838:59:59.9999 +-837:59:59.999999 -838:00:00.0000 +-23:59:59.999999 -24:00:00.0000 +-00:59:59.999999 -01:00:00.0000 +-00:00:59.999999 -00:01:00.0000 +00:00:00.999999 00:00:01.0000 +00:00:59.999999 00:01:00.0000 +00:59:59.999999 01:00:00.0000 +23:59:59.999999 24:00:00.0000 +837:59:59.999999 838:00:00.0000 +838:59:59.999999 838:59:59.9999 +SELECT a, ROUND(a,5) FROM t1; +a ROUND(a,5) +-838:59:59.999999 -838:59:59.99999 +-837:59:59.999999 -838:00:00.00000 +-23:59:59.999999 -24:00:00.00000 +-00:59:59.999999 -01:00:00.00000 +-00:00:59.999999 -00:01:00.00000 +00:00:00.999999 00:00:01.00000 +00:00:59.999999 00:01:00.00000 +00:59:59.999999 01:00:00.00000 +23:59:59.999999 24:00:00.00000 +837:59:59.999999 838:00:00.00000 +838:59:59.999999 838:59:59.99999 +SELECT a, ROUND(a,6) FROM t1; +a ROUND(a,6) +-838:59:59.999999 -838:59:59.999999 +-837:59:59.999999 -837:59:59.999999 +-23:59:59.999999 -23:59:59.999999 +-00:59:59.999999 -00:59:59.999999 +-00:00:59.999999 -00:00:59.999999 +00:00:00.999999 00:00:00.999999 +00:00:59.999999 00:00:59.999999 +00:59:59.999999 00:59:59.999999 +23:59:59.999999 23:59:59.999999 +837:59:59.999999 837:59:59.999999 +838:59:59.999999 838:59:59.999999 +SELECT a, ROUND(a,7) FROM t1; +a ROUND(a,7) +-838:59:59.999999 -838:59:59.999999 +-837:59:59.999999 -837:59:59.999999 +-23:59:59.999999 -23:59:59.999999 +-00:59:59.999999 -00:59:59.999999 +-00:00:59.999999 -00:00:59.999999 +00:00:00.999999 00:00:00.999999 +00:00:59.999999 00:00:59.999999 +00:59:59.999999 00:59:59.999999 +23:59:59.999999 23:59:59.999999 +837:59:59.999999 837:59:59.999999 +838:59:59.999999 838:59:59.999999 +SELECT a, ROUND(a,-1) FROM t1; +a ROUND(a,-1) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -838:00:00 +-23:59:59.999999 -24:00:00 +-00:59:59.999999 -01:00:00 +-00:00:59.999999 -00:01:00 +00:00:00.999999 00:00:01 +00:00:59.999999 00:01:00 +00:59:59.999999 01:00:00 +23:59:59.999999 24:00:00 +837:59:59.999999 838:00:00 +838:59:59.999999 838:59:59 +SELECT a, ROUND(a,-6) FROM t1; +a ROUND(a,-6) +-838:59:59.999999 -838:59:59 +-837:59:59.999999 -838:00:00 +-23:59:59.999999 -24:00:00 +-00:59:59.999999 -01:00:00 +-00:00:59.999999 -00:01:00 +00:00:00.999999 00:00:01 +00:00:59.999999 00:01:00 +00:59:59.999999 01:00:00 +23:59:59.999999 24:00:00 +837:59:59.999999 838:00:00 +838:59:59.999999 838:59:59 +DROP TABLE t1; +SET time_zone=DEFAULT; diff --git a/mysql-test/main/type_time_hires.test b/mysql-test/main/type_time_hires.test index 3785a23f1eb..0949738e949 100644 --- a/mysql-test/main/type_time_hires.test +++ b/mysql-test/main/type_time_hires.test @@ -10,3 +10,66 @@ select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0; drop table t1; select cast(1e-6 as time(6)); + + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +--echo # + +CREATE TABLE t1 (a1 TIME(6), a2 TIME(6) NOT NULL); +CREATE TABLE t2 AS SELECT + ROUND(a1) AS r1, + ROUND(a2) AS r2, + TRUNCATE(a1,0) AS t1, + TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES +('-838:59:59.999999'), +('-837:59:59.999999'), +('-23:59:59.999999'), +('-00:59:59.999999'), +('-00:00:59.999999'), +('00:00:00.999999'), +('00:00:59.999999'), +('00:59:59.999999'), +('23:59:59.999999'), +('837:59:59.999999'), +('838:59:59.999999'); + + +SELECT a, TRUNCATE(a,0) FROM t1; +SELECT a, TRUNCATE(a,1) FROM t1; +SELECT a, TRUNCATE(a,2) FROM t1; +SELECT a, TRUNCATE(a,3) FROM t1; +SELECT a, TRUNCATE(a,4) FROM t1; +SELECT a, TRUNCATE(a,5) FROM t1; +SELECT a, TRUNCATE(a,6) FROM t1; +SELECT a, TRUNCATE(a,7) FROM t1; +SELECT a, TRUNCATE(a,-1) FROM t1; +SELECT a, TRUNCATE(a,-6) FROM t1; + +SELECT a, ROUND(a) FROM t1; +SELECT a, ROUND(a,0) FROM t1; +SELECT a, ROUND(a,1) FROM t1; +SELECT a, ROUND(a,2) FROM t1; +SELECT a, ROUND(a,3) FROM t1; +SELECT a, ROUND(a,4) FROM t1; +SELECT a, ROUND(a,5) FROM t1; +SELECT a, ROUND(a,6) FROM t1; +SELECT a, ROUND(a,7) FROM t1; +SELECT a, ROUND(a,-1) FROM t1; +SELECT a, ROUND(a,-6) FROM t1; + +DROP TABLE t1; + +SET time_zone=DEFAULT; diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index 97a70d042eb..d8c74f15c57 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1267,5 +1267,35 @@ f DROP TABLE t1; SET sql_mode=DEFAULT; # +# MDEV-20417 Assertion `(m_ptr == __null) == item->null_value' failed in VDec::VDec(Item*) +# +CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('2001-01-01','2002-01-01'),('2003-01-01','2004-01-01'); +SELECT * FROM t1 WHERE DEFAULT(b) - a; +a b +Warnings: +Warning 1916 Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated +Warning 1916 Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated +SELECT LEFT('', DEFAULT(b)-a) FROM t1; +LEFT('', DEFAULT(b)-a) + + +Warnings: +Warning 1916 Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated +Warning 1916 Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM; +INSERT IGNORE INTO t1 (a) VALUES ('2001-01-01'),('2003-01-01'); +SELECT * FROM t1 WHERE (SELECT MIN(b) FROM t1) - a; +a b +Warnings: +Warning 1916 Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated +Warning 1916 Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated +SELECT (SELECT MIN(b) FROM t1) - a FROM t1; +(SELECT MIN(b) FROM t1) - a +-20010101000000.0000 +-20030101000000.0000 +DROP TABLE t1; +# # End of 10.4 tests # diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index ce932b7720c..7b2311eaac3 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -837,6 +837,24 @@ SELECT GREATEST(a,b) AS f FROM t1 ORDER BY 1 DESC; DROP TABLE t1; SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-20417 Assertion `(m_ptr == __null) == item->null_value' failed in VDec::VDec(Item*) +--echo # + +CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM; +INSERT IGNORE INTO t1 VALUES ('2001-01-01','2002-01-01'),('2003-01-01','2004-01-01'); +SELECT * FROM t1 WHERE DEFAULT(b) - a; +SELECT LEFT('', DEFAULT(b)-a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM; +INSERT IGNORE INTO t1 (a) VALUES ('2001-01-01'),('2003-01-01'); +SELECT * FROM t1 WHERE (SELECT MIN(b) FROM t1) - a; +SELECT (SELECT MIN(b) FROM t1) - a FROM t1; +DROP TABLE t1; + + --echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/type_timestamp_hires.result b/mysql-test/main/type_timestamp_hires.result index fa6adc075ed..dc69aa36b0f 100644 --- a/mysql-test/main/type_timestamp_hires.result +++ b/mysql-test/main/type_timestamp_hires.result @@ -17,8 +17,8 @@ a 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 -select truncate(a, 6) from t1; -truncate(a, 6) +select cast(a AS double(30,6)) from t1; +cast(a AS double(30,6)) 0.000000 20101211002003.120000 20101211010203.457031 @@ -333,3 +333,298 @@ t1 CREATE TABLE `t1` ( `a` timestamp(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000' ON UPDATE current_timestamp(5) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# Start of 10.4 tests +# +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +# ROUND(timestamp) and TRUNCATE(timestamp) currently return DATETIME. +# This may change in the future to return TIMESTAMP. +CREATE TABLE t1 (a1 TIMESTAMP(6) NULL DEFAULT '2001-01-01 00:00:00', a2 TIMESTAMP(6) NOT NULL); +CREATE TABLE t2 AS SELECT +ROUND(a1) AS r1, +ROUND(a2) AS r2, +TRUNCATE(a1,0) AS t1, +TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `r1` datetime DEFAULT NULL, + `r2` datetime DEFAULT NULL, + `t1` datetime DEFAULT NULL, + `t2` datetime NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP(6)); +INSERT INTO t1 VALUES +('1970-01-01 00:00:01.999999'), +('2000-01-01 00:00:00.999999'), +('2000-01-01 23:59:59.999999'), +('2000-02-29 23:59:59.999999'), +('2000-12-31 23:59:59.999999'), +('2001-01-01 00:00:00.999999'), +('2001-01-01 23:59:59.999999'), +('2001-02-28 23:59:59.999999'), +('2001-12-31 23:59:59.999999'), +('2038-01-19 03:14:07.999999'); +SELECT a, TRUNCATE(a,0) FROM t1; +a TRUNCATE(a,0) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07 +SELECT a, TRUNCATE(a,1) FROM t1; +a TRUNCATE(a,1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.9 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.9 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.9 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.9 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.9 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.9 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.9 +SELECT a, TRUNCATE(a,2) FROM t1; +a TRUNCATE(a,2) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.99 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.99 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.99 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.99 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.99 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.99 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.99 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.99 +SELECT a, TRUNCATE(a,3) FROM t1; +a TRUNCATE(a,3) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999 +SELECT a, TRUNCATE(a,4) FROM t1; +a TRUNCATE(a,4) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.9999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.9999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.9999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.9999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.9999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.9999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.9999 +SELECT a, TRUNCATE(a,5) FROM t1; +a TRUNCATE(a,5) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.99999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.99999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.99999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.99999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.99999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.99999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.99999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.99999 +SELECT a, TRUNCATE(a,6) FROM t1; +a TRUNCATE(a,6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, TRUNCATE(a,7) FROM t1; +a TRUNCATE(a,7) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, TRUNCATE(a,-1) FROM t1; +a TRUNCATE(a,-1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07 +SELECT a, TRUNCATE(a,-6) FROM t1; +a TRUNCATE(a,-6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07 +SELECT a, ROUND(a) FROM t1; +a ROUND(a) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +SELECT a, ROUND(a,0) FROM t1; +a ROUND(a,0) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +SELECT a, ROUND(a,1) FROM t1; +a ROUND(a,1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.0 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.0 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.0 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.0 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.0 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.0 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.0 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.0 +SELECT a, ROUND(a,2) FROM t1; +a ROUND(a,2) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.00 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.00 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.00 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.00 +SELECT a, ROUND(a,3) FROM t1; +a ROUND(a,3) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.000 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.000 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.000 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.000 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.000 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.000 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.000 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.000 +SELECT a, ROUND(a,4) FROM t1; +a ROUND(a,4) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.0000 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.0000 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.0000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0000 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.0000 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.0000 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.0000 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.0000 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.0000 +SELECT a, ROUND(a,5) FROM t1; +a ROUND(a,5) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.00000 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.00000 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.00000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00000 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.00000 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.00000 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.00000 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.00000 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.00000 +SELECT a, ROUND(a,6) FROM t1; +a ROUND(a,6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, ROUND(a,7) FROM t1; +a ROUND(a,7) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, ROUND(a,-1) FROM t1; +a ROUND(a,-1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +SELECT a, ROUND(a,-6) FROM t1; +a ROUND(a,-6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +DROP TABLE t1; +SET time_zone=DEFAULT; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_timestamp_hires.test b/mysql-test/main/type_timestamp_hires.test index 0b05f81ef42..f2e765d09fe 100644 --- a/mysql-test/main/type_timestamp_hires.test +++ b/mysql-test/main/type_timestamp_hires.test @@ -40,3 +40,73 @@ show create table t1; create or replace table t1 (a timestamp(5) on update current_timestamp(6)); show create table t1; drop table t1; + + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +--echo # + +--echo # ROUND(timestamp) and TRUNCATE(timestamp) currently return DATETIME. +--echo # This may change in the future to return TIMESTAMP. + +CREATE TABLE t1 (a1 TIMESTAMP(6) NULL DEFAULT '2001-01-01 00:00:00', a2 TIMESTAMP(6) NOT NULL); +CREATE TABLE t2 AS SELECT + ROUND(a1) AS r1, + ROUND(a2) AS r2, + TRUNCATE(a1,0) AS t1, + TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +SET time_zone='+00:00'; + +CREATE TABLE t1 (a TIMESTAMP(6)); +INSERT INTO t1 VALUES +('1970-01-01 00:00:01.999999'), +('2000-01-01 00:00:00.999999'), +('2000-01-01 23:59:59.999999'), +('2000-02-29 23:59:59.999999'), +('2000-12-31 23:59:59.999999'), +('2001-01-01 00:00:00.999999'), +('2001-01-01 23:59:59.999999'), +('2001-02-28 23:59:59.999999'), +('2001-12-31 23:59:59.999999'), +('2038-01-19 03:14:07.999999'); + +SELECT a, TRUNCATE(a,0) FROM t1; +SELECT a, TRUNCATE(a,1) FROM t1; +SELECT a, TRUNCATE(a,2) FROM t1; +SELECT a, TRUNCATE(a,3) FROM t1; +SELECT a, TRUNCATE(a,4) FROM t1; +SELECT a, TRUNCATE(a,5) FROM t1; +SELECT a, TRUNCATE(a,6) FROM t1; +SELECT a, TRUNCATE(a,7) FROM t1; +SELECT a, TRUNCATE(a,-1) FROM t1; +SELECT a, TRUNCATE(a,-6) FROM t1; + +SELECT a, ROUND(a) FROM t1; +SELECT a, ROUND(a,0) FROM t1; +SELECT a, ROUND(a,1) FROM t1; +SELECT a, ROUND(a,2) FROM t1; +SELECT a, ROUND(a,3) FROM t1; +SELECT a, ROUND(a,4) FROM t1; +SELECT a, ROUND(a,5) FROM t1; +SELECT a, ROUND(a,6) FROM t1; +SELECT a, ROUND(a,7) FROM t1; +SELECT a, ROUND(a,-1) FROM t1; +SELECT a, ROUND(a,-6) FROM t1; + +DROP TABLE t1; + +SET time_zone=DEFAULT; + +--echo # +--echo # End of 10.4 tests +--echo # |