[YSQL] Unexpected truncated data returned by MAX()

[bajinsheng]

Jira Link: DB-15997

Description

Please see this test case:

CREATE TABLE t1(c0 CHAR(100));
INSERT INTO t1(c0) VALUES('a');
SELECT MAX(t1.c0) FROM t1;

The query returns the column with truncated data:

 max 
-----
 a
(1 row)

While the data type is CHAR(100), based on the doc, I believe the returned data should not be truncated.

To further explore this issue, I ran the same query on PostgreSQL, and it returned:

                                                 max                                                  
------------------------------------------------------------------------------------------------------
 a                                                                                                   
(1 row)

I also ran another query SELECT t1.c0 FROM t1 on YugabyteDB, and the result is also expected:

                                                  c0                                                  
------------------------------------------------------------------------------------------------------
 a                                                                                                   
(1 row)

Version:

                                                                                         version                                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2-YB-2.25.1.0-b0 on x86_64-pc-linux-gnu, compiled by clang version 17.0.6 (https://github.com/yugabyte/llvm-project.git 9b881774e40024e901fc6f3d313607b071c08631), 64-bit
(1 row)

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.

[kmuthukk]

Thx for reporting @bajinsheng. I am able to see the difference you are reporting as well.

However, not sure if I would describe the issue as the "data having been truncated". In both YB & PG, I see the same output if we do something like the following; so even though the type of c0 is char(100), the length of the column is 1 in this case, and trailing spaces are not insignificant.

yugabyte=# CREATE TABLE t1(c0 CHAR(100));
CREATE TABLE
yugabyte=# INSERT INTO t1(c0) VALUES('a');
INSERT 0 1
yugabyte=# SELECT length(MAX(t1.c0)), MAX(t1.c0) || ';' FROM t1;
 length | ?column?
--------+----------
      1 | a;
(1 row)

In any case, we'll look into why we have this difference in behavior in the rendering of the output between PostgreSQL & YugabyteDB.

[bajinsheng]

@kmuthukk Thanks a lot for your prompt reply!

When looking at the source code, I have another finding. This query uses an aggregation pushdown optimization. If I disable it in the source code as follows:

diff --git a/src/postgres/src/backend/executor/nodeAgg.c b/src/postgres/src/backend/executor/nodeAgg.c
index 3c998fb4c1..cbee32e619 100644
--- a/src/postgres/src/backend/executor/nodeAgg.c
+++ b/src/postgres/src/backend/executor/nodeAgg.c
@@ -2151,7 +2151,7 @@ yb_agg_pushdown_supported(AggState *aggstate)
 
        /* Initially set pushdown supported to false. */
        aggstate->yb_pushdown_supported = false;
-
+       return;
        /* Phase 0 is a dummy phase, so there should be two phases. */
        if (aggstate->numphases != 2)
                return;

The return result is also expected:

                                                 max                                                  
------------------------------------------------------------------------------------------------------
 a                                                                                                   
(1 row)

Hope this info can provide some help.

Version: 2003789

[jasonyb]

Yes, it is aggregate pushdown related:

create table t2 (i int, c char(100));
insert into t2 values (1, 'a');
create index on t2 (i);
set yb_enable_index_aggregate_pushdown = on;
explain select max(c) from t2 where i = 1;
select max(c) from t2 where i = 1;
set yb_enable_index_aggregate_pushdown = off;
explain select max(c) from t2 where i = 1;
select max(c) from t2 where i = 1;

yugabyte=# create table t2 (i int, c char(100));
CREATE TABLE
yugabyte=# insert into t2 values (1, 'a');
INSERT 0 1
yugabyte=# create index on t2 (i);
CREATE INDEX
yugabyte=# set yb_enable_index_aggregate_pushdown = on;
SET
yugabyte=# explain select max(c) from t2 where i = 1;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Finalize Aggregate  (cost=5.25..5.26 rows=1 width=32)
   ->  Index Scan using t2_i_idx on t2  (cost=0.00..5.22 rows=10 width=404)
         Index Cond: (i = 1)
         Partial Aggregate: true
(4 rows)

yugabyte=# select max(c) from t2 where i = 1;
 max 
-----
 a
(1 row)

yugabyte=# set yb_enable_index_aggregate_pushdown = off;
SET
yugabyte=# explain select max(c) from t2 where i = 1;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Aggregate  (cost=5.25..5.26 rows=1 width=32)
   ->  Index Scan using t2_i_idx on t2  (cost=0.00..5.22 rows=10 width=404)
         Index Cond: (i = 1)
(3 rows)

yugabyte=# select max(c) from t2 where i = 1;
                                                 max                                                  
------------------------------------------------------------------------------------------------------
 a                                                                                                   
(1 row)