{"record":{"id":"fe979ae1c16a3747","repo":"pentaho/pentaho-kettle","slug":"only-1-row-was-expected-as-a-result-of-a-lookup-and-at-least","errorCode":null,"errorMessage":"Only 1 row was expected as a result of a lookup, and at least 2 were found!","messagePattern":"Only 1 row was expected as a result of a lookup, and at least 2 were found!","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"core/src/main/java/org/pentaho/di/core/database/Database.java","lineNumber":3379,"sourceCode":"  }\n\n  public Object[] getLookup( PreparedStatement ps, boolean failOnMultipleResults ) throws KettleDatabaseException {\n    return getLookup( ps, failOnMultipleResults, false );\n  }\n\n  public Object[] getLookup( PreparedStatement ps, boolean failOnMultipleResults, boolean lazyConversion )\n    throws KettleDatabaseException {\n    log.snap( Metrics.METRIC_DATABASE_GET_LOOKUP_START, databaseMeta.getName() );\n    try ( ResultSet res = ps.executeQuery() ) {\n      Object[] ret = getRow( res, lazyConversion );\n\n      if ( failOnMultipleResults ) {\n        if ( ret != null && res.next() ) {\n          // if the previous row was null, there's no reason to try res.next()\n          // again.\n          // on DB2 this will even cause an exception (because of the buggy DB2\n          // JDBC driver).\n          throw new KettleDatabaseException(\n            \"Only 1 row was expected as a result of a lookup, and at least 2 were found!\" );\n        }\n      }\n      return ret;\n    } catch ( SQLException ex ) {\n      throw new KettleDatabaseException( \"Error looking up row in database\", ex );\n    } finally {\n      log.snap( Metrics.METRIC_DATABASE_GET_LOOKUP_STOP, databaseMeta.getName() );\n    }\n  }\n\n  public DatabaseMetaData getDatabaseMetaData() throws KettleDatabaseException {\n    if ( dbmd == null ) {\n      try {\n        log.snap( Metrics.METRIC_DATABASE_GET_DBMETA_START, databaseMeta.getName() );\n\n        if ( connection == null ) {\n          throw new KettleDatabaseException( BaseMessages.getString( PKG,","sourceCodeStart":3361,"sourceCodeEnd":3397,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/core/src/main/java/org/pentaho/di/core/database/Database.java#L3361-L3397","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Make the lookup key unique (use the primary key or add a unique constraint)","Deduplicate source table rows or add additional filter conditions to the lookup","Disable failOnMultipleResults only if returning any first row is acceptable","Clean up existing duplicates before rerunning the transformation"],"exampleFix":"// before\nlookupKey = \"email\"; // non-unique, matches multiple rows\n// after\nlookupKey = \"customer_id\"; // unique primary key, guarantees a single row","handlingStrategy":"validation","validationCode":"// check uniqueness of lookup key before running the lookup\nlong dupes = countDuplicates(connection, tableName, lookupKeyColumn);\nif ( dupes > 0 ) throw new IllegalStateException(lookupKeyColumn + \" is not unique: \" + dupes + \" duplicates\");","typeGuard":null,"tryCatchPattern":"try {\n  RowMetaAndData row = database.getLookup(...);\n} catch ( KettleDatabaseException e ) {\n  if ( e.getMessage().contains(\"at least 2 were found\") ) {\n    logError(\"Lookup key not unique - fix data or key selection\");\n  }\n  throw e;\n}","preventionTips":["Look up only on primary keys or columns with unique constraints","Add unique indexes to lookup tables","De-duplicate source data before lookups","Keep failOnMultipleResults enabled to catch non-unique keys early"],"tags":["database","lookup","duplicate-rows","data-integrity"],"backgroundTag":"unexpected-response-shape","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}