Dashboard filter is not working

bach1922 · April 22, 2024, 1:11pm

Hey there, I am trying to make a basic filter to select the data range, below is the query

SELECT
    om.date,
    om.new_test_added_in_notion,
    SUM(sm.ticket_done) AS total_ticket_done
FROM
    overall_metrics om [[WHERE {{date_filter}}]]
LEFT JOIN
    squad_metrics sm ON om.date = sm.date
GROUP BY
    om.date, om.new_test_added_in_notion
ORDER BY
    om.date;

The issue I am facing is when I am any selection by using the data filter, the dashboard I built with the GUI was working ,but all other graph that built with SQL query is not.. show below

there is my variable setting

If I turn on a defult value and select any time range, I got this error

ERROR: syntax error at or near "LEFT" Position: 303

I really can't see anything wrong with the query, any help would be much appreciated !!

Please see Dignostic info


{
  "browser-info": {
    "language": "en",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.22+7",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.22",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.22+7",
    "os.name": "Linux",
    "os.version": "4.19.0-26-amd64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "2.1.214 (2022-06-13)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "2.1.214 (2022-06-13)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2024-04-16",
      "tag": "v0.49.6",
      "hash": "5abf130"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

and logs below

   "SELECT\n    om.date,\n    om.new_test_added_in_notion,\n    SUM(sm.ticket_done) AS total_ticket_done\nFROM\n    overall_metrics om [[WHERE {{date_filter}}]]\nLEFT JOIN\n    squad_metrics sm ON om.date = sm.date\nGROUP BY\n    om.date, om.new_test_added_in_notion\nORDER BY\n    om.date;"},
  :parameters
  [{:id "3ba151a2-ebf0-411f-870b-dd664fae08a1",
    :type "date/range",
    :value "2024-04-08~2024-04-22",
    :target ["dimension" ["template-tag" "date_filter"]],
    :options nil}],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class org.postgresql.util.PSQLException,
 :stacktrace
 ["org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)"
  "org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)"
  "org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)"
  "org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)"
  "org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)"
  "org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)"
  "org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)"
  "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1471)"
  "--> driver.sql_jdbc.execute$fn__79321.invokeStatic(execute.clj:556)"
  "driver.sql_jdbc.execute$fn__79321.invoke(execute.clj:554)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:569)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:565)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__79404$fn__79405.invoke(execute.clj:699)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__79404.invoke(execute.clj:698)"
  "driver.sql_jdbc.execute$fn__79197$fn__79198.invoke(execute.clj:388)"
  "driver.sql_jdbc.execute$do_with_resolved_connection.invokeStatic(execute.clj:334)"
  "driver.sql_jdbc.execute$do_with_resolved_connection.invoke(execute.clj:317)"
  "driver.sql_jdbc.execute$fn__79197.invokeStatic(execute.clj:382)"
  "driver.sql_jdbc.execute$fn__79197.invoke(execute.clj:380)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:692)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:678)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:689)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:678)"
  "driver.sql_jdbc$fn__106743.invokeStatic(sql_jdbc.clj:78)"
  "driver.sql_jdbc$fn__106743.invoke(sql_jdbc.clj:76)"
  "query_processor.context$executef.invokeStatic(context.clj:60)"
  "query_processor.context$executef.invoke(context.clj:49)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:44)"
  "query_processor.context.default$default_runf.invoke(default.clj:42)"
  "query_processor.context$runf.invokeStatic(context.clj:46)"
  "query_processor.context$runf.invoke(context.clj:40)"
  "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:39)"
  "query_processor.reducible$identity_qp.invoke(reducible.clj:36)"
  "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___72236.invoke(cache.clj:229)"
  "query_processor.middleware.permissions$check_query_permissions$fn__66593.invoke(permissions.clj:140)"
  "query_processor.middleware.enterprise$check_download_permissions_middleware$fn__72057.invoke(enterprise.clj:51)"
  "query_processor.middleware.enterprise$maybe_apply_column_level_perms_check_middleware$fn__72067.invoke(enterprise.clj:64)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__71499.invoke(mbql_to_native.clj:24)"
  "query_processor$fn__73404$combined_post_process__73409$combined_post_process_STAR___73410.invoke(query_processor.clj:262)"
  "query_processor$fn__73404$combined_pre_process__73405$combined_pre_process_STAR___73406.invoke(query_processor.clj:259)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__66690.invoke(fetch_source_query.clj:303)"
  "query_processor.middleware.resolve_database_and_driver$resolve_driver_and_database_local_values$fn__72147$fn__72151.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__72147.invoke(resolve_database_and_driver.clj:76)"
  "query_processor.middleware.store$initialize_store$fn__67317$fn__67318.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__67317.invoke(store.clj:13)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database$fn__72144.invoke(resolve_database_and_driver.clj:60)"
  "query_processor.middleware.normalize_query$normalize$fn__72449.invoke(normalize_query.clj:38)"
  "query_processor.middleware.enterprise$fn__72084$handle_audit_app_internal_queries__72085$fn__72087.invoke(enterprise.clj:96)"
  "query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__72095.invoke(enterprise.clj:103)"
  "query_processor.middleware.constraints$mark_needs_default_userland_constraints$fn__71210.invoke(constraints.clj:104)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__72380.invoke(process_userland_query.clj:156)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__72981.invoke(catch_exceptions.clj:171)"
  "query_processor.reducible$async_qp$qp_STAR___62831$thunk__62833.invoke(reducible.clj:126)"
  "query_processor.reducible$async_qp$qp_STAR___62831.invoke(reducible.clj:132)"
  "query_processor.reducible$sync_qp$qp_STAR___62843.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__93912.invoke(dataset.clj:79)"
  "query_processor.streaming$streaming_response_STAR_$fn__53261$fn__53263.invoke(streaming.clj:168)"
  "query_processor.streaming$streaming_response_STAR_$fn__53261.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__43756.invoke(streaming_response.clj:88)"],
 :card_id nil,
 :context :ad-hoc,
 :error "ERROR: syntax error at or near \"LEFT\"\n  Position: 303",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}

TonyC · April 22, 2024, 1:17pm

You cannot use Aliases with field filters

bach1922 · April 22, 2024, 1:59pm

Hey Tony, thanks for your quick reply ! I tried to remove the alias and below is the updated query

SELECT
    overall_metrics.date,
    overall_metrics.new_test_added_in_notion,
    SUM(squad_metrics.ticket_done) AS total_ticket_done
FROM
    overall_metrics [[WHERE overall_metrics.date = {{date}}]]
LEFT JOIN
    squad_metrics ON overall_metrics.date = squad_metrics.date

GROUP BY
    overall_metrics.date, overall_metrics.new_test_added_in_notion
ORDER BY
    overall_metrics.date;

and it's still not working, and when I remove the square bracket [ and give a value it shows

ERROR: syntax error at or near "LEFT" Position: 360

But I really can't see anything wrong..

TonyC · April 22, 2024, 2:03pm

For field filters you don't have to use this syntax

Checkout this article Field Filters: create smart filter widgets for SQL questions

bach1922 · April 22, 2024, 4:15pm

Hey @TonyC ,thanks for the reply! I've went through this article before and also tried out , not sure if I understand what do you mean.. I tried both WHERE overall {{date}} and WHERE overall_metrics.date = {{date}} and the result were the same..

bach1922 · April 22, 2024, 7:46pm

I solved it by change the query to

LEFT JOIN

squad_metrics ON overall_metrics.date = squad_metrics.date [[WHERE {{date}}]]

looks like the [[WHERE {{date}}]] needs to be right after the data section of the field ,so either after From or data join..