Description:
5.0.16: select with too many "and" terms, each containing X like '%%', returns empty set, but removing any one term returns correct result set. This is peculiar since the right-hand operand of the like operator is a wildcard.
using this table:
CREATE TABLE `dbconnect` (
`dbconnect` int(11) NOT NULL auto_increment,
`platform` varchar(20) default NULL,
`version` varchar(20) default NULL,
`promotion` varchar(20) default NULL,
`instance` varchar(20) default NULL,
`hostname` varchar(20) default NULL,
`port` varchar(5) default NULL,
`status` varchar(20) default NULL,
`connected` datetime default NULL,
PRIMARY KEY (`dbconnect`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
and this data:
INSERT INTO `dbconnect` (`dbconnect`,`platform`,`version`,`promotion`,`instance`,`hostname`,`port`,`status`,`connected`) VALUES
(11,'MSSQL','2000 SP3A','DEV','ADVNTT2','10.218.209.61',NULL,NULL,NULL),
(12,'MSSQL','2000 SP2','PROD','COMMLSQL1','10.218.25.58',NULL,NULL,NULL),
(13,'MSSQL','7.0 SP4','PROD','ETRUSTLOGSRV01','10.211.10.36',NULL,NULL,NULL),
(14,'MSSQL','2000 SP2','DEV','IOMSQL20','10.211.12.102',NULL,NULL,NULL),
(15,'MSSQL','2000 SP2','PROD','IOMSQL30','10.211.12.100',NULL,NULL,NULL),
(16,'MSSQL','2000 SP2','DR','IOMSQL40','10.216.3.49',NULL,NULL,NULL),
(17,'MSSQL','6.5 SP4','PROD','USA200HVS1','10.208.132.21',NULL,NULL,NULL),
(18,'MSSQL','2000 SP3A','PREPROD','USA540CMB2-DR','10.218.210.12',NULL,NULL,NULL),
(19,'MSSQL','2000 SP3A','DR','USA551LCCSQL1','10.211.129.126',NULL,NULL,NULL),
(20,'MSSQL','2000 SP3','PROD','USACHICCSSQL','10.218.18.120',NULL,NULL,NULL),
(21,'MSSQL','2000 SP3A','PROD','USACHILCCSQL1','10.218.25.60',NULL,NULL,NULL),
(22,'MSSQL','2000 SP3A','DEV','USADEVLCCSQL1','10.218.24.59',NULL,NULL,NULL),
(23,'MSSQL','2000 SP 3A','DEV','USAG551CCD1-1','10.211.145.39',NULL,NULL,NULL);
and this query:
select dbconnect '*dbconnect',platform,version,instance,promotion,hostname,port,status,connected
from dbconnect
where platform like '%%'
and version like '%%'
and instance like '%%'
and promotion like '%%'
and hostname like '%%'
and port like '%%'
order by platform,version,instance,promotion;
How to repeat:
run the sql statements given above. The erroneous result is "Empty set (0.00 sec)"
Now, remove at least one term in the where clause. For example, remove "and port like '%%'". Mysteriously, the new result is "13 rows in set (0.00 sec)"
Suggested fix:
There should not be a limit on the number of "X like '%%'" terms in a where clause.
