Queries may fail when combining EMIT - SET chains with ROWNUM

Details

Detail name Value
Changelog Number 12399
Type Bug
Status Resolved
Affected Versions Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions Exasol 8.7.0
Resolution Date 2022-11-01

Description

The following situation may return one of the below error messages:

  • A query contains a SCALAR-EMITS UDF.
  • The results of the emitting function go directly into a SET UDF in a select around the emitting UDF.
  • The select with the SET function uses ROWNUM
  • Additional conditions apply (e.g. the WHERE-clause of select containing the SET-UDF has to be empty)

The error raised could be one of the following:

  • internal server error
  • 'data exception - invalid character value for cast'

Preparation: 

create schema if not exists test;

--/
create or replace python3 scalar script emit_example()
emits(res varchar(2000)) as

def run(ctx):
    for x in range (0,10000):
        ctx.emit('abc' + str(x))
/

--/
create or replace python3 set script aggregate_example(my_input varchar(2000))
emits(res1 varchar(2000),res2 varchar(2000000)) as

def run(ctx):
    while True:
        ctx.emit(ctx.my_input, 'X' + ctx.my_input + 'x')      
        if not ctx.next(): break
/

Example:

SELECT aggregate_example(res) 
FROM 
    (SELECT emit_example()) 
GROUP BY 
    ROWNUM MOD 2;

Workaround

Wrap the SCALAR-EMITS UDF into another subquery layer:

SELECT aggregate_example(res) 
FROM 
    (SELECT * 
        FROM 
            (SELECT emit_example())) 
GROUP BY 
    ROWNUM MOD 2;

Fix

The query does not return an error, as expected.