You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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.
The text was updated successfully, but these errors were encountered: