Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17613

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.3.7
    • Fix Version/s: 10.3
    • Component/s: System versioning
    • 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

          Activity

            People

            • Assignee:
              midenok Aleksey Midenok
              Reporter:
              hiller1 hiller1
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: