DB Merge Node: doesen´t insert all values if thw date is only unique by to columns

KNIME Analytics Platform
1

If you try to update a table by megre step the insert is only done for 1 row if the date is unique only by a pair of columns.
columnA columnB Value
a b 1
b c 2
a c 3

Line ac3 wil not be inserted in the first try. what can be the problem?
Knime 4.14

2

Hello @KarstenS,

how did you configure DB Merge node for example given and how does a table in DB looks like? Which DB are you working with? Also maybe you can make flow example using SQLite DB. That would help understand is it a configuration issue, expected behavior, bug or all of the above :wink:

Br,
Ivan

1 Like
3

Here you can see the result:
image
here is the config of the node

image
image569×690 13.1 KB

So for my point of view nothing special.
So the combination of kpi_id and plotgroup (in first Screenshot it is the Sting column after kpi_is) is unique.

4

Hello @KarstenS,

I have tried it with SQLite DB and seems fine for me. Can you check workflow attached and see if you can set it up so behavior you described can be seen?
2021_01_18_DB_Merge.knwf (22.2 KB)

Br,
Ivan

5

The Problem happend on Microsoft SQL Server. Can you pls try it with mssql(express)

6

Hello @KarstenS,

I could but don’t have it configured. However before that could you tell me does the example I created is the one you are talking about? And you have tried it and it does work as expected?

Br,
Ivan

7

Hello Karsten,
I was finally able to reproduce the problem. First of all the merging works as expected. The reason for the 0 entries in the MergeStatus column are due to duplicate rows within the same batch (defined by the batch size parameter). When checking for duplicates only the columns in the SET and WHERE section are considered. So if a row that does not exist in the database occurs twice within the same batch the second row will become a 0 merge status. This is because during update no matching row has been found in the db and the subsequent insert statement wasn’t done since the precious row with the same values in the batch has already be inserted.
Bye
Tobias

2021_01_18_DB_Merge_reproduced.knwf (21.2 KB)

1 Like
8

Sorry for the late reply, yes your solutions works like expected.

1 Like
9

Thanks for explaining the cause.

10

Hi Tobias,

I have similar problem with DB Merge, with PostgreSQL (v9.6).

DB merge does not insert some lines that are not duplicated lines. The bug can be reproduced with the following workflow :

Can you please take a look at this ?

Many thanks in advanced,

Thanh Thanh