SQL: ordering not working for PIVOTed column

[bpintea]

Ordering by a pivoted column is executed, but the requested ordering is not respected.

sql> SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('M', 'F')) order by 'F' asc;
   languages   |       'M'       |       'F'
---------------+-----------------+------------------
null           |48396.28571428572|62140.666666666664
1              |49767.22222222222|47073.25
2              |44103.90909090909|50684.4
3              |51741.90909090909|53660.0
4              |47058.90909090909|49291.5
5              |39052.875        |46705.555555555555

Using an alias for the pivoted column is nicely also accepted, but also ignored:

sql> SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('M', 'F' as XX)) order by XX asc;
   languages   |       'M'       |        XX
---------------+-----------------+------------------
null           |48396.28571428572|62140.666666666664
1              |49767.22222222222|47073.25
2              |44103.90909090909|50684.4
3              |51741.90909090909|53660.0
4              |47058.90909090909|49291.5
5              |39052.875        |46705.555555555555

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[arnab30dutta]

@Moderators / @Admins Will it be a part of idp or smb tests ? Can any sample Data set be provided for simple reproducable ingress