Analytic Function and Scalar Subquery cause Error

Detail name	Value
Changelog Number	30423
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.32.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0
Fix Versions	Exasol 2025.1.11, Exasol 2026.1.1
Resolution Date	2026-06-03

Description

A query that contains an analytic function and a scalar subquery may cause an “Internal Server Error” or lead to wrong results.
This happens if all the following criteria are met and appear in the same query:

• The query contains an analytic function.
• The analytic function references a column of a subquery in the PARTITION BY or ORDER BY clause.
• The referenced column in the subquery is an alias and references a non trivial expression in the select list of the subquery.
• The referenced non trivial expression in turn contains a scalar subquery.
• The compiler can eliminate the subquery that contains the referenced expression.

Example:

CREATE TABLE T1 (F1 INT);

SELECT
  z, DENSE_RANK() OVER(ORDER BY z)
FROM
(
  SELECT 'x' AS f1, CASE WHEN 1 >= (SELECT 4 FROM DUAL) THEN 'y' END AS z FROM T1
);

Workaround

Prevent the subquery elimination with ORDER BY FALSE.

SELECT
  z, DENSE_RANK() OVER(ORDER BY z)
FROM
(
  SELECT 'x' AS f1, CASE WHEN 1 >= (SELECT 4 FROM DUAL) THEN 'y' END AS z FROM T1 ORDER BY FALSE
);

Fix

The query works as intended.