Обсуждение: Problem with planer
select ats.id, ap.value from akh_test_suit ats LEFT JOIN akh_properties ap on ap.ID =3D ats.test_suit_type_id where ats.ID =3D 472 id | value 472 | 472 ID -- integer value -- text select * from akh_test_suit ats LEFT JOIN akh_properties ap on ap.ID =3D ats.test_suit_type_id where ats.ID =3D 472 and ap.value::integer =3D ats.ID ERROR: invalid input syntax for integer: "--username sergeiz --password se= rgeiz --non-interactive svn://sergeiz" akh_properties.values has non numeric values, but those rows do not (MUST N= OT) participate in results as showed in first query Why PG check them?
Eugen.Konkov@aldec.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" > 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? can you share the PostgreSQL version you are using with us? maybe you can compile a test case? or maybe you are facing some sort of corruption? what happens after dump / reload? many thanks, hans -- Cybertec Schönig & Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com
> can you share the PostgreSQL version you are using PG8.3.1 >maybe you are facing some sort of corruption? I do not know. All other works fine except that. ----- Original Message ----- From: "Hans-Juergen Schoenig" <postgres@cybertec.at> To: <Eugen.Konkov@aldec.com> Cc: <pgsql-bugs@postgresql.org> Sent: Tuesday, August 26, 2008 1:15 PM Subject: Re: [BUGS] Problem with planer > Eugen.Konkov@aldec.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" >> 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? > > can you share the PostgreSQL version you are using with us? > maybe you can compile a test case? > or maybe you are facing some sort of corruption? what happens after dump / > reload? > > many thanks, > > hans > > -- > Cybertec Schönig & Schönig GmbH > PostgreSQL Solutions and Support > Gröhrmühlgasse 26, A-2700 Wiener Neustadt > Tel: +43/1/205 10 35 / 340 > www.postgresql-support.de, www.postgresql-support.com >
Eugen.Konkov@aldec.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?