[ https://issues.apache.org/jira/browse/PHOENIX-2170?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dumindu Buddhika updated PHOENIX-2170: -------------------------------------- Description: Results incorrect for string expression in where clause. create table TEST (ID BIGINT PRIMARY KEY, FIRST_NAME VARCHAR,LAST_NAME VARCHAR); upsert into TEST (ID, FIRST_NAME, LAST_NAME) values (1,'Joe','Smith'); /* incorrectly returns 0 */ select count(*) from TEST where lower(FIRST_NAME || ' ' || LAST_NAME) = 'joe smith'; /* incorrectly returns no rows */ select * from TEST where lower(FIRST_NAME || ' ' || LAST_NAME) = 'joe smith'; /* correctly returns 1 */ select count(*) from TEST where lower(FIRST_NAME || ' ' ) = 'joe '; select count(*) from TEST where lower(' ' || LAST_NAME) = ' smith'; select count(*) from TEST where lower(FIRST_NAME || ' ' || ' ') = 'joe 0'; /* correctly returns 'joe smith' */ select LOWER(FIRST_NAME||' '||LAST_NAME) from TEST; was: Results incorrect for string expression in where clause. create table TEST (ID BIGINT PRIMARY KEY, FIRST_NAME VARCHAR,LAST_NAME VARCHAR); upsert into TEST (ID, FIRST_NAME, LAST_NAME) values (1,'Joe','Smith'); /* incorrectly returns 0 */ select count(*) from TEST where lower(FIRST_NAME || ' ' || LAST_NAME) = 'joe smith'; /* incorrectly returns no rows */ select * from TEST where lower(FIRST_NAME || ' ' || LAST_NAME) = 'joe smith'; /* correctly returns 1 */ select count(*) from TEST where lower(FIRST_NAME || ' ' ) = 'joe '; select count(*) from TEST where lower(' ' || LAST_NAME) = ' smith'; select count(*) from TEST where lower(FIRST_NAME || ' ' || ' ') = 'joe '; /* correctly returns 'joe smith' */ select LOWER(FIRST_NAME||' '||LAST_NAME) from TEST; > WHERE condition with string expression returns incorrect results > ---------------------------------------------------------------- > > Key: PHOENIX-2170 > URL: https://issues.apache.org/jira/browse/PHOENIX-2170 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.5.0 > Reporter: Dan Meany > Original Estimate: 504h > Remaining Estimate: 504h > > Results incorrect for string expression in where clause. > create table TEST (ID BIGINT PRIMARY KEY, FIRST_NAME VARCHAR,LAST_NAME > VARCHAR); > upsert into TEST (ID, FIRST_NAME, LAST_NAME) values (1,'Joe','Smith'); > /* incorrectly returns 0 */ > select count(*) from TEST where lower(FIRST_NAME || ' ' || LAST_NAME) = 'joe > smith'; > /* incorrectly returns no rows */ > select * from TEST where lower(FIRST_NAME || ' ' || LAST_NAME) = 'joe smith'; > /* correctly returns 1 */ > select count(*) from TEST where lower(FIRST_NAME || ' ' ) = 'joe '; > select count(*) from TEST where lower(' ' || LAST_NAME) = ' smith'; > select count(*) from TEST where lower(FIRST_NAME || ' ' || ' ') = 'joe 0'; > /* correctly returns 'joe smith' */ > select LOWER(FIRST_NAME||' '||LAST_NAME) from TEST; -- This message was sent by Atlassian JIRA (v6.3.4#6332)