pentaho/pentaho-kettle · error · KettleException

Key sorting problem detected during row cache lookup: the…

Error message

Key sorting problem detected during row cache lookup: the lookup date of the row retrieved is lower than or equal to the start of the date range.

What it means

In DimensionCache.lookupRow's binary search, when a candidate row's toDate is null (meaning +Infinity) or in the mirrored branch, the code checks that fromDate <= lookupDate. If fromDate is instead greater than the lookup date while the search position implies the row should match, the assumed key/date sorting invariant is broken and this KettleException is thrown. Like its sibling (line 144), it flags a flaw in the data or the search algorithm, not a normal lookup miss.

Solutions

  1. Find and fix dimension rows whose date_from exceeds the lookup date or date_to (SQL: SELECT * FROM dim WHERE date_from > date_to OR date_from > NOW()).
  2. Confirm the date field used for the range is the correct one in the DimensionLookup step metadata (stream vs table date field).
  3. Disable the row cache on the DimensionLookup step to isolate whether ordering in the cache or the underlying data is at fault.
  4. Reload/rebuild the dimension table so date ranges are ordered and consistent before running the transformation.

Example fix

// before: row with a future start date breaks range ordering
// UPDATE dim SET date_from = '2030-01-01' WHERE key = 1;
// after: restore a valid, ordered range
// UPDATE dim SET date_from = '2023-01-01' WHERE key = 1;
Defensive patterns

Strategy: validation

Validate before calling

// SQL pre-check before running the transformation:
// SELECT * FROM dim WHERE date_from > date_to OR date_from > NOW();
// Abort if rows are returned.

Try / catch

try {
  lookupRow(...);
} catch (KettleException e) {
  if (e.getMessage().contains("Key sorting problem")) {
    // log the dimension key, repair data or rerun without cache
  } else throw e;
}

Prevention

When it happens

Trigger: Binary search reaches a row where fromDate > lookupDate at an insertion point that should have matched (e.g. toDate null with fromDate after the lookup date, or comparison results inconsistent with sorted order).

Common situations: Dimension rows with start dates after the range end or in the future; rows appended out of chronological order; null/incorrect date_from values from manual data fixes; upstream steps delivering unsorted or wrongly dated rows.

Understand the failure class

Background: "This is a bug, please report it": internal invariant violations, unreachable panics, and SNH errors explained — this error's family across 47 libraries.

Related errors

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionCache.java:158

              } else {
                // This should never happen, it's a flaw in the data or the binary search algorithm...
                // TODO: print the row perhaps?
                //
                throw new KettleException(
                  "Key sorting problem detected during row cache lookup: the lookup date of "
                    + "the row retrieved is higher than or equal to the end of the date range." );
              }
            } else if ( fromDate != null && toDate == null ) {
              // This is the case where the toDate is null and the fromDate is not.
              // This is a special case where null as an end date means +Infinity
              //
              if ( fromDate.compareTo( lookupDate ) <= 0 ) {
                return insertionPoint; // found the key!!
              } else {
                // This should never happen, it's a flaw in the data or the binary search algorithm...
                // TODO: print the row perhaps?
                //
                throw new KettleException(
                  "Key sorting problem detected during row cache lookup: the lookup date of the row "
                    + "retrieved is lower than or equal to the start of the date range." );
              }
            } else {
              // Both dates are available: simply see if the lookup date falls in between...
              //
              if ( fromDate.compareTo( lookupDate ) <= 0 && toDate.compareTo( lookupDate ) > 0 ) {
                return insertionPoint;
              }
              // Else this is a cache miss.
            }
          }
        }
      }
      return index;
    } catch ( RuntimeException e ) {
      throw new KettleException( e );
    }

View on GitHub (pinned to f3058517a1)