[PHOENIX-4139] Incorrect query result for trailing duplicate GROUP BY expression

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 4.12.0
• Fix Version/s: 4.14.0, 5.0.0

Environment

minicluster

• Assignee: Csaba Skrabak
• Reporter: Csaba Skrabak
• Created: 29/Aug/17 16:18
• Updated: 26/Jul/18 01:15
• Resolved: 11/Mar/18 17:12

Description

From sme-hbase hipchat room:
Pulkit Bhardwaj·10:31

i'm seeing a weird issue with phoenix, appreciate some thoughts

Created a simple table in phoenix

0: jdbc:phoenix:> create table test_select(nam VARCHAR(20), address VARCHAR(20), id BIGINT
. . . . . . . . > constraint my_pk primary key (id));

0: jdbc:phoenix:> upsert into test_select (nam, address,id) values('pulkit','badaun',1);

0: jdbc:phoenix:> select * from test_select;
+---------+----------+-----+
|   NAM   | ADDRESS  | ID  |
+---------+----------+-----+
| pulkit  | badaun   | 1   |
+---------+----------+-----+


0: jdbc:phoenix:> select distinct 'harshit' as "test_column", nam from test_select;
+--------------+---------+
| test_column  |   NAM   |
+--------------+---------+
| harshit      | pulkit  |
+--------------+---------+


0: jdbc:phoenix:> select distinct 'harshit' as "test_column", trim(nam), trim(nam) from test_select;
+--------------+----------------+----------------+
| test_column  |   TRIM(NAM)    |   TRIM(NAM)    |
+--------------+----------------+----------------+
| harshit      | pulkitpulkit  | pulkitpulkit  |
+--------------+----------------+----------------+

When I apply a trim on the nam column and use it multiple times, the output has the cell data duplicated!

0: jdbc:phoenix:> select distinct 'harshit' as "test_column", trim(nam), trim(nam), trim(nam) from test_select;
+--------------+-----------------------+-----------------------+-----------------------+
| test_column  |       TRIM(NAM)       |       TRIM(NAM)       |       TRIM(NAM)       |
+--------------+-----------------------+-----------------------+-----------------------+
| harshit      | pulkitpulkitpulkit  | pulkitpulkitpulkit  | pulkitpulkitpulkit  |
+--------------+-----------------------+-----------------------+-----------------------+

Wondering if someone has seen this before??

One thing to note is, if I remove the —— distinct 'harshit' as "test_column" ——  The issue is not seen

0: jdbc:phoenix:> select trim(nam), trim(nam), trim(nam) from test_select;
+------------+------------+------------+
| TRIM(NAM)  | TRIM(NAM)  | TRIM(NAM)  |
+------------+------------+------------+
| pulkit     | pulkit     | pulkit     |
+------------+------------+------------+

Issue Links

• is cloned by: PHOENIX-4568 Duplicate entries in the GroupBy structure when running AggregateIT.testTrimDistinct