| Bug #7016 | LEFT JOIN results incorrect | ||
|---|---|---|---|
| Submitted: | 5 Dec 2004 2:26 | Modified: | 14 Dec 2004 8:27 |
| Reporter: | Richard Clarke | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.7 | OS: | Linux (Linux) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[5 Dec 2004 12:10]
Hartmut Holzgraefe
Verified, only happens with HEAP tables. By removing the PRIMARY KEY definitions i get the right results.
[8 Dec 2004 16:01]
Matthias Pigulla
I have a similar case not using HEAP tables. I don't know if it's related of if it is a new bug. Things worked on 3.23 and 4.0 before migrating to 4.1.7. Using a "nested sets" approach, I need to get all rows that do not match rows in another table. We now could use subselects for that (NOT EXISTS...), but this is legacy code I cannot change. CREATE TABLE test ( id smallint(5) unsigned NOT NULL, test varchar(10) NOT NULL default '', l tinyint(3) unsigned NOT NULL default '0', r tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO test VALUES (1, 'hasmatch', 2, 3); INSERT INTO test VALUES (2, 'nomatch', 1, 4); -- Nested sets - find all rows that do not have a node above them select * from test a left join test b on b.l < a.l and b.r > a.r where isnull(b.id) -- Result (as expected): +----+---------+---+---+------+------+------+------+ | id | test | l | r | id | test | l | r | +----+---------+---+---+------+------+------+------+ | 2 | nomatch | 1 | 4 | NULL | NULL | NULL | NULL | +----+---------+---+---+------+------+------+------+ -- Now things break: ALTER TABLE `test` ADD INDEX ( `l` , `r` ) -- Repeat the above query, you'll get Empty set (0.00 sec)
[14 Dec 2004 8:27]
Sergey Petrunya
Tried with today 4.1 tree (tip ChangeSet@1.2168, 2004-12-14 03:36:19+03:00, sergefp@mysql.com) and with earlier tree (tip ChangeSet@1.2122, 2004-12-03 23:24:41+02:00, bell@sanja.is.com.ua) Couldn't reproduce on two x86 boxes.
[15 Dec 2004 15:41]
Matthias Pigulla
That means it's probably fixed in the current CVS snapshots? When will 4.1.8 be released?
Description: CREATE TABLE `search_result` ( `search_id` int(10) unsigned NOT NULL default '0', `server_id` smallint(5) unsigned NOT NULL default '0', `post_id` int(10) unsigned NOT NULL default '0', `thread_id` mediumint(8) unsigned NOT NULL default '0', `forum_id` mediumint(8) unsigned NOT NULL default '0', `posted` datetime default NULL, `score` float default NULL, PRIMARY KEY (`search_id`,`server_id`,`post_id`,`thread_id`) ) TYPE=HEAP CREATE TABLE `alert_result` ( `alert_id` int(10) unsigned NOT NULL default '0', `server_id` smallint(5) unsigned NOT NULL default '0', `post_id` int(10) unsigned NOT NULL default '0', `thread_id` mediumint(8) unsigned NOT NULL default '0', `forum_id` mediumint(8) unsigned NOT NULL default '0', `posted` datetime default NULL, `score` float default NULL, PRIMARY KEY (`alert_id`,`server_id`,`post_id`,`thread_id`) ) TYPE=HEAP select * from search_result sr LEFT JOIN alert_result ar on(sr.search_id=ar.alert_id AND sr.server_id=ar.server_id AND sr.post_id=ar.post_id) where search_id=? and alert_id is null; Query produces no results when mathing rows can clearly be seen when where condition is removed. Query produces expected results with mysql 4.0.20 How to repeat: Create tables above. Use following test data. insert into search_result values(1,1,1,1,1,1,1); insert into alert_result values(1,1,1,1,1,1,1); insert into search_result values(2,1,1,1,1,1,1); mysql> select * from search_result sr LEFT JOIN -> alert_result ar -> on(sr.search_id=ar.alert_id AND sr.server_id=ar.server_id AND sr.post_id=ar.post_id) -> where search_id=2 and alert_id is null; Empty set (0.00 sec) Suggested fix: Make it work like 4.0.20 :)