Ambiguous call when trying to call a stored function returning a table type

[lukaseder]

Describe the problem

I can't call a stored function that returns a table type from SELECT

To Reproduce

create table t (i int, j int);
insert into t values (1, 2);

create or replace function f () returns t as 
$$
select i, j from t;
$$
language sql;

select f();

The error is:

SQL Error [42725]: ERROR: ambiguous call: f(), candidates are:
f() -> tuple{int AS i, int AS j}
f() -> tuple{int AS i, int AS j}

But I'm not seeing the ambiguity in the catalog:

select count(*) from pg_proc where proname = 'f';
select count(*) from pg_proc where pronamespace = (
  select oid from pg_namespace where nspname = 'public'
);

Result for both queries:

|count|
|-----|
|1    |

Note, I also can't drop the function!

drop function f ();

SQL Error [XXUUU]: ERROR: function name "f" is not unique

Expected behavior

I would expect this result as in native PostgreSQL:

|i  |j  |
|---|---|
|1  |2  |

Environment:

• CockroachDB version: CockroachDB CCL v22.2.0 (x86_64-pc-linux-gnu, built 2022/12/05 16:37:36, go1.19.1)
• Server OS: Linux (Docker)
• Client app: Dbeaver (JDBC)

Jira issue: CRDB-22269

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[mgartner]

I think this might be related to #93321. cc @DrewKimball

[DrewKimball]

I'm unable to reproduce this either on current master or on v22.2.0. @lukaseder do you have any other details from when you observed this?

[lukaseder]

Sure, I did this:

docker pull cockroachdb/cockroach
docker run -d --name=COCKROACHDB --hostname=localhost --net=roachnet -p 26257:26257 -p 8081:8080 -v "//c/data/cockroach" cockroachdb/cockroach start-single-node --insecure

Then connected with Dbeaver:

Then I ran this, as mentioned in the ticket:

create table t (i int, j int);
insert into t values (1, 2);

create or replace function f () returns t as 
$$
select i, j from t;
$$
language sql;

select f();

Result:

select version();

Produces:

CockroachDB CCL v22.2.6 (x86_64-pc-linux-gnu, built 2023/03/03 19:31:13, go1.19.4)

Hope this helps

[lukaseder]

Not sure if it's relevant, but I'm running Docker Desktop version 4.14.1 (91661) on Windows:

C:\Users\lukas>ver

Microsoft Windows [Version 10.0.22621.1265]

[DrewKimball]

Thanks for the help, it reproduces for me using dbeaver.

[DrewKimball]

This is fixed by #96734, which will make it into the next 22.2 minor version.