Possible Internal Server Error for Queries that combine With Clauses and Analytic Functions

Detail name	Value
Changelog Number	6625
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0
Fix Versions	Exasol 6.0.12
Resolution Date	2018-08-17

Background

To cause this rare internal server error, several preconditions have to be met:

• The query contains a WITH clause
• The WITH clause uses an analytical function within an expression
• The analytic function uses a pl/SQL function that resolves to a constant expression

This combination can lead to access of invalid memory which leads to the internal server error.

Example

CREATE SCHEMA TEST;
OPEN SCHEMA TEST;

CREATE TABLE LOOKUP(KEY_NAME VARCHAR(10), KEY_VALUE INT);
INSERT INTO LOOKUP VALUES('X', 5);

--/
-- the pl/sql function
CREATE FUNCTION LOOKUP_FUNCTION ()
   RETURN TIMESTAMP
   IS
     RES TIMESTAMP;
   BEGIN
      RES := 
         ( 
            SELECT
                ADD_HOURS(TRUNC(SYSDATE), KEY_VALUE) AS Y
            FROM LOOKUP
             WHERE KEY_NAME = 'X'
         );
   RETURN RES;
END LOOKUP_FUNCTION;
/ 


CREATE TABLE FACT(ID INT, MEASURE INT);
INSERT INTO FACT VALUES
    (1,2)
    ,(2,3);
    
-- the view
CREATE OR REPLACE VIEW FACT_WEEK AS (
    SELECT * 
    FROM FACT 
    WHERE NOW() < LOOKUP_FUNCTION() + 5
);

-- multiple WITH clauses
WITH A AS (
    WITH B AS (
        SELECT
            -- the analytical function expression
            1 + SUM(MEASURE) OVER (PARTITION BY ID) as NONSENSE
        FROM FACT_WEEK
    )
    SELECT * FROM B
)
SELECT * FROM A;

Workaround

Remove one of the preconditions by rewriting the specific query.