Bug #15881: cast problems

  The optimizer removes expressions from GROUP BY/DISTINCT
  if they happen to participate in a <expression> = <const>
  predicates of the WHERE clause (the idea being that if
  it's always equal to a constant it can't have multiple 
  values).
  However for predicates where the expression and the 
  constant item are of different result type this is not
  valid (e.g. a string column compared to 0).
  Fixed by additional check of the result types of the 
  expression and the constant and if they differ the 
  expression don't get removed from the group by list.


mysql-test/r/distinct.result:
  Bug #15881: cast problems
    - test case
mysql-test/t/distinct.test:
  Bug #15881: cast problems
    - test case
sql/sql_select.cc:
  Bug #15881: cast problems
    - can't use <expr>=<const> to remove GROUP BY/DISTINCT cols
      if they're not of the same type.

1 files changed, 21 insertions, 0 deletions

diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index b2cc42cc0ff..8734b940241 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -504,3 +504,24 @@ DROP TABLE t1;
 #DROP TABLE t1;
 #DROP TABLE t2;
 
+#
+# Bug #15881: cast problems
+#
+CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+SELECT DISTINCT a FROM t1 WHERE a=0;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
+                WHERE ADDDATE(a,1) = '2002-08-03');
+CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
+INSERT INTO t2 VALUES (0xf6);
+INSERT INTO t2 VALUES ('oe');
+
+SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
+SELECT COUNT(*) FROM 
+  (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
+
+DROP TABLE t1, t2;