Can`t prepare recursive query if recursive part contains ALIASED datasource in
the join with anchor table
---------------------------------------------------------------------------------------------------------
Key: CORE-4694
URL: http://tracker.firebirdsql.org/browse/CORE-4694
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
DDL:
====
show version;
recreate table xcall_stack (
xcall_id int
,xcaller_id int
);
commit;
set planonly;
with recursive
r as (
select c.xcall_id
from xcall_stack c
where c.xcaller_id is null
UNION ALL
select
c.xcall_id
from xcall_stack c
join r
AS h -- <<<<<<<<<<<<<<<<<<<<<< ::: NB ::: `r` is aliased
on c.xcaller_id = h.xcall_id
)
select r.xcall_id
from r;
Test on 2.5 (passed OK):
=========
ISQL Version: WI-V2.5.4.26838 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.4.26838 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.4.26838 Firebird
2.5/tcp (csprog)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.4.26838 Firebird
2.5/tcp (csprog)/P12"
on disk structure version 11.2
PLAN (R C NATURAL)
PLAN (R C NATURAL)
Test on 3.0:
=========
ISQL Version: WI-T3.0.0.31655 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31655 Firebird
3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31655 Firebird
3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31655
Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-H.XCALL_ID
-At line 17, column 6
At line 26 in file rec-broken.sql
PS. No error message if recursive part looks like this:
. . .
UNION ALL
select c.xcall_id
from xcall_stack c
join r -- <<<<<<<<<<<<<<<<<<<<<<<<<<< NO alias here for `r`
on c.xcaller_id = r.xcall_id
. . .
--
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
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=190641631&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
