In the past couple of weeks our PostGIS tests against PostgreSQL 9.6 dev started failing. I traced the issue down to a behavior change in 9.6 when dealing with output of set returning functions when used with (func).* syntax.
Here is an example not involving PostGIS. Is this an intentional change in behavior? CREATE OR REPLACE FUNCTION dumpset(param_num integer, param_text text) RETURNS TABLE(id integer, junk1 text, junk2 text) AS $$ BEGIN RETURN QUERY SELECT id2 As id, $1 || $2::text As junk1, $1 || id2::text AS junk2 FROM generate_series(1,2) As id2; END; $$ language 'plpgsql'; -- Get 16 rows in 9.6, Get 8 rows in 9.5 SELECT (dumpset(f.test, 'hello world' || f.test)).* FROM generate_series(1,4) As f(test) ORDER BY junk2; I know that functions get called multiple times with (..).* and so it's frowned upon, but before the results would only return once and I suspect for people who are lazy and also don't mind the penalty cost they might just use this syntax. If its intentional I can change the tests to follow the best practice approach. I think the tests started failing around March 8th which I thought might have to do with this commit: 9118d03a8cca3d97327c56bf89a72e328e454e63 (around that time) When appropriate, postpone SELECT output expressions till after ORDER BY. It is frequently useful for volatile, set-returning, or expensive functions in a SELECT's targetlist to be postponed till after ORDER BY and LIMIT are done. Which involved change in output sort. I'm not absolutely sure if this has to do with that commit, because we had another test failing (where the order of the result changed, and putting in an order by fixed that test). Thanks, Regina -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
