This is an automated email from the ASF dual-hosted git repository.

bamaer pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git


The following commit(s) were added to refs/heads/main by this push:
     new 41d3fad1d9 Update: batch mode with an error hop reports the wrong row 
on SQL Ser… (#9251)
41d3fad1d9 is described below

commit 41d3fad1d9817f679841e37095d34c31f623d553
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Fri Nov 6 06:34:00 2026 +0100

    Update: batch mode with an error hop reports the wrong row on SQL Ser… 
(#9251)
    
    * Update: batch mode with an error hop reports the wrong row on SQL Server 
and Oracle, fixes #9248
    
    * Keep order of rows
---
 plugins/transforms/update/pom.xml                  |  11 +
 .../hop/pipeline/transforms/update/Update.java     | 280 ++++++++++++++++++-
 .../hop/pipeline/transforms/update/UpdateData.java |  82 +++++-
 .../update/messages/messages_en_US.properties      |   2 +
 .../transforms/update/UpdateBatchErrorTest.java    | 272 ++++++++++++++++++
 .../transforms/update/UpdateBatchH2Test.java       | 310 +++++++++++++++++++++
 6 files changed, 947 insertions(+), 10 deletions(-)

diff --git a/plugins/transforms/update/pom.xml 
b/plugins/transforms/update/pom.xml
index ea80ba001c..71ce70f747 100644
--- a/plugins/transforms/update/pom.xml
+++ b/plugins/transforms/update/pom.xml
@@ -42,6 +42,17 @@
     </dependencyManagement>
 
     <dependencies>
+        <dependency>
+            <groupId>com.h2database</groupId>
+            <artifactId>h2</artifactId>
+            <scope>test</scope>
+        </dependency>
+        <dependency>
+            <groupId>org.apache.hop</groupId>
+            <artifactId>hop-databases-h2</artifactId>
+            <version>${project.version}</version>
+            <scope>test</scope>
+        </dependency>
         <dependency>
             <groupId>org.apache.hop</groupId>
             <artifactId>hop-databases-postgresql</artifactId>
diff --git 
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
 
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
index 46c34b5858..32bdabeb61 100644
--- 
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
+++ 
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
@@ -17,12 +17,17 @@
 
 package org.apache.hop.pipeline.transforms.update;
 
+import com.google.common.annotations.VisibleForTesting;
+import java.sql.PreparedStatement;
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.util.ArrayList;
+import java.util.List;
 import org.apache.commons.lang3.ArrayUtils;
 import org.apache.hop.core.Const;
 import org.apache.hop.core.database.Database;
 import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.exception.HopDatabaseBatchException;
 import org.apache.hop.core.exception.HopDatabaseException;
 import org.apache.hop.core.exception.HopException;
 import org.apache.hop.core.exception.HopTransformException;
@@ -56,9 +61,13 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
     super(transformMeta, meta, data, copyNr, pipelineMeta, pipeline);
   }
 
+  /**
+   * @return the row for the output, or null when it goes to the error hop or 
waits in the batch
+   */
   private synchronized Object[] lookupValues(IRowMeta rowMeta, Object[] row) 
throws HopException {
     Object[] outputRow = row;
     Object[] add;
+    boolean batched = false;
 
     // Create the output row and copy the input values. add flag field!
     if (!Utils.isEmpty(meta.getIgnoreFlagField())) {
@@ -187,8 +196,18 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
                   + data.lookupParameterRowMeta.getString(lookupRow));
         }
         data.db.setValues(data.updateParameterRowMeta, updateRow, 
data.prepStatementUpdate);
-        data.db.insertRow(data.prepStatementUpdate, data.batchMode, 
!data.useSavepoints);
-        incrementLinesUpdated();
+        if (data.batchMode) {
+          // Only added to the batch: flushBatch() runs it, then passes the 
row on to the output or
+          // to the error hop, depending on what the database says about this 
row.
+          data.db.insertRow(data.prepStatementUpdate, true, false);
+          UpdateData.PendingRow pending = UpdateData.PendingRow.batched(row, 
outputRow, updateRow);
+          data.batchBuffer.add(pending);
+          data.pendingRows.add(pending);
+          batched = true;
+        } else {
+          data.db.insertRow(data.prepStatementUpdate, false, 
!data.useSavepoints);
+          incrementLinesUpdated();
+        }
       } else {
         incrementLinesSkipped();
       }
@@ -199,7 +218,7 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
       }
     }
 
-    return outputRow;
+    return batched ? null : outputRow;
   }
 
   @Override
@@ -210,7 +229,9 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
 
     Object[] r = getRow(); // Get row from input rowset & set row busy!
     if (r == null) { // no more input to be expected...
-
+      if (data.batchMode) {
+        flushBatchOrStop();
+      }
       setOutputDone();
       return false;
     }
@@ -340,10 +361,10 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
         commitEveryCommitSize();
       }
       if (data.keyNotFound) {
-        putError(inputRowMeta, r, 1L, data.stringErrorKeyNotFound, 
data.stringFieldnames, "UPD001");
+        putErrorInOrder(r, data.stringErrorKeyNotFound, data.stringFieldnames, 
"UPD001");
       }
       if (outputRow != null) {
-        putRow(data.outputRowMeta, outputRow); // copy non-ignored rows to 
output rowset(s)
+        putRowInOrder(outputRow); // copy non-ignored rows to output rowset(s)
       }
       if (checkFeedback(getLinesRead()) && isBasic()) {
         logBasic(BaseMessages.getString(PKG, "Update.Log.LineNumber") + 
getLinesRead());
@@ -362,13 +383,242 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
 
       if (sendToErrorRow) {
         // Simply add this row to the error row
-        putError(getInputRowMeta(), r, 1, errorMessage, null, "UPD001");
+        putErrorInOrder(r, errorMessage, null, "UPD001");
       }
     }
 
+    // Outside the try above: a failed batch is about the rows in it, not 
about this row.
+    // The rows waiting behind the batch count as well, so that memory stays 
at one commit size
+    // when few rows need an update: the batch then runs before it is full.
+    if (data.batchMode && data.pendingRows.size() >= data.commitSize && 
!flushBatchOrStop()) {
+      setOutputDone();
+      return false;
+    }
+
     return true;
   }
 
+  /**
+   * Passes a row on to the output, after the rows of a pending batch that 
came in before it.
+   *
+   * @param outputRow the row for the output
+   */
+  private void putRowInOrder(Object[] outputRow) throws HopTransformException {
+    if (data.batchBuffer.isEmpty()) {
+      putRow(data.outputRowMeta, outputRow);
+    } else {
+      data.pendingRows.add(UpdateData.PendingRow.output(outputRow));
+    }
+  }
+
+  /**
+   * Passes a row on to the error hop, after the rows of a pending batch that 
came in before it.
+   *
+   * @param inputRow the row as it came in
+   */
+  private void putErrorInOrder(
+      Object[] inputRow, String errorMessage, String errorFields, String 
errorCode)
+      throws HopTransformException {
+    if (data.batchBuffer.isEmpty()) {
+      putError(getInputRowMeta(), inputRow, 1L, errorMessage, errorFields, 
errorCode);
+    } else {
+      data.pendingRows.add(
+          UpdateData.PendingRow.error(inputRow, errorMessage, errorFields, 
errorCode));
+    }
+  }
+
+  /**
+   * Passes every pending row on in the order the rows came in: a failed row 
to the error hop, the
+   * others to the output. Empties the batch.
+   */
+  private void releasePendingRows() throws HopTransformException {
+    for (UpdateData.PendingRow row : data.pendingRows) {
+      if (row.isFailed()) {
+        putError(
+            getInputRowMeta(),
+            row.getInputRow(),
+            1L,
+            row.getErrorMessage(),
+            row.getErrorFields(),
+            row.getErrorCode());
+      } else {
+        putRow(data.outputRowMeta, row.getOutputRow());
+        if (row.isBatched()) {
+          incrementLinesUpdated();
+        }
+      }
+    }
+    clearPendingRows();
+  }
+
+  private void clearPendingRows() {
+    data.pendingRows.clear();
+    data.batchBuffer.clear();
+  }
+
+  /**
+   * Runs the batch, stopping the transform when that fails without an error 
hop.
+   *
+   * @return false when the transform was stopped
+   */
+  private boolean flushBatchOrStop() {
+    try {
+      flushBatch();
+      return true;
+    } catch (HopException e) {
+      logError(BaseMessages.getString(PKG, "Update.Log.ErrorInTransform"), e);
+      setErrors(1);
+      stopAll();
+      return false;
+    }
+  }
+
+  /**
+   * Runs and commits the JDBC batch, then passes every pending row on in the 
order the rows came
+   * in: a row of the batch goes to the output when the database updated it 
and to the error hop
+   * when it didn't; the rows that waited between them go where they were 
already headed.
+   *
+   * <p>Before, a row went to the output as soon as it was added to the batch, 
and a failed batch
+   * only surfaced on the row that happened to run it, at the commit size. So 
the error hop got that
+   * row (100, 200, ...) instead of the one that failed (97, 194, ...), which 
had already gone to
+   * the output.
+   *
+   * @throws HopException when the batch fails without an error hop: the 
transaction is rolled back
+   */
+  @VisibleForTesting
+  void flushBatch() throws HopException {
+    if (data.batchBuffer.isEmpty()) {
+      return;
+    }
+    PreparedStatement statement = data.prepStatementUpdate;
+    try {
+      statement.executeBatch();
+      data.db.commit();
+      statement.clearBatch();
+    } catch (SQLException e) {
+      HopDatabaseBatchException be =
+          Database.createHopDatabaseBatchException(
+              BaseMessages.getString(PKG, "Update.Exception.BatchFailed", 
data.schemaTable), e);
+      data.db.clearBatch(statement);
+      if (!getTransformMeta().isDoingErrorHandling()) {
+        data.db.rollback();
+        clearPendingRows();
+        throw be;
+      }
+      // Keep what the database did apply, then sort out the rows.
+      data.db.commit(true);
+      routeFailedBatch(be.toString(), be.getUpdateCounts(), 
be.getExceptionsList());
+      return;
+    }
+    releasePendingRows();
+  }
+
+  /**
+   * Works out which rows of a failed batch the database refused, whatever 
shape the update counts
+   * of the driver have, then passes every pending row on to exactly one 
stream, in the order the
+   * rows came in.
+   *
+   * <ul>
+   *   <li>One count per row, with {@link Statement#EXECUTE_FAILED} at the 
rows that failed: SQL
+   *       Server, MySQL, H2.
+   *   <li>Fewer counts than rows: the driver stopped at the first failure. 
The row at {@code
+   *       counts.length} failed and the rows after it were never sent, so 
they are sent again as a
+   *       new batch, which can fail in its turn: Oracle, Derby. A failure on 
the first row gives a
+   *       zero-length array.
+   *   <li>No counts: nothing says which rows were applied, and sending them 
again could update a
+   *       row twice, so they all go to the error hop.
+   * </ul>
+   *
+   * @throws HopException when there are more counts than rows: they can't 
belong to this batch
+   */
+  @VisibleForTesting
+  void routeFailedBatch(String errorMessage, int[] updateCounts, 
List<Exception> exceptionsList)
+      throws HopException {
+    List<UpdateData.PendingRow> buffer = data.batchBuffer;
+    int size = buffer.size();
+    if (updateCounts != null && updateCounts.length > size) {
+      clearPendingRows();
+      throw new HopException(
+          BaseMessages.getString(
+              PKG, "Update.Exception.BatchCountsMismatch", 
updateCounts.length, size));
+    }
+
+    int from = 0;
+    int[] counts = updateCounts;
+    String message = errorMessage;
+    List<Exception> exceptions = exceptionsList == null ? List.of() : 
exceptionsList;
+
+    while (from < size) {
+      int counted = counts == null ? 0 : counts.length;
+      int exceptionNr = 0;
+      for (int i = 0; i < counted; i++) {
+        // SUCCESS_NO_INFO and a count of zero (no row matched the key) are 
not failures.
+        if (counts[i] == Statement.EXECUTE_FAILED) {
+          String rowMessage = message;
+          if (exceptionNr < exceptions.size()) {
+            rowMessage = exceptions.get(exceptionNr++).toString();
+          }
+          buffer.get(from + i).fail(rowMessage, null, "UPD002");
+        }
+      }
+      from += counted;
+      if (from >= size) {
+        break;
+      }
+
+      if (counts == null) {
+        for (int i = from; i < size; i++) {
+          buffer.get(i).fail(message, null, "UPD002");
+        }
+        break;
+      }
+
+      // The driver stopped here: this is the row it refused.
+      buffer.get(from).fail(message, null, "UPD002");
+      from++;
+      if (from >= size) {
+        break;
+      }
+
+      // The rows after it were never sent.
+      try {
+        counts = retryBatch(from, size);
+        message = errorMessage;
+        exceptions = List.of();
+      } catch (HopDatabaseBatchException be) {
+        counts = be.getUpdateCounts();
+        message = be.toString();
+        exceptions = be.getExceptionsList() == null ? List.of() : 
be.getExceptionsList();
+      }
+    }
+    releasePendingRows();
+  }
+
+  /**
+   * Sends rows {@code [from, to)} of the batch again as one batch, and 
commits them.
+   *
+   * @return the update counts, relative to {@code from}
+   */
+  private int[] retryBatch(int from, int to) throws HopException {
+    PreparedStatement statement = data.prepStatementUpdate;
+    try {
+      for (int i = from; i < to; i++) {
+        data.db.setValues(
+            data.updateParameterRowMeta, 
data.batchBuffer.get(i).getBindValues(), statement);
+        statement.addBatch();
+      }
+      int[] counts = statement.executeBatch();
+      data.db.commit();
+      statement.clearBatch();
+      return counts;
+    } catch (SQLException e) {
+      data.db.clearBatch(statement);
+      data.db.commit(true);
+      throw Database.createHopDatabaseBatchException(
+          BaseMessages.getString(PKG, "Update.Exception.BatchFailed", 
data.schemaTable), e);
+    }
+  }
+
   /**
    * The lookup keys drive the WHERE clause of both the lookup SELECT and the 
UPDATE. Without a
    * single key we would generate a statement ending in a dangling WHERE, so 
fail with a
@@ -577,7 +827,11 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
         data.useSavepoints =
             
data.db.isUsingRowSavepoints(getTransformMeta().isDoingErrorHandling());
         // A failed row in a batch can't be undone on its own: with 
savepoints, no batch.
-        data.batchMode = meta.isUseBatchUpdate() && !data.useSavepoints;
+        data.batchMode =
+            meta.isUseBatchUpdate()
+                && !data.useSavepoints
+                && data.commitSize > 0
+                && data.db.getUseBatchInsert(true);
         if (meta.isUseBatchUpdate() && data.useSavepoints) {
           logBasic(BaseMessages.getString(PKG, 
"Update.Log.BatchOffForErrorHandling"));
         }
@@ -595,6 +849,9 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
   @Override
   public void batchComplete() throws HopException {
     // This is needed to properly handle transactions when we are using 
SingleThreadedExecutor
+    if (data.batchMode) {
+      flushBatch();
+    }
     commitBatch(false);
   }
 
@@ -611,7 +868,12 @@ public class Update extends BaseTransform<UpdateMeta, 
UpdateData> {
         if (!data.db.isAutoCommit()) {
           if (getErrors() == 0) {
             if (dispose) {
-              data.db.emptyAndCommit(data.prepStatementUpdate, data.batchMode);
+              if (data.batchMode && !data.batchBuffer.isEmpty()) {
+                // Stopped before the end of the input: run what is left, as 
before.
+                flushBatchOrStop();
+              }
+              // The batch is empty by now: only commit and close the 
statement.
+              data.db.emptyAndCommit(data.prepStatementUpdate, false);
               data.prepStatementUpdate = null;
             } else {
               data.db.commit();
diff --git 
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
 
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
index 1266c2d8cb..c806050ba7 100644
--- 
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
+++ 
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
@@ -18,6 +18,9 @@
 package org.apache.hop.pipeline.transforms.update;
 
 import java.sql.PreparedStatement;
+import java.util.ArrayList;
+import java.util.List;
+import lombok.Getter;
 import org.apache.hop.core.database.Database;
 import org.apache.hop.core.row.IRowMeta;
 import org.apache.hop.pipeline.transform.BaseTransformData;
@@ -33,9 +36,86 @@ public class UpdateData extends BaseTransformData implements 
ITransformData {
    */
   public boolean useSavepoints;
 
-  /** Whether the statements go out in JDBC batches: asked for, and no 
savepoint per row. */
+  /**
+   * Whether the statements go out in JDBC batches: asked for, no savepoint 
per row, a commit size
+   * and a database that runs batches on this connection.
+   */
   public boolean batchMode;
 
+  /**
+   * A row that waits for the current JDBC batch before it is passed on.
+   *
+   * <p>A row with bind values is in the batch: it goes to the output or the 
error hop once the
+   * database has said which. The other rows (nothing to update, key not 
found, an error of their
+   * own) already know where they go, but wait as well, so that rows leave in 
the order they came
+   * in.
+   */
+  @Getter
+  public static final class PendingRow {
+    /** The row as it came in, for the error hop. */
+    private final Object[] inputRow;
+
+    /** The row for the output, or null when it goes to the error hop. */
+    private final Object[] outputRow;
+
+    /** The UPDATE parameters of a row in the batch, to send it again; null 
for the other rows. */
+    private final Object[] bindValues;
+
+    private String errorMessage;
+    private String errorFields;
+
+    /** Set when the row goes to the error hop. */
+    private String errorCode;
+
+    private PendingRow(Object[] inputRow, Object[] outputRow, Object[] 
bindValues) {
+      this.inputRow = inputRow;
+      this.outputRow = outputRow;
+      this.bindValues = bindValues;
+    }
+
+    /** A row whose UPDATE is in the batch. */
+    public static PendingRow batched(Object[] inputRow, Object[] outputRow, 
Object[] bindValues) {
+      return new PendingRow(inputRow, outputRow, bindValues);
+    }
+
+    /** A row for the output that isn't in the batch. */
+    public static PendingRow output(Object[] outputRow) {
+      return new PendingRow(null, outputRow, null);
+    }
+
+    /** A row for the error hop that isn't in the batch. */
+    public static PendingRow error(
+        Object[] inputRow, String errorMessage, String errorFields, String 
errorCode) {
+      PendingRow row = new PendingRow(inputRow, null, null);
+      row.fail(errorMessage, errorFields, errorCode);
+      return row;
+    }
+
+    public boolean isBatched() {
+      return bindValues != null;
+    }
+
+    public boolean isFailed() {
+      return errorCode != null;
+    }
+
+    /** Sends the row to the error hop instead of the output. */
+    public void fail(String errorMessage, String errorFields, String 
errorCode) {
+      this.errorMessage = errorMessage;
+      this.errorFields = errorFields;
+      this.errorCode = errorCode;
+    }
+  }
+
+  /** The rows in the current JDBC batch, in the order they were added to it. 
*/
+  public final List<PendingRow> batchBuffer = new ArrayList<>();
+
+  /**
+   * Every row that waits for the current batch, in the order the rows came 
in: the rows of {@link
+   * #batchBuffer} and the rows that arrived between them. Empty when no batch 
is pending.
+   */
+  public final List<PendingRow> pendingRows = new ArrayList<>();
+
   /** The lookup of the current row found no row to update, with an error hop 
to send it to. */
   public boolean keyNotFound;
 
diff --git 
a/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
 
b/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
index a9a7daa759..4c1f2fb8a2 100644
--- 
a/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
+++ 
b/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
@@ -19,6 +19,8 @@ Update.Description=Update data in a database table based upon 
keys
 Update.Exception.FieldRequired=Field [{0}] is required and couldn''t be found!
 Update.Exception.KeyCouldNotFound=Entry to update with following key could not 
be found: 
 Update.Exception.NoKeyFields=No key fields are specified. At least one key is 
required to build the WHERE clause of the UPDATE statement, also when the Skip 
lookup option is enabled.
+Update.Exception.BatchFailed=Error updating a batch of rows in table [{0}]
+Update.Exception.BatchCountsMismatch=Unable to tell which rows of the batch 
failed: the database returned {0} update counts for a batch of {1} rows.
 Update.Init.ConnectionMissing=Database connection is missing for transform 
[{0}]!
 Update.Log.CheckingRow=Checking row: 
 Update.Log.ConnectedToDB=Connected to database...
diff --git 
a/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchErrorTest.java
 
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchErrorTest.java
new file mode 100644
index 0000000000..cf089c2850
--- /dev/null
+++ 
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchErrorTest.java
@@ -0,0 +1,272 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *       http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.update;
+
+import static java.sql.Statement.EXECUTE_FAILED;
+import static java.sql.Statement.SUCCESS_NO_INFO;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyLong;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.ArgumentMatchers.nullable;
+import static org.mockito.Mockito.doAnswer;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.doThrow;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.sql.BatchUpdateException;
+import java.sql.PreparedStatement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import java.util.stream.LongStream;
+import org.apache.hop.core.database.Database;
+import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.pipeline.PipelineMeta;
+import org.apache.hop.pipeline.engines.local.LocalPipelineEngine;
+import org.apache.hop.pipeline.transform.TransformMeta;
+import org.apache.hop.pipeline.transform.TransformPartitioningMeta;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Which stream each row of a failed batch goes to, for each shape of update 
counts a driver gives
+ * back. Every row in the batch has to leave on exactly one stream.
+ */
+class UpdateBatchErrorTest {
+
+  private static final int BATCH = 10;
+
+  private Update update;
+  private TransformMeta transformMeta;
+  private UpdateData data;
+  private PreparedStatement statement;
+  private List<Long> output;
+  private List<Long> errors;
+
+  @BeforeEach
+  void setUp() throws Exception {
+    UpdateMeta meta = mock(UpdateMeta.class);
+    transformMeta = mock(TransformMeta.class);
+    doReturn("update").when(transformMeta).getName();
+    doReturn(mock(TransformPartitioningMeta.class))
+        .when(transformMeta)
+        .getTargetTransformPartitioningMeta();
+    doReturn(meta).when(transformMeta).getTransform();
+    doReturn(true).when(transformMeta).isDoingErrorHandling();
+    PipelineMeta pipelineMeta = mock(PipelineMeta.class);
+    doReturn(transformMeta).when(pipelineMeta).findTransform(anyString());
+
+    IRowMeta rowMeta = new RowMeta();
+    rowMeta.addValueMeta(new ValueMetaInteger("id"));
+
+    data = new UpdateData();
+    data.db = mock(Database.class);
+    data.batchMode = true;
+    data.outputRowMeta = rowMeta;
+    data.updateParameterRowMeta = rowMeta;
+    statement = mock(PreparedStatement.class);
+    data.prepStatementUpdate = statement;
+    for (long id = 0; id < BATCH; id++) {
+      Object[] row = {id};
+      UpdateData.PendingRow pending = UpdateData.PendingRow.batched(row, row, 
row);
+      data.batchBuffer.add(pending);
+      data.pendingRows.add(pending);
+    }
+
+    update = spy(new Update(transformMeta, meta, data, 0, pipelineMeta, new 
LocalPipelineEngine()));
+    doReturn(transformMeta).when(update).getTransformMeta();
+    doReturn(rowMeta).when(update).getInputRowMeta();
+
+    output = new ArrayList<>();
+    errors = new ArrayList<>();
+    doAnswer(
+            inv -> {
+              output.add((Long) ((Object[]) inv.getArgument(1))[0]);
+              return null;
+            })
+        .when(update)
+        .putRow(any(IRowMeta.class), any());
+    doAnswer(
+            inv -> {
+              errors.add((Long) ((Object[]) inv.getArgument(1))[0]);
+              return null;
+            })
+        .when(update)
+        .putError(
+            any(IRowMeta.class),
+            any(),
+            anyLong(),
+            nullable(String.class),
+            nullable(String.class),
+            anyString());
+  }
+
+  private static int[] counts(int length, int... failed) {
+    int[] counts = new int[length];
+    Arrays.fill(counts, 1);
+    for (int i : failed) {
+      counts[i] = EXECUTE_FAILED;
+    }
+    return counts;
+  }
+
+  private void assertEveryRowLeftOnce() {
+    List<Long> all = new ArrayList<>(output);
+    all.addAll(errors);
+    all.sort(Long::compare);
+    assertEquals(
+        LongStream.range(0, BATCH).boxed().toList(),
+        all,
+        "every row of the batch must leave on exactly one stream");
+    assertTrue(data.batchBuffer.isEmpty(), "the batch must be emptied");
+    assertTrue(data.pendingRows.isEmpty(), "the pending rows must be emptied");
+  }
+
+  /** SQL Server, MySQL, H2: one count per row. */
+  @Test
+  void aCountPerRowMarksTheFailedRows() throws HopException {
+    update.routeFailedBatch("failed", counts(BATCH, 3, 7), null);
+
+    assertEquals(List.of(3L, 7L), errors);
+    assertEveryRowLeftOnce();
+    assertEquals(BATCH - 2, update.getLinesUpdated());
+  }
+
+  /** Oracle, Derby: stopped at row 3; rows 4..9 were never sent and are sent 
again. */
+  @Test
+  void aShortCountsArraySendsTheRowsAfterTheFailureAgain() throws Exception {
+    when(statement.executeBatch()).thenReturn(counts(BATCH - 4));
+
+    update.routeFailedBatch("failed", counts(3), null);
+
+    assertEquals(List.of(3L), errors);
+    assertEveryRowLeftOnce();
+    verify(statement, times(BATCH - 4)).addBatch();
+  }
+
+  /** The retry fails again, at its third row (row 6 of the batch). */
+  @Test
+  void aRetryThatFailsIsSortedOutTheSameWay() throws Exception {
+    when(statement.executeBatch())
+        .thenThrow(new BatchUpdateException("again", counts(2)))
+        .thenReturn(counts(3));
+
+    update.routeFailedBatch("failed", counts(3), null);
+
+    assertEquals(List.of(3L, 6L), errors);
+    assertEveryRowLeftOnce();
+  }
+
+  /** A failure on the first row gives a zero-length array, not null. */
+  @Test
+  void aFailureOnTheFirstRow() throws Exception {
+    when(statement.executeBatch()).thenReturn(counts(BATCH - 1));
+
+    update.routeFailedBatch("failed", new int[0], null);
+
+    assertEquals(List.of(0L), errors);
+    assertEveryRowLeftOnce();
+  }
+
+  /** Nothing says which rows were applied: sending them again could update a 
row twice. */
+  @Test
+  void noCountsSendsEveryRowToTheErrorHop() throws Exception {
+    update.routeFailedBatch("failed", null, null);
+
+    assertEquals(BATCH, errors.size());
+    assertEveryRowLeftOnce();
+    verify(statement, never()).executeBatch();
+  }
+
+  @Test
+  void successNoInfoAndZeroRowsAreNotFailures() throws HopException {
+    int[] counts = counts(BATCH, 5);
+    counts[1] = SUCCESS_NO_INFO;
+    counts[2] = 0;
+
+    update.routeFailedBatch("failed", counts, null);
+
+    assertEquals(List.of(5L), errors);
+    assertEveryRowLeftOnce();
+  }
+
+  /**
+   * Rows that need no update wait behind the batch: the output keeps the 
order the rows came in,
+   * also when the batch fails.
+   */
+  @Test
+  void rowsThatWaitedBehindTheBatchKeepTheirPlace() throws HopException {
+    // 100 and 101 came in after batched row 2, 102 after batched row 7; 103 
is an error of its own.
+    data.pendingRows.add(3, UpdateData.PendingRow.output(new Object[] {100L}));
+    data.pendingRows.add(4, UpdateData.PendingRow.output(new Object[] {101L}));
+    data.pendingRows.add(10, UpdateData.PendingRow.output(new Object[] 
{102L}));
+    data.pendingRows.add(
+        11, UpdateData.PendingRow.error(new Object[] {103L}, "not found", 
"id", "UPD001"));
+
+    update.routeFailedBatch("failed", counts(BATCH, 3), null);
+
+    assertEquals(List.of(0L, 1L, 2L, 100L, 101L, 4L, 5L, 6L, 7L, 102L, 8L, 
9L), output);
+    assertEquals(List.of(3L, 103L), errors);
+    assertEquals(BATCH - 1, update.getLinesUpdated());
+    assertTrue(data.pendingRows.isEmpty());
+    assertTrue(data.batchBuffer.isEmpty());
+  }
+
+  @Test
+  void moreCountsThanRowsFails() {
+    assertThrows(
+        HopException.class, () -> update.routeFailedBatch("failed", 
counts(BATCH + 1), null));
+  }
+
+  /** Without an error hop a failed batch rolls back and stops the transform. 
*/
+  @Test
+  void withoutErrorHopTheBatchIsRolledBack() throws Exception {
+    doReturn(false).when(transformMeta).isDoingErrorHandling();
+    doThrow(new BatchUpdateException("failed", counts(BATCH, 
3))).when(statement).executeBatch();
+
+    assertThrows(HopException.class, () -> update.flushBatch());
+
+    verify(data.db).rollback();
+    assertTrue(output.isEmpty());
+    assertTrue(errors.isEmpty());
+    assertTrue(data.batchBuffer.isEmpty());
+  }
+
+  @Test
+  void aBatchThatRunsPassesEveryRowOn() throws Exception {
+    when(statement.executeBatch()).thenReturn(counts(BATCH));
+
+    update.flushBatch();
+
+    assertEquals(BATCH, output.size());
+    assertTrue(errors.isEmpty());
+    assertEveryRowLeftOnce();
+    verify(data.db).commit();
+  }
+}
diff --git 
a/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchH2Test.java
 
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchH2Test.java
new file mode 100644
index 0000000000..bb48a3dd33
--- /dev/null
+++ 
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchH2Test.java
@@ -0,0 +1,310 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *       http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.update;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Queue;
+import java.util.Set;
+import java.util.StringJoiner;
+import java.util.TreeSet;
+import java.util.UUID;
+import java.util.concurrent.ConcurrentLinkedQueue;
+import java.util.function.LongFunction;
+import java.util.stream.LongStream;
+import org.apache.hop.core.HopClientEnvironment;
+import org.apache.hop.core.database.Database;
+import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.DatabasePluginType;
+import org.apache.hop.core.logging.LoggingObject;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.variables.Variables;
+import org.apache.hop.databases.h2.H2DatabaseMeta;
+import org.apache.hop.metadata.serializer.memory.MemoryMetadataProvider;
+import org.apache.hop.pipeline.PipelineHopMeta;
+import org.apache.hop.pipeline.PipelineMeta;
+import org.apache.hop.pipeline.engines.local.LocalPipelineEngine;
+import org.apache.hop.pipeline.transform.TransformErrorMeta;
+import org.apache.hop.pipeline.transform.TransformMeta;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Update with "use batch update" against a real JDBC batch. H2, like SQL 
Server and MySQL, runs the
+ * whole batch and marks the rows that failed in the update counts.
+ *
+ * <p>A failed batch used to surface on the row that ran it, at the commit 
size: the error hop got
+ * rows 100, 200, ... instead of the ones that failed, and the failed rows had 
already gone to the
+ * output.
+ */
+class UpdateBatchH2Test {
+
+  private static final String CONNECTION = "h2";
+  private static final int ROWS = 250;
+  private static final int COMMIT_SIZE = 100;
+  private static final int BAD_EVERY = 47;
+
+  private DatabaseMeta databaseMeta;
+
+  /** How many rows had gone to the output each time the transform read a row. 
*/
+  private final List<Integer> outputSizeAtRead = new ArrayList<>();
+
+  @BeforeAll
+  static void setUpOnce() throws Exception {
+    HopClientEnvironment.init();
+    
DatabasePluginType.getInstance().registerClassPathPlugin(H2DatabaseMeta.class);
+    DatabaseMeta.clearDatabaseInterfacesMap();
+  }
+
+  @BeforeEach
+  void setUp() throws Exception {
+    String databaseName =
+        "mem:update" + UUID.randomUUID().toString().replace("-", "") + 
";DB_CLOSE_DELAY=-1";
+    databaseMeta = new DatabaseMeta(CONNECTION, "H2", "Native", "", 
databaseName, "", "sa", "");
+    try (Database db = new Database(new LoggingObject("setup"), new 
Variables(), databaseMeta)) {
+      db.connect();
+      db.execStatement("CREATE TABLE target (id BIGINT PRIMARY KEY, name 
VARCHAR(5))");
+      db.execStatement("INSERT INTO target SELECT x, 'x' FROM SYSTEM_RANGE(1, 
" + ROWS + ")");
+    }
+  }
+
+  private static boolean bad(long id) {
+    return id % BAD_EVERY == 0;
+  }
+
+  /** Every row is set to 'a', except the bad ones, whose name is too long for 
the column. */
+  private static Queue<Object[]> input() {
+    return input(id -> bad(id) ? "toolong" : "a");
+  }
+
+  private static Queue<Object[]> input(LongFunction<String> name) {
+    Queue<Object[]> input = new ConcurrentLinkedQueue<>();
+    for (long id = 1; id <= ROWS; id++) {
+      input.add(new Object[] {id, name.apply(id)});
+    }
+    return input;
+  }
+
+  private static List<Long> idList(List<Object[]> rows) {
+    List<Long> ids = new ArrayList<>();
+    for (Object[] row : rows) {
+      ids.add((Long) row[0]);
+    }
+    return ids;
+  }
+
+  private String table() throws Exception {
+    StringJoiner content = new StringJoiner(",");
+    try (Database db = new Database(new LoggingObject("check"), new 
Variables(), databaseMeta)) {
+      db.connect();
+      for (Object[] row : db.getRows("SELECT id, name FROM target ORDER BY 
id", -1)) {
+        content.add(row[0] + ":" + row[1]);
+      }
+    }
+    return content.toString();
+  }
+
+  private static String expectedTable(boolean updated) {
+    StringJoiner content = new StringJoiner(",");
+    for (long id = 1; id <= ROWS; id++) {
+      content.add(id + ":" + (updated && !bad(id) ? "a" : "x"));
+    }
+    return content.toString();
+  }
+
+  private static Set<Long> ids(List<Object[]> rows) {
+    Set<Long> ids = new TreeSet<>();
+    for (Object[] row : rows) {
+      assertTrue(ids.add((Long) row[0]), "row " + row[0] + " was passed on 
twice");
+    }
+    return ids;
+  }
+
+  /** An Update in batch mode, with an error hop when errors isn't null. */
+  private Update update(Queue<Object[]> input, List<Object[]> output, 
List<Object[]> errors)
+      throws Exception {
+    IRowMeta inputRowMeta = new RowMeta();
+    inputRowMeta.addValueMeta(new ValueMetaInteger("id"));
+    inputRowMeta.addValueMeta(new ValueMetaString("name"));
+
+    PipelineMeta pipelineMeta = new PipelineMeta();
+    MemoryMetadataProvider metadataProvider = new MemoryMetadataProvider();
+    metadataProvider.getSerializer(DatabaseMeta.class).save(databaseMeta);
+    pipelineMeta.setMetadataProvider(metadataProvider);
+
+    UpdateMeta meta = new UpdateMeta();
+    meta.setDefault();
+    meta.setConnection(CONNECTION);
+    meta.setCommitSize(Integer.toString(COMMIT_SIZE));
+    meta.setUseBatchUpdate(true);
+    meta.getLookupField().setSchemaName("");
+    meta.getLookupField().setTableName("target");
+    meta.getLookupField().getLookupKeys().add(new UpdateKeyField("id", "id", 
"="));
+    meta.getLookupField().getUpdateFields().add(new UpdateField("name", 
"name"));
+    TransformMeta transformMeta = new TransformMeta("update", meta);
+    pipelineMeta.addTransform(transformMeta);
+
+    Update update =
+        new Update(
+            transformMeta, meta, new UpdateData(), 0, pipelineMeta, new 
LocalPipelineEngine()) {
+          @Override
+          public IRowMeta getInputRowMeta() {
+            return inputRowMeta;
+          }
+
+          @Override
+          public Object[] getRow() {
+            outputSizeAtRead.add(output.size());
+            Object[] row = input.poll();
+            return row == null ? null : row.clone();
+          }
+
+          @Override
+          public void putRow(IRowMeta rowMeta, Object[] row) {
+            output.add(row);
+          }
+
+          @Override
+          public void putError(
+              IRowMeta rowMeta,
+              Object[] row,
+              long nrErrors,
+              String errorDescriptions,
+              String fieldNames,
+              String errorCodes) {
+            errors.add(row);
+          }
+        };
+
+    if (errors != null) {
+      // After the transform: its constructor looks at the hops of the 
pipeline.
+      TransformMeta target = new TransformMeta("errors", new UpdateMeta());
+      pipelineMeta.addTransform(target);
+      pipelineMeta.addPipelineHop(new PipelineHopMeta(transformMeta, target));
+      TransformErrorMeta errorMeta = new TransformErrorMeta(transformMeta, 
target);
+      errorMeta.setEnabled(true);
+      transformMeta.setTransformErrorMeta(errorMeta);
+    }
+    return update;
+  }
+
+  private static void run(Update update) {
+    assertTrue(update.init());
+    try {
+      while (update.processRow()) {
+        // keep going
+      }
+    } catch (Exception e) {
+      throw new AssertionError(e);
+    } finally {
+      update.dispose();
+    }
+  }
+
+  @Test
+  void theRowsThatFailedGoToTheErrorHop() throws Exception {
+    List<Object[]> output = new ArrayList<>();
+    List<Object[]> errors = new ArrayList<>();
+    Update update = update(input(), output, errors);
+
+    run(update);
+
+    Set<Long> expectedErrors = new TreeSet<>();
+    Set<Long> expectedOutput = new TreeSet<>();
+    for (long id = 1; id <= ROWS; id++) {
+      (bad(id) ? expectedErrors : expectedOutput).add(id);
+    }
+    // 47, 94, 141, 188 and 235: the last one is in the partial batch run at 
the end of the input.
+    assertEquals(expectedErrors, ids(errors));
+    assertEquals(expectedOutput, ids(output));
+    assertEquals(0, update.getErrors());
+    assertEquals(expectedOutput.size(), update.getLinesUpdated());
+    assertEquals(expectedTable(true), table());
+  }
+
+  /**
+   * Rows that need no update wait for the batch in front of them, so the 
output keeps the order of
+   * the input. Here the even rows already hold their value and only the odd 
ones are updated.
+   */
+  @Test
+  void theOutputKeepsTheOrderOfTheInput() throws Exception {
+    List<Object[]> output = new ArrayList<>();
+    List<Object[]> errors = new ArrayList<>();
+    Update update =
+        update(input(id -> id % 2 == 0 ? "x" : bad(id) ? "toolong" : "a"), 
output, errors);
+
+    run(update);
+
+    List<Long> expectedOutput = new ArrayList<>();
+    List<Long> expectedErrors = new ArrayList<>();
+    StringJoiner expectedTable = new StringJoiner(",");
+    for (long id = 1; id <= ROWS; id++) {
+      boolean failed = id % 2 != 0 && bad(id);
+      (failed ? expectedErrors : expectedOutput).add(id);
+      expectedTable.add(id + ":" + (id % 2 != 0 && !failed ? "a" : "x"));
+    }
+    assertEquals(expectedOutput, idList(output));
+    assertEquals(expectedErrors, idList(errors));
+    assertEquals(0, update.getErrors());
+    assertEquals(ROWS / 2 - expectedErrors.size(), update.getLinesUpdated());
+    assertEquals(ROWS / 2, update.getLinesSkipped());
+    assertEquals(expectedTable.toString(), table());
+  }
+
+  /**
+   * Only the first row needs an update. The rows behind it wait for the 
batch, but no more than a
+   * commit size of them: the batch then runs before it is full, so they don't 
pile up in memory.
+   */
+  @Test
+  void rowsWaitingBehindTheBatchAreCappedAtTheCommitSize() throws Exception {
+    List<Object[]> output = new ArrayList<>();
+    List<Object[]> errors = new ArrayList<>();
+    Update update = update(input(id -> id == 1 ? "a" : "x"), output, errors);
+
+    run(update);
+
+    assertEquals(LongStream.rangeClosed(1, ROWS).boxed().toList(), 
idList(output));
+    assertTrue(errors.isEmpty());
+    assertEquals(1, update.getLinesUpdated());
+    for (int read = 0; read < outputSizeAtRead.size(); read++) {
+      assertTrue(
+          read - outputSizeAtRead.get(read) <= COMMIT_SIZE,
+          "more than a commit size of rows waited at read " + read);
+    }
+  }
+
+  @Test
+  void withoutErrorHopAFailedBatchStopsTheTransformAndRollsBack() throws 
Exception {
+    List<Object[]> output = new ArrayList<>();
+    Update update = update(input(), output, null);
+
+    run(update);
+
+    assertEquals(1, update.getErrors());
+    // Row 47 fails the first batch: none of its rows was passed on.
+    assertTrue(output.isEmpty(), "rows of a failed batch went to the output");
+    assertEquals(expectedTable(false), table());
+  }
+}

Reply via email to