summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/type_enum.result41
-rw-r--r--mysql-test/r/type_float.result67
-rw-r--r--mysql-test/r/type_newdecimal.result81
-rw-r--r--mysql-test/r/type_set.result41
-rw-r--r--mysql-test/r/type_uint.result37
-rw-r--r--mysql-test/r/type_varchar.result41
-rw-r--r--mysql-test/t/type_enum.test27
-rw-r--r--mysql-test/t/type_float.test42
-rw-r--r--mysql-test/t/type_newdecimal.test41
-rw-r--r--mysql-test/t/type_set.test27
-rw-r--r--mysql-test/t/type_uint.test26
-rw-r--r--mysql-test/t/type_varchar.test26
-rw-r--r--sql/opt_range.cc2
-rw-r--r--sql/opt_table_elimination.cc2
-rw-r--r--sql/sql_select.cc4
15 files changed, 505 insertions, 0 deletions
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index d373d14c089..bc71d51152d 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1869,3 +1869,44 @@ 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;
+#
+# End of 10.0 tests
+#
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index f498b6889a5..57e2660750b 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -465,3 +465,70 @@ 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
+#
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 3f9f234c740..ab075d29e22 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -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
+#
diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result
index 09531ec60d5..bc6cbf202c6 100644
--- a/mysql-test/r/type_set.result
+++ b/mysql-test/r/type_set.result
@@ -104,3 +104,44 @@ INSERT INTO t1 ( set_unique_utf8 ) VALUES ( '' );
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;
+#
+# End of 10.0 tests
+#
diff --git a/mysql-test/r/type_uint.result b/mysql-test/r/type_uint.result
index e08605fb237..10aa2f2f393 100644
--- a/mysql-test/r/type_uint.result
+++ b/mysql-test/r/type_uint.result
@@ -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
+#
diff --git a/mysql-test/r/type_varchar.result b/mysql-test/r/type_varchar.result
index 38ed8a47339..965d113124b 100644
--- a/mysql-test/r/type_varchar.result
+++ b/mysql-test/r/type_varchar.result
@@ -510,3 +510,44 @@ 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;
+#
+# End of 10.0 tests
+#
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index 5b0b70631a5..0a4bdc864e3 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -236,3 +236,30 @@ 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 # End of 10.0 tests
+--echo #
diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test
index 96ce1bcbd9e..bb7a784553e 100644
--- a/mysql-test/t/type_float.test
+++ b/mysql-test/t/type_float.test
@@ -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 #
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index bb5a61d84ec..09149253c67 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1581,3 +1581,44 @@ select 0.000000000000000000000000000000000000000000000000001 mod 1;
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 #
diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test
index a851d116743..35729b277cd 100644
--- a/mysql-test/t/type_set.test
+++ b/mysql-test/t/type_set.test
@@ -95,3 +95,30 @@ 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 # End of 10.0 tests
+--echo #
diff --git a/mysql-test/t/type_uint.test b/mysql-test/t/type_uint.test
index a9212183cb6..3a949c5c47a 100644
--- a/mysql-test/t/type_uint.test
+++ b/mysql-test/t/type_uint.test
@@ -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 #
diff --git a/mysql-test/t/type_varchar.test b/mysql-test/t/type_varchar.test
index 33b84266118..528d26d6f86 100644
--- a/mysql-test/t/type_varchar.test
+++ b/mysql-test/t/type_varchar.test
@@ -217,3 +217,29 @@ 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 # End of 10.0 tests
+--echo #
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 6871a6bd0af..7854a4b0081 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -8252,6 +8252,8 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
!(conf_func->compare_collation()->state & MY_CS_BINSORT &&
(type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC)))
goto end;
+ if (value->cmp_type() == TIME_RESULT && field->cmp_type() != TIME_RESULT)
+ goto end;
if (key_part->image_type == Field::itMBR)
{
diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc
index 2ef565517b5..9c10790f9c0 100644
--- a/sql/opt_table_elimination.cc
+++ b/sql/opt_table_elimination.cc
@@ -1486,6 +1486,8 @@ void check_equality(Dep_analysis_context *ctx, Dep_module_expr **eq_mod,
left->real_item()->type() == Item::FIELD_ITEM)
{
Field *field= ((Item_field*)left->real_item())->field;
+ if (right->cmp_type() == TIME_RESULT && field->cmp_type() != TIME_RESULT)
+ return;
if (field->result_type() == STRING_RESULT)
{
if (right->result_type() != STRING_RESULT)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ef69f8c11c8..ce17adf44d4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4440,6 +4440,10 @@ add_key_field(JOIN *join,
if (!eq_func) // eq_func is NEVER true when num_values > 1
return;
+ if ((*value)->cmp_type() == TIME_RESULT &&
+ field->cmp_type() != TIME_RESULT)
+ return;
+
/*
We can't use indexes when comparing a string index to a
number or two strings if the effective collation
View Lorry Controller Status