maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #07849
PLEASE REVIEWA: pre-requisite patch for the data type plugins, also fixing a number of optimizer bugs
Hello,
While working on pluggable data types, I noticed a few problems
related to optimizer:
MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns
MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
MDEV-6978 Bad results with joins comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
MDEV-6982 LEFT JOIN table elimination is not always used when it could
MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases
MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could
MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
In some cases wrong result sets are returned.
So it would be nice to have this fixed in 10.0.
I made these changes as a standalone patch.
Please review.
I also noticed some other optimizer bugs but not sure how to fix them
properly:
MDEV-6986 Bad results with join comparing INT and VARCHAR columns
MDEV-6969 Bad results with joins comparing DOUBLE to BIGINT/DECIMAL columns
MDEV-6993 Bad results with join comparing DECIMAL and ENUM/SET columns
Igor or SergeyP can possibly fix them on top of my changes.
Also, I found some other problems (not related to optimizer):
MDEV-6973 XOR aggregates argument collations
MDEV-7005 NULLIF does not work as documented
Not sure which version to fix them in.
Please suggest.
Thanks.
=== modified file 'mysql-test/r/group_min_max.result'
--- mysql-test/r/group_min_max.result 2014-08-07 16:06:56 +0000
+++ mysql-test/r/group_min_max.result 2014-11-01 21:20:24 +0000
@@ -3686,3 +3686,43 @@ a b
3 2
3 3
drop table t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+#
+CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'2001-01-01');
+INSERT INTO t1 VALUES (1,'2001-01-02');
+INSERT INTO t1 VALUES (1,'2001-01-03');
+INSERT INTO t1 VALUES (1,' 2001-01-04');
+INSERT INTO t1 VALUES (2,'2001-01-01');
+INSERT INTO t1 VALUES (2,'2001-01-02');
+INSERT INTO t1 VALUES (2,'2001-01-03');
+INSERT INTO t1 VALUES (2,' 2001-01-04');
+INSERT INTO t1 VALUES (3,'2001-01-01');
+INSERT INTO t1 VALUES (3,'2001-01-02');
+INSERT INTO t1 VALUES (3,'2001-01-03');
+INSERT INTO t1 VALUES (3,' 2001-01-04');
+INSERT INTO t1 VALUES (4,'2001-01-01');
+INSERT INTO t1 VALUES (4,'2001-01-02');
+INSERT INTO t1 VALUES (4,'2001-01-03');
+INSERT INTO t1 VALUES (4,' 2001-01-04');
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
+id MIN(a) MAX(a)
+1 2001-01-04 2001-01-04
+2 2001-01-04 2001-01-04
+3 2001-01-04 2001-01-04
+4 2001-01-04 2001-01-04
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
+id MIN(a) MAX(a)
+1 2001-01-04 2001-01-04
+2 2001-01-04 2001-01-04
+3 2001-01-04 2001-01-04
+4 2001-01-04 2001-01-04
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/range.result'
--- mysql-test/r/range.result 2014-06-05 22:07:27 +0000
+++ mysql-test/r/range.result 2014-11-01 21:20:19 +0000
@@ -618,10 +618,10 @@ id select_type table type possible_keys
1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a=binary 'aaa';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 11 NULL 2 Using index condition
+1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a='aaa' collate latin1_bin;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 11 NULL 2 Using index condition
+1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a='aaa' collate latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a NULL NULL NULL 9 Using where
=== modified file 'mysql-test/r/range_mrr_icp.result'
--- mysql-test/r/range_mrr_icp.result 2014-06-05 22:07:27 +0000
+++ mysql-test/r/range_mrr_icp.result 2014-11-01 21:20:19 +0000
@@ -620,10 +620,10 @@ id select_type table type possible_keys
1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a=binary 'aaa';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Rowid-ordered scan
+1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a='aaa' collate latin1_bin;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Rowid-ordered scan
+1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a='aaa' collate latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a NULL NULL NULL 9 Using where
=== modified file 'mysql-test/r/type_date.result'
--- mysql-test/r/type_date.result 2014-05-09 10:35:11 +0000
+++ mysql-test/r/type_date.result 2014-11-01 21:20:23 +0000
@@ -438,3 +438,281 @@ id select_type table type possible_keys
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
1
drop table t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6982 LEFT JOIN table elimination is not always used when it could
+#
+CREATE TABLE t1 (c1 INT PRIMARY KEY);
+INSERT INTO t1 VALUES (20010101),(20010102);
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+20010101
+20010102
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+20010101
+20010102
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+20010101
+20010102
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+20010101
+20010102
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
+INSERT INTO t1 VALUES (20010101),(20010102);
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+20010101
+20010102
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+20010101
+20010102
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+20010101
+20010102
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+20010101
+20010102
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 8 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DECIMAL(20,0) PRIMARY KEY);
+INSERT INTO t1 VALUES (20010101),(20010102);
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+20010101
+20010102
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+20010101
+20010102
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+20010101
+20010102
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+20010101
+20010102
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 9 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 CHAR(20) PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001/01/02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 20 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 ENUM('2001-01-01','2001/01/02') PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001/01/02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 1 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 SET('2001-01-01','2001/01/02') PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001/01/02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001/01/02
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 1 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001-01-02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001-01-02
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01
+2001-01-02
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01
+2001-01-02
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 3 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DATETIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01 00:00:00
+2001-01-02 00:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01 00:00:00
+2001-01-02 00:00:00
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+2001-01-01 00:00:00
+2001-01-02 00:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+2001-01-01 00:00:00
+2001-01-02 00:00:00
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 8 NULL 2 Using index
+DROP TABLE t1,t2;
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
+CREATE TABLE t1 (c1 TIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('00:00:00'),('24:00:00');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+00:00:00
+24:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+00:00:00
+24:00:00
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+c1
+00:00:00
+24:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+c1
+00:00:00
+24:00:00
+# Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 3 NULL 2 Using index
+DROP TABLE t1,t2;
+SET timestamp=DEFAULT;
+#
+# MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could
+#
+CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'2001-01-01');
+INSERT INTO t1 VALUES (1,'2001-01-02');
+INSERT INTO t1 VALUES (1,'2001-01-03');
+INSERT INTO t1 VALUES (1,'2001-01-04');
+INSERT INTO t1 VALUES (2,'2001-01-01');
+INSERT INTO t1 VALUES (2,'2001-01-02');
+INSERT INTO t1 VALUES (2,'2001-01-03');
+INSERT INTO t1 VALUES (2,'2001-01-04');
+INSERT INTO t1 VALUES (3,'2001-01-01');
+INSERT INTO t1 VALUES (3,'2001-01-02');
+INSERT INTO t1 VALUES (3,'2001-01-03');
+INSERT INTO t1 VALUES (3,'2001-01-04');
+INSERT INTO t1 VALUES (4,'2001-01-01');
+INSERT INTO t1 VALUES (4,'2001-01-02');
+INSERT INTO t1 VALUES (4,'2001-01-03');
+INSERT INTO t1 VALUES (4,'2001-01-04');
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104 GROUP BY id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_enum.result'
--- mysql-test/r/type_enum.result 2013-09-14 01:09:36 +0000
+++ mysql-test/r/type_enum.result 2014-11-03 09:00:12 +0000
@@ -1869,3 +1869,259 @@ AVG(f1)
1.5000
drop table t1;
End of 5.3 tests
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/ENUM/VARCHAR columns
+#
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01');
+CREATE TABLE t2 (c1 ENUM('2001-01-01','2001/01/01'));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001/01/01');
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+2001-01-01
+2001-01-01
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE t1, t2;
+#
+# MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+#
+CREATE TABLE t1 (c1 ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+#
+# MDEV-6982 LEFT JOIN table elimination is not always used when it could
+#
+CREATE TABLE t1 (c1 INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 ENUM('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+# Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 ENUM('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+# Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 8 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 ENUM('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1.0
+2.0
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1.0
+2.0
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1.0
+2.0
+# Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 5 NULL 2 Using index
+DROP TABLE t1,t2;
+#
+# MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+#
+CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'01');
+INSERT INTO t1 VALUES (1,'02');
+INSERT INTO t1 VALUES (1,'03');
+INSERT INTO t1 VALUES (1,'04');
+INSERT INTO t1 VALUES (2,'01');
+INSERT INTO t1 VALUES (2,'02');
+INSERT INTO t1 VALUES (2,'03');
+INSERT INTO t1 VALUES (2,'04');
+INSERT INTO t1 VALUES (3,'01');
+INSERT INTO t1 VALUES (3,'02');
+INSERT INTO t1 VALUES (3,'03');
+INSERT INTO t1 VALUES (3,'04');
+INSERT INTO t1 VALUES (4,'01');
+INSERT INTO t1 VALUES (4,'02');
+INSERT INTO t1 VALUES (4,'03');
+INSERT INTO t1 VALUES (4,'04');
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+id MIN(a) MAX(a)
+1 02 04
+2 02 04
+3 02 04
+4 02 04
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+id MIN(a) MAX(a)
+1 02 04
+2 02 04
+3 02 04
+4 02 04
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+id MIN(a) MAX(a)
+1 02 04
+2 02 04
+3 02 04
+4 02 04
+# Should NOT use group_min_max optimization
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL id 6 NULL 16 Using where; Using index
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+id MIN(a) MAX(a)
+1 02 04
+2 02 04
+3 02 04
+4 02 04
+# Should NOT use group_min_max optimization
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL id 6 NULL 16 Using where; Using index
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_float.result'
--- mysql-test/r/type_float.result 2014-03-14 07:31:16 +0000
+++ mysql-test/r/type_float.result 2014-11-03 09:10:53 +0000
@@ -465,3 +465,70 @@ SELECT * FROM t1 ORDER BY f;
1
2
DROP TABLE t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns
+#
+CREATE TABLE t1 (a DATETIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
+CREATE TABLE t2 (a DOUBLE);
+INSERT INTO t2 VALUES (19990101000000);
+INSERT INTO t2 VALUES (990101000000);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
+#
+# MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
+#
+CREATE TABLE t1 (a TIME(6) PRIMARY KEY);
+INSERT INTO t1 VALUES ('10:20:30');
+CREATE TABLE t2 (a DOUBLE);
+INSERT INTO t2 VALUES (102030),(102030.000000001);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+# t2 should NOT be elimitated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_newdecimal.result'
--- mysql-test/r/type_newdecimal.result 2014-10-09 08:30:11 +0000
+++ mysql-test/r/type_newdecimal.result 2014-11-03 09:11:43 +0000
@@ -1997,3 +1997,84 @@ select 0.0000000001 mod 1;
select 0.01 mod 1;
0.01 mod 1
0.01
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
+#
+CREATE TABLE t1 (a DATETIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
+CREATE TABLE t2 (a DECIMAL(30,1));
+INSERT INTO t2 VALUES (19990101000000);
+INSERT INTO t2 VALUES (990101000000);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a
+1999-01-01 00:00:00
+1999-01-01 00:00:00
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 14 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
+#
+# MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
+#
+CREATE TABLE t1 (a TIME(6) PRIMARY KEY);
+INSERT INTO t1 VALUES ('10:20:30');
+CREATE TABLE t2 (a DECIMAL(30,10));
+INSERT INTO t2 VALUES (102030),(102030.000000001);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+Warnings:
+Note 1292 Truncated incorrect time value: '102030.0000000000'
+Note 1292 Truncated incorrect time value: '102030.0000000010'
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+Warnings:
+Note 1292 Truncated incorrect time value: '102030.0000000000'
+Note 1292 Truncated incorrect time value: '102030.0000000000'
+Note 1292 Truncated incorrect time value: '102030.0000000010'
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+Warnings:
+Note 1292 Truncated incorrect time value: '102030.0000000000'
+Note 1292 Truncated incorrect time value: '102030.0000000010'
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+a
+10:20:30.000000
+10:20:30.000000
+Warnings:
+Note 1292 Truncated incorrect time value: '102030.0000000000'
+Note 1292 Truncated incorrect time value: '102030.0000000000'
+Note 1292 Truncated incorrect time value: '102030.0000000010'
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 14 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_set.result'
--- mysql-test/r/type_set.result 2008-09-05 15:21:59 +0000
+++ mysql-test/r/type_set.result 2014-11-03 09:03:20 +0000
@@ -104,3 +104,205 @@ INSERT INTO t1 ( set_unique_utf8 ) VALUE
ERROR 23000: Duplicate entry '' for key 'set_unique_utf8'
DROP TABLE t1;
End of 5.0 tests
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6950 Bad results with joins compating DATE and INT/ENUM/VARCHAR columns
+#
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01');
+CREATE TABLE t2 (c1 SET('2001-01-01','2001/01/01'));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001/01/01');
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE t1, t2;
+#
+# MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+#
+CREATE TABLE t1 (c1 ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+c1
+a
+a
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+#
+# MDEV-6982 LEFT JOIN table elimination is not always used when it could
+#
+CREATE TABLE t1 (c1 INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 SET('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+# Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 SET('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1
+2
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1
+2
+# Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 8 NULL 2 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 SET('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+c1
+1.0
+2.0
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1.0
+2.0
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+1.0
+2.0
+# Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 5 NULL 2 Using index
+DROP TABLE t1,t2;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_uint.result'
--- mysql-test/r/type_uint.result 2005-12-02 11:01:44 +0000
+++ mysql-test/r/type_uint.result 2014-11-03 09:05:25 +0000
@@ -14,3 +14,40 @@ this
0
4294967295
drop table t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
+#
+CREATE TABLE t1 (a DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('1999-01-01');
+CREATE TABLE t2 (a INT UNSIGNED);
+INSERT INTO t2 VALUES (19990101);
+INSERT INTO t2 VALUES (990101);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+a
+1999-01-01
+1999-01-01
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a
+1999-01-01
+1999-01-01
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+a
+1999-01-01
+1999-01-01
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a
+1999-01-01
+1999-01-01
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_varchar.result'
--- mysql-test/r/type_varchar.result 2011-10-19 19:45:18 +0000
+++ mysql-test/r/type_varchar.result 2014-11-03 09:06:59 +0000
@@ -510,3 +510,104 @@ SELECT 5 = a FROM t1;
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 's '
DROP TABLE t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
+#
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01');
+CREATE TABLE t2 (c1 VARCHAR(20));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001/01/01');
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+c1
+2001-01-01
+2001-01-01
+# t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index
+DROP TABLE IF EXISTS t1,t2;
+#
+# MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases
+#
+CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');
+SELECT * FROM t1 WHERE c1=BINARY 'a';
+c1
+a
+EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index
+SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
+c1
+a
+EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+c1 c1
+a a
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index
+ALTER TABLE t1 MODIFY c1 VARBINARY(10);
+SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+c1 c1
+a a
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index
+DROP TABLE t1, t2;
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a'),('c');
+CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+a
+c
+# t2 should be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+ALTER TABLE t1 MODIFY c1 VARBINARY(10);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+c1
+a
+c
+# t2 should be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+DROP TABLE t1,t2;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/t/group_min_max.test'
--- mysql-test/t/group_min_max.test 2014-08-07 16:06:56 +0000
+++ mysql-test/t/group_min_max.test 2014-11-01 21:20:24 +0000
@@ -1488,3 +1488,38 @@ SELECT distinct a, b FROM t1 where a = '
SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
drop table t1;
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+--echo #
+CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'2001-01-01');
+INSERT INTO t1 VALUES (1,'2001-01-02');
+INSERT INTO t1 VALUES (1,'2001-01-03');
+INSERT INTO t1 VALUES (1,' 2001-01-04');
+INSERT INTO t1 VALUES (2,'2001-01-01');
+INSERT INTO t1 VALUES (2,'2001-01-02');
+INSERT INTO t1 VALUES (2,'2001-01-03');
+INSERT INTO t1 VALUES (2,' 2001-01-04');
+INSERT INTO t1 VALUES (3,'2001-01-01');
+INSERT INTO t1 VALUES (3,'2001-01-02');
+INSERT INTO t1 VALUES (3,'2001-01-03');
+INSERT INTO t1 VALUES (3,' 2001-01-04');
+INSERT INTO t1 VALUES (4,'2001-01-01');
+INSERT INTO t1 VALUES (4,'2001-01-02');
+INSERT INTO t1 VALUES (4,'2001-01-03');
+INSERT INTO t1 VALUES (4,' 2001-01-04');
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
=== modified file 'mysql-test/t/type_date.test'
--- mysql-test/t/type_date.test 2014-05-09 10:35:11 +0000
+++ mysql-test/t/type_date.test 2014-11-01 21:20:23 +0000
@@ -385,3 +385,162 @@ select 1 from t1 as t1_0 inner join t1 a
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
drop table t1;
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6982 LEFT JOIN table elimination is not always used when it could
+--echo #
+
+CREATE TABLE t1 (c1 INT PRIMARY KEY);
+INSERT INTO t1 VALUES (20010101),(20010102);
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
+INSERT INTO t1 VALUES (20010101),(20010102);
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DECIMAL(20,0) PRIMARY KEY);
+INSERT INTO t1 VALUES (20010101),(20010102);
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 CHAR(20) PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001/01/02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 ENUM('2001-01-01','2001/01/02') PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001/01/02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 SET('2001-01-01','2001/01/02') PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001/01/02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DATETIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
+CREATE TABLE t1 (c1 TIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('00:00:00'),('24:00:00');
+CREATE TABLE t2 (c1 DATE);
+INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING(c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+--echo # Ok to eliminate t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(c1);
+DROP TABLE t1,t2;
+SET timestamp=DEFAULT;
+
+
+--echo #
+--echo # MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could
+--echo #
+CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'2001-01-01');
+INSERT INTO t1 VALUES (1,'2001-01-02');
+INSERT INTO t1 VALUES (1,'2001-01-03');
+INSERT INTO t1 VALUES (1,'2001-01-04');
+INSERT INTO t1 VALUES (2,'2001-01-01');
+INSERT INTO t1 VALUES (2,'2001-01-02');
+INSERT INTO t1 VALUES (2,'2001-01-03');
+INSERT INTO t1 VALUES (2,'2001-01-04');
+INSERT INTO t1 VALUES (3,'2001-01-01');
+INSERT INTO t1 VALUES (3,'2001-01-02');
+INSERT INTO t1 VALUES (3,'2001-01-03');
+INSERT INTO t1 VALUES (3,'2001-01-04');
+INSERT INTO t1 VALUES (4,'2001-01-01');
+INSERT INTO t1 VALUES (4,'2001-01-02');
+INSERT INTO t1 VALUES (4,'2001-01-03');
+INSERT INTO t1 VALUES (4,'2001-01-04');
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104 GROUP BY id;
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_enum.test'
--- mysql-test/t/type_enum.test 2013-09-14 01:09:36 +0000
+++ mysql-test/t/type_enum.test 2014-11-03 08:58:47 +0000
@@ -236,3 +236,148 @@ SELECT AVG(f1) FROM t1;
drop table t1;
--echo End of 5.3 tests
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/ENUM/VARCHAR columns
+--echo #
+
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01');
+CREATE TABLE t2 (c1 ENUM('2001-01-01','2001/01/01'));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001/01/01');
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+--echo #
+CREATE TABLE t1 (c1 ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+--echo #
+--echo # MDEV-6982 LEFT JOIN table elimination is not always used when it could
+--echo #
+CREATE TABLE t1 (c1 INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 ENUM('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 ENUM('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 ENUM('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+#
+# Uncomment this after fixing MDEV-6993
+#SELECT t1.* FROM t1 JOIN t2 USING (c1);
+#
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+--echo #
+CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'01');
+INSERT INTO t1 VALUES (1,'02');
+INSERT INTO t1 VALUES (1,'03');
+INSERT INTO t1 VALUES (1,'04');
+INSERT INTO t1 VALUES (2,'01');
+INSERT INTO t1 VALUES (2,'02');
+INSERT INTO t1 VALUES (2,'03');
+INSERT INTO t1 VALUES (2,'04');
+INSERT INTO t1 VALUES (3,'01');
+INSERT INTO t1 VALUES (3,'02');
+INSERT INTO t1 VALUES (3,'03');
+INSERT INTO t1 VALUES (3,'04');
+INSERT INTO t1 VALUES (4,'01');
+INSERT INTO t1 VALUES (4,'02');
+INSERT INTO t1 VALUES (4,'03');
+INSERT INTO t1 VALUES (4,'04');
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+--echo # Should NOT use group_min_max optimization
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+--echo # Should NOT use group_min_max optimization
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_float.test'
--- mysql-test/t/type_float.test 2014-03-14 07:31:16 +0000
+++ mysql-test/t/type_float.test 2014-11-03 09:10:24 +0000
@@ -343,3 +343,45 @@ INSERT INTO t1 VALUES ('2.0.');
INSERT INTO t1 VALUES ('.');
SELECT * FROM t1 ORDER BY f;
DROP TABLE t1;
+
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns
+--echo #
+CREATE TABLE t1 (a DATETIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
+CREATE TABLE t2 (a DOUBLE);
+INSERT INTO t2 VALUES (19990101000000);
+INSERT INTO t2 VALUES (990101000000);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
+--echo #
+CREATE TABLE t1 (a TIME(6) PRIMARY KEY);
+INSERT INTO t1 VALUES ('10:20:30');
+CREATE TABLE t2 (a DOUBLE);
+INSERT INTO t2 VALUES (102030),(102030.000000001);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+--echo # t2 should NOT be elimitated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_newdecimal.test'
--- mysql-test/t/type_newdecimal.test 2014-10-07 22:46:10 +0000
+++ mysql-test/t/type_newdecimal.test 2014-11-03 09:11:22 +0000
@@ -1581,3 +1581,44 @@ select 0.0000000000000000000000000000000
select 0.0000000001 mod 1;
select 0.01 mod 1;
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
+--echo #
+CREATE TABLE t1 (a DATETIME PRIMARY KEY);
+INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
+CREATE TABLE t2 (a DECIMAL(30,1));
+INSERT INTO t2 VALUES (19990101000000);
+INSERT INTO t2 VALUES (990101000000);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
+--echo #
+CREATE TABLE t1 (a TIME(6) PRIMARY KEY);
+INSERT INTO t1 VALUES ('10:20:30');
+CREATE TABLE t2 (a DECIMAL(30,10));
+INSERT INTO t2 VALUES (102030),(102030.000000001);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1 JOIN t2 USING(a);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_set.test'
--- mysql-test/t/type_set.test 2008-09-05 15:21:59 +0000
+++ mysql-test/t/type_set.test 2014-11-03 09:02:45 +0000
@@ -95,3 +95,117 @@ DROP TABLE t1;
--echo End of 5.0 tests
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6950 Bad results with joins compating DATE and INT/ENUM/VARCHAR columns
+--echo #
+
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01');
+CREATE TABLE t2 (c1 SET('2001-01-01','2001/01/01'));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001/01/01');
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+--echo #
+CREATE TABLE t1 (c1 ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+--echo #
+--echo # MDEV-6982 LEFT JOIN table elimination is not always used when it could
+--echo #
+CREATE TABLE t1 (c1 INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 SET('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 SET('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c1 SET('a','b'));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 JOIN t2 USING (c1);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+#
+# Uncomment this after fixing MDEV-6993
+#SELECT t1.* FROM t1 JOIN t2 USING (c1);
+#
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # Ok to eliminated t2
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_uint.test'
--- mysql-test/t/type_uint.test 2005-07-28 00:22:47 +0000
+++ mysql-test/t/type_uint.test 2014-11-03 09:05:02 +0000
@@ -15,3 +15,29 @@ select * from t1;
drop table t1;
# End of 4.1 tests
+
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
+--echo #
+CREATE TABLE t1 (a DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('1999-01-01');
+CREATE TABLE t2 (a INT UNSIGNED);
+INSERT INTO t2 VALUES (19990101);
+INSERT INTO t2 VALUES (990101);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_varchar.test'
--- mysql-test/t/type_varchar.test 2009-10-09 13:34:07 +0000
+++ mysql-test/t/type_varchar.test 2014-11-03 09:06:21 +0000
@@ -217,3 +217,66 @@ CREATE TABLE t1 (a CHAR(16));
INSERT INTO t1 VALUES ('5'), ('s'), ('');
SELECT 5 = a FROM t1;
DROP TABLE t1;
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
+--echo #
+CREATE TABLE t1 (c1 DATE PRIMARY KEY);
+INSERT INTO t1 VALUES ('2001-01-01');
+CREATE TABLE t2 (c1 VARCHAR(20));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001/01/01');
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+ALTER TABLE t2 ADD PRIMARY KEY(c1);
+SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+--echo # t2 should NOT be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
+DROP TABLE IF EXISTS t1,t2;
+
+
+--echo #
+--echo # MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases
+--echo #
+CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');
+SELECT * FROM t1 WHERE c1=BINARY 'a';
+EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a';
+SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
+EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+ALTER TABLE t1 MODIFY c1 VARBINARY(10);
+SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a'),('c');
+CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # t2 should be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t1 MODIFY c1 VARBINARY(10);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # t2 should be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
=== modified file 'sql/field.cc'
--- sql/field.cc 2014-10-20 12:42:00 +0000
+++ sql/field.cc 2014-11-03 07:15:23 +0000
@@ -1246,6 +1246,66 @@ double Field::pos_in_interval_val_str(Fi
}
+/*
+ This handles all numeric and BIT data types.
+*/
+bool Field::can_optimize(const Tool &tool,
+ const Item_func *cond, const Item *arg)
+{
+ DBUG_ASSERT((tool.key_flags() & HA_SPATIAL) == 0);
+ DBUG_ASSERT(cmp_type() != STRING_RESULT);
+ /*
+ Can't optimize a date/datetime/timestamp condition on a numeruc column,
+ as mapping from numbers to date/datetime/timestamp is not linear and
+ not consistent:
+ - Different numeric values can map to the same temporal value
+ (990101 and 19990101 map to DATE'1999-01-01')
+ - Bad values like -1 and 99991232 (notice bad day) are equal to zero date:
+ SELECT DATE'0000-00-00'=-1; -> 1
+ SELECT DATE'0000-00-00'=99991232; -> 1
+ The same for time:
+ SELECT TIME'00:00:00'=99991231; -> 1
+ - Numbers and temporal values can be compared differently in different
+ context (e.g. mdev-6983).
+ Also, can't optimize a string condition on a numeric column.
+ Allow only conditions on numeric data types.
+ */
+ if (cond->compare_type() != INT_RESULT &&
+ cond->compare_type() != DECIMAL_RESULT &&
+ cond->compare_type() != REAL_RESULT )
+ return false;
+
+ switch (tool.type())
+ {
+ case TOOL_KEYPART_RANGE:
+ return tool.optimize_range(table);
+ case TOOL_KEY_GROUP_MIN_MAX:
+ case TOOL_PARTITION_RANGE:
+ case TOOL_SELECTIVITY:
+ return true;
+ case TOOL_EQUALITY_FOR_OUTER_JOIN_TABLE_ELIMINATION:
+ case TOOL_KEYPART_RANGE_TRIVIAL:
+ case TOOL_KEYPART_REF:
+ case TOOL_PARTITION_REF:
+ case TOOL_HASH_JOIN:
+ /*
+ This fixes MDEV-6969 and MDEV-6986 but breaks some existing tests.
+ Needs a discussion with the optimizer team.
+ */
+ /*
+ if (query.cond()->compare_type() == REAL_RESULT)
+ {
+ uint precise_digits= type() == MYSQL_TYPE_FLOAT ? 7 : 15;
+ if (query.item()->max_length > precise_digits)
+ return false;
+ }
+ */
+ return true;
+ }
+ return false;
+}
+
+
/**
Numeric fields base class constructor.
*/
@@ -1933,12 +1993,6 @@ int Field::store_time_dec(MYSQL_TIME *lt
}
-bool Field::optimize_range(uint idx, uint part)
-{
- return MY_TEST(table->file->index_flags(idx, part, 1) & HA_READ_RANGE);
-}
-
-
Field *Field::new_field(MEM_ROOT *root, TABLE *new_table,
bool keep_type __attribute__((unused)))
{
@@ -5256,6 +5310,31 @@ my_decimal *Field_temporal::val_decimal(
return TIME_to_my_decimal(<ime, d);
}
+
+bool Field_temporal::can_optimize(const Tool &tool,
+ const Item_func *cond, const Item *arg)
+{
+ DBUG_ASSERT((tool.key_flags() & HA_SPATIAL) == 0);
+ if (cond->compare_type() != TIME_RESULT)
+ return false;
+ switch (tool.type())
+ {
+ case TOOL_KEYPART_RANGE:
+ return tool.optimize_range(table);
+ case TOOL_EQUALITY_FOR_OUTER_JOIN_TABLE_ELIMINATION:
+ case TOOL_KEY_GROUP_MIN_MAX:
+ case TOOL_PARTITION_RANGE:
+ case TOOL_KEYPART_RANGE_TRIVIAL:
+ case TOOL_KEYPART_REF:
+ case TOOL_PARTITION_REF:
+ case TOOL_HASH_JOIN:
+ case TOOL_SELECTIVITY:
+ return true;
+ }
+ return false;
+}
+
+
/****************************************************************************
** time type
** In string context: HH:MM:SS
@@ -6462,6 +6541,72 @@ uint32 Field_longstr::max_data_length()
}
+bool Field_longstr::can_optimize(const Tool &tool,
+ const Item_func *cond, const Item *arg)
+
+{
+ DBUG_ASSERT((tool.key_flags() & HA_SPATIAL) == 0);
+ DBUG_ASSERT(arg->result_type() == STRING_RESULT ||
+ arg->cmp_type() != STRING_RESULT);
+
+ if (cond->compare_type() != STRING_RESULT)
+ return false;
+
+ switch (tool.type())
+ {
+ case TOOL_KEYPART_RANGE:
+ if (!tool.optimize_range(table))
+ return false;
+ // Pass through
+ case TOOL_HASH_JOIN:
+ case TOOL_PARTITION_RANGE:
+ case TOOL_KEY_GROUP_MIN_MAX:
+ return charset() == cond->compare_collation();
+
+ case TOOL_EQUALITY_FOR_OUTER_JOIN_TABLE_ELIMINATION:
+ /*
+ A string column cannot be eliminated when compared as a number:
+ CREATE TABLE t1 (a INT PRIMARY KEY);
+ CREATE TABLE t2 (a VARCHAR(10) PRIMARY KEY);
+ SELECT t1.* FROM t1 LEFT JOIN t2 USING (a);
+ because unique string values '10' and '010' produce
+ non-unique val_int(), val_real(), val_decimal() results.
+
+ A string column cannot be eliminated when compared as date/time/datetime,
+ because unique string values can produce non-unique get_date() values:
+ '2001-10-10' and '2001/10/10' -> DATE'2001-10-10'
+ '10:10:10' and ' 10:10:10' -> TIME'10:10:10'
+
+ A string column can be eliminated when compared as a string, only:
+ - if the collation of the operation is exactly the same with the
+ collation of the column;
+ - or if binary comparison is used.
+ These three queries return exactly the same set of rows:
+ SELECT DISTINCT primary_key_value FROM t2;
+ SELECT DISTINCT BINARY primary_key_value FROM t2;
+ SELECT DISTINCT primary_key_value COLLATE xxx_bin FROM t2;
+ So it should be safe to eliminate t2 from a LEFT JOIN in case
+ of binary comparison.
+ */
+ case TOOL_KEYPART_REF:
+ case TOOL_KEYPART_RANGE_TRIVIAL:
+ case TOOL_PARTITION_REF:
+ case TOOL_SELECTIVITY:
+ /*
+ Usually we can't use an index if the column collation
+ differ from the operation collation, with the exception that it's okey
+ to use any index (including case or accent insensitive indexes)
+ for binary equality:
+ WHERE latin1_swedish_ci_column = 'a' COLLATE lati1_bin
+ WHERE latin1_swedish_ci_colimn = BINARY 'a '
+ */
+ return (charset() == cond->compare_collation() ||
+ (cond->compare_collation()->state & MY_CS_BINSORT));
+ }
+ return false;
+}
+
+
double Field_string::val_real(void)
{
ASSERT_COLUMN_MARKED_FOR_READ;
@@ -7923,6 +8068,38 @@ int Field_geom::store(const char *from,
return -1;
}
+
+bool Field_geom::can_optimize(const Tool &tool,
+ const Item_func *cond, const Item *arg)
+
+{
+ if ((tool.key_flags() & HA_SPATIAL) == 0)
+ {
+ // Non spatial operation on a spatial column
+ return Field_blob::can_optimize(tool, cond, arg);
+ }
+
+ // @todo: use is_spatial_operator() instead?
+ switch (cond->functype()) {
+ case Item_func::SP_EQUALS_FUNC:
+ case Item_func::SP_DISJOINT_FUNC:
+ case Item_func::SP_INTERSECTS_FUNC:
+ case Item_func::SP_TOUCHES_FUNC:
+ case Item_func::SP_CROSSES_FUNC:
+ case Item_func::SP_WITHIN_FUNC:
+ case Item_func::SP_CONTAINS_FUNC:
+ case Item_func::SP_OVERLAPS_FUNC:
+ return true;
+ default:
+ /*
+ We cannot involve spatial indexes for queries that
+ don't use MBREQUALS(), MBRDISJOINT(), etc. functions.
+ */
+ break;
+ }
+ return false;
+}
+
#endif /*HAVE_SPATIAL*/
/****************************************************************************
@@ -8366,6 +8543,68 @@ const uchar *Field_enum::unpack(uchar *t
}
+bool Field_enum::can_optimize(const Tool &tool,
+ const Item_func *cond, const Item *arg)
+{
+ DBUG_ASSERT((tool.key_flags() & HA_SPATIAL) == 0);
+
+ switch (tool.type())
+ {
+ case TOOL_EQUALITY_FOR_OUTER_JOIN_TABLE_ELIMINATION:
+ {
+ /*
+ We can't eliminate t2 in case of temporal data types in t1:
+ CREATE TABLE t1 (a DATE PRIMARY KEY);
+ CREATE TABLE t2 (a ENUM('2001-01-01','2001/01/01') PRIMARY KEY);
+ SELECT t1.* FROM t1 LEFT JOIN t2 USING (a);
+ because two different ENUM values '2001-01-01' and '2001/01/01'
+ map to the same DATE value '2001-01-01'. Disallow TIME_RESULT.
+
+ Allow all numeric data types (INT, REAL, DECIMAL results).
+ If a set of ENUM values is known to be unique, the set of its
+ val_int(), val_real() and val_decimal() values is also guaranteed
+ to be unique.
+ Allow string data types (STRING result) if the collation of the operation
+ is exactly the same with the collation of the field.
+ */
+ return (cond->compare_type() == STRING_RESULT &&
+ charset() == cond->compare_collation()) ||
+ cond->compare_type() == INT_RESULT ||
+ cond->compare_type() == REAL_RESULT ||
+ cond->compare_type() == DECIMAL_RESULT;
+ }
+ case TOOL_KEY_GROUP_MIN_MAX:
+ /*
+ Can't use GROUP_MIN_MAX optimization, because the values are stored
+ as numbers in index, while MIN() and MAX() work as strings.
+ It would return the records with min and max enum numeric indexes.
+ "Bug#45300 MAX() and ENUM type" should be fixed first.
+ */
+ case TOOL_KEYPART_RANGE:
+ case TOOL_PARTITION_RANGE:
+ return false;
+ case TOOL_KEYPART_RANGE_TRIVIAL:
+ case TOOL_KEYPART_REF:
+ case TOOL_PARTITION_REF:
+ case TOOL_HASH_JOIN:
+ case TOOL_SELECTIVITY:
+ /*
+ ENUM/SET do not allow ref access for TIME_RESULT conditions,
+ because multiple ENUM/SET values can map to the same temporal value:
+ Both values in ENUM('2010/01/01','2010-01-01') equal to DATE'2010-01-01'.
+ So the lines below do not include TIME_RESULT.
+ (these lines may slightly change after fixing MDEV-6993 though)
+ */
+ return (cond->compare_type() == STRING_RESULT &&
+ charset() == cond->compare_collation()) ||
+ arg->cmp_type() == INT_RESULT ||
+ arg->cmp_type() == DECIMAL_RESULT ||
+ arg->cmp_type() == REAL_RESULT;
+ }
+ return false;
+}
+
+
/**
@return
returns 1 if the fields are equally defined
=== modified file 'sql/field.h'
--- sql/field.h 2014-10-20 12:42:00 +0000
+++ sql/field.h 2014-11-03 07:06:48 +0000
@@ -271,7 +271,182 @@ class Virtual_column_info: public Sql_al
}
};
-class Field
+
+class Item_func;
+
+class Field_optimizer
+{
+protected:
+ enum Tooltype
+ {
+ TOOL_KEYPART_REF,
+ TOOL_KEYPART_RANGE,
+ TOOL_KEYPART_RANGE_TRIVIAL, // an empty range: a BETWEEN 1 AND 1
+ TOOL_KEY_GROUP_MIN_MAX,
+ TOOL_PARTITION_REF,
+ TOOL_PARTITION_RANGE,
+ TOOL_HASH_JOIN,
+ TOOL_EQUALITY_FOR_OUTER_JOIN_TABLE_ELIMINATION,
+ TOOL_SELECTIVITY
+ };
+
+ class Key_part
+ {
+ uint m_keyno;
+ uint m_partno;
+ public:
+ Key_part(uint keyno, uint partno)
+ :m_keyno(keyno), m_partno(partno)
+ { }
+ bool optimize_range(const TABLE *table) const
+ {
+ return table->optimize_range(m_keyno, m_partno);
+ }
+ };
+
+ class Tool
+ {
+ public:
+ virtual Tooltype type() const = 0;
+ virtual uint key_flags() const { return 0; }
+ virtual bool optimize_range(const TABLE *table) const { return false; }
+ };
+
+ class Tool_key: public Tool
+ {
+ const KEY *m_key;
+ public:
+ Tool_key(const KEY *key) :m_key(key) { }
+ const KEY *key() const { return m_key; }
+ uint key_flags() const { return m_key->flags; }
+ };
+
+ class Tool_key_group_min_max: public Tool_key
+ {
+ public:
+ Tool_key_group_min_max(const KEY *key) :Tool_key(key) { }
+ Tooltype type() const { return TOOL_KEY_GROUP_MIN_MAX; }
+ };
+
+ class Tool_keypart: public Tool_key, public Key_part
+ {
+ public:
+ Tool_keypart(const KEY *key, uint16 keyno, uint16 partno)
+ :Tool_key(key), Key_part(keyno, partno) { }
+ bool optimize_range(const TABLE *table) const
+ { return Key_part::optimize_range(table); }
+ };
+
+ class Tool_keypart_ref: public Tool_keypart
+ {
+ public:
+ Tool_keypart_ref(const KEY *key, uint16 keyno, uint16 partno):
+ Tool_keypart(key, keyno, partno) { }
+ Tooltype type() const { return TOOL_KEYPART_REF; }
+ };
+
+ class Tool_keypart_range: public Tool_keypart
+ {
+ public:
+ Tool_keypart_range(const KEY *key, uint16 keyno, uint16 parno)
+ :Tool_keypart(key, keyno, parno) { }
+ Tooltype type() const { return TOOL_KEYPART_RANGE; }
+ };
+
+ class Tool_keypart_range_trivial: public Tool_keypart
+ {
+ public:
+ Tool_keypart_range_trivial(const KEY *key, uint16 keyno, uint16 parno)
+ :Tool_keypart(key, keyno, parno) { }
+ Tooltype type() const { return TOOL_KEYPART_RANGE_TRIVIAL; }
+ };
+
+ class Tool_partition: public Tool
+ {
+ Tooltype m_type;
+ public:
+ Tool_partition(Tooltype type)
+ :m_type(type)
+ {
+ DBUG_ASSERT(type == TOOL_PARTITION_REF || type == TOOL_PARTITION_RANGE);
+ }
+ Tooltype type() const { return m_type; }
+ };
+
+ class Tool_selectivity: public Tool
+ {
+ public:
+ Tooltype type() const { return TOOL_SELECTIVITY; }
+ };
+
+ class Tool_hash_join: public Tool
+ {
+ public:
+ Tooltype type() const { return TOOL_HASH_JOIN; }
+ };
+
+ class Tool_equality_for_outer_join_table_elimination: public Tool
+ {
+ public:
+ Tooltype type() const
+ {
+ return TOOL_EQUALITY_FOR_OUTER_JOIN_TABLE_ELIMINATION;
+ }
+ };
+
+ virtual bool can_optimize(const Tool &tool,
+ const Item_func *cond, const Item *arg) = 0;
+
+public:
+ bool can_optimize_keypart_ref(const KEY *key, uint keyno, uint partno,
+ Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_keypart_ref(key, keyno, partno), cond, item);
+ }
+ bool can_optimize_keypart_range(const KEY *key, uint keyno, uint partno,
+ Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_keypart_range(key, keyno, partno), cond, item);
+ }
+ bool can_optimize_keypart_range_trivial(const KEY *key,
+ uint keyno, uint partno,
+ Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_keypart_range_trivial(key, keyno, partno),
+ cond, item);
+ }
+ bool can_optimize_partition_ref(Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_partition(TOOL_PARTITION_REF), cond, item);
+ }
+ bool can_optimize_partition_range(Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_partition(TOOL_PARTITION_RANGE), cond, item);
+ }
+ bool can_optimize_key_group_min_max(const KEY *key,
+ Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_key_group_min_max(key), cond, item);
+ }
+ bool can_optimize_for_selectivity(Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_selectivity(), cond, item);
+ }
+ bool can_optimize_equality_for_outer_join_table_elimination(
+ Item_func *cond,
+ Item *item)
+ {
+ return can_optimize(Tool_equality_for_outer_join_table_elimination(),
+ cond, item);
+ }
+ bool can_optimize_hash_join(Item_func *cond, Item *item)
+ {
+ return can_optimize(Tool_hash_join(), cond, item);
+ }
+};
+
+
+class Field: public Field_optimizer
{
Field(const Item &); /* Prevent use of these */
void operator=(Field &);
@@ -731,7 +906,6 @@ class Field
void make_sort_key(uchar *buff, uint length);
virtual void make_field(Send_field *);
virtual void sort_string(uchar *buff,uint length)=0;
- virtual bool optimize_range(uint idx, uint part);
virtual void free() {}
virtual Field *new_field(MEM_ROOT *root, TABLE *new_table,
bool keep_type);
@@ -850,16 +1024,6 @@ class Field
{ return binary() ? &my_charset_bin : charset(); }
virtual CHARSET_INFO *sort_charset(void) const { return charset(); }
virtual bool has_charset(void) const { return FALSE; }
- /*
- match_collation_to_optimize_range() is to distinguish in
- range optimizer (see opt_range.cc) between real string types:
- CHAR, VARCHAR, TEXT
- and the other string-alike types with result_type() == STRING_RESULT:
- DATE, TIME, DATETIME, TIMESTAMP
- We need it to decide whether to test if collation of the operation
- matches collation of the field (needed only for real string types).
- */
- virtual bool match_collation_to_optimize_range() const { return false; }
virtual void set_charset(CHARSET_INFO *charset_arg) { }
virtual enum Derivation derivation(void) const
{ return DERIVATION_IMPLICIT; }
@@ -952,6 +1116,7 @@ class Field
return (double) 0.5;
}
+ bool can_optimize(const Tool &tool, const Item_func *cond, const Item *arg);
friend int cre_myisam(char * name, register TABLE *form, uint options,
ulonglong auto_increment_value);
friend class Copy_field;
@@ -1132,7 +1297,7 @@ class Field_longstr :public Field_str
int store_decimal(const my_decimal *d);
uint32 max_data_length() const;
- bool match_collation_to_optimize_range() const { return true; }
+ bool can_optimize(const Tool &tool, const Item_func *cond, const Item *arg);
};
/* base class for float and double and decimal (old one) */
@@ -1560,6 +1725,11 @@ class Field_null :public Field_str {
uint size_of() const { return sizeof(*this); }
uint32 max_display_length() { return 4; }
void move_field_offset(my_ptrdiff_t ptr_diff) {}
+ bool can_optimize(const Tool &tool, const Item_func *cond, const Item *arg)
+ {
+ DBUG_ASSERT(0);
+ return false;
+ }
};
@@ -1592,6 +1762,7 @@ class Field_temporal: public Field {
{
return pos_in_interval_val_real(min, max);
}
+ bool can_optimize(const Tool &tool, const Item_func *cond, const Item *arg);
};
@@ -2551,7 +2722,6 @@ class Field_geom :public Field_blob {
{ geom_type= geom_type_arg; }
enum ha_base_keytype key_type() const { return HA_KEYTYPE_VARBINARY2; }
enum_field_types type() const { return MYSQL_TYPE_GEOMETRY; }
- bool match_collation_to_optimize_range() const { return false; }
void sql_type(String &str) const;
int store(const char *to, uint length, CHARSET_INFO *charset);
int store(double nr);
@@ -2566,6 +2736,7 @@ class Field_geom :public Field_blob {
int reset(void) { return Field_blob::reset() || !maybe_null(); }
geometry_type get_geometry_type() { return geom_type; };
+ bool can_optimize(const Tool &tool, const Item_func *cond, const Item *arg);
};
#endif /*HAVE_SPATIAL*/
@@ -2608,7 +2779,7 @@ class Field_enum :public Field_str {
{ return (field_metadata & 0x00ff); }
uint row_pack_length() const { return pack_length(); }
virtual bool zero_pack() const { return 0; }
- bool optimize_range(uint idx, uint part) { return 0; }
+ bool can_optimize(const Tool &tool, const Item_func *cond, const Item *arg);
bool eq_def(Field *field);
bool has_charset(void) const { return TRUE; }
/* enum and set are sorted as integers */
=== modified file 'sql/item.h'
--- sql/item.h 2014-11-01 21:08:09 +0000
+++ sql/item.h 2014-11-03 05:38:06 +0000
@@ -114,6 +114,11 @@ class DTCollation {
derivation= derivation_arg;
set_repertoire_from_charset(collation_arg);
}
+ DTCollation(CHARSET_INFO *collation_arg, Derivation derivation_arg,
+ uint repertoire_arg)
+ :collation(collation_arg), derivation(derivation_arg),
+ repertoire(repertoire_arg)
+ { }
void set(DTCollation &dt)
{
collation= dt.collation;
@@ -1182,7 +1187,6 @@ class Item {
virtual Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
static CHARSET_INFO *default_charset();
- virtual CHARSET_INFO *compare_collation() { return NULL; }
/*
For backward compatibility, to make numeric
=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2014-11-01 21:08:09 +0000
+++ sql/item_cmpfunc.cc 2014-11-03 07:27:47 +0000
@@ -140,8 +140,8 @@ static int cmp_row_type(Item* item1, Ite
Aggregates result types from the array of items.
SYNOPSIS:
- agg_cmp_type()
- type [out] the aggregated type
+ set_compare_context()
+ name name of the function, for error reporting
items array of items to aggregate the type from
nitems number of items in the array
@@ -159,13 +159,15 @@ static int cmp_row_type(Item* item1, Ite
0 otherwise
*/
-static int agg_cmp_type(Item_result *type, Item **items, uint nitems)
+bool Hybrid_cmp_context::set_compare_context(const char *name,
+ Item **items,
+ uint nitems)
{
uint i;
- type[0]= items[0]->cmp_type();
+ m_compare_type= items[0]->cmp_type();
for (i= 1 ; i < nitems ; i++)
{
- type[0]= item_cmp_type(type[0], items[i]->cmp_type());
+ m_compare_type= item_cmp_type(m_compare_type, items[i]->cmp_type());
/*
When aggregating types of two row expressions we have to check
that they have the same cardinality and that each component
@@ -173,10 +175,13 @@ static int agg_cmp_type(Item_result *typ
the signature of the corresponding component of the second row
expression.
*/
- if (type[0] == ROW_RESULT && cmp_row_type(items[0], items[i]))
- return 1; // error found: invalid usage of rows
+ if (m_compare_type == ROW_RESULT && cmp_row_type(items[0], items[i]))
+ return true; // error found: invalid usage of rows
}
- return 0;
+ if (m_compare_type == STRING_RESULT &&
+ agg_item_charsets_for_comparison(cmp_collation, name, items, nitems))
+ return true;
+ return false;
}
@@ -537,26 +542,10 @@ void Item_bool_func2::fix_length_and_dec
if (!args[0] || !args[1])
return;
- /*
- We allow to convert to Unicode character sets in some cases.
- The conditions when conversion is possible are:
- - arguments A and B have different charsets
- - A wins according to coercibility rules
- - character set of A is superset for character set of B
-
- If all of the above is true, then it's possible to convert
- B into the character set of A, and then compare according
- to the collation of A.
- */
-
- DTCollation coll;
- if (args[0]->cmp_type() == STRING_RESULT &&
- args[1]->cmp_type() == STRING_RESULT &&
- agg_arg_charsets_for_comparison(coll, args, 2))
+ if (cmp.set_compare_context(func_name(), args, 2))
return;
-
- args[0]->cmp_context= args[1]->cmp_context=
- item_cmp_type(args[0]->result_type(), args[1]->result_type());
+
+ args[0]->cmp_context= args[1]->cmp_context= cmp.compare_type();
/*
Make a special case of compare with fields to get nicer comparisons
@@ -578,7 +567,10 @@ void Item_bool_func2::fix_length_and_dec
if ((field_item->field_type() == MYSQL_TYPE_LONGLONG ||
field_item->field_type() == MYSQL_TYPE_YEAR) &&
convert_const_to_int(thd, field_item, &args[!field]))
+ {
args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
+ cmp.set_compare_type(INT_RESULT);
+ }
}
}
set_cmp_func();
@@ -591,6 +583,7 @@ int Arg_comparator::set_compare_func(Ite
func= comparator_matrix[type]
[is_owner_equal_func()];
+ set_compare_type(type);
switch (type) {
case TIME_RESULT:
cmp_collation.collation= &my_charset_numeric;
@@ -2247,11 +2240,8 @@ void Item_func_between::fix_length_and_d
*/
if (!args[0] || !args[1] || !args[2])
return;
- if ( agg_cmp_type(&cmp_type, args, 3))
+ if (set_compare_context(func_name(), args, 3))
return;
- if (cmp_type == STRING_RESULT &&
- agg_arg_charsets_for_comparison(cmp_collation, args, 3))
- return;
/*
When comparing as date/time, we need to convert non-temporal values
@@ -2261,7 +2251,7 @@ void Item_func_between::fix_length_and_d
For this to work, we need to know what date/time type we compare
strings as.
*/
- if (cmp_type == TIME_RESULT)
+ if (compare_type() == TIME_RESULT)
compare_as_dates= find_date_time_item(args, 3, 0);
/* See the comment about the similar block in Item_bool_func2 */
@@ -2275,7 +2265,7 @@ void Item_func_between::fix_length_and_d
const bool cvt_arg1= convert_const_to_int(thd, field_item, &args[1]);
const bool cvt_arg2= convert_const_to_int(thd, field_item, &args[2]);
if (cvt_arg1 && cvt_arg2)
- cmp_type=INT_RESULT; // Works for all types.
+ set_compare_type(INT_RESULT); // Works for all types.
}
}
}
@@ -2285,7 +2275,7 @@ longlong Item_func_between::val_int()
{
DBUG_ASSERT(fixed == 1);
- switch (cmp_type) {
+ switch (compare_type()) {
case TIME_RESULT:
{
THD *thd= current_thd;
@@ -3993,7 +3983,7 @@ void Item_func_in::fix_length_and_dec()
Item *date_arg= 0;
uint found_types= 0;
uint type_cnt= 0, i;
- Item_result cmp_type= STRING_RESULT;
+ set_compare_type(STRING_RESULT);
left_result_type= args[0]->cmp_type();
if (!(found_types= collect_cmp_types(args, arg_count, true)))
return;
@@ -4011,18 +4001,18 @@ void Item_func_in::fix_length_and_dec()
if (found_types & (1U << i))
{
(type_cnt)++;
- cmp_type= (Item_result) i;
+ set_compare_type((Item_result) i);
}
}
if (type_cnt == 1)
{
- if (cmp_type == STRING_RESULT &&
+ if (compare_type() == STRING_RESULT &&
agg_arg_charsets_for_comparison(cmp_collation, args, arg_count))
return;
arg_types_compatible= TRUE;
- if (cmp_type == ROW_RESULT)
+ if (compare_type() == ROW_RESULT)
{
uint cols= args[0]->cols();
cmp_item_row *cmp= 0;
@@ -4072,7 +4062,7 @@ void Item_func_in::fix_length_and_dec()
See the comment about the similar block in Item_bool_func2
*/
if (args[0]->real_item()->type() == FIELD_ITEM &&
- !thd->lex->is_view_context_analysis() && cmp_type != INT_RESULT)
+ !thd->lex->is_view_context_analysis() && compare_type() != INT_RESULT)
{
Item_field *field_item= (Item_field*) (args[0]->real_item());
if (field_item->field_type() == MYSQL_TYPE_LONGLONG ||
@@ -4085,10 +4075,10 @@ void Item_func_in::fix_length_and_dec()
all_converted= FALSE;
}
if (all_converted)
- cmp_type= INT_RESULT;
+ set_compare_type(INT_RESULT);
}
}
- switch (cmp_type) {
+ switch (compare_type()) {
case STRING_RESULT:
array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in,
cmp_collation.collation);
@@ -4887,7 +4877,7 @@ longlong Item_func_like::val_int()
null_value=0;
if (canDoTurboBM)
return turboBM_matches(res->ptr(), res->length()) ? 1 : 0;
- return my_wildcmp(cmp.cmp_collation.collation,
+ return my_wildcmp(compare_collation(),
res->ptr(),res->ptr()+res->length(),
res2->ptr(),res2->ptr()+res2->length(),
escape,wild_one,wild_many) ? 0 : 1;
@@ -4946,7 +4936,7 @@ bool Item_func_like::fix_fields(THD *thd
return TRUE;
}
- if (use_mb(cmp.cmp_collation.collation))
+ if (use_mb(compare_collation()))
{
CHARSET_INFO *cs= escape_str->charset();
my_wc_t wc;
@@ -4963,7 +4953,7 @@ bool Item_func_like::fix_fields(THD *thd
code instead of Unicode code as "escape" argument.
Convert to "cs" if charset of escape differs.
*/
- CHARSET_INFO *cs= cmp.cmp_collation.collation;
+ CHARSET_INFO *cs= compare_collation();
uint32 unused;
if (escape_str->needs_conversion(escape_str->length(),
escape_str->charset(), cs, &unused))
@@ -5270,7 +5260,7 @@ void Item_func_like::turboBM_compute_suf
int f = 0;
int g = plm1;
int *const splm1 = suff + plm1;
- CHARSET_INFO *cs= cmp.cmp_collation.collation;
+ CHARSET_INFO *cs= compare_collation();
*splm1 = pattern_len;
@@ -5370,7 +5360,7 @@ void Item_func_like::turboBM_compute_bad
int *end = bmBc + alphabet_size;
int j;
const int plm1 = pattern_len - 1;
- CHARSET_INFO *cs= cmp.cmp_collation.collation;
+ CHARSET_INFO *cs= compare_collation();
for (i = bmBc; i < end; i++)
*i = pattern_len;
@@ -5402,7 +5392,7 @@ bool Item_func_like::turboBM_matches(con
int shift = pattern_len;
int j = 0;
int u = 0;
- CHARSET_INFO *cs= cmp.cmp_collation.collation;
+ CHARSET_INFO *cs= compare_collation();
const int plm1= pattern_len - 1;
const int tlmpl= text_len - pattern_len;
@@ -5716,8 +5706,10 @@ Item *Item_bool_rowready_func2::negated_
of the type Item_field or Item_direct_view_ref(Item_field).
*/
-Item_equal::Item_equal(Item *f1, Item *f2, bool with_const_item)
- : Item_bool_func(), eval_item(0), cond_false(0), cond_true(0),
+Item_equal::Item_equal(const Hybrid_cmp_context context,
+ Item *f1, Item *f2, bool with_const_item)
+ : Item_bool_func_hybrid_cmp_type(context),
+ eval_item(0), cond_false(0), cond_true(0),
context_field(NULL), link_equal_fields(FALSE)
{
const_item_cache= 0;
@@ -5743,8 +5735,9 @@ Item_equal::Item_equal(Item *f1, Item *f
*/
Item_equal::Item_equal(Item_equal *item_equal)
- : Item_bool_func(), eval_item(0), cond_false(0), cond_true(0),
- context_field(NULL), link_equal_fields(FALSE)
+ : Item_bool_func_hybrid_cmp_type((Hybrid_cmp_context) *item_equal),
+ eval_item(0), cond_false(0), cond_true(0),
+ context_field(NULL), link_equal_fields(FALSE)
{
const_item_cache= 0;
List_iterator_fast<Item> li(item_equal->equal_items);
@@ -6286,14 +6279,6 @@ void Item_equal::print(String *str, enum
}
-CHARSET_INFO *Item_equal::compare_collation()
-{
- Item_equal_fields_iterator it(*this);
- Item *item= it++;
- return item->collation.collation;
-}
-
-
/*
@brief Get the first equal field of multiple equality.
@param[in] field the field to get equal field to
=== modified file 'sql/item_cmpfunc.h'
--- sql/item_cmpfunc.h 2014-11-01 21:08:09 +0000
+++ sql/item_cmpfunc.h 2014-11-03 07:23:59 +0000
@@ -28,6 +28,25 @@
#define PCRE_STATIC 1 /* Important on Windows */
#include "pcre.h" /* pcre header file */
+
+class Hybrid_cmp_context
+{
+ Item_result m_compare_type;
+protected:
+ DTCollation cmp_collation;
+public:
+ Hybrid_cmp_context() :m_compare_type(REAL_RESULT) { }
+ Hybrid_cmp_context(const Field *field):
+ m_compare_type(field->cmp_type()),
+ cmp_collation(field->charset(), field->derivation(), field->repertoire())
+ { }
+ Item_result compare_type() const { return m_compare_type; }
+ CHARSET_INFO *compare_collation() const { return cmp_collation.collation; }
+ void set_compare_type(Item_result type) { m_compare_type= type; }
+ bool set_compare_context(const char *name, Item **args, uint arg_count);
+};
+
+
extern Item_result item_cmp_type(Item_result a,Item_result b);
class Item_bool_func2;
class Arg_comparator;
@@ -36,7 +55,8 @@ typedef int (Arg_comparator::*arg_cmp_fu
typedef int (*Item_field_cmpfunc)(Item *f1, Item *f2, void *arg);
-class Arg_comparator: public Sql_alloc
+class Arg_comparator: public Sql_alloc,
+ public Hybrid_cmp_context
{
Item **a, **b;
arg_cmp_func func;
@@ -49,7 +69,6 @@ class Arg_comparator: public Sql_alloc
Item *a_cache, *b_cache; // Cached values of a and b items
// when one of arguments is NULL.
public:
- DTCollation cmp_collation;
/* Allow owner function to use string buffers. */
String value1, value2;
@@ -122,7 +141,9 @@ class Item_bool_func :public Item_int_fu
Item_bool_func() :Item_int_func() {}
Item_bool_func(Item *a) :Item_int_func(a) {}
Item_bool_func(Item *a,Item *b) :Item_int_func(a,b) {}
+ Item_bool_func(Item *a, Item *b, Item *c) :Item_int_func(a, b, c) {}
Item_bool_func(THD *thd, Item_bool_func *item) :Item_int_func(thd, item) {}
+ Item_bool_func(List<Item> &list) :Item_int_func(list) { }
bool is_bool_func() { return 1; }
void fix_length_and_dec() { decimals=0; max_length=1; }
uint decimal_precision() const { return 1; }
@@ -364,7 +385,7 @@ class Le_creator :public Comp_creator
virtual bool l_op() const { return 1; }
};
-class Item_bool_func2 :public Item_int_func
+class Item_bool_func2 :public Item_bool_func
{ /* Bool with 2 string args */
protected:
Arg_comparator cmp;
@@ -372,7 +393,7 @@ class Item_bool_func2 :public Item_int_f
public:
Item_bool_func2(Item *a,Item *b)
- :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1),
+ :Item_bool_func(a, b), cmp(tmp_arg, tmp_arg+1),
abort_on_null(FALSE) { sargable= TRUE; }
void fix_length_and_dec();
int set_cmp_func()
@@ -389,14 +410,13 @@ class Item_bool_func2 :public Item_int_f
}
bool is_null() { return MY_TEST(args[0]->is_null() || args[1]->is_null()); }
- bool is_bool_func() { return 1; }
- CHARSET_INFO *compare_collation() { return cmp.cmp_collation.collation; }
- uint decimal_precision() const { return 1; }
+ CHARSET_INFO *compare_collation() const { return cmp.compare_collation(); }
+ Item_result compare_type() const { return cmp.compare_type(); }
void top_level_item() { abort_on_null= TRUE; }
Arg_comparator *get_comparator() { return &cmp; }
void cleanup()
{
- Item_int_func::cleanup();
+ Item_bool_func::cleanup();
cmp.cleanup();
}
@@ -637,6 +657,23 @@ class Item_func_ne :public Item_bool_row
};
+class Item_bool_func_hybrid_cmp_type :public Item_bool_func,
+ public Hybrid_cmp_context
+{
+public:
+ Item_bool_func_hybrid_cmp_type(const Hybrid_cmp_context context)
+ :Item_bool_func(), Hybrid_cmp_context(context) { }
+ Item_bool_func_hybrid_cmp_type(Item *a, Item *b, Item *c)
+ :Item_bool_func(a, b, c) { }
+ Item_bool_func_hybrid_cmp_type(List<Item> &list)
+ :Item_bool_func(list) { }
+ Item_result compare_type() const
+ { return Hybrid_cmp_context::compare_type(); }
+ CHARSET_INFO *compare_collation() const
+ { return Hybrid_cmp_context::compare_collation(); }
+};
+
+
/*
The class Item_func_opt_neg is defined to factor out the functionality
common for the classes Item_func_between and Item_func_in. The objects
@@ -646,16 +683,16 @@ class Item_func_ne :public Item_bool_row
*/
-class Item_func_opt_neg :public Item_int_func
+class Item_func_opt_neg :public Item_bool_func_hybrid_cmp_type
{
public:
bool negated; /* <=> the item represents NOT <func> */
bool pred_level; /* <=> [NOT] <func> is used on a predicate level */
public:
Item_func_opt_neg(Item *a, Item *b, Item *c)
- :Item_int_func(a, b, c), negated(0), pred_level(0) {}
+ :Item_bool_func_hybrid_cmp_type(a, b, c), negated(0), pred_level(0) {}
Item_func_opt_neg(List<Item> &list)
- :Item_int_func(list), negated(0), pred_level(0) {}
+ :Item_bool_func_hybrid_cmp_type(list), negated(0), pred_level(0) {}
public:
inline void negate() { negated= !negated; }
inline void top_level_item() { pred_level= 1; }
@@ -671,9 +708,7 @@ class Item_func_opt_neg :public Item_int
class Item_func_between :public Item_func_opt_neg
{
- DTCollation cmp_collation;
public:
- Item_result cmp_type;
String value0,value1,value2;
/* TRUE <=> arguments will be compared as dates. */
Item *compare_as_dates;
@@ -686,9 +721,6 @@ class Item_func_between :public Item_fun
bool fix_fields(THD *, Item **);
void fix_length_and_dec();
virtual void print(String *str, enum_query_type query_type);
- bool is_bool_func() { return 1; }
- CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
- uint decimal_precision() const { return 1; }
bool eval_not_null_tables(uchar *opt_arg);
void fix_after_pullout(st_select_lex *new_parent, Item **ref);
bool count_sargable_conds(uchar *arg);
@@ -1226,14 +1258,14 @@ class cmp_item_sort_string_in_static :pu
function and only comparators for there result types are used.
*/
-class Item_func_case :public Item_func_hybrid_field_type
+class Item_func_case :public Item_func_hybrid_field_type,
+ public Hybrid_cmp_context
{
int first_expr_num, else_expr_num;
enum Item_result left_result_type;
String tmp_value;
uint ncases;
Item_result cmp_type;
- DTCollation cmp_collation;
cmp_item *cmp_items[6]; /* For all result types */
cmp_item *case_item;
public:
@@ -1267,7 +1299,6 @@ class Item_func_case :public Item_func_h
const char *func_name() const { return "case"; }
virtual void print(String *str, enum_query_type query_type);
Item *find_item(String *str);
- CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
void cleanup();
void agg_str_lengths(Item *arg);
void agg_num_lengths(Item *arg);
@@ -1303,7 +1334,6 @@ class Item_func_in :public Item_func_opt
bool arg_types_compatible;
Item_result left_result_type;
cmp_item *cmp_items[6]; /* One cmp_item for each result type */
- DTCollation cmp_collation;
Item_func_in(List<Item> &list)
:Item_func_opt_neg(list), array(0), have_null(0),
@@ -1316,7 +1346,6 @@ class Item_func_in :public Item_func_opt
longlong val_int();
bool fix_fields(THD *, Item **);
void fix_length_and_dec();
- uint decimal_precision() const { return 1; }
void cleanup()
{
uint i;
@@ -1337,8 +1366,6 @@ class Item_func_in :public Item_func_opt
enum Functype functype() const { return IN_FUNC; }
const char *func_name() const { return " IN "; }
bool nulls_in_row();
- bool is_bool_func() { return 1; }
- CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
bool eval_not_null_tables(uchar *opt_arg);
void fix_after_pullout(st_select_lex *new_parent, Item **ref);
};
@@ -1374,10 +1401,22 @@ class in_row :public in_vector
/* Functions used by where clause */
-class Item_func_isnull :public Item_bool_func
+
+class Item_func_null_predicate :public Item_bool_func
{
public:
- Item_func_isnull(Item *a) :Item_bool_func(a) { sargable= TRUE; }
+ Item_func_null_predicate(Item *a) :Item_bool_func(a) { sargable= true; }
+ optimize_type select_optimize() const { return OPTIMIZE_NULL; }
+ CHARSET_INFO *compare_collation() const
+ { return args[0]->collation.collation; }
+ Item_result compare_type() const { return args[0]->cmp_type(); }
+};
+
+
+class Item_func_isnull :public Item_func_null_predicate
+{
+public:
+ Item_func_isnull(Item *a) :Item_func_null_predicate(a) { }
longlong val_int();
enum Functype functype() const { return ISNULL_FUNC; }
void fix_length_and_dec()
@@ -1402,9 +1441,7 @@ class Item_func_isnull :public Item_bool
}
}
table_map not_null_tables() const { return 0; }
- optimize_type select_optimize() const { return OPTIMIZE_NULL; }
Item *neg_transformer(THD *thd);
- CHARSET_INFO *compare_collation() { return args[0]->collation.collation; }
};
/* Functions used by HAVING for rewriting IN subquery */
@@ -1435,12 +1472,12 @@ class Item_is_not_null_test :public Item
};
-class Item_func_isnotnull :public Item_bool_func
+class Item_func_isnotnull :public Item_func_null_predicate
{
bool abort_on_null;
public:
- Item_func_isnotnull(Item *a) :Item_bool_func(a), abort_on_null(0)
- { sargable= TRUE; }
+ Item_func_isnotnull(Item *a) :Item_func_null_predicate(a), abort_on_null(0)
+ { }
longlong val_int();
enum Functype functype() const { return ISNOTNULL_FUNC; }
void fix_length_and_dec()
@@ -1448,12 +1485,10 @@ class Item_func_isnotnull :public Item_b
decimals=0; max_length=1; maybe_null=0;
}
const char *func_name() const { return "isnotnull"; }
- optimize_type select_optimize() const { return OPTIMIZE_NULL; }
table_map not_null_tables() const
{ return abort_on_null ? not_null_tables_cache : 0; }
Item *neg_transformer(THD *thd);
virtual void print(String *str, enum_query_type query_type);
- CHARSET_INFO *compare_collation() { return args[0]->collation.collation; }
void top_level_item() { abort_on_null=1; }
};
@@ -1603,7 +1638,6 @@ class Item_func_regex :public Item_bool_
print_op(str, query_type);
}
- CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
};
@@ -1771,7 +1805,7 @@ template <template<class> class LI, clas
object represents f1=f2= ...=fn to the projection of known fields fi1=...=fik.
*/
-class Item_equal: public Item_bool_func
+class Item_equal: public Item_bool_func_hybrid_cmp_type
{
/*
The list of equal items. Currently the list can contain:
@@ -1835,11 +1869,8 @@ class Item_equal: public Item_bool_func
COND_EQUAL *upper_levels; /* multiple equalities of upper and levels */
- inline Item_equal()
- : Item_bool_func(), with_const(FALSE), eval_item(0), cond_false(0),
- context_field(NULL)
- { const_item_cache=0; sargable= TRUE; }
- Item_equal(Item *f1, Item *f2, bool with_const_item);
+ Item_equal(const Hybrid_cmp_context context,
+ Item *f1, Item *f2, bool with_const_item);
Item_equal(Item_equal *item_equal);
/* Currently the const item is always the first in the list of equal items */
inline Item* get_const() { return with_const ? equal_items.head() : NULL; }
@@ -1866,8 +1897,6 @@ class Item_equal: public Item_bool_func
bool walk(Item_processor processor, bool walk_subquery, uchar *arg);
Item *transform(Item_transformer transformer, uchar *arg);
virtual void print(String *str, enum_query_type query_type);
- CHARSET_INFO *compare_collation();
-
void set_context_field(Item_field *ctx_field) { context_field= ctx_field; }
void set_link_equal_fields(bool flag) { link_equal_fields= flag; }
friend class Item_equal_fields_iterator;
=== modified file 'sql/item_func.h'
--- sql/item_func.h 2014-08-07 16:06:56 +0000
+++ sql/item_func.h 2014-11-03 07:43:02 +0000
@@ -30,7 +30,32 @@ extern "C" /* Bug in BSDI include fil
}
#endif
-class Item_func :public Item_result_field
+
+/*
+ Item_func metods that are needed for Field_optimizer->can_optimize_xxx().
+ In fact, these methods are only called for some Item_bool_func descendants.
+ Have DBUG_ASSERT as the default method implementation.
+ All Item_bool_func descendants that really use
+ Field_optimizer->can_optimize_xxx() override these methods.
+*/
+class Cmp_context_owner
+{
+public:
+ virtual CHARSET_INFO *compare_collation() const
+ {
+ DBUG_ASSERT(0);
+ return NULL;
+ }
+ virtual Item_result compare_type() const
+ {
+ DBUG_ASSERT(0);
+ return REAL_RESULT;
+ }
+};
+
+
+class Item_func :public Item_result_field,
+ public Cmp_context_owner
{
protected:
Item **args, *tmp_arg[2];
=== modified file 'sql/opt_range.cc'
--- sql/opt_range.cc 2014-10-28 21:33:31 +0000
+++ sql/opt_range.cc 2014-11-03 06:40:38 +0000
@@ -853,11 +853,14 @@ class RANGE_OPT_PARAM
uint keys;
/*
- If true, the index descriptions describe real indexes (and it is ok to
- call field->optimize_range(real_keynr[...], ...).
+ If true, the index descriptions describe real indexes.
Otherwise index description describes fake indexes.
*/
- bool using_real_indexes;
+ virtual bool using_real_indexes() const = 0;
+
+ virtual bool can_optimize(Item_func *conf_func, Field *field,
+ KEY_PART *key_part,
+ Item_func::Functype type, Item *value) const = 0;
/*
Aggressively remove "scans" that do not have conditions on first
@@ -892,8 +895,25 @@ class RANGE_OPT_PARAM
thd->is_error() ||
alloced_sel_args > SEL_ARG::MAX_SEL_ARGS;
}
+
+};
+
+
+class RANGE_OPT_PARAM_PARTITION: public RANGE_OPT_PARAM
+{
+public:
+ bool using_real_indexes() const { return false; }
+ bool can_optimize(Item_func *cond, Field *field, KEY_PART *key_part,
+ Item_func::Functype func_type, Item *value) const
+ {
+ return
+ func_type == Item_func::EQ_FUNC || func_type == Item_func::EQUAL_FUNC ?
+ field->can_optimize_partition_ref(cond, value) :
+ field->can_optimize_partition_range(cond, value);
+ }
};
+
class PARAM : public RANGE_OPT_PARAM
{
public:
@@ -925,6 +945,30 @@ class PARAM : public RANGE_OPT_PARAM
/* Number of ranges in the last checked tree->key */
uint n_ranges;
uint8 first_null_comp; /* first null component if any, 0 - otherwise */
+ bool using_real_indexes() const { return true; }
+ bool can_optimize(Item_func *cond, Field *field, KEY_PART *key_part,
+ Item_func::Functype type, Item *value) const
+ {
+ uint keyno= real_keynr[key_part->key];
+ KEY *key= &table->key_info[keyno];
+ return (type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) ?
+ field->can_optimize_keypart_range_trivial(key, keyno, key_part->part,
+ cond, value) :
+ field->can_optimize_keypart_range(key, keyno, key_part->part,
+ cond, value);
+ }
+};
+
+
+class PARAM_FOR_SELECTIVITY: public PARAM
+{
+public:
+ bool using_real_indexes() const { return false; }
+ bool can_optimize(Item_func *cond, Field *field, KEY_PART *key_part,
+ Item_func::Functype type, Item *value) const
+ {
+ return field->can_optimize_for_selectivity(cond, value);
+ }
};
@@ -939,10 +983,12 @@ class TABLE_READ_PLAN;
struct st_index_scan_info;
struct st_ror_scan_info;
-static SEL_TREE * get_mm_parts(RANGE_OPT_PARAM *param,COND *cond_func,Field *field,
+static SEL_TREE * get_mm_parts(RANGE_OPT_PARAM *param,
+ Item_func *cond_func, Field *field,
Item_func::Functype type,Item *value,
Item_result cmp_type);
-static SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param,COND *cond_func,Field *field,
+static SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param,
+ Item_func *cond_func, Field *field,
KEY_PART *key_part,
Item_func::Functype type,Item *value);
static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond);
@@ -3047,7 +3093,6 @@ int SQL_SELECT::test_quick_select(THD *t
param.old_root= thd->mem_root;
param.needed_reg= &needed_reg;
param.imerge_cost_buff_size= 0;
- param.using_real_indexes= TRUE;
param.remove_jump_scans= TRUE;
param.force_default_mrr= ordered_output;
param.possible_keys.clear_all();
@@ -3592,7 +3637,7 @@ bool calculate_cond_selectivity_for_tabl
if (thd->variables.optimizer_use_condition_selectivity > 2 &&
!bitmap_is_clear_all(used_fields))
{
- PARAM param;
+ PARAM_FOR_SELECTIVITY param;
MEM_ROOT alloc;
SEL_TREE *tree;
SEL_ARG **key, **end;
@@ -3612,7 +3657,6 @@ bool calculate_cond_selectivity_for_tabl
param.prev_tables= param.read_tables= 0;
param.current_table= table->map;
- param.using_real_indexes= FALSE;
param.real_keynr[0]= 0;
param.alloced_sel_args= 0;
@@ -3890,10 +3934,9 @@ typedef void (*mark_full_part_func)(part
/*
Partition pruning operation context
*/
-typedef struct st_part_prune_param
+class PART_PRUNE_PARAM: public RANGE_OPT_PARAM_PARTITION
{
- RANGE_OPT_PARAM range_param; /* Range analyzer parameters */
-
+public:
/***************************************************************
Following fields are filled in based solely on partitioning
definition and not modified after that:
@@ -3953,7 +3996,7 @@ typedef struct st_part_prune_param
uchar *cur_max_key;
uint cur_min_flag, cur_max_flag;
-} PART_PRUNE_PARAM;
+};
static bool create_partition_index_description(PART_PRUNE_PARAM *prune_par);
static int find_used_partitions(PART_PRUNE_PARAM *ppar, SEL_ARG *key_tree);
@@ -4010,7 +4053,7 @@ bool prune_partitions(THD *thd, TABLE *t
PART_PRUNE_PARAM prune_param;
MEM_ROOT alloc;
- RANGE_OPT_PARAM *range_par= &prune_param.range_param;
+ RANGE_OPT_PARAM *range_par= &prune_param;
my_bitmap_map *old_sets[2];
prune_param.part_info= part_info;
@@ -4035,7 +4078,6 @@ bool prune_partitions(THD *thd, TABLE *t
range_par->current_table= table->map;
range_par->keys= 1; // one index
- range_par->using_real_indexes= FALSE;
range_par->remove_jump_scans= FALSE;
range_par->real_keynr[0]= 0;
range_par->alloced_sel_args= 0;
@@ -4045,7 +4087,7 @@ bool prune_partitions(THD *thd, TABLE *t
bitmap_clear_all(&part_info->read_partitions);
- prune_param.key= prune_param.range_param.key_parts;
+ prune_param.key= prune_param.key_parts;
SEL_TREE *tree;
int res;
@@ -4069,8 +4111,8 @@ bool prune_partitions(THD *thd, TABLE *t
prune_param.cur_part_fields= 0;
prune_param.cur_subpart_fields= 0;
- prune_param.cur_min_key= prune_param.range_param.min_key;
- prune_param.cur_max_key= prune_param.range_param.max_key;
+ prune_param.cur_min_key= prune_param.min_key;
+ prune_param.cur_max_key= prune_param.max_key;
prune_param.cur_min_flag= prune_param.cur_max_flag= 0;
init_all_partitions_iterator(part_info, &prune_param.part_iter);
@@ -4174,7 +4216,7 @@ bool prune_partitions(THD *thd, TABLE *t
static void store_selargs_to_rec(PART_PRUNE_PARAM *ppar, SEL_ARG **start,
int num)
{
- KEY_PART *parts= ppar->range_param.key_parts;
+ KEY_PART *parts= ppar->key_parts;
for (SEL_ARG **end= start + num; start != end; start++)
{
SEL_ARG *sel_arg= (*start);
@@ -4236,7 +4278,7 @@ static int find_used_partitions_imerge_l
my_bitmap_map *bitmap_buf;
uint n_bits= ppar->part_info->read_partitions.n_bits;
bitmap_bytes= bitmap_buffer_size(n_bits);
- if (!(bitmap_buf= (my_bitmap_map*) alloc_root(ppar->range_param.mem_root,
+ if (!(bitmap_buf= (my_bitmap_map*) alloc_root(ppar->mem_root,
bitmap_bytes)))
{
/*
@@ -4300,8 +4342,8 @@ int find_used_partitions_imerge(PART_PRU
ppar->cur_part_fields= 0;
ppar->cur_subpart_fields= 0;
- ppar->cur_min_key= ppar->range_param.min_key;
- ppar->cur_max_key= ppar->range_param.max_key;
+ ppar->cur_min_key= ppar->min_key;
+ ppar->cur_max_key= ppar->max_key;
ppar->cur_min_flag= ppar->cur_max_flag= 0;
init_all_partitions_iterator(ppar->part_info, &ppar->part_iter);
@@ -4431,7 +4473,7 @@ int find_used_partitions(PART_PRUNE_PARA
bool set_full_part_if_bad_ret= FALSE;
bool ignore_part_fields= ppar->ignore_part_fields;
bool did_set_ignore_part_fields= FALSE;
- RANGE_OPT_PARAM *range_par= &(ppar->range_param);
+ RANGE_OPT_PARAM *range_par= ppar;
if (check_stack_overrun(range_par->thd, 3*STACK_MIN_SIZE, NULL))
return -1;
@@ -4842,7 +4884,7 @@ static bool fields_ok_for_partition_inde
static bool create_partition_index_description(PART_PRUNE_PARAM *ppar)
{
- RANGE_OPT_PARAM *range_par= &(ppar->range_param);
+ RANGE_OPT_PARAM *range_par= ppar;
partition_info *part_info= ppar->part_info;
uint used_part_fields, used_subpart_fields;
@@ -7530,7 +7572,8 @@ QUICK_SELECT_I *TRP_ROR_UNION::make_quic
0 on error
*/
-static SEL_TREE *get_ne_mm_tree(RANGE_OPT_PARAM *param, Item_func *cond_func,
+static SEL_TREE *get_ne_mm_tree(RANGE_OPT_PARAM *param,
+ Item_func *cond_func,
Field *field,
Item *lt_value, Item *gt_value,
Item_result cmp_type)
@@ -7565,7 +7608,8 @@ static SEL_TREE *get_ne_mm_tree(RANGE_OP
Pointer to the tree built tree
*/
-static SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Item_func *cond_func,
+static SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
+ Item_func *cond_func,
Field *field, Item *value,
Item_result cmp_type, bool inv)
{
@@ -7732,7 +7776,7 @@ static SEL_TREE *get_func_mm_tree(RANGE_
Partitioning indexes are never partial.
*/
- if (param->using_real_indexes)
+ if (param->using_real_indexes())
{
const KEY key=
param->table->key_info[param->real_keynr[idx]];
@@ -8086,7 +8130,7 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
Item_result cmp_type= field->cmp_type();
if (!((ref_tables | field->table->map) & param_comp))
{
- tree= get_mm_parts(param, cond, field, Item_func::EQ_FUNC,
+ tree= get_mm_parts(param, item_equal, field, Item_func::EQ_FUNC,
value,cmp_type);
ftree= !ftree ? tree : tree_and(param, ftree, tree);
}
@@ -8139,7 +8183,7 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
static SEL_TREE *
-get_mm_parts(RANGE_OPT_PARAM *param, COND *cond_func, Field *field,
+get_mm_parts(RANGE_OPT_PARAM *param, Item_func *cond_func, Field *field,
Item_func::Functype type,
Item *value, Item_result cmp_type)
{
@@ -8192,11 +8236,10 @@ get_mm_parts(RANGE_OPT_PARAM *param, CON
static SEL_ARG *
-get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
+get_mm_leaf(RANGE_OPT_PARAM *param, Item_func *conf_func, Field *field,
KEY_PART *key_part, Item_func::Functype type,Item *value)
{
uint maybe_null=(uint) field->real_maybe_null();
- bool optimize_range;
SEL_ARG *tree= 0;
MEM_ROOT *alloc= param->mem_root;
uchar *str;
@@ -8232,55 +8275,9 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
goto end;
}
- /*
- 1. Usually we can't use an index if the column collation
- differ from the operation collation.
-
- 2. However, we can reuse a case insensitive index for
- the binary searches:
-
- WHERE latin1_swedish_ci_column = 'a' COLLATE lati1_bin;
-
- WHERE latin1_swedish_ci_colimn = BINARY 'a '
-
- */
- if (field->result_type() == STRING_RESULT &&
- field->match_collation_to_optimize_range() &&
- value->result_type() == STRING_RESULT &&
- key_part->image_type == Field::itRAW &&
- field->charset() != conf_func->compare_collation() &&
- !(conf_func->compare_collation()->state & MY_CS_BINSORT &&
- (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC)))
+ if (!param->can_optimize(conf_func, field, key_part, type, value))
goto end;
- if (key_part->image_type == Field::itMBR)
- {
- // @todo: use is_spatial_operator() instead?
- switch (type) {
- case Item_func::SP_EQUALS_FUNC:
- case Item_func::SP_DISJOINT_FUNC:
- case Item_func::SP_INTERSECTS_FUNC:
- case Item_func::SP_TOUCHES_FUNC:
- case Item_func::SP_CROSSES_FUNC:
- case Item_func::SP_WITHIN_FUNC:
- case Item_func::SP_CONTAINS_FUNC:
- case Item_func::SP_OVERLAPS_FUNC:
- break;
- default:
- /*
- We cannot involve spatial indexes for queries that
- don't use MBREQUALS(), MBRDISJOINT(), etc. functions.
- */
- goto end;
- }
- }
-
- if (param->using_real_indexes)
- optimize_range= field->optimize_range(param->real_keynr[key_part->key],
- key_part->part);
- else
- optimize_range= TRUE;
-
if (type == Item_func::LIKE_FUNC)
{
bool like_error;
@@ -8290,8 +8287,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
size_t length, offset, min_length, max_length;
uint field_length= field->pack_length()+maybe_null;
- if (!optimize_range)
- goto end;
if (!(res= value->val_str(&tmp)))
{
tree= &null_element;
@@ -8361,17 +8356,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
goto end;
}
- if (!optimize_range &&
- type != Item_func::EQ_FUNC &&
- type != Item_func::EQUAL_FUNC)
- goto end; // Can't optimize this
-
- /*
- We can't always use indexes when comparing a string index to a number
- cmp_type() is checked to allow compare of dates to numbers
- */
- if (field->cmp_type() == STRING_RESULT && value->cmp_type() != STRING_RESULT)
- goto end;
err= value->save_in_field_no_warnings(field, 1);
if (err == 2 && field->cmp_type() == STRING_RESULT)
{
@@ -12500,7 +12484,7 @@ static bool get_constant_key_infix(KEY *
KEY_PART_INFO **first_non_infix_part);
static bool
check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
- Field::imagetype image_type,
+ const KEY *key,
bool *has_min_max_fld, bool *has_other_fld);
static void
@@ -13105,8 +13089,7 @@ get_best_group_min_max(PARAM *param, SEL
bool has_min_max_fld= false, has_other_fld= false;
if (join->conds && min_max_arg_item &&
!check_group_min_max_predicates(join->conds, min_max_arg_item,
- (index_info->flags & HA_SPATIAL) ?
- Field::itMBR : Field::itRAW,
+ index_info,
&has_min_max_fld, &has_other_fld))
DBUG_RETURN(NULL);
@@ -13157,7 +13140,7 @@ get_best_group_min_max(PARAM *param, SEL
check_group_min_max_predicates()
cond [in] the expression tree being analyzed
min_max_arg [in] the field referenced by the MIN/MAX function(s)
- image_type [in]
+ key [in]
has_min_max_arg [out] true if the subtree being analyzed references min_max_arg
has_other_arg [out] true if the subtree being analyzed references a column
other min_max_arg
@@ -13181,7 +13164,7 @@ get_best_group_min_max(PARAM *param, SEL
static bool
check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
- Field::imagetype image_type,
+ const KEY *key,
bool *has_min_max_arg, bool *has_other_arg)
{
DBUG_ENTER("check_group_min_max_predicates");
@@ -13205,7 +13188,7 @@ check_group_min_max_predicates(Item *con
the min/max argument and other columns.
*/
if (!check_group_min_max_predicates(and_or_arg, min_max_arg_item, //1
- image_type,
+ key,
&has_min_max, &has_other) ||
(func_type == Item_func::COND_OR_FUNC && has_min_max && has_other))//2
DBUG_RETURN(FALSE);
@@ -13317,23 +13300,10 @@ check_group_min_max_predicates(Item *con
if (!simple_pred(pred, args, &inv))
DBUG_RETURN(FALSE);
- /* Check for compatible string comparisons - similar to get_mm_leaf. */
if (args[0] && args[1] && !args[2] && // this is a binary function
- min_max_arg_item->result_type() == STRING_RESULT &&
- /*
- Don't use an index when comparing strings of different collations.
- */
- ((args[1]->result_type() == STRING_RESULT &&
- image_type == Field::itRAW &&
- min_max_arg_item->field->charset() !=
- pred->compare_collation())
- ||
- /*
- We can't always use indexes when comparing a string index to a
- number.
- */
- (args[1]->result_type() != STRING_RESULT &&
- min_max_arg_item->field->cmp_type() != args[1]->result_type())))
+ !min_max_arg_item->field->can_optimize_key_group_min_max(key,
+ pred,
+ args[1]))
DBUG_RETURN(FALSE);
}
else
@@ -13341,7 +13311,7 @@ check_group_min_max_predicates(Item *con
}
else if (cur_arg->type() == Item::FUNC_ITEM)
{
- if (!check_group_min_max_predicates(cur_arg, min_max_arg_item, image_type,
+ if (!check_group_min_max_predicates(cur_arg, min_max_arg_item, key,
&has_min_max, &has_other))
DBUG_RETURN(FALSE);
}
=== modified file 'sql/opt_table_elimination.cc'
--- sql/opt_table_elimination.cc 2014-02-19 10:05:15 +0000
+++ sql/opt_table_elimination.cc 2014-11-03 04:05:14 +0000
@@ -1486,24 +1486,9 @@ void check_equality(Dep_analysis_context
left->real_item()->type() == Item::FIELD_ITEM)
{
Field *field= ((Item_field*)left->real_item())->field;
- if (field->result_type() == STRING_RESULT)
- {
- if (right->result_type() != STRING_RESULT)
- {
- if (field->cmp_type() != right->result_type())
- return;
- }
- else
- {
- /*
- We can't assume there's a functional dependency if the effective
- collation of the operation differ from the field collation.
- */
- if (field->cmp_type() == STRING_RESULT &&
- field->charset() != cond->compare_collation())
- return;
- }
- }
+ if (!field->can_optimize_equality_for_outer_join_table_elimination(cond,
+ right))
+ return;
Dep_value_field *field_val;
if ((field_val= ctx->get_field_value(field)))
add_module_expr(ctx, eq_mod, and_level, field_val, right, NULL);
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2014-10-29 12:20:46 +0000
+++ sql/sql_select.cc 2014-11-03 08:10:05 +0000
@@ -4098,6 +4098,7 @@ make_join_statistics(JOIN *join, List<TA
typedef struct key_field_t {
Field *field;
Item *val; ///< May be empty if diff constant
+ Item_func *cond;
uint level;
uint optimize;
bool eq_func;
@@ -4439,20 +4440,6 @@ add_key_field(JOIN *join,
}
if (!eq_func) // eq_func is NEVER true when num_values > 1
return;
-
- /*
- We can't use indexes when comparing a string index to a
- number or two strings if the effective collation
- of the operation differ from the field collation.
- */
-
- if (field->cmp_type() == STRING_RESULT)
- {
- if ((*value)->cmp_type() != STRING_RESULT)
- return;
- if (field->charset() != cond->compare_collation())
- return;
- }
}
}
/*
@@ -4464,6 +4451,7 @@ add_key_field(JOIN *join,
(*key_fields)->field= field;
(*key_fields)->eq_func= eq_func;
(*key_fields)->val= *value;
+ (*key_fields)->cond= cond;
(*key_fields)->level= and_level;
(*key_fields)->optimize= optimize;
/*
@@ -4905,7 +4893,9 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array
uint key_parts= form->actual_n_key_parts(keyinfo);
for (uint part=0 ; part < key_parts ; part++)
{
- if (field->eq(form->key_info[key].key_part[part].field))
+ if (field->eq(form->key_info[key].key_part[part].field) &&
+ field->can_optimize_keypart_ref(keyinfo, key, part,
+ key_field->cond, key_field->val))
{
if (add_keyuse(keyuse_array, key_field, key, part))
return TRUE;
@@ -4916,6 +4906,8 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array
(key_field->optimize & KEY_OPTIMIZE_EQ) &&
key_field->val->used_tables())
{
+ if (!field->can_optimize_hash_join(key_field->cond, key_field->val))
+ return false;
/*
If a key use is extracted from an equi-join predicate then it is
added not only as a key use for every index whose component can
@@ -12407,7 +12399,7 @@ static bool check_simple_equality(Item *
else
{
/* None of the fields was found in multiple equalities */
- Item_equal *item_equal= new Item_equal(orig_left_item,
+ Item_equal *item_equal= new Item_equal(left_field, orig_left_item,
orig_right_item,
FALSE);
item_equal->set_context_field((Item_field*)left_item);
@@ -12444,20 +12436,26 @@ static bool check_simple_equality(Item *
{
bool copyfl;
- if (field_item->cmp_type() == STRING_RESULT)
+ Hybrid_cmp_context cmp;
+ Item *args[2]= {orig_left_item, orig_right_item};
+ if (cmp.set_compare_context("=<mul>=", args, 2))
+ {
+ /*
+ Should not happen, as all comparison arguments (field + const pairs)
+ have previously been checked for compatibility in fix_length_and_dec
+ or their original comparison functions, e.g Item_func_eq.
+ */
+ DBUG_ASSERT(0);
+ return false;
+ }
+
+ if (cmp.compare_type() == STRING_RESULT)
{
CHARSET_INFO *cs= field_item->field->charset();
- if (!item)
- {
- Item_func_eq *eq_item;
- if (!(eq_item= new Item_func_eq(orig_left_item, orig_right_item)) ||
- eq_item->set_cmp_func())
- return FALSE;
- eq_item->quick_fix_field();
- item= eq_item;
- }
- if ((cs != ((Item_func *) item)->compare_collation()) ||
- !cs->coll->propagate(cs, 0, 0))
+ CHARSET_INFO *cmpcs= item && item->type() == Item::FUNC_ITEM ?
+ ((Item_func *) item)->compare_collation() :
+ cmp.compare_collation();
+ if (cs != cmpcs || !cs->coll->propagate(cs, 0, 0))
return FALSE;
}
@@ -12480,7 +12478,7 @@ static bool check_simple_equality(Item *
}
else
{
- item_equal= new Item_equal(const_item, orig_field_item, TRUE);
+ item_equal= new Item_equal(cmp, const_item, orig_field_item, TRUE);
item_equal->set_context_field(field_item);
cond_equal->current_level.push_back(item_equal);
}
=== modified file 'sql/table.h'
--- sql/table.h 2014-09-16 12:03:17 +0000
+++ sql/table.h 2014-11-03 06:45:20 +0000
@@ -1373,6 +1373,11 @@ struct TABLE
DBUG_VOID_RETURN;
}
+ bool optimize_range(uint idx, uint part) const
+ {
+ return MY_TEST(file->index_flags(idx, part, 1) & HA_READ_RANGE);
+ }
+
bool update_const_key_parts(COND *conds);
uint actual_n_key_parts(KEY *keyinfo);
ulong actual_key_flags(KEY *keyinfo);
Follow ups
