ClickHouse doesn't return affected rows via MySQL protocol for INSERT queries after 20.5+

[Slach]

Describe the bug
ClickHouse doesn't return affected rows via MySQL protocol for INSERT queries after 20.5+

How to reproduce
i create clear docker-compose environment for reproduce behavior MySQL + clickhouse + php + python
https://gist.github.com/Slach/aa67440ce856a3a53f64f92eeddfbc1b

Queries to run that lead to unexpected result

INSERT INTO default.t1(n) SELECT * FROM numbers(1000)

Expected behavior
MySQL protocol shall return affected rows field for INSERT queries (it works successful in 20.3 and 20.4)

Actual behavior

[den-crane]

CH simply does not know this.

[Slach]

@den-crane
why CH knew it in 20.3, 20.4 but forget in 20.5+?
and why it successfully save into system.query_log result_rows field?
it will break MUCH cases for users which expect affected rows to ensure some data was inserted

[den-crane]

Not sure Why it was working before.

create table A ( A String) Engine=Memory;
insert into A values('a'),('b');
2 rows in set.                  <<<<<<<-----------

create table A1 as A
insert into A1 select * from A
0 rows in set

insert into A1 SELECT toString(number) FROM numbers(1000)
0 rows in set.

[den-crane]

@Slach Check this in 20.3

INSERT INTO default.t1(n) SELECT * FROM numbers(1000) where number%3

[Slach]

@den-crane
done
look to https://gist.github.com/Slach/aa67440ce856a3a53f64f92eeddfbc1b/revisions?diff=split

string(26) "CLICKHOUSE #2 INSERT QUERY"
string(70) "INSERT INTO default.t1(n) SELECT * FROM numbers(1000) where number % 3"
string(6) "RESULT"
bool(true)
string(27) "CLICKHOUSE #2 affected_rows"
int(666)

[Slach]

add steps to reproduce for python in https://gist.github.com/Slach/aa67440ce856a3a53f64f92eeddfbc1b

@zhang2014 @BohuTANG could you help us resolve this issue?

[BohuTANG]

The problem does exist, but it's not the MySQLHandler/MySQL problem.
When we execute INSERT INTO default.t1(n) SELECT * FROM numbers(1000) where number % 3, the packet send to MySQL client is not through the MySQLOutputFormat.cpp:

ClickHouse/src/Processors/Formats/Impl/MySQLOutputFormat.cpp

Lines 77 to 82 in 64bd63c

	const auto & header = getPort(PortKind::Main).getHeader();
	if (header.columns() == 0)
	packet_endpoint->sendPacket(OKPacket(0x0, context->mysql.client_capabilities, affected_rows, 0, 0, "", human_readable_info), true);
	else
	if (context->mysql.client_capabilities & CLIENT_DEPRECATE_EOF)
	packet_endpoint->sendPacket(OKPacket(0xfe, context->mysql.client_capabilities, affected_rows, 0, 0, "", human_readable_info), true);

Instead it send directly here:

ClickHouse/src/Server/MySQLHandler.cpp

Lines 338 to 339 in 64bd63c

	if (!with_output)
	packet_endpoint->sendPacket(OKPacket(0x00, client_capability_flags, 0, 0, 0), true);

I am not sure why, maybe @alexey-milovidov could give some hints.

[Slach]

@zhang2014 could you look to #16605 (comment)
and help us resolve this issue?