Skip to content

Support of the block of totals #297

Open

Description

@nicktrandafil

With the SQL:

SELECT name
FROM workflow_jobs        
GROUP BY name
    WITH TOTALS
LIMIT 5, 5

clickhosue-cpp responds with exception unimplemented 7. Would be nice to have this feature.

Activity

  1. nicktrandafil commented on Apr 14, 2023

    @nicktrandafil
    Author

    Is there a different way to obtain row count in a SQL which contains "LIMIT" before the limit is applied? I mean without employing two separate queries. This would be useful in implementing pagination on the data.

  2. Enmk commented on Apr 25, 2023

    @Enmk
    Contributor

    Hello @nicktrandafil could you please share a simple SQL example to test this feature? That would help with development.

  3. nicktrandafil commented on Apr 26, 2023

    @nicktrandafil
    Author

    Hello @Enmk. You could use the SQL which I provided in my first comment. I tested the feature on the official playground of Clickhouse

    clickhouse client --secure --host play.clickhouse.com --user explorer
    

    then

    SELECT name               
    FROM workflow_jobs
    GROUP BY name                          
        WITH TOTALS   
    LIMIT 5, 5                             
    FORMAT JSON  
    

    In the results there is the desired statistic rows_before_limit_at_least:

    {
            ...
            "rows": 5,
            "rows_before_limit_at_least": 906,
            ...
    }
    

    Notice FORMAT JSON. In default format of response representation you wouldn't see this statistic.

  4. polyglotAI-bot commented on Nov 19, 2026

    @polyglotAI-bot
    Collaborator

    Recommendation: Keep open — feature implementation needed
    Decide how the API gives the totals and extremes blocks (for example new Query callbacks), then read both packets in ReceivePacket(). Separately, review PR #611: with it, the Client stays usable after this error (run below).

    Analysis: Client::Impl::ReceivePacket() has no case for ServerCodes::Totals (7) or ServerCodes::Extremes (8) (clickhouse/protocol.h:15-16), so the default branch throws UnimplementedError("unimplemented <type>") (clickhouse/client.cpp:777-778). It throws after it reads the packet type and before it reads the block. Thus the next query on the same Client reads the rest of the old response and fails (unsupported column type, unsupported custom serialization in the run). With the diff of PR #611 (370a334), the same Client runs the next query, but WITH TOTALS still fails. For the second question in the thread (rows before LIMIT without a second query): Query::OnProfile gives Profile::rows_before_limit and calculated_rows_before_limit (clickhouse/query.h:32-39, clickhouse/query.h:177), and the run shows rows_before_limit=20 for GROUP BY name LIMIT 5, 5 without WITH TOTALS.

    Reproduction: complete program and output
    #include <clickhouse/client.h>
    
    #include <cstdlib>
    #include <iostream>
    #include <string>
    
    using namespace clickhouse;
    
    static ClientOptions Options() {
        const char* host = std::getenv("CLICKHOUSE_HOST");
        const char* port = std::getenv("CLICKHOUSE_PORT");
        return ClientOptions()
            .SetHost(host ? host : "localhost")
            .SetPort(port ? static_cast<uint16_t>(std::stoi(port)) : 9000);
    }
    
    // Runs one SELECT and prints the number of rows, or the exception.
    // Then checks whether the same Client can run the next query.
    static void Run(const std::string& label, Query query) {
        Client client(Options());
        size_t rows = 0;
        query.OnData([&rows](const Block& block) { rows += block.GetRowCount(); });
        try {
            client.Execute(query);
            std::cout << label << ": rows=" << rows << std::endl;
        } catch (const std::exception& e) {
            std::cout << label << ": exception: " << e.what() << std::endl;
        }
        try {
            client.Execute(Query("SELECT 1"));
            std::cout << label << ": next query on the same Client: ok" << std::endl;
        } catch (const std::exception& e) {
            std::cout << label << ": next query on the same Client: exception: " << e.what() << std::endl;
        }
    }
    
    int main() {
        Client client(Options());
        client.Execute("DROP TABLE IF EXISTS triage_297");
        client.Execute("CREATE TABLE triage_297 (name String) ENGINE = MergeTree ORDER BY name");
        client.Execute("INSERT INTO triage_297 SELECT toString(number % 20) FROM numbers(1000)");
    
        // The query of the report.
        Run("GROUP BY ... WITH TOTALS LIMIT 5, 5",
            Query("SELECT name FROM triage_297 GROUP BY name WITH TOTALS LIMIT 5, 5"));
    
        // The Extremes packet has the same problem.
        Query extremes("SELECT name FROM triage_297 LIMIT 5");
        extremes.SetSetting("extremes", QuerySettingsField{"1", 0});
        Run("SETTINGS extremes = 1", extremes);
    
        // Second question of the report: rows before LIMIT without a second query.
        Query limited("SELECT name FROM triage_297 GROUP BY name LIMIT 5, 5");
        limited.OnProfile([](const Profile& p) {
            std::cout << "GROUP BY ... LIMIT 5, 5 (no TOTALS): OnProfile rows_before_limit=" << p.rows_before_limit
                      << " calculated_rows_before_limit=" << p.calculated_rows_before_limit << std::endl;
        });
        Run("GROUP BY ... LIMIT 5, 5 (no TOTALS)", limited);
    
        client.Execute("DROP TABLE IF EXISTS triage_297");
        return 0;
    }

    Output on master (2f8583f), ClickHouse 26.10.4.207, default 2.x build options, program compiled with clang++ -std=c++17:

    GROUP BY ... WITH TOTALS LIMIT 5, 5: exception: unimplemented 7
    GROUP BY ... WITH TOTALS LIMIT 5, 5: next query on the same Client: exception: unsupported column type: 
    SETTINGS extremes = 1: exception: unimplemented 8
    SETTINGS extremes = 1: next query on the same Client: exception: unsupported custom serialization
    GROUP BY ... LIMIT 5, 5 (no TOTALS): OnProfile rows_before_limit=20 calculated_rows_before_limit=1
    GROUP BY ... LIMIT 5, 5 (no TOTALS): rows=5
    GROUP BY ... LIMIT 5, 5 (no TOTALS): next query on the same Client: ok
    exit=0
    

    Output with the diff of PR #611 (370a334) applied to master:

    GROUP BY ... WITH TOTALS LIMIT 5, 5: exception: unimplemented 7
    GROUP BY ... WITH TOTALS LIMIT 5, 5: next query on the same Client: ok
    SETTINGS extremes = 1: exception: unimplemented 8
    SETTINGS extremes = 1: next query on the same Client: ok
    GROUP BY ... LIMIT 5, 5 (no TOTALS): OnProfile rows_before_limit=20 calculated_rows_before_limit=1
    GROUP BY ... LIMIT 5, 5 (no TOTALS): rows=5
    GROUP BY ... LIMIT 5, 5 (no TOTALS): next query on the same Client: ok
    exit=0
    

    Automated triage by Polyglot AI. A maintainer makes the final decision.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions