sql: typing of multi-row subqueries is confusing

[petermattis]

Multi-row subqueries are currently typed as tuple{U}. This plays nicely with the type checking code which can thus treat a multi-row subquery very similarly to a tuple, but is wrong semantically. The subquery has a variable number of elements, while tuple{U} is specifying a fixed length tuple with 1 element. That this works is due to misuse of the tuple type in the type checking code. See typeCheckSubqueryWithIn.

Instead of using tuple{U}, multi-row subqueries should be typed with a variable length type such as table{U} or vtuple{U} (variable tuple). The existing types.TTable type seems to fit the bill, though there is mild concern due to the existing usage of that type for set-returning-functions (e.g. generate_series).

In order to change the type of multi-row subqueries, the type checking code for IN, NOT IN, ALL, SOME, ANY and ARRAY needs to change. This is currently non-trivial. For example, there currently exists an overload for <tuple> IN <tuple>. Would we also need an overload for <tuple> IN <table>? There are also bits of special type checking code which look for types.TTuple which would need to be generalized. See #21123 for additional commentary on the type checking code.

Cc @nvanbenschoten, @knz

Jira issue: CRDB-5899

[RaduBerinde]

See #21273 for a related issue in NewTypedComparisonExpr* code.

[asubiotto]

@RaduBerinde this seems like a planning issue, moving to your project.

[RaduBerinde]

Leaving some notes about this:

• original TODO now here:

  cockroach/pkg/sql/opt/optbuilder/subquery.go

  Line 183 in 8983baf

  // TODO(peter): Using a tuple type to represent a multi-row

• cockroach/pkg/sql/sem/tree/expr.go

  Line 514 in 8983baf

  // x = ANY(SELECT y FROM t)

• sample problematic query: select 1 < any(1,2,3) (should error out)

[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!