liushuo created PHOENIX-5413: -------------------------------- Summary: 'case when' return a wrong result when working on a union all Subquery Key: PHOENIX-5413 URL: https://issues.apache.org/jira/browse/PHOENIX-5413 Project: Phoenix Issue Type: Bug Affects Versions: 4.14.1 Reporter: liushuo Attachments: image-2019-07-25-16-32-58-194.png
{code:java} create table user_props (csid varchar primary key, dim varchar, dvalue varchar); UPSERT INTO user_props (csid,dim,dvalue) VALUES('a','ppl_peopleMainsiteCenter','4') ; UPSERT INTO user_props (csid,dim,dvalue) VALUES('b','ppl_peopleMainsiteCenter','4') ; UPSERT INTO user_props (csid,dim,dvalue) VALUES('c','ppl_peopleMainsiteCenter','4') ; create table id_cs1_mapping (id varchar primary key,cs1 varchar); UPSERT INTO id_cs1_mapping (id,cs1) VALUES('a','1111'); UPSERT INTO id_cs1_mapping (id,cs1) VALUES('b','2222'); UPSERT INTO id_cs1_mapping (id,cs1) VALUES('c','3333');{code} sql: {code:java} SELECT csid, (case WHEN dim='ppl_peopleMainsiteCenter' THEN dvalue ELSE NULL end) ppl_peopleMainsiteCenter FROM ( SELECT csid, dim, dvalue FROM user_props UNION all SELECT id csid, 'cs1' dim, cs1 dvalue FROM id_cs1_mapping ) t {code} result: ||csid||ppl_peopleMainsiteCenter|| |a|null| |b|null| |c|null| |a|null| |b|null| |c|null| but, i expect ||csid||ppl_peopleMainsiteCenter|| |a|null| |b|null| |c|null| |a|4| |b|4| |c|4| debug code, i found that {code:java} case WHEN dim='ppl_peopleMainsiteCenter' THEN dvalue ELSE NULL end{code} is compiled {code:java} case WHEN fase THEN dvalue ELSE NULL end{code} I think the error result due to 'ComparisonExpression.java' line : 248, which lhsExpr is instance of projectedColumExpression so that the result of ' if ' is true。 maybe, add the condition ' lhsExprDataType.isFixedWidth() '. so, i get the expected result. !image-2019-07-25-16-32-58-194.png! -- This message was sent by Atlassian JIRA (v7.6.14#76016)