[MDEV-17613] MIN/MAX Optimization (Select tables optimized away) does not work - Jira

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3.7, 10.3(EOL)
Fix Version/s: 10.6
Component/s: Partitioning, Versioned Tables
Labels:
None
Environment:
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

test_cases.tgz
1 kB
2019-08-21 12:59

Issue Links

causes

MDEV-20347 ASAN heap-use-after-free in String::c_ptr / partition_info::prune_partition_bitmaps

Closed

MDEV-20358 Table corruption or AddressSanitizer: use-after-poison after failed ALTER

Closed

MDEV-20360 Assertion bitmap_is_set(read_partitions) upon updating view of join

Closed

MDEV-20376 Assertion in ha_maria::close() upon complex SELECT from specific data

Closed

is blocked by

MDEV-20250 Implement early partition pruning before statement start

In Progress

relates to

MDEV-18501 Partition pruning doesn't work for historical queries

Closed

MDEV-18727 System Versioning: optimize DML operation

Closed

MDEV-20347 ASAN heap-use-after-free in String::c_ptr / partition_info::prune_partition_bitmaps

Closed

(3 relates to)

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2018-11-05 11:35

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`

Sergei Golubchik added a comment - 2018-11-05 12:19

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.

Sergei Golubchik added a comment - 2018-11-05 12:19 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.

Aleksey Midenkov added a comment - 2018-12-25 20:10

Fix

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

Aleksey Midenkov added a comment - 2018-12-25 20:10 Fix Instead of adding WHERE ROW_END = MAX condition add current partition selection.

Aleksey Midenkov added a comment - 2019-08-21 12:42 - edited

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.

Aleksey Midenkov added a comment - 2019-08-21 12:42 - edited 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.

Aleksey Midenkov added a comment - 2019-08-21 13:01

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

Aleksey Midenkov added a comment - 2019-08-21 13:01 Upon completion of this task all test cases from the caused issues should be included in test.

MariaDB Server

MIN/MAX Optimization (Select tables optimized away) does not work

Details

Description

Attachments

Attachments

Issue Links

Activity

Fix

People

Dates

Git Integration