Bug: Changing a Cell in Joined Tables Updates All Records of The Column Incorrectly

[Rhubarb-Sun]

Describe the bug
When editing the result set after joining two or more tables, updating a single cell mistakenly updates all rows in that column instead of the intended field.

To Reproduce
Steps to reproduce the behavior:

1. Prepare two tables

CREATE TABLE `book` (
  `book_id` bigint NOT NULL AUTO_INCREMENT,
  `author_id` bigint NOT NULL,
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `author` (
  `author_id` bigint NOT NULL,
  `author_name` varchar(255) NOT NULL,
  PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `book` (`book_id`, `author_id`)
VALUES
(1, 1001),
(2, 1002),
(3, 1003),
(4, 1004),
(5, 1005);

INSERT INTO `author` (`author_id`, `author_name`)
VALUES
(1001, 'Name1'),
(1002, 'Name2'),
(1003, 'Name3'),
(1004, 'Name4');

2. Write a SQL query that performs a join between two or more tables.

select b.book_id, a.author_name from book b left join author a on b.author_id = a.author_id
where a.author_name = 'Name1'

3. Execute the query.
4. In the result set, attempt to edit a value in one of the fields, and save the change.

5. Observe that upon committing the change, all columns in the row are modified to the new value.

Desktop (please complete the following information):

• OS: [MacOS 14.6.1]
• Browser [chrome Version 129.0.6668.59]
• Database type and version: [MySQL 8.0.26-17]
• Version 1 [Backend version 24.2.4.202410160731 Frontend version 24.2.4.202410160737]
• Version 2 [Backend version 24.1.3.202407220833 Frontend version 24.1.3.202407220836]

Additional context
This issue is critical in data modification scenarios, especially in joined tables where each column might come from different tables. The incorrect update leads to potential data corruption, as all fields in the result row are overwritten unexpectedly.

[EvgeniaBzzz]

Hi @Rhubarb-Sun
Thank you for the report! We'll take a look and fix it in one of the nearest releases.

[LonwoLonwo]

Hello @Rhubarb-Sun

As a workaround you can correct your SQL and add a second PK in the query.
Like this:

select b.book_id, a.author_name, a.author_id from book b left join author a on b.author_id = a.author_id
where a.author_name = 'Name1'

Then you will have correct SQL for data updating.

[Rhubarb-Sun]

Hello @Rhubarb-Sun

As a workaround you can correct your SQL and add a second PK in the query. Like this:

select b.book_id, a.author_name, a.author_id from book b left join author a on b.author_id = a.author_id
where a.author_name = 'Name1'

Then you will have correct SQL for data updating.

Thank you for your reply.

However, this workaround has introduced two issues on my end:

1. Ambiguous Column Error: When attempting to update the result set, I receive the following exception:
   Error updating resultset data: SQL Error [1052] [23000]: Column 'author_id' in where clause is ambiguous

This occurs even though I have given the column an alias to avoid ambiguity.

2. Unexpected Cell Update: Despite the exception being thrown, the specific cell I was editing still gets updated, which seems incorrect behavior.

[dariamarutkina]

Hello @Rhubarb-Sun !
You can find a fix in v.24.2.4 💙
Added a ban on editing the table when combining queries
Thank you for being with CloudBeaver!