sql: support array_agg(record)

[nvanbenschoten]

Needed for #69010.

In Postgres:

> SELECT array_agg(ROW(1, 2));
 array_agg
-----------
 {"(1,2)"}

In CockroachDB:

> SELECT array_agg(ROW(1, 2));
ERROR: unknown signature: array_agg(tuple{int, int})

Prototype:

diff --git a/pkg/sql/sem/builtins/builtins.go b/pkg/sql/sem/builtins/builtins.go
index 73ac8b8c4c..9ad1baa460 100644
--- a/pkg/sql/sem/builtins/builtins.go
+++ b/pkg/sql/sem/builtins/builtins.go
@@ -7057,6 +7057,8 @@ func arrayBuiltin(impl func(*types.T) tree.Overload) builtinDefinition {
                        overloads = append(overloads, impl(typ))
                }
        }
+       // WIP: this isn't actually the right way to do this.
+       overloads = append(overloads, impl(types.AnyTuple))
        return builtinDefinition{
                props:     tree.FunctionProperties{Category: categoryArray},
                overloads: overloads,

Epic CRDB-10300

[rafiss]

I think we might need to resolve #32715 for this

My latest attempt was in #63996 but I stopped working on it

[jordanlewis]

You could do it by editing this code in distsql_physical_planner

	case *tree.CastExpr:
		// TODO (rohany): I'm not sure why this CastExpr doesn't have a type
		//  annotation at this stage of processing...
		if typ, ok := tree.GetStaticallyKnownType(t.Type); ok {
			switch typ.Family() {
			case types.OidFamily:
				v.err = newQueryNotSupportedErrorf("cast to %s is not supported by distsql", t.Type)
				return false, expr
			}
		}
	}

[jordanlewis]

You could also set the array_agg overloads that take tuples to be DistSQLBlocklist=true, right?

[rafiss]

I don't think the issue is DistSQL -- the problem is the vectorized engine:

        (XX000) internal error: unexpected error from the vectorized engine: couldn't decode type anyelement
        error.go:88: in func1()
        DETAIL: stack trace:
        github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:88: func1()
        runtime/panic.go:965: gopanic()
        github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:192: InternalError()
        github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/inbox.go:307: func1()
        runtime/panic.go:965: gopanic()
        github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:192: InternalError()
        github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/inbox.go:379: func2()
        github.com/cockroachdb/cockroach/pkg/sql/colmem/allocator.go:301: PerformOperation()
        github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/inbox.go:377: Next()
        github.com/cockroachdb/cockroach/pkg/sql/colexec/invariants_checker.go:85: Next()
        github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:99: Next()
        github.com/cockroachdb/cockroach/pkg/sql/colexecop/operator.go:371: Next()

The AnyTuple type contains an anyelement in it which can't be handled here

cockroach/pkg/sql/rowenc/column_type_encoding.go

Line 692 in 98e65dc

return nil, buf, errors.Errorf("couldn't decode type %s", t)

[rafiss]

Ah ok, nvm, that does also block it from using the vectorized engine. I just wasn't seeing that since I was setting the property in the wrong place.

[yuzefovich]

On a quick glance, the vectorized engine should work here in the same manner as the row engine (because we're using the same representation - coldata.DatumVec), and the stack trace posted above might have originated because of the usage of the DistSQL. If this feature is blocklisted for DistSQL, the internal error above maybe wouldn't occur.