Description:
I have two select statements which have a common part in a join:
(Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL)
I get inconsistent results wherever I run this. In the database where this was found, running the second prevents the first from returning the expected results. The only way to get it working is to comment out the common part mentioned, run it, uncomment and rerun. This happens every time.
I copied the table structure and data to another database running on the same server. This time, running the second query will break the first but only once -- running it again returns the expected results.
I have duplicated this setup (same version, same my.ini, same structure & data) on a completely separate machine but cannot replicate.
I know I am several versions behind but given that I cannot even replicate it even on the same server but different DB, I have reported it in case its non-appearance in 5.0.27 is not because it's been fixed.
I apologise if this *has* already been fixed but if somebody could point me towards the relevant bug report (I couldn't find one) I'd be grateful.
How to repeat:
CREATE TABLE `user` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(32) collate latin1_general_cs NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
INSERT INTO user VALUES (1, 'charles'), (2, 'bob');
CREATE TABLE `privilege` (
`user_id` mediumint(8) unsigned NOT NULL,
`network_id` mediumint(8) unsigned default NULL,
`type` varchar(32) collate latin1_general_cs NOT NULL,
UNIQUE KEY `user_id` (`user_id`,`network_id`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
INSERT INTO privilege VALUES (1, 1, 'user-manage'), (2, 1, 'network-member');
Run this query to see the expected results:
SELECT *
FROM privilege Paccess
JOIN privilege Phome
ON Phome.user_id = '2'
AND Phome.type = 'network-member'
AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL)
WHERE Paccess.user_id = '1'
AND Paccess.type = 'user-manage';
Run this query:
SELECT U.name
FROM user U
-- find each user's home network
JOIN privilege Phome
ON Phome.user_id = U.id
AND Phome.type = 'network-member'
-- current user's user-management access rights
LEFT JOIN privilege Paccess
ON Paccess.user_id = '2'
AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL)
AND Paccess.type = 'user-manage';
If you now rerun the first query, you'll see that it doesn't return any results this time. Run it again and it works as expected.
If you change the common part of the two queries from:
AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL)
to:
AND (Paccess.network_id IS NULL OR Paccess.network_id = Phome.network_id)
you can see that the second query no longer breaks the first.
Description: I have two select statements which have a common part in a join: (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL) I get inconsistent results wherever I run this. In the database where this was found, running the second prevents the first from returning the expected results. The only way to get it working is to comment out the common part mentioned, run it, uncomment and rerun. This happens every time. I copied the table structure and data to another database running on the same server. This time, running the second query will break the first but only once -- running it again returns the expected results. I have duplicated this setup (same version, same my.ini, same structure & data) on a completely separate machine but cannot replicate. I know I am several versions behind but given that I cannot even replicate it even on the same server but different DB, I have reported it in case its non-appearance in 5.0.27 is not because it's been fixed. I apologise if this *has* already been fixed but if somebody could point me towards the relevant bug report (I couldn't find one) I'd be grateful. How to repeat: CREATE TABLE `user` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(32) collate latin1_general_cs NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs; INSERT INTO user VALUES (1, 'charles'), (2, 'bob'); CREATE TABLE `privilege` ( `user_id` mediumint(8) unsigned NOT NULL, `network_id` mediumint(8) unsigned default NULL, `type` varchar(32) collate latin1_general_cs NOT NULL, UNIQUE KEY `user_id` (`user_id`,`network_id`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs; INSERT INTO privilege VALUES (1, 1, 'user-manage'), (2, 1, 'network-member'); Run this query to see the expected results: SELECT * FROM privilege Paccess JOIN privilege Phome ON Phome.user_id = '2' AND Phome.type = 'network-member' AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL) WHERE Paccess.user_id = '1' AND Paccess.type = 'user-manage'; Run this query: SELECT U.name FROM user U -- find each user's home network JOIN privilege Phome ON Phome.user_id = U.id AND Phome.type = 'network-member' -- current user's user-management access rights LEFT JOIN privilege Paccess ON Paccess.user_id = '2' AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL) AND Paccess.type = 'user-manage'; If you now rerun the first query, you'll see that it doesn't return any results this time. Run it again and it works as expected. If you change the common part of the two queries from: AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL) to: AND (Paccess.network_id IS NULL OR Paccess.network_id = Phome.network_id) you can see that the second query no longer breaks the first.