Issue Details (XML | Word | Printable)

Key: CORE-413
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Arno Brinkman
Reporter: Claudio Valderrama C.
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

DISTINCT propagates outside a VIEW

Created: 07/Dec/00 12:00 AM   Updated: 14/Jun/06 09:39 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 1.5.0

Time Tracking:
Not Specified

SF_ID: 224810


 Description  « Hide
SFID: 224810#
Submitted By: robocop

Given these two views:

CREATE VIEW VDISTIDX (RDB$RELATION_NAME) AS
 select distinct rdb$relation_name from rdb$indices

CREATE VIEW VDISTIDX2 (RDB$RELATION_NAME) AS
 select rdb$relation_name from rdb$indices
group by rdb$relation_name

Then
select count(*) from vdistidx v
join rdb$relation_fields rf
on v.rdb$relation_name = rf.rdb$relation_name
with the scheduler saying
PLAN SORT (JOIN (V RDB$INDICES NATURAL,RF INDEX (RDB$INDEX_4)))

produces a different result than
select count(*) from vdistidx2 v
join rdb$relation_fields rf
on v.rdb$relation_name = rf.rdb$relation_name
with the scheduler saying
PLAN MERGE (SORT (RF NATURAL),SORT (SORT (V RDB$INDICES NATURAL)))

Notice both JOIN statements produce different query plans but both views alone produce the same plan. I think that the first case is a bug: the DISTINCT clause is propagating outside the VIEW; just change the fist case to become
select rdb$relation_name
from VDISTIDX v join rdb$relation_fields rf
on v.rdb$relation_name = rf.rdb$relation_name

and you'll observe that effectively, the DISTINCT applied to the result of the JOIN, leaving each table name only once. I consider it to be a bug, since other engines (MsSql for example) produced the same result with both statements. Comments?

C.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.