[multistage] UNION queries produces wrong sorting results

[walterddr]

SELECT val FROM (
SELECT val, ord FROM (
  SELECT CAST(1 AS INT) AS val, 1 AS ord
  UNION ALL
  SELECT CAST(3 AS INT) AS val, 3 AS ord
)
UNION ALL
SELECT val, ord FROM (
  SELECT CAST(2.1 AS DOUBLE) AS val, 2 AS ord
  UNION ALL
  SELECT CAST(4.1 AS DOUBLE) AS val, 4 AS ord
)
)
ORDER BY ord

throws exception in final order-by comparison b/c of different data type (using either both DOUBLE or both INT are fine.

[kim-jaewoong]

Hi, I first reported the bug, and here is a pdf file that I made to report it. It has some test cases and results I explored.
Pinot bug report.pdf

It includes two bugs and the first one is in another issue: #11821

[kim-jaewoong]

To summarize, there are four test cases, and only the second one throws exception.

• no order by

SET useMultistageEngine = true;
SELECT val FROM (
SELECT val, ord FROM (
SELECT CAST(1 AS INT) AS val, 1 AS ord
UNION ALL
SELECT CAST(3 AS INT) AS val, 3 AS ord
)
UNION ALL
SELECT val, ord FROM (
SELECT CAST(2.1 AS DOUBLE) AS val, 2 AS ord
UNION ALL
SELECT CAST(4.1 AS DOUBLE) AS val, 4 AS ord
)
)
-- ORDER BY ord

• with INT and DOUBLE

SET useMultistageEngine = true;
SELECT val FROM (
SELECT val, ord FROM (
SELECT CAST(1 AS INT) AS val, 1 AS ord
UNION ALL
SELECT CAST(3 AS INT) AS val, 3 AS ord
)
UNION ALL
SELECT val, ord FROM (
SELECT CAST(2.1 AS DOUBLE) AS val, 2 AS ord
UNION ALL
SELECT CAST(4.1 AS DOUBLE) AS val, 4 AS ord
)
)
ORDER BY ord

• with DOUBLE and STRING

SET useMultistageEngine = true;
SELECT val FROM (
SELECT val, ord FROM (
SELECT CAST(1.1 AS DOUBLE) AS val, 1 AS ord
UNION ALL
SELECT CAST(3.1 AS DOUBLE) AS val, 3 AS ord
)
UNION ALL
SELECT val, ord FROM (
SELECT '2.1' AS val, 2 AS ord
UNION ALL
SELECT '4.1' AS val, 4 AS ord
)
)
ORDER BY ord

• with INT and STRING

SET useMultistageEngine = true;
SELECT val FROM (
SELECT val, ord FROM (
SELECT CAST(1 AS INT) AS val, 1 AS ord
UNION ALL
SELECT CAST(3 AS INT) AS val, 3 AS ord
)
UNION ALL
SELECT val, ord FROM (
SELECT '2.1' AS val, 2 AS ord
UNION ALL
SELECT '4.1' AS val, 4 AS ord
)
)
ORDER BY ord