Fri Jun 20 16:24:58 2008: Request 36972 was acted upon.
Transaction: Ticket created by shaund.at.teqcle.co.za
Queue: DBI
Subject: SELECT DISTINCT with ORDER BY and an empty field
Broken in: 1.53
Severity: Normal
Owner: Nobody
Requestors: [EMAIL PROTECTED]
Status: new
Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=36972 >
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.
-- 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 [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-06-20 20:18:00
