Internal server error for set functions in WITH clauses

Details

Detail name Value
Changelog Number 12514
Type Bug
Status Resolved
Affected Versions Exasol 6.2.15, Exasol 7.0.10
Fix Versions Exasol 6.2.16, Exasol 7.0.11
Resolution Date 2021-06-30

Background

Queries that use some specific set functions (COUNT with tuples, REGR_*, or RATIO_TO_REPORT) in common table expressions may cause an internal server error.
The fix to EXASOL-2889 causes this problem.

Description

The error appears if the following conditions are met:

  • The set function is of type REGR_*, ratio_to_report, or COUNT with tuples.
  • A common table expression contains one of the affected set functions.
  • The argument of the affected set function is the result of another set function or an analytic function.

Preparation 

DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
CREATE OR REPLACE TABLE test.t(a int);

Example: 

WITH W1 as ( SELECT regr_count(5, c1) as c2 FROM ( SELECT min(a) as c1 FROM Test.T ) ) SELECT * FROM W1;

Workaround

Inline the common table expression or create temporary view.
Example:

SELECT * FROM ( SELECT regr_count(5, c1) as c2 FROM ( SELECT min(a) as c1 FROM Test.T ) );

Fix

The query does not throw an error.