MySQL Bugs: #9860: simple query returning different results

Bug #9860	simple query returning different results
Submitted:	12 Apr 2005 22:41	Modified:	26 May 2005 17:20
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.3-beta	OS:	Solaris (Solaris 9 SPARC, Windows 2000...)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Execute the attached SQL file on 5.0.3.  Both final SELECT statements should produce the same result.  On x86 linux they do, however on Solaris 9 SPARC (32 & 64 bit) and on Windows 2000 they do not.  I have not tested any other OS/ARCH combinations, I just wanted to make sure the problem was not Solaris specific nor 64 bit specific.

The difference is that the first query has:
... and f10 is null ...
The second query does not.  f10 is in fact NULL for
the row so they should still return the same row.

How to repeat:
# connect to 5.0.3-beta
mysql>source sample.sql; /* attached to bug report */

# repeat on Solaris 9 and x86 linux.

the SQL to show the problem.

Attachment: sample.sql (application/octet-stream, text), 1.51 KiB.

I tested this on sunfire280:

SunOS sunfire280 5.9 Generic_117171-15 sun4u sparc SUNW,Sun-Fire-280R

mysql> desc cp_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| f1    | int(11)     | NO   | PRI |         |       |
| f2    | char(2)     | NO   | MUL |         |       |
| f3    | varchar(6)  | NO   |     |         |       |
| f4    | varchar(5)  | YES  |     | NULL    |       |
| f5    | varchar(2)  | YES  |     | NULL    |       |
| f6    | varchar(10) | YES  |     | NULL    |       |
| f7    | char(2)     | YES  |     | NULL    |       |
| f8    | varchar(6)  | YES  |     | NULL    |       |
| f9    | varchar(6)  | YES  |     | NULL    |       |
| f10   | char(2)     | YES  |     | NULL    |       |
| f11   | varchar(10) | YES  |     | NULL    |       |
| f12   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
12 rows in set (0.02 sec)

mysql> insert into
    ->   cp_test
    -> values
    -> (25490,'US','99573','MXWEL','R','60',  null,'RR',    null,null,'BOX','157'),
    -> (20321,'US','69101','JCKSN','S','2320',null,'BRCHWD','RD',null,null, null),
    -> (20331,'US','82221','NSHLS','R','77',  null,'RR',    null,null,'BOX','356')
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select f1
    ->   from cp_test
    ->  where f2 = 'US'
    ->    and f3 = '99573'
    ->    and f4 = 'MXWEL'
    ->    and f5 = 'R'
    ->    and f6 = '60'
    ->    and f7 is null
    ->    and f8 = 'RR'
    ->    and f9 is null
    ->    and f10 is null
    ->    and f11 = 'BOX'
    ->    and f12 = 157
    -> ;
+-------+
| f1    |
+-------+
| 25490 |
+-------+
1 row in set (0.01 sec)

mysql> select f1
    ->   from cp_test
    ->  where f2 = 'US'
    ->    and f3 = '99573'
    ->    and f4 = 'MXWEL'
    ->    and f5 = 'R'
    ->    and f6 = '60'
    ->    and f7 is null
    ->    and f8 = 'RR'
    ->    and f9 is null
    -> --   and f10 is null 
    ->    and f11 = 'BOX'
    ->    and f12 = 157
    -> ;
+-------+
| f1    |
+-------+
| 25490 |
+-------+
1 row in set (0.00 sec)

This seems to be fixed, unless the difference in platform is enough to be the issue. I have also had the same successful results on Mac OSX.

Success on windows XP as well - with latest 5.0.6 build from buildxp1

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:

Could not get the failure to occur in latest 5.0.6 in both sunfire280 or my own XP platform.