sql: subquery producing `NULL` is incorrectly elided

[mgartner]

A subquery projecting NULL and used in a scalar expression can be incorrectly elided and replaced with a constant NULL in the query plan. This is incorrect because the subquery could produce multiple rows, which should result in an error.

Example:

CREATE TABLE t (
  i INT
);

INSERT INTO t VALUES (1), (2);

-- This should result in an error:
--
--   ERROR: more than one row returned by a subquery used as an expression
--   SQLSTATE: 21000
--
SELECT j + (SELECT NULL FROM t) FROM (VALUES (0)) v(j);
--   ?column?
-- ------------
--   NULL
-- (1 row)

EXPLAIN (OPT, VERBOSE)
SELECT j + (SELECT NULL FROM t) FROM (VALUES (0)) v(j);
--              info
-- -------------------------------
--   values
--    ├── columns: "?column?":7
--    ├── cardinality: [1 - 1]
--    ├── stats: [rows=1]
--    ├── cost: 0.02
--    ├── key: ()
--    ├── fd: ()-->(7)
--    ├── distribution: us-east1
--    ├── prune: (7)
--    └── (NULL,)
-- (10 rows)

After a quick investigation, the subquery appears to be elided during static analysis, before optimization.

This bug has been present since v19.1 or earlier.

Jira issue: CRDB-40804

[blathers-crl[bot]]

Hi @mgartner, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}