It's not the LIMIT as much as the OFFSET. Remove the OFFSET and you'll get the correct number of records.

https://sqlime.org/#deta:69lq8zgn152q

Yeah, but I need the OFFSET for paging of the result.

Indeed. I was just 'clarifying' or adding that the OFFSET was causing the hiccup.

The problem bisects to check-in 4b631364354068af, 2015-10-06, version 3.9.0.

Repo case:

.echo on
.mode box
CREATE TABLE employees (
  id integer primary key,
  name text,
  city text,
  department text,
  salary integer
);
INSERT INTO employees VALUES
  (11,'Diane','London','hr',70),
  (12,'Bob','London','hr',78),
  (21,'Emma','London','it',84),
  (22,'Grace','Berlin','it',90),
  (23,'Henry','London','it',104),
  (24,'Irene','Berlin','it',104),
  (25,'Frank','Berlin','it',120),
  (31,'Cindy','Berlin','sales',96),
  (32,'Dave','London','sales',96),
  (33,'Alice','Berlin','sales',100);
CREATE VIEW v AS
  SELECT * FROM (
    SELECT * FROM employees 
     WHERE salary < 100
     ORDER BY salary desc)
   UNION ALL 
   SELECT * FROM (
     SELECT * FROM employees
     WHERE salary >= 100
     ORDER BY salary asc);
SELECT * FROM v LIMIT 5 OFFSET 0;
.testctrl optimizations 1
SELECT * FROM v LIMIT 5 OFFSET 0;

You can copy/paste the above into https://sqlite.org/fiddle to see a demonstration of the problem. The ".testctrl optimizations 1" command turns off the query flattening optimizations, which causes the correct answer to come out.

Simpler test case:

.echo on
.mode box
CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);
WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE x<100)
  INSERT INTO t1(a) SELECT 100-x FROM c;
SELECT * FROM (SELECT * FROM t1 WHERE a<50 ORDER BY a DESC)
UNION ALL
SELECT * FROM (SELECT * FROM t1 WHERE a>=50 ORDER BY a ASC)
LIMIT 5 OFFSET 47;

From this test case, it appears that the 3 rows that are output by the right-hand side of the UNION ALL are not counting toward the total limit. In other words, the limit counter is getting reset before the left-hand side of the UNION ALL is run, so that the left-hand side outputs 5 rows even if the right-hand side has already output one or more rows.

Now fixed on trunk, and on branch-3.39.

The problem was that when certain query re-writes that attempt to improve performance are applied to a query that has all three of the following characteristics:

1. A LIMIT + OFFSET clause
2. A UNION ALL
3. Both arms of the UNION ALL are subqueries that contains ORDER BY

Then the resulting byte-code was not correctly keeping track of the number of rows that had been output and might end up returning more rows than requested by the LIMIT. The rows that are returned were always correct. It would never return too few rows. The only fault is tha more rows than requested might be returned.

This problem has been in the code since 2015.

So it was a bug. Thanks for the quick fix!

Now, only if Android can pick this change... sigh