{"record":{"id":"db49769946d401c4","repo":"apache/beam","slug":"s-side-of-an-outer-join-must-be-a-non-seekable-table","errorCode":null,"errorMessage":"%s side of an OUTER JOIN must be a non Seekable table.","messagePattern":"(.+?) side of an OUTER JOIN must be a non Seekable table\\.","errorType":"exception","errorClass":"java.lang.UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rel/BeamSideInputLookupJoinRel.java","lineNumber":89,"sourceCode":"    super(cluster, traitSet, left, right, condition, variablesSet, joinType);\n  }\n\n  @Override\n  public PTransform<PCollectionList<Row>, PCollection<Row>> buildPTransform() {\n    // if one of the sides is Seekable & the other is non Seekable\n    // then do a sideInputLookup join.\n    // When doing a sideInputLookup join, the windowFn does not need to match.\n    // Only support INNER JOIN & LEFT OUTER JOIN where left side of the join must be\n    // non Seekable & RIGHT OUTER JOIN where right side of the join must be non Seekable\n    if (joinType == JoinRelType.FULL) {\n      throw new UnsupportedOperationException(\n          \"FULL OUTER JOIN is not supported when join \"\n              + \"a Seekable table with a non Seekable table.\");\n    }\n\n    if ((joinType == JoinRelType.LEFT && seekableInputIndex().get() == 0)\n        || (joinType == JoinRelType.RIGHT && seekableInputIndex().get() == 1)) {\n      throw new UnsupportedOperationException(\n          String.format(\"%s side of an OUTER JOIN must be a non Seekable table.\", joinType.name()));\n    }\n    return new SideInputLookupJoin();\n  }\n\n  private class SideInputLookupJoin extends PTransform<PCollectionList<Row>, PCollection<Row>> {\n\n    @Override\n    public PCollection<Row> expand(PCollectionList<Row> pinput) {\n      Schema schema = CalciteUtils.toSchema(getRowType());\n\n      BeamRelNode seekableRel =\n          BeamSqlRelUtils.getBeamRelInput(getInput(seekableInputIndex().get()));\n      BeamRelNode nonSeekableRel =\n          BeamSqlRelUtils.getBeamRelInput(getInput(nonSeekableInputIndex().get()));\n\n      // Offset field references according to which table is on the left\n      int factColOffset =","sourceCodeStart":71,"sourceCodeEnd":107,"githubUrl":"https://github.com/apache/beam/blob/12126d8942aaf848030c478b4c6a28c6af861c66/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rel/BeamSideInputLookupJoinRel.java#L71-L107","documentation":"For side-input lookup joins, LEFT OUTER JOIN requires the left (outer) side to be the non-seekable table, and RIGHT OUTER JOIN requires the right side to be non-seekable — the seekable side is broadcast/looked up, and the outer side drives the stream. If the outer side is the seekable table, translation throws this UnsupportedOperationException.","triggerScenarios":"BeamSideInputLookupJoinRel.buildPTransform sees joinType==LEFT with seekableInputIndex()==0 (left side seekable), or joinType==RIGHT with seekableInputIndex()==1 (right side seekable).","commonSituations":"Writing 'SELECT ... FROM seekable_table LEFT OUTER JOIN streaming_table ...' when the rule expects the reverse orientation; users often don't know which table is registered as Seekable.","solutions":["Swap the join operands so the non-seekable table is on the OUTER side: RIGHT OUTER JOIN with the seekable table on the left, or LEFT OUTER JOIN with it on the right.","Verify which input is Seekable (seekableInputIndex) and orient the outer join accordingly.","Use an INNER JOIN, which has no outer-side orientation requirement, if unmatched rows aren't needed.","Fall back to a standard join (non side-input-lookup) by making the lookup table a regular bounded input."],"exampleFix":"-- before\nSELECT * FROM seekable_table LEFT OUTER JOIN stream_table ON seekable_table.k = stream_table.k\n-- after\nSELECT * FROM stream_table RIGHT OUTER JOIN seekable_table ON stream_table.k = seekable_table.k","handlingStrategy":"validation","validationCode":"if ((joinType == JoinRelType.LEFT && seekableIndex == 0) || (joinType == JoinRelType.RIGHT && seekableIndex == 1)) { throw new IllegalArgumentException(\"Outer side must be the non-Seekable table\"); }","typeGuard":"boolean orientationOk = joinType != JoinRelType.LEFT || !isSeekable(left) && joinType != JoinRelType.RIGHT || !isSeekable(right);","tryCatchPattern":"try { pipeline.apply(SqlTransform.query(sql)); } catch (UnsupportedOperationException e) { if (e.getMessage().contains(\"must be a non Seekable table\")) { /* swap join operands */ } else { throw e; } }","preventionTips":["Always place the non-seekable (streaming) table on the OUTER side of LEFT/RIGHT joins","Determine SeekableTable registration order before authoring the query","Use INNER JOIN to sidestep orientation constraints","Add a startup-time boundedness/seekability assertion in pipeline construction"],"tags":["sql","join","seekable-table","streaming"],"backgroundTag":"unsupported-operation","analyzedSha":"12126d8942aaf848030c478b4c6a28c6af861c66","analyzedAt":"2026-09-13T01:50:10.254Z","contentChangedAt":"2026-09-13T01:50:10.254Z","schemaVersion":2},"datasetVersion":"2026-09-14T16:17:12.679Z"}