Issue Details (XML | Word | Printable)

Key: CORE-3312
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

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

Sub-optimal join plan when the slave table depends on the master one via the OR predicate

Created: 20/Jan/11 06:01 AM   Updated: 04/Jun/13 12:30 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.0.4, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0
Fix Version/s: 2.5.1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
Test case:

select *
from rdb$relations r
  join rdb$security_classes sc
    on (r.rdb$security_class = sc.rdb$security_class)
-- Good plan:
-- PLAN JOIN (R NATURAL, SC INDEX (RDB$INDEX_7))

select *
from rdb$relations r
  join rdb$security_classes sc
    on (r.rdb$security_class = sc.rdb$security_class
      or r.rdb$default_class = sc.rdb$security_class)
-- Bad plan:
-- PLAN JOIN (R NATURAL, SC NATURAL)
-- Expected plan:
-- PLAN JOIN (R NATURAL, SC INDEX (RDB$INDEX_7, RDB$INDEX_7))

An attempt to specify the expected plan manually throws an error:
-- index RDB$INDEX_7 cannot be used in the specified plan

A prerequisite is that the slave index must be unique.

One more example is below. In fact, this is a bit different issue, although related and thus can be described by the same ticket.

select *
from rdb$relations r
  join rdb$security_classes sc
    on (r.rdb$security_class = sc.rdb$security_class and r.rdb$relation_id = 0)
      or (r.rdb$default_class = sc.rdb$security_class and r.rdb$relation_id = 1)
-- Reported (bad) plan:
-- PLAN JOIN (R INDEX (RDB$INDEX_1, RDB$INDEX_1), SC NATURAL)
-- Expected plan:
-- PLAN JOIN (R INDEX (RDB$INDEX_1, RDB$INDEX_1), SC INDEX (RDB$INDEX_7, RDB$INDEX_7))

This time the prerequisite is that the OR predicate has two ANDed sub-conditions and one of them (filter) is indexable for the master table.

 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.