-- Test without PK, with a windowed sort query.
CREATE TABLE test(i INTEGER, t TIMESTAMP TIME INDEX) WITH('compaction.type'='twcs');

Affected Rows: 0

INSERT INTO test VALUES (1, 1), (NULL, 2), (1, 3);

Affected Rows: 3

ADMIN FLUSH_TABLE('test');

+---------------------------+
| ADMIN FLUSH_TABLE('test') |
+---------------------------+
| 0                         |
+---------------------------+

INSERT INTO test VALUES (2, 4), (2, 5), (NULL, 6);

Affected Rows: 3

ADMIN FLUSH_TABLE('test');

+---------------------------+
| ADMIN FLUSH_TABLE('test') |
+---------------------------+
| 0                         |
+---------------------------+

INSERT INTO test VALUES (3, 7), (3, 8), (3, 9);

Affected Rows: 3

ADMIN FLUSH_TABLE('test');

+---------------------------+
| ADMIN FLUSH_TABLE('test') |
+---------------------------+
| 0                         |
+---------------------------+

INSERT INTO test VALUES (4, 10), (4, 11), (4, 12);

Affected Rows: 3

SELECT * FROM test ORDER BY t LIMIT 5;

+---+-------------------------+
| i | t                       |
+---+-------------------------+
| 1 | 1970-01-01T00:00:00.001 |
|   | 1970-01-01T00:00:00.002 |
| 1 | 1970-01-01T00:00:00.003 |
| 2 | 1970-01-01T00:00:00.004 |
| 2 | 1970-01-01T00:00:00.005 |
+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test ORDER BY t LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@1 ASC NULLS LAST], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@1 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

SELECT * FROM test ORDER BY t DESC LIMIT 5;

+---+-------------------------+
| i | t                       |
+---+-------------------------+
| 4 | 1970-01-01T00:00:00.012 |
| 4 | 1970-01-01T00:00:00.011 |
| 4 | 1970-01-01T00:00:00.010 |
| 3 | 1970-01-01T00:00:00.009 |
| 3 | 1970-01-01T00:00:00.008 |
+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test ORDER BY t DESC LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@1 DESC], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@1 DESC], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

-- Filter on a field.
SELECT * FROM test where i > 2 ORDER BY t LIMIT 4;

+---+-------------------------+
| i | t                       |
+---+-------------------------+
| 3 | 1970-01-01T00:00:00.007 |
| 3 | 1970-01-01T00:00:00.008 |
| 3 | 1970-01-01T00:00:00.009 |
| 4 | 1970-01-01T00:00:00.010 |
+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test where i > 2 ORDER BY t LIMIT 4;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@1 ASC NULLS LAST], fetch=4 REDACTED
|_|_|_SortExec: TopK(fetch=4), expr=[t@1 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|_|_|_FilterExec: i@0 > 2 REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 4_|
+-+-+-+

-- Filter on a field.
SELECT * FROM test where i > 2 ORDER BY t DESC LIMIT 4;

+---+-------------------------+
| i | t                       |
+---+-------------------------+
| 4 | 1970-01-01T00:00:00.012 |
| 4 | 1970-01-01T00:00:00.011 |
| 4 | 1970-01-01T00:00:00.010 |
| 3 | 1970-01-01T00:00:00.009 |
+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test where i > 2 ORDER BY t DESC LIMIT 4;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@1 DESC], fetch=4 REDACTED
|_|_|_SortExec: TopK(fetch=4), expr=[t@1 DESC], preserve_partitioning=[true] REDACTED
|_|_|_FilterExec: i@0 > 2 REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 4_|
+-+-+-+

-- Filter on the time index.
SELECT * FROM test where t > 8 ORDER BY t DESC LIMIT 4;

+---+-------------------------+
| i | t                       |
+---+-------------------------+
| 4 | 1970-01-01T00:00:00.012 |
| 4 | 1970-01-01T00:00:00.011 |
| 4 | 1970-01-01T00:00:00.010 |
| 3 | 1970-01-01T00:00:00.009 |
+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test where t > 8 ORDER BY t DESC LIMIT 4;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@1 DESC], fetch=4 REDACTED
|_|_|_SortExec: TopK(fetch=4), expr=[t@1 DESC], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":2, "mem_ranges":1, "files":1, "file_ranges":1} REDACTED
|_|_|_|
|_|_| Total rows: 4_|
+-+-+-+

DROP TABLE test;

Affected Rows: 0

-- Test with PK, with a windowed sort query.
CREATE TABLE test_pk(pk INTEGER PRIMARY KEY, i INTEGER, t TIMESTAMP TIME INDEX) WITH('compaction.type'='twcs', 'compaction.twcs.trigger_file_num'='4');

Affected Rows: 0

INSERT INTO test_pk VALUES (1, 1, 1), (2, NULL, 2), (3, 1, 3);

Affected Rows: 3

ADMIN FLUSH_TABLE('test_pk');

+------------------------------+
| ADMIN FLUSH_TABLE('test_pk') |
+------------------------------+
| 0                            |
+------------------------------+

INSERT INTO test_pk VALUES (4, 2, 4), (5, 2, 5), (6, NULL, 6);

Affected Rows: 3

ADMIN FLUSH_TABLE('test_pk');

+------------------------------+
| ADMIN FLUSH_TABLE('test_pk') |
+------------------------------+
| 0                            |
+------------------------------+

INSERT INTO test_pk VALUES (7, 3, 7), (8, 3, 8), (9, 3, 9);

Affected Rows: 3

ADMIN FLUSH_TABLE('test_pk');

+------------------------------+
| ADMIN FLUSH_TABLE('test_pk') |
+------------------------------+
| 0                            |
+------------------------------+

INSERT INTO test_pk VALUES (10, 4, 10), (11, 4, 11), (12, 4, 12);

Affected Rows: 3

SELECT * FROM test_pk ORDER BY t LIMIT 5;

+----+---+-------------------------+
| pk | i | t                       |
+----+---+-------------------------+
| 1  | 1 | 1970-01-01T00:00:00.001 |
| 2  |   | 1970-01-01T00:00:00.002 |
| 3  | 1 | 1970-01-01T00:00:00.003 |
| 4  | 2 | 1970-01-01T00:00:00.004 |
| 5  | 2 | 1970-01-01T00:00:00.005 |
+----+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test_pk ORDER BY t LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@2 ASC NULLS LAST], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@2 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE (files.*) REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE VERBOSE SELECT * FROM test_pk ORDER BY t LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@2 ASC NULLS LAST], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@2 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, {"partition_count":{"count":4, "mem_ranges":1, "REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

SELECT * FROM test_pk ORDER BY t DESC LIMIT 5;

+----+---+-------------------------+
| pk | i | t                       |
+----+---+-------------------------+
| 12 | 4 | 1970-01-01T00:00:00.012 |
| 11 | 4 | 1970-01-01T00:00:00.011 |
| 10 | 4 | 1970-01-01T00:00:00.010 |
| 9  | 3 | 1970-01-01T00:00:00.009 |
| 8  | 3 | 1970-01-01T00:00:00.008 |
+----+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test_pk ORDER BY t DESC LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@2 DESC], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@2 DESC], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

-- Filter on a pk column.
SELECT * FROM test_pk where pk > 7 ORDER BY t LIMIT 5;

+----+---+-------------------------+
| pk | i | t                       |
+----+---+-------------------------+
| 8  | 3 | 1970-01-01T00:00:00.008 |
| 9  | 3 | 1970-01-01T00:00:00.009 |
| 10 | 4 | 1970-01-01T00:00:00.010 |
| 11 | 4 | 1970-01-01T00:00:00.011 |
| 12 | 4 | 1970-01-01T00:00:00.012 |
+----+---+-------------------------+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE SELECT * FROM test_pk where pk > 7 ORDER BY t LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@2 ASC NULLS LAST], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@2 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":4, "mem_ranges":1, "files":3, "file_ranges":3} REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE (files.*) REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
EXPLAIN ANALYZE VERBOSE SELECT * FROM test_pk where pk > 7 ORDER BY t LIMIT 5;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [t@2 ASC NULLS LAST], fetch=5 REDACTED
|_|_|_SortExec: TopK(fetch=5), expr=[t@2 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|_|_|_SeqScan: region=REDACTED, {"partition_count":{"count":4, "mem_ranges":1, "REDACTED
|_|_|_|
|_|_| Total rows: 5_|
+-+-+-+

DROP TABLE test_pk;

Affected Rows: 0

