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 higher than or equal to the end of the date range.

What it means

DimensionCache.lookupRow performs a binary search over cached dimension rows using each row's date range (fromDate/toDate). When the candidate row is found via toDate comparison but its fromDate is after the lookup date (or the toDate branch fails consistency), the code concludes the cache is not sorted as the algorithm assumes and throws this KettleException. It signals corrupted or misordered data rather than a normal 'not found' outcome.

Solutions

  1. Verify the dimension table's date ranges are contiguous and sorted: ensure each row's date_from <= date_to and no row starts after its range should.
  2. Inspect and fix rows where date_from is later than date_to or later than lookup dates (SQL: SELECT * FROM dim WHERE date_from > date_to OR date_from > CURRENT_DATE).
  3. Re-run the DimensionLookup step with the cache disabled to confirm whether the problem is cache ordering or source data.
  4. If data is legitimately unsorted, reload/rebuild the dimension so ranges are ordered before lookups.

Example fix

// before: dimension rows with overlapping/unsorted ranges loaded externally
// INSERT INTO dim(key, date_from, date_to) VALUES (1, '2024-01-01', '2023-01-01');
// after: correct the range so from <= to
// INSERT INTO dim(key, date_from, date_to) VALUES (1, '2023-01-01', '2024-01-01');
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();
// Fail the ETL if any rows are returned.

Try / catch

try {
  lookupRow(...);
} catch (KettleException e) {
  if (e.getMessage().contains("Key sorting problem")) {
    // log offending rows, disable cache or fail the transformation
  } else throw e;
}

Prevention

When it happens

Trigger: Binary search finds an insertion point where toDate > lookupDate, but fromDate >= lookupDate (or the comparison otherwise contradicts the expected ordering), so neither the date-range match nor the null-toDate/+Infinity branch applies.

Common situations: Dimension table rows with overlapping or out-of-order date ranges loaded by external ETL; rows inserted into the cache unsorted; corrupted fromDate values (e.g. dates far in the future) in the source table; clocks/timezone skew producing future start dates.

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/3528f776400dacfd. Report an issue: GitHub.

Appendix: source

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

          if ( cmp == 0 ) {
            // The natural keys match, now see if the lookup date (lookupRowData[fromDateIndex]) is between
            // row[fromDateIndex] and row[toDateIndex]
            //
            Date fromDate = rowMeta.getDate( row, fromDateIndex );
            Date toDate = rowMeta.getDate( row, toDateIndex );
            Date lookupDate = rowMeta.getDate( lookupRowData, fromDateIndex );

            if ( fromDate == null && toDate != null ) {
              // This is the case where the fromDate is null and the toDate is not.
              // This is a special case where null as a start date means -Infinity
              //
              if ( toDate.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 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 {

View on GitHub (pinned to f3058517a1)