Exasol – object XXX not found

TLDR: Identifiers in Exasol are stored in upper case internally. Selections should also be quoted.

Observation:

In Exasol I created a Python User Defined Function like this:

CREATE OR REPLACE PYTHON3 SCALAR SCRIPT "SCHEMA"."PARSE_XML" ("xml" VARCHAR(2000000) UTF8) 
EMITS ("parsed_column" VARCHAR(2000000) UTF8) AS
# Python script

Then tried to call the UDF in a select statement like this:

With CTE( 
    SELECT parse_cml(column) as parsed_column
    FROM table )
Select parsed_column
FROM CTE

However I received the error:

object PARSED_COLUMN not found

Although the column output is named in the UDF and even aliased in the cte, I was not able to select it. Conversely, if I would execute SELECT * the column would appear in the output.

Background Information:

Probably related to: “Regular identifiers are not case-sensitive but are always stored in UPPERCASE letters in the database.” (Exasol Docs)

Apparrently, in Exasol, when referencing a column without using double quotes, the database automatically converts the column name to uppercase. This behavior extends to aliases as well; if an alias is provided without double quotes, Exasol internally converts the alias to uppercase. That also explains why the error output mentions the missing object in upper case.

I also observed that it seems like aliasing the output of the UDF does not take any effect. SELECT * FROM CTE would still output the same column name as defined in the UDF.

Resolution:

This means that only the output column of the UDF is called “parsed_column”, but the output of the CTE is “PARSED_COLUMN”.

Having all this information we now have two alternatives:

Renaming the output of the UDF in upper case

CREATE OR REPLACE PYTHON3 SCALAR SCRIPT "SCHEMA"."PARSE_XML" ("xml" VARCHAR(2000000) UTF8) 
EMITS ("PARSED_COLUMN" VARCHAR(2000000) UTF8) AS
# Python script

Quoting the columns in the SELECT statement

With CTE( 
    SELECT parse_xml(column)
    FROM table )
Select "parsed_column"
FROM CTE