Different results in mysql & mysql query browser?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

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

Ok, the query (I've stripped it bare, the real query is a bit more

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:

personId int unsigned not null auto_increment
place int

locationId int unsigned not null auto_increment
suburbName varchar(50) not null

SELECT DISTINCT (personId),place
FROM suburbs, people
        people.place = suburbs.locationId //mapping the place integer
to a suburb name
        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?



Version info:

MySQL version: 5.0.19-nt via TCP/IP
MySQL Query Browser version: 1.1.20

Site Timeline