Virtual Schemas: Wrong results for UNION ALL with ORDER BY, LIMIT

Detail name	Value
Changelog Number	27922
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0, Exasol 2025.1.0, Exasol 2025.2.0
Fix Versions	Exasol 2026.1.0, Exasol 2025.2.1
Resolution Date	2026-03-20

Description

Virtual schema queries which combine UNION ALL with ORDER BY and/or LIMIT for the whole UNION ALL can return wrong results. For example the query SELECT * FROM vs.t UNION ALL SELECT * FROM vs.u ORDER BY a LIMIT 2 is affected. In particular, the ORDER BY and LIMIT which are supposed to apply to the whole table operator, are instead applied to the first operand of the table operator. This bug can only occur if the virtual schema adapter supports the pushdown of ORDER BY and/or LIMIT and this is not disabled for the virtual schema. This bug does not affect the other table operators UNION without ALL, INTERSECT, and EXCEPT/MINUS.

Example

-- Create a native schema containing these tables:
CREATE TABLE native.t(a INT);
INSERT INTO  native.t VALUES (5), (3), (1);
CREATE TABLE native.u(a INT);
INSERT INTO  native.u VALUES (6), (4), (2);

-- Create a virtual schema:
CREATE VIRTUAL SCHEMA vs USING native.jdbc_adapter WITH CONNECTION_NAME='VS_CONNECTION' SCHEMA_NAME='NATIVE';

-- Bug: The following query on the virtual schema returns wrong results:
SELECT * FROM vs.t     UNION ALL SELECT * FROM vs.u     ORDER BY a LIMIT 2; -- Bug: Returns 5 rows.

-- In comparison, an equivalent native query returns the correct results:
SELECT * FROM native.t UNION ALL SELECT * FROM native.u ORDER BY a LIMIT 2; -- OK:  Returns 2 rows.

-- The bug is caused by an incorrect pushdown of ORDER BY and LIMIT which is visible in EXPLAIN VIRTUAL:
EXPLAIN VIRTUAL SELECT * FROM vs.t UNION ALL SELECT * FROM vs.u ORDER BY a LIMIT 2;

Workaround

If the adapter supports excluding capabilities, disable the pushdown of LIMIT and ORDER BY for the virtual schema, for example:

ALTER VIRTUAL SCHEMA vs SET EXCLUDED_CAPABILITIES = 'LIMIT,LIMIT_WITH_OFFSET,ORDER_BY_COLUMN,ORDER_BY_EXPRESSION';

Fix

This kind of queries returns the correct results.