Hi.
On Mon, Mar 19, 2001 at 09:06:49PM -0700, [EMAIL PROTECTED] wrote:
> > 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
No, it is well possible that EXPLAIN fails and SELECT succeeds.
EXPLAIN fails because it cannot tell you how it would optimize the
query, because the query returns in an early stage with an empty
result set.
Maybe I didn't understand your objection completely. But what I see
seems perfectly reasonable to me.
Bye,
Benjamin.
> >
> > "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
