[Bug]: Create or Replace in Trino dosn't work with Nessie Catalog

[shohamyamin]

What happened

When I try to make a create or replace in trino than in the cases that the table should be replaced it failed.

This is the Trace:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [65536]: Query failed (#20240219_221031_00018_ps77a): Bad Request (HTTP/400): New value to update existing key 'sample.customer' has no content ID at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:600) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:503) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:510) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:962) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4123) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLException: Query failed (#20240219_221031_00018_ps77a): Bad Request (HTTP/400): New value to update existing key 'sample.customer' has no content ID at io.trino.jdbc.AbstractTrinoResultSet.resultsException(AbstractTrinoResultSet.java:1937) at io.trino.jdbc.TrinoResultSet$ResultsPageIterator.computeNext(TrinoResultSet.java:294) at io.trino.jdbc.TrinoResultSet$ResultsPageIterator.computeNext(TrinoResultSet.java:254) at io.trino.jdbc.$internal.guava.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) at io.trino.jdbc.$internal.guava.collect.AbstractIterator.hasNext(AbstractIterator.java:140) at java.base/java.util.Spliterators$IteratorSpliterator.tryAdvance(Unknown Source) at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.lambda$initPartialTraversalState$0(Unknown Source) at java.base/java.util.stream.StreamSpliterators$AbstractWrappingSpliterator.fillBuffer(Unknown Source) at java.base/java.util.stream.StreamSpliterators$AbstractWrappingSpliterator.doAdvance(Unknown Source) at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.tryAdvance(Unknown Source) at java.base/java.util.Spliterators$1Adapter.hasNext(Unknown Source) at io.trino.jdbc.TrinoResultSet$AsyncIterator.lambda$new$1(TrinoResultSet.java:179) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.base/java.lang.Thread.run(Unknown Source) Caused by: org.projectnessie.error.NessieBadRequestException: Bad Request (HTTP/400): New value to update existing key 'sample.customer' has no content ID at org.projectnessie.error.ErrorCode.lambda$asException$1(ErrorCode.java:66) at java.base/java.util.Optional.map(Optional.java:260) at org.projectnessie.error.ErrorCode.asException(ErrorCode.java:66) at org.projectnessie.client.rest.ResponseCheckFilter.checkResponse(ResponseCheckFilter.java:68) at org.projectnessie.client.rest.NessieHttpResponseFilter.filter(NessieHttpResponseFilter.java:29) at org.projectnessie.client.http.impl.jdk11.JavaRequest.lambda$executeRequest$1(JavaRequest.java:140) at java.base/java.util.ArrayList.forEach(ArrayList.java:1596) at java.base/java.util.Collections$UnmodifiableCollection.forEach(Collections.java:1116) at org.projectnessie.client.http.impl.jdk11.JavaRequest.executeRequest(JavaRequest.java:140) at org.projectnessie.client.http.HttpRequest.post(HttpRequest.java:90) at org.projectnessie.client.rest.v1.RestV1TreeClient.commitMultipleOperations(RestV1TreeClient.java:204) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at org.projectnessie.client.rest.v1.RestV1Client$ExceptionRewriter.invoke(RestV1Client.java:78) at jdk.proxy7/jdk.proxy7.$Proxy333.commitMultipleOperations(Unknown Source) at org.projectnessie.client.rest.v1.HttpCommitMultipleOperations.commit(HttpCommitMultipleOperations.java:34) at org.apache.iceberg.nessie.NessieIcebergClient.commitTable(NessieIcebergClient.java:483) at io.trino.plugin.iceberg.catalog.nessie.IcebergNessieTableOperations.commitToExistingTable(IcebergNessieTableOperations.java:117) at io.trino.plugin.iceberg.catalog.AbstractIcebergTableOperations.commit(AbstractIcebergTableOperations.java:174) at org.apache.iceberg.BaseTransaction.lambda$commitReplaceTransaction$1(BaseTransaction.java:370) at org.apache.iceberg.util.Tasks$Builder.runTaskWithRetry(Tasks.java:413) at org.apache.iceberg.util.Tasks$Builder.runSingleThreaded(Tasks.java:219) at org.apache.iceberg.util.Tasks$Builder.run(Tasks.java:203) at org.apache.iceberg.util.Tasks$Builder.run(Tasks.java:196) at org.apache.iceberg.BaseTransaction.commitReplaceTransaction(BaseTransaction.java:354) at org.apache.iceberg.BaseTransaction.commitTransaction(BaseTransaction.java:303) at io.trino.plugin.iceberg.IcebergMetadata.finishInsert(IcebergMetadata.java:1113) at io.trino.plugin.iceberg.IcebergMetadata.finishCreateTable(IcebergMetadata.java:973) at io.trino.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.finishCreateTable(ClassLoaderSafeConnectorMetadata.java:577) at io.trino.tracing.TracingConnectorMetadata.finishCreateTable(TracingConnectorMetadata.java:649) at io.trino.metadata.MetadataManager.finishCreateTable(MetadataManager.java:1109) at io.trino.tracing.TracingMetadata.finishCreateTable(TracingMetadata.java:598) at io.trino.sql.planner.LocalExecutionPlanner.lambda$createTableFinisher$4(LocalExecutionPlanner.java:4156) at io.trino.operator.TableFinishOperator.getOutput(TableFinishOperator.java:319) at io.trino.operator.Driver.processInternal(Driver.java:398) at io.trino.operator.Driver.lambda$process$8(Driver.java:301) at io.trino.operator.Driver.tryWithLock(Driver.java:704) at io.trino.operator.Driver.process(Driver.java:293) at io.trino.operator.Driver.processForDuration(Driver.java:264) at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:887) at io.trino.execution.executor.timesharing.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:187) at io.trino.execution.executor.timesharing.TimeSharingTaskExecutor$TaskRunner.run(TimeSharingTaskExecutor.java:565) at io.trino.$gen.Trino_435____20240219_220610_2.run(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583)

How to reproduce it

In Trino you can just run this queries that create table and replace it in iceberg:

1. create schema if not exists datalake.sample WITH (location = 's3a://datalake/sample');
2. create or replace table datalake.sample.customer as select * from tpch.tiny.customer;
3. create or replace table datalake.sample.customer as select * from tpch.tiny.lineitem;

in the third command it shown the error

Nessie server type (docker/uber-jar/built from source) and version

docker version with nessie 0.71.0 and 0.77.0 and Trino 434

Client type (Ex: UI/Spark/pynessie ...) and version

Trino

Additional information

No response

[dimas-b]

Could you try with Trino 436 or later? This fix might help: trinodb/trino#17813

[shohamyamin]

I just tried with Trino 439 and I having the same error.

[shohamyamin]

SQL Error [65536]: Query failed (#20240301_144137_00027_dkew3): Bad Request (HTTP/400): New value to update existing key 'sample.customer' has no content ID

[shohamyamin]

@dimas-b you have any idea how to solve this or where the problem is?

[dimas-b]

@ajantha-bhat : Could you have a look, please?

[shohamyamin]

@ajantha-bhat @dimas-b any idea what is causing that?

[ajantha-bhat]

Hi, I checked in the latest version.

We already had a testcase BaseIcebergConnectorSmokeTest.testCreateOrReplaceTable() which runs as part of TestIcebergNessieCatalogConnectorSmokeTest.

It used to do a create table followed by a create/replace table.
I have tried adding another create/replace table like below in the end and it passed.

assertUpdate("CREATE OR REPLACE TABLE " + table.getName() + " AS SELECT BIGINT '99' a, DOUBLE '11.1' b", 1);
assertThat(query("SELECT a, b FROM " + table.getName())).matches("VALUES (BIGINT '99', 111e-1)");

I also tried just two create/replace table (by removing simple create). It passed aswell.

TLDR; I couldn't repro the issue. Please try it on the latest code or update the steps.

[ajantha-bhat]

commitToExistingTable from the callstack was having a bug and in the latest version (while bumping Iceberg to 1.5.0), it was fixed by adding a lookup to fetch the content id if it is null. Which might have fixed this issue.

So, feel free to try it on the latest version and let me know.

[ajantha-bhat]

Closing this as another user has confirmed that it is working on the latest version
trinodb/trino#19424 (comment)