No results on specific date for datetime column

zero-element commented 5 months ago

Describe the bug

Steps to reproduce

Create a table with datetime column
Create a date time filter
Select Current -> Day or Specific dates -> On
Get No results!

Expected behaviour

Get results on the specific date, similar to what the v1.2.5 driver does. In v1.2.5, CAST is used to ensure that the datetime column is comparable with the input date, but newer versions "Removed superfluous CAST calls from generated queries that use Date* columns and/or intervals".

Error log

Configuration

Environment

metabase-clickhouse-driver version: 1.3.2 (1.2.5 works well)
metabase-clickhouse-driver configuration:
Metabase version: 0.48.3
OS:

ClickHouse server

ClickHouse Server version: 22.8.5.29
ClickHouse Server non-default settings, if any:

CREATE TABLE statements for tables involved:

CREATE TABLE test.test
(
`min_time` DateTime
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMMDD(min_time)
SETTINGS index_granularity = 8192;

Sample data for all these tables, use clickhouse-obfuscator if necessary

slvrtrn commented 5 months ago

Thanks for the report!

While it works from a simple filter, it does not from the editor with variables cause it generates smth like

SELECT
  `default`.`test`.`min_time` AS `min_time`
FROM
  `default`.`test`
 WHERE `default`.`test`.`min_time` = '2024-01-16'
LIMIT
  1048575

ClickHouse is too strict for this...

Given that the available metadata contains no useful info to decide on the transformation (toDate(x)) in this case

{:description nil,
  :lib/type :metadata/column,
  :base-type :type/DateTime,
  :semantic-type nil,
  :table-id 9,
  :name "min_time",
  :coercion-strategy nil,
  :settings nil,
  :nfc-path nil,
  :database-type "DateTime",
  :effective-type :type/DateTime,
  :fk-target-field-id nil,
  :id 72,
  :parent-id nil,
  :visibility-type :normal,
  :display-name "Min Time",
  :position 0,
  :fingerprint
  {:global {:distinct-count 1, :nil% 0.0},
   :type {:type/DateTime {:earliest "2024-01-16T16:47:38Z", :latest "2024-01-16T16:47:38Z"}}}})

could be that a small patch to MB will be required.

We will discuss a possible solution in this case. Please stay tuned.

slvrtrn commented 5 months ago

@zero-element, the ETA for the fix is 0.49.0 (needs a little patch in the MB, see the linked PRs). Until it arrives, please stay on 0.47/1.2.5 if the feature is essential for your use case.

slvrtrn commented 3 months ago

@zero-element, I forgot to write an update here: as Metabase 0.49.0-RC2 is out and contains the necessary fixes, and our CI is green with it, I merged the PR and released 1.4.0, which is supposed to resolve this issue. If it still occurs, please feel free to re-open, and I will investigate.

ClickHouse / metabase-clickhouse-driver