Thread: Weird behaviour

Could anyone please explain the behaviour of Postgres in the cases 
below? It evaluates an unused expression t.x || t.y in the first case 
but doesn't do it in the second one. It's also strange that the last 
explain throws an error.

postgres=# select version();                                                    version
---------------------------------------------------------------------------------------------------------------
PostgreSQL9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 
 
4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit
(1 row)

postgres=# create or replace function f()
postgres-# returns text as $$
postgres$# begin
postgres$#   raise exception 'here';
postgres$# end;
postgres$# $$ language plpgsql immutable;

postgres=# select t.x
postgres-# from (
postgres(#   select t.x, t.x || f()
postgres(#   from (values(1)) as t(x)
postgres(# ) t;
ERROR:  here

postgres=# select t.x
postgres-# from (
postgres(#   select t.x, t.x::text || f()
postgres(#   from (values(1)) as t(x)
postgres(# ) t; x
--- 1
(1 row)

postgres=# explain select t.x
postgres-# from (
postgres(#   select t.x, t.x || f()
postgres(#   from (values(1)) as t(x)
postgres(# ) t;
ERROR:  here

Vlad Arkhipov <arhipov@dc.baikal.ru> writes:
> Could anyone please explain the behaviour of Postgres in the cases 
> below?

I think it has something to do with anytextcat() being mistakenly marked
as volatile, thus preventing flattening of the subquery in the cases
where you don't explicitly coerce the integer to text.  When the
subquery does get flattened, that results in discarding the troublesome
expression as being unreferenced, so no error.  HEAD doesn't throw the
error for either case, thanks to commit
3db6524fe63f0598dcb2b307bb422bc126f2b15d.

> It evaluates an unused expression t.x || t.y in the first case 
> but doesn't do it in the second one. It's also strange that the last 
> explain throws an error.

I think your expectations need adjustment: what is strange is not
getting the error, but failing to get it.  In general the planner
assumes that it can freely evaluate immutable functions, and so this
query typically *will* throw an error during constant-simplification.
In some of these phrasings you manage to avoid that because the
expression is discarded as unreferenced before const-simplification
gets run, but that's an implementation artifact that should not
be relied on.
        regards, tom lane