Try this one:
create table fltsrc (f1 float8);
insert into fltsrc values (1.0);
insert into fltsrc values (1.1);
create table intdest (f1 int4);
insert into intdest select distinct f1 from fltsrc;
Currently, this coredumps because it tries to apply float8lt
to integer values; the result of column f1 has been coerced to
the eventual destination format (int4) before it ever gets out
of the SELECT stage. But the parser assigned the sortop to use
for DISTINCT while f1 was still float :-(.
In 6.4.2, there's no coredump, but only one tuple gets inserted
into intdest, because the DISTINCT processing is done on int4 values.
I claim that is wrong too, because "select distinct f1 from fltsrc"
yields two tuples not one.
As far as I can see, there is no way to represent doing the Right
Thing with the current querytree representation. Either the
targetlist expression includes a coercion to int4 or it doesn't;
we can't represent "do the DISTINCT sort and filter on float8,
*then* coerce to int4" in a single-level targetlist.
Meanwhile, Jan has been muttering that he can't really do rules
right without an RTE entry for the result of a sub-select. And
the more I look at the UNION/EXCEPT/INTERSECT code, the less I
like it.
Maybe it is time to swallow hard and redesign the querytree
representation? I think we really need a multi-level-plan
data structure right out of the starting gate... a format
closer to the plantree structure that the executor uses would
probably be less trouble all around.
regards, tom lane