Unexpected SQL results: string vs. direct SQL

Question

Working SQL

The following code works as expected, returning two columns of data (a row number and a valid value):

sql_amounts := '
  SELECT
    row_number() OVER (ORDER BY taken)::integer,
    avg( amount )::double precision
  FROM
    x_function( '|| id || ', 25 ) ca,
    x_table m
  WHERE
    m.category_id = 1 AND
    m.location_id = ca.id AND
    extract( month from m.taken ) = 1 AND
    extract( day from m.taken ) = 1
  GROUP BY
    m.taken
  ORDER BY
    m.taken';

FOR r, amount IN EXECUTE sql_amounts LOOP
  SELECT array_append( v_row, r::integer ) INTO v_row;
  SELECT array_append( v_amount, amount::double precision ) INTO v_amount;
END LOOP;

Non-Working SQL

The following code does not work as expected; the first column is a row number, the second column is NULL.

FOR r, amount IN
  SELECT
    row_number() OVER (ORDER BY taken)::integer,
    avg( amount )::double precision
  FROM
    x_function( id, 25 ) ca,
    x_table m
  WHERE
    m.category_id = 1 AND
    m.location_id = ca.id AND
    extract( month from m.taken ) = 1 AND
    extract( day from m.taken ) = 1
  GROUP BY
    m.taken
  ORDER BY
    m.taken
LOOP
  SELECT array_append( v_row, r::integer ) INTO v_row;
  SELECT array_append( v_amount, amount::double precision ) INTO v_amount;
END LOOP;

Question

Why does the non-working code return a NULL value for the second column when the query itself returns two valid columns? (This question is mostly academic; if there is a way to express the query without resorting to wrapping it in a text string, that would be great to know.)

Full Code

http://pastebin.com/hgV8f8gL

Software

PostgreSQL 8.4

Thank you.

@mu: `id` is an integer with value of `4` in both cases; the two queries return the same number of rows. — Dave Jarvis, May 29 '11 at 04:14
@mu: It seems as though I cannot include a `PREPARE` statement inside of a stored procedure. Running the proc twice results in an error that the prepared statement already exists. — Dave Jarvis, May 29 '11 at 10:39
@Dave: in case my answer rang the slightest kind of bell to you, please post the full code for your plpgsql function, and ping me by adding a note to my answer once you've done so. I'll edit it accordingly (or delete it, if it makes no sense in the light of additional context) once you do. — Denis de Bernardy, May 29 '11 at 11:27
@mu: Tom Lane mentioned that in PostgreSQL, the SQL statements are `PREPARE`d automatically. Might be for version 9.0, though. — Dave Jarvis, May 31 '11 at 10:16

score 1 · Answer 1 · answered May 29 '11 at 09:59

1

The two statements aren't strictly equivalent.

Assuming id = 4, the first one gets planned/prepared on each pass, and behaves like:

prepare dyn_stmt as '... x_function( 4, 25 ) ...'; execute dyn_stmt;

The other gets planned/prepared on the first pass only, and behaves more like:

prepare stc_stmt as '... x_function( $1, 25 ) ...'; execute stc_stmt(4);

(The loop will actually make it prepare a cursor for the above, but that's besides the point for our sake.)

A number of factors can make the two yield different results.

Search path changes before calling the procedure will be ignored by the second call. In particular if this makes x_table point to something different.
Constants of all kinds and calls to immutable functions are "hard-wired" in the second call's plan.

Consider this as an illustration of these side-effects:

deallocate all;
begin;
prepare good as select now();
prepare bad as select current_timestamp;
execute good; -- yields the current timestamp
execute bad;  -- yields the current timestamp
commit;
execute good; -- yields the current timestamp
execute bad;  -- yields the timestamp at which it was prepared

Why the two aren't returning the same results in your case would depend on the context (you only posted part of your pl/pgsql function, so it's hard to tell), but my guess is you're running into a variation of the above kind of problem.

answered May 29 '11 at 09:59

Denis de Bernardy

75,850
13
131
154

But preparing the statement should not yield different results! The might produce different execution plans though. – May 29 '11 at 10:02
See the example with now() vs current_timestamp. We'd need the whole function to know what's happening exactly... – Denis de Bernardy May 29 '11 at 10:05
@Denis: but none of the two statemens have such a criteria included. From what I can see they should not give different results (unless different values for `id` are passed) – May 29 '11 at 10:08
They do, actually. The second will cache the regproc/regclass of the function and table during the planning step; the first won't because it's planned on every call. If the OP subsequently changes the search path, they might be pointing to different objects. – Denis de Bernardy May 29 '11 at 10:11
Try this: create two schemas, test1 and test2. Add a test table with 1 row in the first, two rows in the second. set the search path to public, test1, and prepare `select * from test;` then set the search_path to public, test2 and execute the prepared statement. – Denis de Bernardy May 29 '11 at 10:12
@Denis: I've dropped the code into pastebin and updated the question with a link. – Dave Jarvis May 29 '11 at 11:51
Can you confirm how you're measuring that the second query is returning null as an average? Is it that result ends up with a null value in the array, or have you raised a notice in there somewhere? – Denis de Bernardy May 29 '11 at 12:30
@Denis: I used `RAISE NOTICE` to print the values when running the query with and without quotes. Without the quotes, all values were `NULL`. Also, the `plr_model_gam` function call fails, citing that it needs more than 3 non-null values. Use quotes and no complaints. – Dave Jarvis May 29 '11 at 13:33
@Denis: I see what you're doing, but it will use too much disk space. and it is difficult to verify whether the independent parts are correct (because they will take days to run). The proc must generate `INSERT` statements into a table that associates the city, day, and last value from the `gam` function (that amalgamates all the data from stations within a 25km radius of the city). – Dave Jarvis May 29 '11 at 13:45
I see the point on the disk space problem. Do take a look at the [latest iteration](http://pastebin.com/RJhc3mG1), though: if wrapped into an sql function, I think it'll be reasonable to pass arbitrary parameters in the actual query to verify intermediary results. – Denis de Bernardy May 29 '11 at 13:49
Anyway, I've honestly no idea why one is returning null and the other not. The most reasonable explanation I could think of on closer look at your whole function, was that the query plans differ if only very slightly, and that the prepared version matches a row that yields a null average while the dynamic version doesn't. But your order by clauses in each query should invalidate this idea, since they ensure that the row ordering is consistent. – Denis de Bernardy May 29 '11 at 13:55

score 1 · Accepted Answer · answered May 31 '11 at 10:10

From Tom Lane:

I think the problem is that you're assuming "amount" will refer to a table column of the query, when actually it's a local variable of the plpgsql function. The second interpretation will take precedence unless you qualify the column reference with the table's name/alias.

Note: PG 9.0 will throw an error by default when there is an ambiguity of this type.