[DRILL-2328] Concat operator returns wrong result when one of the operands is NULL

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 0.8.0
• Component/s: Query Planning & Optimization
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Victoria Markman
• Created: 27/Feb/15 00:39
• Updated: 02/Mar/15 17:39
• Resolved: 28/Feb/15 19:26

Description

Queries below should return NULL:

0: jdbc:drill:schema=dfs> select cast(null as varchar(10)) || '--' from t1;
+------------+
|   EXPR$0   |
+------------+
| --         |
| --         |
| --         |
| --         |
| --         |
| --         |
| --         |
| --         |
| --         |
| --         |
+------------+
10 rows selected (0.09 seconds)
0: jdbc:drill:schema=dfs> select a1 || '--' from t1 where a1 is null;
+------------+
|   EXPR$0   |
+------------+
| --         |
+------------+
1 row selected (0.105 seconds)

Looks harmless at first, but a very common pattern in many customer queries will be broken: grouping by using '||' as following:

select
        cast(extract(day from c_timestamp) as varchar(10)) || '-' || cast(extract(month from c_timestamp) as varchar(10)) || '-' || cast(extract(year from c_timestamp) as varchar(10)),
        sum(c_integer)  as sum1
from
        alltypes_with_nulls
group by
        cast(extract(day from c_timestamp) as varchar(10)) || '-' || cast(extract(month from c_timestamp) as varchar(10)) || '-' || cast(extract(year from c_timestamp) as varchar(10))
order by
        cast(extract(day from c_timestamp) as varchar(10)) || '-' || cast(extract(month from c_timestamp) as varchar(10)) || '-' || cast(extract(year from c_timestamp) as varchar(10))
;