JOIN ON condition with scalar subquery is rejected with ERROR 1105

[FangCundi]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Create a small table:

DROP TABLE IF EXISTS t_join_scalar;

CREATE TABLE t_join_scalar (
    id INT PRIMARY KEY,
    v INT
);

INSERT INTO t_join_scalar VALUES (1, 10), (2, 20);

Run a JOIN query whose ON condition contains a scalar subquery:

SELECT
    a.id AS a_id,
    b.id AS b_id
FROM t_join_scalar AS a
JOIN t_join_scalar AS b
  ON b.v = (
      SELECT MAX(c.v)
      FROM t_join_scalar AS c
      WHERE c.id >= a.id
  );

The same issue also appears in more complex nested-join queries where the ON condition contains scalar subqueries mixed with EXISTS predicates. A representative shape is:

SELECT ...
FROM table0 AS ref_1
INNER JOIN (
    SELECT ref_2.v0 AS c0,
           82 AS c1,
           (SELECT v1 FROM table0) AS c2,
           ref_2.v0 AS c3
    FROM table0 AS ref_2
    WHERE true
) AS subq_1
  ON true
RIGHT JOIN table0 AS ref_5
  ON (
      (false OR EXISTS (
          SELECT (SELECT v3 FROM table0) AS c0,
                 ref_3.v1 AS c1,
                 (SELECT v3 FROM table0) AS c2,
                 ref_4.v3 AS c3
          FROM table0 AS ref_6
          WHERE EXISTS (
              SELECT ref_6.v2 AS c0, ref_4.v1 AS c1
              FROM table0 AS ref_7
              WHERE false
          )
      ))
      AND false
  );

The important part is that the query has a legal-looking subquery expression under a JOIN ... ON predicate. TiDB accepts the syntax but rejects it during planning.

2. What did you expect to see? (Required)

TiDB should either plan and execute this query according to SQL semantics, or return a clear documented unsupported-feature error for subqueries in JOIN ... ON predicates.

For the minimal scalar-subquery example, the expected behavior is that the scalar subquery is evaluated as part of the join condition:

ON b.v = (SELECT MAX(c.v) FROM t_join_scalar AS c WHERE c.id >= a.id)

This is a normal SQL expression shape and should not fail with a generic internal error code.

3. What did you see instead (Required)

TiDB returned:

ERROR 1105 (HY000): ON condition doesn't support subqueries yet

The error appears while planning the query, not while parsing it.

The failing pattern is a subquery inside the ON condition, for example:

JOIN t_join_scalar AS b
  ON b.v = (
      SELECT MAX(c.v)
      FROM t_join_scalar AS c
      WHERE c.id >= a.id
  )

In the larger nested query, the same failure happens when ON contains subquery expressions such as:

RIGHT JOIN table0 AS ref_5
  ON ((false OR EXISTS (... SELECT ...)) AND false)

The TiDB server stays up after the query fails, but the query cannot be executed because the planner rejects the ON condition.

4. What is your TiDB version? (Required)

SELECT tidb_version();

8.0.11-TiDB-v8.5.6