Limit on inner select does not seem to be applied by SQLite

The following SQLite query is fast (with sqlite 3.7.5):

sqlite> select distinct DOB from (select * from MyTable limit 3) limit 20;
1958-11-05
1959-01-01
1963-06-07

while the following one 开发者_JS百科is slow (limit 20 was removed):

sqlite> select distinct DOB from (select * from MyTable limit 3);
1933-01-03
1934-12-24
1935-01-07

I find this surprising, because I expect the inner limit to quickly extract only 3 rows, so that the external limit 20 really does not matter; however, the timings of both queries are quite different. Why is the first query much faster?

SQLite has some bugs around folding subqueries when the outer query involves DISTINCT. The LIMIT is migrated to the outer query, which then require DISTINCT to be resolved before it can produce 3 records that are not all the same (due to distinct).

Related: SQLite outer query is returning results not found in inner query

The workaround (using OFFSET to avoid the folding) could work for your scenario as well.