Обсуждение: Bug
---------- Forwarded message ----------
Date: Fri, 17 Sep 1999 17:52:44 +0400 (MSD)
From: Artem Chuprina <ran@pirit.com>
ran=> create table test_source (src text);
CREATE
ran=> insert into test_source values('First distinct');
INSERT 235913 1
ran=> insert into test_source values('First distinct');
INSERT 235914 1
ran=> insert into test_source values('Second distinct');
INSERT 235915 1
ran=> insert into test_source values('Second distinct');
INSERT 235916 1
ran=> select src from test_source;
src
---------------
First distinct
First distinct
Second distinct
Second distinct
(4 rows)
ran=> select distinct src from test_source;
src
---------------
First distinct
Second distinct
(2 rows)
ran=> create sequence seq_test;
CREATE
ran=> create table test1 (n int default nextval('seq_test'), t text);
CREATE
ran=> create table test2 (n int, t text);
CREATE
ran=> insert into test2 ("t") select distinct src from test_source;
INSERT 0 2
ran=> insert into test1 ("t") select distinct src from test_source;
INSERT 0 4
Look here^
ran=> select * from test2;
n|t
-+---------------|First distinct |Second distinct
(2 rows)
ran=> select * from test1;
n|t
-+---------------
1|First distinct
2|First distinct
3|Second distinct
4|Second distinct
(4 rows)
PostgreSQL 6.4.2, PostgreSQL 6.5.1.
--
Artem Chuprina E-mail: ran@pirit.com
Network Administrator FIDO: 2:5020/371.32
PIRIT Corp. Phone: +7(095) 115-7101
Oleg Broytmann <phd@sun.med.ru> writes:
> ran=> create table test1 (n int default nextval('seq_test'), t text);
> ran=> insert into test1 ("t") select distinct src from test_source;
> [ doesn't work right ]
My, that's an interesting case. I think that fits right in with my
remark yesterday that the SELECT inside an INSERT ... SELECT needs
to have a targetlist that's separate from the INSERT's list. As it
stands, we form a targetlist representing the set of values that need
to be inserted into the target table --- and then the DISTINCT pass
runs on those tuples :-(, because there is nothing else for it to
run on.
In short, this is not a trivial thing to fix. We need multilevel
query trees...
regards, tom lane
> Oleg Broytmann <phd@sun.med.ru> writes:
> > ran=> create table test1 (n int default nextval('seq_test'), t text);
> > ran=> insert into test1 ("t") select distinct src from test_source;
> > [ doesn't work right ]
>
> My, that's an interesting case. I think that fits right in with my
> remark yesterday that the SELECT inside an INSERT ... SELECT needs
> to have a targetlist that's separate from the INSERT's list. As it
> stands, we form a targetlist representing the set of values that need
> to be inserted into the target table --- and then the DISTINCT pass
> runs on those tuples :-(, because there is nothing else for it to
> run on.
>
> In short, this is not a trivial thing to fix. We need multilevel
> query trees...
Added to TODO:
* Allow multi-level query trees for INSERT INTO ... SELECT
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
It appears this bug still exists.
>
> ---------- Forwarded message ----------
> Date: Fri, 17 Sep 1999 17:52:44 +0400 (MSD)
> From: Artem Chuprina <ran@pirit.com>
>
> ran=> create table test_source (src text);
> CREATE
> ran=> insert into test_source values('First distinct');
> INSERT 235913 1
> ran=> insert into test_source values('First distinct');
> INSERT 235914 1
> ran=> insert into test_source values('Second distinct');
> INSERT 235915 1
> ran=> insert into test_source values('Second distinct');
> INSERT 235916 1
> ran=> select src from test_source;
> src
> ---------------
> First distinct
> First distinct
> Second distinct
> Second distinct
> (4 rows)
>
> ran=> select distinct src from test_source;
> src
> ---------------
> First distinct
> Second distinct
> (2 rows)
>
> ran=> create sequence seq_test;
> CREATE
> ran=> create table test1 (n int default nextval('seq_test'), t text);
> CREATE
> ran=> create table test2 (n int, t text);
> CREATE
> ran=> insert into test2 ("t") select distinct src from test_source;
> INSERT 0 2
> ran=> insert into test1 ("t") select distinct src from test_source;
> INSERT 0 4
>
> Look here^
>
> ran=> select * from test2;
> n|t
> -+---------------
> |First distinct
> |Second distinct
> (2 rows)
>
> ran=> select * from test1;
> n|t
> -+---------------
> 1|First distinct
> 2|First distinct
> 3|Second distinct
> 4|Second distinct
> (4 rows)
>
> PostgreSQL 6.4.2, PostgreSQL 6.5.1.
>
> --
> Artem Chuprina E-mail: ran@pirit.com
> Network Administrator FIDO: 2:5020/371.32
> PIRIT Corp. Phone: +7(095) 115-7101
>
>
>
> ************
>
>
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> It appears this bug still exists.
Yes. I think this cannot be fixed without having a two-level querytree
structure for INSERT ... SELECT. The problem is basically that the
DISTINCT processing is happening on the tuples that are ready to put
into the target table (after the 'n' column is added), rather than on
the tuples that are coming out of the source table. With only one
targetlist there is no way to represent the notion that the DISTINCT
needs to happen on just the 't' column.
This is one of a large number of things waiting for a redesign of
querytrees...
regards, tom lane
>> ran=> create table test1 (n int default nextval('seq_test'), t text);
>>
>> ran=> insert into test1 ("t") select distinct src from test_source;
>>
>> ran=> select * from test1;
>> n|t
>> -+---------------
>> 1|First distinct
>> 2|First distinct
>> 3|Second distinct
>> 4|Second distinct
>> (4 rows)
A good while back, Oleg Broytmann <phd@sun.med.ru> wrote:
>> ran=> create table test1 (n int default nextval('seq_test'), t text);
>> ran=> insert into test1 ("t") select distinct src from test_source;
>> [ doesn't work right ]
> My, that's an interesting case. I think that fits right in with my
> remark yesterday that the SELECT inside an INSERT ... SELECT needs
> to have a targetlist that's separate from the INSERT's list. As it
> stands, we form a targetlist representing the set of values that need
> to be inserted into the target table --- and then the DISTINCT pass
> runs on those tuples :-(, because there is nothing else for it to
> run on.
FYI, this now works in current sources.
regards, tom lane