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.