{"record":{"id":"769142b0478e80e4","repo":"apache/beam","slug":"full-outer-join-is-not-supported-when-join-a-seekable-table","errorCode":null,"errorMessage":"FULL OUTER JOIN is not supported when join a Seekable table with a non Seekable table.","messagePattern":"FULL OUTER JOIN is not supported when join a Seekable table with 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":82,"sourceCode":"      RelOptCluster cluster,\n      RelTraitSet traitSet,\n      RelNode left,\n      RelNode right,\n      RexNode condition,\n      Set<CorrelationId> variablesSet,\n      JoinRelType joinType) {\n    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","sourceCodeStart":64,"sourceCodeEnd":100,"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#L64-L100","documentation":"Beam SQL's side-input lookup join supports only INNER, LEFT OUTER (non-seekable on the left) and RIGHT OUTER (non-seekable on the right) joins between a Seekable table and a non-Seekable table. A FULL OUTER JOIN cannot be performed this way because it would require streaming the seekable side's unmatched rows, so the translation throws this UnsupportedOperationException.","triggerScenarios":"Translating a BeamSql FULL OUTER JOIN query where exactly one side is a SeekableTable and the side-input lookup join rule (BeamSideInputLookupJoinRel) is chosen.","commonSituations":"Attempting FULL OUTER JOIN against a seekable side-input table such as a lookup backed by a stateful/seekable DoFn (e.g. a JDBC or BigTable-backed SeekableTable) in a streaming pipeline.","solutions":["Rewrite as LEFT OUTER JOIN with the non-seekable table on the left, plus a complementary RIGHT-OUTER-derived query for unmatched seekable rows, and UNION ALL the results.","Change the join type to INNER or LEFT/RIGHT OUTER, whichever matches which side is seekable.","Materialize the seekable table as a bounded PCollection and use a regular (non-lookup) join that supports FULL OUTER JOIN.","Implement a custom join transform (e.g. CoGroupByKey-based) if FULL OUTER semantics over both sides are truly required."],"exampleFix":"-- before\nSELECT * FROM non_seekable FULL OUTER JOIN seekable_table ON non_seekable.k = seekable_table.k\n-- after\nSELECT * FROM non_seekable LEFT OUTER JOIN seekable_table ON non_seekable.k = seekable_table.k","handlingStrategy":"validation","validationCode":"if (joinType == JoinRelType.FULL && (isSeekable(left) ^ isSeekable(right))) { throw new IllegalArgumentException(\"FULL OUTER JOIN unsupported for mixed Seekable/non-Seekable sides\"); }","typeGuard":"boolean lookupJoinAllowed = !(joinType == JoinRelType.FULL && exactlyOneSeekable);","tryCatchPattern":"try { pipeline.apply(SqlTransform.query(sql)); } catch (UnsupportedOperationException e) { if (e.getMessage().contains(\"FULL OUTER JOIN is not supported\")) { /* decompose into LEFT OUTER + anti-join UNION ALL */ } else { throw e; } }","preventionTips":["Avoid FULL OUTER JOIN when one side is a SeekableTable","Decompose FULL OUTER into LEFT OUTER plus complementary anti-join","Know which of your tables are registered as Seekable before writing join SQL","Materialize seekable lookup tables as bounded PCollections when full outer semantics are needed"],"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"}