Drill-through on a timestamp column binned as XofY fails unless sematic type is defined

[paoliniluis]

Describe the bug
When a timestamp field hasn't been explicitly casted as a timestamp, Metabase will build invalid queries.

Logs
DB query: 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" FROM "public"."orders" WHERE "public"."orders"."created_at" = 6 LIMIT 2000
ERROR: operator does not exist: timestamp without time zone = integer at character 557

Metabase log

``` metabase-postgres | 2022-07-12 02:12:55,294 ERROR middleware.catch-exceptions :: Error processing query: ERROR: operator does not exist: timestamp without time zone = integer metabase-postgres | Hint: No operator matches the given name and argument types. You might need to add explicit type casts. metabase-postgres | Position: 557 metabase-postgres | {:database_id 2, metabase-postgres | :started_at #t "2022-07-12T02:12:55.146530Z[GMT]", metabase-postgres | :via metabase-postgres | [{:status :failed, metabase-postgres | :class clojure.lang.ExceptionInfo, metabase-postgres | :error metabase-postgres | "Error executing query: ERROR: operator does not exist: timestamp without time zone = integer\n Hint: No operator matches the given name and argument types. You might need to add explicit type casts.\n Position: 557", metabase-postgres | :stacktrace metabase-postgres | ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__54060.invoke(execute.clj:502)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)" metabase-postgres | "driver.sql_jdbc$fn__88867.invokeStatic(sql_jdbc.clj:54)" metabase-postgres | "driver.sql_jdbc$fn__88867.invoke(sql_jdbc.clj:52)" metabase-postgres | "query_processor.context$executef.invokeStatic(context.clj:59)" metabase-postgres | "query_processor.context$executef.invoke(context.clj:48)" metabase-postgres | "query_processor.context.default$default_runf.invokeStatic(default.clj:67)" metabase-postgres | "query_processor.context.default$default_runf.invoke(default.clj:65)" metabase-postgres | "query_processor.context$runf.invokeStatic(context.clj:45)" metabase-postgres | "query_processor.context$runf.invoke(context.clj:39)" metabase-postgres | "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)" metabase-postgres | "query_processor.reducible$identity_qp.invoke(reducible.clj:9)" metabase-postgres | "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___51447.invoke(cache.clj:220)" metabase-postgres | "query_processor.middleware.permissions$check_query_permissions$fn__47230.invoke(permissions.clj:109)" metabase-postgres | "metabase_enterprise.advanced_permissions.query_processor.middleware.permissions$check_download_permissions$fn__83123.invoke(permissions.clj:124)" metabase-postgres | "metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$maybe_apply_column_level_perms_check$fn__84895.invoke(column_level_perms_check.clj:30)" metabase-postgres | "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__50388.invoke(mbql_to_native.clj:23)" metabase-postgres | "query_processor$fn__52921$combined_post_process__52926$combined_post_process_STAR___52927.invoke(query_processor.clj:207)" metabase-postgres | "query_processor$fn__52921$combined_pre_process__52922$combined_pre_process_STAR___52923.invoke(query_processor.clj:204)" metabase-postgres | "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51368$fn__51373.invoke(resolve_database_and_driver.clj:35)" metabase-postgres | "driver$do_with_driver.invokeStatic(driver.clj:75)" metabase-postgres | "driver$do_with_driver.invoke(driver.clj:71)" metabase-postgres | "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51368.invoke(resolve_database_and_driver.clj:34)" metabase-postgres | "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47473.invoke(fetch_source_query.clj:281)" metabase-postgres | "query_processor.middleware.store$initialize_store$fn__47661$fn__47662.invoke(store.clj:11)" metabase-postgres | "query_processor.store$do_with_store.invokeStatic(store.clj:44)" metabase-postgres | "query_processor.store$do_with_store.invoke(store.clj:38)" metabase-postgres | "query_processor.middleware.store$initialize_store$fn__47661.invoke(store.clj:10)" metabase-postgres | "query_processor.middleware.normalize_query$normalize$fn__51640.invoke(normalize_query.clj:22)" metabase-postgres | "metabase_enterprise.audit_app.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__58307.invoke(handle_audit_queries.clj:130)" metabase-postgres | "query_processor.middleware.constraints$add_default_userland_constraints$fn__48730.invoke(constraints.clj:53)" metabase-postgres | "query_processor.middleware.process_userland_query$process_userland_query$fn__51579.invoke(process_userland_query.clj:145)" metabase-postgres | "query_processor.middleware.catch_exceptions$catch_exceptions$fn__51951.invoke(catch_exceptions.clj:168)" metabase-postgres | "query_processor.reducible$async_qp$qp_STAR___44200$thunk__44202.invoke(reducible.clj:100)" metabase-postgres | "query_processor.reducible$async_qp$qp_STAR___44200.invoke(reducible.clj:106)" metabase-postgres | "query_processor.reducible$async_qp$qp_STAR___44200.invoke(reducible.clj:91)" metabase-postgres | "query_processor.reducible$sync_qp$qp_STAR___44211.doInvoke(reducible.clj:126)" metabase-postgres | "query_processor$process_userland_query.invokeStatic(query_processor.clj:326)" metabase-postgres | "query_processor$process_userland_query.doInvoke(query_processor.clj:322)" metabase-postgres | "query_processor$fn__52968$process_query_and_save_execution_BANG___52977$fn__52980.invoke(query_processor.clj:337)" metabase-postgres | "query_processor$fn__52968$process_query_and_save_execution_BANG___52977.invoke(query_processor.clj:330)" metabase-postgres | "query_processor$fn__53012$process_query_and_save_with_max_results_constraints_BANG___53021$fn__53024.invoke(query_processor.clj:349)" metabase-postgres | "query_processor$fn__53012$process_query_and_save_with_max_results_constraints_BANG___53021.invoke(query_processor.clj:342)" metabase-postgres | "api.dataset$run_query_async$fn__67429.invoke(dataset.clj:68)" metabase-postgres | "query_processor.streaming$streaming_response_STAR_$fn__39322$fn__39323.invoke(streaming.clj:162)" metabase-postgres | "query_processor.streaming$streaming_response_STAR_$fn__39322.invoke(streaming.clj:161)" metabase-postgres | "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)" metabase-postgres | "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)" metabase-postgres | "async.streaming_response$do_f_async$task__18948.invoke(streaming_response.clj:84)"], metabase-postgres | :error_type :invalid-query, metabase-postgres | :ex-data metabase-postgres | {:sql metabase-postgres | "-- Metabase:: userID: 1 queryType: MBQL queryHash: 5bd890d406a58716018d2ff09b4145141e613655c245bac96f4c933b0c53b0ea\nSELECT \"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\" FROM \"public\".\"orders\" WHERE \"public\".\"orders\".\"created_at\" = 6 LIMIT 2000", metabase-postgres | :params nil, metabase-postgres | :type :invalid-query}}], metabase-postgres | :state "42883", metabase-postgres | :error_type :invalid-query, metabase-postgres | :json_query metabase-postgres | {:database 2, metabase-postgres | :query {:source-table 5, :filter ["=" ["field" 42 nil] 6]}, metabase-postgres | :type "query", metabase-postgres | :parameters [], metabase-postgres | :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}, metabase-postgres | :native metabase-postgres | {:query metabase-postgres | "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\" FROM \"public\".\"orders\" WHERE \"public\".\"orders\".\"created_at\" = 6 LIMIT 1048575", metabase-postgres | :params nil}, metabase-postgres | :status :failed, metabase-postgres | :class org.postgresql.util.PSQLException, metabase-postgres | :stacktrace metabase-postgres | ["org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)" metabase-postgres | "org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)" metabase-postgres | "org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)" metabase-postgres | "org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)" metabase-postgres | "org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)" metabase-postgres | "org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)" metabase-postgres | "org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)" metabase-postgres | "org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)" metabase-postgres | "org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)" metabase-postgres | "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)" metabase-postgres | "--> driver.sql_jdbc.execute$fn__53980.invokeStatic(execute.clj:367)" metabase-postgres | "driver.sql_jdbc.execute$fn__53980.invoke(execute.clj:365)" metabase-postgres | "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:375)" metabase-postgres | "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:372)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query$fn__54060.invoke(execute.clj:500)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)" metabase-postgres | "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)" metabase-postgres | "driver.sql_jdbc$fn__88867.invokeStatic(sql_jdbc.clj:54)" metabase-postgres | "driver.sql_jdbc$fn__88867.invoke(sql_jdbc.clj:52)" metabase-postgres | "query_processor.context$executef.invokeStatic(context.clj:59)" metabase-postgres | "query_processor.context$executef.invoke(context.clj:48)" metabase-postgres | "query_processor.context.default$default_runf.invokeStatic(default.clj:67)" metabase-postgres | "query_processor.context.default$default_runf.invoke(default.clj:65)" metabase-postgres | "query_processor.context$runf.invokeStatic(context.clj:45)" metabase-postgres | "query_processor.context$runf.invoke(context.clj:39)" metabase-postgres | "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)" metabase-postgres | "query_processor.reducible$identity_qp.invoke(reducible.clj:9)" metabase-postgres | "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___51447.invoke(cache.clj:220)" metabase-postgres | "query_processor.middleware.permissions$check_query_permissions$fn__47230.invoke(permissions.clj:109)" metabase-postgres | "metabase_enterprise.advanced_permissions.query_processor.middleware.permissions$check_download_permissions$fn__83123.invoke(permissions.clj:124)" metabase-postgres | "metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$maybe_apply_column_level_perms_check$fn__84895.invoke(column_level_perms_check.clj:30)" metabase-postgres | "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__50388.invoke(mbql_to_native.clj:23)" metabase-postgres | "query_processor$fn__52921$combined_post_process__52926$combined_post_process_STAR___52927.invoke(query_processor.clj:207)" metabase-postgres | "query_processor$fn__52921$combined_pre_process__52922$combined_pre_process_STAR___52923.invoke(query_processor.clj:204)" metabase-postgres | "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51368$fn__51373.invoke(resolve_database_and_driver.clj:35)" metabase-postgres | "driver$do_with_driver.invokeStatic(driver.clj:75)" metabase-postgres | "driver$do_with_driver.invoke(driver.clj:71)" metabase-postgres | "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51368.invoke(resolve_database_and_driver.clj:34)" metabase-postgres | "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47473.invoke(fetch_source_query.clj:281)" metabase-postgres | "query_processor.middleware.store$initialize_store$fn__47661$fn__47662.invoke(store.clj:11)" metabase-postgres | "query_processor.store$do_with_store.invokeStatic(store.clj:44)" metabase-postgres | "query_processor.store$do_with_store.invoke(store.clj:38)" metabase-postgres | "query_processor.middleware.store$initialize_store$fn__47661.invoke(store.clj:10)" metabase-postgres | "query_processor.middleware.normalize_query$normalize$fn__51640.invoke(normalize_query.clj:22)" metabase-postgres | "metabase_enterprise.audit_app.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__58307.invoke(handle_audit_queries.clj:130)" metabase-postgres | "query_processor.middleware.constraints$add_default_userland_constraints$fn__48730.invoke(constraints.clj:53)" metabase-postgres | "query_processor.middleware.process_userland_query$process_userland_query$fn__51579.invoke(process_userland_query.clj:145)" metabase-postgres | "query_processor.middleware.catch_exceptions$catch_exceptions$fn__51951.invoke(catch_exceptions.clj:168)" metabase-postgres | "query_processor.reducible$async_qp$qp_STAR___44200$thunk__44202.invoke(reducible.clj:100)" metabase-postgres | "query_processor.reducible$async_qp$qp_STAR___44200.invoke(reducible.clj:106)" metabase-postgres | "query_processor.reducible$async_qp$qp_STAR___44200.invoke(reducible.clj:91)" metabase-postgres | "query_processor.reducible$sync_qp$qp_STAR___44211.doInvoke(reducible.clj:126)" metabase-postgres | "query_processor$process_userland_query.invokeStatic(query_processor.clj:326)" metabase-postgres | "query_processor$process_userland_query.doInvoke(query_processor.clj:322)" metabase-postgres | "query_processor$fn__52968$process_query_and_save_execution_BANG___52977$fn__52980.invoke(query_processor.clj:337)" metabase-postgres | "query_processor$fn__52968$process_query_and_save_execution_BANG___52977.invoke(query_processor.clj:330)" metabase-postgres | "query_processor$fn__53012$process_query_and_save_with_max_results_constraints_BANG___53021$fn__53024.invoke(query_processor.clj:349)" metabase-postgres | "query_processor$fn__53012$process_query_and_save_with_max_results_constraints_BANG___53021.invoke(query_processor.clj:342)" metabase-postgres | "api.dataset$run_query_async$fn__67429.invoke(dataset.clj:68)" metabase-postgres | "query_processor.streaming$streaming_response_STAR_$fn__39322$fn__39323.invoke(streaming.clj:162)" metabase-postgres | "query_processor.streaming$streaming_response_STAR_$fn__39322.invoke(streaming.clj:161)" metabase-postgres | "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)" metabase-postgres | "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)" metabase-postgres | "async.streaming_response$do_f_async$task__18948.invoke(streaming_response.clj:84)"], metabase-postgres | :card_id nil, metabase-postgres | :context :ad-hoc, metabase-postgres | :error metabase-postgres | "ERROR: operator does not exist: timestamp without time zone = integer\n Hint: No operator matches the given name and argument types. You might need to add explicit type casts.\n Position: 557", metabase-postgres | :row_count 0, metabase-postgres | :running_time 0, metabase-postgres | :preprocessed metabase-postgres | {:database 2, metabase-postgres | :query metabase-postgres | {:source-table 5, metabase-postgres | :filter metabase-postgres | [:= metabase-postgres | [:field 42 {:temporal-unit :default}] metabase-postgres | [:value metabase-postgres | 6 metabase-postgres | {:base_type :type/DateTime, metabase-postgres | :effective_type :type/DateTime, metabase-postgres | :coercion_strategy nil, metabase-postgres | :semantic_type nil, metabase-postgres | :database_type "timestamp", metabase-postgres | :name "created_at", metabase-postgres | :unit :default}]], metabase-postgres | :fields metabase-postgres | [[:field 38 nil] metabase-postgres | [:field 44 nil] metabase-postgres | [:field 43 nil] metabase-postgres | [:field 39 nil] metabase-postgres | [:field 40 nil] metabase-postgres | [:field 37 nil] metabase-postgres | [:field 45 nil] metabase-postgres | [:field 42 {:temporal-unit :default}] metabase-postgres | [:field 41 nil]], metabase-postgres | :limit 1048575, metabase-postgres | :metabase.query-processor.middleware.limit/original-limit nil}, metabase-postgres | :type :query, metabase-postgres | :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}, metabase-postgres | :info {:executed-by 1, :context :ad-hoc}}, metabase-postgres | :data {:rows [], :cols []}} ```

To Reproduce

1. using sample database go to data model -> orders -> created at -> No semantic type. Then exit admin
2. new question -> Orders -> summarize by created at -> day of the week
3. Click on Friday-> View these Orders
4. see the error

ERROR: operator does not exist: timestamp without time zone = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 557

Also, see the filter on the top ("[Unknown]")

Interesting fact: Metabase knows that it's a timestamp field (go to query builder and click on the filter)

Expected behavior
Build the correct query for the drill down to happen correctly

Screenshots

Information about your Metabase Installation:

• Your browser and the version: Brave latest
• Your operating system: Pop-os 22.04
• Your databases: Postgres 14.2 with sample database
• Metabase version: 1.43.4
• Metabase hosting environment: Docker
• Metabase internal database: Postgres 14.2

Severity
P2'ish

Additional context
Is this frontend?

[flamber]

Very related to #18011

[deniskaber]

This has been addressed in #34233 . So, it should be fixed as soon as #35272 gets merged. Provided test case works there, but right now we open bar visualization by default. So, this is related to #12368 as well

[snoe]

@paoliniluis can you confirm this has been fixed, I can not reproduce

[kamilmielnik]

Reproducible in 0.48.0.
Cannot reproduce in master at 950a275.
Cannot reproduce in 0.49.0-RC1.