Description:
Query returns 0 rows for Query Browser, 1 row for mysql command line:
select u.*,p.* from ut u,ut p
where (u.parentid=p.userid OR (u.parentid is null and u.userid=p.userid)) and u.userid=12;
This query returns data with either Query Browser versions 1.1.19 (or 1.1.17), mysql command line, or with JConnector 3.0.9 when connected to MySQL Servers 4.0.12 or 4.0.26.
It also works with mysql command line when connected to MySQL Server 5.0.18 (or 4.1.18).
It does not work with either Query Browser version 1.1.19 or JConnector 3.1.12 when connected to MySQL Server 5.0.18 (or 4.1.18).
I've confirmed this failure on 2 different Windows XP machines.
These install files were used:
mysql-essential-4.1.18-win32.msi
mysql-essential-5.0.18-win32.msi
mysql-query-browser-1.1.19-win.msi
I tried mostly default my.ini files created by the installer.
This bug is posted under the Query Browser and not JConnector, since this is where I did most of the testing.
Sometimes the problem seemed to be dependant on the number of rows in the table.
A workaround query that you'd think would return the same resultset is:
select u.*,p.* from ut u,ut p
where u.parentid is null and u.userid=p.userid and u.userid=12
union
select u.*,p.* from ut u,ut p
where u.parentid=p.userid and u.userid=12;
this always returns data
another workaround is to do an outer join, which returns different data, but is what the original developer should have wrote for our application.
How to repeat:
/* first run the following in mysql command line as root, then repeat the queries in Query Browser */
drop database if exists test2;
create database test2;
use test2;
DROP TABLE IF EXISTS `ut`;
CREATE TABLE `ut` (
`userid` int(11) NOT NULL auto_increment,
`parentid` int(11) default NULL,
PRIMARY KEY (`userid`),
KEY `parentid` (`parentid`)
);
INSERT INTO `ut` VALUES (1,NULL),(2,NULL),(3,2),(4,3),(5,4),(6,2),(7,2),(8,NULL),(9,4),(10,4),(11,4),(12,4),(13,4),(14,4),(15,4),(16,4),(17,4),(18,4),(19,4);
/* returns 0 rows for Query Browser, but 1 row for mysql command line */
select u.*,p.* from ut u,ut p
where (u.parentid=p.userid OR (u.parentid is null and u.userid=p.userid)) and u.userid=12;
/* workaround: always returns 1 row */
select u.*,p.* from ut u,ut p
where u.parentid is null and u.userid=p.userid and u.userid=12
union
select u.*,p.* from ut u,ut p
where u.parentid=p.userid and u.userid=12;
Suggested fix:
See workarond listed earlier, but finding the queries that will fail in a big app is difficult.
