Today filter not working when timesamp field is in datetime64 format

[antoxa9898]

Describe the bug

When applying 'today' filter in metabase i have no results, but if i use specific date filter - on specific date(for example 29.01.23), i receive results. This issue appeared after i changed timestamp field in my clickhouse table from DateTime to DateTime64 type( i need to se MS).

Steps to reproduce

1. open clickhouse datasource table in metabase
2. apply 'today' filter
3. you will reseive no results

Configuration

Environment

• metabase-clickhouse-driver version: 1.3.0
• Metabase version: v0.48.1

this is my create table:

CREATE TABLE vector.qa_nginx_logs
(
    `ASN` String,
    `bytes_sent` String,
    `correlation_id` String,
    `country` String,
    `host` String,
    `http_host` String,
    `method` String,
    `path` String,
    `referer` String,
    `remote_addr` String,
    `request_full` String,
    `request_length` UInt64,
    `request_scheme` LowCardinality(String),
    `request_time` String,
    `response_content_type` LowCardinality(String),
    `status` Int32,
    `timestamp` DateTime64(3),
    `trace_identifier` String,
    `upstream_addr` String,
    `upstream_cache_status` String,
    `upstream_connect_time` String,
    `upstream_header_time` String,
    `upstream_response_time` String,
    `user_agent` String,
    `x_forwarded_for` String
)
ENGINE = ReplicatedMergeTree('/vector.qa_nginx_logs_new', '{replica}')
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (timestamp, host)
TTL toDateTime(timestamp) + toIntervalWeek(2)
SETTINGS index_granularity = 8192

this is sql query which is generated by metabase:

SELECT
  `vector`.`qa_nginx_logs`.`ASN` AS `ASN`,
  `vector`.`qa_nginx_logs`.`bytes_sent` AS `bytes_sent`,
  `vector`.`qa_nginx_logs`.`correlation_id` AS `correlation_id`,
  `vector`.`qa_nginx_logs`.`country` AS `country`,
  `vector`.`qa_nginx_logs`.`host` AS `host`,
  `vector`.`qa_nginx_logs`.`http_host` AS `http_host`,
  `vector`.`qa_nginx_logs`.`method` AS `method`,
  `vector`.`qa_nginx_logs`.`path` AS `path`,
  `vector`.`qa_nginx_logs`.`referer` AS `referer`,
  `vector`.`qa_nginx_logs`.`remote_addr` AS `remote_addr`,
  `vector`.`qa_nginx_logs`.`request_full` AS `request_full`,
  `vector`.`qa_nginx_logs`.`request_length` AS `request_length`,
  `vector`.`qa_nginx_logs`.`request_scheme` AS `request_scheme`,
  `vector`.`qa_nginx_logs`.`request_time` AS `request_time`,
  `vector`.`qa_nginx_logs`.`response_content_type` AS `response_content_type`,
  `vector`.`qa_nginx_logs`.`status` AS `status`,
  `vector`.`qa_nginx_logs`.`timestamp` AS `timestamp`,
  `vector`.`qa_nginx_logs`.`trace_identifier` AS `trace_identifier`,
  `vector`.`qa_nginx_logs`.`upstream_addr` AS `upstream_addr`,
  `vector`.`qa_nginx_logs`.`upstream_cache_status` AS `upstream_cache_status`,
  `vector`.`qa_nginx_logs`.`upstream_connect_time` AS `upstream_connect_time`,
  `vector`.`qa_nginx_logs`.`upstream_header_time` AS `upstream_header_time`,
  `vector`.`qa_nginx_logs`.`upstream_response_time` AS `upstream_response_time`,
  `vector`.`qa_nginx_logs`.`user_agent` AS `user_agent`,
  `vector`.`qa_nginx_logs`.`x_forwarded_for` AS `x_forwarded_for`
FROM
  `vector`.`qa_nginx_logs`
WHERE
  (
    `vector`.`qa_nginx_logs`.`timestamp` >= CAST(NOW() AS date)
  )

   AND (
    `vector`.`qa_nginx_logs`.`timestamp` < CAST(NOW() + INTERVAL 1 day AS date)
  )
LIMIT
  1048575

[slvrtrn]

I tested it via Metabase with the following:

CREATE TABLE uniq_test
(
    `gettime` DateTime64(3),
    `uid` String
)
ENGINE = MergeTree
ORDER BY tuple()

INSERT INTO uniq_test VALUES (now(), 'foo'), (toDateTime64(now() - interval 2 day, 3), 'bar')

┌─────────────────gettime─┬─uid─┐
│ 2024-01-31 16:25:21.000 │ foo │
│ 2024-01-29 16:25:21.000 │ bar │
└─────────────────────────┴─────┘

SELECT
    default.uniq_test.gettime AS gettime,
    default.uniq_test.uid AS uid
FROM default.uniq_test
WHERE (default.uniq_test.gettime >= CAST(NOW(), 'date')) AND (default.uniq_test.gettime < CAST(NOW() + toIntervalDay(1), 'date'))
LIMIT 2000

┌─────────────────gettime─┬─uid─┐
│ 2024-01-31 16:25:21.000 │ foo │
└─────────────────────────┴─────┘

Does your generated query also return no rows when executed via CH client?

[antoxa9898]

thanks for your hint. I tested creation table with your query and it works. after some tests i determined that promlem appears when datetime64 type field is primary or partition key. and after testing in different versions i determined that problem dissapeared in ClickHouse release 23.3. So this is clickhouse bug, not click metabase plugin