Assertion failure during unoptimized-query-oracle/disable-rules=all/rand-tables test

[renatolabs]

I was running a sample of roachtests on my PR (#107548) and noticed that the unoptimized-query-oracle/disable-rules=all/rand-tables test failed due to an assertion failure in the cockroach node:

E230726 03:19:06.241395 2499 sql/sqltelemetry/report.go:57 ⋮ [T1,n1,client=35.230.180.232:57860,hostnossl,user=root] 6246 +building declarative schema change targets for CREATE FUNCTION: ReturnType called on TypedExpr with empty typeAnnotation. Was the underlying Expr type-checked before asserting a type of TypedExpr?

I don't think this is related to the diff on my PR (mostly changes in the roachtest monitor), so I'm opening a separate issue for this. Artifacts can be found at:

https://teamcity.cockroachdb.com/viewLog.html?buildId=11055808&buildTypeId=Cockroach_Nightlies_RoachtestNightlyGceBazel&tab=buildResultsDiv

Jira issue: CRDB-30148

[mgartner]

Looks like another case of #105259. AFAICT the PR had the fix #106868, so this seems to be a different case. I was able to put together a reproduction from the stack trace in the logs:

CREATE TYPE e AS ENUM ('foo');

CREATE VIEW v AS SELECT (
  CASE WHEN true THEN (SELECT 'foo') ELSE NULL END
)::e;

So we need a more robust solution than #106868.

[mgartner]

I spent an afternoon trying to fix this, and unfortunately it's fairly tricky. We really shouldn't be re-type-checking the AST outside of the optimizer - that's the real offender here. Unfortunately, in order to stop doing that, we'll need to do something like annotating the AST during type checking with information that can be used to serialize enum values and types during formatting. I went down this path and think there's potential, but I'd be hard-pressed to back port it.

Next I tried a more backportable approach that would only convert enums and types in the form 'foo'::enum. But this is insufficient for expressions like ARRAY['foo']::enum[]. And I'm sure there's many other expressions that we'd miss if we tried to handle specific cases.

So I think annotating the AST is our best bet, but it's a significant amount of work and not backportable.

[mgartner]

The draft PR of my annotations is here: #107678

It's not really sufficient because it wouldn't correctly cover the case mentioned above either, ARRAY['foo']::enum[]. So consider it a prototype at best...

[mgartner]

I don't see a simple way to fix this, so for now I think we should detect usage of UDTs within subqueries and return an "unimplemented" error.