DISTINCT query is returning duplicate rows
------------------------------------------
Key: DERBY-251
URL: http://issues.apache.org/jira/browse/DERBY-251
Project: Derby
Type: Bug
Components: SQL
Versions: 10.1.0.0
Reporter: Mamta A. Satoor
Assigned to: Mamta A. Satoor
Following query on a table with primary key returns duplicate rows
select distinct q1."NO1" from IDEPT q1, IDEPT q2
where ( q2."DISCRIM_DEPT" = 'HardwareDept')
and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1"
<> ALL ( select q3."NO1" from IDEPT q3 where ( q3."REPORTTO_NO" =
q2."NO1") ) ) ;
The sql script to create the table and load data into it is as follows
CREATE TABLE "APP"."IDEPT" ("DISCRIM_DEPT" VARCHAR(32), "NO1" INTEGER NOT NULL,
"NAME" VARCHAR(50), "AUDITOR_NO" INTEGER, "REPORTTO_NO" INTEGER, "HARDWAREASSET"
VARCHAR(15), "SOFTWAREASSET" VARCHAR(15));
-- primary/unique
ALTER TABLE "APP"."IDEPT" ADD CONSTRAINT "PK_IDEPT" PRIMARY KEY ("NO1");
insert into idept values ('Dept', 1, 'Department1', null, null, null, null);
insert into idept values ('HardwareDept', 2, 'Department2', 25, 1,
'hardwareaset2', null);
insert into idept values ('HardwareDept', 3, 'Department3', 25, 2,
'hardwareaset3', null);
insert into idept values ('SoftwareDept', 4, 'Department4', 25, 1, null,
'softwareasset4');
insert into idept values ('SoftwareDept', 5, 'Department5', 30, 4, null,
'softwareasset5');
The problem appears to be in
org.apache.derby.impl.sql.compile.FromList.returnsAtMostSingleRow() method.
This method along with other things tries to determine if the DISTINCT can be
dropped without causing duplicate rows. For the query in question, this method
decides that DISTINCT is not necessary which I think is incorrect.
If the table above is created with no primary key, the DISTINCT query does not
return duplicate rows. That is because one of the several criterias for
dropping DISTINCT is that there should be a unique index on the columns in the
where clause.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
