Skip to content

Exception: Conversion from AggregateFunction(sum, Nullable(Int64)) to AggregateFunction(sum, Int64) is not supported: While executing Remote. #23019

New issue
New issue
Closed
Closed
Exception: Conversion from AggregateFunction(sum, Nullable(Int64)) to AggregateFunction(sum, Int64) is not supported: While executing Remote.#23019
Assignees
alexey-milovidov
Labels
not plannedKnown issue, no plans to fix it currenltyquestionQuestion?question-answeredv22.4-affected
@Sammion

Description

@Sammion
Sammion
opened on Apr 13, 2021

Hi team,
I meet an error when try to query a distribute table:
Here is query and error:
select clsfd_trffc_chnl_name,page_type_txt,sum(clsfd_pv_cnt) as pv from my_db.all_PI_TRAFFIC where 1=1 group by clsfd_trffc_chnl_name,page_type_txt order by clsfd_trffc_chnl_name,page_type_txt;

Exception: Conversion from AggregateFunction(sum, Nullable(Int64)) to AggregateFunction(sum, Int64) is not supported: While executing Remote.

but I try the same column on the local server, it works well. Here is query
0be97fd088ac :) select clsfd_trffc_chnl_name,page_type_txt,sum(clsfd_pv_cnt) as pv from my_db.PI_TRAFFIC where 1=1 group by clsfd_trffc_chnl_name,page_type_txt order by clsfd_trffc_chnl_name,page_type_txt;

I am not sure if it is a bug. Anyone know why?
How can I do to avoid it happens?

Activity

Sammion
added
questionQuestion?
on Apr 13, 2021
akuzm

akuzm commented on Apr 15, 2021

@akuzm
akuzm
on Apr 15, 2021
Contributor

Please post the CREATE TABLE statements for both tables, my_db.PI_TRAFFIC and my_db.all_PI_TRAFFIC.

Slach

Slach commented on Jun 28, 2022

@Slach
Slach
on Jun 28, 2022 · edited by Slach
Contributor

@akuzm

SELECT argMax(s, d) FROM (
  SELECT arrayJoin(['1',Null,'2']) s, now() d
)


CREATE DATABASE bms_qa_roletesting;

CREATE TABLE bms_qa_roletesting.device_time_series (`device_id` String, `building_id` String, `t_utc` DateTime('UTC'), `s` Nullable(String), `t_local` DateTime) ENGINE = MergeTree() PARTITION BY toYYYYMM(t_local) ORDER BY (device_id, t_utc) SETTINGS index_granularity = 8192;

CREATE TABLE bms_qa_roletesting.device_time_series_last_values (`building_id` String, `device_id` String, `s` AggregateFunction(argMax, String, DateTime), `time` AggregateFunction(max, DateTime)) ENGINE = AggregatingMergeTree() ORDER BY device_id SETTINGS index_granularity = 8192;

CREATE MATERIALIZED VIEW bms_qa_roletesting.device_time_series_last_values_view TO bms_qa_roletesting.device_time_series_last_values (`building_id` String, `device_id` String, `s` AggregateFunction(argMax, String, DateTime), `time` AggregateFunction(max, DateTime)) AS SELECT building_id, device_id, argMaxState(s, t_local) AS s, maxState(t_local) AS time FROM bms_qa_roletesting.device_time_series GROUP BY building_id, device_id;

SELECT * FROM bms_qa_roletesting.device_time_series_last_values_view;

works fine in 20.x, 21.x and 22.3 but failed in 22.4+

Slach
mentioned this on Jun 28, 2022
filimonov
added
v22.4-affected
on Jun 28, 2022
alexey-milovidov

alexey-milovidov commented on Dec 26, 2022

@alexey-milovidov
alexey-milovidov
on Dec 26, 2022
Member

The data types
AggregateFunction(argMax, Nullable(String), DateTime) and AggregateFunction(argMax, String, DateTime)
are definitely incompatible:

milovidov-desktop :) SELECT hex(argMaxState(a, m)) FROM (SELECT 'Hello' AS a, 123 AS m)

SELECT hex(argMaxState(a, m))
FROM
(
    SELECT
        'Hello' AS a,
        123 AS m
)

Query id: b6f9478a-1582-4a99-a8f9-a8aa791940f0

┌─hex(argMaxState(a, m))───┐
│ 0600000048656C6C6F00017B │
└──────────────────────────┘

1 row in set. Elapsed: 0.004 sec. 

milovidov-desktop :) SELECT hex(argMaxState(a, m)) FROM (SELECT toNullable('Hello') AS a, 123 AS m)

SELECT hex(argMaxState(a, m))
FROM
(
    SELECT
        toNullable('Hello') AS a,
        123 AS m
)

Query id: ebf0b9c2-4da1-4704-99a2-ee4abb53a7ec

┌─hex(argMaxState(a, m))─────┐
│ 010600000048656C6C6F00017B │
└────────────────────────────┘

1 row in set. Elapsed: 0.005 sec.

The error message is correct.
The previous version might appear to be working due to a bug we have now fixed.

alexey-milovidov
closed this as completedon Dec 26, 2022
alexey-milovidov
self-assigned this
on Dec 26, 2022
alexey-milovidov
added
not plannedKnown issue, no plans to fix it currenlty
question-answered
on Dec 26, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

not plannedKnown issue, no plans to fix it currenltyquestionQuestion?question-answeredv22.4-affected

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

    Development

    No branches or pull requests

      Participants

      @Slach@filimonov@alexey-milovidov@Sammion@akuzm

      Issue actions
        Exception: Conversion from AggregateFunction(sum, Nullable(Int64)) to AggregateFunction(sum, Int64) is not supported: While executing Remote. · Issue #23019 · ClickHouse/ClickHouse