Version client-v2-0.9.4 changes order of query settings as URL params in CH HTTP request

[shrisha-c]

Once we upgraded from client-v2-0.9.0 to client-v2-0.9.4 we are seeing that in the final HTTP request made to clickhouse the order of the URL params is changing resulting in the change in behavior.

Since clickhouse processes the query settings in the same order, giving preference to the parameter coming later, in our case we are seeing the profile setting parameter going to the end resulting in the change in query result.

Query string in version 0.9.0

http://localhost:18123?query_id=ddcb9fb8-b875-477f-b221-839b6ae2b375_http-nio-4010-exec-10_143&compress=1&role=event_interactive_reader&profile=event_interactive_reader_profile&prefer_global_in_and_join=0&log_comment=; role_s: [event_interactive_reader]; profile_s: event_interactive_reader_profile

Query string in version 0.9.4
http://localhost:18123?query_id=2dd8034a-c44f-4524-a51d-9022cb50b402_http-nio-4010-exec-1_449&compress=1&role=event_interactive_reader&log_comment=; role_s: [event_interactive_reader]; profile_s: event_interactive_reader_profile&prefer_global_in_and_join=1&profile=event_interactive_reader_profile

If you see the profile parameter is going to the end resulting in the settings from the profile not allowing to override the prefer_global_in_and_join=1 value. Its defaulting to the value in the setting.

In version 0.9.0 the order of adding the setting to QuerySettings through serverSetting method was retained, but its breaking in 0.9.4

Steps to reproduce

Have a profile settings and some query setting overriding the value in setting. Set the query setting on QuerySettings at the end in version 0.9.0 this is preserved in the final URL, but its not in 0.9.4.

Error Log or Exception StackTrace

No errors as such, but due to the. different query settings being applied the result from clickhouse would vary. In our case the value of prefer_global_in_and_join was changing resulting in no data being returned.

Expected Behaviour

Code Example

 QuerySettings querySettings = new QuerySettings();
querySettings.setFormat(clickHouseFormat);
querySettings.setDBRoles(Set.of("event_interactive_reader"));

String readSettingsProfile = "event_interactive_reader_profile";
settings.put("profile", "event_interactive_reader_profile");
settings.put("prefer_global_in_and_join", "1");

querySettings.logComment("; role_s: [event_interactive_reader]; profile_s: event_interactive_reader_profile");
querySettings.setQueryId("2dd8034a-c44f-4524-a51d-9022cb50b402_http-nio-4010-exec-1_449");
QueryResponse queryResponse = client.query("select * from database.events", querySettings).get(getClientSocketTimeout(client), TimeUnit.MILLISECONDS);

If on the profile event_interactive_reader_profile if prefer_global_in_and_join is 0 then in client version 0.9.4 it will always be 0 as profile ends up in the end.

Configuration

Client Configuration

com.clickhouse.client.api.Client client = new com.clickhouse.client.api.Client.Builder()
                .addEndpoint(endpoint)
                .setUsername(username)
                .setPassword(password)
                .setMaxConnections(10)
             
                .setConnectionTTL(getPropertyOrDefault("clickhouse.client.connection.ttlMillis", 0L, Long::valueOf), ChronoUnit.MILLIS)
                .setKeepAliveTimeout(getPropertyOrDefault("clickhouse.client.keepAlive.timeoutMillis", -1L, Long::valueOf), ChronoUnit.MILLIS)
                .setConnectionReuseStrategy(ConnectionReuseStrategy.valueOf(getPropertyOrDefault("clickhouse.client.connection.reuseStrategy", "LIFO", String::valueOf)))
                .setConnectTimeout(getPropertyOrDefault("clickhouse.client.connection.timeoutMillis", 5000L, Long::valueOf), ChronoUnit.MILLIS)
                .setSocketTimeout(getPropertyOrDefault("clickhouse.client.default.socketTimeoutMillis", 30000L, Long::valueOf), ChronoUnit.MILLIS)
                .build();

Environment

• Cloud
• Client version: client-v2-0.9.4
• Language version: Java
• OS: Debian

ClickHouse Server

• ClickHouse Server version: 25.3
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[chernser]

Good day, @shrisha-c !

Thank you for reporting! We will look into it.

[chernser]

@shrisha-c

Internally we were using HashMap so it a bit strange that order of settings has changed.
And I think, I do not understand the problem because different values are passed for prefer_global_in_and_join:

0.9.0
http://localhost:18123?
    query_id=ddcb9fb8-b875-477f-b221-839b6ae2b375_http-nio-4010-exec-10_143
    &compress=1
    &role=event_interactive_reader
    &profile=event_interactive_reader_profile
    &prefer_global_in_and_join=0
    &log_comment=; role_s: [event_interactive_reader]; profile_s: event_interactive_reader_profile
    
    
0.9.4 

http://localhost:18123?
    query_id=2dd8034a-c44f-4524-a51d-9022cb50b402_http-nio-4010-exec-1_449
    &compress=1
    &role=event_interactive_reader
    &log_comment=; role_s: [event_interactive_reader]; profile_s: event_interactive_reader_profile
    &prefer_global_in_and_join=1
    &profile=event_interactive_reader_profile
    

I'd understand if settings have same name. Here it looks like question to DB why it handles prefer_global_in_and_join differently depending on order with profile setting.

[shrisha-c]

@chernser Sorry for the change in the value of prefer_global_in_and_join, however our usage of client sends the same value, but with new client the order changes. I was experimenting with different values and might have pasted the one with wrong value here.

You are right, I see that even in version 0.9.0 there was no guarantee in the client code to preserve the ordering, but it used to work because the map.keyset() would iterate over the same order we insert to the map.

However since 0.9.4, in Client.java

clickhouse-java/client-v2/src/main/java/com/clickhouse/client/api/Client.java

Line 1632 in 59d9678

final QuerySettings requestSettings = new QuerySettings(buildRequestSettings(settings.getAllSettings()));

we are combining all config properties on the client to the same QuerySettings resulting in loss of order due to other properties. This is the change I noticed between 0.9.4 and 0.9.0 which might have caused the issue.

Usually the order of parameters passed to CH does not matter, however if we have profiles and if profile is passed at the end it overrides the other query level settings, if the setting is defined in the profile.

Refer clickhouse documentation -https://clickhouse.com/docs/operations/settings/settings-profiles
https://clickhouse.com/docs/operations/settings/query-level

Response from Gemini on CH handling of profile setting and why order matters.

[chernser]

@shrisha-c
I see.
I've put into the planning but this feature have to wait some time.