Thanks for the response and clarifications!

I think this scenario would illustrate it.

Make a table with one column, x, and insert into it rows with values 1-5, and then 8-20.

Then query it using more or less `SELECT x FROM (SELECT x FROM xs LIMIT 15 ORDER BY x) LIMIT 10`, and then insert 6 into the table. Output should be 1-6, 8-11. Of course as long as the limits aren't merged together during optimisation, that would make the test-case moot.

Good luck with your product!

Thanks! Looks like that works.

Here is the query I set up on try.feldera.com.

  CREATE TABLE foo (x INTEGER NOT NULL PRIMARY KEY) WITH ('materialized' = 'true') ;

  CREATE MATERIALIZED VIEW bar AS SELECT x FROM (SELECT x FROM foo ORDER BY x LIMIT 15) LIMIT 10;

I then used our CLI tool fda to insert some rows and inspect the states after starting the pipeline: https://docs.feldera.com/reference/cli

  try.feldera.com/foo> select * from foo;

  +----+
  | x  |
  +----+
  | 1  |
  | 2  |
  | 3  |
  | 4  |
  | 5  |
  | 8  |
  | 9  |
  | 10 |
  | 11 |
  | 12 |
  | 13 |
  | 14 |
  | 15 |
  | 16 |
  | 17 |
  | 18 |
  | 19 |
  | 20 |
  +----+

  try.feldera.com/foo> insert into foo values (6);

  +-------+
  | count |
  +-------+
  | 1     |
  +-------+

  try.feldera.com/foo> select * from bar;

  +----+
  | x  |
  +----+
  | 1  |
  | 2  |
  | 3  |
  | 4  |
  | 5  |
  | 6  |
  | 8  |
  | 9  |
  | 10 |
  | 11 |
  +----+

Awesome, thanks for double-checking!