Fix KQL and PRQL distributed tables queries

[kothiga]

Changelog category (leave one):

• Bug Fix (user-visible misbehavior in an official stable release)

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Fixed a minor bug that prevented distributed table queries sent from either KQL or PRQL dialect clients to be executed on replicas.

Documentation entry for user-facing changes

This PR address #56289 and #57378 which found that queries using a distributed table with the kusto dialect resulted in a syntax error.

Root cause of this issue seems to be with how the queries are passed onto replicas for execution. A query such as

distributed_table | limit 2

is transformed to

SELECT * FROM distributed_table LIMIT 2

When executing this, the query is sent to connected replicas, which includes a modified copy of the settings. Within this, was the inclusion of the dialect change, which caused the replica to try and execute an SQL query with the KQL parser. Modifying the dialect prior to distribution resolves this issue.

This PR depends on #59626 being merged prior.

Edit: As observed below, this bug also extends to PRQL for the same reason. Adjustments have been made to just blanket set the dialect to ClickHouse-SQL before querying replicas.

Information about CI checks: https://clickhouse.com/docs/en/development/continuous-integration/

[robot-ch-test-poll1]

This is an automated comment for commit e23dd5b with description of existing statuses. It's updated for the latest CI running

❌ Click here to open a full report in a separate page

Successful checks

Check name	Description	Status
A Sync	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
Docs check	Builds and tests the documentation	✅ success
Style check	Runs a set of checks to keep the code style clean. If some of tests failed, see the related log from the report	✅ success

Check name	Description	Status
Bugfix validation	Checks that either a new test (functional or integration) or there some changed tests that fail with the binary built on master branch	❌ failure
CI running	A meta-check that indicates the running CI. Normally, it's in success or pending state. The failed status indicates some problems with the PR	⏳ pending
ClickHouse build check	Builds ClickHouse in various configurations for use in further steps. You have to fix the builds that fail. Build logs often has enough information to fix the error, but you might have to reproduce the failure locally. The cmake options can be found in the build log, grepping for cmake. Use these options and follow the general build process	⏳ pending
Fast test	Normally this is the first check that is ran for a PR. It builds ClickHouse and runs most of stateless functional tests, omitting some. If it fails, further checks are not started until it is fixed. Look at the report to see which tests fail, then reproduce the failure locally as described here	❌ failure
Mergeable Check	Checks if all other necessary checks are successful	❌ failure

[alexey-milovidov]

This looks wrong. Why only Kusto?
What about the PRQL dialect?

[kothiga]

I will investigate. I hadn't seen any issues submitted for PRQL having the same issue with distributed tables, and thought maybe the dialect was doing something else making it unaffected. I will report what I find.

[kothiga]

I can confirm that this issue also effects PRQL. Ex. error output

Received exception from server (version 23.12.2):
Code: 62. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse03:9000. DB::Exception: PRQL syntax error: 'Error:
   ╭─[:1:1]
   │
 1 │ SELECT `shared_test_table`.`id` FROM `default`.`shared_test_table`
   │ ───┬──
   │    ╰──── Unknown name
───╯
'. (SYNTAX_ERROR)

Here, we can see the same behavior, of the transformed SQL statement trying to be executed in the incorrect dialect.

I will extend my change and add appropriate tests.

[alexey-milovidov]

Missing tests.

[alexey-milovidov]

Also, write a test with PRQL.

[alexey-milovidov]

This code is non-generic.

[kothiga]

I had the change that was made in 01e203d in an earlier commit, and ultimately changed it to what I had because of backwards compatibility tests throwing a Unknown setting dialect.

I definitely agree that the generic form of blanket setting the dialect is the better way to go.

[alexey-milovidov]

.

[alexey-milovidov]

I've simplified the code.

[alexey-milovidov]

Sorry, editing through web UI was a bad idea. Continued here: #60470

[alexey-milovidov]

Done.