I get an exception when I try to use a CASE statement in INSERT ON CONFLICT DO UPDATE assignments.
I know that this is supported in postgres. Appreciate any thoughts on whether this is a bug or known unsupported feature. I could not tell from the documentation or searches if it might be latter case.
A simple example that can be used to observe this issue is as follows:
create table test(a int primary key, b timestamp);
insert into test (a, b) values (1, ‘2020-01-01T00:00:00Z’::timestamp)
on conflict (a)
do update set b = case when excluded.b > b then excluded.b else b end;
I am using crate.io version 4.1.1. The error trace shown in the console is as follows:
SQLActionException: 500 Internal Server Error 5000 ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
at io.crate.execution.dml.upsert.UpdateSourceGen.generateSource(UpdateSourceGen.java:125)
at io.crate.execution.dml.upsert.TransportShardUpsertAction.indexItem(TransportShardUpsertAction.java:303)
at io.crate.execution.dml.upsert.TransportShardUpsertAction.indexItem(TransportShardUpsertAction.java:241)
at io.crate.execution.dml.upsert.TransportShardUpsertAction.processRequestItems(TransportShardUpsertAction.java:149)
at io.crate.execution.dml.upsert.TransportShardUpsertAction.processRequestItems(TransportShardUpsertAction.java:81)
at io.crate.execution.dml.TransportShardAction$1.call(TransportShardAction.java:116)
at io.crate.execution.dml.TransportShardAction$1.call(TransportShardAction.java:113)
at io.crate.execution.dml.TransportShardAction.wrapOperationInKillable(TransportShardAction.java:140)
at io.crate.execution.dml.TransportShardAction.shardOperationOnPrimary(TransportShardAction.java:119)
at io.crate.execution.dml.TransportShardAction.shardOperationOnPrimary(TransportShardAction.java:65)
at org.elasticsearch.action.support.replication.TransportReplicationAction$PrimaryShardReference.perform(TransportReplicationAction.java:1019)
at org.elasticsearch.action.support.replication.TransportReplicationAction$PrimaryShardReference.perform(TransportReplicationAction.java:997)
at org.elasticsearch.action.support.replication.ReplicationOperation.execute(ReplicationOperation.java:105)
at org.elasticsearch.action.support.replication.TransportReplicationAction$AsyncPrimaryAction.onResponse(TransportReplicationAction.java:360)
at org.elasticsearch.action.support.replication.TransportReplicationAction$AsyncPrimaryAction.onResponse(TransportReplicationAction.java:300)
at org.elasticsearch.action.support.replication.TransportReplicationAction$1.onResponse(TransportReplicationAction.java:960)
at org.elasticsearch.action.support.replication.TransportReplicationAction$1.onResponse(TransportReplicationAction.java:957)
at org.elasticsearch.index.shard.IndexShardOperationPermits.acquire(IndexShardOperationPermits.java:271)
at org.elasticsearch.index.shard.IndexShardOperationPermits.acquire(IndexShardOperationPermits.java:238)
at org.elasticsearch.index.shard.IndexShard.acquirePrimaryOperationPermit(IndexShard.java:2231)
at org.elasticsearch.action.support.replication.TransportReplicationAction.acquirePrimaryShardReference(TransportReplicationAction.java:969)
at org.elasticsearch.action.support.replication.TransportReplicationAction$AsyncPrimaryAction.doRun(TransportReplicationAction.java:321)
at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
at org.elasticsearch.action.support.replication.TransportReplicationAction$PrimaryOperationTransportHandler.messageReceived(TransportReplicationAction.java:296)
at org.elasticsearch.action.support.replication.TransportReplicationAction$PrimaryOperationTransportHandler.messageReceived(TransportReplicationAction.java:288)
at org.elasticsearch.transport.RequestHandlerRegistry.processMessageReceived(RequestHandlerRegistry.java:63)
at org.elasticsearch.transport.TransportService$7.doRun(TransportService.java:662)
at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:694)
at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:830)