Flink SQL API: Extra columns added from order by

Posted by Morrisa Brenner on
URL: http://deprecated-apache-flink-user-mailing-list-archive.369.s1.nabble.com/Flink-SQL-API-Extra-columns-added-from-order-by-tp28711.html

Hi Flink folks,

We have a custom date formatting function that we use to format the output of columns containing dates. Ideally what we want is to format the output in the select statement but be able to order by the underlying datetime (so that and output with formatted dates "February 2019" and "April 2019" is guaranteed to have the rows sorted in time order rather than alphabetical order).

When I go to add the unformatted column to the order by, however, that gets appended as an extra column to the select statement during the query planning process within Calcite. (In the order by parsing, it's considering this a different column from the one in the select statement.) When the group by column is different in the same way but there's no order by column, the extra column isn't added. I've included a couple of simple examples below.

Is this the intended behavior of the query planner? Does anyone know of a way around this without needing to change the formatting so that it makes the output dates correctly sortable?

Thanks for your help!

Morrisa

Example query and output with order by using formatted date:

SELECT 

formatDate(floor(`testTable`.`timestamp` TO MONTH), 'MONTH'), 

sum(`testTable`.`count`) 

FROM `testTable` 

GROUP BY formatDate(floor(`testTable`.`timestamp` TO MONTH), 'MONTH') 

ORDER BY formatDate(floor(`testTable`.`timestamp` TO MONTH), 'MONTH') ASC

Month	SUM VALUE
April 2019	1052
February 2019	1



Example query and output without order by but group by using unformatted date:

SELECT 

formatDate(floor(`testTable`.`timestamp` TO MONTH), 'MONTH'), 

sum(`testTable`.`count`) 

FROM `testTable` 

GROUP BY floor(`testTable`.`timestamp` TO MONTH)

Month	SUM VALUE
February 2019	1
April 2019	1052

We would like to enforce the ordering, so although this output is what we want, I don't think we can use this solution.

Example query and output with order by using unformatted date:

SELECT 

formatDate(floor(`testTable`.`timestamp` TO MONTH), 'MONTH'), 

sum(`testTable`.`count`) 

FROM `testTable` 

GROUP BY floor(`testTable`.`timestamp` TO MONTH) 

ORDER BY floor(`testTable`.`timestamp` TO MONTH) ASC

Month	SUM VALUE
February 2019	1	2/1/2019 12:00 AM
April 2019	1052	4/1/2019 12:00 AM

--