cast string column to date in where clause

[skyyws <sk...@163.com>]

Hello everyone,
Recently when I use Kylin to query like this:
select dt from table
where cast(dt as date) >= cast('2020-07-21' as date) and cast(dt as date) <= cast('2020-07-22' as date)
group by dt;
The result is null, but when I use this query:
select dt from table
where dt >= '2020-07-21' and dt <= '2020-07-22'
group by dt;
The result is '2020-07-21'. By the way, 'dt' column type is string, and Kylin version is 2.6.6.
When I read the source code, I found that when I use the first query,  Kylin server will transform constant in where condition to bigint: 1595289600000 and 1595376000000. When prune segments, these two values will compare to segment range(20200721000000_20200722000000), which means compare bigint format string(1595289600000) with date format string(2020-07-21).
I am not sure if my understanding is correct? Can we cast string type column to date, and compare with constant in where clause?

Re: cast string column to date in where clause

[Yaqian Zhang <Ya...@126.com>]

Hi:

Can you provide the detail query log? SegmentPruner  may prune the segment by mistake for some reason.

> 在 2020年7月22日，19:54，skyyws <sk...@163.com> 写道：
> 
> Hello everyone,
> Recently when I use Kylin to query like this:
> select dt from table
> where cast(dt as date) >= cast('2020-07-21' as date) and cast(dt as date) <= cast('2020-07-22' as date)
> group by dt;
> The result is null, but when I use this query:
> select dt from table
> where dt >= '2020-07-21' and dt <= '2020-07-22'
> group by dt;
> The result is '2020-07-21'. By the way, 'dt' column type is string, and Kylin version is 2.6.6.
> When I read the source code, I found that when I use the first query,  Kylin server will transform constant in where condition to bigint: 1595289600000 and 1595376000000. When prune segments, these two values will compare to segment range(20200721000000_20200722000000), which means compare bigint format string(1595289600000) with date format string(2020-07-21).
> I am not sure if my understanding is correct? Can we cast string type column to date, and compare with constant in where clause?