Bug #3759 LEFT JOIN not fully optimized with IN condition in where

• Submitted: 14 May 2004 11:48
• Modified: 30 Aug 2004 17:15
• Reporter: Martin Friebe (Gold Quality Contributor) (OCA)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.18
• OS: FreeBSD (freebsd (possible all))
• Assigned to: Timour Katchaounov
• CPU Architecture: Any

[14 May 2004 11:48] Martin Friebe

Description:
From the documentation
> 7.2.8 How MySQL Optimizes LEFT JOIN and RIGHT JOIN
> Starting from 4.0.14, MySQL does the following LEFT JOIN optimization: If the
> WHERE condition is always false for the generated NULL row, the LEFT JOIN is
> changed to a normal join.

I found this does not work, if the where condition uses the "IN" operator. See "How To Repeat". The first explain contains an IN operator, the table order is not modified.
If the statement uses an "=" operator or "IN" in a normal join, the server does adjust the table order.

How to repeat:
create temporary table t1 (
 a integer,  b integer, index(a), index(b)
);
create temporary table t2 (
 c integer,  d integer, index(c), index(d)
);

insert into t1 values (1,2), (2,2), (3,2), (4,2);
insert into t2 values (1,3), (2,3), (3,4), (4,4);

explain select * from t1 left join t2 on a=c where d in (4);
#table	type	possible_keys	key	key_len	ref	rows	Extra
#t1	ALL	NULL	NULL	NULL	NULL	4	
#t2	ref	c	c	5	t1.a	4	Using where

explain select * from t1 left join t2 on a=c where d = 4;
#table	type	possible_keys	key	key_len	ref	rows	Extra
#t2	ref	c,d	d	5	const	2	Using where
#t1	ALL	a	NULL	NULL	NULL	3	Using where

explain select * from t1      join t2 on a=c where d in (4);
#table	type	possible_keys	key	key_len	ref	rows	Extra
#t2	range	c,d	d	5	NULL	2	Using where
#t1	ALL	a	NULL	NULL	NULL	3	Using where

[17 May 2004 12:58] Martin Friebe

Also just seen the differnece in the output of explain between =4 and in(4).
Is there are reason why the ref for t2 is differnt (const vs NULL)?

Should an IN (single value) be treated like an = ?

[28 May 2004 5:01] MySQL Verification Team

Verified against latest 4.0 bk tree.

[30 Aug 2004 17:15] Timour Katchaounov

Solution:
The cause of the bug is that Item_func_in::fix_fields did not fully update its
used_table_cache. This was the cause for not_null_tables in setup_conds() to be
still 0 after the call
    not_null_tables= (*conds)->not_null_tables();
As a result the condition in setup_conds()
    if ( ... (table->table->map & not_null_tables) ...)
failed, which was the cause for the ON expression not to be added to
conds, and later the optimizer couldn't detect that it could apply
the OUTER JOIN ==> JOIN optimization.