Date Casting Optimization

Driver Development
1

Hello fellow Metabase enthusiasts,

do you have any hints how to adjust the behavior of the query optimization for temporal filters? When we have a Date column, we cannot compare directly against a DateTime String, but we have to cast to Date or DateTime first. E.g. “WHERE db.table.date_column >= ‘2020-01-31 23:00:00’ AND db.table.date_column < ‘2020-02-29 23:00:00’” query does not work.

Perhaps there is a smarter way to serialize Date values, depending on the field type, in the driver, or there is even a simpler solution to this issue. Please enlighten me! :slight_smile:

Best regards,
enqueue

2

Hi @enqueue
I’m definitely do not have a lot of knowledge about drivers, but I’m sure you can find drivers where this is done. Which driver are you working on?
It’s kinda the opposite of this PR - does that help looking at the diff to figuring out how you can hack around that?
https://github.com/metabase/metabase/pull/15115

3

Hi @flamber,

thank you for your quick reply! I am working on the ClickHouse driver. The PR you linked is very interesting because it shows how some RDBMs need to get fed their parameters in the right format to make them digestable to the query optimizer, I think it is one step too far already. There seems to be an a priori optimization in place that tries to pass date/time parameters as Strings directly instead of subjecting them to driver treatment (class optimize_temporal_filters). I would like to know what the best way is to make our date columns filter correctly when a date time range is passed.

When passing a date time String, we would need to cast to Date or DateTime. If we could truncate the date time String to a date String, this would be OK, too.

Thank you for your help! Best regards,
enqueue

4

@enqueue I might be mixing/reversing what you’re looking for, but wasn’t that added to 0.38?
https://github.com/metabase/metabase/pull/13860
Other 0.38 changes: https://github.com/metabase/metabase/wiki/What’s-new-in-0.38.0-for-Metabase-driver-authors

5

Thanks again for your help, @flamber. I think I found the right place to hook into the query processor that is run before the optimization kicks in. We are overriding a couple of the metabase.driver.sql.query-processor methods for several java.time methods, like this:

(defmethod sql.qp/->honeysql [:clickhouse OffsetDateTime]
  [driver t]
  (hsql/call :parseDateTimeBestEffort t))

It would be nicer to avoid this, but at least it works.

1 Like