ClickHouse/metabase-clickhouse-driver

No results on specific date for datetime column

zero-element opened this issue · 3 comments

zero-element commented

Describe the bug

No results on specific date for datetime column

Steps to reproduce

  1. Create a table with datetime column
  2. Create a date time filter
  3. Select Current -> Day or Specific dates -> On
  4. 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;
slvrtrn commented

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.

  • 👍1
slvrtrn commented

@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.

  • 🎉1
slvrtrn commented

@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.

  • 🎉1