[
https://issues.apache.org/jira/browse/PHOENIX-5413?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
liushuo updated PHOENIX-5413:
-----------------------------
Description:
{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}
||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!
was:
{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!
> '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
> Priority: Major
> 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}
> ||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)
