Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR - Mailing list pgsql-general

From John Keith Hohm
Subject Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR
Date
Msg-id 20080908120500.1558b07a@sneezy.prov.us
Whole thread Raw
Responses Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR  (Alvaro Herrera <alvherre@commandprompt.com>)
Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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@hohm.net>

pgsql-general by date:

Previous
From: "Scott Marlowe"
Date:
Subject: Re: recover in single-user backend fails
Next
From: Alvaro Herrera
Date:
Subject: Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR