Navigation Menu

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

Jump to bottom

Query with SP doesn't accept explicit plan [CORE3451] #3812

Closed
firebird-automations opened this issue Apr 22, 2011 · 4 comments
Closed

Query with SP doesn't accept explicit plan [CORE3451] #3812

firebird-automations opened this issue Apr 22, 2011 · 4 comments
Assignees
@dyemanov
Labels
affect-version: 1.5.6 affect-version: 2.0.4 affect-version: 2.0.5 affect-version: 2.0.6 affect-version: 2.1.0 affect-version: 2.1.1 affect-version: 2.1.2 affect-version: 2.1.3 affect-version: 2.1.4 affect-version: 2.5.0 affect-version: 3.0 Initial component: engine fix-version: 5.0 Beta 1 priority: minor qa: done successfully type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pcisar

Relate to CORE3450
Is duplicated by CORE2629

Test query for CORE3450 doesn't accept explicit execution plan.

select t2.fld1
from tmp_tbl2 t2
join tmp_tbl1 t1 on t1.fld1=t2.fld1
join tmp_sp1 on tmp_sp1.fld1=t1.fld2
join tmp_tbl3 t3 on t3.fld1=t1.fld3
where t2.fld2=2
PLAN JOIN (JOIN (TMP_SP1 NATURAL, T1 INDEX (TMP_TBL1_FLD2)), T2 INDEX (TMP_TBL2_FLD1), T3 INDEX (TMP_TBL3_FLD1))
;
return error:
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Invalid command
there is no alias or table named TMP_SP1 at this scope level

The explicit plan is exactly the one returned for the query.
@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue relate to CORE3450 [ CORE3450 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.1.4 [ 10361 ]

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

Version: 1.5.6 [ 10225 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE2629 [ CORE2629 ]

@dyemanov dyemanov self-assigned this Sep 17, 2022
@dyemanov
Copy link
Member

I have somewhat mixed feelings about this ticket. It's really impossible to specify procedures in plans now and it looks inconsistent at the first glance (the plan reported by the engine should be accepted back, at least in trivial cases), but under the hood procedures are optimized separately from tables and the following plan is accepted and works as expected:
PLAN JOIN (JOIN (T1 INDEX (TMP_TBL1_FLD2)), T2 INDEX (TMP_TBL2_FLD1), T3 INDEX (TMP_TBL3_FLD1))
i.e. just remove procedure from the plan and it becomes "usable".

However, sometimes it could be useful to define whether a procedure should be joined using JOIN or HASH algorithm and it becomes impossible without specifying it inside the plan.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@dyemanov dyemanov

Labels
affect-version: 1.5.6 affect-version: 2.0.4 affect-version: 2.0.5 affect-version: 2.0.6 affect-version: 2.1.0 affect-version: 2.1.1 affect-version: 2.1.2 affect-version: 2.1.3 affect-version: 2.1.4 affect-version: 2.5.0 affect-version: 3.0 Initial component: engine fix-version: 5.0 Beta 1 priority: minor qa: done successfully type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@dyemanov @pavel-zotov @firebird-automations