Using limit in sqlite SQL statement in combination with order by clause

Question

Will the following two SQL statements always produce the same result set?

 1. SELECT * FROM MyTable where Status='0' order by StartTime asc limit 10

 2. SELECT * FROM (SELECT * FROM MyTable where Status='0' order by StartTime asc) limit 10

Answer 1

No. First because the StartTime column may not have UNIQUE constraint. So, even the first query may not always produce the same result - with itself!

Second, even if there are never two rows with same StartTime, the answer is still negative.

The first statement will always order on StartTime and produce the first 10 rows. The second query may produce the same result set but only with a primitive optimizer that doesn't understand that the ORDER BY in the subquery is redundant. And only if the execution plan includes this ordering phase.

The SQLite query optimizer may (at the moment) not be very bright and do just that (no idea really, we'll have to check the source code of SQLite*). So, it may appear that the two queries are producing identical results all the time. Still, it's not a good idea to count on it. You never know what changes will be made in a future version of SQLite.

I think it's not good practice to use LIMIT without ORDER BY, in any DBMS. It may work now, but you never know how long these queries will be used by the application. And you may not be around when SQLite is upgraded or the DBMS is changed.

(*) @Gareth's link provides the execution plan which suggests that current SQLite code is dumb enough to execute the redundant ordering.