Result of select is broken by running another select

[Rob Desbois]

Hi all,

I have an extremely strange problem here. A particular part of a project I work on has two
SQL queries -- both selects, both join similar tables, and neither modifies anything.

Running the first one works until I run the second. After that, running the first returns
no results (it previously returned 1 row). The only way to make the first run again is to
comment out a particular part of it (which is also in the other query) and run it.
Uncommenting that part then makes it run as usual until the second query is run again.

I can provide more information on the actual data and tables involved if required but am
leaving it out for now as I have a feeling this may be some sort of caching issue
(because of the repeated part of query being key to making it work again). If anyone
feels this information may assist in diagnosing the problem please let me know.

The first query (the one which breaks) is this:
   SELECT *
   FROM privilege Paccess
   JOIN privilege Phome
      ON Phome.user_id = '26'
      AND Phome.type = 'network-member'
-- AND (Phome.network_id = Paccess.network_id OR Paccess.network_id IS NULL)
   WHERE Paccess.user_id = '4'
      AND Paccess.type = 'user-manage';

The commented out line is the one which must be commented to make it run again.
The second query (the one which makes the first one break) is this:
   SELECT U.id, 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 = '4'
      AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL)
      AND Paccess.type = 'user-manage';

You can clearly see the second to last line is repeated from the first.

This is running on MySQL Ver14.12 Distrib 5.0.22 for Win32 on a Windows 2000 box. I have
set up a Windows XP box with same version and duplicated the database content onto it but
cannot replicate the problem there.

Please help, this is driving me mad!
--rob


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________