[ https://issues.apache.org/jira/browse/PHOENIX-4167?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dumindu Buddhika reassigned PHOENIX-4167: ----------------------------------------- Assignee: Dumindu Buddhika > 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 > Assignee: Dumindu Buddhika > 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 > {code:sql} > 0: jdbc:phoenix:> select * from test_select; > +---------+----------+-----+ > | NAM | ADDRESS | ID | > +---------+----------+-----+ > | user | place | 1 | > +---------+----------+-----+ > {code} > 4. Now run the following query > {code:sql} > 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 | > +--------------+----------------+----------------+ > {code} > 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 > {code:sql} > 0: jdbc:phoenix:> select distinct 'arbitrary' as "test_column", trim(nam), > trim(nam), trim(nam) from test_select; > {code} > Would generate > {code:sql} > +--------------+-----------------------+-----------------------+-----------------------+ > | test_column | TRIM(NAM) | TRIM(NAM) | > TRIM(NAM) | > +--------------+-----------------------+-----------------------+-----------------------+ > | arbitrary | useruseruser | useruseruser | useruseruser | > +--------------+-----------------------+-----------------------+-----------------------+ > {code} > A couple of things to notice > 1. If I remove the —— distinct 'harshit' as "test_column" —— The issue is > not seen > {code:sql} > 0: jdbc:phoenix:> select trim(nam), trim(nam), trim(nam) from test_select; > +------------+------------+------------+ > | TRIM(NAM) | TRIM(NAM) | TRIM(NAM) | > +------------+------------+------------+ > | user | user | user | > +------------+------------+------------+ > {code} > 2. If I remove the trim() again the issue is not seen > {code:sql} > 0: jdbc:phoenix:> select distinct 'arbitrary' as "test_column" ,nam, nam from > test_select; > +--------------+---------+---------+ > | test_column | NAM | NAM | > +--------------+---------+---------+ > | arbitrary | user | user | > +--------------+---------+---------+ > {code} -- This message was sent by Atlassian JIRA (v6.4.14#64029)