Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DISTINCT propagates outside a VIEW [CORE413] #757

Closed
firebird-automations opened this issue Dec 7, 2000 · 2 comments
Closed

DISTINCT propagates outside a VIEW [CORE413] #757

firebird-automations opened this issue Dec 7, 2000 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: @ArnoBrinkman

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10437 ] => Firebird [ 14726 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant