Bug #20088 Query using ORs and NULLs doesn't return correct number of results

• Submitted: 26 May 2006 8:38
• Modified: 26 May 2006 12:33
• Reporter: Taras Di
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.21-community-nt
• OS: Windows (windows 2000 v5.0 sp4)
• CPU Architecture: Any

[26 May 2006 8:38] Taras Di

Description:
The query below does not return the correct number of results. All 3 results should be returned, but only two of the results are returned.

I've tried a few variations on the query, the results are as follows:

removing the DISTINCT qualifier still produces incorrect results

changing the columns we wish to select to '*' produces correct results

removing 'AND countries.locationId=1' produces correct results

How to repeat:
Create the following table:

DROP TABLE IF EXISTS `people`;
DROP TABLE IF EXISTS `countries`;

create table people
(
  personId int unsigned not null auto_increment primary key,
  place int unsigned
);

create table countries
(
  locationId int unsigned not null auto_increment primary key,
  countryName varchar(50) not null
);

insert into people values(NULL,1);
insert into people values(NULL,2);
insert into people values(NULL,NULL);

insert into countries values(1,'Australia');
insert into countries values(2,'China');

Run the query:

SELECT DISTINCT (personId),place FROM countries, people WHERE (people.place = countries.locationId) OR (people.place IS NULL AND countries.locationId = 1)

I get two results instead of three

+----------+-------+
| personId | place |
+----------+-------+
|        1    |     1   |
|        3    |  NULL |
+----------+-------+

[26 May 2006 8:43] Taras Di

Sorry, wrong severity level initially chosen

[26 May 2006 12:33] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DISTINCT (personId),place FROM countries, people WHERE (people.place =
    -> countries.locationId) OR (people.place IS NULL AND countries.locationId = 1);
+----------+-------+
| personId | place |
+----------+-------+
| 1        | 1     |
| 3        | NULL  |
+----------+-------+
2 rows in set (0.02 sec)

mysql>
-----------------------------------------------------------------------------------
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DISTINCT (personId),place FROM countries, people WHERE (people.place =
    -> countries.locationId) OR (people.place IS NULL AND countries.locationId = 1);
+----------+-------+
| personId | place |
+----------+-------+
|        1 |     1 |
|        2 |     2 |
|        3 |  NULL |
+----------+-------+
3 rows in set (0.00 sec)

mysql>