Error when reusing a model that has comment at the end of it

Bug reports
1

The context
At my company we have created a set a models that are duplicable for every database we add to Metabase. These models are based one upon another. So model B depends on model A to work.

The problem
When model A SQL request ends with a comment, it makes model B crash. However, when the comment is not at the end of SQL request, it works just fine.

Example
This works :

MODEL A

select id, name
from fruits
-- where name like 'banana'
where name like 'potatoe'

MODEL B

select *
from {{#234}} as fruits -- where 234 is the id of MODEL A
join shopping_centers on shopping_centers.product_id = fruits.id

This does not work:

MODEL A

select id, name
from fruits
where name like 'banana'
-- where name like 'potatoe'

MODEL B

select *
from {{#234}} as fruits -- where 234 is the id of MODEL A
join shopping_centers on shopping_centers.product_id = fruits.id
2

Is it rebelling against your spelling of 'potato' :rofl::rofl:

You're in good company: https://www.youtube.com/watch?v=dKVbt2ugTJE

More seriously, what do you seen when you click 'view SQL'?

3

A potatoe is a mix of a common eatable thing from which we make French fries, and the fingers' feet of someone you dislike :stuck_out_tongue_winking_eye:. Not english-native speaker sorry.

Here's the capture I have
image

So it seems to me the comment is not considered as a comment, but rather like normal SQL code.

Here's the full log error found in logs :

[9c8849c0-0d76-46c5-bf73-9f1c773b1e71] 2022-08-09T12:25:32+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: ERROR: syntax error at end of input
  Position: 178
{:database_id 19,
 :started_at #t "2022-08-09T10:25:32.096193Z[Etc/UTC]",
 :via
 [{:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error "Error executing query: ERROR: syntax error at end of input\n  Position: 178",
   :stacktrace
   ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__55031.invoke(execute.clj:502)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
    "driver.sql_jdbc$fn__84993.invokeStatic(sql_jdbc.clj:54)"
    "driver.sql_jdbc$fn__84993.invoke(sql_jdbc.clj:52)"
    "query_processor.context$executef.invokeStatic(context.clj:59)"
    "query_processor.context$executef.invoke(context.clj:48)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:67)"
    "query_processor.context.default$default_runf.invoke(default.clj:65)"
    "query_processor.context$runf.invokeStatic(context.clj:45)"
    "query_processor.context$runf.invoke(context.clj:39)"
    "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)"
    "query_processor.reducible$identity_qp.invoke(reducible.clj:9)"
    "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___52408.invoke(cache.clj:220)"
    "query_processor.middleware.permissions$check_query_permissions$fn__47931.invoke(permissions.clj:109)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51349.invoke(mbql_to_native.clj:23)"
    "query_processor$fn__53884$combined_post_process__53889$combined_post_process_STAR___53890.invoke(query_processor.clj:211)"
    "query_processor$fn__53884$combined_pre_process__53885$combined_pre_process_STAR___53886.invoke(query_processor.clj:208)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52329$fn__52334.invoke(resolve_database_and_driver.clj:35)"
    "driver$do_with_driver.invokeStatic(driver.clj:75)"
    "driver$do_with_driver.invoke(driver.clj:71)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52329.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48363.invoke(fetch_source_query.clj:346)"
    "query_processor.middleware.store$initialize_store$fn__48551$fn__48552.invoke(store.clj:11)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
    "query_processor.store$do_with_store.invoke(store.clj:38)"
    "query_processor.middleware.store$initialize_store$fn__48551.invoke(store.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__52601.invoke(normalize_query.clj:22)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__49633.invoke(constraints.clj:53)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__52540.invoke(process_userland_query.clj:145)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__52912.invoke(catch_exceptions.clj:167)"
    "query_processor.reducible$async_qp$qp_STAR___44771$thunk__44773.invoke(reducible.clj:100)"
    "query_processor.reducible$async_qp$qp_STAR___44771.invoke(reducible.clj:106)"
    "query_processor.reducible$async_qp$qp_STAR___44771.invoke(reducible.clj:91)"
    "query_processor.reducible$sync_qp$qp_STAR___44782.doInvoke(reducible.clj:126)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:330)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:326)"
    "query_processor$fn__53931$process_query_and_save_execution_BANG___53940$fn__53943.invoke(query_processor.clj:341)"
    "query_processor$fn__53931$process_query_and_save_execution_BANG___53940.invoke(query_processor.clj:334)"
    "query_processor$fn__53975$process_query_and_save_with_max_results_constraints_BANG___53984$fn__53987.invoke(query_processor.clj:353)"
    "query_processor$fn__53975$process_query_and_save_with_max_results_constraints_BANG___53984.invoke(query_processor.clj:346)"
    "api.dataset$run_query_async$fn__68659.invoke(dataset.clj:69)"
    "query_processor.streaming$streaming_response_STAR_$fn__39889$fn__39890.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__39889.invoke(streaming.clj:161)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
    "async.streaming_response$do_f_async$task__19006.invoke(streaming_response.clj:84)"],
   :error_type :invalid-query,
   :ex-data
   {:sql
    "-- Metabase:: userID: 17 queryType: native queryHash: 9ad4da639c20f25cad27ad8e2670eaeb3c7d2cfc6dbd593236a7f7d6e9d55668\nselect *\nfrom (select *\nfrom decidim_users\n-- where admin)",
    :params [],
    :type :invalid-query}}],
 :state "42601",
 :error_type :invalid-query,
 :json_query
 {:type "native",
  :native
  {:query "select *\nfrom {{#2760}}",
   :template-tags
   {:#2760
    {:id "1581e937-667f-7204-5636-32de253ab484", :name "#2760", :display-name "#2760", :type "card", :card-id 2760}}},
  :database 19,
  :parameters [],
  :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:2675)"
  "org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)"
  "org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)"
  "org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)"
  "org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)"
  "org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)"
  "org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)"
  "org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)"
  "org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)"
  "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
  "--> driver.sql_jdbc.execute$fn__54951.invokeStatic(execute.clj:367)"
  "driver.sql_jdbc.execute$fn__54951.invoke(execute.clj:365)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:375)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:372)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__55031.invoke(execute.clj:500)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
  "driver.sql_jdbc$fn__84993.invokeStatic(sql_jdbc.clj:54)"
  "driver.sql_jdbc$fn__84993.invoke(sql_jdbc.clj:52)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:67)"
  "query_processor.context.default$default_runf.invoke(default.clj:65)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)"
  "query_processor.reducible$identity_qp.invoke(reducible.clj:9)"
  "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___52408.invoke(cache.clj:220)"
  "query_processor.middleware.permissions$check_query_permissions$fn__47931.invoke(permissions.clj:109)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51349.invoke(mbql_to_native.clj:23)"
  "query_processor$fn__53884$combined_post_process__53889$combined_post_process_STAR___53890.invoke(query_processor.clj:211)"
  "query_processor$fn__53884$combined_pre_process__53885$combined_pre_process_STAR___53886.invoke(query_processor.clj:208)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52329$fn__52334.invoke(resolve_database_and_driver.clj:35)"
  "driver$do_with_driver.invokeStatic(driver.clj:75)"
  "driver$do_with_driver.invoke(driver.clj:71)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52329.invoke(resolve_database_and_driver.clj:34)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48363.invoke(fetch_source_query.clj:346)"
  "query_processor.middleware.store$initialize_store$fn__48551$fn__48552.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
  "query_processor.store$do_with_store.invoke(store.clj:38)"
  "query_processor.middleware.store$initialize_store$fn__48551.invoke(store.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__52601.invoke(normalize_query.clj:22)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__49633.invoke(constraints.clj:53)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__52540.invoke(process_userland_query.clj:145)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__52912.invoke(catch_exceptions.clj:167)"
  "query_processor.reducible$async_qp$qp_STAR___44771$thunk__44773.invoke(reducible.clj:100)"
  "query_processor.reducible$async_qp$qp_STAR___44771.invoke(reducible.clj:106)"
  "query_processor.reducible$async_qp$qp_STAR___44771.invoke(reducible.clj:91)"
  "query_processor.reducible$sync_qp$qp_STAR___44782.doInvoke(reducible.clj:126)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:330)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:326)"
  "query_processor$fn__53931$process_query_and_save_execution_BANG___53940$fn__53943.invoke(query_processor.clj:341)"
  "query_processor$fn__53931$process_query_and_save_execution_BANG___53940.invoke(query_processor.clj:334)"
  "query_processor$fn__53975$process_query_and_save_with_max_results_constraints_BANG___53984$fn__53987.invoke(query_processor.clj:353)"
  "query_processor$fn__53975$process_query_and_save_with_max_results_constraints_BANG___53984.invoke(query_processor.clj:346)"
  "api.dataset$run_query_async$fn__68659.invoke(dataset.clj:69)"
  "query_processor.streaming$streaming_response_STAR_$fn__39889$fn__39890.invoke(streaming.clj:162)"
  "query_processor.streaming$streaming_response_STAR_$fn__39889.invoke(streaming.clj:161)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
  "async.streaming_response$do_f_async$task__19006.invoke(streaming_response.clj:84)"],
 :card_id nil,
 :context :ad-hoc,
 :error "ERROR: syntax error at end of input\n  Position: 178",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}
4

Hi @dynnammo
Because Metabase applies parenthesis around {{#234}}, which effectively means that your comment is escaping the ) as fruits causing invalid query.
You'll be interested in this:
https://github.com/metabase/metabase/issues/6096 - upvote by clicking :+1: on the first post

1 Like
5

I have same issue . So what could i do to fix it ??

6

Basically I deleted the comment ^^.

Haven't tried it but you could also use the other way you comment comment in SQL i.e.

select*
from fruits
where name like 'banana'
/* where name like 'potatoe' */
1 Like
7

Thanks you so much. It works for me <3

1 Like