Hi @Tim Armstrong    Thanks again for your reply.  I submitted https://issues.apache.org/jira/browse/IMPALA-8664 . Hope it makes sense.   After reading your response, I thought DECIMAL would be the issue, but it seems not. Using Impala 2.11 (CDH 5.14.4), no DECIMAL, no CAST, but still fails:   0: jdbc:hive2://test> SELECT (-1 * CASE WHEN SUM(1) IS NULL THEN NULL WHEN COALESCE(NULL, 0) = 0 THEN NULL ELSE 0 END) FROM (SELECT 1 as ytd_id, 2 as derv_alw_amt) dummy_t; Error: AnalysisException: Arithmetic operation requires numeric operands: (-1 * CASE WHEN sum(1) IS NULL THEN NULL ELSE NULL END) (state=HY000,code=0) No COALESCE, works!     0: jdbc:hive2://test> SELECT (-1 * CASE WHEN SUM(1) IS NULL THEN NULL WHEN 1 = 0 THEN NULL ELSE 0 END) FROM (SELECT 1 as ytd_id, 2 as derv_alw_amt) dummy_t; 0     BTW, because of the inconsistency I mentioned in the first comment, faliing below MAKES SENSE (because CASE returns NULL):   0: jdbc:hive2://test> SELECT (-1 * CASE WHEN SUM(1) IS NULL THEN NULL WHEN 0 = 0 THEN NULL ELSE 0 END) FROM (SELECT 1 as ytd_id, 2 as derv_alw_amt) dummy_t; Error: AnalysisException: Arithmetic operation requires numeric operands: (-1 * CASE WHEN sum(1) IS NULL THEN NULL ELSE NULL END) (state=HY000,code=0)   HOWEVER, removing SUM returns different result:   0: jdbc:hive2://test> SELECT (-1 * CASE WHEN COALESCE(NULL, 0) = 0 THEN NULL ELSE 0 END) FROM (SELECT 1 as ytd_id, 2 as derv_alw_amt) dummy_t; null     So this is a bit too confusing for me, and I think this would be an Impala bug.   ... View more

I mean, the output/result of the following query is different by Impala version: SELECT (-1 * CASE WHEN CAST( SUM( CASE when dummy_t.col1 = 1 then dummy_t.col2 else 0 END ) AS DECIMAL(32, 2) ) IS NULL THEN NULL WHEN COALESCE(CAST(NULL AS DOUBLE), 0) = 0 THEN NULL ELSE CAST( CASE WHEN CAST(NULL AS DOUBLE) = 0 THEN NULL ELSE ( CAST( SUM( CASE when dummy_t.col1 = 1 then dummy_t.col2 else 0 END ) AS DECIMAL(32, 2) ) / CAST(NULL AS DOUBLE) ) END AS DOUBLE ) END) FROM (SELECT 1 as col1, 2 as col2) dummy_t;   ... View more

Thank you for your reply, @Tim Armstrong ! If Impala doesn't throw Exception by CASE, that would be very nice.   But now I found more interesting issue 🙂   Old Impala 2.5 => No error [root@quickstart ~]# beeline -u "jdbc:hive2://`hostname -f`:21050/;auth=noSasl" 2019-06-12 07:27:40,131 WARN [main] mapreduce.TableMapReduceUtil: The hbase-prefix-tree module jar containing PrefixTreeCodec is not present. Continuing without it. scan complete in 2ms Connecting to jdbc:hive2://quickstart.cloudera:21050/;auth=noSasl Connected to: Impala (version 2.5.0-cdh5.7.0) Driver: Hive JDBC (version 1.1.0-cdh5.7.0) Transaction isolation: TRANSACTION_REPEATABLE_READ Beeline version 1.1.0-cdh5.7.0 by Apache Hive 0: jdbc:hive2://quickstart.cloudera:21050/> SELECT (-1 * CASE WHEN CAST( 0: jdbc:hive2://quickstart.cloudera:21050/> SUM( 0: jdbc:hive2://quickstart.cloudera:21050/> CASE when dummy_t.col1 = 1 then dummy_t.col2 else 0 END 0: jdbc:hive2://quickstart.cloudera:21050/> ) AS DECIMAL(32, 2) 0: jdbc:hive2://quickstart.cloudera:21050/> ) IS NULL THEN NULL WHEN COALESCE(CAST(NULL AS DOUBLE), 0) = 0 THEN NULL ELSE CAST( 0: jdbc:hive2://quickstart.cloudera:21050/> CASE WHEN CAST(NULL AS DOUBLE) = 0 THEN NULL ELSE ( 0: jdbc:hive2://quickstart.cloudera:21050/> CAST( 0: jdbc:hive2://quickstart.cloudera:21050/> SUM( 0: jdbc:hive2://quickstart.cloudera:21050/> CASE when dummy_t.col1 = 1 then dummy_t.col2 else 0 END 0: jdbc:hive2://quickstart.cloudera:21050/> ) AS DECIMAL(32, 2) 0: jdbc:hive2://quickstart.cloudera:21050/> ) / CAST(NULL AS DOUBLE) 0: jdbc:hive2://quickstart.cloudera:21050/> ) END AS DOUBLE 0: jdbc:hive2://quickstart.cloudera:21050/> ) END) FROM (SELECT 1 as col1, 2 as col2) dummy_t; null +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | (-1 * 1 * case when cast(sum(case when dummy_t.col1 = 1 then dummy_t.col2 else 0 end) as decimal(32,2)) is null then null when coalesce(cast(null as double), 0) = 0 then null else cast(case when cast(null as double) = 0 then null else (cast(sum(case when dummy_t.col1 = 1 then dummy_t.col2 else 0 end) as decimal(32,2)) / cast(null as double)) end as double) end) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ No rows selected (0.093 seconds) 0: jdbc:hive2://quickstart.cloudera:21050/> Newer Impala 2.11 and 3.0 doesn't like above: [root@test /]# beeline -u "jdbc:hive2://`hostname -f`:21050/;auth=noSasl" scan complete in 2ms Connecting to jdbc:hive2://test.impala.com:21050/;auth=noSasl Connected to: Impala (version 2.11.0-cdh5.14.4) Driver: Hive JDBC (version 1.1.0-cdh5.14.4) Transaction isolation: TRANSACTION_REPEATABLE_READ Beeline version 1.1.0-cdh5.14.4 by Apache Hive 0: jdbc:hive2://hadoop-node-01.docker.infra.a> SELECT (-1 * CASE WHEN CAST( . . . . . . . . . . . . . . . . . . . . . . .> SUM( . . . . . . . . . . . . . . . . . . . . . . .> CASE when dummy_t.col1 = 1 then dummy_t.col2 else 0 END . . . . . . . . . . . . . . . . . . . . . . .> ) AS DECIMAL(32, 2) . . . . . . . . . . . . . . . . . . . . . . .> ) IS NULL THEN NULL WHEN COALESCE(CAST(NULL AS DOUBLE), 0) = 0 THEN NULL ELSE CAST( . . . . . . . . . . . . . . . . . . . . . . .> CASE WHEN CAST(NULL AS DOUBLE) = 0 THEN NULL ELSE ( . . . . . . . . . . . . . . . . . . . . . . .> CAST( . . . . . . . . . . . . . . . . . . . . . . .> SUM( . . . . . . . . . . . . . . . . . . . . . . .> CASE when dummy_t.col1 = 1 then dummy_t.col2 else 0 END . . . . . . . . . . . . . . . . . . . . . . .> ) AS DECIMAL(32, 2) . . . . . . . . . . . . . . . . . . . . . . .> ) / CAST(NULL AS DOUBLE) . . . . . . . . . . . . . . . . . . . . . . .> ) END AS DOUBLE . . . . . . . . . . . . . . . . . . . . . . .> ) END) FROM (SELECT 1 as col1, 2 as col2) dummy_t; Error: AnalysisException: Arithmetic operation requires numeric operands: (-1 * CASE WHEN CAST(sum(CASE WHEN dummy_t.col1 = 1 THEN dummy_t.col2 ELSE 0 END) AS DECIMAL(32,2)) IS NULL THEN NULL ELSE NULL END) (state=HY000,code=0) Did I find a bug this time? 😉 ... View more