odd behavior/possible bug
I created a function thus:
CREATE OR REPLACE FUNCTION dwarray(anyelement, anyelement)
RETURNS anyarray AS '
SELECT ARRAY[$1,$2]
' LANGUAGE 'sql' IMMUTABLE STRICT;
My hope was to use STRICT to get the following behavior: if either or
both arguments are NULL, I get a NULL. If both are non-NULL, I get an
array. Seems simple enough at first glance. Here's what I get when I try
it out:
regression=# select dwarray(1,2);
dwarray
---------
{1,2}
(1 row)
regression=# select dwarray(1,null) is null;
?column?
----------
t
(1 row)
regression=# select dwarray(null,2) is null;
?column?
----------
t
(1 row)
So far so good. But look at this one:
regression=# select dwarray(null,null);
ERROR: cannot determine ANYARRAY/ANYELEMENT type because input is UNKNOWN
This happens because enforce_generic_type_consistency() can't resolve
the return type from the NULLs which are typed as UNKNOWN. This call is
made from ParseFuncOrColumn().
Should ParseFuncOrColumn() bypass the call to
enforce_generic_type_consistency() when all arguments are NULL?
The next item is a bit more strange. Create a table and load some data:
create table t(f1 int, f2 float, f3 float);
insert into t values(1,11.1,21.1);
insert into t values(1,11.2,21.2);
insert into t values(1,11.3,21.3);
insert into t values(1,11.4,null);
insert into t values(1,null,21.5);
insert into t values(1,null,null);
Now call the same function:
regression=# select dwarray(f2,f3) from t;
ERROR: arrays cannot have NULL elements
This call makes it all the way to ExecEvalArray(), which means that
dwarray() is getting evaluated even though it is declared STRICT and has
been called with NULL inputs. That shouldn't happen, should it?
Joe
Joe Conway <mail@joeconway.com> writes:
So far so good. But look at this one:
regression=# select dwarray(null,null);
ERROR: cannot determine ANYARRAY/ANYELEMENT type because input is UNKNOWN
That seems correct to me. What would you expect to happen? There's no
type we could assign as the function's actual return type.
This call makes it all the way to ExecEvalArray(), which means that
dwarray() is getting evaluated even though it is declared STRICT and has
been called with NULL inputs. That shouldn't happen, should it?
I think what is happening is that the SQL function is getting inlined,
probably because the inlining logic thinks ARRAY[] is strict and so
there'd be no change in semantics.
We could probably hack the inlining logic to prevent it from inlining
the function in this scenario, but I wonder whether this doesn't say
that ExecEvalArray is behaving inconsistently. In other operations, any
NULL in means NULL out. Shouldn't it simply quietly return a NULL array
if one of the supplied elements is NULL?
regards, tom lane
Tom Lane wrote:
Joe Conway <mail@joeconway.com> writes:
So far so good. But look at this one:
regression=# select dwarray(null,null);
ERROR: cannot determine ANYARRAY/ANYELEMENT type because input is UNKNOWNThat seems correct to me. What would you expect to happen? There's no
type we could assign as the function's actual return type.
I see your point, but mine was that in this case I'd like a NULL
returned and I don't really care about the type. ISTM that NULL should
be able to morph into any type it needs to.
This call makes it all the way to ExecEvalArray(), which means that
dwarray() is getting evaluated even though it is declared STRICT and has
been called with NULL inputs. That shouldn't happen, should it?I think what is happening is that the SQL function is getting inlined,
probably because the inlining logic thinks ARRAY[] is strict and so
there'd be no change in semantics.We could probably hack the inlining logic to prevent it from inlining
the function in this scenario, but I wonder whether this doesn't say
that ExecEvalArray is behaving inconsistently. In other operations, any
NULL in means NULL out. Shouldn't it simply quietly return a NULL array
if one of the supplied elements is NULL?
That probably makes good sense, at least until we can deal with NULL
elements. Would that patch count as a bug fix?
Joe
Joe Conway <mail@joeconway.com> writes:
Tom Lane wrote:
We could probably hack the inlining logic to prevent it from inlining
the function in this scenario, but I wonder whether this doesn't say
that ExecEvalArray is behaving inconsistently. In other operations, any
NULL in means NULL out. Shouldn't it simply quietly return a NULL array
if one of the supplied elements is NULL?
That probably makes good sense, at least until we can deal with NULL
elements. Would that patch count as a bug fix?
Either one would count as a bug fix IMHO. Anyone else have an opinion
on which to do?
regards, tom lane
On Thu, 24 Jul 2003, Joe Conway wrote:
Tom Lane wrote:
Joe Conway <mail@joeconway.com> writes:
So far so good. But look at this one:
regression=# select dwarray(null,null);
ERROR: cannot determine ANYARRAY/ANYELEMENT type because input is UNKNOWNThat seems correct to me. What would you expect to happen? There's no
type we could assign as the function's actual return type.I see your point, but mine was that in this case I'd like a NULL
returned and I don't really care about the type. ISTM that NULL should
be able to morph into any type it needs to.
I don't think that's necessarily true.
As a potentially absurd example, do we want
select CAST( CAST( NULL as DATE ) as POINT );
to succeed when dates aren't convertable to points?
The case of func(anyelement, anyelement) returns anyarray could
potentially return some kind of "array of unknown (but single) type"
when presented with unknown inputs. I'm not sure what use that'd be
unless you are allowed to convert it into something else, though.
Stephan Szabo wrote:
On Thu, 24 Jul 2003, Joe Conway wrote:
I see your point, but mine was that in this case I'd like a NULL
returned and I don't really care about the type. ISTM that NULL should
be able to morph into any type it needs to.I don't think that's necessarily true.
As a potentially absurd example, do we want
select CAST( CAST( NULL as DATE ) as POINT );
to succeed when dates aren't convertable to points?
good point -- no pun intended ;)
The case of func(anyelement, anyelement) returns anyarray could
potentially return some kind of "array of unknown (but single) type"
when presented with unknown inputs. I'm not sure what use that'd be
unless you are allowed to convert it into something else, though.
Hmm, this sounds like yet another reason for UNKNOWN[]. Specifically
what I wanted to do was create a function that would give me a NULL if
any arguments to my ARRAY[] expression were NULL. But the change Tom
suggested (i.e. ARRAY[NULL,...] evaluates to NULL) gives me that without
needing the function, so probably that is the best answer.
Joe
Tom Lane wrote:
Joe Conway <mail@joeconway.com> writes:
That probably makes good sense, at least until we can deal with NULL
elements. Would that patch count as a bug fix?Either one would count as a bug fix IMHO. Anyone else have an opinion
on which to do?
Here's a patch that replaces the ERROR with a NULL return value when an
element in an array expression is NULL.
Joe
Attachments:
array-null-fix.01.patchtext/plain; name=array-null-fix.01.patchDownload
Index: src/backend/executor/execQual.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/execQual.c,v
retrieving revision 1.135
diff -c -r1.135 execQual.c
*** src/backend/executor/execQual.c 21 Jul 2003 17:05:08 -0000 1.135
--- src/backend/executor/execQual.c 24 Jul 2003 22:44:44 -0000
***************
*** 1642,1650 ****
dvalues[i++] = ExecEvalExpr(e, econtext, &eisnull, NULL);
if (eisnull)
! ereport(ERROR,
! (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
! errmsg("arrays cannot have NULL elements")));
}
/* setup for 1-D array of the given length */
--- 1642,1651 ----
dvalues[i++] = ExecEvalExpr(e, econtext, &eisnull, NULL);
if (eisnull)
! {
! *isNull = true;
! return (Datum) 0;
! }
}
/* setup for 1-D array of the given length */
***************
*** 1686,1694 ****
arraydatum = ExecEvalExpr(e, econtext, &eisnull, NULL);
if (eisnull)
! ereport(ERROR,
! (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
! errmsg("arrays cannot have NULL elements")));
array = DatumGetArrayTypeP(arraydatum);
--- 1687,1696 ----
arraydatum = ExecEvalExpr(e, econtext, &eisnull, NULL);
if (eisnull)
! {
! *isNull = true;
! return (Datum) 0;
! }
array = DatumGetArrayTypeP(arraydatum);
Joe Conway <mail@joeconway.com> writes:
Here's a patch that replaces the ERROR with a NULL return value when an
element in an array expression is NULL.
Applied. I also added a comment to remind us to change
contain_nonstrict_functions() when ARRAY's behavior is changed again.
regards, tom lane