Re: BUG #2451: Short column names return no values within function

The following bug has been logged online:

Bug reference: 2451
Logged by: Alex Weslowski
Email address: aweslowski(at)rpa(dot)com
PostgreSQL version: 8.1
Operating system: Windows XP
Description: Short column names return no values within function
Details:

Below is code for duplicating this error. Fields "Peg" and "Rs03" and "Rs12"
are absent from returned record (either Record or Cursor) even though the
values in the table are not null.

Problem might be related to type conversion (NULL converts to '' which has
no meaning to INT or NUMERIC).

Problem is fixed by renaming columns to "Peg_Ratio" and "RS03RS" and
"RS12RS". So, there is something more going on here, related to length of
column name.

-----

CREATE TABLE TestBug (
Date DATE,
Symbol VARCHAR(10),
Peg NUMERIC(8, 3),
RS03 SMALLINT,
RS12 SMALLINT
);

INSERT INTO TestBug VALUES ('5/18/06', 'ABAX', 1.38, 78, 95);
INSERT INTO TestBug VALUES ('5/18/06', 'IRIX', NULL, 97, 92);
INSERT INTO TestBug VALUES ('5/18/06', 'SCSC', 1.31, 59, 65);

CREATE TYPE row_TestBug AS (
idx INT,
str VARCHAR(512)
);

CREATE OR REPLACE FUNCTION fn_TestBug(
d DATE
)
RETURNS SETOF row_TestBug AS $$
DECLARE

varSym VARCHAR(10) := '';
peg VARCHAR(5) := '';
numPeg NUMERIC(8, 3) := NULL;
varPeg VARCHAR(9) := NULL;
rs03 VARCHAR(3) := '';
intRs03 INT := NULL;
varRs03 VARCHAR(8) := NULL;
rs12 VARCHAR(3) := '';
intRs12 INT := NULL;
varRs12 VARCHAR(8) := NULL;
str VARCHAR(512) := '';
i INT := 0;

rtn row_TestBug;

rec RECORD;

BEGIN

FOR rec IN SELECT Symbol, RS03, RS12, Peg
FROM TestBug WHERE Date=d
ORDER BY RS12 DESC LOOP

varSym := rec.Symbol;

varSym := RTRIM(varSym) || REPEAT(' ', 8 - LENGTH(RTRIM(varSym)));

rs03 := ' NA';
IF (rec.RS03 IS NOT NULL) THEN
IF (rec.RS03 > 0) THEN
rs03 := CAST(rec.RS03 AS VARCHAR);
END IF;
END IF;

rs12 := ' NA';
IF (rec.RS12 IS NOT NULL) THEN
IF (rec.RS12 > 0) THEN
rs12 := CAST(rec.RS12 AS VARCHAR);
END IF;
END IF;

peg := ' NA';
IF (rec.Peg IS NOT NULL) THEN
peg := CAST(CAST(rec.Peg AS NUMERIC(5, 2)) AS VARCHAR);
END IF;

str := varSym || ' 3-Mo RS:' || rs03 || ' 12-Mo RS:' || rs12 || ' PEG: '
|| peg;

rtn := ROW(i, str);
RETURN NEXT rtn;

i := i + 1;

END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql;

SELECT str FROM fn_TestBug('2006/05/18');

"Alex Weslowski" <aweslowski(at)rpa(dot)com> writes:
> DECLARE
> peg VARCHAR(5) := '';
> rs03 VARCHAR(3) := '';
> rs12 VARCHAR(3) := '';
> rec RECORD;
> BEGIN
> FOR rec IN SELECT Symbol, RS03, RS12, Peg
> FROM TestBug WHERE Date=d
> ORDER BY RS12 DESC LOOP

The problem is that you've got local variables shadowing the field names
you want to use. What the SQL engine sees from that is

SELECT Symbol, $1, $2, $3
FROM TestBug WHERE Date=$4
ORDER BY $5 DESC

and it just assigns random ?columnN? names to the record columns (which
are going to have useless empty-string values anyway).

Use different local variable names, or qualify the field references in
the SELECT, eg TestBug.RS03.

regards, tom lane

On Tue, May 23, 2006 at 03:27:01AM +0000, Alex Weslowski wrote:
> Below is code for duplicating this error. Fields "Peg" and "Rs03" and "Rs12"
> are absent from returned record (either Record or Cursor) even though the
> values in the table are not null.

The function declares variables with the same names as table columns;
that makes queries like "SELECT Symbol, RS03, RS12, Peg ..." ambiguous
because it's not clear whether those names refer to columns or to
variables.

> Problem might be related to type conversion (NULL converts to '' which has
> no meaning to INT or NUMERIC).
>
> Problem is fixed by renaming columns to "Peg_Ratio" and "RS03RS" and
> "RS12RS". So, there is something more going on here, related to length of
> column name.

Type conversion and label length aren't relevant -- the problem is
due to using the same label to refer to multiple things. Use different
names for the variables or qualify the column names in the query
("SELECT t.symbol, t.rs03, t.rs12, t.peg FROM testbug AS t ...").

--
Michael Fuhr