Company or project name

No response

Use case

Provide a way for tools to reformat queries exactly as ClickHouse does.
Provide a way to modify queries programmatically, so that various UI tools can do interactive sorting and pagination, filtering, data export, etc.

Describe the solution you'd like

It will be done with the EXPLAIN TEXT query. You provide the original query and ways to modify it:

EXPLAIN TEXT
SELECT a, b c FROM table FORMAT TSV  -- the source query, could be in parentheses
MODIFY WHERE ADD c = 1,  -- modification action, it will add a condition to the AND chain in WHERE
MODIFY FORMAT CSV,   -- modification action, it will change FORMAT from TSV to CSV in the query
MULTILINE   -- modification action for query formatting
FORMAT JSON   -- the format of the EXPLAIN query, not related to the rewritten query

The main query text could be in parentheses.
The modification actions are listed as comma-separated.
In the case of subqueries, the modifications apply to the outermost one.

This EXPLAIN query produces a result with a single row and a single column with the rewritten text.

The actions are as follows:

ONELINE/MULTILINE - how to format the query

KEEP SPACE AND COMMENTS is an interesting option. While formatting the query, it tries to keep track of the original contents on a best-effort basis and inserts the original whitespace and comments in the corresponding places. It allows query rewrite in a user-friendly way.

MODIFY FORMAT - changes format to the specified, adding the format clause if it does not exist;
MODIFY LIMIT - modifies LIMIT and, possibly, OFFSET
MODIFY OFFSET
PAGE n - modifies OFFSET to LIMIT * (n - 1)
MODIFY ORDER BY - replaces ORDER BY with the new one
PREPEND ORDER BY - adds elements to the front of ORDER BY if they didn't already present
ADD SELECT expr, ...
REPLACE SELECT expr1 WITH expr2
REMOVE SELECT expr
ADD WHERE expr
REMOVE WHERE expr
...

Keep in mind that MODIFY ORDER BY or MODIFY WHERE, and similar, mean "modify the query", rather than "apply this transformation to the result".

For example, if we have a query

SELECT * FROM table ORDER BY x LIMIT 100

and we want to change the order to descending:

EXPLAIN TEXT
(SELECT * FROM table ORDER BY x LIMIT 100)
MODIFY ORDER BY (x DESC), KEEP SPACE AND COMMENTS
FORMAT JSONEachRow

it will produce:

{"text":"SELECT * FROM table ORDER BY x DESC LIMIT 100"}

It is not the same as sorting the result, which would be:

SELECT * FROM (SELECT * FROM table ORDER BY x LIMIT 100) ORDER BY x DESC

Describe alternatives you've considered

I thought we could add a special handler in the HTTP interface, but it would be much more limited and less consistent:

• works only in the HTTP interface;
• requires a new logic for access control, formatting, etc, rather than reusing the existing logic;
• no support for query_log, accounting, etc;
• less extensible.

See the discussion around the selected design here: #29922.

Additional context

This task should not be confused with API for query construction, #46925.

This task should not be confused with out-of-band query modifiers with settings, such as additional_table_filters, additional_result_filter, limit, offset. You can see that none of them serve the purpose of rewriting the original query.