vtab offset to sub selection combine by union has bug

(1) By superzmy on 2024-04-26 01:50:15 [source]

I’ve encountered an issue with the SELECT statement involving a UNION ALL operation on virtual tables when using a LIMIT clause with an OFFSET. Specifically, the query is of the form:

SELECT * FROM ( SELECT some_columns FROM virtual_table1 UNION ALL SELECT some_columns FROM virtual_table2 ) LIMIT A OFFSET B

The problem is that the OFFSET value from the LIMIT clause seems to be applied to the xFilter callback for both SELECT subqueries within the UNION ALL. This behavior is not as expected, since the correct approach should be to apply the OFFSET to the first SELECT subquery only, fetch the required rows, and then apply the remaining LIMIT count to the second SELECT subquery.

This incorrect handling can lead to unexpected results and reduces the efficiency of queries, as both parts of the UNION ALL needlessly skip the initial OFFSET number of rows instead of only the first one doing so.

I believe this is not in line with the intended behavior, as the documentation does not suggest such a distribution of the OFFSET across the UNION ALL components.

To illustrate with an example, if we have A = 10 and B = 5, the expectation is that the query would skip the first 5 rows from virtual_table1, return the next 10 rows (if available), and then if fewer than 10 rows were returned from virtual_table1, it would pick up the remainder from virtual_table2.

(2) By Richard Hipp (drh) on 2024-04-26 19:12:06 in reply to 1 [link] [source]

Should be fixed as of check-in 5f6c079d847e3664. Please verify and report back.

(3) By superzmy on 2024-04-27 07:13:29 in reply to 2 [link] [source]

It seems well. How does it work?

(4) By Richard Hipp (drh) on 2024-04-27 11:13:34 in reply to 3 [link] [source]

The SQLITE_INDEX_CONSTRAINT_LIMIT and SQLITE_INDEX_CONSTRAINT_OFFSET feature that was added by check-in 1e227ad9f413227f on 2022-01-28 contained bugs, such as the one you discovered. We didn't have any test cases that triggered those bugs, and so we never noticed them. Neither did anybody else, apparently, as nobody else has ever before complained.

When you have a query like:

SELECT * FROM (
  SELECT * FROM vtab1
  UNION ALL
  SELECT * FROM vtab2
) LIMIT 10 OFFSET 5;

It is not valid to transform this into:

SELECT * FROM (
  SELECT * FROM (SELECT * FROM vtab1 LIMIT 10 OFFSET 5)
  UNION ALL
  SELECT * FROM (SELECT * FROM vtab2 LIMIT 10 OFFSET 5)
);

But that is what the older code was doing. We simply changed the code so that it doesn't do that any more.