question Hive 3.1.4 Multiple rows from COUNT(*) query in Support Questions

question Hive 3.1.4 Multiple rows from COUNT(*) query in Support Questions https://community.cloudera.com/t5/Support-Questions/Hive-3-1-4-Multiple-rows-from-COUNT-query/m-p/313875#M225777 Hi,on Hive 3.1.4 we have a COUNT(*) query which returns more than one rows, instead of exactly one.I created this test table:CREATE TABLE test_case ( cod_pers STRING, cod_address STRING, PRIMARY KEY (cod_pers) DISABLE NOVALIDATE ) PARTITIONED BY (num_snapshot BIGINT) ;and populated it with more than 500,000 rows in 3 partitions.To find how many people changed address, I wrote this query:WITH data1 AS ( -- eliminate duplicates select t.cod_pers, t.cod_address, count(*) as num_address from test_case as t group by t.cod_pers, t.cod_address ), data2 AS ( -- find changes per person select s.cod_pers, count(*) as num_changes from data1 s group by s.cod_pers having count(*)>1 ) select count(*) as num_all_changes from data2 as gg ;Instead of obtaining a single row with the total number, the query returns 4 rows :+------------------+ | num_all_changes | +------------------+ | 63 | | 58 | | 64 | | 59 | +------------------+ 4 rows selected (1.252 seconds)4 is also the number of reducers used by the query.If I add a "LIMIT 1" clause, the query works as expected, and a final reducer is added in the query execution. The same happens adding "GROUP BY 1" clause. I tried to COMPACT the table and have the statistics recalculated, but nothing changed.I know I can rewrite the query, but I'm not looking for a work-aroud: I wonder if it's a known bug of CBO and if someone else experimented the same behavior (and also if a patch is available). Thanks in advance Wed, 31 Mar 2021 08:17:55 GMT giovannimori 2021-03-31T08:17:55Z