Generated column with on conflict

CrateDB
1

I am trying to use a generated column defined as in the next table:

CREATE TABLE IF NOT EXISTS "ts_real_raw_test" (
   "real_id" BIGINT NOT NULL,
   "date" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   "value" DOUBLE PRECISION,
   "value_text" TEXT,
   "modification_date" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS CURRENT_TIMESTAMP,
   "year" INTEGER GENERATED ALWAYS AS EXTRACT(YEAR FROM "date"),
   PRIMARY KEY ("real_id", "date", "year")
)
CLUSTERED INTO 4 SHARDS
WITH (
column_policy = 'dynamic'
);

When using simple insert like:

INSERT INTO "ts_real_raw_test" (real_id, date, value, value_text) VALUES (6,1920975192100,99999,NULL);

The insertion result in the table is correct, year column is generated correctly.

If I try to use on conflict like the following sentence:

INSERT INTO "ts_real_raw_test" (real_id, "date", value, value_text) VALUES (6,'2030-11-15 12:13:13',99999,null) ON CONFLICT ("real_id", "date", "year") DO UPDATE SET value = excluded.value, value_text = excluded.value_text;

The resulting insertion has year column set to null (the column is not generated).

image
image871×61 7.25 KB

Moreover, if I execute this sentence again using the same value in value column or even changing it:

INSERT INTO "ts_real_raw_test" (real_id, "date", value, value_text) VALUES (6,'2030-11-15 12:13:13',99998,null) ON CONFLICT ("real_id", "date", "year") DO UPDATE SET value = excluded.value, value_text = excluded.value_text;

I receive an exception:

SQL Error [XX000]: ERROR: Given value null for generated column year does not match calculation extract(YEAR FROM date) = 2030
  Where: io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:152)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:141)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:190)
io.crate.protocols.postgres.RowCountReceiver.fail(RowCountReceiver.java:72)
io.crate.action.sql.RowConsumerToResultReceiver.accept(RowConsumerToResultReceiver.java:63)
io.crate.planner.operators.InsertFromValues.lambda$execute$2(InsertFromValues.java:306)
java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:863)
java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:841)
java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510)
java.base/java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:2194)
io.crate.planner.operators.InsertFromValues.lambda$execute$10(InsertFromValues.java:665)
io.crate.planner.operators.InsertFromValues$1.onResponse(InsertFromValues.java:703)
io.crate.planner.operators.InsertFromValues$1.onResponse(InsertFromValues.java:690)
io.crate.execution.support.RetryListener.onResponse(RetryListener.java:57)
org.elasticsearch.action.ActionListener.accept(ActionListener.java:54)
org.elasticsearch.action.ActionListener.accept(ActionListener.java:39)
java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:863)
java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:841)
java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510)
java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2179)

The version of crate is 5.5.0. The production cluster is still in 5.2.8 and it works as expected. I think this problem started with 5.3.

Thank you

2

I have finally opened an issue #15089 in Github, I expect I have done it correctly.

1 Like
3

Updated to the newly released 5.5.1 and the problem has been corrected. Thank you!

1 Like