[YSQL] A max operation on a subquery with an OFFSET 0 clause returns the max value from the wrong column when yb_enable_expression_pushdown is true

[markpyb]

Jira Link: DB-13426

Description

With value yb_enable_expression_pushdown set to on

this test case

create table test (a bigint, b bigint);
insert into test (a, b)
select i, 2024000000000 + i
from generate_series(1, 100000) as i;

returns the max of A instead of the max of B

yugabyte=# select max(sub.b) from (select
yugabyte(# b from test
yugabyte(# where (
yugabyte(# b > 2024000000000 AND
yugabyte(# b < 2025000000000
yugabyte(# )
yugabyte(# OFFSET 0 ) as sub
yugabyte-# ;
  max   
--------
 100000
(1 row)

yugabyte=# insert into test values(100002,2024000100001) ;
INSERT 0 1
yugabyte=# select max(sub.b) from (select
yugabyte(# b from test
yugabyte(# where (
yugabyte(# b > 2024000000000 AND
yugabyte(# b < 2025000000000
yugabyte(# )
yugabyte(# OFFSET 0 ) as sub
yugabyte-# ;
  max   
--------
 100002
(1 row)

yugabyte=# select count(*) from test ;
 count  
--------
 100001
(1 row)

yugabyte=# select max(a),max(b) from test  ;
  max   |      max      
--------+---------------
 100002 | 2024000100001
(1 row)

Although not good practice SQL to use OFFSET like this, workarounds are available

Workarounds

(1)
Disabling expression pushdown returns correct result
set yb_enable_expression_pushdown to off;
(2)
Using ORDER BY semantics return correct result

select max(sub.b) from (select
b from test
where (
b > 2024000000000 AND
b < 2025000000000
)
ORDER BY B OFFSET 0 ) as sub
;
      max      
---------------
 2024000100000
(1 row)

(3)
Dont use OFFSET 0

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[andrei-mart]

The WHERE clause doesn't really matter.
It is interesting that plans look identical.

yugabyte=# explain verbose select max(sub.b) from (select b from test) as sub;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Finalize Aggregate  (cost=102.50..102.51 rows=1 width=8)
   Output: max(test.b)
   ->  Seq Scan on public.test  (cost=0.00..100.00 rows=1000 width=8)
         Output: test.b
         Partial Aggregate: true
 Query Identifier: 2271208597106393862
(6 rows)

yugabyte=# explain verbose select max(sub.b) from (select b from test OFFSET 0) as sub;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Finalize Aggregate  (cost=112.50..112.51 rows=1 width=8)
   Output: max(test.b)
   ->  Seq Scan on public.test  (cost=0.00..100.00 rows=1000 width=8)
         Output: test.b
         Partial Aggregate: true
 Query Identifier: 5078754654128703500
(6 rows)

The column b is the first in the subquery, but the second in the table, and this OFFSET 0 somehow prevents this mapping when partial aggregate is pushed down to the scan.

yugabyte=# select max(sub.b) from (select b from test OFFSET 0) as sub;
  max   
--------
 100000
(1 row)

yugabyte=# select max(sub.b) from (select b from test) as sub;
      max      
---------------
 2024000100000
(1 row)