View creation fails for some subqueries containing IN and ORDER BY LIMIT

Detail name	Value
Changelog Number	12552
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 8.21.0
Resolution Date	2023-08-04

Description

View creation fails for certain subqueries if the following conditions are met:

• View text contains an IN clause with a subselect
• the IN subselect contains an ORDER BY LIMIT clause
• the IN subselect orders by a column or expression that is not in the select list

Preparation

create schema test;

create table t(a varchar(10));
insert into t values 'a';

Example:

create or replace view v as
SELECT
	*
FROM
	t t1
WHERE
	a IN (
	SELECT
		a || 'a'
	FROM
		t t2
	ORDER BY
		a DESC
	LIMIT 1
);

The query returns the following error message:

[Code: 0, SQL State: 71459] too many values (Session: 1703429765309106052)

Workaround

Use EXISTS instead of IN:

CREATE OR REPLACE VIEW 
    v AS
SELECT
    *
FROM
    t t1
WHERE
    EXISTS 
    (   SELECT 
            1 
        FROM 
            (   SELECT 
                    a || 'a' c 
                FROM 
                    t 
                ORDER BY 
                    a DESC LIMIT 1) t2 
        WHERE 
            t1.a=t2.c);

Fix

The view creation succeeds, as expected.