Pulkit Bhardwaj created PHOENIX-4167:
----------------------------------------
Summary: Phoenix SELECT query returns duplicate data in the same
varchar/char column if a trim() is applied on the column AND a distinct
arbitrary column is generated in the query
Key: PHOENIX-4167
URL: https://issues.apache.org/jira/browse/PHOENIX-4167
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.7.0
Reporter: Pulkit Bhardwaj
Priority: Minor
1. Created a simple table in phoenix
{code:sql}
create table test_select(nam VARCHAR(20), address VARCHAR(20), id BIGINT
constraint my_pk primary key (id));
{code}
2. Insert a sample row
{code:sql}
upsert into test_select (nam, address,id) values('user','place',1);
{code}
3. Confirm that the row is present
0: jdbc:phoenix:> select * from test_select;
+---------+----------+-----+
| NAM | ADDRESS | ID |
+---------+----------+-----+
| user | place | 1 |
+---------+----------+-----+
4. Now run the following query
0: jdbc:phoenix:> select distinct 'arbitrary' as "test_column", trim(nam),
trim(nam) from test_select;
This would generate the following output
+--------------+----------------+----------------+
| test_column | TRIM(NAM) | TRIM(NAM) |
+--------------+----------------+----------------+
| arbitrary | useruser | useruser |
+--------------+----------------+----------------+
As we can see the output for the trim(name) which should have been 'user' is
actually printed as 'useruser'
The concatenation to the string is actually the number of times the column is
printed.
The following
0: jdbc:phoenix:> select distinct 'arbitrary' as "test_column", trim(nam),
trim(nam), trim(nam) from test_select;
Would generate
+--------------+-----------------------+-----------------------+-----------------------+
| test_column | TRIM(NAM) | TRIM(NAM) |
TRIM(NAM) |
+--------------+-----------------------+-----------------------+-----------------------+
| arbitrary | useruseruser | useruseruser | useruseruser |
+--------------+-----------------------+-----------------------+-----------------------+
A couple of things to notice
1. 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) |
+------------+------------+------------+
| user | user | user |
+------------+------------+------------+
2. If I remove the trim() again the issue is not seen
0: jdbc:phoenix:> select trim(nam), trim(nam) from test_select;
+------------+------------+
| TRIM(NAM) | TRIM(NAM) |
+------------+------------+
| user | user |
+------------+------------+
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
