Bug #30788: Inconsistent retrieval of char/varchar

Index lookup does not always guarantee that we can
simply remove the relevant conditions from the WHERE
clause. Reasons can be e.g. conversion errors, 
partial indexes etc. 
The optimizer was removing these parts of the WHERE 
condition without any further checking.
This leads to "false positives" when using indexes.
Fixed by checking the index reference conditions
(using WHERE) when using indexes with sub-queries.


mysql-test/r/subselect.result:
  Bug #30788: 
   - using where
   - test case
mysql-test/r/subselect3.result:
  Bug #30788: using where
mysql-test/t/subselect.test:
  Bug #30788: test case
sql/item.h:
  Bug #30788: 
   - Declare eq() method of Item_cache descendants : this is used in
   test_if_ref()
   - preserve the field that is being cached for type comparisions
sql/sql_select.cc:
  Bug #30788: Don't remove the WHERE when using index lookup 
  with subqueries.

1 files changed, 1 insertions, 1 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 098dacc8189..bdf00e4c307 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -757,6 +757,6 @@ a
 EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
 DROP TABLE t1;
 End of 5.0 tests