search

pyrooka / metabase-cubejs-driver

Connect Metabase with Cube.js.
GNU Affero General Public License v3.0
4 stars 6 forks source link

Distincts generate wrong query #30

Closed norbertbede closed 4 years ago

norbertbede commented 5 years ago

steps to reproduce

  1. connection to cubejs eg. invoices
  2. open table invoices
  3. select the table
  4. on first column dropdown menu and select DISTINCTS <== ERROR
distincts-01 distincts-02 distincts-03 distincts-04

LOG from metabaseserver `11-25 14:36:56 DEBUG middleware.log :: POST /api/dataset 200 [ASYNC: completed] 2.2 s (16 DB calls) Jetty threads: 3/50 (2 idle, 0 queued) (45 total active threads) Queries in flight: 0 11-25 14:37:49 WARN middleware.process-userland-query :: Query failure {:status :failed, :class clojure.lang.ExceptionInfo, :error "clj-http: status 400", :stacktrace ("slingshot.support$stack_trace.invoke(support.clj:199)" "clj_http.client$exceptions_response.invokeStatic(client.clj:245)" "clj_http.client$exceptions_response.invoke(client.clj:236)" "clj_http.client$wrap_exceptions$fn54887.invoke(client.clj:254)" "clj_http.client$wrap_accept$fn55090.invoke(client.clj:737)" "clj_http.client$wrap_accept_encoding$fn55097.invoke(client.clj:759)" "clj_http.client$wrap_content_type$fn__55084.invoke(client.clj:720)" "clj_http.client$wrap_form_params$fn55183.invoke(client.clj:961)" "clj_http.client$wrap_nested_params$fn55204.invoke(client.clj:995)" "clj_http.client$wrap_flatten_nested_params$fn55213.invoke(client.clj:1019)" "clj_http.client$wrap_method$fn55151.invoke(client.clj:895)" "clj_http.cookies$wrap_cookies$fn53920.invoke(cookies.clj:131)" "clj_http.links$wrap_links$fn54715.invoke(links.clj:63)" "clj_http.client$wrap_unknown_host$fn55221.invoke(client.clj:1048)" "--> driver.cubejs.utils$make_request.invokeStatic(utils.clj:41)" "driver.cubejs.utils$make_request.invoke(utils.clj:32)" "driver.cubejs.query_processor$execute_http_request.invokeStatic(query_processor.clj:38)" "driver.cubejs.query_processor$execute_http_request.invoke(query_processor.clj:36)" "driver.cubejs$fn563.invokeStatic(cubejs.clj:170)" "driver.cubejs$fn__563.invoke(cubejs.clj:169)" "query_processor$fn44023$execute_query44028$fn44029.invoke(query_processor.clj:71)" "query_processor$fn44023$execute_query44028.invoke(query_processor.clj:65)" "query_processor.middleware.mbql_to_native$mbqlGT_native$fn34284.invoke(mbql_to_native.clj:38)" "query_processor.middleware.annotate$result_rows_mapsGT_vectors$fn__36550.invoke(annotate.clj:541)" "query_processor.middleware.annotate$add_column_info$fn36456.invoke(annotate.clj:485)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn37491.invoke(cumulative_aggregations.clj:57)" "query_processor.middleware.resolve_joins$resolve_joins$fn41131.invoke(resolve_joins.clj:184)" "query_processor.middleware.limit$limit$fn38092.invoke(limit.clj:19)" "query_processor.middleware.results_metadata$record_and_return_metadataBANG$fn43890.invoke(results_metadata.clj:87)" "query_processor.middleware.format_rows$format_rows$fn38080.invoke(format_rows.clj:26)" "query_processor.middleware.add_dimension_projections$add_remapping$fn35043.invoke(add_dimension_projections.clj:234)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn35684.invoke(add_source_metadata.clj:107)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__41181.invoke(resolve_source_table.clj:46)" "query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn35531.invoke(add_row_count_and_status.clj:16)" "query_processor.middleware.driver_specific$process_query_in_context$fn37567.invoke(driver_specific.clj:12)" "query_processor.middleware.add_settings$add_settings$fn35554.invoke(add_settings.clj:45)" "query_processor.middleware.resolve_driver$resolve_driver$fn40795.invoke(resolve_driver.clj:22)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn36881$fn36882.invoke(bind_effective_timezone.clj:9)" "util.date$call_with_effective_timezone.invokeStatic(date.clj:88)" "util.date$call_with_effective_timezone.invoke(date.clj:77)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn36881.invoke(bind_effective_timezone.clj:8)" "query_processor.middleware.store$initialize_store$fn43915$fn43916.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$fn43915.invoke(store.clj:10)" "query_processor.middleware.async$asyncGT_sync$fn34195.invoke(async.clj:23)" "query_processor.middleware.async_wait$runnable$fn36607.invoke(async_wait.clj:89)"), :query {:query {:source-table 1750, :aggregation [["distinct" ["field-id" 51320]]]}, :type "query", :parameters [], :async? true, :middleware {:add-default-userland-constraints? true, :userland-query? true}, :info {:executed-by 1, :context :ad-hoc, :card-id nil, :nested? false, :query-hash [-106, 16, -54, 35, -40, 77, -47, -122, -19, -12, -72, -43, -115, -95, 67, 56, -25, 36, 5, 54, -36, -17, 78, -124, -124, 107, 97, 39, 79, -24, 32, -4]}, :constraints {:max-results 10000, :max-results-bare-rows 2000}}, :preprocessed {:constraints {:max-results 10000, :max-results-bare-rows 2000}, :type :query, :middleware {:add-default-userland-constraints? true, :userland-query? true}, :info {:executed-by 1, :context :ad-hoc, :nested? false, :query-hash [-106, 16, -54, 35, -40, 77, -47, -122, -19, -12, -72, -43, -115, -95, 67, 56, -25, 36, 5, 54, -36, -17, 78, -124, -124, 107, 97, 39, 79, -24, 32, -4]}, :preprocessing-level 1, :database 3, :driver :cubejs, :query {:source-table 1750, :aggregation [[:aggregation-options [:distinct [:field-id 51320]] {:name "count"}]]}}, :native {:query nil, :aggregation? true, :mbql? true}, :ex-data {:object {:cached nil, :request-time 239, :repeatable? false, :protocol-version {:name "HTTP", :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x5fdf8000 "org.apache.http.impl.client.InternalHttpClient@5fdf8000"], :chunked? false, :type :clj-http.client/unexceptional-status, :reason-phrase "Bad Request", :headers {"X-Powered-By" "Express", "Access-Control-Allow-Origin" "", "Content-Type" "application/json; charset=utf-8", "Content-Length" "35", "ETag" "W/\"23-RTUP5Q13NiVoMZnFul0uWEsLi5M\"", "Date" "Mon, 25 Nov 2019 13:37:48 GMT", "Connection" "close", "Strict-Transport-Security" "max-age=15768000"}, :orig-content-encoding nil, :status 400, :length 35, :body "{\"error\":\"query param is required\"}", :trace-redirects []}, :environment {req {:url "https://cubesapi.cloudempiere.com/cubejs-api/v1/load", :headers {:authorization nil, "accept" "application/json"}, :query-params {"query" nil}, :as :json, :request-method :get, :flatten-nested-keys (:query-params)}, p__54882 {:cached nil, :request-time 239, :repeatable? false, :protocol-version {:name "HTTP", :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x5fdf8000 "org.apache.http.impl.client.InternalHttpClient@5fdf8000"], :chunked? false, :reason-phrase "Bad Request", :headers {"X-Powered-By" "Express", "Access-Control-Allow-Origin" "", "Content-Type" "application/json; charset=utf-8", "Content-Length" "35", "ETag" "W/\"23-RTUP5Q13NiVoMZnFul0uWEsLi5M\"", "Date" "Mon, 25 Nov 2019 13:37:48 GMT", "Connection" "close", "Strict-Transport-Security" "max-age=15768000"}, :orig-content-encoding nil, :status 400, :length 35, :body "{\"error\":\"query param is required\"}", :trace-redirects []}, map__54883 {:cached nil, :request-time 239, :repeatable? false, :protocol-version {:name "HTTP", :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x5fdf8000 "org.apache.http.impl.client.InternalHttpClient@5fdf8000"], :chunked? false, :reason-phrase "Bad Request", :headers {"X-Powered-By" "Express", "Access-Control-Allow-Origin" "", "Content-Type" "application/json; charset=utf-8", "Content-Length" "35", "ETag" "W/\"23-RTUP5Q13NiVoMZnFul0uWEsLi5M\"", "Date" "Mon, 25 Nov 2019 13:37:48 GMT", "Connection" "close", "Strict-Transport-Security" "max-age=15768000"}, :orig-content-encoding nil, :status 400, :length 35, :body "{\"error\":\"query param is required\"}", :trace-redirects []}, resp {:cached nil, :request-time 239, :repeatable? false, :protocol-version {:name "HTTP", :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x5fdf8000 "org.apache.http.impl.client.InternalHttpClient@5fdf8000"], :chunked? false, :reason-phrase "Bad Request", :headers {"X-Powered-By" "Express", "Access-Control-Allow-Origin" "", "Content-Type" "application/json; charset=utf-8", "Content-Length" "35", "ETag" "W/\"23-RTUP5Q13NiVoMZnFul0uWEsLi5M\"", "Date" "Mon, 25 Nov 2019 13:37:48 GMT", "Connection" "close", "Strict-Transport-Security" "max-age=15768000"}, :orig-content-encoding nil, :status 400, :length 35, :body "{\"error\":\"query param is required\"}", :trace-redirects []}, status 400, data {:cached nil, :request-time 239, :repeatable? false, :protocol-version {:name "HTTP", :major 1, :minor 1}, :streaming? true, :http-client #object[org.apache.http.impl.client.InternalHttpClient 0x5fdf8000 "org.apache.http.impl.client.InternalHttpClient@5fdf8000"], :chunked? false, :type :clj-http.client/unexceptional-status, :reason-phrase "Bad Request", :headers {"X-Powered-By" "Express", "Access-Control-Allow-Origin" "*", "Content-Type" "application/json; charset=utf-8", "Content-Length" "35", "ETag" "W/\"23-RTUP5Q13NiVoMZnFul0uWEsLi5M\"", "Date" "Mon, 25 Nov 2019 13:37:48 GMT", "Connection" "close", "Strict-Transport-Security" "max-age=15768000"}, :orig-content-encoding nil, :status 400, :length 35, :body "{\"error\":\"query param is required\"}", :trace-redirects []}}}}

11-25 14:37:50 DEBUG middleware.log :: POST /api/dataset 200 [ASYNC: completed] 1.5 s (29 DB calls) Jetty threads: 3/50 (3 idle, 0 queued) (43 total active threads) Queries in flight: 0`

norbertbede commented 5 years ago

similar result for Distribution command in same menu

11-25 14:50:06 WARN middleware.process-userland-query :: Query failure {:status :failed, :class java.lang.Exception, :error "Query processor error: mismatched number of columns in query and results. Expected 2 fields, got 1\nExpected: [\"Invoices.bpartner\" \"count\"]\nActual: [:Invoices.bpartner]", :stacktrace ("--> query_processor.middleware.annotate$check_correct_number_of_columns_returned.invokeStatic(annotate.clj:371)" "query_processor.middleware.annotate$check_correct_number_of_columns_returned.invoke(annotate.clj:364)" "query_processor.middleware.annotate$fn__36396.invokeStatic(annotate.clj:437)" "query_processor.middleware.annotate$fn__36396.invoke(annotate.clj:434)" "query_processor.middleware.annotate$fn__36434$add_column_info_STAR___36439$fn__36443.invoke(annotate.clj:475)" "query_processor.middleware.annotate$fn__36434$add_column_info_STAR___36439.invoke(annotate.clj:470)" "query_processor.middleware.annotate$add_column_info$fn__36456.invoke(annotate.clj:485)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__37491.invoke(cumulative_aggregations.clj:57)" "query_processor.middleware.resolve_joins$resolve_joins$fn__41131.invoke(resolve_joins.clj:184)" "query_processor.middleware.limit$limit$fn__38092.invoke(limit.clj:19)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__43890.invoke(results_metadata.clj:87)" "query_processor.middleware.format_rows$format_rows$fn__38080.invoke(format_rows.clj:26)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__35043.invoke(add_dimension_projections.clj:234)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__35684.invoke(add_source_metadata.clj:107)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__41181.invoke(resolve_source_table.clj:46)" "query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__35531.invoke(add_row_count_and_status.clj:16)" "query_processor.middleware.driver_specific$process_query_in_context$fn__37567.invoke(driver_specific.clj:12)" "query_processor.middleware.add_settings$add_settings$fn__35554.invoke(add_settings.clj:45)" "query_processor.middleware.resolve_driver$resolve_driver$fn__40795.invoke(resolve_driver.clj:22)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881$fn__36882.invoke(bind_effective_timezone.clj:9)" "util.date$call_with_effective_timezone.invokeStatic(date.clj:88)" "util.date$call_with_effective_timezone.invoke(date.clj:77)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881.invoke(bind_effective_timezone.clj:8)" "query_processor.middleware.store$initialize_store$fn__43915$fn__43916.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__43915.invoke(store.clj:10)" "query_processor.middleware.async$async__GT_sync$fn__34195.invoke(async.clj:23)" "query_processor.middleware.async_wait$runnable$fn__36607.invoke(async_wait.clj:89)"), :query {:type "query", :query {:source-table 1750, :aggregation [["count"]], :breakout [["field-id" 51320]]}, :parameters [], :async? true, :middleware {:add-default-userland-constraints? true, :userland-query? true}, :info {:executed-by 1, :context :ad-hoc, :card-id nil, :nested? false, :query-hash [-122, 27, -24, 14, -25, -33, -69, -48, -90, -62, 61, -84, -74, -45, -73, -41, 86, 100, 122, -26, -114, 0, 14, 14, -24, 77, -28, 35, -2, -120, 120, -80]}, :constraints {:max-results 10000, :max-results-bare-rows 2000}}, :preprocessed {:constraints {:max-results 10000, :max-results-bare-rows 2000}, :type :query, :middleware {:add-default-userland-constraints? true, :userland-query? true}, :info {:executed-by 1, :context :ad-hoc, :nested? false, :query-hash [-122, 27, -24, 14, -25, -33, -69, -48, -90, -62, 61, -84, -74, -45, -73, -41, 86, 100, 122, -26, -114, 0, 14, 14, -24, 77, -28, 35, -2, -120, 120, -80]}, :preprocessing-level 1, :database 3, :driver :cubejs, :query {:source-table 1750, :aggregation [[:aggregation-options [:count] {:name "count"}]], :breakout [[:field-id 51320]], :order-by [[:asc [:field-id 51320]]]}}, :native {:query {:dimensions ("Invoices.bpartner"), :order {"Invoices.bpartner" :asc}}, :aggregation? true, :mbql? true}}

pyrooka commented 4 years ago

I guess this is related to #11 but I will investigate this next week.

pyrooka commented 4 years ago

I just investigated the error and found the problem: this is an aggregation that cannot be implemented on the Metabase driver side. Aggregations are happening on the logic level in the Cube.js exclusively. Metabase is just a "frontend" for the Cube.js backend and the Cube.js doesn't support ad hoc aggregations, you have to define it in the schema. For more info: https://cube.dev/docs/types-and-formats#measures-types-count-distinct