* in from clause causes syntax error
olafbuitelaar opened this issue · 3 comments
Describe the bug
when i try to create a question for ClickHouse, with a from function containing * with a variable, metabase seems to produce a syntax error.
Steps to reproduce
create a question with query and variable XXX
select
columnA,
sum(arrayCount(x -> x == 'abc',Events.Name)) as eventAbc
from s3('https://s3.url.location/x/*/*/*/*/*.parquet','admin','default') as data_source
where XXX={{XXX}}
group by columnA
Expected behaviour
the * in the function parameter (string) not causing syntax errors
Error log
Configuration
Environment
- Metabase Version: v0.48.3
- Hosted on K8s
ClickHouse server
- ClickHouse Server version: 23.12.2.59 (official build)
I tried to compose a minimal repro. Is it the same error?
(using a public dataset from here)
SELECT *
FROM s3('https://datasets-documentation.s3.amazonaws.com/github/commits/*/commits.tsv.xz', 'TSV', 'hash String,author LowCardinality(String), time DateTime, message String, files_added UInt32, files_deleted UInt32, files_renamed UInt32, files_modified UInt32, lines_added UInt32, lines_deleted UInt32, hunks_added UInt32, hunks_removed UInt32, hunks_changed UInt32')
WHERE 1=1 [[AND 'd98b7d731555ae78ce7cdd7c7f4c8f8e302e71aa' = {{x}}]]
LIMIT 10
produces
Code: 62. DB::Exception: Syntax error: failed at position 511 (''d98b7d731555ae78ce7cdd7c7f4c8f8e302e71aa'') (line 4, col 17): 'd98b7d731555ae78ce7cdd7c7f4c8f8e302e71aa' = {{x}}]] LIMIT 10. Expected one of: token, Dot, Comma, ClosingSquareBracket, OR, AND, IS NOT DISTINCT FROM, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, alias, AS. (SYNTAX_ERROR) (version 23.8.9.54 (official build)) , server ClickHouseNode [uri=http://clickhouse:8123/default, options={use_server_time_zone_for_dates=true,use_no_proxy=false,product_name=metabase/1.3.3}]@1745067332
Actual minimal repro is
SELECT toString('/*/')
WHERE 1=1 [[AND '42' = {{x}}]]
LIMIT 10
For some reason, /*/
in combination with a variable is not working as expected. I will look into it.
Similar behavior with MySQL, i.e., confirmed Metabase bug.
SELECT concat('/*/')
WHERE 1=1 [[AND '42' = {{x}}]]
LIMIT 10
unknown escape sequence {{x}}
metabase/metabase#38051 will be re-opened.