> hist.id will never be NULL and 5 at the same time. Your clauses conflict
> with each other.
> You need to re-write your query.
This is ture, except the query suceeds if there is records in 'hist'
Even if it is not a bug it is at least inconsistent behaviour of MySQL
>
> "Michael Griffith" <[EMAIL PROTECTED]> wrote:
>
> EXPLAIN LEFT JOIN fails when joining on an empty table with constant in
> WHERE clause
>
> CREATE TABLE idx (
> id mediumint,
> KEY id (id),
> ) TYPE=MyISAM
>
> CREATE TABLE hist (
> id mediumint,
> link mediumint,
> KEY id (id,link)
> ) TYPE=MyISAM
>
> Table idx is populated with thousands or records. Consider the following
> query:
>
> 1. SELECT idx.id FROM idx
> 2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5
> 3. WHERE
> 4. hist.id=5 AND
> 5. hist.id IS NULL AND
> 6. idx.id=2
>
> The query works perfectly, buy ONLY if table 'hist' is populated. If table
> 'hist' is empty then the query returns no results. This is easy to fix by
> commenting out line #4. However by commenting out line 4 the optimizer
> decides to use a temporary table which would slow it down.
>
> Here is the EXPLAIN output as shown above with empty 'hist' table:
> "Impossible WHERE noticed after reading const tables"
>
> Here is the EXPLAIN output from above query when 'hist' has 1 record:
>
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra
>
+-------+------+---------------+------+---------+------------+------+-------
>
> ------------------------------+
> | idx | ref | id | id | 3 | const | 3 |
> where used ; Using index (using temp without ln #4)
> | hist | ref | id | id | 6 | const,idx.id | 2 |
> where used; Using index; Not exists
>
+-------+------+---------------+------+---------+------------+------+-------
>
> ------------------------------+
>
> It seems the optimizer is checking the constant in hist before it even
> considers this is a LEFT JOIN. This is a problem on my Windows MySQL
> 3.23.33. Based on the EXPLAIN output of a simpler query I do not believe
> 3.22 does the same thing.
>
>
>
>
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
