[FB-Tracker] Created: (CORE-5519) WI-V2.5.7.27050 generates "alias X conflicts with an alias in the same statement" on using both non-aliased and aliased CTE

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[FB-Tracker] Created: (CORE-5519) WI-V2.5.7.27050 generates "alias X conflicts with an alias in the same statement" on using both non-aliased and aliased CTE

JIRA tracker@firebirdsql.org
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
Loading...