diff options
Diffstat (limited to 'mysql-test/r/select_jcl6.result')
| -rw-r--r-- | mysql-test/r/select_jcl6.result | 149 |
1 files changed, 148 insertions, 1 deletions
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index e9c88771752..c4f071aedb0 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -5362,7 +5362,7 @@ a b c 8 8 8 DROP TABLE t1,t2; # -# Bug mdev-4413: another manifestations of bug mdev-2474 +# Bug mdev-4413: another manifestations of bug mdev-4274 # (valgrind complains) # CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; @@ -5374,7 +5374,154 @@ WHERE c = a AND ( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c ); a b c DROP TABLE t1, t2; +# +# Bug mdev-4355: equalities from the result of simplification of OR +# are not propagated to lower AND levels +# +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +a b +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 5) and (`test`.`t1`.`a` = 5)) +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +a b +DROP TABLE t1; +# +# Bug mdev-4418: impossible multiple equality in OR formula +# after row substitution +# +CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,'j'), (8,'v'); +CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('k','k'); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136)) +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +a b c d +DROP TABLE t1,t2; +# +# Bug mdev-4944: range conditition in OR formula with fields +# belonging to multiple equalities +# +CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,8); +CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8), (9); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0 +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +i1 j1 i2 +DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value |