Re: PostgreSQL 7.0 beta 4: Error in Insert/Select

> Michael Shepelev <MichaelS(at)alias(dot)ru> writes:
>> I found bug (IMHO) in Insert/Select Union command.
>> Result of SELECT UNION differs from INSERT SELECT UNION.

> Wow, that's bizarre. I confirm seeing the inconsistent behavior.

On further investigation, it seems this is just another facet of the
known bogosities in INSERT ... SELECT ... UNION (see for example my
pghackers message of 7/19/99 22:59 EDT, as well as some others in the
archives). The INSERT into "o1" needs to rewrite the result of the
SELECT to include a couple of null column values, and this is being
done to the first sub-SELECT's target list *but not the second's*.
The upshot is garbage data inserted into the result table.

I looked at fixing this and concluded that trying to fix it five
days before 7.0 release would be just plain foolhardy. That part
of the code needs a thorough rewrite not emergency patches :-(
I recommend adding this example to the list of test cases for our
upcoming querytree rewrite.

In the meantime, an ugly workaround is available for Michael:
don't omit any columns in the INSERT targetlist, but instead
write explicit NULL results in the right places in the sub-SELECTs.
(Ick... but that's the best we can do for now...)

regards, tom lane