ORDER BY before SELECT

[johnedquinn]

Background

I have a question regarding the evaluation order of PartiQL's SFW query. It originally stems from my PR in PartiQL Kotlin's PR #740, which originally came from Issue #571.

It specifically has to do with the input bindings to ORDER BY and where it lies in the evaluation order. In Section 3.3 of the PartiQL Specification, it states:

The clauses of an PartiQL SFW query are evaluated in the following order: WITH, FROM, LET, WHERE, GROUP BY, HAVING, LETTING (which is special to PartiQL), ORDER BY, LIMIT / OFFSET and SELECT (or SELECT VALUE or PIVOT, which are both special to ion PartiQL).

It was my assumption that ORDER BY, LIMIT, and OFFSET would follow SELECT, but in this case, it does not.

Then, Section 12.5 of the PartiQL Specification writes:

Recall from Section 3 that ORDER BY is evaluated before SELECT. For SQL-compatibility, given SELECT ei AS ai, PartiQL also supports the syntactic sugar of using ai in lieu of ei in the ORDER BY clause. Therefore, both SFW queries below are equivalent:

• SELECT ei AS ai FROM ... ORDER BY ai
• SELECT ei AS ai FROM ... ORDER BY ei

My Question

Why did PartiQL opt to choose the order of evaluation that it uses? We specify a workaround in Section 12.5 to maintain compatibility with SQL, but this would not be necessary if ORDER BY was evaluated after SELECT.

References

• I have some examples of what I think the expected behavior of some queries should be in the PR linked above.

[almann]

The main reason for this is that SELECT ..., SELECT VALUE ... and PIVOT return collections of values and not collections of bindings. Variable resolution is defined by the latter, so if SELECT ... happens before ORDER BY we cannot see other variable bindings as they will be gone. What this rule is saying is that there are implicit bindings that are introduced to maintain compatibility with SQL.

Even more interesting is section 12.4 which has to deal with the lack of environment for the set operators and ORDER BY which also has to explain the operational semantics as introducing an implicit binding and having attribute as variable rewrites as explaining what is going on.