Skip to content

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

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
Closed
Sammion opened this issue Apr 13, 2021 · 3 comments
Closed

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

Sammion opened this issue Apr 13, 2021 · 3 comments
Assignees
@alexey-milovidov
Labels
not planned Known issue, no plans to fix it currenlty question Question? question-answered v22.4-affected

Comments

@Sammion
Copy link

Sammion commented 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?
@Sammion Sammion added the question Question? label Apr 13, 2021
@akuzm
Copy link
Contributor

akuzm commented Apr 15, 2021

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

@Slach
Copy link
Contributor

Slach commented Jun 28, 2022 •
edited
Loading

@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 Slach mentioned this issue Jun 28, 2022
Closed
@alexey-milovidov
Copy link
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 alexey-milovidov self-assigned this Dec 26, 2022
@alexey-milovidov alexey-milovidov added not planned Known issue, no plans to fix it currenlty question-answered labels Dec 26, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@alexey-milovidov alexey-milovidov

Labels
not planned Known issue, no plans to fix it currenlty question Question? question-answered v22.4-affected
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@Slach @filimonov @alexey-milovidov @Sammion @akuzm