Wrong result for a join with OR in the WHERE condition when using a view

[Igor Babaev]

If in maria-db 5.3 one creates and populates tables t1 and t2 with the following commands:
  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);
and then runs the query:
  select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
one gets the result:

MariaDB [test]> 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 |
+------+------+------+

This result is correct.

If now one create the view
  create view v as select * from t2;
and executes an equivalent query
  select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.c >=4;
one gets:
MariaDB [test]> select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.c >=4;
+------+------+------+
| a | b | c |
+------+------+------+
| 2 | 4 | 4 |
| 2 | 4 | 4 |
+------+------+------+

This result is incorrect.

Why does it happen? EXPLAIN EXTENDED for these queries give us an answer:

MariaDB [test]> 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) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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))) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> explain extended select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.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) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`b` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4))) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As we can see the optimizer performs an invalid transformation of the second query.

mariadb-5.1/5.2 are not affected because there the optimizer does not perform any optimizations for the second query at all:
MariaDB [test]> explain extended select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.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 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`.`t2`.`c` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t2`.`c` >= 4))) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4