Union fields with time attributes have different types

[Sebastián Magrí]

I'm using a query like this

WITH aggs_1m AS (
  SELECT
    `evt`,
    `startts`
    `endts`,
    SUM(`value`) AS `value`
  FROM aggregates_per_minute
), aggs_3m AS (
  SELECT
    `evt`,
    TUMBLE_START(`endts`, INTERVAL '3' MINUTE) AS `startts`,
    TUMBLE_END(`endts`, INTERVAL '3' MINUTE) AS `endts`,
    SUM(`c`) AS `value`
  FROM aggregates_per_minute
  GROUP BY t, TUMBLE(`endts`, INTERVAL '3' MINUTE)
)
SELECT `evt`, `value`, `startts`, `endts`
FROM aggs_1m
UNION
SELECT `evt`, `value`, `startts`, `endts`
FROM aggs_3m

But it's throwing this exception

org.apache.flink.table.api.ValidationException: Union fields with time attributes have different types.

Doesn't TUMBLE_START(somets, ...) return a TIMESTAMP of the same type?

--

[Jark Wu-3]

Hi Sebastián,

`endts` in your case is a time attribute which is slightly different than a regular TIMESTAMP type. 

You can manually `cast(endts as timestamp(3)` to make this query work which removes the time attribute meta. 

SELECT `evt`, `value`, `startts`, cast(endts as timestamp(3)
FROM aggs_1m

Best,

Jark

On Mon, 22 Feb 2021 at 05:01, Sebastián Magrí <[hidden email]> wrote:

I'm using a query like this

WITH aggs_1m AS (
  SELECT
    `evt`,
    `startts`
    `endts`,
    SUM(`value`) AS `value`
  FROM aggregates_per_minute
), aggs_3m AS (
  SELECT
    `evt`,
    TUMBLE_START(`endts`, INTERVAL '3' MINUTE) AS `startts`,
    TUMBLE_END(`endts`, INTERVAL '3' MINUTE) AS `endts`,
    SUM(`c`) AS `value`
  FROM aggregates_per_minute
  GROUP BY t, TUMBLE(`endts`, INTERVAL '3' MINUTE)
)
SELECT `evt`, `value`, `startts`, `endts`
FROM aggs_1m
UNION
SELECT `evt`, `value`, `startts`, `endts`
FROM aggs_3m

But it's throwing this exception

org.apache.flink.table.api.ValidationException: Union fields with time attributes have different types.

Doesn't TUMBLE_START(somets, ...) return a TIMESTAMP of the same type?

--

Sebastián Ramírez Magrí