Description:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.3-alpha-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t60 (a int, b int ) engine=ndbcluster;
Query OK, 0 rows affected (0.35 sec)
mysql> create table t61 (a int, b int ) engine=ndbcluster;
Query OK, 0 rows affected (0.29 sec)
mysql>
mysql> insert into t60 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t60 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into t61 values (1,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t61 values (1,4);
Query OK, 1 row affected (0.00 sec)
mysql> set engine_condition_pushdown=off;
Query OK, 0 rows affected (0.00 sec)
select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null;
Empty set (0.04 sec)
mysql> set engine_condition_pushdown=on;
Query OK, 0 rows affected (0.00 sec)
select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | NULL | NULL |
| 1 | 1 | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)
mysql> explain extended select * from t60 left join t61 on t60.a=t61.a wh
ere t61.b = 3 or t61.b is null;
+----+-------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------------------------
-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------------------------
-------------------------+
| 1 | SIMPLE | t60 | ALL | NULL | NULL | NULL | NULL
| 2 |
|
| 1 | SIMPLE | t61 | ALL | NULL | NULL | NULL | NULL
| 2 | Using where with pushed condition: ((`test`.`t61`.`b` = 3) or i
snull(`test`.`t61`.`b`)) |
+----+-------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------------------------
-------------------------+
2 rows in set, 1 warning (0.06 sec)
select * from t60 join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null;
Empty set (0.00 sec)
mysql> explain extended select * from t60 join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
| 1 | SIMPLE | t60 | ALL | NULL | NULL | NULL | NULL | 2 |
|
| 1 | SIMPLE | t61 | ALL | NULL | NULL | NULL | NULL | 2 | Using where with pushed condition: ((`test`.`t61`.`b` = 3) or isnull(`test`.`t61`.`b`)) |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
How to repeat:
create table t60 (a int, b int ) engine=ndbcluster;
create table t61 (a int, b int ) engine=ndbcluster;
insert into t60 values (1,1);
insert into t60 values (1,1);
insert into t61 values (1,4);
insert into t61 values (1,4);
set engine_condition_pushdown=on;
select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null;
Suggested fix:
Current workaround is not to use condition pushdown for left joins,
thus no showstopper. Analysing problem.
Description: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.3-alpha-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table t60 (a int, b int ) engine=ndbcluster; Query OK, 0 rows affected (0.35 sec) mysql> create table t61 (a int, b int ) engine=ndbcluster; Query OK, 0 rows affected (0.29 sec) mysql> mysql> insert into t60 values (1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into t60 values (1,1); Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into t61 values (1,4); Query OK, 1 row affected (0.00 sec) mysql> insert into t61 values (1,4); Query OK, 1 row affected (0.00 sec) mysql> set engine_condition_pushdown=off; Query OK, 0 rows affected (0.00 sec) select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null; Empty set (0.04 sec) mysql> set engine_condition_pushdown=on; Query OK, 0 rows affected (0.00 sec) select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null; +------+------+------+------+ | a | b | a | b | +------+------+------+------+ | 1 | 1 | NULL | NULL | | 1 | 1 | NULL | NULL | +------+------+------+------+ 2 rows in set (0.00 sec) mysql> explain extended select * from t60 left join t61 on t60.a=t61.a wh ere t61.b = 3 or t61.b is null; +----+-------------+-------+------+---------------+------+---------+----- -+------+---------------------------------------------------------------- -------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+----- -+------+---------------------------------------------------------------- -------------------------+ | 1 | SIMPLE | t60 | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | SIMPLE | t61 | ALL | NULL | NULL | NULL | NULL | 2 | Using where with pushed condition: ((`test`.`t61`.`b` = 3) or i snull(`test`.`t61`.`b`)) | +----+-------------+-------+------+---------------+------+---------+----- -+------+---------------------------------------------------------------- -------------------------+ 2 rows in set, 1 warning (0.06 sec) select * from t60 join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null; Empty set (0.00 sec) mysql> explain extended select * from t60 join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+ | 1 | SIMPLE | t60 | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | SIMPLE | t61 | ALL | NULL | NULL | NULL | NULL | 2 | Using where with pushed condition: ((`test`.`t61`.`b` = 3) or isnull(`test`.`t61`.`b`)) | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) How to repeat: create table t60 (a int, b int ) engine=ndbcluster; create table t61 (a int, b int ) engine=ndbcluster; insert into t60 values (1,1); insert into t60 values (1,1); insert into t61 values (1,4); insert into t61 values (1,4); set engine_condition_pushdown=on; select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null; Suggested fix: Current workaround is not to use condition pushdown for left joins, thus no showstopper. Analysing problem.