X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1F6B3528FF for ; Tue, 19 Jul 2005 08:15:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96068-07 for ; Tue, 19 Jul 2005 11:15:28 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id F10EE528BD for ; Tue, 19 Jul 2005 08:15:25 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 08946415C92; Tue, 19 Jul 2005 12:15:21 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 88AA215EE6; Tue, 19 Jul 2005 12:03:39 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03747-09; Tue, 19 Jul 2005 12:03:36 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 4EED115EDA; Tue, 19 Jul 2005 12:03:36 +0100 (BST) Message-ID: <42DCDE07.4020906@archonet.com> Date: Tue, 19 Jul 2005 12:03:35 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: ilejn@yandex.ru Cc: pgsql-general@postgresql.org Subject: Re: Old question - failed to find conversion function from References: <42DCC1E9.2060300@aeccom.com> <42DCCCCE.000006.04382@mfront8.yandex.ru> In-Reply-To: <42DCCCCE.000006.04382@mfront8.yandex.ru> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200507/758 X-Sequence-Number: 80856 Ilja Golshtein wrote: > > When I invoke > > select 1 where 5 in (null) > > everything is Ok, > though when I try > > select 1 where 5 in (select null) > > I get > > ERROR: failed to find conversion function from "unknown" to integer. > > Is it desired behavior or subject to change > in future versions of PG? Well, it would obviously be better if PG could figure out it was safe, but I'm not sure there's a general case where it is. You can see it's OK because you know there's only one row in your SELECT result-set. However, what if you had: ... (select null UNION ALL select '2005-01-01') Now is this a set of "unknown" or "text" or "date"? Should PG change it's decision based on what the rest of the query wants? So - although this may change in future versions of PG, I wouldn't count on it never being a problem. > I realize my simplified example looks radiculous while it came from reality. > Sometimes my engine generates SQL queries does not know correct type > and unable to cast, while PostgreSQL should have all information required. How are you generating the SQL if you don't know the types involved? Are you just blindly quoting all values? What do you do with arrays/points etc, or don't you handle those? -- Richard Huxton Archonet Ltd