SQL containing WITH clause and WITH TOTALS fails with ClickHouseUnknownException

[vinodkmrm]

SQL containing the WITH clause (Common Table Expressions (CTE)) and WITH TOTAL clause fails, when looping through the result set. Call to ClickHouseResultSet.next() after reading the last row fails with ClickHouseUnknownException. Call to ClickHouseResultSet.getTotals() fails with "java.lang.IllegalStateException: Cannot get totals when totals are not being used." e.g

Bug is in ru.yandex.clickhouse.ClickHouseStatementImpl#extractWithTotals method. This method must check for sql staring with "WITH" or "SELECT". Right now it only checks for SQL starting with "SELECT", as show below -

  private boolean extractWithTotals(String sql) {
       if (Utils.startsWithIgnoreCase(sql, "select")) {
           String withoutStrings = Utils.retainUnquoted(sql, '\'');
           return withoutStrings.toLowerCase().contains(" with totals");
       }
       return false;
   }

Query :

WITH 2 AS factor
SELECT 
    number % 2 AS odd_even, 
    count(*) AS count, 
    sum(factor * number) AS output
FROM 
(
    SELECT number
    FROM system.numbers
    LIMIT 100
)
GROUP BY number % 2
    WITH TOTALS
 

Java Code :

       ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(
                "jdbc:clickhouse://localhost:8123"
        );
        String sqlQueryWith = "WITH 2 AS factor\n" +
                "SELECT \n" +
                "    number % 2 AS odd_even, \n" +
                "    count(*) AS count, \n" +
                "    sum(factor * number) AS output\n" +
                "FROM \n" +
                "(\n" +
                "    SELECT number\n" +
                "    FROM system.numbers\n" +
                "    LIMIT 100\n" +
                ")\n" +
                "GROUP BY number % 2\n" +
                "    WITH TOTALS\n";

        try (ClickHouseConnection connection = clickHouseDataSource.getConnection()) {
            ClickHouseStatement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sqlQueryWith);
            while (rs.next()) {
                System.out.println(format("odd_even %d, count : %d, output %d", rs.getInt("odd_even"),
                        rs.getInt("count"), rs.getInt("output")));
            }
            ((ClickHouseResultSet) rs).getTotals();
            System.out.println(format("Total odd_even %d, count : %d, output %d", rs.getInt("odd_even"),
                    rs.getInt("count"), rs.getInt("output")));

        }