[MDEV-17613] MIN/MAX Optimization (Select tables optimized away) does not work Created: 2018-11-05  Updated: 2021-10-26

Status: Stalled
Project: MariaDB Server
Component/s: Partitioning, Versioned Tables
Affects Version/s: 10.3.7, 10.3
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: hiller1 Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 1
Labels: None
Environment:

mariadb 10.3

Attachments: File test_cases.tgz    
Issue Links:
Blocks
is blocked by MDEV-20250 do not open partitions excluded by au... Stalled
Problem/Incident
causes MDEV-20347 ASAN heap-use-after-free in String::c... Closed
causes MDEV-20358 Table corruption or AddressSanitizer:... Closed
causes MDEV-20360 Assertion bitmap_is_set(read_partitio... Closed
causes MDEV-20376 Assertion in ha_maria::close() upon c... Closed
Relates
relates to MDEV-18501 Partition pruning doesn't work for hi... Closed
relates to MDEV-18727 System Versioning: optimize DML opera... Closed
relates to MDEV-20347 ASAN heap-use-after-free in String::c... Closed

 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



 Comments   
Comment by Alice Sherepa [ 2018-11-05 ]

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`

Comment by Sergei Golubchik [ 2018-11-05 ]

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.

Comment by Aleksey Midenkov [ 2018-12-25 ]

Fix

Instead of adding WHERE ROW_END = MAX condition add current partition selection.

Comment by Aleksey Midenkov [ 2019-08-21 ]

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.

Comment by Aleksey Midenkov [ 2019-08-21 ]

Upon completion of this task all test cases from the caused issues should be included in test.

Generated at Thu Feb 08 08:37:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.