datetimeDiff Error

justinolee · April 4, 2023, 1:58am

I am trying to create a custom column by using datetimeDiff, but the function throws this error:
"No method in multimethod '->honeysql' for dispatch value: [:sqlserver :datetime-diff]".

I am on 1.45.3, and the custom expression builder does not seem to recognize datetimeDiff, until I fully type out the name of the method.

Screenshot 2023-04-03 at 8.59.15 PM

TonyC · April 4, 2023, 11:05am

Can you share a screenshot of the whole GUI questions since it seems similar to this:

github.com/metabase/metabase

Cumulative Sum/Count fails when there's an aggregation after the calculation

opened 10:20PM - 29 Oct 20 UTC

flamber

Type:Bug Priority:P2 Querying/Processor Difficulty:Hard .Reproduced .Team/QueryProcessor

**Describe the bug** Queries fail, when summarizing with Cumulative Sum/Count a…nd then adding a Custom Column (or Filter) after the aggregation. **To Reproduce** 1. Custom question > Sample Dataset > Orders 2. Summarize "Cumulative sum of Total" (or "Cumulative count") by CreatedAt:Month 3. Custom Column formula `1 + 1` as "CustomColumn" (or create a Filter by CreatedAt:Month) ![image](https://user-images.githubusercontent.com/1447303/97637727-77309f80-1a3b-11eb-81a9-2ec919d12995.png) 4. Error with `No method in multimethod '->honeysql' for dispatch value: [:h2 :cum-sum]` / `No method in multimethod '->honeysql' for dispatch value: [:h2 :cum-count]` <details><summary>Full stacktrace</summary> ``` 2020-10-29 23:13:28,537 ERROR middleware.catch-exceptions :: Error processing query: null {:database_id 4, :started_at #t "2020-10-29T23:13:27.287588+01:00[Europe/Copenhagen]", :json_query {:database 4, :query {:source-query {:source-table 11, :aggregation [["cum-sum" ["field-id" 88]]], :breakout [["datetime-field" ["field-id" 86] "month"]]}, :expressions {:CustomColumn ["+" 1 1]}}, :type "query", :parameters [], :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}, :native nil, :status :failed, :class java.lang.IllegalArgumentException, :stacktrace ["clojure.lang.MultiFn.getFn(MultiFn.java:156)" "clojure.lang.MultiFn.invoke(MultiFn.java:233)" "--> driver.sql.query_processor$fn__68784.invokeStatic(query_processor.clj:467)" "driver.sql.query_processor$fn__68784.invoke(query_processor.clj:465)" "driver.sql.query_processor$fn__68867$iter__68869__68873$fn__68874$fn__68875.invoke(query_processor.clj:563)" "driver.sql.query_processor$fn__68867$iter__68869__68873$fn__68874.invoke(query_processor.clj:562)" "driver.sql.query_processor$fn__68867.invokeStatic(query_processor.clj:562)" "driver.sql.query_processor$fn__68867.invoke(query_processor.clj:560)" "driver.sql.query_processor$apply_top_level_clauses$fn__69164.invoke(query_processor.clj:838)" "driver.sql.query_processor$apply_top_level_clauses.invokeStatic(query_processor.clj:836)" "driver.sql.query_processor$apply_top_level_clauses.invoke(query_processor.clj:832)" "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:913)" "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:898)" "driver.sql.query_processor$apply_source_query$fn__69170.invoke(query_processor.clj:865)" "driver.sql.query_processor$apply_source_query.invokeStatic(query_processor.clj:864)" "driver.sql.query_processor$apply_source_query.invoke(query_processor.clj:857)" "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:909)" "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:898)" "driver.sql.query_processor$apply_source_query$fn__69170.invoke(query_processor.clj:865)" "driver.sql.query_processor$apply_source_query.invokeStatic(query_processor.clj:864)" "driver.sql.query_processor$apply_source_query.invoke(query_processor.clj:857)" "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:909)" "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:898)" "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:904)" "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:898)" "driver.sql.query_processor$fn__69241$build_honeysql_form__69246$fn__69250.invoke(query_processor.clj:918)" "driver.sql.query_processor$fn__69241$build_honeysql_form__69246.invoke(query_processor.clj:915)" "driver.sql.query_processor$mbql__GT_honeysql.invokeStatic(query_processor.clj:929)" "driver.sql.query_processor$mbql__GT_honeysql.invoke(query_processor.clj:927)" "driver.sql.query_processor$mbql__GT_native.invokeStatic(query_processor.clj:935)" "driver.sql.query_processor$mbql__GT_native.invoke(query_processor.clj:931)" "driver.sql$fn__78087.invokeStatic(sql.clj:40)" "driver.sql$fn__78087.invoke(sql.clj:38)" "query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:15)" "query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:10)" "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47424.invoke(mbql_to_native.clj:23)" "query_processor.middleware.check_features$check_features$fn__46699.invoke(check_features.clj:42)" "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47589.invoke(optimize_datetime_filters.clj:133)" "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45500.invoke(auto_parse_filter_values.clj:44)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49155.invoke(wrap_value_literals.clj:149)" "query_processor.middleware.annotate$add_column_info$fn__45262.invoke(annotate.clj:575)" "query_processor.middleware.permissions$check_query_permissions$fn__46574.invoke(permissions.clj:70)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48107.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46772.invoke(cumulative_aggregations.clj:61)" "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48344.invoke(resolve_joined_fields.clj:36)" "query_processor.middleware.resolve_joins$resolve_joins$fn__48663.invoke(resolve_joins.clj:183)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__40987.invoke(add_implicit_joins.clj:254)" "query_processor.middleware.large_int_id$convert_id_to_string$fn__47385.invoke(large_int_id.clj:44)" "query_processor.middleware.limit$limit$fn__47410.invoke(limit.clj:38)" "query_processor.middleware.format_rows$format_rows$fn__47365.invoke(format_rows.clj:84)" "query_processor.middleware.desugar$desugar$fn__46838.invoke(desugar.clj:22)" "query_processor.middleware.binning$update_binning_strategy$fn__45859.invoke(binning.clj:229)" "query_processor.middleware.resolve_fields$resolve_fields$fn__46376.invoke(resolve_fields.clj:24)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__40531.invoke(add_dimension_projections.clj:318)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40738.invoke(add_implicit_clauses.clj:141)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41136.invoke(add_source_metadata.clj:105)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48304.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45447.invoke(auto_bucket_datetimes.clj:125)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46423.invoke(resolve_source_table.clj:46)" "query_processor.middleware.parameters$substitute_parameters$fn__48089.invoke(parameters.clj:114)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46475.invoke(resolve_referenced.clj:80)" "query_processor.middleware.expand_macros$expand_macros$fn__47094.invoke(expand_macros.clj:158)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__41167.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49025.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48315$fn__48319.invoke(resolve_database_and_driver.clj:33)" "driver$do_with_driver.invokeStatic(driver.clj:61)" "driver$do_with_driver.invoke(driver.clj:57)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48315.invoke(resolve_database_and_driver.clj:27)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47312.invoke(fetch_source_query.clj:267)" "query_processor.middleware.store$initialize_store$fn__49034$fn__49035.invoke(store.clj:11)" "query_processor.store$do_with_store.invokeStatic(store.clj:46)" "query_processor.store$do_with_store.invoke(store.clj:40)" "query_processor.middleware.store$initialize_store$fn__49034.invoke(store.clj:10)" "query_processor.middleware.cache$maybe_return_cached_results$fn__46352.invoke(cache.clj:214)" "query_processor.middleware.validate$validate_query$fn__49043.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__47437.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41005.invoke(add_rows_truncated.clj:36)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49010.invoke(results_metadata.clj:147)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__46715.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__48178.invoke(process_userland_query.clj:136)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46658.invoke(catch_exceptions.clj:174)" "query_processor.reducible$async_qp$qp_STAR___39794$thunk__39795.invoke(reducible.clj:103)" "query_processor.reducible$async_qp$qp_STAR___39794.invoke(reducible.clj:109)" "query_processor.reducible$sync_qp$qp_STAR___39803$fn__39806.invoke(reducible.clj:135)" "query_processor.reducible$sync_qp$qp_STAR___39803.invoke(reducible.clj:134)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:237)" "query_processor$process_userland_query.doInvoke(query_processor.clj:233)" "query_processor$fn__49201$process_query_and_save_execution_BANG___49210$fn__49213.invoke(query_processor.clj:249)" "query_processor$fn__49201$process_query_and_save_execution_BANG___49210.invoke(query_processor.clj:241)" "query_processor$fn__49245$process_query_and_save_with_max_results_constraints_BANG___49254$fn__49257.invoke(query_processor.clj:261)" "query_processor$fn__49245$process_query_and_save_with_max_results_constraints_BANG___49254.invoke(query_processor.clj:254)" "api.dataset$fn__52570$fn__52573.invoke(dataset.clj:59)" "query_processor.streaming$streaming_response_STAR_$fn__37098$fn__37099.invoke(streaming.clj:73)" "query_processor.streaming$streaming_response_STAR_$fn__37098.invoke(streaming.clj:72)" "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)" "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)" "async.streaming_response$do_f_async$fn__24643.invoke(streaming_response.clj:85)"], :context :ad-hoc, :error "No method in multimethod '->honeysql' for dispatch value: [:h2 :cum-sum]", :row_count 0, :running_time 0, :preprocessed {:database 4, :query {:expressions {:CustomColumn [:+ 1 1]}, :source-metadata [{:table_id 11, :special_type :type/CreationTimestamp, :unit :month, :name "CREATED_AT", :settings nil, :id 86, :display_name "Created At", :fingerprint {:global {:distinct-count 10001, :nil% 0.0}, :type {:type/DateTime {:earliest "2016-05-01T00:56:13.352Z", :latest "2020-04-19T20:07:15.657Z"}}}, :base_type :type/DateTime} {:name "sum", :display_name "Sum of Total", :base_type :type/Float, :special_type :type/Income, :settings nil}], :fields [[:field-literal "CREATED_AT" :type/DateTime] [:field-literal "sum" :type/Float] [:expression "CustomColumn"]], :limit 2000, :source-query {:source-table 11, :aggregation [[:aggregation-options [:cum-sum [:field-id 88]] {:name "sum"}]], :breakout [[:datetime-field [:field-id 86] :month]], :order-by [[:asc [:datetime-field [:field-id 86] :month]]]}}, :type :query, :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}, :info {:executed-by 1, :context :ad-hoc, :nested? false, :query-hash [11, -117, -105, 7, 125, -69, 83, 46, -49, -77, 63, 2, 121, 35, 30, 12, 21, -72, -46, 44, -19, -17, 100, -118, 3, 106, -112, -102, 35, 31, 21, -101]}, :constraints {:max-results 10000, :max-results-bare-rows 2000}}, :data {:rows [], :cols []}} 2020-10-29 23:13:28,557 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 1.3 s (7 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (4 idle, 0 queued) (53 total active threads) Queries in flight: 1 (0 queued) ``` </details> **Information about your Metabase Installation:** Metabase 0.34.3, 0.35.4, 0.36.8.2, 0.37.0.2 **Additional context** Likely very related to #12762 and possibly a little to #13437 :arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment

But it fails when there's an aggregation after the calculation not Datetime specific