Missing LEVEL pseudo-column for VALUES and VALUES BETWEEN

Detail name	Value
Changelog Number	9776
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 8.0.0
Resolution Date	2022-04-20

Description

Queries fail if the LEVEL pseudo-column is used on VALUES or VALUES BETWEEN syntax.

Examples raising Error "object LEVEL not found"

select LEVEL
from (VALUES ('a')) as T(EX)
connect by LEVEL < 3;

select LEVEL
from VALUES BETWEEN 1 and 3 as T(EX)
connect by LEVEL < 3;

Workaround

Wrap the VALUES or VALUES BETWEEN into a subselect
example:

select LEVEL
from (select * from values ('a')) as T(EX)
connect by LEVEL < 3;

Fix

Those pseudo-columns now exist, so that the queries above succeed.

Changed behavior

Fix a bug where the LEVEL, CONNECT_BY_ISCYCLE and CONNECT_BY_ISLEAF pseudocolumns for CONNECT BY could not be used when selecting from VALUES or VALUES BETWEEN.