Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
12.3, 12.3.2
-
Unexpected results
-
SELECT operations on PARTITIONED tables sometimes return fewer rows than expected.
-
Q4/2026 Server Maintenance
Description
I have a table:
CREATE TABLE `report` (
|
`sequenceNumber` bigint(20) NOT NULL AUTO_INCREMENT,
|
`senderInfoId` int(11) DEFAULT NULL,
|
`receiverInfoId` int(11) DEFAULT NULL,
|
`frequency` bigint(20) DEFAULT NULL,
|
`mode` varchar(8) DEFAULT NULL,
|
`source` tinyint(4) NOT NULL DEFAULT 1,
|
`senderStatus` tinyint(4) NOT NULL DEFAULT 0,
|
`sNR` tinyint(4) DEFAULT NULL,
|
`iMD` tinyint(4) DEFAULT NULL,
|
`flowStartSeconds` int(10) unsigned NOT NULL DEFAULT 0,
|
`ipOriginId` int(11) DEFAULT NULL,
|
`senderMobileLocator` varchar(10) DEFAULT NULL,
|
`senderDxcc` int(11) DEFAULT NULL,
|
`receiverDxcc` int(11) DEFAULT NULL,
|
`band` varchar(8) DEFAULT NULL,
|
PRIMARY KEY (`sequenceNumber`,`flowStartSeconds`),
|
UNIQUE KEY `source_rx_tx_fss` (`source`,`receiverInfoId`,`senderInfoId`,`flowStartSeconds`),
|
KEY `calltime` (`flowStartSeconds`),
|
KEY `senderSeconds` (`senderInfoId`,`flowStartSeconds`),
|
KEY `receiverSeconds` (`receiverInfoId`,`flowStartSeconds`),
|
KEY `mode_fss` (`mode`,`flowStartSeconds`),
|
KEY `rx_seq` (`receiverInfoId`,`sequenceNumber`),
|
KEY `tx_seq` (`senderInfoId`,`sequenceNumber`),
|
KEY `band_fss` (`band`,`flowStartSeconds`),
|
KEY `sdx_fss` (`senderDxcc`,`flowStartSeconds`),
|
KEY `rDx_fss` (`receiverDxcc`,`flowStartSeconds`),
|
KEY `receiverInfoId_index` (`receiverInfoId`)
|
) ENGINE=InnoDB AUTO_INCREMENT=70930128192 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_SAMPLE_PAGES=100
|
PARTITION BY RANGE (`flowStartSeconds`)
|
(PARTITION `p20260709` VALUES LESS THAN (1783555200) ENGINE = InnoDB,
|
PARTITION `p20260710` VALUES LESS THAN (1783641600) ENGINE = InnoDB,
|
PARTITION `p20260711` VALUES LESS THAN (1783728000) ENGINE = InnoDB,
|
PARTITION `p20260712` VALUES LESS THAN (1783814400) ENGINE = InnoDB,
|
PARTITION `p20260713` VALUES LESS THAN (1783900800) ENGINE = InnoDB,
|
PARTITION `p20260714` VALUES LESS THAN (1783987200) ENGINE = InnoDB,
|
PARTITION `p20260715` VALUES LESS THAN (1784073600) ENGINE = InnoDB,
|
PARTITION `p20260716` VALUES LESS THAN (1784160000) ENGINE = InnoDB,
|
PARTITION `p20260717` VALUES LESS THAN (1784246400) ENGINE = InnoDB,
|
PARTITION `p20260718` VALUES LESS THAN (1784332800) ENGINE = InnoDB,
|
PARTITION `p20260719` VALUES LESS THAN (1784419200) ENGINE = InnoDB,
|
PARTITION `p20260722` VALUES LESS THAN (1784678400) ENGINE = InnoDB,
|
PARTITION `p20260723` VALUES LESS THAN (1784764800) ENGINE = InnoDB,
|
PARTITION `p20260724` VALUES LESS THAN (1784851200) ENGINE = InnoDB,
|
PARTITION `p20260725` VALUES LESS THAN (1784937600) ENGINE = InnoDB,
|
PARTITION `p20260726` VALUES LESS THAN (1785024000) ENGINE = InnoDB)
|
with data being fed into it with (I think the only relevant column) the `flowStartSeconds` field gets records that are within a few seconds of real time.
These two very similar SELECTs demonstrate the problem:
MariaDB [ipfix]> SELECT flowStartSeconds FROM report WHERE receiverInfoId = 136257050 ORDER BY flowStartSeconds + 0 DESC LIMIT 10;
|
+------------------+
|
| flowStartSeconds |
|
+------------------+
|
| 1784682465 |
|
| 1784682465 |
|
| 1784682465 |
|
| 1784682451 |
|
| 1784682451 |
|
| 1784682451 |
|
| 1784682450 |
|
| 1784682435 |
|
| 1784682420 |
|
| 1784682420 |
|
+------------------+
|
10 rows in set (0.018 sec)
|
 |
MariaDB [ipfix]> SELECT flowStartSeconds FROM report WHERE receiverInfoId = 136257050 ORDER BY flowStartSeconds DESC LIMIT 10;
|
+------------------+
|
| flowStartSeconds |
|
+------------------+
|
| 1784682465 |
|
| 1784678385 |
|
| 1784419185 |
|
| 1784332785 |
|
| 1784246385 |
|
+------------------+
|
5 rows in set (0.001 sec)
|
You observe that both queries should return the same data and yet they don't.
In my debugging, I wondered about corrupted indexes so I ran optimize:
MariaDB [ipfix]> optimize table report;
|
+--------------+----------+----------+-------------------------------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------------+----------+----------+-------------------------------------------------------------------+
|
| ipfix.report | optimize | note | Table does not support optimize, doing recreate + analyze instead |
|
| ipfix.report | optimize | status | OK |
|
+--------------+----------+----------+-------------------------------------------------------------------+
|
2 rows in set (40 min 48.721 sec)
|
But it didn't help.
Full version: 12.3.2-MariaDB-ubu2404 mariadb.org binary distribution
I note that this works on 10.2.44-MariaDB MariaDB Server
I'm trying to migrate forward to a current software version.
Note:
MariaDB [ipfix]> SELECT flowStartSeconds, from_unixtime(flowStartSeconds) FROM report WHERE receiverInfoId = 136257050 ORDER BY flowStartSeconds DESC LIMIT 10;
|
+------------------+---------------------------------+
|
| flowStartSeconds | from_unixtime(flowStartSeconds) |
|
+------------------+---------------------------------+
|
| 1784683072 | 2026-07-22 01:17:52 |
|
| 1784678385 | 2026-07-21 23:59:45 |
|
| 1784419185 | 2026-07-18 23:59:45 |
|
| 1784332785 | 2026-07-17 23:59:45 |
|
| 1784246385 | 2026-07-16 23:59:45 |
|
+------------------+---------------------------------+
|
5 rows in set (0.001 sec)
|
It appears that it produces 1 row from each partition (the partition boundaries are at Midnight).
Attachments
Issue Links
- is caused by
-
MDEV-37330 Ordered scans over PARTITION BY RANGE should not use priority queue
-
- Closed
-
