Skip site navigation (1) Skip section navigation (2)

Re: Problem with planer

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: Eugen(dot)Konkov(at)aldec(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Problem with planer
Date: 2008-09-02 04:21:07
Message-ID: 48BCBF33.5030809@pws.com.au (view raw or flat)
Thread:
Lists: pgsql-bugs
Eugen(dot)Konkov(at)aldec(dot)com wrote:
> select ats.id, ap.value from akh_test_suit ats
> LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id
> where ats.ID = 472
>  
> id | value
> 472 | 472
> ID -- integer
> value -- text
>  
>  
> select * from akh_test_suit ats
> LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id
> where ats.ID = 472 and ap.value::integer = ats.ID
>  
> ERROR:  invalid input syntax for integer: "--username sergeiz
> --password sergeiz --non-interactive svn://sergeiz"
>  
where clauses are not evaluated in the order written.  There is no
guarantee that ats.ID = 472 will be evaluated before ap.value::integer. 
You can't write SQL queries like they are a piece of programming logic,
they are not always evaluated in order and may not be evaluated in the
order you wrote them.  My first guess it to write it up as a subquery;

select * from (select * from akh_test_suit ats
LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id
where ats.ID = 472) as sub WHERE value::integer = ID;

Or unless you are specifically needing the performance, you could just
allow pg to automatically coerce ats.ID to text.

Regards

Russell.

> akh_properties.values has non numeric values, but those rows do not
> (MUST NOT) participate in results as showed in first query
> Why PG check them?


In response to

pgsql-bugs by date

Next:From: Hemavathi B NDate: 2008-09-02 05:08:52
Subject: BUG #4393: failed toget system metics for terminal services:87
Previous:From: Tom LaneDate: 2008-09-01 22:32:44
Subject: Re: Bug with FOR ... LOOP and composite types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group