WI-V2.5.7.27050 generates "alias X conflicts with an alias in the same
statement" on using both non-aliased and aliased CTE
---------------------------------------------------------------------------------------------------------------------------
Key: CORE-5519
URL: http://tracker.firebirdsql.org/browse/CORE-5519
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.7
Environment: Windows 7 x64 running Win32 WI-V2.5.7.27050
Reporter: Jeroen Wiert Pluimers
Priority: Minor
>From
>https://stackoverflow.com/questions/43354272/why-alias-x-conflicts-with-an-alias-in-the-same-statement-on-aliased-cte-use-b
I wonder why the below statement causes this error with at least the Win32
Firebird WI-V2.5.7.27050 and WI-V2.5.2.26540 versions.
{code:text}
Dynamic SQL Error SQL error code = -204 alias TRIPLEDIGITS conflicts with an
alias in the same statement
{code}
Unlinke many errors in SQL statements, the above error does not indicate the
line that causes it, but with some fiddling I found the line marked with a
comment causes it.
Query having one CTE unaliased and an the same CTE aliased:
{code:sql}
with
recursive
tripledigits(n) as (
select 0
from rdb$database
union all
select tripledigits.n + 1
from tripledigits
where tripledigits.n < 999
),
sextupledigits(n) as (
select tripledigits.n
+ tripledigits000.n * 1000
from tripledigits
cross join tripledigits tripledigits000 -- causes "Dynamic SQL Error SQL
error code = -204 alias TRIPLEDIGITS conflicts with an alias in the same
statement"
order by tripledigits.n
+ tripledigits000.n * 1000
)
select sextupledigits.n
from sextupledigits
order by sextupledigits.n
{code}
It also fails when moving the failing query out of the `sextupledigits` CTE
into the main query:
{code:sql}
with
recursive
tripledigits(n) as (
select 0
from rdb$database
union all
select tripledigits.n + 1
from tripledigits
where tripledigits.n < 999
)
select tripledigits.n
+ tripledigits000.n * 1000
from tripledigits
cross join tripledigits tripledigits000 -- causes "Dynamic SQL Error SQL error
code = -204 alias TRIPLEDIGITS conflicts with an alias in the same statement"
order by tripledigits.n
+ tripledigits000.n * 1000
{code}
It does not fail when using a regular table in unaliased and aliased form:
{code:sql}
select rdb$database.rdb$relation_id + rd.rdb$relation_id * 1000
from rdb$database
cross join rdb$database rd
{code}
--
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
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
