Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.7, 10.3(EOL)
-
None
-
mariadb 10.3
Description
MIN/MAX Optimization (Select tables optimized away) does not work
MariaDB [holmes]> desc select max(id) from t1;
|
+------+-------------+--------------------------------------+-------+---------------+-----------------------+---------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------------------+-------+---------------+-----------------------+---------+------+----------+--------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | IDX_RAFNS_CREATE_TIME | 6 | NULL | 68320103 | Using where; Using index |
|
+------+-------------+--------------------------------------+-------+---------------+-----------------------+---------+------+----------+--------------------------+
|
1 row in set (0.00 sec)
|
|
|
|
|
MariaDB [holmes]> show create table t1\G;
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '',
|
`user_id` bigint(10) DEFAULT NULL COMMENT ',
|
`risk_assess_apply_id` bigint(10) DEFAULT NULL COMMENT '',
|
`risk_assess_flow_id` bigint(10) DEFAULT NULL COMMENT '',
|
`risk_assess_flow_node_id` bigint(10) DEFAULT NULL COMMENT '',
|
`apply_type` tinyint(1) DEFAULT NULL COMMENT '',
|
`state` tinyint(1) DEFAULT NULL COMMENT '',
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
`update_time` datetime DEFAULT NULL,
|
`name` varchar(30) DEFAULT NULL COMMENT '',
|
`ext_id` varchar(50) DEFAULT NULL COMMENT '',
|
`risk_assess_flow_name` varchar(45) DEFAULT NULL COMMENT '',
|
`risk_assess_flow_code` varchar(45) DEFAULT NULL COMMENT '',
|
`channel_code` varchar(30) DEFAULT NULL COMMENT '',
|
`risk_user_id` bigint(10) DEFAULT NULL COMMENT '',
|
`ext_user_id` varchar(50) DEFAULT NULL COMMENT '',
|
`risk_assess_flow_state_id` bigint(10) DEFAULT NULL COMMENT '',
|
`callback_credititem` text DEFAULT NULL COMMENT '',
|
`his_credititem` text DEFAULT NULL COMMENT '',
|
`ext_sub_credititem` text DEFAULT NULL COMMENT '',
|
`decision_sub_credititem` text DEFAULT NULL COMMENT '',
|
`total_sub_credititem` text DEFAULT NULL COMMENT '',
|
`calc_credititem_condition` varchar(400) DEFAULT NULL COMMENT '',
|
`decision_credititem_conditon` varchar(300) DEFAULT NULL COMMENT '',
|
`decision_process_id` varchar(30) DEFAULT NULL COMMENT '决策流程ID',
|
`decision_result_save_condition` varchar(300) DEFAULT NULL COMMENT '',
|
`result_save_condition` varchar(300) DEFAULT NULL COMMENT '',
|
`is_normal` int(10) DEFAULT NULL COMMENT '',
|
`normal_time` datetime DEFAULT NULL COMMENT '',
|
`is_suspend` int(10) DEFAULT NULL COMMENT '',
|
`suspend_time` datetime DEFAULT NULL COMMENT '',
|
`is_continue` int(10) DEFAULT NULL COMMENT '',
|
`continue_time` datetime DEFAULT NULL COMMENT '',
|
`is_decision_stop` int(10) DEFAULT NULL COMMENT '',
|
`decision_stop_time` datetime DEFAULT NULL COMMENT '',
|
`is_exception` int(10) DEFAULT NULL COMMENT '',
|
`exception_time` datetime DEFAULT NULL COMMENT '',
|
`decision_result_id` bigint(10) DEFAULT NULL COMMENT '',
|
`ts` timestamp(6) GENERATED ALWAYS AS ROW START,
|
`te` timestamp(6) GENERATED ALWAYS AS ROW END,
|
PRIMARY KEY (`id`,`te`),
|
KEY `IDX_RAFNS_UID` (`user_id`),
|
KEY `IDX_RAFNS_RISK_ASSESS_APPLY_ID` (`risk_assess_apply_id`),
|
KEY `IDX_RAFNS_RISK_ASSESS_FLOW_NODE_ID` (`risk_assess_flow_node_id`),
|
KEY `IDX_RAFNS_CREATE_TIME` (`create_time`),
|
KEY `IDX_RAFNS_RUI` (`risk_user_id`),
|
KEY `IDX_RAFNS_RAFSI` (`risk_assess_flow_state_id`),
|
KEY `IDX_RAFNS_RAFNI` (`risk_assess_flow_node_id`),
|
KEY `IDX_RAFNS_EUI` (`ext_user_id`),
|
PERIOD FOR SYSTEM_TIME (`ts`, `te`)
|
) ENGINE=InnoDB AUTO_INCREMENT=42164142 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='t1' WITH SYSTEM VERSIONING
|
PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH
|
(PARTITION `p0` HISTORY ENGINE = InnoDB,
|
PARTITION `p1` HISTORY ENGINE = InnoDB,
|
PARTITION `p2` HISTORY ENGINE = InnoDB,
|
PARTITION `p3` HISTORY ENGINE = InnoDB,
|
PARTITION `p4` HISTORY ENGINE = InnoDB,
|
PARTITION `p5` HISTORY ENGINE = InnoDB,
|
PARTITION `p6` HISTORY ENGINE = InnoDB,
|
PARTITION `pcur` CURRENT ENGINE = InnoDB)
|
1 row in set (0.00 sec)
|
|
|
ERROR: No query specified
|
Attachments
Issue Links
- causes
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- is blocked by
-
MDEV-20250 Implement early partition pruning before statement start
-
- In Progress
-
- relates to
-
-
- Closed
-
-
MDEV-18727
System Versioning: optimize DML operation
-
- Closed
-
-
-
- Closed
-
Activity
We can do a special case of a partition pruning optimization here. Because it's partitioned by system_time, and the search is in the current partition, this max() can still be implemented with one key lookup, making it a const table.
Current easy fix is not possible, because SELECT clones ha_partition and then closes the clone which leads to unclosed transactions in partitions we forcely prune out. We could solve this by closing these partitions (and release from transaction) in change_partitions_to_open() at versioning conditions stage, but this is problematic because table lock is acquired for each partition at open stage and therefore must be released when we close partition handler in change_partitions_to_open(). More details in MDEV-20376.
This should change after MDEV-20250 where mechanism of opening partitions will be improved.
Upon completion of this task all test cases from the caused issues should be included in test.
in 10.3 for table with system versioning:
MariaDB [test]> explain extended select max(id) from t1;+------+-------------+-------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | t1 | index | NULL | IDX_RAFNS_CREATE_TIME | 6 | NULL | 9900 | 100.00 | Using where; Using index |+------+-------------+-------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.001 sec)Note (Code 1003): select max(`test`.`t1`.`id`) AS `max(id)` from `test`.`t1` FOR SYSTEM_TIME ALL where `test`.`t1`.`te` = TIMESTAMP'2038-01-19 04:14:07.999999'without system versioning:
MariaDB [test]> explain extended select max(id) from t2;+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+1 row in set, 1 warning (0.001 sec)Note (Code 1003): select max(`test`.`t2`.`id`) AS `max(id)` from `test`.`t2`