description: 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}

=>

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, WI-V2.5.5.26952 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}