|
Posted by taras.di on April 18, 2006, 9:05 am
Please log in for more thread options
Hi everyone,
I've come across an extremely strange problem. The exact same query in
both mysql command line client, and mysql query browser gives entirely
different results. I was hoping someone out there could shed some
light.
Ok, the query (I've stripped it bare, the real query is a bit more
complex)...
Each person in the people table has an associated 'place', which is an
integer that maps onto a suburb in the suburbs table. The 'place' CAN
be NULL.
so, for all intensive purposes, the schema is roughly:
people:
personId int unsigned not null auto_increment
place int
suburbs
locationId int unsigned not null auto_increment
suburbName varchar(50) not null
QUERY
------------------------------------------------------
SELECT DISTINCT (personId),place
FROM suburbs, people
WHERE
(
people.place = suburbs.locationId //mapping the place integer
to a suburb name
)
OR
(
people.place IS NULL
AND suburbs.locationId = 1
)
Why do I set suburbs.locationId = 1? Because we are doing a cartesian
join, we don't want an entire suburbs table for each entry in the
people table where the place is null - ie: it ensures that each line in
the person table is associated with exactly one line in the suburbs
table. Since we don't care about the suburb name when the place is
null, we put in a mock value.
In MySQL query browser, I get 21 results. When I copy and paste the
query into a command line instance of MySQL, I get the (required)
result of 95.
If I delete the line 'AND suburbs.locationId = 1', I get the required
result of 95 in both query browser and mysql command line.
Anyone have any ideas?
Cheers
Taras
Version info:
MySQL version: 5.0.19-nt via TCP/IP
MySQL Query Browser version: 1.1.20
| 
XML Sitemap