Postgres Pro
Downloads
Home   >   mailing lists

Thread: Bug

Bug

From
Oleg Broytmann
Date:
---------- 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

Re: [HACKERS] Bug

From
Tom Lane
Date:
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

Re: [HACKERS] Bug

From
Bruce Momjian
Date:
> 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

Re: [HACKERS] Bug

From
Bruce Momjian
Date:
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

Re: [HACKERS] Bug

From
Tom Lane
Date:
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)

Re: Bug

From
Tom Lane
Date:
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