How the ClickHouse Client Splits a Script into Queries

TL;DR: clickhouse-client --queries-file script.sql does not split the file on ;. It parses one statement at a time, lets the parser tell it where the statement ended, ends inline INSERT data at a blank line, and then walks forward over the semicolons and the trailing comment so the -- { serverError 42 } hint stays attached to the statement it describes.

Splitting a SQL script into statements sounds trivial. It is not, and the reasons are all in the corners of the language:

A semicolon can be data: INSERT INTO t VALUES ('a;b').
A semicolon can be part of a format’s data: INSERT INTO t FORMAT CSV 1,2;3 — is that the number 3 or the end of the statement?
A semicolon can be inside a comment: -- nothing; to see here.
A semicolon can be inside a string: SELECT ';'.
A statement can have no semicolon at all: the last line of the file.
A statement can be followed by a comment that belongs to it: SELECT 1; -- { serverError 42 } — and the client’s test framework depends on that comment being attached to the right statement.

Here is a script that exercises all of it:

sql
-- script.sql
SELECT 1;                     -- statement 1, terminated by ';'
SELECT 'a;b';                 -- the ';' is inside a string: still one statement
INSERT INTO t FORMAT CSV
1,2;3
                              -- a blank line ends the payload, not the ';'
SELECT 3                      -- statement 4, no trailing ';' at all

And the pipeline the client runs to get there:

text
   script text
       |
       v
   parseQuery(one statement)   -- the parser advances the pointer itself
       |
       v
   INSERT with inline data?  --yes-->  VALUES: the real VALUES format parser
       |                                 other formats: find the next "\n\n"
       |no
       v
   adjustQueryEnd: skip the ';'s, pull in the trailing comment
       |
       v
   execute  |  check the "-- { serverError N }" hint

Every SQL client solves this differently, and every solution leaks somewhere. psql uses a lexer. mysql uses a lexer with a mode stack. ClickHouse’s client uses a parser — it asks the real SQL parser where the statement ends — and then applies a small number of special cases for the places where the parser cannot know.

The entry point

--queries-file is a plain option:

cpp
// src/Client/ClientBase.cpp:4678
        ("query,q", po::value<std::vector<std::string>>()->multitoken(), R"(Query. Can be specified multiple times (--query "SELECT 1" --query "SELECT 2") or once with multiple semicolon-separated queries (--query "SELECT 1; SELECT 2;"). In the latter case, INSERT queries with non-VALUE format must be separated by empty lines.)")
        ("queries-file", po::value<std::vector<std::string>>()->multitoken(), "File path with queries to execute; multiple files can be specified (--queries-file file1 file2...)")
        ("multiquery,n", "Obsolete, does nothing")

Note the multiquery flag: it is documented as obsolete and does nothing, because the client has always been able to run a multi-statement script. The flag survives only so that old scripts do not break.

Several files can be given, and each can be preceded by “interleave” files that are run before it — a mechanism for setting up state:

cpp
// programs/client/Client.cpp:1211
        ("interleave-queries-file",

The whole thing is driven from runNonInteractive:

cpp
// src/Client/ClientBase.cpp:5385
    if (!buzz_house && !queries_files.empty())
    {
        for (const auto & queries_file : queries_files)
        {
            for (const auto & interleave_file : interleave_queries_files)
                if (!processMultiQueryFromFile(interleave_file))
                    return;

            if (!processMultiQueryFromFile(queries_file))
                return;
        }

        return;
    }

and processMultiQueryFromFile reads the whole file into memory and hands it to the multi-statement executor:

cpp
// src/Client/ClientBase.cpp:5356
bool ClientBase::processMultiQueryFromFile(const String & file_name)
{
    if (isEmbeeddedClient())
        throw Exception(ErrorCodes::SUPPORT_IS_DISABLED, "Reading queries from file is not supported for an embedded client");

    String queries_from_file;

    ReadBufferFromFile in(file_name);
    readStringUntilEOF(queries_from_file, in);

    /// For `clickhouse-local` only: same entry point as `-q` / stdin so meta-commands (`clear`, `ls`,
    /// `\i`, …) work for whole-file input. Remote `clickhouse-client` keeps `executeMultiQuery` so
    /// `--queries-file` does not apply `exit_strings` and other text-level metas (avoids silent
    /// behavior changes for batch automation).
    if (supportsLocalMetaCommands())
        return processQueryText(queries_from_file);
    return executeMultiQuery(queries_from_file);
}

The difference at the end is a deliberate compatibility decision: clickhouse-local treats a file as “text typed at the prompt”, so \i and clear work inside it; clickhouse-client treats it as a script, and running it through the meta-command processor could change the meaning of an existing pipeline.

The same function is the implementation of the interactive \i command, so “read this file and run it” is one code path everywhere.

The splitting rule, as the code states it

The rule the client actually implements is written down in a comment, right where the multi-statement executor is set up:

cpp
// src/Client/ClientBase.cpp:3587
    /// Several queries separated by ';'.
    /// INSERT data is ended by the empty line (\n\n), not ';'.
    /// Unnecessary semicolons may cause data to be parsed containing ';'
    /// e.g. 'insert into xx format csv val;' will insert "val;" instead of "val"
    ///      'insert into xx format csv val\n;' will insert "val" and ";"
    /// An exception is VALUES format where we also support semicolon in
    /// addition to end of line.

That comment is the whole specification, and it is worth reading twice, because the third line is the surprising one: insert into xx format csv val; inserts the string val; — the semicolon is data. The only reliable separator for a data-carrying INSERT is a blank line.

The executor is a loop over a state machine with four states:

cpp
// src/Client/ClientBase.cpp:3607
    while (true)
    {
        auto stage = analyzeMultiQueryText(this_query_begin, this_query_end, all_queries_end,
                                           parsed_query, current_exception);
        switch (stage)
        {
            case MultiQueryProcessingStage::QUERIES_END:
            {
                /// Compatible with old version when run interactive, e.g. "", "\ld"
                if (is_first && is_interactive)
                {
                    auto u = processTextAsSingleQuery(all_queries_text);
                    UNUSED(u);
                }
                return true;
            }

QUERIES_END, PARSING_FAILED, CONTINUE_PARSING and PARSING_EXCEPTION are the four outcomes; the interesting one is EXECUTE_QUERY, which falls through the switch into the code that runs the statement.

Stage one: let the parser find the boundary

analyzeMultiQueryText is where the work happens. Its first job is to throw away the leading whitespace, so that the queries in the server’s query log do not start with a newline:

cpp
// src/Client/ClientBase.cpp:3340
    const char * raw_query_begin = this_query_begin;

    // Remove leading empty newlines and other whitespace, because they
    // are annoying to filter in the query log. This is mostly relevant for
    // the tests.
    while (this_query_begin < all_queries_end && isWhitespaceASCII(*this_query_begin))
        ++this_query_begin;

Its second job is to detect the “only comments left” case, which the parser cannot handle:

cpp
// src/Client/ClientBase.cpp:3354
    // If there are only comments left until the end of file, we just
    // stop. The parser can't handle this situation because it always
    // expects that there is some query that it can parse.
    // We can get into this situation because the parser also doesn't
    // skip the trailing comments after parsing a query. This is because
    // they may as well be the leading comments for the next query,
    // and it makes more sense to treat them as such.
    {
        Tokens tokens(this_query_begin, all_queries_end);
        IParser::Pos token_iterator(tokens, max_parser_depth, max_parser_backtracks);
        bool only_comments_left = !token_iterator.isValid();

and its third job — the actual split — is to hand the rest of the script to the parser and let it consume exactly one statement:

cpp
// src/Client/ClientBase.cpp:3384
    this_query_end = this_query_begin;
    try
    {
        parsed_query = parseQuery(this_query_end, all_queries_end,
            client_context->getSettingsRef(),
            /*allow_multi_statements=*/ true, raw_query_begin);
    }

parseQuery takes a const char *& and advances it past what it parsed. That is the trick: the parser is the splitter. The client does not look for semicolons; it asks ClickHouse’s own SQL parser to parse one statement and tells it where the end of the buffer is. A ; inside a string literal is inside the string literal, because the lexer said so.

The one thing the parser cannot do is know where a FORMAT CSV payload ends, because the payload is not SQL at all. That is handled separately, below.

Stage two: the INSERT data

After the parser returns, the client checks whether the statement is an INSERT with inline data, and if so, finds the end of the data:

cpp
// src/Client/ClientBase.cpp:3444
    // INSERT queries may have the inserted data in the query text that follow the query itself, e.g. "insert into t format CSV 1,2". They
    // need special handling.
    // - If the INSERT statement FORMAT is VALUES, we use the VALUES format parser to skip the inserted data until we reach the trailing single semicolon.
    // - Other formats (e.g. FORMAT CSV) are arbitrarily more complex and tricky to parse. For example, we may be unable to distinguish if the semicolon
    //   is part of the data or ends the statement. In this case, we simply assume that the end of the INSERT statement is determined by \n\n (two newlines).

VALUES gets the good treatment: the actual VALUES format parser is used to walk the rows, so a semicolon inside a string literal is handled correctly:

cpp
// src/Client/ClientBase.cpp:3461
        if (insert_ast->format == "Values")
        {
            // Invoke the VALUES format parser to skip the inserted data.
            // Skip SQL comments too (as ValuesBlockInputFormat::read does), not just
            // whitespace: a trailing comment after the last row would otherwise be scanned
            // as row data past the terminating ';', swallowing the following queries.
            ReadBufferFromMemory data_in(insert_ast->data, all_queries_end - insert_ast->data);
            skipBOMIfExists(data_in);
            do
            {
                skipWhitespaceAndSQLComments(data_in);
                if (data_in.eof() || *data_in.position() == ';')
                    break;
            }
            while (ValuesBlockInputFormat::skipToNextRow(&data_in, 1, 0));

Every other format gets the crude rule — a blank line ends the data:

cpp
// src/Client/ClientBase.cpp:3488
        else
        {
            // Handling of generic formats
            auto pos_newline = String(insert_ast->data, all_queries_end).find("\n\n");
            if (pos_newline != std::string::npos)
                this_query_end = insert_ast->data + pos_newline;
            else
                this_query_end = all_queries_end;
        }
        insert_ast->end = this_query_end;

This is the rule the option’s help text warns about: “In the latter case, INSERT queries with non-VALUE format must be separated by empty lines.”

It is a real limitation, and the reason is not laziness: a FORMAT CSV payload has no length, no terminator and no quoting rules the client could rely on. A semicolon in a CSV field is legal. A blank line is the only separator the client and the server can agree on without reimplementing every format’s parser.

Stage three: eat the semicolons and the trailing comment

Now the client knows where the statement ends, but there may be a semicolon and a trailing comment after it. The semicolon has to go, and the comment has to be kept — because the test framework puts the expected error code in it:

cpp
// src/Client/ClientBase.cpp:764
void ClientBase::adjustQueryEnd(
    const char *& this_query_end, const char * all_queries_end, uint32_t max_parser_depth, uint32_t max_parser_backtracks)
{
    // We have to skip the trailing semicolon that might be left
    // after VALUES parsing or just after a normal semicolon-terminated query.
    Tokens after_query_tokens(this_query_end, all_queries_end);
    IParser::Pos after_query_iterator(after_query_tokens, max_parser_depth, max_parser_backtracks);
    while (after_query_iterator.isValid() && after_query_iterator->type == TokenType::Semicolon)
    {
        this_query_end = after_query_iterator->end;
        ++after_query_iterator;
    }

Note that it skips all consecutive semicolons, so SELECT 1;; is one statement, not two.

Then the comment logic, which is the subtlest part of the whole file:

cpp
// src/Client/ClientBase.cpp:777
    // Now we have to do some extra work to add the trailing
    // same-line comment to the query, but preserve the leading
    // comments of the next query. The trailing comment is important
    // because the test hints are usually written this way, e.g.:
    // select nonexistent_column; -- { serverError 12345 }.
    // The token iterator skips comments and whitespace, so we have
    // to find the newline in the string manually. If it's earlier
    // than the next significant token, it means that the text before
    // newline is some trailing whitespace or comment, and we should
    // add it to our query. There are also several special cases
    // that are described below.
    const auto * newline = find_first_symbols<'\n'>(this_query_end, all_queries_end);
    const char * next_query_begin = after_query_iterator->begin;

    // We include the entire line if the next query starts after
    // it. This is a generic case of trailing in-line comment.
    // The "equals" condition is for case of end of input (they both equal
    // all_queries_end);
    if (newline <= next_query_begin)
    {
        chassert(newline >= this_query_end);
        this_query_end = newline;
    }
    else
    {
        // Many queries on one line, can't do anything. By the way, this
        // syntax is probably going to work as expected:
        // select nonexistent /* { serverError 12345 } */; select 1
    }

The rule is: if the next statement starts on a later line, everything up to the end of the current line belongs to this statement. If two statements share a line, the comment is ambiguous and the client gives up — with the comment above pointing at the syntax that does work.

This matters because of TestHint, the mechanism ClickHouse’s own test suite uses to assert on errors:

cpp
// src/Client/TestHint.h:17
/// Checks expected server and client error codes.
///
/// The following comment hints are supported:
///
/// - "-- { serverError 60 }" -- in case of you are expecting server error.
/// - "-- { serverError 16, 36 }" -- in case of you are expecting one of the 2 errors.
///
/// - "-- { clientError 20 }" -- in case of you are expecting client error.
/// - "-- { clientError 20, 60, 92 }" -- It's expected that the client will return one of the 3 errors.

and the hint is checked against the exception the server (or the client) produced:

cpp
// src/Client/ClientBase.cpp:3804
                    else if (test_hint.hasServerErrors())
                    {
                        if (!server_exception)
                        {
                            error_matches_hint = false;
                            error_stream << fmt::format("Expected server error code '{}' but got no server error (query: {}).\n",
                                                       test_hint.serverErrors(), full_query);
                        }

So the whole comment-attaching dance exists so that a test file can say “this statement must fail with error 60” and the client can check it. The hint is not a nicety; it is the assertion mechanism of the entire ClickHouse integration test suite.

The statement that is executed

After all of that, the client has three views of the same statement, and uses each for a different purpose:

cpp
// src/Client/ClientBase.cpp:3694
                // Save query without trailing comment
                auto query_to_execute = std::string_view(all_queries_text).substr(this_query_begin - all_queries_text.data(), this_query_end - this_query_begin);
                size_t insert_query_without_data_length = 0;
                if (const auto * insert = parsed_query->as<ASTInsertQuery>())
                    insert_query_without_data_length = insert->data - query_to_execute.data();

                // Try to include the trailing comment with test hints. It is just
                // a guess for now, because we don't yet know where the query ends
                // if it is an INSERT query with inline data. We will do it again
                // after we have processed the query. But even this guess is
                // beneficial so that we see proper trailing comments in "echo" and
                // server log.
                {
                    unsigned max_parser_depth = static_cast<unsigned>(client_context->getSettingsRef()[Setting::max_parser_depth]);
                    unsigned max_parser_backtracks = static_cast<unsigned>(client_context->getSettingsRef()[Setting::max_parser_backtracks]);
                    adjustQueryEnd(this_query_end, all_queries_end, max_parser_depth, max_parser_backtracks);
                }
                // query + inline INSERT data + trailing comments (the latter is our best guess for now).
                auto full_query = std::string_view(all_queries_text).substr(this_query_begin - all_queries_text.data(), this_query_end - this_query_begin);

query_to_execute — the statement without the trailing comment. This is what is sent to the server, so the comment never becomes part of the SQL.
full_query — the statement with the trailing comment. This is what is scanned for the test hint, and what is echoed.
parsed_query — the AST, which is what actually gets planned and sent.

And the script position is tracked for the server’s benefit:

cpp
// src/Client/ClientBase.cpp:3714
                ++script_query_number;
                script_line_number += std::count(prev_query_begin, this_query_begin, '\n');
                prev_query_begin = this_query_begin;
                client_context->setScriptQueryAndLineNumber(script_query_number, 1 + script_line_number);

so that an error message from the server can say “statement 7, line 42 of your script” rather than just quoting the statement.

The format follows the file extension

One more piece of “the client guesses” behaviour, and it is the one users notice first. The client infers the format from the file name — for the input, from the file the client is reading; for the output, from the file the query writes to.

For the input, the client has to ask the kernel what file descriptor 0 actually is:

cpp
// src/Common/tryGetFileNameByFileDescriptor.cpp:14
std::optional<String> tryGetFileNameFromFileDescriptor(int fd)
{
#ifdef OS_LINUX
    std::string proc_path = fmt::format("/proc/self/fd/{}", fd);
    char file_path[PATH_MAX] = {'\0'};
    if (readlink(proc_path.c_str(), file_path, sizeof(file_path) - 1) != -1)
        return file_path;
    return std::nullopt;
#elif defined(OS_DARWIN)
    char file_path[PATH_MAX] = {'\0'};
    if (fcntl(fd, F_GETPATH, file_path) != -1)
        return file_path;
    return std::nullopt;

readlink("/proc/self/fd/0") gives the original path of the redirected file, even through a pipe (/dev/stdin -> the real file), and F_GETPATH is the macOS equivalent. With that name in hand, the extension is mapped to a format:

cpp
// src/Formats/FormatFactory.cpp:1113
std::optional<String> FormatFactory::tryGetFormatFromFileName(String file_name)
{
    if (file_name == "stdin")
        return tryGetFormatFromFileDescriptor(STDIN_FILENO);

    CompressionMethod compression_method = chooseCompressionMethod(file_name, "");
    if (CompressionMethod::None != compression_method)
    {
        auto pos = file_name.find_last_of('.');
        if (pos != String::npos)
            file_name = file_name.substr(0, pos);
    }

    auto pos = file_name.find_last_of('.');
    if (pos == String::npos)
        return std::nullopt;

    String file_extension = file_name.substr(pos + 1, String::npos);
    boost::algorithm::to_lower(file_extension);
    auto it = file_extension_formats.find(file_extension);
    if (it == file_extension_formats.end())
        return std::nullopt;

    return it->second;
}

Three details: the compression suffix is stripped first (so data.csv.gz is a CSV file, not a “gz” file), the extension is lowercased (so DATA.CSV works), and an unknown extension returns nothing rather than guessing.

The client then uses it as the default input format:

cpp
// src/Client/ClientBase.cpp:1418
    else
    {
        std::optional<String> format_from_file_name = FormatFactory::instance().tryGetFormatFromFileDescriptor(stdin_fd);
        if (format_from_file_name)
            default_input_format = *format_from_file_name;
        else
            default_input_format = "auto";
    }

so clickhouse-client < data.csv reads CSV, clickhouse-client < data.jsonl reads JSONEachRow, and clickhouse-client < data.whatever falls back to auto (the format that sniffs the input).

The output side is the same trick, applied to INTO OUTFILE:

cpp
// src/Client/ClientBase.cpp:1124
            else if (query_with_output->out_file)
            {
                auto format_name = FormatFactory::instance().tryGetFormatFromFileName(out_file);
                if (format_name)
                {
                    current_format = *format_name;
                    outfile_format_from_extension = true;
                }
            }

SELECT * FROM t INTO OUTFILE 'out.json' writes JSON, because the file is called out.json. And the extension-derived format has a higher priority than the default_format setting, which is why the code needs a flag to remember where the format came from:

cpp
// src/Client/ClientBase.cpp:1146
            /// ... only when the client itself was not given an explicit output format. `--output-format`,
            /// `--format` and `--vertical` all set `is_default_format = false` (the last via
            /// `default_output_format = "Vertical"`), and such an explicit choice must win over the
            /// `default_format` *setting* — e.g. the display default the local client now seeds as a
            /// setting. Without this guard, `clickhouse-local --vertical` is silently overridden by that
            /// setting and prints TSV instead of Vertical.
            else if (is_default_format && !has_format_clause && !outfile_format_from_extension
                && !format_settings_ref[Setting::default_format].value.empty())
                current_format = format_settings_ref[Setting::default_format];

The full precedence chain is: an explicit FORMAT clause or --format wins; then the output_format/format settings; then the file extension; then the default_format setting; then the client’s built-in default.

Trade-offs

What the design buys.

Correct splitting for free. Because the real parser does the split, the client agrees with the server about where a statement ends in every case the SQL grammar can express — including semicolons inside strings, inside comments, and inside CASE ... WHEN ... THEN ... END.
No lexer to maintain. A hand-written lexer would have to reimplement the SQL string, identifier and comment rules, and would drift from the server’s.
Errors are attributable. The script query number and line number are tracked and sent to the server, so a failure in a 5 000-line script points at a line.
The test framework rides on it. The -- { serverError 42 } hint mechanism is a direct consequence of attaching the trailing comment to the right statement.
Formats are inferred. clickhouse-client < data.csv and INTO OUTFILE 'x.json' both work without a --format flag, because the client asks the kernel for the file name and looks at the extension.

What it costs.

The whole file is in memory. readStringUntilEOF reads the entire script before running anything. A 2 GB script is a 2 GB allocation.
The INSERT ... FORMAT rule is crude. Everything except VALUES ends at a blank line, so a CSV payload cannot contain a blank line, and a payload whose last row is followed by a single newline runs to the end of the file. The VALUES case is much better, because it uses the real format parser.
Two statements on one line lose their comment. select 1 /* { serverError 5 } */; select 2 cannot be disambiguated, and the client says so in a comment rather than guessing.
The extension inference can surprise you. INTO OUTFILE 'data.txt' writes whatever format txt maps to — or, if txt is not registered, it silently keeps the default format. The inference is a convenience with a sharp edge, and the sharp edge is that the mapping is global and configurable by whatever formats the build has.

A parse error aborts the rest of the script unless --ignore-error is given, in which case the client resynchronises by skipping to the next semicolon:

cpp
// src/Client/ClientBase.cpp:3419
            else
            {
                Tokens tokens(this_query_begin, all_queries_end);
                IParser::Pos token_iterator(tokens, max_parser_depth, max_parser_backtracks);
                while (token_iterator->type != TokenType::Semicolon && token_iterator.isValid())
                    ++token_iterator;
                this_query_begin = token_iterator->end;
            }

which is another place where a ; inside a string would confuse the resynchronisation.

The one thing to remember: the client does not split scripts on semicolons. It parses them, one statement at a time, and lets the parser tell it where the boundary is. The semicolon handling and the blank-line rule exist only for the two cases the parser cannot see: inline INSERT payloads and trailing comments.

The part people get wrong: INSERT ... FORMAT CSV data is not terminated by a semicolon, and the client will happily insert the ; as data. If your loader produces a file with a trailing semicolon after the last row, the semicolon is now a column value. Put a blank line after the payload, or use VALUES.