Description:
Queried with partitions,returned result is error
the returned result should not be empty for the where condition is true
mysql> CREATE TABLE `tt1` (
e_col` (`time_col` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`bigint_col`)
(PARTITION p0,
PARTITION p1,
PARTITION p2) */; -> `bigint_col` bigint NOT NULL,
-> `date_col` date DEFAULT NULL,
-> `time_col` time(6) DEFAULT NULL,
-> `binary_col` binary(11) DEFAULT NULL,
-> `store_binary_col` binary(11) GENERATED ALWAYS AS (upper(`binary_col`)) STORED,
-> KEY `ndx_time_col` (`time_col` DESC)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-> /*!50100 PARTITION BY HASH (`bigint_col`)
-> (PARTITION p0,
-> PARTITION p1,
-> PARTITION p2) */;
Query OK, 0 rows affected (0.07 sec)
mysql> insert ignore into tt1(bigint_col,date_col,time_col,binary_col) values(9223372036854775807,'9999-12-31','00:00:00.000000','0x6363633200000000000000');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT A.time_col, A.date_col ,A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col FROM tt1 A GROUP BY 1, 2;
+-----------------+------------+-----------------------------------------------------------------------------------------+
| time_col | date_col | A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col |
+-----------------+------------+-----------------------------------------------------------------------------------------+
| 00:00:00.000000 | 9999-12-31 | 1 |
+-----------------+------------+-----------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT A.time_col, A.date_col FROM tt1 A where A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col GROUP BY 1, 2;
Empty set (0.00 sec)
How to repeat:
1.create table and insert data
CREATE TABLE `tt1` (
`bigint_col` bigint NOT NULL,
`date_col` date DEFAULT NULL,
`time_col` time(6) DEFAULT NULL,
`binary_col` binary(11) DEFAULT NULL,
`store_binary_col` binary(11) GENERATED ALWAYS AS (upper(`binary_col`)) STORED,
KEY `ndx_time_col` (`time_col` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`bigint_col`)
(PARTITION p0,
PARTITION p1,
PARTITION p2) */;
insert ignore into tt1(bigint_col,date_col,time_col,binary_col) values(9223372036854775807,'9999-12-31','00:00:00.000000','0x6363633200000000000000');
2.execute the query
SELECT A.time_col, A.date_col FROM tt1 A where A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col GROUP BY 1, 2;
Description: Queried with partitions,returned result is error the returned result should not be empty for the where condition is true mysql> CREATE TABLE `tt1` ( e_col` (`time_col` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`bigint_col`) (PARTITION p0, PARTITION p1, PARTITION p2) */; -> `bigint_col` bigint NOT NULL, -> `date_col` date DEFAULT NULL, -> `time_col` time(6) DEFAULT NULL, -> `binary_col` binary(11) DEFAULT NULL, -> `store_binary_col` binary(11) GENERATED ALWAYS AS (upper(`binary_col`)) STORED, -> KEY `ndx_time_col` (`time_col` DESC) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci -> /*!50100 PARTITION BY HASH (`bigint_col`) -> (PARTITION p0, -> PARTITION p1, -> PARTITION p2) */; Query OK, 0 rows affected (0.07 sec) mysql> insert ignore into tt1(bigint_col,date_col,time_col,binary_col) values(9223372036854775807,'9999-12-31','00:00:00.000000','0x6363633200000000000000'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT A.time_col, A.date_col ,A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col FROM tt1 A GROUP BY 1, 2; +-----------------+------------+-----------------------------------------------------------------------------------------+ | time_col | date_col | A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col | +-----------------+------------+-----------------------------------------------------------------------------------------+ | 00:00:00.000000 | 9999-12-31 | 1 | +-----------------+------------+-----------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT A.time_col, A.date_col FROM tt1 A where A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col GROUP BY 1, 2; Empty set (0.00 sec) How to repeat: 1.create table and insert data CREATE TABLE `tt1` ( `bigint_col` bigint NOT NULL, `date_col` date DEFAULT NULL, `time_col` time(6) DEFAULT NULL, `binary_col` binary(11) DEFAULT NULL, `store_binary_col` binary(11) GENERATED ALWAYS AS (upper(`binary_col`)) STORED, KEY `ndx_time_col` (`time_col` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`bigint_col`) (PARTITION p0, PARTITION p1, PARTITION p2) */; insert ignore into tt1(bigint_col,date_col,time_col,binary_col) values(9223372036854775807,'9999-12-31','00:00:00.000000','0x6363633200000000000000'); 2.execute the query SELECT A.time_col, A.date_col FROM tt1 A where A.time_col <> '03:59:59.000000' AND A.time_col BETWEEN '08:59:59.000000' AND A.date_col GROUP BY 1, 2;