sql: apply join related issues

[yuzefovich]

SQLSmith has found multiple panics and errors related to apply joins. At the moment, we think of fixing those as having a lower priority than other bugs because apply join is used rarely and the cases when there is a problem is a subset of that "rarity," so it seems that the probability of someone hitting these issues is quite low. Nonetheless, eventually we definitely need to fix these problems, and this issue will be tracking all of them.

The first thing to fix is the problem that Becca found while debugging one of the issue (#39438 (comment)). (this was addressed by 096265d)

And here is the list of the issues:

• sql: invalid datum type given with an apply join #59882 (invalid datum type given)
• sql: support apply joins with subqueries in both "inner" and "outer" contexts #66447 (unsupported case when both the "inner" and "outer" plans have subqueries)
• sql: internal error: invalid index #39433 (invalid index when accessing the subquery result)
• sql: panic: unsupported comparison #39438
• sql: panic: interface conversion: tree.Datum is *tree.DTuple, not *tree.DTime #40432
• sql: internal error: unsupported comparison operator #40444
• sql: panic: invalid datum type given: tuple{int, int}, expected oid[] #40587
• sql: panic: expected *DIPAddr, found *tree.DTuple #40588
• sql: panic: unsupported comparison: inet to int[] #40591
• sql: internal error: invalid cast: oid[] -> bool #40957

Seems to be an optimizer bug:

• sql: internal error: columns required of root must be subset of output columns #40592

Jira issue: CRDB-5529

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[yuzefovich]

Since only #66447 is left, closing this meta issue and #66447 will be tracked separately.