Data Editor - wrong column being used in auto-generated update SQL

[CallumBDX]

Description

Dbeaver Community (24.0.4.20240505183)
Window 10 Pro

With a table loaded (MS SQL Server DB), individual column entries can be edited and then saved. Can see from the preview of the SQL used to update table that it is using a non-identify column in the where clause to determine which row to affect.

I couldn't see anywhere in the settings to alter this, and I couldn't describe the issue in a way that I found any existing issues, so I apologise if this has been asked and answered.

DBeaver Version

Community Edition 24.0.4

Operating System

Windows 10 Pro

Database and driver

Database: MS SQL Server
Driver: Microsoft JDBC Driver for SQL Server (MSSQL)

Steps to reproduce

DDL to replicate:

CREATE TABLE Example.dbo.eg (
	Idx numeric(18,0) IDENTITY(1,1) NOT NULL,
	name varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	variable varchar(30) COLLATE Chinese_PRC_CI_AS NULL
);

The problem is that when I change a row for column variable, the auto-generated SQL for updating uses the name column for the where clause and not the identity, when the name column can be non-unique.

-- Auto-generated SQL script. Actual values for binary/complex data types may differ - what you see is the default string representation of values.
UPDATE Example.dbo.eg 
	SET variable=N'bicycle'
	WHERE name=N'bob';

Additional context

No response

[michele-tramonti]

This issue affects also

DBeaver Version
Community Edition 24.1.3

Operating System
Ubuntu 22.04

[E1izabeth]

@CallumBDX @michele-tramonti
Does your table have unique keys? You can check it here

[E1izabeth]

For now, if I create the table by the provided DDL, it doesn't have a primary key. So, on data edit, I'm asked about the approach

I can reproduce the issue only if manually created virtual key for name column. Could you also check, if you have one?

[CallumBDX]

Hi @E1izabeth, thanks for checking this out. You are correct that there is no physical unique key here, and that the "name" column is set as the virtual unique key. Changing the virtual key to the identity column has fixed the issue for me. I must've set that without thinking years ago!

[E1izabeth]

Thank you for response. I'm closing the issue then.

[michele-tramonti]

Hi E1izabeth,
sorry for the delay: I've checked and also in my case there is no physical primary key in the table.

Anyway I think that there is still a bug here: the update sql script generated by DBeaver is wrong, because it include a 'N' character after each '=' sign, if the column type is a string (you can see it also in the first post of CallumBDX).

maybe, if the table contains a physical primary key this will not appen, but physical primary key is not mandatory so I think this is not a solution.
In my case, for example, I'm not the administrator of the db and I cannot add the the primary key...