When using a Native SQL the incorrect SQL query gets generated for Athena
It produces this query:
SELECT
COUNT(*) AS "count", DATE_TRUNC('hour', CAST("ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp)) AS "partition_date_hour"
FROM
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"
WHERE
1=1
AND DATE_TRUNC('day', CAST("ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp)) BETWEEN timestamp '2024-01-15 09:30:00.000' AND timestamp '2024-01-16 12:30:00.000'
GROUP BY DATE_TRUNC('hour', CAST("ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp))
ORDER BY DATE_TRUNC('hour', CAST("ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp)) ASC
Where is the DATE_TRUNC('day' coming from?
The same query done through the Notebook produces this:
SELECT
DATE_TRUNC(
'hour',
CAST(
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp
)
) AS "partition_date_hour",
COUNT(*) AS "count"
FROM
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"
WHERE
CAST(
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp
) BETWEEN timestamp '2024-01-14 12:30 UTC'
AND timestamp '2024-01-16 12:30 UTC'
GROUP BY
DATE_TRUNC(
'hour',
CAST(
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp
)
)
ORDER BY
DATE_TRUNC(
'hour',
CAST(
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp
)
) ASC
This is the metadata for the partition_date_hour
We've opened an issue here: Incorrect SQL generated for WHERE clause when using Native Query and DateTime Field Filter Variables: DATE_TRUNC · Issue #38384 · metabase/metabase (github.com)
Has anybody run into this before? Any workarounds?