SQL: When to expect stable row order on queries of small, unchanged datasets

[philrz]

Most SQL users are familiar with how the standard makes absolutely no guarantee on the order of the rows returned in absence of an explicit ORDER BY. That said, particular database engines are known to have very stable internal execution plans such that certain simple queries over small, unchanged datasets do consistently return stable results. To provide a good experience for users coming to SuperDB from other engines, at some point we may want to establish and document when our query results are likely to be stable.

Details

At the time this issue is being opened, super is at commit f014c14.

I most recently confronted this topic while working with the many UNION test cases in sqllogictest file select4.test_slow. Based on my test runs it looks like there's 17 or more different test cases in there where SuperDB may return the result in a different order on repeated execution (starting with this one), while Postgres and other systems return the rows for these queries in consistent order.

Here's a very simple example using minimal data/query.

$ cat query.sql 
SELECT a FROM t1
UNION ALL
SELECT a FROM t2

$ cat t1
{a:1}

$ cat t2
{a:2}

$ super -version
Version: f014c14fa

$ psql postgres -c "SELECT * FROM t1;"
 a 
---
 1
(1 row)

$ psql postgres -c "SELECT * FROM t2;"
 a 
---
 2
(1 row)

As shown in the attached video, observing the outputs of these two commands:

$ watch -d -n 0.1 'super -s -I query.sql'
$ watch -d -n 0.1 'psql postgres --file query.sql'

https://github.com/user-attachments/assets/1fe23635-c23d-4f0e-9fa4-d04582ecb0c1

We see that SuperDB's result changes constantly while the one from Postgres is consistently stable.

I imagine it's not a priority for us to take action here because we can always fall back on the "no guarantee" per the standard. In this specific case for instance I could just do some sorting in my testing client. However, as users start to try out SuperDB after their experience with other engines like Postgres they may have expectations about ordering and seek enhancements or docs about what to expect so they know not to file bugs.