Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

[John Keith Hohm <john@xxxxxxxx>]

This fails with ERROR:  invalid input syntax for integer: "JOHN":

select * from (
  select * from (VALUES ('100'), ('JOHN')) as A (n)
  where trim(trim(n), '0123456789') = ''
) as B where n::integer <> -1;

But without the nested trim, this works just fine (although it would not
suffice for my purpose):

select * from (
  select * from (VALUES ('100'), ('JOHN')) as A (n)
  where trim(n, '0123456789') = ''
) as B where n::integer <> -1;

I think they should both work because the documentation says "A
sub-SELECT can appear in the FROM clause. This acts as though its
output were created as a temporary table for the duration of this
single SELECT command."

The cause is clear when you look at the error-ing EXPLAIN:

 Values Scan on "*VALUES*"  (cost=0.00..0.06 rows=1 width=32)
   Filter: (((column1)::integer <> (-1)) AND (btrim(btrim(column1),
'0123456789'::text) = ''::text))

Versus the single-trim EXPLAIN:

 Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=1 width=32)
   Filter: ((btrim(column1, '0123456789'::text) = ''::text) AND
((column1)::integer <> (-1)))

The extra trim causes the cast-and-compare to happen before the
trim-and-compare. By my understanding PostgreSQL should not be allowed
to reorder the clause of the subselect before the outer select.

I'm running the Ubuntu postgresql package version 8.3.3-0ubuntu0.8.04.

I'm also interested in responses of the form "why not just do X?".

-- 
John Keith Hohm
<john@xxxxxxxx>