{"record":{"id":"57e21fba868bf737","repo":"apache/beam","slug":"full-outer-join-is-not-supported-when-join-a-bounded-table","errorCode":null,"errorMessage":"FULL OUTER JOIN is not supported when join a bounded table with an unbounded table.","messagePattern":"FULL OUTER JOIN is not supported when join a bounded table with an unbounded 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/BeamSideInputJoinRel.java","lineNumber":102,"sourceCode":"      RelTraitSet traitSet,\n      RexNode conditionExpr,\n      RelNode left,\n      RelNode right,\n      JoinRelType joinType,\n      boolean semiJoinDone) {\n    return new BeamSideInputJoinRel(\n        getCluster(), traitSet, left, right, conditionExpr, variablesSet, joinType);\n  }\n\n  @Override\n  public PTransform<PCollectionList<Row>, PCollection<Row>> buildPTransform() {\n    // if one of the sides is Bounded & the other is Unbounded\n    // then do a sideInput join.\n    // When doing a sideInput 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    // the unbounded & RIGHT OUTER JOIN where right side of the join must be the unbounded\n    if (joinType == JoinRelType.FULL) {\n      throw new UnsupportedOperationException(\n          \"FULL OUTER JOIN is not supported when join \"\n              + \"a bounded table with an unbounded table.\");\n    }\n\n    BeamRelNode leftRelNode = BeamSqlRelUtils.getBeamRelInput(left);\n    BeamRelNode rightRelNode = BeamSqlRelUtils.getBeamRelInput(right);\n\n    if ((joinType == JoinRelType.LEFT && leftRelNode.isBounded() == PCollection.IsBounded.BOUNDED)\n        || (joinType == JoinRelType.RIGHT\n            && rightRelNode.isBounded() == PCollection.IsBounded.BOUNDED)) {\n      throw new UnsupportedOperationException(\n          String.format(\"%s side of an OUTER JOIN must be Unbounded table.\", joinType.name()));\n    }\n    if (leftRelNode.isBounded() == IsBounded.UNBOUNDED\n        && rightRelNode.isBounded() == IsBounded.UNBOUNDED) {\n      throw new UnsupportedOperationException(\n          \"Side input join can only be used if one table is bounded.\");\n    }","sourceCodeStart":84,"sourceCodeEnd":120,"githubUrl":"https://github.com/apache/beam/blob/12126d8942aaf848030c478b4c6a28c6af861c66/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rel/BeamSideInputJoinRel.java#L84-L120","documentation":"BeamSideInputJoinRel handles joins between a bounded and an unbounded PCollection via side inputs, which only supports INNER, LEFT OUTER (unbounded left), and RIGHT OUTER (unbounded right) joins. A FULL OUTER JOIN in this bounded/unbounded mix cannot be materialized as a side input, so buildPTransform throws UnsupportedOperationException.","triggerScenarios":"A FULL OUTER JOIN SQL query where one side of the join is bounded (batch/BoundedSource) and the other is unbounded (streaming), causing the planner to select the side-input join implementation.","commonSituations":"Streaming-batch enrichment queries written as FULL OUTER JOIN; pipelines that switched one input from batch to streaming (or vice versa) after initially working.","solutions":["Rewrite as LEFT OUTER JOIN with the unbounded relation on the left (semantically equivalent orientation where possible)","Union the unmatched sides manually: run an INNER side-input join plus separate filters for non-matching rows on each side, then UNION the results","Make both sides bounded (batch mode) or both unbounded with compatible windows so a different join implementation is chosen"],"exampleFix":"// before\nSELECT * FROM streaming_events FULL OUTER JOIN batch_dim ON streaming_events.k = batch_dim.k;\n// after\nSELECT s.*, d.* FROM streaming_events s LEFT OUTER JOIN batch_dim d ON s.k = d.k;\n-- plus a separate query for unmatched dim rows if truly needed","handlingStrategy":"validation","validationCode":"// Check boundedness + join type before submitting\nif (joinType == JoinRelType.FULL && (isBounded(left) != isBounded(right))) {\n  throw new IllegalArgumentException(\"FULL OUTER JOIN unsupported for bounded/unbounded mix; use LEFT OUTER\");\n}","typeGuard":null,"tryCatchPattern":"try {\n  result = sqlEnv.sqlQuery(q).evaluate();\n} catch (UnsupportedOperationException e) {\n  if (e.getMessage().contains(\"FULL OUTER JOIN is not supported\")) {\n    q = rewriteFullOuterToLeftOuterPlusUnion(q);\n  } else throw e;\n}","preventionTips":["Prefer LEFT/RIGHT OUTER JOIN with the unbounded side on the correct position over FULL OUTER in mixed batch/streaming pipelines","Determine each input's boundedness (IsBounded) when designing queries","Model FULL OUTER manually: inner join UNION unmatched-left UNION unmatched-right"],"tags":["sql","join","streaming","boundedness","beam"],"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"}