Skip to content

Check date type to avoid scan all partitions #4755

@xinghuayu007

Description

@xinghuayu007

Describe the bug
select day from test where day='2020-10-32', table 'test' is parititioned by day. In this case, '2020-10-32' will be taken as CastExpr not LiteralExpr, and condition "day='2020-10-32'" will not be recognized as partitionfilter. This case will scan all partitions. To avoid scall all partitions, it is better to filter invalid date value.

Doris supports datetime with micro second, but save datetime with second in BE. Doris FE use joda time library to parse a datetime of string type. If a datetime with micro second, it will transfer it into CastExpr, which will be recognized into invalid time. Therefore in the begining, a datetime with micro second will be parsed into datetime with second. It make no difference. For expample k1 <= "2020-10-10 10:10:10.00" will be transfered into k1 <= "2020-10-10 10:10:10";

To Reproduce
Steps to reproduce the behavior:

  1. create table:
    CREATE TABLE test (
    date datetime NULL COMMENT "日期",
    day date NULL COMMENT "",
    app_id varchar(1000) NULL COMMENT "",
    device_id int(11) 0 comment ""
    ) ENGINE=OLAP
    DUPLICATE KEY(date, day, app_id)
    COMMENT "OLAP"
    PARTITION BY RANGE(day)()
    DISTRIBUTED BY HASH(device_id) BUCKETS 64
    PROPERTIES (
    "replication_num" = "1",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.time_zone" = "Asia/Shanghai",
    "dynamic_partition.start" = "-30",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.replication_num" = "1",
    "dynamic_partition.buckets" = "32",
    "in_memory" = "false",
    "storage_format" = "V2"
    );
  2. insert some data into each partition
  3. execute a query which day is invalid, it will scan all partitions
    explain select day from test where '2020-10-32' = day;
    2020-10-17 20-44-01屏幕截图
    explain select day from test where 'hello' = day;
    2020-10-17 20-44-26屏幕截图
  4. if day is valid, it will scan some partitions
    2020-10-17 20-50-19屏幕截图

Desktop (please complete the following information):

  • OS: [e.g. iOS]
  • Browser [e.g. chrome, safari]
  • Version [e.g. 22]

Smartphone (please complete the following information):

  • Device: [e.g. iPhone6]
  • OS: [e.g. iOS8.1]
  • Browser [e.g. stock browser, safari]
  • Version [e.g. 22]

Additional context
Add any other context about the problem here.

Metadata

Metadata

Assignees

No one assigned

    Labels

    Stalearea/plannerIssues or PRs related to the query plannerkind/fixCategorizes issue or PR as related to a bug.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions