BUG #6020: Wrong data type returned after CAST in FROM

The following bug has been logged online:

Bug reference: 6020
Logged by: Skylar Hawk
Email address: skylar(dot)j(dot)hawk(at)gmail(dot)com
PostgreSQL version: 9.0.3
Operating system: OpenBSD
Description: Wrong data type returned after CAST in FROM
Details:

Hello,

I noticed a strange nuance. When I do a regular select on data that I CAST
as a CHAR(), the data I am returned is of type character() as I would
expect. For example:

SELECT
CAST('abcd' AS CHAR(4)) AS data

This returns 'abcd' where the type is character(4).

However, if I do the cast in the FROM portion of the query such as this:

SELECT
data
FROM
CAST('abcd' AS CHAR(4)) AS data

then I am returned 'abcd' and the type is bpchar.

I did not have this issue when I was using Postgres 8.4. I upgraded last
week, and that's when I noticed the issue in a couple database functions I
had written.

-Sky

"Skylar Hawk" <skylar(dot)j(dot)hawk(at)gmail(dot)com> writes:
> I noticed a strange nuance. When I do a regular select on data that I CAST
> as a CHAR(), the data I am returned is of type character() as I would
> expect. For example:
> SELECT
> CAST('abcd' AS CHAR(4)) AS data
> This returns 'abcd' where the type is character(4).
> However, if I do the cast in the FROM portion of the query such as this:
> SELECT
> data
> FROM
> CAST('abcd' AS CHAR(4)) AS data
> then I am returned 'abcd' and the type is bpchar.

What exactly are you using to identify the type?

regards, tom lane

Skylar Hawk <skylar(dot)j(dot)hawk(at)gmail(dot)com> writes:
> I got the similar results when I ran similar queries from a ruby
> script using the 'postgres' gem as well, which notified me of the
> difference through the error message. That's actually what alerted me
> to it first. The script calls a plpsql function which expects a
> CHAR(8) and the script died. The database message said the function
> was expecting a CHAR(8) but got a BPCHAR instead.

OK, now I'm really unsure what you're talking about. There's no such
thing as a plpgsql function that "expects a char(8)" --- well, you can
declare it that way, but the database just ignores the length modifier.
And BPCHAR is just the internal name for CHAR(n), so it should certainly
have matched.

Could we see the *exact* function declaration, the exact SQL you issued,
and the exact error message, rather than ambiguous descriptions?

regards, tom lane

Skylar Hawk <skylar(dot)j(dot)hawk(at)gmail(dot)com> writes:
> My apologies too. Now that I look at it I wasn't accurately describing
> how I was doing things after all. In the script I do pass on the data
> I get back from that query into another function, but after looking
> again, it looks like I have the script pull the data from this query
> and then push it into another query instead of having the database do
> it. Sorry for the excess frustration it probably caused.

Well, the main point here seems to be that you've got a function
returning record, not just a scalar value which is what your initial
example suggested. I've been able to duplicate the error and confirm
that the behavior changed in 9.0, but have not yet tracked down why.
More news as it develops ...

regards, tom lane

I wrote:
> Well, the main point here seems to be that you've got a function
> returning record, not just a scalar value which is what your initial
> example suggested. I've been able to duplicate the error and confirm
> that the behavior changed in 9.0, but have not yet tracked down why.
> More news as it develops ...

I've looked into this and concluded that it is a bug, or at least
undesirable behavior. The reason for the change is that 9.0 is actually
checking that the typmods (length constraints) of the record fields
match what they are supposed to be, which prior versions never did.
(You can get this function to return more than 8 characters in one of
the record fields, for instance, if using pre-9.0.) However, plpgsql
doesn't pass variable typmods into the main executor, so the record
field is identified as char-without-length-constraint here:

> return_pieces := (new_card_id, magic_byte, crazy_eights);

and then the new checking code quite properly complains about that
not matching the record type specified in the calling query.

A workaround while we figure out what to do about it is to add explicit
casts to the row constructor:

return_pieces := (new_card_id, magic_byte::char(8), crazy_eights::char(8));

I'm moving the discussion about what to do about it to pgsql-hackers:
http://archives.postgresql.org/pgsql-hackers/2011-05/msg00738.php
since while it's fairly clear what to change in HEAD, it's not at all
obvious whether we ought to try to change this in released branches.

regards, tom lane

Hi there,

I've no idea if this is the proper way to append information about an
existing bug report, please advise if not.

The bug #6020 seems to have bit me, too, this kind of a query fails under
PostgreSQL 9.0.4:

SELECT * FROM acl_get_list('news', 258, 0);
ERROR: wrong record type supplied in RETURN NEXT
DETAIL: Returned type bpchar does not match expected type character(4) in column 3.
CONTEXT: PL/pgSQL function "acl_get_list" line 9 at RETURN NEXT

Where the code is:

CREATE TYPE acl_list AS (
ugid integer,
isuser boolean,
acl_id CHAR(4)
[...]
);

CREATE OR REPLACE FUNCTION acl_get_list(CHAR(4), INTEGER, INTEGER) RETURNS SETOF acl_list AS '
DECLARE
p_acl_id ALIAS FOR $1;
p_acl_object_id ALIAS FOR $2;
p_lev ALIAS FOR $3;
rec RECORD;
rec1 RECORD;
BEGIN
[...]
FOR rec IN SELECT * FROM acl_inherits WHERE acl_id = p_acl_id AND object_id = p_acl_object_id LOOP
FOR rec1 IN SELECT * FROM acl_get_list(rec.parent_acl_id, rec.parent_object_id, p_lev + 1) LOOP
RETURN NEXT rec1;
END LOOP;
END LOOP;
[...]

This worked just fine in 8.1, 8.3, 8.4, and doesn't seem wrong.

(Coupled with http://bugs.debian.org/630569, this upgrade cycle
has not been happy at all for me... :()

--
2. That which causes joy or happiness.