Jtdylktuy

Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This proved somewhat difficult because they could simply re-run their query seemingly endlessly and it would always return the same set of rows in the same order.

I have noticed this before and all I could really do is insist that they trust me and not simply assume that a database table will behave like a traditional CSV or Excel file.

For example, executing the (PostgreSQL) query

create table mytable (

    id INTEGER PRIMARY KEY,

    data TEXT

);

INSERT INTO mytable VALUES

    (0, 'a'),

    (1, 'b'),

    (2, 'c'),

    (3, 'd'),

    (4, 'e'),

    (5, 'f'),

    (6, 'g'),

    (7, 'h'),

    (8, 'i'),

    (9, 'j');

will create a table with a clear conceptual order. Selecting that same data in the simplest way would be:

SELECT * FROM mytable;

Always gives me the following results:

 id | data 

----+------

  0 | a

  1 | b

  2 | c

  3 | d

  4 | e

  5 | f

  6 | g

  7 | h

  8 | i

  9 | j

(10 rows)

I can do this over and over again and it will always return to me the same data in the same order. However I know that this implicit order can be broken, I have seen it before, particularly in large datasets, where some random value will get apparently thrown up into the "wrong" place when selected. But it has occurred to me that I don't know how this happens or how to reproduce it. I find it difficult to get results on Google because the search query tends to just return general help on sorting result sets.

So, my questions are essentially these:

1. How can I demonstrably and concretely prove that the return order of rows from a query without an ORDER BY statement is not reliable, preferably by causing and showing a breakdown of the implicit order even when the table in question is not updated or edited?




2. Does it make any difference at all if the data is only inserted once en masse and then never updated again?

I would prefer a postgres-based answer since that is the one I am most familiar with but I'm more interested in the theory itself.

I see three ways to try to convince them:

1. Let them try the same query but with bigger table (more number of rows) or when the table is being updated between executions. Or new rows are inserted and some old ones are deleted. Or an index is added or removed between executions. Or the table is vacuumed (in Postgres). Or indexes are rebuilt (in SQL Server). Or the table is changed from clustered to a heap. Or the database service is restarted.




2. You can suggest that they prove that different executions will return the same order. Can they prove it? Can they provide a series of tests that proves that any query will give the result in the same order, no matter how many times it is executed?




3. Provide the documentation of various DBMS in that matter. For example:

PostgreSQL:

Sorting Rows

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

SQL Server:

SELECT - ORDER BY Clause (Transact-SQL)

Sorts data returned by a query in SQL Server. Use this clause to:

Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

Oracle:

order_by_clause

Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

This is the black swan story all over again. If you haven't seen one yet it doesn't mean they don't exist. Hopefully in your case it won't lead to another world wide financial crisis, simply to a few unhappy customers.

Postgres documentation says this explicitly:

If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

"The system" in this case comprises the postgres daemon itself (including implementation of its data access methods and the query optimizer), the underlying operating system, logical and physical layout of the database storage, possibly even CPU caches. Since you as the database user have no control over that stack you should not rely on it continuing to behave forever the way it behaves this very minute.

Your colleagues are committing the hasty generalization fallacy. To disprove their point it is sufficient to show that their assumption is wrong only once, e.g. by this dbfiddle.

Consider the following example, where we have three related tables. Orders, Users, and OrderDetails. OrderDetails is linked with foreign keys to the Orders table and the Users Table. This is essentially a very typical setup for relational databases; arguably the entire purpose of a relational DBMS.

USE tempdb;



IF OBJECT_ID(N'dbo.OrderDetails', N'U') IS NOT NULL

DROP TABLE dbo.OrderDetails;



IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL

DROP TABLE dbo.Orders;



IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL

DROP TABLE dbo.Users;



CREATE TABLE dbo.Orders

(

    OrderID int NOT NULL

        CONSTRAINT OrderTestPK

        PRIMARY KEY

        CLUSTERED

    , SomeOrderData varchar(1000)

        CONSTRAINT Orders_somedata_df

        DEFAULT (CRYPT_GEN_RANDOM(1000))

);



CREATE TABLE dbo.Users

(

    UserID int NOT NULL

        CONSTRAINT UsersPK

        PRIMARY KEY

        CLUSTERED

    , SomeUserData varchar(1000)

        CONSTRAINT Users_somedata_df

        DEFAULT (CRYPT_GEN_RANDOM(1000))

);



CREATE TABLE dbo.OrderDetails

(

    OrderDetailsID int NOT NULL

        CONSTRAINT OrderDetailsTestPK

        PRIMARY KEY

        CLUSTERED

    , OrderID int NOT NULL

        CONSTRAINT OrderDetailsOrderID

        FOREIGN KEY

        REFERENCES dbo.Orders(OrderID)

    , UserID int NOT NULL

        CONSTRAINT OrderDetailsUserID

        FOREIGN KEY

        REFERENCES dbo.Users(UserID)

    , SomeOrderDetailsData varchar(1000)

        CONSTRAINT OrderDetails_somedata_df

        DEFAULT (CRYPT_GEN_RANDOM(1000))

);



INSERT INTO dbo.Orders (OrderID)

SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.syscolumns sc;



INSERT INTO dbo.Users (UserID)

SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.syscolumns sc;



INSERT INTO dbo.OrderDetails (OrderDetailsID, OrderID, UserID)

SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    , o.OrderID

    , u.UserID

FROM sys.syscolumns sc

    CROSS JOIN dbo.Orders o

    CROSS JOIN dbo.Users u

ORDER BY NEWID();



CREATE INDEX OrderDetailsOrderID ON dbo.OrderDetails(OrderID);

CREATE INDEX OrderDetailsUserID ON dbo.OrderDetails(UserID);

Here, we're querying the OrderDetails table where the UserID is 15:

SELECT od.OrderDetailsID

    , o.OrderID

    , u.UserID

FROM dbo.OrderDetails od

    INNER JOIN dbo.Users u ON u.UserID = od.UserID

    INNER JOIN dbo.Orders o ON od.OrderID = o.OrderID

WHERE u.UserID = 15

The output from the query looks like:

╔════════════════╦═════════╦════════╗

║ OrderDetailsID ║ OrderID ║ UserID ║

╠════════════════╬═════════╬════════╣

║        2200115 ║       2 ║     15 ║

║         630215 ║       3 ║     15 ║

║        1990215 ║       3 ║     15 ║

║        4960215 ║       3 ║     15 ║

║         100715 ║       8 ║     15 ║

║        3930815 ║       9 ║     15 ║

║        6310815 ║       9 ║     15 ║

║        4441015 ║      11 ║     15 ║

║        2171315 ║      14 ║     15 ║

║        3431415 ║      15 ║     15 ║

║        4571415 ║      15 ║     15 ║

║        6421515 ║      16 ║     15 ║

║        2271715 ║      18 ║     15 ║

║        2601715 ║      18 ║     15 ║

║        3521715 ║      18 ║     15 ║

║         221815 ║      19 ║     15 ║

║        3381915 ║      20 ║     15 ║

║        4471915 ║      20 ║     15 ║

╚════════════════╩═════════╩════════╝

As you can see, the order of rows output does not match the order of rows in the OrderDetails table.

Adding an explicit ORDER BY ensures rows will be returned to the client in the desired order:

SELECT od.OrderDetailsID

    , o.OrderID

    , u.UserID

FROM dbo.OrderDetails od

    INNER JOIN dbo.Users u ON u.UserID = od.UserID

    INNER JOIN dbo.Orders o ON od.OrderID = o.OrderID

WHERE u.UserID = 15

ORDER BY od.OrderDetailsID;

╔════════════════╦═════════╦════════╗

║ OrderDetailsID ║ OrderID ║ UserID ║

╠════════════════╬═════════╬════════╣

║           3915 ║      40 ║     15 ║

║         100715 ║       8 ║     15 ║

║         221815 ║      19 ║     15 ║

║         299915 ║     100 ║     15 ║

║         368215 ║      83 ║     15 ║

║         603815 ║      39 ║     15 ║

║         630215 ║       3 ║     15 ║

║         728515 ║      86 ║     15 ║

║         972215 ║      23 ║     15 ║

║         992015 ║      21 ║     15 ║

║        1017115 ║      72 ║     15 ║

║        1113815 ║      39 ║     15 ║

╚════════════════╩═════════╩════════╝

If order of rows is imperative, and your engineers know that order is imperative, they should only ever want to use an ORDER BY statement, since it might cost them their designation if there was a failure related to incorrect order.

A second, perhaps more instructive example, using the OrderDetails table from above, where we're not joining any other tables, but have a simple requirement to find rows matching both the OrderID and the UserID, we see the problem.

We'll create an index to support the query, as you would likely do in real life if performance is in any way important (when isn't it?).

CREATE INDEX OrderDetailsOrderIDUserID ON dbo.OrderDetails(OrderID, UserID);

Here's the query:

SELECT od.OrderDetailsID

FROM dbo.OrderDetails od

WHERE od.OrderID = 15

    AND (od.UserID = 21 OR od.UserID = 22)

And the results:

╔════════════════╗

║ OrderDetailsID ║

╠════════════════╣

║          21421 ║

║        5061421 ║

║        7091421 ║

║         691422 ║

║        3471422 ║

║        7241422 ║

╚════════════════╝

Adding an ORDER BY clause will most definitely ensure we get the correct sort here, too.

These mock-ups are just simple examples where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.