diff options
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 155 |
1 files changed, 155 insertions, 0 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 6f6fc949222..2b6bd3b3706 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3974,3 +3974,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; |