GROUP BY used with correlated subqueries can cause "not a valid GROUP BY expression" error

Detail name	Value
Changelog Number	21253
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.0.3, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0
Fix Versions	Exasol 2026.1.0, Exasol 2025.1.11
Resolution Date	2026-05-15

Description

Valid SQL queries that use GROUP BY together with correlated subqueries could fail to compile, producing the error: “not a valid GROUP BY expression”.

Example

CREATE OR REPLACE TABLE customer_orders (order_id INT);
CREATE OR REPLACE TABLE product_catalog (product_id INT);
CREATE OR REPLACE TABLE sales_transactions (transaction_id INT, order_ref INT, product_ref INT);
CREATE OR REPLACE TABLE warehouse_locations (location_id INT, region_id INT);
CREATE OR REPLACE TABLE shipment_batches (batch_id INT, shipment_code INT);

ENFORCE GLOBAL INDEX ON sales_transactions(order_ref);

-- Expected: Success
-- Observed: Error "not a valid GROUP BY expression"
SELECT FIRST_VALUE(
    (
        SELECT COUNT(*)
        FROM customer_orders
        WHERE customer_orders.order_id = transaction_id
    )
) AS count_customer_orders
FROM (
    SELECT product_catalog.product_id, sales_transactions.transaction_id, ROW_NUMBER() OVER () AS rn
    FROM product_catalog
    JOIN sales_transactions ON sales_transactions.order_ref = product_catalog.product_id
    JOIN warehouse_locations ON warehouse_locations.location_id = product_catalog.product_id
    JOIN shipment_batches AS batch0 ON warehouse_locations.region_id = batch0.batch_id
    LEFT JOIN (
        SELECT sales_transactions.order_ref, sales_transactions.transaction_id
        FROM sales_transactions
        GROUP BY sales_transactions.order_ref, sales_transactions.transaction_id
    ) AS cte ON cte.order_ref = product_catalog.product_id
             AND cte.transaction_id = sales_transactions.transaction_id
    LEFT JOIN shipment_batches AS batch1 ON batch1.shipment_code = 1
)
WHERE rn = 42
GROUP BY product_id;

Workaround

Perform the GROUP BY where column contains unique key enforcement, within a CTE and rewrite correlated subqueries with equivalent joins.

WITH base AS (
    SELECT
        pc.product_id,
        st.transaction_id,
        ROW_NUMBER() OVER () AS rn
    FROM product_catalog pc
    JOIN sales_transactions st
        ON st.order_ref = pc.product_id
    JOIN warehouse_locations wl
        ON wl.location_id = pc.product_id
    JOIN shipment_batches batch0
        ON wl.region_id = batch0.batch_id
    LEFT JOIN (
        SELECT
            st2.order_ref,
            st2.transaction_id
        FROM sales_transactions st2
        GROUP BY
            st2.order_ref,
            st2.transaction_id
    ) cte
        ON cte.order_ref = pc.product_id
       AND cte.transaction_id = st.transaction_id
    LEFT JOIN shipment_batches batch1
        ON batch1.shipment_code = 1
),
customer_counts AS (
    SELECT
        co.order_id,
        COUNT(*) AS cnt
    FROM customer_orders co
    GROUP BY co.order_id
)
SELECT
    FIRST_VALUE(COALESCE(cc.cnt, 0)) AS count_customer_orders
FROM base b
LEFT JOIN customer_counts cc
    ON cc.order_id = b.transaction_id
WHERE b.rn = 42
GROUP BY b.product_id;

Fix

Queries using GROUP BY with correlated subqueries now compile and execute as expected without producing the erroneous error message.