Issue Details (XML | Word | Printable)

Key: CORE-4684
Type: Bug Bug
Status: Resolved Resolved
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

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

Created: 06/Feb/15 06:20 PM   Updated: 29/May/15 09:27 PM
Component/s: Engine
Affects Version/s: 3.0 Beta 1
Fix Version/s: 3.0 Beta 2

QA Status: Done successfully


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 07/Feb/15 09:36 AM
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.