Ramin Gharib created FLINK-41333:
------------------------------------
Summary: MIN, MAX and streaming COUNT(DISTINCT) reject the UUID
type
Key: FLINK-41333
URL: https://issues.apache.org/jira/browse/FLINK-41333
Project: Flink
Issue Type: Sub-task
Components: Table SQL / Planner, Table SQL / Runtime
Affects Versions: 2.4.0
Reporter: Ramin Gharib
{{UUID}} can be compared, ordered and grouped, but three aggregates reject it
at planning time.
{code:sql}
CREATE TABLE src (k INT) WITH (
'connector' = 'datagen', 'number-of-rows' = '6',
'fields.k.kind' = 'sequence', 'fields.k.start' = '1', 'fields.k.end' = '6');
CREATE VIEW keyed AS
SELECT CAST(LPAD(CAST(MOD(k, 3) AS STRING), 32, '0') AS UUID) AS u, k FROM src;
{code}
||Query||Streaming||Batch||
|{{SELECT MIN(u), MAX(u) FROM keyed}}|{{Min aggregate function does not support
type: ''UUID''.}}|same error|
|{{SELECT COUNT(DISTINCT u) FROM keyed}}|{{Distinct aggregate function does not
support type: UUID.}}|3|
|{{SELECT APPROX_COUNT_DISTINCT(u) FROM keyed}}|not supported in streaming for
any type|{{APPROX_COUNT_DISTINCT aggregate function does not support type:
''UUID''.}}|
|{{SELECT DISTINCT u}}, {{GROUP BY u}}, {{FIRST_VALUE(u)}}, {{LAST_VALUE(u)}},
{{ARRAY_AGG(u)}}|works|works|
{{MIN}} over {{UUID_V7()}} values gives the earliest event, which is a common
query on the new type.
h3. Cause
{{AggFunctionFactory.createMinAggFunction}} and {{createMaxAggFunction}} list
the supported type roots for the plain and the retracting variant. {{UUID}} is
not in either list. {{AggregateUtil.createDistinctKeyType}} lists the key types
for a streaming distinct view, and {{UUID}} is missing there too.
{{VARBINARY}} fails in exactly the same places, and {{UUID}} shares its
internal {{byte[]}} form. I have not checked why {{BINARY}} is excluded from
the distinct key types. If the reason is identity equality on {{byte[]}} keys,
as in FLINK-41327, then adding {{UUID}} to the list is not enough.
Workaround: go through the canonical string. Fixed-width lower-case hex sorts
the same as the unsigned bytes.
{code:sql}
SELECT CAST(MIN(CAST(u AS STRING)) AS UUID) FROM keyed; --
00000000-0000-0000-0000-000000000000
SELECT COUNT(DISTINCT CAST(u AS STRING)) FROM keyed; -- 3
{code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
