Substitution variables break query when using s3 wildcards

[lucasvanbramer]

Describe the bug

x-posted from here

Using both

• a wildcard inside a Clickhouse S3 Parquet query
• a Metabase template variable

breaks all queries with the error Expected substitution name (identifier). (SYNTAX_ERROR).

Steps to reproduce

Assuming you're using a real S3 parquet file path

This works:

(in Metabase UI, some_value = 'abcd')
SELECT col1 FROM s3('s3://my_bucket/my_file.parquet','Parquet') where col1 = {{ some_value }};

This works:

SELECT col1 FROM s3('s3://my_bucket/*.parquet','Parquet') where col1 = 'abcd';

This does not work:

(in Metabase UI, some_value = 'abcd')
SELECT col1 FROM s3('s3://my_bucket/*.parquet','Parquet') where col1 = {{ some_value }};

Yields the following error: Code: 62. DB::Exception: Syntax error: failed at position 276 ('{') (line 3, col 20): {some_value}}. Expected substitution name (identifier). (SYNTAX_ERROR) (version 24.8.4.13 (official build))

Expected behavior

The query should execute without a syntax error.

Error log

{:type "native",
  :database 274,
  :native
  {:query
   "with a as ( SELECT user_uuid\n    FROM\n      s3('s3://censored_path/*.parquet','Parquet')\n  )\nselect * from a where user_uuid = {{ some }}",
   :template-tags
   {:some {:type "text", :name "some", :id "68ef3abe-e843-4b97-b1f7-287ed184ac17", :display-name "Some"}}},
  :parameters
  [{:id "68ef3abe-e843-4b97-b1f7-287ed184ac17",
    :type "category",
    :value "1234",
    :target ["variable" ["template-tag" "some"]]}],
  :middleware {:js-int-to-string? true, :userland-query? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class java.io.IOException,
 :stacktrace
 ["com.clickhouse.client.http.HttpUrlConnectionImpl.checkResponse(HttpUrlConnectionImpl.java:203)"
  "com.clickhouse.client.http.HttpUrlConnectionImpl.post(HttpUrlConnectionImpl.java:246)"
  "com.clickhouse.client.http.ClickHouseHttpClient.send(ClickHouseHttpClient.java:200)"
  "com.clickhouse.client.AbstractClient.execute(AbstractClient.java:280)"
  "com.clickhouse.client.ClickHouseClientBuilder$Agent.sendOnce(ClickHouseClientBuilder.java:282)"
  "com.clickhouse.client.ClickHouseClientBuilder$Agent.send(ClickHouseClientBuilder.java:294)"
  "com.clickhouse.client.ClickHouseClientBuilder$Agent.execute(ClickHouseClientBuilder.java:349)"
  "com.clickhouse.client.ClickHouseClient.executeAndWait(ClickHouseClient.java:878)"
  "com.clickhouse.client.ClickHouseRequest.executeAndWait(ClickHouseRequest.java:2154)"
  "com.clickhouse.jdbc.internal.ClickHouseStatementImpl.getLastResponse(ClickHouseStatementImpl.java:137)"
  "com.clickhouse.jdbc.internal.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:492)"
  "com.clickhouse.jdbc.internal.ClickHouseStatementImpl.execute(ClickHouseStatementImpl.java:480)"
  "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
  "--> driver.sql_jdbc.execute$fn__81085.invokeStatic(execute.clj:569)"
  "driver.sql_jdbc.execute$fn__81085.invoke(execute.clj:567)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:577)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:574)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__81166$fn__81167.invoke(execute.clj:714)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__81166.invoke(execute.clj:713)"
  "driver.sql_jdbc.execute$fn__80959$fn__80960.invoke(execute.clj:397)"
  "driver.sql_jdbc.execute$do_with_resolved_connection.invokeStatic(execute.clj:337)"
  "driver.sql_jdbc.execute$do_with_resolved_connection.invoke(execute.clj:320)"
  "driver.sql_jdbc.execute$fn__80959.invokeStatic(execute.clj:391)"
  "driver.sql_jdbc.execute$fn__80959.invoke(execute.clj:389)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:707)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:693)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:704)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:693)"
  "driver.sql_jdbc$fn__114725.invokeStatic(sql_jdbc.clj:78)"
  "driver.sql_jdbc$fn__114725.invoke(sql_jdbc.clj:76)"
  "query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:47)"
  "query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:34)"
  "query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:88)"
  "query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:81)"
  "query_processor.execute$run.invokeStatic(execute.clj:61)"
  "query_processor.execute$run.invoke(execute.clj:55)"
  "query_processor.execute$add_native_form_to_result_metadata$fn__69662.invoke(execute.clj:24)"
  "query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__69667.invoke(execute.clj:35)"
  "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69653.invoke(cache.clj:242)"
  "query_processor.middleware.permissions$check_query_permissions$fn__63729.invoke(permissions.clj:118)"
  "metabase_enterprise.advanced_permissions.query_processor.middleware.permissions$fn__109354$check_download_permissions__109355$fn__109356.invoke(permissions.clj:90)"
  "query_processor.middleware.enterprise$check_download_permissions_middleware$fn__64305.invoke(enterprise.clj:51)"
  "metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$fn__111193$maybe_apply_column_level_perms_check__111194$fn__111195.invoke(column_level_perms_check.clj:38)"
  "query_processor.middleware.enterprise$maybe_apply_column_level_perms_check_middleware$fn__64315.invoke(enterprise.clj:64)"
  "query_processor.execute$execute$fn__69694.invoke(execute.clj:93)"
  "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:225)"
  "query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
  "query_processor.execute$execute.invokeStatic(execute.clj:92)"
  "query_processor.execute$execute.invoke(execute.clj:88)"
  "query_processor$process_query_STAR__STAR_.invokeStatic(query_processor.clj:47)"
  "query_processor$process_query_STAR__STAR_.invoke(query_processor.clj:43)"
  "metabase_enterprise.audit_app.query_processor.middleware.handle_audit_queries$fn__81535$handle_audit_app_internal_queries__81536$fn__81537.invoke(handle_audit_queries.clj:145)"
  "query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__64343.invoke(enterprise.clj:103)"
  "query_processor.middleware.process_userland_query$process_userland_query_middleware$fn__75512.invoke(process_userland_query.clj:182)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__75581.invoke(catch_exceptions.clj:128)"
  "query_processor$process_query$fn__75618.invoke(query_processor.clj:78)"
  "query_processor.setup$do_with_canceled_chan$fn__64747.invoke(setup.clj:189)"
  "query_processor.setup$do_with_database_local_settings$fn__64742.invoke(setup.clj:181)"
  "query_processor.setup$do_with_driver$fn__64737$fn__64738.invoke(setup.clj:166)"
  "driver$do_with_driver.invokeStatic(driver.clj:104)"
  "driver$do_with_driver.invoke(driver.clj:99)"
  "query_processor.setup$do_with_driver$fn__64737.invoke(setup.clj:165)"
  "query_processor.setup$do_with_metadata_provider$fn__64730$fn__64733.invoke(setup.clj:151)"
  "query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:171)"
  "query_processor.store$do_with_metadata_provider.invoke(store.clj:151)"
  "query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:160)"
  "query_processor.store$do_with_metadata_provider.invoke(store.clj:151)"
  "query_processor.setup$do_with_metadata_provider$fn__64730.invoke(setup.clj:150)"
  "query_processor.setup$do_with_resolved_database$fn__64724.invoke(setup.clj:128)"
  "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:232)"
  "query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
  "query_processor$process_query.invokeStatic(query_processor.clj:76)"
  "query_processor$process_query.invoke(query_processor.clj:69)"
  "api.dataset$run_streaming_query$fn__97912.invoke(dataset.clj:84)"
  "query_processor.streaming$_streaming_response$fn__68139$fn__68140$fn__68141.invoke(streaming.clj:175)"
  "query_processor.streaming$_streaming_response$fn__68139$fn__68140.invoke(streaming.clj:174)"
  "query_processor.streaming$do_with_streaming_rff.invokeStatic(streaming.clj:165)"
  "query_processor.streaming$do_with_streaming_rff.invoke(streaming.clj:152)"
  "query_processor.streaming$_streaming_response$fn__68139.invoke(streaming.clj:171)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:68)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:66)"
  "async.streaming_response$do_f_async$task__52169.invoke(streaming_response.clj:87)"],
 :card_id nil,
 :context :ad-hoc,
 :error
 "Code: 62. DB::Exception: Syntax error: failed at position 303 ('{') (line 6, col 36): { some }}. Expected substitution name (identifier). (SYNTAX_ERROR) (version 24.8.4.13 (official build))\n",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}

Configuration

Environment

• metabase-clickhouse-driver version: 1.50.7
• OS:

  "browser-info": {
  "language": "en-US",
  "platform": "MacIntel",
  "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36",
  "vendor": "Google Inc."
  },

ClickHouse server

• ClickHouse Server version:
  • 24.8.4.13
• Sample data for all these tables, use [clickhouse-obfuscator]
  • any S3 parquet file

[slvrtrn]

This seems to be similar to https://github.com/ClickHouse/metabase-clickhouse-driver/issues/220, where the cause was https://github.com/metabase/metabase/issues/38051

I see that the related PR was set to be released in 0.50.21