DBeaver not considering all Primary Key Columns when Updating Data in Grid

[egloffmark]

Description

I have H2 DB with a table which has two foreign keys and a data column "sort". The two foreign keys are also registered on the table as primary keys. When I now edit manually the data in the table by using DBEaver with the data grid and press "save" it executes an update but considers only the first primary key column in the "where" section. Hence the wrong parts of may data get updated.

I also tried with the config option "Use all table columns as key upon saving", but it did not helped

Example DDL for the table

CREATE TABLE "shop"."layout_modules" (
	"layout_id" INTEGER NOT NULL,
	"module_id" INTEGER NOT NULL,
	"sort" INTEGER DEFAULT 0 NOT NULL,
	CONSTRAINT "FKg1usbavp8i4rarv3unyx83fnq" PRIMARY KEY ("layout_id"),
	CONSTRAINT "FKo1sjor3ruyi04m7dfurkgn4ru" PRIMARY KEY ("module_id"),
	CONSTRAINT "FKg1usbavp8i4rarv3unyx83fnq" FOREIGN KEY ("layout_id") REFERENCES "shop"."layouts"("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
	CONSTRAINT "FKo1sjor3ruyi04m7dfurkgn4ru" FOREIGN KEY ("module_id") REFERENCES "shop"."modules"("id") ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE INDEX "FKg1usbavp8i4rarv3unyx83fnq_INDEX_8" ON "shop"."layout_modules" ("layout_id");
CREATE INDEX "FKo1sjor3ruyi04m7dfurkgn4ru_INDEX_8" ON "shop"."layout_modules" ("module_id");

When I then press "save" it generates the following wrong "Update" statements where the 2nd primary key column "module_id" get not considered

I tried with a SINGLE "combined" primary key in the DDL "LAYOUT_MODULES_PK " , this works. However Hibernate creates with JPA always separate PRIMARY KEY definitions.

CREATE TABLE "shop"."layout_modules" (
	"layout_id" INTEGER NOT NULL,
	"module_id" INTEGER NOT NULL,
	"sort" INTEGER DEFAULT 0 NOT NULL,
	CONSTRAINT LAYOUT_MODULES_PK PRIMARY KEY ("layout_id","module_id")
);
CREATE UNIQUE INDEX PRIMARY_KEY_8 ON "shop"."layout_modules" ("layout_id","module_id");

Regards
Mark

DBeaver Version

Community Edition Version 24.0.3.202404211624

Operating System

Linux Mint / Ubuntu

Database and driver

H2 Embedded V.2.1.210
org.h2.Driver

Steps to reproduce

1. Create a H2 DB
2. Create the necessary tables e.g. "layouts" and "modules" and add a few records
3. Create the table with foreign key and multiple or combined primary keys to connect layout and modules "layout_modules".
4. Edit the data in the "layout_modules" tables with the Grid Editor and recognize that the "update" statement does only have one primary key column considered

Additional context

No response

[E1izabeth]

Thank you for bug report.

[E1izabeth]

Can you execute the query you provided? I can't.

[egloffmark]

Thank you Elizabeth, I used Hibernate 6 to create the table (auto-ddl) . It looks like that Hibernate creates the two primary columns with an "unsupported" syntax. Not sure why this works. so it seems to be more an issue between Hibernate and H2 Driver.

I will close the issue and may reopen a new one once I have a reproducible example