Обсуждение: BUG #8228: Unexpected "set-valued function" with varchar(n) but not varchar

The following bug has been logged on the website:

Bug reference:      8228
Logged by:          David Johnston
Email address:      david.g.johnston@gmail.com
PostgreSQL version: 9.0.13
Operating system:   Ubuntu Linux 10.04
Description:        =


The following query results in "SQL Error: ERROR: set-valued function called
in context that cannot accept a set"

SELECT *, CASE WHEN id =3D 2 THEN
(regexp_matches(input_string,'^0*([1-9]\d+)$'))[1] ELSE input_string
END::varchar(30) AS o_l2_a =

FROM (
VALUES (1,''), (2,'0000000049404'),(3,'FROM 10000000876')
) l0_src (id, input_string)

The nearly identical query:

SELECT *, CASE WHEN id =3D 2 THEN
(regexp_matches(input_string,'^0*([1-9]\d+)$'))[1] ELSE input_string
END::varchar AS o_l2_a =

FROM (
VALUES (1,''), (2,'0000000049404'),(3,'FROM 10000000876')
) l0_src (id, input_string)

returns 3 records as expected.

The only difference is that the cast at the end of the case construct uses
"varchar(30)" in the failure situation but a plain "varchar" in the
successful situation.

version
PostgreSQL 9.0.13 on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit

Using "substring(CASE ... END::varchar,1,30)" also results in the
"set-valued function" error message.


David J.

david.g.johnston@gmail.com writes:
> The following query results in "SQL Error: ERROR: set-valued function called
> in context that cannot accept a set"

> SELECT *, CASE WHEN id = 2 THEN
> (regexp_matches(input_string,'^0*([1-9]\d+)$'))[1] ELSE input_string
> END::varchar(30) AS o_l2_a
> FROM (
> VALUES (1,''), (2,'0000000049404'),(3,'FROM 10000000876')
> ) l0_src (id, input_string)

Hm, interesting example.  What seems to be happening is that during
evaluation of the SELECT list for the first VALUES row, the CASE
expression doesn't call regexp_matches() but just returns the ELSE
expression.  The ExecMakeFunctionResult() call for the cast function
then decides that the function's argument expression doesn't return a
set, so it changes the node execution pointer so that subsequent
executions go through the much simpler ExecMakeFunctionResultNoSets()
execution function.  And then that spits up when on the next row, the
argument expression *does* return a set :-(

You could work around that using the trick documented in the
regexp_matches documentation to force it to return exactly one row,
ie interpose a sub-SELECT:

regression=# SELECT *, CASE WHEN id = 2 THEN
(select (regexp_matches(input_string,'^0*([1-9]\d+)$'))[1]) ELSE input_string
END::varchar(30) AS o_l2_a
FROM (
VALUES (1,''), (2,'0000000049404'),(3,'FROM 10000000876')
) l0_src (id, input_string);
 id |   input_string   |      o_l2_a
----+------------------+------------------
  1 |                  |
  2 | 0000000049404    | 49404
  3 | FROM 10000000876 | FROM 10000000876
(3 rows)

Not sure about non-hack fixes.  I guess we need to analyze
can-it-return-a-set statically instead of believing the first execution
result, but that might add an unpleasant amount of startup overhead.

            regards, tom lane

David Johnston <david.g.johnston@gmail.com> writes:
> The issue with the regexp_matches call generally is that absence of a "g"
> modifier means that the set-returning function will never return a set.  It
> would seem to make more sense to not make that a modifier but instead have
> one function defined to return a set (i.e., the current definition) and
> another one defined to return a simply text[].

Well, it does return a set, namely either zero or one row.  The point of
the sub-SELECT workaround is to transform the zero-row case to a scalar
NULL.

I tend to agree that this API wasn't that well thought out, but it's
really not regexp_matches()'s fault that you're running into this
problem --- rather, it's the fact that one arm of the CASE can return a
set while the other can't.

            regards, tom lane

On Thu, Jun 13, 2013 at 4:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> david.g.johnston@gmail.com writes:
> > The following query results in "SQL Error: ERROR: set-valued function
> called
> > in context that cannot accept a set"
>
> > SELECT *, CASE WHEN id = 2 THEN
> > (regexp_matches(input_string,'^0*([1-9]\d+)$'))[1] ELSE input_string
> > END::varchar(30) AS o_l2_a
> > FROM (
> > VALUES (1,''), (2,'0000000049404'),(3,'FROM 10000000876')
> > ) l0_src (id, input_string)
>
> Hm, interesting example.  What seems to be happening is that during
> evaluation of the SELECT list for the first VALUES row, the CASE
> expression doesn't call regexp_matches() but just returns the ELSE
> expression.
>

Does all this explain why it DOES work if the cast on the END is a plain
"varchar"?


>
> Not sure about non-hack fixes.  I guess we need to analyze
> can-it-return-a-set statically instead of believing the first execution
> result, but that might add an unpleasant amount of startup overhead.
>
>                         regards, tom lane
>

The issue with the regexp_matches call generally is that absence of a "g"
modifier means that the set-returning function will never return a set.  It
would seem to make more sense to not make that a modifier but instead have
one function defined to return a set (i.e., the current definition) and
another one defined to return a simply text[]. This would make using the
call in a scalar context easier.  Is there any reason why a UDF defined as
such would have a problem?  The set-returning one accepting the parameter
is nice since you can toggle global/single within the same query - but in
many use-cases only the single-match mode is desired.

Are there any other functions that have this same risk profile that would
increase the applicability of such a patch?

David J.