RE: creating index changes result of select query.

Creating an index on a in the queries below table causes a subsequent select
query to produce a wrong result.
How-To-Repeat:
----------------------------------------
CREATE TABLE jp_domain
select p_dom_name
from TBL_ORDER;

create index temp on jp_domain (p_dom_name);

SELECT t1.* FROM jp_reconcile_in as t1
LEFT JOIN jp_domain as t2 ON t1.domain=t2.p_dom_name
WHERE t2.p_dom_name IS NULL;

--------------------------------------
CREATE TABLE jp_domain
select p_dom_name
from TBL_ORDER;

SELECT t1.* FROM jp_reconcile_in as t1
LEFT JOIN jp_domain as t2 ON t1.domain=t2.p_dom_name
WHERE t2.p_dom_name IS NULL;

-----------------------------------

When the index is not created I get a different (I belive correct ) result.
When the index is created the final query returns too many rows. I tried
create unique index temp on jp_domain (p_dom_name);
I also tried creating the index as part of the create TABLE statement. all
with the same reult. p_dom_name is a varchar 100 which may contain nulls

jp_reconcile has a one column with a primary key on it

Gran Morgan


------------------------------------------------------------ ---------
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 bugs-thread13780@lists.mysql.com
To unsubscribe, e-mail