Description:
The less than clause does not appear to function on a partioned table. When you execute a query using a less than (or less than or equals to) clause e.g. epoch <= 1141257601, no results are returned. However an equals to clause e.g. epoch = 1141257600 returns rows and proves there is data that should have been matched under the less than. Note that a between alternative to less than e.g. epoch between 0 and 1141257601 works, but is not a clean work around. Equals and greater than operators seem to work fine.
How to repeat:
mysql> select min(epoch) from RRD_DATA where id=471;
+------------+
| min(epoch) |
+------------+
| 1141257600 |
+------------+
1 row in set
mysql> select * from RRD_DATA where id=471 and epoch = 1141257600;
+-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+
| ID | EPOCH | AVAILABILITY | EBNO | EBNO_AVAILABILITY | REMOTE_EBNO | SIGNAL | TX_LEVEL | TX_POWER | INT_TEMP |
+-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+
| 471 | 1141257600 | 100 | 1180 | 0 | 835 | 0 | 0 | 0 | 0 |
+-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+
1 row in set
mysql> select * from RRD_DATA where id=471 and epoch < 1141257601;
Empty set
mysql> select * from RRD_DATA where id=471 and epoch <= 1141257601;
Empty set
mysql> select * from RRD_DATA where id=471 and epoch between 0 and 1141257601;
+-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+
| ID | EPOCH | AVAILABILITY | EBNO | EBNO_AVAILABILITY | REMOTE_EBNO | SIGNAL | TX_LEVEL | TX_POWER | INT_TEMP |
+-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+
| 471 | 1141257600 | 100 | 1180 | 0 | 835 | 0 | 0 | 0 | 0 |
+-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+
1 row in set
mysql>
Suggested fix:
Not sure, but I suspect this is due to some partition optimisations not working quite right. This looks correct because when using a less than (or equals to), the optimiser returns "Impossible WHERE noticed after reading const tables". It is not impossible, and in fact exists, but this bit of code does not recognise this:
mysql> select count(*) from RRD_DATA where epoch = 1141603200\G
*************************** 1. row ***************************
count(*): 651
1 row in set (0.03 sec)
mysql> explain partitions select * from RRD_DATA where epoch = 1141603200\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: RRD_DATA
partitions: p16
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 145713
Extra: Using where
1 row in set (0.00 sec)
mysql> select count(*) from RRD_DATA where epoch < 1141603200\G
*************************** 1. row ***************************
count(*): 0
1 row in set (0.00 sec)
mysql> select count(*) from RRD_DATA where epoch <= 1141603200\G
*************************** 1. row ***************************
count(*): 0
1 row in set (0.00 sec)
mysql> explain partitions select * from RRD_DATA where epoch <= 1141603200\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)
Description: The less than clause does not appear to function on a partioned table. When you execute a query using a less than (or less than or equals to) clause e.g. epoch <= 1141257601, no results are returned. However an equals to clause e.g. epoch = 1141257600 returns rows and proves there is data that should have been matched under the less than. Note that a between alternative to less than e.g. epoch between 0 and 1141257601 works, but is not a clean work around. Equals and greater than operators seem to work fine. How to repeat: mysql> select min(epoch) from RRD_DATA where id=471; +------------+ | min(epoch) | +------------+ | 1141257600 | +------------+ 1 row in set mysql> select * from RRD_DATA where id=471 and epoch = 1141257600; +-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+ | ID | EPOCH | AVAILABILITY | EBNO | EBNO_AVAILABILITY | REMOTE_EBNO | SIGNAL | TX_LEVEL | TX_POWER | INT_TEMP | +-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+ | 471 | 1141257600 | 100 | 1180 | 0 | 835 | 0 | 0 | 0 | 0 | +-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+ 1 row in set mysql> select * from RRD_DATA where id=471 and epoch < 1141257601; Empty set mysql> select * from RRD_DATA where id=471 and epoch <= 1141257601; Empty set mysql> select * from RRD_DATA where id=471 and epoch between 0 and 1141257601; +-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+ | ID | EPOCH | AVAILABILITY | EBNO | EBNO_AVAILABILITY | REMOTE_EBNO | SIGNAL | TX_LEVEL | TX_POWER | INT_TEMP | +-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+ | 471 | 1141257600 | 100 | 1180 | 0 | 835 | 0 | 0 | 0 | 0 | +-----+------------+--------------+------+-------------------+-------------+--------+----------+----------+----------+ 1 row in set mysql> Suggested fix: Not sure, but I suspect this is due to some partition optimisations not working quite right. This looks correct because when using a less than (or equals to), the optimiser returns "Impossible WHERE noticed after reading const tables". It is not impossible, and in fact exists, but this bit of code does not recognise this: mysql> select count(*) from RRD_DATA where epoch = 1141603200\G *************************** 1. row *************************** count(*): 651 1 row in set (0.03 sec) mysql> explain partitions select * from RRD_DATA where epoch = 1141603200\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: RRD_DATA partitions: p16 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 145713 Extra: Using where 1 row in set (0.00 sec) mysql> select count(*) from RRD_DATA where epoch < 1141603200\G *************************** 1. row *************************** count(*): 0 1 row in set (0.00 sec) mysql> select count(*) from RRD_DATA where epoch <= 1141603200\G *************************** 1. row *************************** count(*): 0 1 row in set (0.00 sec) mysql> explain partitions select * from RRD_DATA where epoch <= 1141603200\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE noticed after reading const tables 1 row in set (0.00 sec)