ORDER BY with invalid syntax does not raise an error

Detail name	Value
Changelog Number	11313
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 8.0.0
Resolution Date	2022-04-20

Description

The ORDER BY phrase allows user to specify an alias for the expressions. Defining an alias in ORDER BY is unintended and should raise an error.

Examples

The following queries do not raise an error. Instead the string following the order by column is treated as an alias and ordering is performed in the default ascending order.

-- DESC is misspelled, but Exasol treats "dsc" as an alias for "start_time".
SELECT * FROM exa_dba_audit_sql ORDER BY start_time dsc;

The bug can be reproduced with ORDER BY in other contexts too, such as in Analytic Functions.

SELECT MAX(dummy) OVER (ORDER BY dummy dsc) FROM dual;

Another unintended side effect of this bug is this:.

-- The following query sorts the result by the first column as expected.
SELECT * FROM exa_all_users u ORDER BY 1;

-- However, in the following queries, the value "1" is treated as an expression and aliased as "dsc" so the result table is sorted by the constant value 1 instead of column 1 and the result is undefined.
SELECT * FROM exa_all_users u ORDER BY 1 dsc;
SELECT * FROM exa_all_users u ORDER BY 1 as dsc;

An alias is not supported when the keywords ASC or DESC are used explicitly to specify the ordering.

--  The following queries cause an error.
SELECT * FROM exa_dba_audit_sql ORDER BY start_time dsc ASC;
SELECT * FROM exa_dba_audit_sql ORDER BY start_time as dsc ASC;
SELECT * FROM exa_dba_audit_sql ORDER BY start_time dsc DESC;
SELECT * FROM exa_dba_audit_sql ORDER BY start_time as dsc DESC;

Note

This bug does not affect the result of the ordering.

Workaround

The alias of the ordering column has no effect and does not produce wrong results. This issue does not need a workaround.

Fix

In the fixed version, an error will be raised when the user tries to specify an alias for the ordering column. This can prevent users from making a typographical error.