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

Error while preparing a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256") [CORE4684] #4992

Closed
firebird-automations opened this issue Feb 6, 2015 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Doing on EMPTY database:

C:\MIX\firebird\fb30>isql T0.FDB
Database: T0.FDB
SQL> show version;
ISQL Version: WI-T3.0.0.31557 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31557 Firebird 3.0 Beta 2"
on disk structure version 12.0
SQL> show table;
There are no tables in this database
SQL>
SQL> in z.sql;
Statement failed, SQLSTATE = 54001
Dynamic SQL Error
-Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256
After line 0 in file z.sql

Content of `z.sql`:

with recursive
c as (
select
rc.rdb$relation_name child_tab
,rc.rdb$constraint_name child_fk
,ru.rdb$const_name_uq parent_uk
,rp.rdb$relation_name parent_tab
from rdb$relation_constraints rc
join rdb$ref_constraints ru on
rc.rdb$constraint_name = ru.rdb$constraint_name
and rc.rdb$constraint_type = 'FOREIGN KEY'
join rdb$relation_constraints rp
on ru.rdb$const_name_uq = rp.rdb$constraint_name
where rc.rdb$relation_name <> rp.rdb$relation_name
)
,d as(
select
0 i
,child_tab
,child_fk
,parent_uk
,parent_tab
from c c0
where not exists( select * from c cx where cx.parent_tab= c0.child_tab )

union all

select
    d\.i\+1
    ,c\.child\_tab
    ,c\.child\_fk
    ,c\.parent\_uk
    ,c\.parent\_tab
from d
join c on d\.parent\_tab = c\.child\_tab

)
,e as(
select
i
,child_tab
,child_fk
,parent_uk
,parent_tab
,(select max(i) from d) as mi
from d
)
,f as(
select distinct
0 i
,child_tab
from e where i=0

UNION DISTINCT

select
    1
    ,child\_tab
from \(select child\_tab from e where i\>0 order by i\)

UNION DISTINCT

select
    2
    ,parent\_tab
from e
where i=mi

)
,t as(
select
rt.rdb$trigger_name trg_name -- f.child_tab, rt.rdb$trigger_name, rt.rdb$trigger_type
from f
join rdb$triggers rt on f.child_tab = rt.rdb$relation_name
where rt.rdb$system_flag=0 and rt.rdb$trigger_inactive=0
)
select 'alter trigger '||trim(trg_name)||' inactive' sql_expr
from t
union all
select 'delete from '||trim(child_tab)
from f
union all
select 'alter trigger '||trim(trg_name)||' active'
from t
;

PS. No such error on WI-V2.5.3.26730.

Commits: 4454a3f FirebirdSQL/fbt-repository@68fbcfa

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Interesting, v3 generates 258 contexts for this query while v2.5 generated only 225. I'm not sure this is a bug per se, maybe some implementation artifact. But it surely affects backward compatibility, so it's worth investigating deeper.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Beta 1 [ 10332 ]

Component: Engine [ 10000 ]

summary: Can`t prepare complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256") in 3.0 but success do that in 2.5 => Error while preparin a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256")

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Error while preparin a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256") => Error while preparing a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256")

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

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

2 participants