diff options
Diffstat (limited to 'mysql-test/r/negation_elimination.result')
| -rw-r--r-- | mysql-test/r/negation_elimination.result | 121 |
1 files changed, 118 insertions, 3 deletions
diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index dea0d865d87..5b09b0fc511 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -4,7 +4,7 @@ insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19); explain select * from t1 where not(not(a)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 20 Using where; Using index select * from t1 where not(not(a)); a 1 @@ -375,6 +375,121 @@ a 13 14 15 +# XOR (Note: XOR is negated by negating one of the operands) +# Should return 6,7 +SELECT * FROM t1 WHERE ((a > 5) XOR (a > 7)); +a +6 +7 +# Should return 0..5,8..19 +SELECT * FROM t1 WHERE ((NOT (a > 5)) XOR (a > 7)); +a +0 +1 +2 +3 +4 +5 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +SELECT * FROM t1 WHERE ((a > 5) XOR (NOT (a > 7))); +a +0 +1 +2 +3 +4 +5 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +SELECT * FROM t1 WHERE NOT ((a > 5) XOR (a > 7)); +a +0 +1 +2 +3 +4 +5 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +# Should return 6,7 +SELECT * FROM t1 WHERE NOT ((NOT (a > 5)) XOR (a > 7)); +a +6 +7 +SELECT * FROM t1 WHERE NOT ((a > 5) XOR (NOT (a > 7))); +a +6 +7 +# Should return 0..5,8..19 +SELECT * FROM t1 WHERE NOT ((NOT (a > 5)) XOR (NOT (a > 7))); +a +0 +1 +2 +3 +4 +5 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +# Should have empty result +SELECT * FROM t1 WHERE (NULL XOR (a > 7)); +a +SELECT * FROM t1 WHERE NOT (NULL XOR (a > 7)); +a +# Should be simplified to "...WHERE (a XOR a) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT ((NOT a) XOR (a)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index NULL a 5 NULL 21 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` xor `test`.`t1`.`a`) +# Should be simplified to "...WHERE (a XOR a) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT (a XOR (NOT a)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index NULL a 5 NULL 21 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` xor `test`.`t1`.`a`) +# End XOR delete from t1 where a > 3; select a, not(not(a)) from t1; a not(not(a)) @@ -385,7 +500,7 @@ NULL NULL 3 1 explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like "1"), not (a not in (1,2)), not(a != 2) from t1 where not(not(a)) having not(not(a)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL a 5 NULL 5 100.00 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 4 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a` +Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where (`test`.`t1`.`a` <> 0) having (`test`.`t1`.`a` <> 0) drop table t1; |