Column Aliases in FROM combined with Analytic Functions cause an error

Detail name	Value
Changelog Number	9748
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.alpha1
Fix Versions	Exasol 7.0.0, Exasol 6.2.5
Resolution Date	2020-03-19

Problem

Column aliases cause an 'Internal Server Error' if several conditions occur at the same time:

• The column aliases are in the from_item (i.e., the FROM clause).
• The column aliases rename columns of a subquery.
• The subquery contains analytic functions.
• The outer query does not use all columns of the subquery.

Example:

create or replace table t1(c1 int);
create or replace table t2(c2 int, c3 int, c4 int);

SELECT
    tmp2
FROM
    (
        SELECT
            c2,
            RANK() OVER (PARTITION BY c3 ORDER BY c4) AS my_rank
        FROM
            t2 ) AS tmp_table( tmp1, tmp2 );

Workaround

Remove one of the conditions above. The easiest workaround is to move the column aliases from the from_item to the select list on either level

Example:

SELECT
    tmp2
FROM
    (
        SELECT
            c2 as tmp1,
            RANK() OVER (PARTITION BY c3 ORDER BY c4) AS tmp2
        FROM
            t2 ) AS tmp_table;