{"record":{"id":"10d53e66f3226d9a","repo":"apache/beam","slug":"s-side-of-an-outer-join-must-be-unbounded-table","errorCode":null,"errorMessage":"%s side of an OUTER JOIN must be Unbounded table.","messagePattern":"(.+?) side of an OUTER JOIN must be 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":113,"sourceCode":"  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    }\n    return new SideInputJoin();\n  }\n\n  private class SideInputJoin extends PTransform<PCollectionList<Row>, PCollection<Row>> {\n\n    @Override\n    public PCollection<Row> expand(PCollectionList<Row> pinput) {\n      Schema leftSchema = pinput.get(0).getSchema();\n      Schema rightSchema = pinput.get(1).getSchema();\n      PCollection<Row> leftRows =\n          pinput","sourceCodeStart":95,"sourceCodeEnd":131,"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#L95-L131","documentation":"Apache Beam SQL throws this when a LEFT OUTER JOIN has a bounded (finite/batch) left table, or a RIGHT OUTER JOIN has a bounded right table, in the side-input join strategy. The side-input implementation materializes the bounded side and streams the unbounded side, so the outer side (whose rows must all be kept) must be the streaming/unbounded one. A bounded outer side would require buffering an infinite side, which this plan cannot do.","triggerScenarios":"Executing a BeamSql query whose chosen physical plan is BeamSideInputJoinRel where joinType==LEFT and the left BeamRelNode is bounded, or joinType==RIGHT and the right input is bounded; thrown from buildPTransform during pipeline translation.","commonSituations":"Mixing a bounded Pub/Sub-batch table with a streaming source in SQL 'SELECT ... FROM bounded LEFT OUTER JOIN streaming'; Calcite picking the side-input join rule because exactly one side is unbounded while the outer side happens to be the bounded one.","solutions":["Swap the join sides so the unbounded (streaming) table is on the OUTER side (use RIGHT JOIN of the bounded table to the streaming table, or vice versa).","Ensure the outer-join side input is unbounded, e.g. it originates from a streaming source (unbounded PCollection).","Use a FULL/INNER join or a different join strategy (e.g. BeamJoinRel alternatives or co-group) that supports the boundedness combination you need.","If the data is truly batch, make both sides bounded and let the batch join path run instead of the side-input path."],"exampleFix":"-- before\nSELECT * FROM bounded_table LEFT OUTER JOIN streaming_table ON ...\n-- after\nSELECT * FROM streaming_table RIGHT OUTER JOIN bounded_table ON ...","handlingStrategy":"validation","validationCode":"if ((joinType == JoinRelType.LEFT && left.isBounded() == IsBounded.BOUNDED) || (joinType == JoinRelType.RIGHT && right.isBounded() == IsBounded.BOUNDED)) { throw new IllegalArgumentException(\"Outer side of OUTER JOIN must be the unbounded table\"); }","typeGuard":"boolean validOuterJoin = (joinType != JoinRelType.LEFT || left.isBounded() == IsBounded.UNBOUNDED) && (joinType != JoinRelType.RIGHT || right.isBounded() == IsBounded.UNBOUNDED);","tryCatchPattern":"try { pipeline.apply(SqlTransform.query(sql)); } catch (UnsupportedOperationException e) { if (e.getMessage().contains(\"OUTER JOIN must be Unbounded\")) { /* rewrite join orientation */ } else { throw e; } }","preventionTips":["Keep the streaming (unbounded) table on the outer side of OUTER JOINs in BeamSql","Check input boundedness (PCollection.isBounded()) before planning outer joins","Prefer INNER JOIN when boundedness orientation is uncertain","Explain the query plan to see which join rel (side-input vs regular) is chosen"],"tags":["sql","join","streaming","unsupported-operation"],"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"}