Bugs item #2546483, was opened at 2009-01-29 20:11
Message generated for change (Settings changed) made by nielsnes
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: Closed
>Resolution: Fixed
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: Niels Nes (nielsnes)
Date: 2009-02-11 19:48
Message:
Fixed (tests added to BugTracker-2009). In insert (and update) we now
detect when need to add an extra project around a distinct-project.
----------------------------------------------------------------------
Comment By: Roberto Cornacchia (cornuz)
Date: 2009-01-30 09: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 22: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
------------------------------------------------------------------------------
Create and Deploy Rich Internet Apps outside the browser with Adobe(R)AIR(TM)
software. With Adobe AIR, Ajax developers can use existing skills and code to
build responsive, highly engaging applications that combine the power of local
resources and data with the reach of the web. Download the Adobe AIR SDK and
Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
