Bugs item #2546483, was opened at 2009-01-29 21:11
Message generated for change (Comment added) made by cornuz
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2546483&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Core
Group: SQL "stable"
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Roberto Cornacchia (cornuz)
Assigned to: Nobody/Anonymous (nobody)
Summary: DISTINCT does'n work when below a join
Initial Comment:
On Feb2009 branch:
CREATE TABLE a(n integer, x varchar(255));
INSERT INTO a VALUES(1, 'ONE');
INSERT INTO a VALUES(2, 'TWO');
INSERT INTO a VALUES(2, 'TWO');
INSERT INTO a VALUES(3, 'THREE');
CREATE VIEW va AS
SELECT DISTINCT n, x
FROM a;
CREATE TABLE b(n integer, x varchar(255));
INSERT INTO b VALUES(1, 'ONE');
INSERT INTO b VALUES(2, 'TWO');
INSERT INTO b VALUES(3, 'THREE');
CREATE VIEW v AS
SELECT va.x, b.x
FROM va, b
WHERE va.n = b.n;
sql>select * from va;
+---+-------+
| n | x |
+===+=======+
| 1 | ONE |
| 2 | TWO |
| 3 | THREE |
+---+-------+
That's ok!
sql>select * from v;
+-------+-------+
| x | x |
+=======+=======+
| ONE | ONE |
| TWO | TWO |
| TWO | TWO |
| THREE | THREE |
+-------+-------+
That's NOT ok!
The DISTINCT on a has been ignored!
----------------------------------------------------------------------
>Comment By: Roberto Cornacchia (cornuz)
Date: 2009-01-30 10:36
Message:
To be more precise on my previous comment: I'm not sure in which cases the
problem occurs.
I will give here an example where it does occur ('insert into t select
distinct ...' , where table t has a serial type):
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
CREATE TABLE a(n integer, x varchar(255));
INSERT INTO a VALUES(1, 'ONE');
INSERT INTO a VALUES(2, 'TWO');
INSERT INTO a VALUES(2, 'TWO');
INSERT INTO a VALUES(3, 'THREE');
CREATE TABLE b(n integer, x varchar(255));
INSERT INTO b SELECT DISTINCT n,x from a;
CREATE TABLE c(n serial, x varchar(255));
INSERT INTO c (x) SELECT DISTINCT x from a;
SELECT * FROM a; -- the initial duplicates are here
SELECT * FROM b; -- here they get removed, correct
SELECT * FROM c; -- here they don't get removed, wrong
----------------------------------------------------------------------
Comment By: Roberto Cornacchia (cornuz)
Date: 2009-01-29 23:24
Message:
Apparently t is not only about the Join.
DISTINCT works correctly only if it is on a top-level query.
INSERT INTO x
SELECT DISTINCT a from y;
does not work correctly either.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2546483&group_id=56967
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
