UPSERT not working properly when used as UPDATE statement

[0ctanium]

What happened When upserting an entry that already exists, fields that have not been explicitly specified in the statement become NULL.

What you expected to happen Upserting already existing entries should act as an update. So fields that are not explicitly defined should not change

How to reproduce it (as minimally and precisely as possible) First, create the table

CREATE TABLE table1 (uid VARCHAR[252], name VARCHAR, email VARCHAR, PRIMARY KEY uid)

Then insert some data

INSERT INTO table1(uid, name, email) VALUES ('BEKZ', 'Marc', 'test@marc.fr')

If you query the table you should get that:

+------------------------+-------------------------+--------------------------+
| (DEFAULTDB TABLE2 UID) | (DEFAULTDB TABLE2 NAME) | (DEFAULTDB TABLE2 EMAIL) |
+------------------------+-------------------------+--------------------------+
| "BEKZ"                 | "Marc"                  | "test@marc.fr"           |
+------------------------+-------------------------+--------------------------+

Now upsert the data

INSERT INTO table1(uid, name, email) VALUES ('BEKZ', 'Marc', 'test@marc.fr')

The email is now NULL. but shouldn't have changed

+------------------------+-------------------------+--------------------------+
| (DEFAULTDB TABLE2 UID) | (DEFAULTDB TABLE2 NAME) | (DEFAULTDB TABLE2 EMAIL) |
+------------------------+-------------------------+--------------------------+
| "BEKZ"                 | "Ben"                   | NULL                     |
+------------------------+-------------------------+--------------------------+

Environment immudb 1.0.1 Built at: Mon, 07 Jun 2021 16:48:47 CEST

[jeroiraz]

Thanks for opening the issue @0ctanium

As we discussed in Discord channel, this is the original expected behaviour we depicted for UPSERT i.e. to be equivalent to INSERT, where the unspecified columns will get a default value. Currently, the default value is NULL.

In case partial updates are desired, the standard UPDATE statement may be used.

Note: there are room of improvements regarding default values...