Recursive CTE can not be compiled when it has part that selects from source
with alias that equal to 'root' part of whole query
-------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5206
URL: http://tracker.firebirdsql.org/browse/CORE-5206
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.0, 4.0 Initial, 3.0 RC2
Reporter: Pavel Zotov
Test:
####
with recursive
r as ( ---------------------------------------------------- note on name: 'r'
select 1 as i from rdb$database
union all
select r.i+1 from r where r.i < 3
)
,g as (
select r.i from r
)
, u as (
select r.i from g as r -- <<<< yes, CTE part 'g' has alias 'r' here, and
this alias exactly matches to 'root' (first) part of CTE
)
select * from u
;
Result on 2.1.7.18553 and 2.5.6.27001: normal, three rows:
I
=====
1
2
3
Result on all FB 3.0 versions:
===
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Recursive CTE member (R) can refer itself only in FROM clause
===
Checked on:
3.0.0.30566 Firebird 3.0 Alpha 1
3.0.0.30809 Firebird 3.0 Alpha 2
3.0.0.31374 Firebird 3.0 Beta 1
3.0.0.31896 Firebird 3.0 Beta 2
3.0.0.32490 Firebird 3.0
4.0.0.141 Firebird 4.0 Unstable
--
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
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
