Bug Report: Query planning fails for `JOIN`s with derived tables containing `UNION` across keyspaces/shards

[arthurschreiber]

Overview of the Issue

When executing a query that joins a table with a derived table containing a UNION that spans multiple keyspaces (or shards), the query planner fails with the error:

VT13001: [BUG] could not find the column '...' on the UNION

Reproduction Steps

This can be reproduced with the following query running against the planner test vschema.

-- Where `user` is in a sharded keyspace and `unsharded` is in an unsharded keyspace.
SELECT u.id
FROM user AS u
JOIN (
    SELECT id AS pid FROM user WHERE id = 1
    UNION
    SELECT id AS pid FROM unsharded WHERE id = 1
) AS i ON u.id = i.pid

Binary Version

This affects v22, v23 and main.

Operating System and Environment details

N/A

Log Fragments

N/A