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
---------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4684
URL: http://tracker.firebirdsql.org/browse/CORE-4684
Project: Firebird Core
Issue Type: Bug
Reporter: 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
