CODEWITHSUNDEEP
clickhouse
Morten V. Gade
Morten V. Gade

Reputation: 89

Clickhouse: Difference in row results between 2 versions

In version 19.13.3.26 the following query returns 1 row:

select   -1 as brandidTotal, toDecimal64(Sum(CostpriceSEK),2) as costprice
  from mytable
  where Stylenumber = 'a row which does not exist'  
  group by brandidTotal

But in version 22.2.2.1 it returns an empty result (which i can understand, since where does not find any rows)

It seems like the aggregate function SUM has changed behaviour. (if second column is removed, both returns an empty set)

Is it possible to make version 22X handle it like 19x does?

Upvotes: 1

Views: 561

Answers (1)

Denny Crane
Denny Crane

Reputation: 13310

  --empty_result_for_aggregation_by_constant_keys_on_empty_set 
            Return empty result when aggregating by constant keys on empty set.


select -1 x, count() from (select 1 yyy where 0) group by x;

0 rows in set. Elapsed: 0.002 sec.


set empty_result_for_aggregation_by_constant_keys_on_empty_set=0;

select -1 x, count() from (select 1 yyy where 0) group by x;

┌──x─┬─count()─┐
│ -1 │       0 │
└────┴─────────┘

to enable it by default for all

cat /etc/clickhouse-server/users.d/const_aggr_emp.xml
<?xml version="1.0" ?>
<yandex>
    <profiles>
        <default>
            <empty_result_for_aggregation_by_constant_keys_on_empty_set>0</empty_result_for_aggregation_by_constant_keys_on_empty_set>
        </default>
    </profiles>
</yandex>

Upvotes: 1

Related Questions