CREATE with subquery fails due to LIMIT in IN list

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

Description

A CREATE statement throws the error “too many values” if the following conditions are met:

• The create uses a subquery to create the data and metadata.
• The subquery contains an IN list.
• The IN list contains another subquery.
• The subquery in the IN list uses LIMIT.

Example

-- throws "too many values"
CREATE OR REPLACE VIEW test.v AS
SELECT 1 c FROM dual 
WHERE 1 IN 
  (SELECT 1 FROM dual ORDER BY 'x' LIMIT 1);

Workaround

Put the subquery in the IN list into another subquery using ORDER BY FALSE.

CREATE OR REPLACE VIEW test.v AS 
SELECT 1 c FROM dual 
WHERE 1 IN 
  (SELECT * FROM 
    (SELECT 1 FROM dual ORDER BY 'x' LIMIT 1)
  ORDER BY FALSE);

Fix

In this situation, the queries run as expected.