JSON vs. NamedValue query parameters

[lcapka]

Hello,

we are working with JSON data and calling function such as jsonMergePatch(). Example:

SELECT jsonMergePatch('{"test":' || test_value || '}') FROM some_table WHERE id = ?

The query has a query parameter. The problem is that this query fails with error described as "expected string value in NamedValue for query parameter". While looking into the code we have discovered that there is some magic regular expression which tries to detect named query arguments in the file https://github.com/ClickHouse/clickhouse-go/blob/main/query_parameters.go#L30C2-L30C18.

hasQueryParamsRe = regexp.MustCompile("{.+:.+}")

We found that the regex does not hit when the query is multiline so we managed to run it successfully by putting closing bracket on the next line:

SELECT jsonMergePatch('{"test":' || test_value || 
   '}') FROM some_table WHERE id = ?

We would like to ask you how we should approach? Is it a bug in the regular expression or we shall run the query somehow differently?

Note that we are not using NamedValue, the query parameter is put to the Query(query, args...) just as a common Go variable.

Thanks.

[SpencerTorres]

Hey! Thanks for submitting this issue. I agree with your findings that this is likely a problem with the regex.

I want to understand this query better so I can reproduce the error and try to find a workaround. Can you provide an anonymized snippet of code as well as the table you're using? Is test_value a column?

[holycheater]

a simple snippet as an example:

db, _ := clickhouse.Open(&clickhouse.Options{
	Auth: clickhouse.Auth{Username: "default", Password: "test"},
})
row := db.QueryRow(context.TODO(), "SELECT '{' as one, ':' as two, '}' as three WHERE ?", 1)
fmt.Println(row.Err())

output: expected string value in NamedValue for query parameter

Basically if you need braces and semicolon in your query for some specific use-case - you get an error

[holycheater]

https://github.com/ClickHouse/clickhouse-go/blob/main/bind.go#L63-L64
Also, a similar things happens with numeric args

Positional args allow escaping with \?, and numeric don't allow escaping with \$1

[SpencerTorres]

Basically if you need braces and semicolon in your query for some specific use-case - you get an error

Wow that sucks. Yeah we need to fix that regex then. Thanks for the extra info 👌

[lcapka]

Thanks for update! The regex mentioned above does just a detection that there are named args in the query string. It's not doing actual binding. The binding part is in a different part of the code and there is another regex for matching the name.

I haven't checked but my blind guess is to replace RE by something like {@[a-zA-Z0-9\_]+:[^"']+}. It seems that the name has specific name limits and also the data type for shouldn't contain quotes or double quotes. And that could be enough to make this working well. The reasoning here is that JSON data shall contain double-quoted names (before colon) while NamedParameters don't and also in mentioned cases above there are characters like spaces that should not be part of the name of NamedParameter. Similarly the data type - I guess should not contain quotes chars.