Relative date filters don't work with custom date columns

[ranquild]

Describe the bug

When using a relative date filter with a custom date column the query always returns "No results" despite data being present for the selected range.

To Reproduce

1. New -> Question -> Orders
2. Add custom column = [Created At], with name DateColumn
3. Filter -> DateColumn -> Relative dates -> Current -> Year
4. Visualize

Actual result:

• No results found

Expected result:

• Rows are returned for the current year. Compare with filtering by Created At directly.

Expected behavior

No response

Logs

No response

Information about your Metabase installation

0.47.0

Severity

P2

Additional context

No response

[kamilmielnik]

Reproducible in 0.47.0.
Reproducible in mlv2-fe-filters/chill-mode (#34851 @ e546daa) and in master (d35c779).

[bshepherdson]

I can reproduce this on master today too.

Bad SQL with custom column:

SELECT
  "source"."ID" AS "ID",
  "source"."USER_ID" AS "USER_ID",
  "source"."PRODUCT_ID" AS "PRODUCT_ID",
  "source"."SUBTOTAL" AS "SUBTOTAL",
  "source"."TAX" AS "TAX",
  "source"."TOTAL" AS "TOTAL",
  "source"."DISCOUNT" AS "DISCOUNT",
  "source"."CREATED_AT" AS "CREATED_AT",
  "source"."QUANTITY" AS "QUANTITY",
  "source"."DateColumn" AS "DateColumn"
FROM (
  SELECT
    "PUBLIC"."ORDERS"."ID" AS "ID",
    "PUBLIC"."ORDERS"."USER_ID" AS "USER_ID",
    "PUBLIC"."ORDERS"."PRODUCT_ID" AS "PRODUCT_ID",
    "PUBLIC"."ORDERS"."SUBTOTAL" AS "SUBTOTAL",
    "PUBLIC"."ORDERS"."TAX" AS "TAX",
    "PUBLIC"."ORDERS"."TOTAL" AS "TOTAL",
    "PUBLIC"."ORDERS"."DISCOUNT" AS "DISCOUNT",
    "PUBLIC"."ORDERS"."CREATED_AT" AS "CREATED_AT",
    "PUBLIC"."ORDERS"."QUANTITY" AS "QUANTITY",
    "PUBLIC"."ORDERS"."CREATED_AT" AS "DateColumn"
  FROM "PUBLIC"."ORDERS") AS "source"
WHERE
  "source"."DateColumn" = DATE_TRUNC('year', NOW())
LIMIT 2000

which looks plausible at a glance but doesn't return any data. The correct query (on Created At directly rather than the custom column) generates this filter instead:

WHERE (
  "PUBLIC"."ORDERS"."CREATED_AT" >= DATE_TRUNC('year', NOW()))
AND 
  "PUBLIC"."ORDERS"."CREATED_AT" < DATE_TRUNC('year', DATEADD('year', CAST(1 AS long), CAST(NOW() AS datetime)))

I guess the custom column's type isn't being correctly established. This may Just Work with MLv2-powered custom column types in the QP, or it might require a deliberate fix. (Maybe instead the resulting type of just a year number is being computed for source.DateColumn even though it's the entire date and hasn't been truncated yet.)