Internal server error when using analytic function arguments that are distribution and not in select-list

Detail name	Value
Changelog Number	9156
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0
Fix Versions	Exasol 7.0.0, Exasol 6.2.4
Resolution Date	2019-12-18

Description

An 'Internal server error' might be caused when a subselect with an analytic function is joined with a table with distribution keys if the following conditions apply to the subselect with the analytic function:

• The FROM-clause of the subselect contains a distributed table
• Some of the distribution columns appear as a component of the analytic function (argument, PARTITION BY, ORDER BY), but not as a separate select-list element

This is a bug. Expected behavior is that the query should just run correctly.

Testcase

In the following example the problem appears because of column dist_col from PARTITION BY clause in subselect sub.

drop schema if exists test cascade;
create schema test;

create or replace table t1(dist_col int, other_col int, distribute by dist_col);
create or replace table t2(dist_col int, join_col int, distribute by dist_col);

SELECT 1
FROM (SELECT MIN(other_col) OVER (PARTITION BY dist_col) AS other_min FROM t1) sub
    JOIN t2 ON (sub.other_min = t2.join_col);

Workaround

A workaround is to remove one of the conditions that cause the query to fail, i.e.

• Add the components of the analytic function to the select list if they are distribution columns

-- add dist_col to select-list
SELECT 1
FROM (SELECT MIN(other_col) OVER (PARTITION BY dist_col) AS other_min,
             dist_col
      FROM t1) sub
    JOIN t2 ON (sub.other_min = t2.join_col);

• remove any chance of keeping distribution from the subselect by adding an ORDER BY-clause

-- eliminate distribution of subselect with ORDER BY-clause
SELECT 1
FROM (SELECT MIN(other_col) OVER (PARTITION BY dist_col) AS other_min FROM t1
      ORDER BY other_col
     ) sub
    JOIN t2 ON (sub.other_min = t2.join_col);