Starting with this DDL:
This query gives the highest id of friends that share friend_id for each friend:
I convert this query to a RelNode and then converted it back to SQL:
This gives the following plan and SQL:
This is a bad conversion. The MAX(`id`) should not be repeated in the outer select. PostgreSQL will complain that the aggregating function requires a group by. MySQL returns the max id that has a non-null friend (208), that id's friend (202), and the max id of all rows (210):
I think the correct SQL should be:
There is code in SqlImplementor and RelToSqlConverter that uses an ordinalMap to track what functions are aliases as what identifiers. When the SQL is generated, the identifier is replaced with the function. If all that code is removed, this works as expected (using EXPR$0 as the alias rather than foaf_id).