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