Thread: PostgreSQL 7.0 beta 4: Error in Insert/Select
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Michael Shepelev
Your email address : docent@alias.ru
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Celeron 400
Operating System (example: Linux 2.0.26 ELF) : Mandrake 7.0 RE
PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0 beta 4
Compiler used (example: gcc 2.8.0) : gcc version 2.95.2
19991024 (release)
Please enter a FULL description of your problem:
------------------------------------------------
Sorry my english.
I found bug (IMHO) in Insert/Select Union command.
Result of SELECT UNION differs from INSERT SELECT UNION.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
I attach an example that demonstrates this bug.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
<<bug_in_insert.tgz>>
Attachment
Michael Shepelev <MichaelS@alias.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.
I think it's probably got something to do with the UNION code's
manipulations of the query targetlist, but am too tired to puzzle out
more tonight. Anyone else want to look at it?
regards, tom lane
> Michael Shepelev <MichaelS@alias.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
Michael Shepelev <MichaelS@alias.ru> wrote a while ago:
> I found bug (IMHO) in Insert/Select Union command.
> Result of SELECT UNION differs from INSERT SELECT UNION.
This is now fixed for 7.1.
regards, tom lane
I believe this is fixed in 7.1beta. [ Charset KOI8-R unsupported, converting... ] > ============================================================================ > POSTGRESQL BUG REPORT TEMPLATE > ============================================================================ > Your name : Michael Shepelev > Your email address : docent@alias.ru > > > System Configuration > --------------------- > Architecture (example: Intel Pentium) : Intel Celeron 400 > > Operating System (example: Linux 2.0.26 ELF) : Mandrake 7.0 RE > > PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0 beta 4 > > Compiler used (example: gcc 2.8.0) : gcc version 2.95.2 > 19991024 (release) > > > Please enter a FULL description of your problem: > ------------------------------------------------ > Sorry my english. > I found bug (IMHO) in Insert/Select Union command. > Result of SELECT UNION differs from INSERT SELECT UNION. > > > Please describe a way to repeat the problem. Please try to provide a > concise reproducible example, if at all possible: > ---------------------------------------------------------------------- > I attach an example that demonstrates this bug. > > > > If you know how this problem might be fixed, list the solution below: > --------------------------------------------------------------------- > <<bug_in_insert.tgz>> [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026