[PHOENIX-5411] Incorrect result is returned when using sum function with case when statement

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.15.0, 5.1.0
• Assignee: Toshihiro Suzuki
• Reporter: Toshihiro Suzuki
• Created: 24/Jul/19 14:35
• Updated: 21/Dec/19 00:53
• Resolved: 26/Jul/19 20:58

Description

In the following case, incorrect result is returned:

0: jdbc:phoenix:> create table tbl (id varchar primary key, col1 varchar, col2 integer);
No rows affected (0.86 seconds)
0: jdbc:phoenix:> upsert into tbl values('id1', 'aaa', 2);
1 row affected (0.078 seconds)
0: jdbc:phoenix:> upsert into tbl values('id2', null, 1);
1 row affected (0.008 seconds)
0: jdbc:phoenix:> select sum(case when col1 is not null then col2 else 0 end), sum(case when col1 is null then col2 else 0 end) from tbl;
+-------------------------------------------------------+-------------------------------------------------------+
| SUM(CASE WHEN COL1 IS NOT NULL THEN COL2 ELSE 0 END)  | SUM(CASE WHEN COL1 IS NOT NULL THEN COL2 ELSE 0 END)  |
+-------------------------------------------------------+-------------------------------------------------------+
| 2                                                     | 2                                                     |
+-------------------------------------------------------+-------------------------------------------------------+
1 row selected (0.03 seconds)

The correct result is (2, 1), but (2, 2) is returned.