[multistage] order-by on UNION STRING and INT columns somehow is allowed

[walterddr]

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 val

This query should throw an exception as 2.1 and 4.1 should not be allowed to compare directly against a numeric value (explicit cast is required) but somehow Pinot produces wrong result instead of throwing an exception.

[walterddr]

related to #11878

[kim-jaewoong]

Hi, I first raised a bug request for #11878, and I'm afraid this issue is wrongly reported. Since the order by is applied to the ord column, the strings are not compared to the numbers, so the query is correct.

[walterddr]

@kim-jaewoong not really, if you change the order by to val it still works. b/c pinot considers literal as unparsed type until execution --> so it is forgiving enough to auto-cast 2.1/4.1 into double during comparison. i adjusted the issue description you can also try it out

[kim-jaewoong]

I meant that they were not directly compared before editing the SQL, but it seems there is also a problem when they are compared.