[MCOL-4740] UPDATE returns wrong "Rows matched" on multi-tables Created: 2021-06-01  Updated: 2023-12-21  Resolved: 2023-12-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 5.5.2
Fix Version/s: 23.10.1

Type: Bug Priority: Critical
Reporter: Yakov Kushnirsky Assignee: Roman
Resolution: Fixed Votes: 1
Labels: triage
Environment:

Centos 8


Issue Links:
Blocks
is blocked by MDEV-28883 Re-design the upper level of handling... Closed
is blocked by MDEV-31725 Change visibility of some multi_updat... Closed
Duplicate
duplicates MCOL-3445 update statement return wrong number ... Closed
Relates
relates to MDEV-32256 bulk multi-update / multi-delete API ... Open
Sprint: 2021-14, 2021-15, 2021-16, 2023-11
Assigned for Review: Denis Khalikov Denis Khalikov
Assigned for Testing: Kirill Perov Kirill Perov

 Description   

Multi-tables UPDATE returns "Rows matched: 0" when expected 1.
SELECT ROW_COUNT(); is also 0, when 1 is expected.

The above happen only if engine=columnstore.
With InnoDB: "Rows matched: 1" and ROW_COUNT() is 1 as well

Reproduced with
plugin_name: COLUMNSTORE_EXTENTS
plugin_status: ACTIVE
plugin_type_version: 100510.0
plugin_library_version: 1.14
plugin_license: GPL
plugin_maturity: Stable
plugin_auth_version: 5.5.2

Repro:
-------
1) - preparation steps:
create database sam;
use sam;

drop table if exists mcs_1;
drop table if exists mcs_2;
create table mcs_1(a int, b int(11), c varchar(100) ) engine=columnstore;
create table mcs_2(a int, b int(11), c varchar(100) ) engine=columnstore;
insert into mcs_1(a,b, c) VAlues( 33 , 99 , 1 );
insert into mcs_1(a,b, c) VAlues( 33 , 99 , 2 );
insert into mcs_1(a,b, c) VAlues( 33 , 99 , 3 );
insert into mcs_2(a,b, c) VAlues( 33 , 11 , 1 );
insert into mcs_2(a,b, c) VAlues( 33 , 11 , 2 );
insert into mcs_2(a,b, c) VAlues( 33 , 11 , 3 );

2) - repro:
UPDATE mcs_1 A , mcs_2 B SET A.b = B.b WHERE A.c = B.c LIMIT 1;
Query OK, 0 rows affected (0.409 sec)
Rows matched: 0 Changed: 0 Warnings: 0 <========/ expected "Row matched: 1" in InnoDB

SELECT ROW_COUNT;



 Comments   
Comment by David Hall (Inactive) [ 2021-11-19 ]

For updates to a single table, we return the counts and they are displayed. This is done by setting the arguments in

int ha_mcs::direct_update_rows(ha_rows *update_rows, ha_rows *found_rows)

But when "SET A.b = B.b", two tables are involved so a completely different code path is followed. The update is performed by the select handler. When this code path is used, the counts come from the count and updated members of class multi_update declared in sql_class.h. These are declared private and Columnstore does not have access.

Simply making these available to the engine may not suffice, as the code path inside class multi_update would change. I don't know how this would affect the processing.

Comment by Hartmut Holzgraefe [ 2022-07-14 ]

Any update on this?

Comment by alexey vorovich (Inactive) [ 2022-09-22 ]

suresh.ramagiri@mariadb.com

Please clarify in which releases it fails and it which it works

Comment by suresh ramagiri [ 2022-09-25 ]

alexey.vorovich, As I noted, locally can repro it at MariaDB ES v10.6.10 columnstore - 6.4.3. A customer reported it on MariaDB ES 10.5/5.

Comment by Kirill Perov [ 2023-11-14 ]

drrtuy, current develop shows:

MariaDB [test1]> UPDATE mcs_1 A , mcs_2 B SET A.b = B.b WHERE A.c = B.c LIMIT 1;
Query OK, 0 rows affected (0.162 sec)
Rows matched: 0 Changed: 0 Warnings: 0

But mcs_1 was changed.

Comment by Kirill Perov [ 2023-12-02 ]

fixed in latest develop (03.12.2023)

testing finished ok

Generated at Thu Feb 08 02:52:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.