udf: unimplemented: SELECT usage inside a function definition (when using EXISTS)

[michae2]

Not sure if this is allowed, but when trying to use WHERE EXISTS (SELECT ...) inside a UDF I get an unimplemented: SELECT usage inside a function definition error. Creating functions with the same query, rewritten in different ways, does not err.

Here's the reproduction. It's kind of a silly semi-join query, finding the first value for which there exists value + 1 in the same table.

CREATE TABLE t (a) AS SELECT generate_series(0, 999);
EXPLAIN SELECT t1.a FROM t t1 JOIN t t2 ON (t2.a = t1.a + 1) ORDER BY t1.a LIMIT 1;
EXPLAIN SELECT t1.a FROM t t1, (SELECT * FROM t) t2 WHERE t2.a = t1.a + 1 ORDER BY t1.a LIMIT 1;
EXPLAIN SELECT t1.a FROM t t1 WHERE EXISTS (SELECT * FROM t t2 WHERE t2.a = t1.a + 1) ORDER BY t1.a LIMIT 1;
CREATE FUNCTION f1 () RETURNS INT LANGUAGE SQL AS $$ SELECT t1.a FROM t t1 JOIN t t2 ON (t2.a = t1.a + 1) ORDER BY t1.a LIMIT 1 $$;
CREATE FUNCTION f2 () RETURNS INT LANGUAGE SQL AS $$ SELECT t1.a FROM t t1, (SELECT * FROM t) t2 WHERE t2.a = t1.a + 1 ORDER BY t1.a LIMIT 1 $$;
CREATE FUNCTION f3 () RETURNS INT LANGUAGE SQL AS $$ SELECT t1.a FROM t t1 WHERE EXISTS (SELECT * FROM t t2 WHERE t2.a = t1.a + 1) ORDER BY t1.a LIMIT 1 $$;

All three versions of the query should be the same. Functions can be made with the first two, but not with the last:

demo@127.0.0.1:26257/defaultdb> CREATE FUNCTION f1 () RETURNS INT LANGUAGE SQL AS $$ SELECT t1.a FROM t t1 JOIN t t2 ON (t2.a = t1.a + 1) ORDER BY t1.a LIMIT 1 $$;
CREATE FUNCTION


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

demo@127.0.0.1:26257/defaultdb> CREATE FUNCTION f2 () RETURNS INT LANGUAGE SQL AS $$ SELECT t1.a FROM t t1, (SELECT * FROM t) t2 WHERE t2.a = t1.a + 1 ORDER BY t1.a LIMIT 1 $$;
CREATE FUNCTION


Time: 29ms total (execution 28ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> CREATE FUNCTION f3 () RETURNS INT LANGUAGE SQL AS $$ SELECT t1.a FROM t t1 WHERE EXISTS (SELECT * FROM t t2 WHERE t2.a = t1.a + 1) ORDER BY t1.a LIMIT 1 $$;
ERROR: unimplemented: SELECT usage inside a function definition
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.

I would expect the WHERE EXISTS (SELECT ...) version to also work.

This is on v22.2.0-alpha.3-421-g77c412dfef (tip of master).

Jira issue: CRDB-19789

[mgoddard]

Would this also address the issue unimplemented: subquery usage inside a function definition? Here's an example from yesterday where I encountered this error message:

CREATE OR REPLACE FUNCTION map_countries (num_codes_agg STRING)
RETURNS STRING
IMMUTABLE LEAKPROOF LANGUAGE SQL AS
$$
  select array_to_string(array_agg(alph), '|')
  from num_to_alph
  where num in
  (
    select * from
    [select regexp_extract(regexp_split_to_table(num_codes_agg, '\|'), '(\d{4})') dig]
    where dig is not null
  )
$$;

[mgartner]

No, that is related to #87291. Can you share the schema of num_to_alpha in that issue with your example?

[mgartner]

This actually related to #87291, which is almost completed. On master, the original failure above no fails with ERROR: unimplemented: functions do not currently support * expressions, which will soon be fixed by #95710 (tracked by #90080).