{"record":{"id":"a46d61adff60f5e4","repo":"pentaho/pentaho-kettle","slug":"key-sorting-problem-detected-during-row-cache-lookup-the-a46d61","errorCode":null,"errorMessage":"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.","messagePattern":"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\\.","errorType":"exception","errorClass":"KettleException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionCache.java","lineNumber":158,"sourceCode":"              } else {\n                // This should never happen, it's a flaw in the data or the binary search algorithm...\n                // TODO: print the row perhaps?\n                //\n                throw new KettleException(\n                  \"Key sorting problem detected during row cache lookup: the lookup date of \"\n                    + \"the row retrieved is higher than or equal to the end of the date range.\" );\n              }\n            } else if ( fromDate != null && toDate == null ) {\n              // This is the case where the toDate is null and the fromDate is not.\n              // This is a special case where null as an end date means +Infinity\n              //\n              if ( fromDate.compareTo( lookupDate ) <= 0 ) {\n                return insertionPoint; // found the key!!\n              } else {\n                // This should never happen, it's a flaw in the data or the binary search algorithm...\n                // TODO: print the row perhaps?\n                //\n                throw new KettleException(\n                  \"Key sorting problem detected during row cache lookup: the lookup date of the row \"\n                    + \"retrieved is lower than or equal to the start of the date range.\" );\n              }\n            } else {\n              // Both dates are available: simply see if the lookup date falls in between...\n              //\n              if ( fromDate.compareTo( lookupDate ) <= 0 && toDate.compareTo( lookupDate ) > 0 ) {\n                return insertionPoint;\n              }\n              // Else this is a cache miss.\n            }\n          }\n        }\n      }\n      return index;\n    } catch ( RuntimeException e ) {\n      throw new KettleException( e );\n    }","sourceCodeStart":140,"sourceCodeEnd":176,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionCache.java#L140-L176","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","solutions":["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()).","Confirm the date field used for the range is the correct one in the DimensionLookup step metadata (stream vs table date field).","Disable the row cache on the DimensionLookup step to isolate whether ordering in the cache or the underlying data is at fault.","Reload/rebuild the dimension table so date ranges are ordered and consistent before running the transformation."],"exampleFix":"// before: row with a future start date breaks range ordering\n// UPDATE dim SET date_from = '2030-01-01' WHERE key = 1;\n// after: restore a valid, ordered range\n// UPDATE dim SET date_from = '2023-01-01' WHERE key = 1;","handlingStrategy":"validation","validationCode":"// SQL pre-check before running the transformation:\n// SELECT * FROM dim WHERE date_from > date_to OR date_from > NOW();\n// Abort if rows are returned.","typeGuard":null,"tryCatchPattern":"try {\n  lookupRow(...);\n} catch (KettleException e) {\n  if (e.getMessage().contains(\"Key sorting problem\")) {\n    // log the dimension key, repair data or rerun without cache\n  } else throw e;\n}","preventionTips":["Never set date_from later than date_to or the current date.","Sort/repair dimension rows after external inserts.","Test dimension loads in a staging environment first.","Disable the cache when suspecting unsorted data to isolate the fault."],"tags":["kettle","pdi","dimension-lookup","binary-search","data-corruption"],"backgroundTag":"internal-invariant-violation","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"}