Question marks '?' break queries with filters on Clickhouse #56364
Description
Describe the bug
When a query, which contains a question mark ? and a Metabase filter is executed agaisnt a Clickhouse database, the ? is replaced by null and the query execution fails.
To Reproduce
- Prepare the following query in Metabase (using new SQL Query) and select a Clickhouse database
WITH data as (SELECT '1' as `field?`) SELECT * FROM data WHERE `field?` = {{data_filter}} - Set the data_filter to type string and value 1
- Execute the query
- Query fails with the following error
Code: 47. DB::Exception: Unknown expression or function identifier `fieldnull` in scope WITH data AS (SELECT '1' AS `field'1'`) SELECT * FROM data WHERE fieldnull = NULL. (UNKNOWN_IDENTIFIER) (version 24.9.3.128 (official build)) When either the ? or WHERE clause is removed, the query is executed successfully.
This behavior cannot be reproduced with a similar query against a PostgreSQL database.
Expected behavior
The Query returns '1'
Logs
[265d845a-15aa-42e8-be8b-f002a2f60a46] 2025-04-07T08:34:03+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Code: 47. DB::Exception: Unknown expression or function identifier `fieldnull` in scope WITH data AS (SELECT '1' AS `field'1'`) SELECT * FROM data WHERE fieldnull = NULL. (UNKNOWN_IDENTIFIER) (version 24.9.3.128 (official build)) {:database_id 2, :parameterized true, :started_at #t "2025-04-07T06:34:03.196225398Z[GMT]", :via [{:status :failed, :class java.sql.SQLException, :error "Code: 47. DB::Exception: Unknown expression or function identifier `fieldnull` in scope WITH data AS (SELECT '1' AS `field'1'`) SELECT * FROM data WHERE fieldnull = NULL. (UNKNOWN_IDENTIFIER) (version 24.9.3.128 (official build)) ", :stacktrace ["com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:64)" "com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:39)" "com.clickhouse.jdbc.StatementImpl.executeQuery(StatementImpl.java:195)" "com.clickhouse.jdbc.StatementImpl.executeQuery(StatementImpl.java:143)" "com.clickhouse.jdbc.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:115)" "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:743)" "--> driver.sql_jdbc.execute$fn__86277.invokeStatic(execute.clj:566)" "driver.sql_jdbc.execute$fn__86277.invoke(execute.clj:564)" "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:579)" "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:575)" "driver.sql_jdbc.execute$execute_reducible_query$fn__86354$fn__86355.invoke(execute.clj:721)" "driver.sql_jdbc.execute$execute_reducible_query$fn__86354.invoke(execute.clj:720)" "driver.clickhouse$fn__117139$fn__117141.invoke(clickhouse.clj:115)" "driver.sql_jdbc.execute$do_with_resolved_connection86125__86126.invokeStatic(execute.clj:338)" "driver.sql_jdbc.execute$do_with_resolved_connection86125__86126.invoke(execute.clj:321)" "driver.clickhouse$fn__117139.invokeStatic(clickhouse.clj:92)" "driver.clickhouse$fn__117139.invoke(clickhouse.clj:90)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:714)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:700)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:711)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:700)" "driver.sql_jdbc$fn__118061.invokeStatic(sql_jdbc.clj:79)" "driver.sql_jdbc$fn__118061.invoke(sql_jdbc.clj:77)" "query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:52)" "query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:39)" "query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:102)" "query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:95)" "query_processor.execute$run.invokeStatic(execute.clj:62)" "query_processor.execute$run.invoke(execute.clj:56)" "query_processor.middleware.update_used_cards$update_used_cards_BANG_74108__74109$fn__74110.invoke(update_used_cards.clj:60)" "query_processor.execute$add_native_form_to_result_metadata$fn__74213.invoke(execute.clj:25)" "query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__74219.invoke(execute.clj:36)" "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___74081.invoke(cache.clj:241)" "query_processor.middleware.permissions$check_query_permissions$fn__73847.invoke(permissions.clj:148)" "query_processor.middleware.enterprise$check_download_permissions_middleware$fn__73948.invoke(enterprise.clj:57)" "query_processor.middleware.enterprise$maybe_apply_column_level_perms_check_middleware$fn__73958.invoke(enterprise.clj:70)" "query_processor.execute$execute74246__74247$fn__74248.invoke(execute.clj:94)" "query_processor.setup$do_with_qp_setup74199__74200.invokeStatic(setup.clj:225)" "qu... Information about your Metabase installation
{ "browser-info": { "language": "de-DE", "platform": "MacIntel", "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Safari/537.36", "vendor": "Google Inc." }, "metabase-info": { "databases": [ "postgres", "clickhouse" ], "run-mode": "prod", "plan-alias": "", "version": { "date": "2025-03-31", "tag": "v0.53.8.6", "hash": "b6c0667" }, "settings": { "report-timezone": "Europe/Zurich" }, "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "14.6 (Ubuntu 14.6-1.pgdg22.04+1)" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.7.4" } } }, "system-info": { "file.encoding": "UTF-8", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "21.0.6+7-LTS", "java.vendor": "Eclipse Adoptium", "java.vendor.url": "https://adoptium.net/", "java.version": "21.0.6", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "21.0.6+7-LTS", "os.name": "Linux", "os.version": "5.10.226-214.879.amzn2.x86_64", "user.language": "en", "user.timezone": "GMT" } }Severity
Blocking an Upgrade
Additional context
Related issue for metabase-clickhouse-driver: ClickHouse/metabase-clickhouse-driver#309