[Bug] Alias in ORDER BY expression throws semantic exception

[dai-chen]

@jingyali-apr Thanks for confirming! I may have found where things go wrong:

sql/sql/src/main/java/org/opensearch/sql/sql/parser/AstSortBuilder.java

Line 53 in 907019c

querySpec.replaceIfAliasOrOrdinal(items.get(i)),

When we try to replace alias in ORDER BY, we only replace ordinal or alias directly. In our case, state is NOT NULL is an expression which causes alias replacement fail. Will debug to confirm and open issue/PR to fix. However, just to let you know, the fix would be only available in latest OpenSearch version (probably 2.3 next). Thanks!

Originally posted by @dai-chen in #292 (comment)

[dai-chen]

I checked the code. It's easy to quick fix field IS (NOT) NULL in AstSortBuilder. However, to support general case for arbitrary expression, it requires cloning entire expression after the alias replacement.