pentaho/pentaho-kettle · error · KettleDatabaseException

Only 1 row was expected as a result of a lookup, and at…

Error message

Only 1 row was expected as a result of a lookup, and at least 2 were found!

What it means

Thrown by Database's lookup (getLookup) when failOnMultipleResults is enabled and the lookup query returns two or more rows, while the API contract expects exactly one row back. Kettle aborts rather than silently picking an arbitrary row, since that could yield wrong data.

Solutions

  1. Make the lookup key unique (use the primary key or add a unique constraint)
  2. Deduplicate source table rows or add additional filter conditions to the lookup
  3. Disable failOnMultipleResults only if returning any first row is acceptable
  4. Clean up existing duplicates before rerunning the transformation

Example fix

// before
lookupKey = "email"; // non-unique, matches multiple rows
// after
lookupKey = "customer_id"; // unique primary key, guarantees a single row
Defensive patterns

Strategy: validation

Validate before calling

// check uniqueness of lookup key before running the lookup
long dupes = countDuplicates(connection, tableName, lookupKeyColumn);
if ( dupes > 0 ) throw new IllegalStateException(lookupKeyColumn + " is not unique: " + dupes + " duplicates");

Try / catch

try {
  RowMetaAndData row = database.getLookup(...);
} catch ( KettleDatabaseException e ) {
  if ( e.getMessage().contains("at least 2 were found") ) {
    logError("Lookup key not unique - fix data or key selection");
  }
  throw e;
}

Prevention

When it happens

Trigger: Executing a DB lookup where the WHERE-key conditions are not selective enough (non-unique key), so the SELECT returns >= 2 rows while the caller asserted a single-row result.

Common situations: Looking up on a non-primary-key or non-unique column; duplicate rows after data-quality issues or missing unique constraint; joining on a truncated/case-insensitive key that matches several records.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/fe979ae1c16a3747. Report an issue: GitHub.

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/database/Database.java:3379

  }

  public Object[] getLookup( PreparedStatement ps, boolean failOnMultipleResults ) throws KettleDatabaseException {
    return getLookup( ps, failOnMultipleResults, false );
  }

  public Object[] getLookup( PreparedStatement ps, boolean failOnMultipleResults, boolean lazyConversion )
    throws KettleDatabaseException {
    log.snap( Metrics.METRIC_DATABASE_GET_LOOKUP_START, databaseMeta.getName() );
    try ( ResultSet res = ps.executeQuery() ) {
      Object[] ret = getRow( res, lazyConversion );

      if ( failOnMultipleResults ) {
        if ( ret != null && res.next() ) {
          // if the previous row was null, there's no reason to try res.next()
          // again.
          // on DB2 this will even cause an exception (because of the buggy DB2
          // JDBC driver).
          throw new KettleDatabaseException(
            "Only 1 row was expected as a result of a lookup, and at least 2 were found!" );
        }
      }
      return ret;
    } catch ( SQLException ex ) {
      throw new KettleDatabaseException( "Error looking up row in database", ex );
    } finally {
      log.snap( Metrics.METRIC_DATABASE_GET_LOOKUP_STOP, databaseMeta.getName() );
    }
  }

  public DatabaseMetaData getDatabaseMetaData() throws KettleDatabaseException {
    if ( dbmd == null ) {
      try {
        log.snap( Metrics.METRIC_DATABASE_GET_DBMETA_START, databaseMeta.getName() );

        if ( connection == null ) {
          throw new KettleDatabaseException( BaseMessages.getString( PKG,

View on GitHub (pinned to f3058517a1)