SQLite Forum

Weird OFFSET and LIMIT behaviour when using VIEW with UNION
Login

Weird OFFSET and LIMIT behaviour when using VIEW with UNION

(1.1) By Pitel (pitlik) on 2022-08-04 11:33:55 edited from 1.0 [source]

I need to ask queries with mixed sorting depending on something, so I created a VIEW for it. But I run SELECT with OFFSET I get more than LIMIT results.

create temp 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;

/* 10 rows */

See for yourself.

The weird think is, that when I use OFFSET with large enough number, so that results from the first sub-query starts running out, it starts behaving correctly, returning only the LIMIT number of rows.

Is this intended behaviour, or did I hit some bug?

This is reproducible on thath site above, and also on my Android 13 phone.

(2.2) By Chris Locke (chrisjlocke1) on 2022-08-04 11:56:49 edited from 2.1 in reply to 1.1 [link] [source]

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

(3) By Pitel (pitlik) on 2022-08-04 11:58:36 in reply to 2.2 [link] [source]

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

(4) By Chris Locke (chrisjlocke1) on 2022-08-04 12:05:35 in reply to 3 [link] [source]

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

(5.1) By Richard Hipp (drh) on 2022-08-04 13:27:12 edited from 5.0 in reply to 1.1 [link] [source]

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.

(6) By Richard Hipp (drh) on 2022-08-04 13:49:57 in reply to 5.1 [link] [source]

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.

(7) By Richard Hipp (drh) on 2022-08-04 17:25:55 in reply to 6 [link] [source]

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.

(8) By Pitel (pitlik) on 2022-08-04 18:09:21 in reply to 7 [link] [source]

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

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