Usage of scalar subqueries in views can lead to Feature not supported Error

Detail name	Value
Changelog Number	21768
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.11.0
Fix Versions	Exasol 8.32.0
Resolution Date	2024-11-08

Description

A query with a scalar subquery may lead to error “Feature not supported: Unexpected node during index creation: JoinPrefetchBasis” if the following conditions are met:

• The query contains a scalar subquery.
• The scalar subquery contains a CROSS JOIN.
• The query contains a view.

Preparation

CREATE SCHEMA test;

Example

CREATE VIEW unexpected_node AS
SELECT
    NULL AS dummy
FROM
    (VALUES NULL)
INNER JOIN
    (
    SELECT
        NULL AS join_column
    FROM
        (VALUES NULL)
    )
ON
    join_column =
        (
        SELECT
            NULL
        FROM
            (VALUES NULL)
        CROSS JOIN
            (VALUES NULL)
        )
;

Workaround

Remove one of the conditions above.

CREATE VIEW unexpected_node AS
SELECT
    NULL AS dummy
FROM
    (VALUES NULL)
INNER JOIN
    (
    SELECT
        NULL AS join_column
    FROM
        (VALUES NULL)
    )
ON
    join_column =
        (
        SELECT
            NULL
        FROM
            (VALUES NULL)
        )
;

Fix

Those queries run as expected.