Bug #2419 ORDER BY ignore rows
Submitted: 16 Jan 2004 1:32 Modified: 3 Feb 2004 5:47
Reporter: Javier Tacón Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.1 & 5.0.0 OS:Linux (Linux Red Hat 7.3)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[16 Jan 2004 1:32] Javier Tacón
Description:

When you make a select like ..

select * from tabletest where b='' or b is null;

.. In a table that b column have an index, the result is OK:

+-------+------+
| a     | b    |
+-------+------+
| Row 1 |      |
| Row 2 |      |
| Row 3 | NULL |
| Row 4 | NULL |
+-------+------+

But, if you want to make an ORDER BY, the query ignores the rows with NULL value in b column:

+-------+------+
| a     | b    |
+-------+------+
| Row 1 |      |
| Row 2 |      |
+-------+------+

Some tests I've done:

- I tried with char,varchar and int column types, it fails too.
- If you make a fulltext index, works fine.
- If you make an index(a,b), works fine.

How to repeat:

create table tabletest ( a char(50), b char(50), index(b) );
insert into tabletest values ('R1',''), ('R2',''), ('R3',NULL), ('R4',NULL);
select count(*) from tabletest where b='' or b is null;
select * from tabletest where b='' or b is null;
select count(*) from tabletest where (b='' or b is null) order by a;
select * from tabletest where b='' or b is null order by a;
drop table tabletest;
[16 Jan 2004 8:03] Dean Ellis
Verified with 4.1.2.  Thank you for the report.
[30 Jan 2004 8:50] MySQL Verification Team
An additional test case ...

drop table if exists t1;
CREATE TABLE t1 (  Id INTEGER NOT NULL,  Id2 INTEGER NOT NULL,  Val FLOAT NOT NULL,  Id3 INTEGER NOT NULL,  KEY Key2 (Id2, Val),  KEY Key3 (Id3));
INSERT INTO t1 VALUES(1,3,-12,4);
INSERT INTO t1 VALUES(2,2,-27.5,4);
INSERT INTO t1 VALUES(3,2,18.4,3);

SELECT Id3 FROM t1 WHERE Id2=2 AND Val BETWEEN -30 AND 20;
SELECT Id3 FROM t1 WHERE Id2=2 AND Val BETWEEN -30 AND 20 ORDER BY Id3;
drop table if exists t1;
[3 Feb 2004 5:47] Ramil Kalimullin
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