'first_value' could produce wrong results when combined with other aggregation functions

Detail name	Value
Changelog Number	27678
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.33.0, Exasol 2025.1.0, Exasol 2025.2.0
Fix Versions	Exasol 2025.1.5, Exasol 2025.2.0
Resolution Date	2025-12-05

Description

A bug caused first_value to interact with other set functions to return incorrect results. As an example:

select first_value(s) || count from(select 'HA' as s from (values between 1 and 5) T(idx));

would result in HA10 instead of the correct HA5.

Workaround

Ensure that the first_value is not evaluated in the same aggregate context as the other aggregates. See the example below

-- Example 1: 
-- returns the incorrect value 'HA10' as `first_value` is in the same
-- aggregation context as the other aggregation function `count`.
with base as (
  select 'HA' as s, idx
  from (values between 1 and 5) t(idx)
)
select first_value(s) || count(*) from base;

--===================================--

-- Example 2:
-- returns the correct result of 'HA5' because `first_value` is now in it's own 
-- aggregation context
with base as (
  select 'HA' as s, idx
  from (values between 1 and 5) t(idx)
)
select
  (select s from base order by idx limit 1) ||
  (select count(*) from base);

Fix

This bug has been fixed, and first_value now returns the correct results.