Bug #36972 for DBD-mysql: SELECT DISTINCT with ORDER BY and an empty field

Fri Jun 20 16:24:58 2008 shaund [...] teqcle.co.za - Ticket created

Subject:

SELECT DISTINCT with ORDER BY and an empty field

If I run a "SELECT DISTINCT field FROM table ORDER BY field" and there is an empty value (not a NULL) in one of the rows, DBI returns no rows from either a "$sth->fetchrow()" or a "$sth->fetchrow_array()" and also raises no errors. The rows are returned if I do a "$sth->dump_results()" I am running this against MySQL version 14.12. on perl version 5.8.8 on Linux "Linux thumper 2.6.23.15-80.fc7 #1 SMP Sun Feb 10 17:29:10 EST 2008 i686 i686 i386 GNU/Linux" I have attached both my test code and a dump of the MySQL test database. One of the "protocol" fields in the DB is already set to "", and simply running the script against this database should show no output, but altering the field to a value then running the script should show output. I hope this can help you guys.

Subject:

test.pl

Subject:

teqacct.sql

-- MySQL dump 10.11 -- -- Host: localhost Database: teqacct -- ------------------------------------------------------ -- Server version 5.0.45-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `logs` -- DROP TABLE IF EXISTS `logs`; CREATE TABLE `logs` ( `datein` date NOT NULL, `hour` int(2) default NULL, `username` varchar(50) NOT NULL, `ipprot` varchar(4) NOT NULL, `protocol` varchar(20) NOT NULL, `remote` varchar(100) NOT NULL, `direction` varchar(3) NOT NULL, `length` int(10) NOT NULL, KEY `username` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `logs` -- LOCK TABLES `logs` WRITE; /*!40000 ALTER TABLE `logs` DISABLE KEYS */; INSERT INTO `logs` VALUES ('2008-06-20',12,'boo','tcp','http','jdhjhdj','in',1234),('2008-06-20',12,'boo','tcp','http','jdhjhdj','in',1234),('2008-06-20',12,'boo','tcp','https','jdhjhdj','in',1234),('2008-06-20',12,'boo','tcp','','jdhjhdj','in',1234); /*!40000 ALTER TABLE `logs` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2008-06-20 20:18:00

Tue Jun 24 17:13:58 2008 TIMB [...] cpan.org - Queue changed from DBI to DBD-mysql

Tue Oct 07 23:43:12 2008 daniel.frett [...] ccci.org - Correspondence added

From:

daniel.frett [...] ccci.org

On Fri Jun 20 16:24:58 2008, shaund.at.teqcle.co.za wrote: Show quoted text

> If I run a "SELECT DISTINCT field FROM table ORDER BY field" and there > is an empty value (not a NULL) in one of the rows, DBI returns no rows > from either a "$sth->fetchrow()" or a "$sth->fetchrow_array()" and also > raises no errors. The rows are returned if I do a "$sth->dump_results()" > > I am running this against MySQL version 14.12. on perl version 5.8.8 on > Linux "Linux thumper 2.6.23.15-80.fc7 #1 SMP Sun Feb 10 17:29:10 EST > 2008 i686 i686 i386 GNU/Linux" > > I have attached both my test code and a dump of the MySQL test database. > > One of the "protocol" fields in the DB is already set to "", and simply > running the script against this database should show no output, but > altering the field to a value then running the script should show output. > > I hope this can help you guys.

I'm not seeing the test script attached to this bug report so I can't run your example, but my guess is the loop over the data retrieved from the ->fetchrow_array method call is short circuiting because the first row of data returned is evaluating as false. -Daniel

Tue Oct 07 23:43:14 2008 The RT System itself - Status changed from 'new' to 'open'

Mon Oct 21 06:37:46 2013 MICHIELB [...] cpan.org - Correspondence added

Hi, On Tue Oct 07 23:43:12 2008, dfrett wrote: Show quoted text

> On Fri Jun 20 16:24:58 2008, shaund.at.teqcle.co.za wrote:

> > If I run a "SELECT DISTINCT field FROM table ORDER BY field" and there > > is an empty value (not a NULL) in one of the rows, DBI returns no rows > > from either a "$sth->fetchrow()" or a "$sth->fetchrow_array()" and also > > raises no errors. The rows are returned if I do a "$sth->dump_results()" > > > > I am running this against MySQL version 14.12. on perl version 5.8.8 on > > Linux "Linux thumper 2.6.23.15-80.fc7 #1 SMP Sun Feb 10 17:29:10 EST > > 2008 i686 i686 i386 GNU/Linux" > > > > I have attached both my test code and a dump of the MySQL test database. > > > > One of the "protocol" fields in the DB is already set to "", and simply > > running the script against this database should show no output, but > > altering the field to a value then running the script should show output.

> > I'm not seeing the test script attached to this bug report so I can't > run your example, but my guess is the loop over the data retrieved from > the ->fetchrow_array method call is short circuiting because the first > row of data returned is evaluating as false.

The test is missing, as described, and I also support the theory the problem is the code used and not actually a bug in DBD::mysql. If you can demonstrate how to reproduce your bug, please feel free to reopen! -- Mike

Mon Oct 21 06:37:47 2013 MICHIELB [...] cpan.org - Status changed from 'open' to 'rejected'

Mon Oct 21 06:37:48 2013 MICHIELB [...] cpan.org - Taken