Bug #15040 ORDER BY .. DESC missing rows with BDB

• Submitted: 18 Nov 2005 1:14
• Modified: 21 Aug 2006 22:19
• Reporter: Michael Kirkham
• Status: Unsupported
• Category: MySQL Server: BDB
• Severity: S2 (Serious)
• Version: 4.1.13/5.0 BK
• OS: FreeBSD (FreeBSD/Linux)
• CPU Architecture: Any

[18 Nov 2005 1:14] Michael Kirkham

Description:
This seems to be similar to #12941 and #12661, but for BDB tables and earlier version than 5.x.

The following query returns zero rows:

SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
AND OEMID='0000' ORDER BY Serial DESC LIMIT 1;

While the following returns 1 row:

SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
AND OEMID='0000' LIMIT 1;

Switching to MyISAM or InnoDB both queries return 1 row.

How to repeat:
-- MySQL dump 10.9
--
-- Host: localhost    Database: users
-- ------------------------------------------------------
-- Server version	4.1.13-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 */;
/*!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 `licenses`
--

DROP TABLE IF EXISTS `licenses`;
CREATE TABLE `licenses` (
  `PurchaserID` int(10) unsigned NOT NULL default '0',
  `OwnerID` int(10) unsigned NOT NULL default '0',
  `ProductID` int(6) unsigned zerofill NOT NULL default '000000',
  `PlatformID` int(2) unsigned zerofill NOT NULL default '00',
  `OEMID` int(4) unsigned zerofill NOT NULL default '0000',
  `Serial` int(8) unsigned zerofill NOT NULL default '00000000',
  `Purchased` date NOT NULL default '0000-00-00',
  `InvoiceID` int(6) unsigned zerofill default NULL,
  `Expires` date NOT NULL default '0000-00-00',
  `HostID` varchar(20) default NULL,
  `AltHostID` varchar(20) default NULL,
  `Flags` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ProductID`,`PlatformID`,`OEMID`,`Serial`),
  KEY `PurchaserID` (`PurchaserID`),
  KEY `OwnerID` (`OwnerID`),
  KEY `InvoiceID` (`InvoiceID`)
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `licenses`
--

/*!40000 ALTER TABLE `licenses` DISABLE KEYS */;
LOCK TABLES `licenses` WRITE;
INSERT INTO `licenses` VALUES (161,161,100301,01,0000,00000001,'2005-08-15',NULL,'2006-08-30','005056C00008',NULL,0),(170,170,100301,05,0000,00000001,'2005-08-29',103027,'2006-08-29','000A95B481E2','000D93396148',0);
UNLOCK TABLES;
/*!40000 ALTER TABLE `licenses` ENABLE KEYS */;

/*!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 */;

[18 Nov 2005 11:34] MySQL Verification Team

Thank you for the bug report.

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.17-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
    -> AND OEMID='0000' ORDER BY Serial DESC LIMIT 1;
Empty set (0.00 sec)

mysql> SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
    -> AND OEMID='0000' LIMIT 1;
+----------+
| Serial   |
+----------+
| 00000001 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.16-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
    -> AND OEMID='0000' ORDER BY Serial DESC LIMIT 1;
Empty set (0.17 sec)

mysql> SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
    -> AND OEMID='0000' LIMIT 1;
+----------+
| Serial   |
+----------+
| 00000001 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.26-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
    -> AND OEMID='0000' ORDER BY Serial DESC LIMIT 1;
+----------+
| Serial   |
+----------+
| 00000001 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT Serial FROM `licenses` WHERE ProductID='100301' AND PlatformID='05'
    -> AND OEMID='0000' LIMIT 1;
+----------+
| Serial   |
+----------+
| 00000001 |
+----------+
1 row in set (0.00 sec)