Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  5249   Status: Reported
wrong resultsets when doing join on views based on distinct select
Project:  InterBase Build #:  WI-V6.0.1.0
Version:    6.0 Submitted By:   Oliver Wegner
Report Type:  Basic functionality failure Date Reported:  7/15/2003 6:20:49 PM
Severity:    Commonly encountered problem Last Updated: 7/15/2003 6:31:59 PM
Platform:    Windows 95/98/NT 4.0 on Intel Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
When one creates a view which is based on two or more joined tables and which uses a "distinct" type of select, then using that specific view for inner joins in further select statements (or views) can deliver an incomplete resultset for that select (or view).
Steps to Reproduce:
I would like to show it with a certain database setup. Unfortunately, the SQL statements for setting up the whole database won't fit in here, so i have to restrict it:

the tables have this form:
CREATE TABLE T1
(
  ID
INTEGER NOT NULL,
  COLUMN1
INTEGER,
CONSTRAINT PK_T1 PRIMARY KEY (ID)
);

CREATE TABLE T1_TEXT
(
  ID
INTEGER NOT NULL,
  REFID
INTEGER NOT NULL,
  TEXT
VARCHAR(30),
  NUMBER
INTEGER,
CONSTRAINT PK_T1_TEXT PRIMARY KEY (ID)
);
ALTER TABLE T1_TEXT ADD CONSTRAINT FK1_T1_TEXT FOREIGN KEY (REFID) REFERENCES T1 (ID);

CREATE TABLE T2
(
  ID
INTEGER NOT NULL,
  T1REFID
INTEGER NOT NULL,
  DESCRIPTION
VARCHAR(250),
CONSTRAINT PK_T2 PRIMARY KEY (ID)
);
ALTER TABLE T2 ADD CONSTRAINT FK1_T2 FOREIGN KEY (T1REFID) REFERENCES T1 (ID);

the views look like this:
CREATE VIEW V1 (
  COLUMN1,
  T1ID,
  TEXT
) AS
select distinct t.column1,t.id,tt.text from
  t1 t join t1_text tt on t.id=tt.refid;

CREATE VIEW V2 (
  COLUMN1,
  T1ID,
  TEXT
) AS
select t.column1,t.id,tt.text from
  t1 t join t1_text tt on t.id=tt.refid
  group by t.column1,t.id,tt.text;

CREATE VIEW SUPERVIEW1 (
  COLUMN1,
  T1ID,
  DESCRIPTION
) AS
select v1.column1,v1.t1id,t2.description
  from v1 join t2 on v1.t1id=t2.t1refid;

CREATE VIEW SUPERVIEW2 (
  COLUMN1,
  T1ID,
  DESCRIPTION
) AS
select v2.column1,v2.t1id,t2.description
  from v2 join t2 on v2.t1id=t2.t1refid;

Be aware that V1 operates with "distinct" and V2 with "group by", but both should return the same resultsets (and they do that in fact). Contrary to that, SUPERVIEW1 and SUPERVIEW2 return different resultsets. SUPERVIEW1 (which is based on that "distinct" view V1) does not list all records if in table T2 are more than one record referring to the same id (T1ID) in V1. It seems when distinct is being used, the select that is based on V1 won't iterate through all records. SUPERVIEW2 gives the correct output though.
A complete example will you find in Attachments.
Workarounds
1. A workaround seems to be to use no "select distinct" statements and instead replace them by select statements with a group-clause over all selected columns. That should produce the correct resultset as you can see in the example.
Attachment
bugtest.zip
Comments

None

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.