[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

hiller1 created issue - 2018-11-05 08:05

Alice Sherepa made changes - 2018-11-05 10:44

Field	Original Value	New Value
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 \| {color:red}Using where; Using index{color} \| +------+-------------+--------------------------------------+-------+---------------+-----------------------+---------+------+----------+--------------------------+ 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	MIN/MAX Optimization (Select tables optimized away) does not work {noformat} 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 \| {color:red}Using where; Using index{color} \| +------+-------------+--------------------------------------+-------+---------------+-----------------------+---------+------+----------+--------------------------+ 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 {noformat}

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`

Alice Sherepa made changes - 2018-11-05 11:47

Component/s

System versioning [ 14303 ]

Sergei Golubchik made changes - 2018-11-05 11:50

Description

MIN/MAX Optimization (Select tables optimized away) does not work
{noformat}
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 | {color:red}Using where; Using index{color} |
+------+-------------+--------------------------------------+-------+---------------+-----------------------+---------+------+----------+--------------------------+
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
{noformat}

MIN/MAX Optimization (Select tables optimized away) does not work
{noformat}
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
{noformat}

Alice Sherepa made changes - 2018-11-05 12:15

Affects Version/s

10.3 [ 22126 ]

Alice Sherepa made changes - 2018-11-05 12:15

Fix Version/s

10.3 [ 22126 ]

Alice Sherepa made changes - 2018-11-05 12:15

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2018-11-05 12:16

Assignee

Aleksey Midenok [ midenok ]

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 made changes - 2018-12-25 08:35

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

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 made changes - 2018-12-27 12:58

Assignee	Aleksey Midenok [ midenok ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2019-03-29 12:04

Fix Version/s

10.4 [ 22408 ]

Sergei Golubchik made changes - 2019-07-05 10:27

Link

This issue blocks ~~MDEV-18727~~ [ ~~MDEV-18727~~ ]

Aleksey Midenkov made changes - 2019-08-11 11:40

Assignee

Sergei Golubchik [ serg ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2019-08-11 11:40

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Aleksey Midenkov made changes - 2019-08-11 11:41

Component/s

Partitioning [ 10802 ]

Aleksey Midenkov made changes - 2019-08-11 11:42

issue.field.resolutiondate

2019-08-11 11:42:05.0

2019-08-11 11:42:05.382

Aleksey Midenkov made changes - 2019-08-11 11:42

Fix Version/s		10.3.18 [ 23719 ]
Fix Version/s		10.4.8 [ 23721 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Aleksey Midenkov made changes - 2019-08-14 13:45

Link

This issue relates to ~~MDEV-20347~~ [ ~~MDEV-20347~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:16

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2019-08-21 12:16

Fix Version/s		10.5 [ 23123 ]
Fix Version/s	10.3.18 [ 23719 ]
Fix Version/s	10.4.8 [ 23721 ]

Aleksey Midenkov made changes - 2019-08-21 12:17

Link

This issue blocks ~~MDEV-18727~~ [ ~~MDEV-18727~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:18

Link

This issue relates to ~~MDEV-18727~~ [ ~~MDEV-18727~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:20

Link

This issue relates to ~~MDEV-18501~~ [ ~~MDEV-18501~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:23

Link

This issue causes ~~MDEV-20376~~ [ ~~MDEV-20376~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:24

Link

This issue causes ~~MDEV-20360~~ [ ~~MDEV-20360~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:24

Link

This issue causes ~~MDEV-20347~~ [ ~~MDEV-20347~~ ]

Aleksey Midenkov made changes - 2019-08-21 12:25

Link

This issue causes ~~MDEV-20358~~ [ ~~MDEV-20358~~ ]

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 made changes - 2019-08-21 12:43

Link

This issue is blocked by MDEV-20250 [ MDEV-20250 ]

Aleksey Midenkov made changes - 2019-08-21 12:59

Attachment

test_cases.tgz [ 48692 ]

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.

Aleksey Midenkov made changes - 2019-08-21 13:25

Comment

[ A comment with security level 'Developers' was removed. ]

Aleksey Midenkov made changes - 2019-08-21 15:05

Link

This issue relates to MDEV-20250 [ MDEV-20250 ]

Aleksey Midenkov made changes - 2019-08-21 15:05

Link

This issue is blocked by MDEV-20250 [ MDEV-20250 ]

Aleksey Midenkov made changes - 2020-05-29 08:18

Link

This issue is blocked by MDEV-20250 [ MDEV-20250 ]

Aleksey Midenkov made changes - 2020-05-29 08:19

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.5 [ 23123 ]

Sergei Golubchik made changes - 2020-05-29 08:26

Fix Version/s		10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]

Sergei Golubchik made changes - 2020-05-29 08:27

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.5 [ 23123 ]

Sergei Golubchik made changes - 2020-08-16 19:20

Rank

Ranked higher

Sergei Golubchik made changes - 2021-10-26 17:57

Link

This issue relates to MDEV-20250 [ MDEV-20250 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 90420 ]

MariaDB v4 [ 143564 ]

People

Assignee:: Aleksey Midenkov

Reporter:: hiller1

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2018-11-05 08:05

Updated:: 2024-11-26 09:31

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

Fix

People

Dates

Git Integration