Combination of Scalar Subselects, Window Functions and Filters can cause Error

Detail name	Value
Changelog Number	10037
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.alpha1, Exasol 6.1.10
Fix Versions	Exasol 6.1.11, Exasol 7.0.rc1, Exasol 6.2.9
Resolution Date	2020-07-21

Problem

The combination of a scalar subselect with analytic functions and filters can cause an error if several conditions apply to the same query:

• The query contains a scalar subselect.
• The query contains a subselect with an analytic function.
• The query contains a filter on the analytic function result.
• The select with the analytic function has a column of the analytic function partition clause (c1 in the example below) in the select list.
• The analytic function is not part of an expression.

Example:

CREATE SCHEMA test;
CREATE OR REPLACE TABLE t1(c1 INT);
CREATE OR REPLACE TABLE t2(c2 INT, c3 INT,c4 INT);
    
SELECT tmp.c1 FROM 
    (
    SELECT * FROM 
        (
        SELECT c1, MIN(c1) OVER (PARTITION BY c1) AS row_filter FROM test.t1
        )
     WHERE row_filter = 1
     ) tmp
WHERE tmp.c1 =
    (SELECT c4 FROM test.t2 );

Workaround

It is necessary to remove one of the above conditions. The easiest workaround is the introduction of an expression that involves the analytic function and the adjustment of the filter.
Example:

CREATE SCHEMA test;
CREATE OR REPLACE TABLE t1(c1 INT);
CREATE OR REPLACE TABLE t2(c2 INT, c3 INT,c4 INT);
    
SELECT tmp.c1 FROM 
    (
    SELECT * FROM 
        (
        SELECT c1, MIN(c1) OVER (PARTITION BY c1) + 1 AS row_filter FROM test.t1
        )
     WHERE row_filter = 2
     ) tmp
WHERE tmp.c1 =
    (SELECT c4 FROM test.t2 );