ResolveFunctionByOID function not found with optimizer rules disabled

[cockroach-teamcity]

roachtest.unoptimized-query-oracle/disable-rules=all/seed-multi-region failed with artifacts on master @ d4a584e49f0b1ca89738376090939d7669c3b3db:

test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=all/seed-multi-region/run_1
(query_comparison_util.go:251).runOneRoundQueryComparison: internal error while running optimized statement. 1204 statements run: pq: internal error: regproc(): ResolveFunctionByOID unimplemented

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-25232

[msirek]

Reproducible test case:

SET testing_optimizer_random_seed = 4225827507322334784;

SET testing_optimizer_disable_rule_probability = 1.000000;

SET vectorize = off;

CREATE TABLE IF NOT EXISTS seed_mr_table AS
        SELECT
                g::INT2 AS _int2,
                g::INT4 AS _int4,
                g::INT8 AS _int8,
                g::FLOAT8 AS _float8,
                '2001-01-01'::DATE + g AS _date,
                '2001-01-01'::TIMESTAMP + g * '1 day'::INTERVAL AS _timestamp,
                '2001-01-01'::TIMESTAMPTZ + g * '1 day'::INTERVAL AS _timestamptz,
                g * '1 day'::INTERVAL AS _interval,
                g % 2 = 1 AS _bool,
                g::DECIMAL AS _decimal,
                g::STRING AS _string,
                g::STRING::BYTES AS _bytes,
                substring('00000000-0000-0000-0000-' || g::STRING || '00000000000', 1, 36)::UUID AS _uuid
        FROM
                generate_series(1, 5) AS g;

EXPLAIN SELECT
        '=r2':::STRING:::NAME AS "
col857",
        "ta b276"._string AS "co%ql858",
        "ta b276"._int4 AS col859,
        " tab 278"._interval AS col860,
        "ta\v😸b279".tableoid AS col861,
        " tab 278"._interval AS col862,
        "ta\v😸b279"._float8 AS "col%863",
        0.7427599033731295:::FLOAT8 AS c😠ol864,
        regproc("ta\v😸b279"._int2::INT8)::REGPROC AS col865,
        "ta\v😸b279"._int2 AS col866,
        tab277._date AS "col^K867",
        12345.67890123456:::FLOAT8 AS "co^Kl868",
        'aaaaaa':::STRING AS col869,
        'infinity':::DATE AS col870,
        tab277._float8 AS col871,
        4046:::INT8 AS col872,
        (-78):::INT8 AS col873,
        "ta b276"._timestamp AS col874
FROM
        defaultdb.public.seed_mr_table@[0] AS "ta b276"
        JOIN defaultdb.public.seed_mr_table@[0] AS tab277 ON
                        ("ta b276"._int4) = (tab277._int4) AND ("ta b276"._int8) = (tab277._int4)
        JOIN defaultdb.public.seed_mr_table@[0] AS " tab 278"
                FULL JOIN defaultdb.public.seed_mr_table@[0] AS "ta\v😸b279" ON
                                (" tab 278"._interval) = ("ta\v😸b279"._interval)
                                AND (" tab 278".crdb_internal_mvcc_timestamp) = ("ta\v😸b279".crdb_internal_mvcc_timestamp) ON
                        ("ta b276"._int4) = (" tab 278"._int8)
WHERE
        (bool("ta b276"._int4::INT8)::BOOL OR (NOT ('o':::STRING ILIKE "ta\v😸b279"._string)));

ERROR: internal error: regproc(): ResolveFunctionByOID unimplemented
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/faketreeeval/evalctx.go:457: ResolveFunctionByOID()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/cast.go:1001: performIntToOidCast()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/cast.go:920: performCastWithoutPrecisionTruncation()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/cast.go:68: PerformCast()
github.com/cockroachdb/cockroach/pkg/sql/sem/builtins/pg_builtins.go:164: 1()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:479: EvalFuncExpr()
github.com/cockroachdb/cockroach/bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/sem/tree/eval_expr_generated.go:276: Eval()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:26: Expr()
github.com/cockroachdb/cockroach/pkg/sql/execinfrapb/pkg/sql/execinfrapb/expr.go:248: Eval()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:275: ProcessRow()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:711: ProcessRowHelper()
github.com/cockroachdb/cockroach/pkg/sql/rowexec/filterer.go:104: Next()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/base.go:186: Run()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:733: Run()
github.com/cockroachdb/cockroach/pkg/sql/flowinfra/flow.go:447: func1()
GOROOT/src/runtime/asm_arm64.s:1172: goexit()

On v22.2.6 a different error occurs:

ERROR: relation "defaultdb.public.seed_mr_table" does not exist

Removing release blocker label. Can only be reproduced by disabling normalizations.

[mgartner]

@otan I think this is related to #97093. Do you mind taking a look?

[otan]

Hmm, not sure about this one or at least it's caused by me.

This is evaluated using &DummyEvalPlanner{}, which only happens here:

cockroach/pkg/sql/distsql/server.go

Line 357 in ed7feb0

Planner: &faketreeeval.DummyEvalPlanner{Monitor: monitor},

- in theory this is a problem if you just did ("ta\v😸b279"._int2::INT8)::REGPROC AS col865.

the minimal repro above doesn't work for me:

ERROR: no data source matches prefix: " tab 278" in this context
SQLSTATE: 42P01

[mgartner]

Adding release-blocker label until we have a smaller repro and understand this better.

[DrewKimball]

@msirek was it necessary to use a multiregion cluster to reproduce this? It doesn't reproduce for me on a single-node cluster on current master.

[msirek]

@msirek was it necessary to use a multiregion cluster to reproduce this? It doesn't reproduce for me on a single-node cluster on current master.

Yes, it's possible I was running a multiregion cluster at the time. Let me try reproducing this again.

[msirek]

The repro isn't working for me now, so I'm re-running the full setup script.

[msirek]

I can't reproduce this bug now, even with running the full setup. Not sure what's changed on my machine.
I'm wondering if this is related to #98322, where we don't seem to call the function, and don't error out, but instead return the input to the function as the result.

If this issue can't be reproduced, maybe we should close it as a dup of #98322, and fix that one instead. That one may be worse because it causes incorrect results instead of erroring out the query.

[cucaroach]

Maybe some of these OID functions should be in distsql block list.

[msirek]

OK, here's a logic test that hits the bug in the fakedist-disk config:

statement ok
SET vectorize = off;

statement ok
CREATE TABLE IF NOT EXISTS seed_mr_table AS
        SELECT
                g::INT2 AS _int2,
                g::INT4 AS _int4,
                g::INT8 AS _int8
        FROM
                generate_series(1, 5) AS g;

query T
SELECT
        regproc(_int2::INT8)::REGPROC
FROM
        seed_mr_table@[0]
----