Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Column is not under aggregate function and not in GROUP BY in mat view in 23.5 #50928

Open
alexeyrusnak opened this issue Jun 13, 2023 · 12 comments
Open

Column is not under aggregate function and not in GROUP BY in mat view in 23.5 #50928

alexeyrusnak opened this issue Jun 13, 2023 · 12 comments
Labels
bug Confirmed user-visible misbehaviour in official release v23.5-affected v23.8-affected

Comments

@alexeyrusnak
Copy link

Hi

I have issue when upgrading from 23.4. to 23.5

The error is

DB::Exception: Column `post_nat_source_ipv4` is not under aggregate function and not in GROUP BY

On previous versions the code below works fine. Please note that column post_nat_source_ipv4 is in GROUP BY

create database if not exists qoestor engine = Ordinary;

use qoestor;


create table if not exists fullflow
(
    octet_delta_count           UInt64,
    packet_delta_count          UInt64,
    protocol_identifier         UInt8,
    ip_class_of_service         UInt8,
    source_port                 UInt16,
    source_ipv4                 String,
    source_ipv6                 String,
    destination_port            UInt16,
    destination_ipv4            String,
    destination_ipv6            String,
    bgp_source_as_number        UInt32,
    bgp_destination_as_number   UInt32,
    flow_start_date             Date,
    flow_start_time             DateTime,
    flow_start_millisecond      UInt64,
    flow_end_date               Date,
    flow_end_time               DateTime,
    flow_end_millisecond        UInt64,
    input_snmp                  UInt16,
    output_snmp                 UInt16,
    ip_version                  UInt8,
    session_id                  UInt64,
    host                        String,
    protocol_code               UInt16,
    login                       String,
    post_nat_source_ipv4        String,
    post_nat_source_port        UInt16,
    frgmt_delta_packs           UInt16,
    repeat_delta_pack           UInt16,
    packet_deliver_time         UInt32,
    dpi_id                      UInt8,
    bridge_vchannel_num         UInt16

) engine = MergeTree() partition by toStartOfHour(flow_start_time) order by (
                                                                              flow_start_date,
                                                                              flow_start_time,
                                                                              flow_start_millisecond,
                                                                              session_id,
                                                                              host,
                                                                              source_ipv4,
                                                                              destination_ipv4,
                                                                              source_ipv6,
                                                                              destination_ipv6,
                                                                              dpi_id,
                                                                              bridge_vchannel_num
                                                                              );

alter table fullflow add column if not exists from_subscriber UInt8,
                     add column if not exists source_ip String alias (source_ipv6 == '::' ? source_ipv4 : source_ipv6),
                     add column if not exists destination_ip String alias (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6),
                     add column if not exists source_asnum UInt32 alias (from_subscriber == 1 ? bgp_source_as_number : bgp_destination_as_number),
                     add column if not exists destination_asnum UInt32 alias (from_subscriber == 0 ? bgp_source_as_number : bgp_destination_as_number),
                     add column if not exists source_astitle String alias (dictHas('asnum_local_dic', toUInt64(source_asnum)) == 1 ? dictGetString('asnum_local_dic', 'description', toUInt64(source_asnum)) : dictGetString('asnum_dic', 'description', toUInt64(source_asnum))),
                     add column if not exists destination_astitle String alias (dictHas('asnum_local_dic', toUInt64(destination_asnum)) == 1 ? dictGetString('asnum_local_dic', 'description', toUInt64(destination_asnum)) : dictGetString('asnum_dic', 'description', toUInt64(destination_asnum))),
                     add column if not exists is_subscriber UInt8,
                     add column if not exists is_excluded_subscriber UInt8,
                     add column if not exists subscriber String alias (from_subscriber == 1 ? (source_ipv6 == '::' ? source_ipv4 : source_ipv6) : (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6)),
                     add column if not exists subscriber_login String alias (empty(login) ? subscriber : login),
                     add column if not exists host_ip String alias (from_subscriber == 0 ? (source_ipv6 == '::' ? source_ipv4 : source_ipv6) : (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6)),
                     add column if not exists subscriber_port UInt16 alias (from_subscriber == 1 ? source_port : destination_port),
                     add column if not exists host_port UInt16 alias (from_subscriber == 0 ? source_port : destination_port),
                     add column if not exists bridge_vchannel_num UInt16 after dpi_id, modify order by (flow_start_date, flow_start_time, flow_start_millisecond, session_id, host, source_ipv4, destination_ipv4, source_ipv6, destination_ipv6, dpi_id, bridge_vchannel_num);

alter table fullflow modify column if exists from_subscriber UInt8 default (input_snmp == 1 and output_snmp == 2 ? 1 : 0);

alter table fullflow modify column if exists is_subscriber UInt8 default 1;

alter table fullflow modify column if exists is_excluded_subscriber UInt8 default 0;



create table if not exists `.inner.fullflow_agg`
(
    date                                            Date,
    hour                                            DateTime,
    time                                            DateTime,
    from_subscriber                                 UInt8,
    source_ip                                       String,
    destination_ip                                  String,
    source_asnum                                    UInt32,
    destination_asnum                               UInt32,
    source_astitle                                  String,
    destination_astitle                             String,
    is_subscriber                                   UInt8,
    is_excluded_subscriber                          UInt8 default 0,
    subscriber                                      String,
    subscriber_login                                String,
    login                                           String,
    host_ip                                         String,
    host                                            String,
    protocol_code                                   UInt16,
    protocol_identifier                             UInt8,
    post_nat_source_ipv4                            String,
    post_nat_source_port                            UInt16,
    dpi_id                                          UInt8,
    bridge_vchannel_num                             UInt16,
    ip_class_of_service                             UInt8,
    flow_start_millisecond_min                      AggregateFunction(min, UInt64),
    flow_start_millisecond_from_subscriber_min_if   AggregateFunction(minIf, UInt64, UInt8),
    flow_start_millisecond_to_subscriber_min_if     AggregateFunction(minIf, UInt64, UInt8),
    flow_end_millisecond_max                        AggregateFunction(max, UInt64),
    flow_end_millisecond_from_subscriber_max_if     AggregateFunction(maxIf, UInt64, UInt8),
    flow_end_millisecond_to_subscriber_max_if       AggregateFunction(maxIf, UInt64, UInt8),
    total_count                                     AggregateFunction(count),
    session_id_uniq                                 AggregateFunction(uniq, UInt64),
    octet_delta_count_from_subscriber_sum_if        AggregateFunction(sumIf, UInt64, UInt8),
    octet_delta_count_to_subscriber_sum_if          AggregateFunction(sumIf, UInt64, UInt8),
    octet_delta_count_sum                           AggregateFunction(sum, UInt64),
    packet_delta_count_from_subscriber_sum_if       AggregateFunction(sumIf, UInt64, UInt8),
    packet_delta_count_to_subscriber_sum_if         AggregateFunction(sumIf, UInt64, UInt8),
    packet_delta_count_sum                          AggregateFunction(sum, UInt64),
    ip_class_of_service_any                         AggregateFunction(anyHeavy, UInt8),
    rtt_from_subscriber_avg_if                      AggregateFunction(avgIf, UInt32, UInt8),
    rtt_to_subscriber_avg_if                        AggregateFunction(avgIf, UInt32, UInt8),
    rtt_avg_if                                      AggregateFunction(avgIf, UInt32, UInt8),
    rtt_median_if                                   AggregateFunction(quantileIf, UInt32, UInt8),
    rtt_from_subscriber_any_heavy_if                AggregateFunction(anyHeavyIf, UInt32, UInt8),
    rtt_to_subscriber_any_heavy_if                  AggregateFunction(anyHeavyIf, UInt32, UInt8),
    rtt_any_heavy_if                                AggregateFunction(anyHeavyIf, UInt32, UInt8),
    retransmits_from_subscriber_avg_if              AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_to_subscriber_avg_if                AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_avg_if                              AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_rate_from_subscriber_avg_if         AggregateFunction(avgIf, Float64, UInt8),
    retransmits_rate_to_subscriber_avg_if           AggregateFunction(avgIf, Float64, UInt8),
    retransmits_rate_avg_if                         AggregateFunction(avgIf, Float64, UInt8),
    fragments_avg_if                                AggregateFunction(avgIf, UInt16, UInt8)
) engine = AggregatingMergeTree() partition by toStartOfHour(`time`) order by (`date`, `hour`, `time`,
                                                                            source_asnum,
                                                                            destination_asnum,
                                                                            source_astitle,
                                                                            destination_astitle,
                                                                            from_subscriber,
                                                                            is_subscriber,
                                                                            source_ip,
                                                                            destination_ip,
                                                                            subscriber,
                                                                            subscriber_login,
                                                                            login,
                                                                            host_ip,
                                                                            host,
                                                                            protocol_identifier,
                                                                            protocol_code,
                                                                            post_nat_source_ipv4,
                                                                            post_nat_source_port,
                                                                            dpi_id,
                                                                            bridge_vchannel_num,
                                                                            ip_class_of_service);

detach table if exists fullflow_agg;

alter table `.inner.fullflow_agg` add column if not exists is_excluded_subscriber UInt8 default 0 after is_subscriber,
                                  add column if not exists bridge_vchannel_num UInt16 after dpi_id,
                                  add column if not exists ip_class_of_service UInt8 after bridge_vchannel_num,
                                  modify order by (`date`, `hour`, `time`, source_asnum, destination_asnum, source_astitle, destination_astitle, from_subscriber, is_subscriber, source_ip, destination_ip, subscriber, subscriber_login, login, host_ip, host, protocol_identifier, protocol_code, post_nat_source_ipv4, post_nat_source_port, dpi_id, bridge_vchannel_num, ip_class_of_service);

attach materialized view if not exists fullflow_agg
  engine = AggregatingMergeTree() partition by toStartOfHour(`time`) order by (`date`, `hour`, `time`,
                                                                            source_asnum,
                                                                            destination_asnum,
                                                                            source_astitle,
                                                                            destination_astitle,
                                                                            from_subscriber,
                                                                            is_subscriber,
                                                                            source_ip,
                                                                            destination_ip,
                                                                            subscriber,
                                                                            subscriber_login,
                                                                            login,
                                                                            host_ip,
                                                                            host,
                                                                            protocol_identifier,
                                                                            protocol_code,
                                                                            post_nat_source_ipv4,
                                                                            post_nat_source_port,
                                                                            dpi_id,
                                                                            bridge_vchannel_num,
                                                                            ip_class_of_service) as
    select

      flow_start_date as `date`,
      toStartOfHour(flow_start_time) as `hour`,
      toStartOfInterval(flow_start_time, toIntervalMinute(toUInt8(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_GROUP_TIME_INTERVAL'),'15')))) as `time`,
      from_subscriber,
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),0) ? source_ip : '' as source_ip, --0x1
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),1) ? destination_ip : '' as destination_ip, --0x2
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),2) ? source_asnum : 0 as source_asnum, --0x4
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),3) ? destination_asnum : 0 as destination_asnum, --0x8
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),4) ? source_astitle : '' as source_astitle, --0x10
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),5) ? destination_astitle : '' as destination_astitle, --0x20
      is_subscriber,
      is_excluded_subscriber,
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),6) ? subscriber : '' as subscriber, --0x40
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),7) ? subscriber_login : '' as subscriber_login, --0x80
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),8) ? login : '' as login, --0x100
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),9) ? host_ip : '' as host_ip, --0x200
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),10) ? host : '' as host, --0x400
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),11) ? protocol_code : 0 as protocol_code, --0x800
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),12) ? protocol_identifier : 0 as protocol_identifier, --0x1000
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),13) ? post_nat_source_ipv4 : '' as post_nat_source_ipv4, --0x2000
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),14) ? post_nat_source_port : 0 as post_nat_source_port, --0x4000
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),15) ? dpi_id : 0 as dpi_id, --0x8000
      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),16) ? bridge_vchannel_num : 0 as bridge_vchannel_num, --0x10000

      bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value', tuple('FULLFLOW_AGG_LOG_FIELDS_TO_SAVE_BITMASK'),'0'),'0x','')))),17) ? ip_class_of_service : 0 as ip_class_of_service, --0x20000

      minState(flow_start_millisecond) flow_start_millisecond_min,
      minIfState(flow_start_millisecond, from_subscriber == 1) flow_start_millisecond_from_subscriber_min_if,
      minIfState(flow_start_millisecond, from_subscriber == 0) flow_start_millisecond_to_subscriber_min_if,

      maxState(flow_end_millisecond) flow_end_millisecond_max,
      maxIfState(flow_end_millisecond, from_subscriber == 1) flow_end_millisecond_from_subscriber_max_if,
      maxIfState(flow_end_millisecond, from_subscriber == 0) flow_end_millisecond_to_subscriber_max_if,

      countState() total_count,
      uniqState(session_id) session_id_uniq,

      sumIfState(octet_delta_count, from_subscriber == 1) octet_delta_count_from_subscriber_sum_if,
      sumIfState(octet_delta_count, from_subscriber == 0) octet_delta_count_to_subscriber_sum_if,
      sumState(octet_delta_count) octet_delta_count_sum,

      sumIfState(packet_delta_count, from_subscriber == 1) packet_delta_count_from_subscriber_sum_if,
      sumIfState(packet_delta_count, from_subscriber == 0) packet_delta_count_to_subscriber_sum_if,
      sumState(packet_delta_count) packet_delta_count_sum,

      anyHeavyState(ip_class_of_service) ip_class_of_service_any,

      avgIfState(packet_deliver_time, from_subscriber == 1 and packet_deliver_time <> 0) rtt_from_subscriber_avg_if,
      avgIfState(packet_deliver_time, from_subscriber == 0 and packet_deliver_time <> 0) rtt_to_subscriber_avg_if,
      avgIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_avg_if,
      medianIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_median_if,

      anyHeavyIfState(packet_deliver_time, from_subscriber == 1 and packet_deliver_time <> 0) rtt_from_subscriber_any_heavy_if,
      anyHeavyIfState(packet_deliver_time, from_subscriber == 0 and packet_deliver_time <> 0) rtt_to_subscriber_any_heavy_if,
      anyHeavyIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_any_heavy_if,

      avgIfState(repeat_delta_pack, from_subscriber == 1 and repeat_delta_pack <> 0) retransmits_from_subscriber_avg_if,
      avgIfState(repeat_delta_pack, from_subscriber == 0 and repeat_delta_pack <> 0) retransmits_to_subscriber_avg_if,
      avgIfState(repeat_delta_pack, repeat_delta_pack <> 0) retransmits_avg_if,

      avgIfState(repeat_delta_pack / packet_delta_count, from_subscriber == 1) retransmits_rate_from_subscriber_avg_if,
      avgIfState(repeat_delta_pack / packet_delta_count, from_subscriber == 0) retransmits_rate_to_subscriber_avg_if,
      avgIfState(repeat_delta_pack / packet_delta_count, 1) retransmits_rate_avg_if,

      avgIfState(frgmt_delta_packs, frgmt_delta_packs <> 0) fragments_avg_if
    from fullflow
      where is_subscriber = 1 and is_excluded_subscriber = 0
      group by `date`, `hour`, `time`,
        source_asnum,
        destination_asnum,
        source_astitle,
        destination_astitle,
        from_subscriber,
        is_subscriber,
        is_excluded_subscriber,
        source_ip,
        destination_ip,
        subscriber,
        subscriber_login,
        login,
        host_ip,
        host,
        protocol_identifier,
        protocol_code,
        post_nat_source_ipv4,
        post_nat_source_port,
        dpi_id,
        bridge_vchannel_num,
        ip_class_of_service
      order by `time` asc;
@alexeyrusnak alexeyrusnak added the potential bug To be reviewed by developers and confirmed/rejected. label Jun 13, 2023
@den-crane
Copy link
Contributor

https://fiddle.clickhouse.com/b8bd6a8a-2ed4-4c8b-90e1-b4244586a86c

Dictionary (settings_dic) not found: While processing if(bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value',

@alexeyrusnak
Copy link
Author

https://fiddle.clickhouse.com/b8bd6a8a-2ed4-4c8b-90e1-b4244586a86c

Dictionary (settings_dic) not found: While processing if(bitTest(reinterpretAsInt64(reverse(unhex(replaceOne(dictGetStringOrDefault('settings_dic', 'value',

I have replaced function bitTest with boolean results accordinary settings_dic. Now error is reproduced.

set allow_deprecated_database_ordinary=1;

create database if not exists qoestor engine = Ordinary;

use qoestor;


create table if not exists fullflow
(
    octet_delta_count           UInt64,
    packet_delta_count          UInt64,
    protocol_identifier         UInt8,
    ip_class_of_service         UInt8,
    source_port                 UInt16,
    source_ipv4                 String,
    source_ipv6                 String,
    destination_port            UInt16,
    destination_ipv4            String,
    destination_ipv6            String,
    bgp_source_as_number        UInt32,
    bgp_destination_as_number   UInt32,
    flow_start_date             Date,
    flow_start_time             DateTime,
    flow_start_millisecond      UInt64,
    flow_end_date               Date,
    flow_end_time               DateTime,
    flow_end_millisecond        UInt64,
    input_snmp                  UInt16,
    output_snmp                 UInt16,
    ip_version                  UInt8,
    session_id                  UInt64,
    host                        String,
    protocol_code               UInt16,
    login                       String,
    post_nat_source_ipv4        String,
    post_nat_source_port        UInt16,
    frgmt_delta_packs           UInt16,
    repeat_delta_pack           UInt16,
    packet_deliver_time         UInt32,
    dpi_id                      UInt8,
    bridge_vchannel_num         UInt16

) engine = MergeTree() partition by toStartOfHour(flow_start_time) order by (
                                                                              flow_start_date,
                                                                              flow_start_time,
                                                                              flow_start_millisecond,
                                                                              session_id,
                                                                              host,
                                                                              source_ipv4,
                                                                              destination_ipv4,
                                                                              source_ipv6,
                                                                              destination_ipv6,
                                                                              dpi_id,
                                                                              bridge_vchannel_num
                                                                              );

alter table fullflow add column if not exists from_subscriber UInt8,
                     add column if not exists source_ip String alias (source_ipv6 == '::' ? source_ipv4 : source_ipv6),
                     add column if not exists destination_ip String alias (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6),
                     add column if not exists source_asnum UInt32 alias (from_subscriber == 1 ? bgp_source_as_number : bgp_destination_as_number),
                     add column if not exists destination_asnum UInt32 alias (from_subscriber == 0 ? bgp_source_as_number : bgp_destination_as_number),
                     add column if not exists source_astitle String alias (dictHas('asnum_local_dic', toUInt64(source_asnum)) == 1 ? dictGetString('asnum_local_dic', 'description', toUInt64(source_asnum)) : dictGetString('asnum_dic', 'description', toUInt64(source_asnum))),
                     add column if not exists destination_astitle String alias (dictHas('asnum_local_dic', toUInt64(destination_asnum)) == 1 ? dictGetString('asnum_local_dic', 'description', toUInt64(destination_asnum)) : dictGetString('asnum_dic', 'description', toUInt64(destination_asnum))),
                     add column if not exists is_subscriber UInt8,
                     add column if not exists is_excluded_subscriber UInt8,
                     add column if not exists subscriber String alias (from_subscriber == 1 ? (source_ipv6 == '::' ? source_ipv4 : source_ipv6) : (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6)),
                     add column if not exists subscriber_login String alias (empty(login) ? subscriber : login),
                     add column if not exists host_ip String alias (from_subscriber == 0 ? (source_ipv6 == '::' ? source_ipv4 : source_ipv6) : (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6)),
                     add column if not exists subscriber_port UInt16 alias (from_subscriber == 1 ? source_port : destination_port),
                     add column if not exists host_port UInt16 alias (from_subscriber == 0 ? source_port : destination_port),
                     add column if not exists bridge_vchannel_num UInt16 after dpi_id, modify order by (flow_start_date, flow_start_time, flow_start_millisecond, session_id, host, source_ipv4, destination_ipv4, source_ipv6, destination_ipv6, dpi_id, bridge_vchannel_num);

alter table fullflow modify column if exists from_subscriber UInt8 default (input_snmp == 1 and output_snmp == 2 ? 1 : 0);

alter table fullflow modify column if exists is_subscriber UInt8 default 1;

alter table fullflow modify column if exists is_excluded_subscriber UInt8 default 0;



create table if not exists `.inner.fullflow_agg`
(
    date                                            Date,
    hour                                            DateTime,
    time                                            DateTime,
    from_subscriber                                 UInt8,
    source_ip                                       String,
    destination_ip                                  String,
    source_asnum                                    UInt32,
    destination_asnum                               UInt32,
    source_astitle                                  String,
    destination_astitle                             String,
    is_subscriber                                   UInt8,
    is_excluded_subscriber                          UInt8 default 0,
    subscriber                                      String,
    subscriber_login                                String,
    login                                           String,
    host_ip                                         String,
    host                                            String,
    protocol_code                                   UInt16,
    protocol_identifier                             UInt8,
    post_nat_source_ipv4                            String,
    post_nat_source_port                            UInt16,
    dpi_id                                          UInt8,
    bridge_vchannel_num                             UInt16,
    ip_class_of_service                             UInt8,
    flow_start_millisecond_min                      AggregateFunction(min, UInt64),
    flow_start_millisecond_from_subscriber_min_if   AggregateFunction(minIf, UInt64, UInt8),
    flow_start_millisecond_to_subscriber_min_if     AggregateFunction(minIf, UInt64, UInt8),
    flow_end_millisecond_max                        AggregateFunction(max, UInt64),
    flow_end_millisecond_from_subscriber_max_if     AggregateFunction(maxIf, UInt64, UInt8),
    flow_end_millisecond_to_subscriber_max_if       AggregateFunction(maxIf, UInt64, UInt8),
    total_count                                     AggregateFunction(count),
    session_id_uniq                                 AggregateFunction(uniq, UInt64),
    octet_delta_count_from_subscriber_sum_if        AggregateFunction(sumIf, UInt64, UInt8),
    octet_delta_count_to_subscriber_sum_if          AggregateFunction(sumIf, UInt64, UInt8),
    octet_delta_count_sum                           AggregateFunction(sum, UInt64),
    packet_delta_count_from_subscriber_sum_if       AggregateFunction(sumIf, UInt64, UInt8),
    packet_delta_count_to_subscriber_sum_if         AggregateFunction(sumIf, UInt64, UInt8),
    packet_delta_count_sum                          AggregateFunction(sum, UInt64),
    ip_class_of_service_any                         AggregateFunction(anyHeavy, UInt8),
    rtt_from_subscriber_avg_if                      AggregateFunction(avgIf, UInt32, UInt8),
    rtt_to_subscriber_avg_if                        AggregateFunction(avgIf, UInt32, UInt8),
    rtt_avg_if                                      AggregateFunction(avgIf, UInt32, UInt8),
    rtt_median_if                                   AggregateFunction(quantileIf, UInt32, UInt8),
    rtt_from_subscriber_any_heavy_if                AggregateFunction(anyHeavyIf, UInt32, UInt8),
    rtt_to_subscriber_any_heavy_if                  AggregateFunction(anyHeavyIf, UInt32, UInt8),
    rtt_any_heavy_if                                AggregateFunction(anyHeavyIf, UInt32, UInt8),
    retransmits_from_subscriber_avg_if              AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_to_subscriber_avg_if                AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_avg_if                              AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_rate_from_subscriber_avg_if         AggregateFunction(avgIf, Float64, UInt8),
    retransmits_rate_to_subscriber_avg_if           AggregateFunction(avgIf, Float64, UInt8),
    retransmits_rate_avg_if                         AggregateFunction(avgIf, Float64, UInt8),
    fragments_avg_if                                AggregateFunction(avgIf, UInt16, UInt8)
) engine = AggregatingMergeTree() partition by toStartOfHour(`time`) order by (`date`, `hour`, `time`,
                                                                            source_asnum,
                                                                            destination_asnum,
                                                                            source_astitle,
                                                                            destination_astitle,
                                                                            from_subscriber,
                                                                            is_subscriber,
                                                                            source_ip,
                                                                            destination_ip,
                                                                            subscriber,
                                                                            subscriber_login,
                                                                            login,
                                                                            host_ip,
                                                                            host,
                                                                            protocol_identifier,
                                                                            protocol_code,
                                                                            post_nat_source_ipv4,
                                                                            post_nat_source_port,
                                                                            dpi_id,
                                                                            bridge_vchannel_num,
                                                                            ip_class_of_service);

detach table if exists fullflow_agg;

alter table `.inner.fullflow_agg` add column if not exists is_excluded_subscriber UInt8 default 0 after is_subscriber,
                                  add column if not exists bridge_vchannel_num UInt16 after dpi_id,
                                  add column if not exists ip_class_of_service UInt8 after bridge_vchannel_num,
                                  modify order by (`date`, `hour`, `time`, source_asnum, destination_asnum, source_astitle, destination_astitle, from_subscriber, is_subscriber, source_ip, destination_ip, subscriber, subscriber_login, login, host_ip, host, protocol_identifier, protocol_code, post_nat_source_ipv4, post_nat_source_port, dpi_id, bridge_vchannel_num, ip_class_of_service);

attach materialized view if not exists fullflow_agg
  engine = AggregatingMergeTree() partition by toStartOfHour(`time`) order by (`date`, `hour`, `time`,
                                                                            source_asnum,
                                                                            destination_asnum,
                                                                            source_astitle,
                                                                            destination_astitle,
                                                                            from_subscriber,
                                                                            is_subscriber,
                                                                            source_ip,
                                                                            destination_ip,
                                                                            subscriber,
                                                                            subscriber_login,
                                                                            login,
                                                                            host_ip,
                                                                            host,
                                                                            protocol_identifier,
                                                                            protocol_code,
                                                                            post_nat_source_ipv4,
                                                                            post_nat_source_port,
                                                                            dpi_id,
                                                                            bridge_vchannel_num,
                                                                            ip_class_of_service) as
    select

      flow_start_date as `date`,
      toStartOfHour(flow_start_time) as `hour`,
      toStartOfInterval(flow_start_time, toIntervalMinute(15)) as `time`,
      from_subscriber,
      true ? source_ip : '' as source_ip, --0x1
      true ? destination_ip : '' as destination_ip, --0x2
      true ? source_asnum : 0 as source_asnum, --0x4
      true ? destination_asnum : 0 as destination_asnum, --0x8
      true ? source_astitle : '' as source_astitle, --0x10
      true ? destination_astitle : '' as destination_astitle, --0x20
      is_subscriber,
      is_excluded_subscriber,
      true ? subscriber : '' as subscriber, --0x40
      true ? subscriber_login : '' as subscriber_login, --0x80
      true ? login : '' as login, --0x100
      true ? host_ip : '' as host_ip, --0x200
      true ? host : '' as host, --0x400
      true ? protocol_code : 0 as protocol_code, --0x800
      true ? protocol_identifier : 0 as protocol_identifier, --0x1000
      false ? post_nat_source_ipv4 : '' as post_nat_source_ipv4, --0x2000
      false ? post_nat_source_port : 0 as post_nat_source_port, --0x4000
      true ? dpi_id : 0 as dpi_id, --0x8000
      true ? bridge_vchannel_num : 0 as bridge_vchannel_num, --0x10000

      true ? ip_class_of_service : 0 as ip_class_of_service, --0x20000

      minState(flow_start_millisecond) flow_start_millisecond_min,
      minIfState(flow_start_millisecond, from_subscriber == 1) flow_start_millisecond_from_subscriber_min_if,
      minIfState(flow_start_millisecond, from_subscriber == 0) flow_start_millisecond_to_subscriber_min_if,

      maxState(flow_end_millisecond) flow_end_millisecond_max,
      maxIfState(flow_end_millisecond, from_subscriber == 1) flow_end_millisecond_from_subscriber_max_if,
      maxIfState(flow_end_millisecond, from_subscriber == 0) flow_end_millisecond_to_subscriber_max_if,

      countState() total_count,
      uniqState(session_id) session_id_uniq,

      sumIfState(octet_delta_count, from_subscriber == 1) octet_delta_count_from_subscriber_sum_if,
      sumIfState(octet_delta_count, from_subscriber == 0) octet_delta_count_to_subscriber_sum_if,
      sumState(octet_delta_count) octet_delta_count_sum,

      sumIfState(packet_delta_count, from_subscriber == 1) packet_delta_count_from_subscriber_sum_if,
      sumIfState(packet_delta_count, from_subscriber == 0) packet_delta_count_to_subscriber_sum_if,
      sumState(packet_delta_count) packet_delta_count_sum,

      anyHeavyState(ip_class_of_service) ip_class_of_service_any,

      avgIfState(packet_deliver_time, from_subscriber == 1 and packet_deliver_time <> 0) rtt_from_subscriber_avg_if,
      avgIfState(packet_deliver_time, from_subscriber == 0 and packet_deliver_time <> 0) rtt_to_subscriber_avg_if,
      avgIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_avg_if,
      medianIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_median_if,

      anyHeavyIfState(packet_deliver_time, from_subscriber == 1 and packet_deliver_time <> 0) rtt_from_subscriber_any_heavy_if,
      anyHeavyIfState(packet_deliver_time, from_subscriber == 0 and packet_deliver_time <> 0) rtt_to_subscriber_any_heavy_if,
      anyHeavyIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_any_heavy_if,

      avgIfState(repeat_delta_pack, from_subscriber == 1 and repeat_delta_pack <> 0) retransmits_from_subscriber_avg_if,
      avgIfState(repeat_delta_pack, from_subscriber == 0 and repeat_delta_pack <> 0) retransmits_to_subscriber_avg_if,
      avgIfState(repeat_delta_pack, repeat_delta_pack <> 0) retransmits_avg_if,

      avgIfState(repeat_delta_pack / packet_delta_count, from_subscriber == 1) retransmits_rate_from_subscriber_avg_if,
      avgIfState(repeat_delta_pack / packet_delta_count, from_subscriber == 0) retransmits_rate_to_subscriber_avg_if,
      avgIfState(repeat_delta_pack / packet_delta_count, 1) retransmits_rate_avg_if,

      avgIfState(frgmt_delta_packs, frgmt_delta_packs <> 0) fragments_avg_if
    from fullflow
      where is_subscriber = 1 and is_excluded_subscriber = 0
      group by `date`, `hour`, `time`,
        source_asnum,
        destination_asnum,
        source_astitle,
        destination_astitle,
        from_subscriber,
        is_subscriber,
        is_excluded_subscriber,
        source_ip,
        destination_ip,
        subscriber,
        subscriber_login,
        login,
        host_ip,
        host,
        protocol_identifier,
        protocol_code,
        post_nat_source_ipv4,
        post_nat_source_port,
        dpi_id,
        bridge_vchannel_num,
        ip_class_of_service
      order by `time` asc;

@alexeyrusnak
Copy link
Author

Latest version - error
https://fiddle.clickhouse.com/0b5a3921-2a9d-462d-8118-0919c0015b35

23.4.3 version - ok
https://fiddle.clickhouse.com/01f2064d-f036-48fa-803a-c8725ccd8e3d

@den-crane den-crane added bug Confirmed user-visible misbehaviour in official release v23.5-affected and removed potential bug To be reviewed by developers and confirmed/rejected. labels Jun 15, 2023
@den-crane
Copy link
Contributor

No. Still a problem because of a dictionary https://fiddle.clickhouse.com/6f249dc0-b1e2-43cb-9b27-3f8b70b03a3b

@den-crane den-crane added potential bug To be reviewed by developers and confirmed/rejected. and removed bug Confirmed user-visible misbehaviour in official release v23.5-affected labels Jun 15, 2023
@alexeyrusnak
Copy link
Author

No. Still a problem because of a dictionary https://fiddle.clickhouse.com/6f249dc0-b1e2-43cb-9b27-3f8b70b03a3b

Now I have remove all dics

Latest - error
https://fiddle.clickhouse.com/f68b4ab1-40cc-4eeb-88f2-a04fcc66ee45

23.4.3.48 – ok
https://fiddle.clickhouse.com/751640b7-a0ed-448d-a64d-720d34b691c6

set allow_deprecated_database_ordinary=1;

create database if not exists qoestor engine = Ordinary;

use qoestor;


create table if not exists fullflow
(
    octet_delta_count           UInt64,
    packet_delta_count          UInt64,
    protocol_identifier         UInt8,
    ip_class_of_service         UInt8,
    source_port                 UInt16,
    source_ipv4                 String,
    source_ipv6                 String,
    destination_port            UInt16,
    destination_ipv4            String,
    destination_ipv6            String,
    bgp_source_as_number        UInt32,
    bgp_destination_as_number   UInt32,
    flow_start_date             Date,
    flow_start_time             DateTime,
    flow_start_millisecond      UInt64,
    flow_end_date               Date,
    flow_end_time               DateTime,
    flow_end_millisecond        UInt64,
    input_snmp                  UInt16,
    output_snmp                 UInt16,
    ip_version                  UInt8,
    session_id                  UInt64,
    host                        String,
    protocol_code               UInt16,
    login                       String,
    post_nat_source_ipv4        String,
    post_nat_source_port        UInt16,
    frgmt_delta_packs           UInt16,
    repeat_delta_pack           UInt16,
    packet_deliver_time         UInt32,
    dpi_id                      UInt8,
    bridge_vchannel_num         UInt16

) engine = MergeTree() partition by toStartOfHour(flow_start_time) order by (
                                                                              flow_start_date,
                                                                              flow_start_time,
                                                                              flow_start_millisecond,
                                                                              session_id,
                                                                              host,
                                                                              source_ipv4,
                                                                              destination_ipv4,
                                                                              source_ipv6,
                                                                              destination_ipv6,
                                                                              dpi_id,
                                                                              bridge_vchannel_num
                                                                              );

alter table fullflow add column if not exists from_subscriber UInt8,
                     add column if not exists source_ip String alias (source_ipv6 == '::' ? source_ipv4 : source_ipv6),
                     add column if not exists destination_ip String alias (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6),
                     add column if not exists source_asnum UInt32 alias (from_subscriber == 1 ? bgp_source_as_number : bgp_destination_as_number),
                     add column if not exists destination_asnum UInt32 alias (from_subscriber == 0 ? bgp_source_as_number : bgp_destination_as_number),
                     add column if not exists source_astitle String alias 'SASN descr',
                     add column if not exists destination_astitle String alias 'DASN descr',
                     add column if not exists is_subscriber UInt8,
                     add column if not exists is_excluded_subscriber UInt8,
                     add column if not exists subscriber String alias (from_subscriber == 1 ? (source_ipv6 == '::' ? source_ipv4 : source_ipv6) : (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6)),
                     add column if not exists subscriber_login String alias (empty(login) ? subscriber : login),
                     add column if not exists host_ip String alias (from_subscriber == 0 ? (source_ipv6 == '::' ? source_ipv4 : source_ipv6) : (destination_ipv6 == '::' ? destination_ipv4 : destination_ipv6)),
                     add column if not exists subscriber_port UInt16 alias (from_subscriber == 1 ? source_port : destination_port),
                     add column if not exists host_port UInt16 alias (from_subscriber == 0 ? source_port : destination_port),
                     add column if not exists bridge_vchannel_num UInt16 after dpi_id, modify order by (flow_start_date, flow_start_time, flow_start_millisecond, session_id, host, source_ipv4, destination_ipv4, source_ipv6, destination_ipv6, dpi_id, bridge_vchannel_num);

alter table fullflow modify column if exists from_subscriber UInt8 default (input_snmp == 1 and output_snmp == 2 ? 1 : 0);

alter table fullflow modify column if exists is_subscriber UInt8 default 1;

alter table fullflow modify column if exists is_excluded_subscriber UInt8 default 0;



create table if not exists `.inner.fullflow_agg`
(
    date                                            Date,
    hour                                            DateTime,
    time                                            DateTime,
    from_subscriber                                 UInt8,
    source_ip                                       String,
    destination_ip                                  String,
    source_asnum                                    UInt32,
    destination_asnum                               UInt32,
    source_astitle                                  String,
    destination_astitle                             String,
    is_subscriber                                   UInt8,
    is_excluded_subscriber                          UInt8 default 0,
    subscriber                                      String,
    subscriber_login                                String,
    login                                           String,
    host_ip                                         String,
    host                                            String,
    protocol_code                                   UInt16,
    protocol_identifier                             UInt8,
    post_nat_source_ipv4                            String,
    post_nat_source_port                            UInt16,
    dpi_id                                          UInt8,
    bridge_vchannel_num                             UInt16,
    ip_class_of_service                             UInt8,
    flow_start_millisecond_min                      AggregateFunction(min, UInt64),
    flow_start_millisecond_from_subscriber_min_if   AggregateFunction(minIf, UInt64, UInt8),
    flow_start_millisecond_to_subscriber_min_if     AggregateFunction(minIf, UInt64, UInt8),
    flow_end_millisecond_max                        AggregateFunction(max, UInt64),
    flow_end_millisecond_from_subscriber_max_if     AggregateFunction(maxIf, UInt64, UInt8),
    flow_end_millisecond_to_subscriber_max_if       AggregateFunction(maxIf, UInt64, UInt8),
    total_count                                     AggregateFunction(count),
    session_id_uniq                                 AggregateFunction(uniq, UInt64),
    octet_delta_count_from_subscriber_sum_if        AggregateFunction(sumIf, UInt64, UInt8),
    octet_delta_count_to_subscriber_sum_if          AggregateFunction(sumIf, UInt64, UInt8),
    octet_delta_count_sum                           AggregateFunction(sum, UInt64),
    packet_delta_count_from_subscriber_sum_if       AggregateFunction(sumIf, UInt64, UInt8),
    packet_delta_count_to_subscriber_sum_if         AggregateFunction(sumIf, UInt64, UInt8),
    packet_delta_count_sum                          AggregateFunction(sum, UInt64),
    ip_class_of_service_any                         AggregateFunction(anyHeavy, UInt8),
    rtt_from_subscriber_avg_if                      AggregateFunction(avgIf, UInt32, UInt8),
    rtt_to_subscriber_avg_if                        AggregateFunction(avgIf, UInt32, UInt8),
    rtt_avg_if                                      AggregateFunction(avgIf, UInt32, UInt8),
    rtt_median_if                                   AggregateFunction(quantileIf, UInt32, UInt8),
    rtt_from_subscriber_any_heavy_if                AggregateFunction(anyHeavyIf, UInt32, UInt8),
    rtt_to_subscriber_any_heavy_if                  AggregateFunction(anyHeavyIf, UInt32, UInt8),
    rtt_any_heavy_if                                AggregateFunction(anyHeavyIf, UInt32, UInt8),
    retransmits_from_subscriber_avg_if              AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_to_subscriber_avg_if                AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_avg_if                              AggregateFunction(avgIf, UInt16, UInt8),
    retransmits_rate_from_subscriber_avg_if         AggregateFunction(avgIf, Float64, UInt8),
    retransmits_rate_to_subscriber_avg_if           AggregateFunction(avgIf, Float64, UInt8),
    retransmits_rate_avg_if                         AggregateFunction(avgIf, Float64, UInt8),
    fragments_avg_if                                AggregateFunction(avgIf, UInt16, UInt8)
) engine = AggregatingMergeTree() partition by toStartOfHour(`time`) order by (`date`, `hour`, `time`,
                                                                            source_asnum,
                                                                            destination_asnum,
                                                                            source_astitle,
                                                                            destination_astitle,
                                                                            from_subscriber,
                                                                            is_subscriber,
                                                                            source_ip,
                                                                            destination_ip,
                                                                            subscriber,
                                                                            subscriber_login,
                                                                            login,
                                                                            host_ip,
                                                                            host,
                                                                            protocol_identifier,
                                                                            protocol_code,
                                                                            post_nat_source_ipv4,
                                                                            post_nat_source_port,
                                                                            dpi_id,
                                                                            bridge_vchannel_num,
                                                                            ip_class_of_service);

detach table if exists fullflow_agg;

alter table `.inner.fullflow_agg` add column if not exists is_excluded_subscriber UInt8 default 0 after is_subscriber,
                                  add column if not exists bridge_vchannel_num UInt16 after dpi_id,
                                  add column if not exists ip_class_of_service UInt8 after bridge_vchannel_num,
                                  modify order by (`date`, `hour`, `time`, source_asnum, destination_asnum, source_astitle, destination_astitle, from_subscriber, is_subscriber, source_ip, destination_ip, subscriber, subscriber_login, login, host_ip, host, protocol_identifier, protocol_code, post_nat_source_ipv4, post_nat_source_port, dpi_id, bridge_vchannel_num, ip_class_of_service);

attach materialized view if not exists fullflow_agg
  engine = AggregatingMergeTree() partition by toStartOfHour(`time`) order by (`date`, `hour`, `time`,
                                                                            source_asnum,
                                                                            destination_asnum,
                                                                            source_astitle,
                                                                            destination_astitle,
                                                                            from_subscriber,
                                                                            is_subscriber,
                                                                            source_ip,
                                                                            destination_ip,
                                                                            subscriber,
                                                                            subscriber_login,
                                                                            login,
                                                                            host_ip,
                                                                            host,
                                                                            protocol_identifier,
                                                                            protocol_code,
                                                                            post_nat_source_ipv4,
                                                                            post_nat_source_port,
                                                                            dpi_id,
                                                                            bridge_vchannel_num,
                                                                            ip_class_of_service) as
    select

      flow_start_date as `date`,
      toStartOfHour(flow_start_time) as `hour`,
      toStartOfInterval(flow_start_time, toIntervalMinute(15)) as `time`,
      from_subscriber,
      true ? source_ip : '' as source_ip, --0x1
      true ? destination_ip : '' as destination_ip, --0x2
      true ? source_asnum : 0 as source_asnum, --0x4
      true ? destination_asnum : 0 as destination_asnum, --0x8
      true ? source_astitle : '' as source_astitle, --0x10
      true ? destination_astitle : '' as destination_astitle, --0x20
      is_subscriber,
      is_excluded_subscriber,
      true ? subscriber : '' as subscriber, --0x40
      true ? subscriber_login : '' as subscriber_login, --0x80
      true ? login : '' as login, --0x100
      true ? host_ip : '' as host_ip, --0x200
      true ? host : '' as host, --0x400
      true ? protocol_code : 0 as protocol_code, --0x800
      true ? protocol_identifier : 0 as protocol_identifier, --0x1000
      false ? post_nat_source_ipv4 : '' as post_nat_source_ipv4, --0x2000
      false ? post_nat_source_port : 0 as post_nat_source_port, --0x4000
      true ? dpi_id : 0 as dpi_id, --0x8000
      true ? bridge_vchannel_num : 0 as bridge_vchannel_num, --0x10000

      true ? ip_class_of_service : 0 as ip_class_of_service, --0x20000

      minState(flow_start_millisecond) flow_start_millisecond_min,
      minIfState(flow_start_millisecond, from_subscriber == 1) flow_start_millisecond_from_subscriber_min_if,
      minIfState(flow_start_millisecond, from_subscriber == 0) flow_start_millisecond_to_subscriber_min_if,

      maxState(flow_end_millisecond) flow_end_millisecond_max,
      maxIfState(flow_end_millisecond, from_subscriber == 1) flow_end_millisecond_from_subscriber_max_if,
      maxIfState(flow_end_millisecond, from_subscriber == 0) flow_end_millisecond_to_subscriber_max_if,

      countState() total_count,
      uniqState(session_id) session_id_uniq,

      sumIfState(octet_delta_count, from_subscriber == 1) octet_delta_count_from_subscriber_sum_if,
      sumIfState(octet_delta_count, from_subscriber == 0) octet_delta_count_to_subscriber_sum_if,
      sumState(octet_delta_count) octet_delta_count_sum,

      sumIfState(packet_delta_count, from_subscriber == 1) packet_delta_count_from_subscriber_sum_if,
      sumIfState(packet_delta_count, from_subscriber == 0) packet_delta_count_to_subscriber_sum_if,
      sumState(packet_delta_count) packet_delta_count_sum,

      anyHeavyState(ip_class_of_service) ip_class_of_service_any,

      avgIfState(packet_deliver_time, from_subscriber == 1 and packet_deliver_time <> 0) rtt_from_subscriber_avg_if,
      avgIfState(packet_deliver_time, from_subscriber == 0 and packet_deliver_time <> 0) rtt_to_subscriber_avg_if,
      avgIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_avg_if,
      medianIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_median_if,

      anyHeavyIfState(packet_deliver_time, from_subscriber == 1 and packet_deliver_time <> 0) rtt_from_subscriber_any_heavy_if,
      anyHeavyIfState(packet_deliver_time, from_subscriber == 0 and packet_deliver_time <> 0) rtt_to_subscriber_any_heavy_if,
      anyHeavyIfState(packet_deliver_time, packet_deliver_time <> 0) rtt_any_heavy_if,

      avgIfState(repeat_delta_pack, from_subscriber == 1 and repeat_delta_pack <> 0) retransmits_from_subscriber_avg_if,
      avgIfState(repeat_delta_pack, from_subscriber == 0 and repeat_delta_pack <> 0) retransmits_to_subscriber_avg_if,
      avgIfState(repeat_delta_pack, repeat_delta_pack <> 0) retransmits_avg_if,

      avgIfState(repeat_delta_pack / packet_delta_count, from_subscriber == 1) retransmits_rate_from_subscriber_avg_if,
      avgIfState(repeat_delta_pack / packet_delta_count, from_subscriber == 0) retransmits_rate_to_subscriber_avg_if,
      avgIfState(repeat_delta_pack / packet_delta_count, 1) retransmits_rate_avg_if,

      avgIfState(frgmt_delta_packs, frgmt_delta_packs <> 0) fragments_avg_if
    from fullflow
      where is_subscriber = 1 and is_excluded_subscriber = 0
      group by `date`, `hour`, `time`,
        source_asnum,
        destination_asnum,
        source_astitle,
        destination_astitle,
        from_subscriber,
        is_subscriber,
        is_excluded_subscriber,
        source_ip,
        destination_ip,
        subscriber,
        subscriber_login,
        login,
        host_ip,
        host,
        protocol_identifier,
        protocol_code,
        post_nat_source_ipv4,
        post_nat_source_port,
        dpi_id,
        bridge_vchannel_num,
        ip_class_of_service
      order by `time` asc;

@alexeyrusnak
Copy link
Author

@den-crane Could you check again please

@den-crane
Copy link
Contributor

simplified repro

CREATE TABLE f(c String) ENGINE = Null;

create  materialized view v engine = Null as
select
   false ? c : '' as c, 
   countState() t
from f group by c;

https://fiddle.clickhouse.com/4dc51ebf-a3da-4013-b5dc-1d8521702504

As workaround you can use 0 ? c : '' as c,

@den-crane den-crane added bug Confirmed user-visible misbehaviour in official release and removed potential bug To be reviewed by developers and confirmed/rejected. labels Jun 27, 2023
@den-crane
Copy link
Contributor

den-crane commented Jun 27, 2023 •
edited

even simpler

select false  ? c : '' as c, count() from (select '' c) group by c;

Column `c` is not under aggregate function and not in GROUP BY. Have columns: ['false','count()']
select if( 0 , c, '') _c, count() from (select '' c) group by _c;
┌─_c─┬─count()─┐
│    │       1 │
└────┴─────────┘

select if(1 = 0, c, '') _c, count() from (select '' c) group by _c;
DB::Exception: Column `c` is not under aggregate function and not in GROUP BY.
Have columns: ['equals(1, 0)','1','0','count()']: While processing if(1 = 0, c, '') AS _c, count(). (NOT_AN_AGGREGATE)

@den-crane
Copy link
Contributor

den-crane commented Jun 27, 2023 •
edited

another WA - materialize

select materialize(false) ? c : 'x' as c, count() from (select 'o' c) group by c;
┌─c─┬─count()─┐
│ x │       1 │
└───┴─────────┘

@den-crane
Copy link
Contributor

den-crane commented Jun 27, 2023 •
edited

@alexey-milovidov probably you introduced this issue in #50536

select if(1 = 0, c, '') _c, count() from (select '' c) group by _c;
DB::Exception: Column `c` is not under aggregate function

select if(1 = 1, c, '') _c, count() from (select '' c) group by _c;
┌─_c─┬─count()─┐
│    │       1 │
└────┴─────────┘

@zzznikola
Copy link

zzznikola commented Jul 20, 2023 •
edited

Such behaviour is result of this commit
But reason is somewhere inside syntax analysis. Analyser of functions in SELECT statement part does not use IFunction getConstantResultForNonConstArguments, but analyser of GROUP BY part does. So this bug can affect all functions that override getConstantResultForNonConstArguments, for example

SELECT timezoneOf(expires_at) tz, count(*) FROM system.query_cache GROUP BY tz
DB::Exception: Column `expires_at` is not under aggregate function

zzznikola added a commit to zzznikola/ClickHouse that referenced this issue Jul 28, 2023
@zzznikola
fix ClickHouse#50928
73d5bf7
@den-crane den-crane mentioned this issue Sep 26, 2023
Closed
@den-crane
Copy link
Contributor

den-crane commented Dec 18, 2023 •
edited

@kitaisreal

#50928 (comment)

#50928 (comment)

@SaltTan SaltTan mentioned this issue Jan 19, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Confirmed user-visible misbehaviour in official release v23.5-affected v23.8-affected
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@den-crane @alexeyrusnak @zzznikola