Fixed LP bugs #717577, #724942.

Both these two bugs happened due to the following problem.
When a view column is referenced in the query an Item_direct_view_ref
object is created that is refers to the Item_field for the column.
All references to the same view column refer to the same Item_field.
Different references can belong to different AND/OR levels and,
as a result, can be included in different Item_equal object.
These Item_equal objects may include different constant objects.
If these constant objects are substituted for the Item_field created
for a view column we have a conflict situation when the second
substitution annuls the first substitution. This leads to
wrong result sets returned by the query. Bug #724942 demonstrates
such an erroneous behaviour.
Test case of the bug #717577 produces wrong result sets because best
equal fields of the multiple equalities built for different OR levels
of the WHERE condition differs. The subsitution for the best equal field
in the second OR branch overwrites the the substitution made for the
first branch.

To avoid such conflicts we have to substitute for the references
to the view columns rather than for the underlying field items.
To make such substitutions possible we have to include into
multiple equalities references to view columns rather than 
field items created for such columns.

This patch modifies the Item_equal class to include references
to view columns into multiple equality objects. It also performs
a clean up of the class methods and adds more comments. The methods
of the Item_direct_view_ref class that assist substitutions for
references to view columns has been also added by this patch.

1 files changed, 155 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 3037ef4942f..4c54a2889f6 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3959,3 +3959,158 @@ WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5;
 a	pk	b	pk	b
 DROP VIEW v1;
 DROP TABLE t1, t2, t3;
+#
+# Bug#717577: substitution for best field in a query over a view and
+#             with OR in the WHERE condition 
+#
+create table t1 (a int, b int);
+insert into t1 values (2,4), (1,3);
+create table t2 (c int);
+insert into t2 values (6), (4), (1), (3), (8), (3), (4), (2);
+select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
+a	b	c
+2	4	4
+1	3	1
+2	4	4
+2	4	2
+explain extended
+select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4)))
+create view v1 as select * from t2;
+select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4;
+a	b	c
+2	4	4
+1	3	1
+2	4	4
+2	4	2
+explain extended
+select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4)))
+create view v2 as select * from v1;
+select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4;
+a	b	c
+2	4	4
+1	3	1
+2	4	4
+2	4	2
+explain extended
+select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4)))
+create view v3 as select * from t1;
+select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4;
+a	b	c
+2	4	4
+1	3	1
+2	4	4
+2	4	2
+explain extended
+select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4)))
+drop view v1,v2,v3;
+drop table t1,t2;
+#
+# Bug#724942: substitution of the constant into a view field
+#              
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2), (9), (9), (6), (5), (4), (7);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3;
+a
+2
+9
+9
+6
+5
+4
+7
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -(1))
+SELECT * FROM v1 WHERE a > -1 OR a AND a = 0;
+a
+2
+9
+9
+6
+5
+4
+7
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > -1 OR a AND a = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -(1))
+CREATE VIEW v2 AS SELECT * FROM v1;
+SELECT * FROM v2 WHERE a > -1 OR a AND a = 0;
+a
+2
+9
+9
+6
+5
+4
+7
+EXPLAIN EXTENDED
+SELECT * FROM v2 WHERE a > -1 OR a AND a = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -(1))
+DROP VIEW v1,v2;
+DROP TABLE t1;
+CREATE TABLE t1 (a varchar(10), KEY (a)) ;
+INSERT INTO t1 VALUES
+('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+a
+KK
+MM
+ZZ
+ZZ
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'VV'
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'VV'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 'JJ')
+SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
+a
+KK
+MM
+ZZ
+ZZ
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'VV'
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'VV'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 'JJ')
+DROP VIEW v1;
+DROP TABLE t1;