Invalid query error when trying to zoom in Bar visualization

[adronkin]

Describe the bug

When trying to zoom Bar visualisation based on joined MySQL tables I get an error:
Invalid query: {:query {:breakout [nil nil nil nil nil [nil nil ["Invalid :temporal-unit for the specified :base-type."]]]}}

To Reproduce

• I have a MySQL table orders with a list of orders, date format linux time stamp.
• I created an Orders with date Question based on the orders table - SELECT order.order AS order, FROM_UNIXTIME(order.open_milli / 1000) AS timestamp FROM order;
  Then I joined orders and Orders with date by order number.
• I created a visualization in which I added several fields with order information from the orders table and timestamp from Orders with date
• Visualization format Bar, view previous 30 days by day
• When trying to zoom I get an error (group by hours):
  Invalid query: {:query {:breakout [nil nil nil nil nil [nil nil ["Invalid :temporal-unit for the specified :base-type."]]]}}
  
  
• Then if I change group by minute and return group by hour. Everything is fine
  

Expected behavior

The visualization should group by hours without error.

Logs

[183bf2df-5196-4ed0-a1eb-94885de9a6e7] 2024-05-17T11:25:52+04:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Invalid query: {:query {:breakout [nil nil nil nil nil [nil nil ["Invalid :temporal-unit for the specified :base-type."]]]}}
{:database_id 3,
:started_at #t "2024-05-17T07:25:52.140083702Z[Etc/UTC]",
:action_id nil,
:json_query
{:database 3,
:type "query",
:query
{:source-table 20,
:joins
[{:alias "Deal - Order",
:condition
["="
["field" 252 {:base-type "type/Integer"}]
["field" 217 {:base-type "type/Integer", :join-alias "Deal - Order"}]],
:source-table 18}
{:alias "Order DB with Date - Order",
:condition
["="
["field" 252 {:base-type "type/Integer"}]
["field" "order" {:base-type "type/Integer", :join-alias "Order DB with Date - Order"}]],
:source-table "card__15"}],
:expressions
{:Volume
["" ["field" 239 {:base-type "type/Float"}] ["field" 218 {:base-type "type/Float", :join-alias "Deal - Order"}]]},
:aggregation [["sum" ["expression" "Volume" {:base-type "type/Float"}]]],
:breakout
[["field" 242 {:base-type "type/Text"}]
["field" 232 {:base-type "type/Text"}]
["field" 229 {:base-type "type/Text"}]
["field" 231 {:base-type "type/Text"}]
["field" 243 {:base-type "type/Text"}]
["field" "timestamp" {:base-type "type/Date", :join-alias "Order DB with Date - Order", :temporal-unit "hour"}]],
:filter
["between"
["field" "timestamp" {:base-type "type/Date", :join-alias "Order DB with Date - Order", :temporal-unit "day"}]
"2024-05-09T00:00Z"
"2024-05-10T00:00Z"]},
:parameters [],
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:native nil,
:status :failed,
:class clojure.lang.ExceptionInfo,
:stacktrace
["--> mbql.schema$fn__29379$fn__29380.invoke(schema.cljc:1874)"
"query_processor.middleware.validate$validate_query.invokeStatic(validate.clj:9)"
"query_processor.middleware.validate$validate_query.invoke(validate.clj:6)"
"query_processor$preprocess_STAR_$fn__73445.invoke(query_processor.clj:164)"
"query_processor$preprocess_STAR_.invokeStatic(query_processor.clj:162)"
"query_processor$preprocess_STAR_.invoke(query_processor.clj:157)"
"query_processor$fn__73453$combined_pre_process__73454$combined_pre_process_STAR___73455.invoke(query_processor.clj:259)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__66731.invoke(fetch_source_query.clj:303)"
"query_processor.middleware.resolve_database_and_driver$resolve_driver_and_database_local_values$fn__72196$fn__72200.invoke(resolve_database_and_driver.clj:77)"
"driver$do_with_driver.invokeStatic(driver.clj:97)"
"driver$do_with_driver.invoke(driver.clj:92)"
"query_processor.middleware.resolve_database_and_driver$resolve_driver_and_database_local_values$fn__72196.invoke(resolve_database_and_driver.clj:76)"
"query_processor.middleware.store$initialize_store$fn__67358$fn__67359.invoke(store.clj:14)"
"query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:169)"
"query_processor.store$do_with_metadata_provider.invoke(store.clj:150)"
"query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:158)"
"query_processor.store$do_with_metadata_provider.invoke(store.clj:150)"
"query_processor.middleware.store$initialize_store$fn__67358.invoke(store.clj:13)"
"query_processor.middleware.resolve_database_and_driver$resolve_database$fn__72193.invoke(resolve_database_and_driver.clj:60)"
"query_processor.middleware.normalize_query$normalize$fn__72498.invoke(normalize_query.clj:38)"
"query_processor.middleware.enterprise$fn__72133$handle_audit_app_internal_queries__72134$fn__72136.invoke(enterprise.clj:96)"
"query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__72144.invoke(enterprise.clj:103)"
"query_processor.middleware.constraints$mark_needs_default_userland_constraints$fn__71259.invoke(constraints.clj:104)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__72429.invoke(process_userland_query.clj:156)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__73030.invoke(catch_exceptions.clj:171)"
"query_processor.reducible$async_qp$qp_STAR___62871$thunk__62873.invoke(reducible.clj:126)"
"query_processor.reducible$async_qp$qp_STAR___62871.invoke(reducible.clj:132)"
"query_processor.reducible$sync_qp$qp_STAR___62883.doInvoke(reducible.clj:153)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:402)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:398)"
"query_processor$process_query_and_save_execution_BANG_.invokeStatic(query_processor.clj:416)"
"query_processor$process_query_and_save_execution_BANG_.invoke(query_processor.clj:406)"
"query_processor$process_query_and_save_with_max_results_constraints_BANG_.invokeStatic(query_processor.clj:431)"
"query_processor$process_query_and_save_with_max_results_constraints_BANG_.invoke(query_processor.clj:421)"
"api.dataset$run_query_async$fn__93999.invoke(dataset.clj:79)"
"query_processor.streaming$streaming_response_STAR_$fn__53297$fn__53299.invoke(streaming.clj:168)"
"query_processor.streaming$streaming_response_STAR_$fn__53297.invoke(streaming.clj:167)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
"async.streaming_response$do_f_async$task__43762.invoke(streaming_response.clj:88)"],
:card_id nil,
:context :ad-hoc,
:error
"Invalid query: {:query {:breakout [nil nil nil nil nil [nil nil ["Invalid :temporal-unit for the specified :base-type."]]]}}",
:row_count 0,
:running_time 0,
:preprocessed nil,
:ex-data
{:error {:query {:breakout [nil nil nil nil nil [nil nil ["Invalid :temporal-unit for the specified :base-type."]]]}},
:original
{:schema [:ref :metabase.mbql.schema/Query],
:value
{:database 3,
:type :query,
:query
{:source-table 20,
:joins
[{:alias "Deal - Order",
:condition
[:= [:field 252 {:base-type :type/Integer}] [:field 217 {:base-type :type/Integer, :join-alias "Deal - Order"}]],
:source-table 18}
{:alias "Order DB with Date - Order",
:condition
[:=
[:field 252 {:base-type :type/Integer}]
[:field "order" {:base-type :type/Integer, :join-alias "Order DB with Date - Order"}]],
:source-card-id 15,
:source-query
{:collection "order",
:native
"SELECT\n order.order AS order,\n order.open_milli AS open_milli,\n FROM_UNIXTIME(order.open_milli / 1000) AS timestamp\nFROM\n order;"},
:source-metadata
[{:display_name "order",
:field_ref [:field "order" {:base-type :type/Integer}],
:name "order",
:base_type :type/Integer,
:effective_type :type/Integer,
:semantic_type nil,
:fingerprint
{:global {:distinct-count 2000, :nil% 0.0},
:type
{:type/Number
{:min 1.0, :q1 500.9768035073571, :q3 1500.5, :max 2000.0, :sd 577.4160643813981, :avg 1000.5}}}}
{:display_name "open_milli",
:field_ref [:field "open_milli" {:base-type :type/BigInteger}],
:name "open_milli",
:base_type :type/BigInteger,
:effective_type :type/BigInteger,
:semantic_type nil,
:fingerprint
{:global {:distinct-count 1531, :nil% 0.0},
:type
{:type/Number
{:min 1.667905697787E12,
:q1 1.6772687549379685E12,
:q3 1.687731282289729E12,
:max 1.68937138035E12,
:sd 6.950498492473033E9,
:avg 1.682831989752517E12}}}}
{:display_name "timestamp",
:field_ref [:field "timestamp" {:base-type :type/DateTime}],
:name "timestamp",
:base_type :type/DateTime,
:effective_type :type/DateTime,
:semantic_type nil,
:fingerprint
{:global {:distinct-count 1531, :nil% 0.0},
:type {:type/DateTime {:earliest "2022-11-08T11:08:17.787Z", :latest "2023-07-14T21:49:40.35Z"}}}}]}],
:expressions
{"Volume"
[: [:field 239 {:base-type :type/Float}] [:field 218 {:base-type :type/Float, :join-alias "Deal - Order"}]]},
:aggregation [[:sum [:expression "Volume" {:base-type :type/Float}]]],
:breakout
[[:field 242 {:base-type :type/Text}]
[:field 232 {:base-type :type/Text}]
[:field 229 {:base-type :type/Text}]
[:field 231 {:base-type :type/Text}]
[:field 243 {:base-type :type/Text}]
[:field "timestamp" {:base-type :type/Date, :join-alias "Order DB with Date - Order", :temporal-unit :hour}]],
:filter
[:between
[:field "timestamp" {:base-type :type/Date, :join-alias "Order DB with Date - Order", :temporal-unit :day}]
"2024-05-09T00:00Z"
"2024-05-10T00:00Z"]},
:middleware
{:js-int-to-string? true,
:add-default-userland-constraints? true,
:metabase.query-processor.middleware.constraints/add-userland-constraints? true},
:info {:executed-by 1, :context :ad-hoc, :query-hash #object["[B" 0x33359799 "[B@33359799"]}},
:errors
({:path [0 0 0 :query 0 0 :breakout 0 0 :field 0 0 1 "options" 0 0 0 1],
:in [:query :breakout 5 2],
:schema
[:fn {:error/message "Invalid :temporal-unit for the specified :base-type."} #object[metabase.mbql.schema$valid_temporal_unit_for_base_type_QMARK_ 0x3d36b3a "metabase.mbql.schema$valid_temporal_unit_for_base_type_QMARK_@3d36b3a"]],
:value {:base-type :type/Date, :join-alias "Order DB with Date - Order", :temporal-unit :hour}})}},
:data {:rows [], :cols []}}

Information about your Metabase installation

- Browsers: Chrome 125.0.6422.61, Firefox 125.0.3
- OS: Windows 10-11
- Databases: MariaDB
- Metabase Version: 0.49.10
- Metabase Hosting Env: Jar-File on Ubuntu 22.04.4 LTS
- Metabase internal database: H2

Severity

Low

Additional context

No response