sql: concatenating null array incorrectly returns null

[rytaft]

Concatenating a NULL array to any other array currently returns NULL in CockroachDB. However, array concatenation should actually just ignore NULL arguments. For example, this statement currently returns NULL in CockroachDB, which is incorrect:

SELECT ARRAY[]::BOOL[] || NULL::BOOL[];

It should return {}, which is what Postgres returns.

Jira issue: CRDB-16858

Epic: CRDB-14914

[rytaft]

FYI, I looked at this issue and it's not an optimizer issue -- it's a difference between the vectorized engine and the row-based engine. Similar to how we check NullableArgs for comparison operations in colexec/colexeccmp/default_cmp_expr_tmpl.go, we'll also need to check NullableArgs for binary operations. As far as I can tell, the code in colexec/colexecproj/proj_non_const_ops_tmpl.go and colexec/colexecprojconst/proj_const_ops_tmpl.go is ignoring NullableArgs altogether. For example, this looks suspicious to me:

cockroach/pkg/sql/colexec/colexecprojconst/proj_const_ops_tmpl.go

Lines 184 to 185 in 928f605

	if !colNulls.NullAt(i) {
	// We only want to perform the projection operation if the value is not null.

[rytaft]

Here's a reproduction that actually works (the repro in the issue description only works if constant folding is disabled in the optimizer):

[email protected]:26257/defaultdb> CREATE TABLE t (b BOOL[]);
CREATE TABLE


Time: 5ms total (execution 5ms / network 0ms)

[email protected]:26257/defaultdb> INSERT INTO t VALUES (ARRAY[]);
INSERT 0 1


Time: 6ms total (execution 6ms / network 0ms)

[email protected]:26257/defaultdb> SELECT b || NULL FROM t;
  ?column?
------------
  NULL
(1 row)