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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
