Incorrect row count returned for delete statement

[sahoss]

Describe the bug

The jdbc client returns incorrect row count when running a delete query

Steps to reproduce

1. Setup clickhouse db and fill in the username/password/host details in the code snippet
2. [optional] setup a mysql db and fill in the details. mysql step is required to demonstrate correct behavior
3. Run the code snippet and observe output.

Expected behaviour

1. The execute count should return correct value and not a harcode value of 1

Code example

package org.example;

import com.clickhouse.jdbc.ClickHouseDataSource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Main
{
    public static void main(String[] args)
            throws SQLException, ClassNotFoundException
    {
        mysql();
        clickhouse();
    }

    public static void mysql()
            throws ClassNotFoundException, SQLException
    {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mysql", "username", "password");

        Statement stmt = conn.createStatement();

        stmt.execute("create table if not exists myTable (a int)");
        stmt.execute("insert into myTable values (1), (2), (3)");

        System.out.println("mysql:expecting : 3, got: " + stmt.executeUpdate("delete from myTable where a > 0"));
        System.out.println("mysql:expecting : 0, got: " +stmt.executeUpdate("delete from myTable where a < 0"));
    }


    public static void clickhouse()
            throws SQLException
    {
        String url = "jdbc:ch://0.0.0.0:8123/default"; // use http protocol and port 8123 by default
        Properties properties = new Properties();
        ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
        try (Connection conn = dataSource.getConnection("username", "password");
                Statement stmt = conn.createStatement()) {

            stmt.execute("create table if not exists myTable (a int) engine=MergeTree ORDER BY a");
            stmt.execute("insert into myTable values (1), (2), (3)");

            System.out.println("clickhouse:expecting : 3, got: " + stmt.executeUpdate("delete from myTable where a > 0"));
            System.out.println("clickhouse:expecting : 0, got: " +stmt.executeUpdate("delete from myTable where a < 0"));
        }
    }
}

Error log

mysql:expecting : 3, got: 3
mysql:expecting : 0, got: 0
clickhouse:expecting : 3, got: 1
clickhouse:expecting : 0, got: 1

Configuration

Environment

• Client version: clickhouse-jdbc 0.4.6
• Language version: openjdk-19.0.1
• OS: fedora 37

ClickHouse server

• ClickHouse Server version: ClickHouse release 23.7
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[zhicwu]

Hi @sahoss, thanks for the report. Unfortunately this is a known issue. As of now, clickhouse-java uses http to access ClickHouse, and there's no accurate affected rows returned from server. I believe you tried insert as well, but please do NOT count on that - see ClickHouse/ClickHouse#43327 or ClickHouse/ClickHouse#18237.

{% shell(cli.stderr.redirect=true): 
/usr/local/bin/docker stop ch || true; 
/usr/local/bin/docker run --name ch --rm -it -p8123:8123 -d clickhouse/clickhouse-server:23.7;
sleep 1
%}

{% sql(id=ch-local):
DROP TABLE IF EXISTS myTable;
create table myTable (a int) engine=MergeTree ORDER BY a;
insert into myTable values (1), (2), (3);
%}

{{ shell(cli.stderr.redirect=true): 
echo 'delete from myTable where a > 1'  | curl -v 'http://localhost:8123' --data-binary @-
}}

...
< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0","result_rows":"0","result_bytes":"0","peak_memory_usage":"0"}
...

[zjsun]

Maybe return java.sql.Statement#SUCCESS_NO_INFO?

[chernser]

wait_end_of_query=1 should be set for query settings (server settings) This summary counters will not work with async_insert=1 either.