[Bug] Query returns duplicated and out-of-order rows when seq TsFiles overlap #19020

Summary

A sequence (seq) TsFile can be created whose time range overlaps an existing seq TsFile of the same series. Once that state exists, SeriesScanUtil silently returns duplicated and out-of-order rows for a plain SELECT (no aggregation involved, no exception raised).

The overlapping seq file is produced by the engine itself through INSERT INTO ... SELECT, so no manual file manipulation, no crash, and no abnormal configuration is required.

Environment

• Apache IoTDB 2.0.11-SNAPSHOT, master @ ba287a9cb4
• Single node, table model
• max_number_of_points_in_page=10 (only to create a multi-page chunk from a small data set; the default is 10000, so in production the same scenario needs >10000 points in one seq file, which is trivially reached)

Reproduction (pure SQL)

CREATE DATABASE test;
USE test;
CREATE TABLE dst (id string tag, s int32 field);
CREATE TABLE src (id string tag, s int32 field);

-- (1) create seq file S1 covering [10,39]; 15 is intentionally omitted
INSERT INTO dst (time, id, s) VALUES
 (10,'x',10),(11,'x',11),(12,'x',12),(13,'x',13),(14,'x',14),
 (16,'x',16),(17,'x',17),(18,'x',18),(19,'x',19),(20,'x',20),
 (21,'x',21),(22,'x',22),(23,'x',23),(24,'x',24),(25,'x',25),
 (26,'x',26),(27,'x',27),(28,'x',28),(29,'x',29),(30,'x',30),
 (31,'x',31),(32,'x',32),(33,'x',33),(34,'x',34),(35,'x',35),
 (36,'x',36),(37,'x',37),(38,'x',38),(39,'x',39);
FLUSH;

INSERT INTO src (time, id, s) VALUES (100,'x',1),(15,'x',2);

-- (2) the child operator emits rows in nested-loop order [100, 15],
--     which makes the tablet below out of time order
INSERT INTO dst (SELECT b.time, b.id, b.s FROM src a, src b
                 WHERE a.id='x' AND b.id='x' AND a.time<>b.time);
FLUSH;

SELECT time, s FROM dst WHERE id='x';

Note: ORDER BY inside INSERT INTO ... SELECT is optimized away and UNION ALL as an insert source silently inserts 0 rows, so the join form above is used to obtain a deterministic, unsorted tablet. A fresh database is required, because HashLastFlushTimeMap may degrade to partition-level flush times and then a new device inherits lastFlushTime from other devices (everything goes to unseq and the issue does not reproduce).

Expected

31 rows, strictly increasing by time:

10 11 12 13 14 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 100

Actual

31 rows, but 15 is emitted after 30 instead of between 14 and 16:

10 11 12 13 14 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 15 31 32 33 34 35 36 37 38 39 100

The overlap is not handled "never" but "too late": once P3[31,39] becomes the head of the page list, the page of the overlapping file (P4[15,100]) is detected and merged with it, so 15 is emitted before 31. By that time the earlier pages have already been returned, so the overlap between P4 and P1[10,20] / P2[21,30] can no longer be merged or de-duplicated.

If the variant where 15 already exists in S1 (i.e. (15,'x',15) in step 1 and (15,'x',2) in the src insert) is used, the query returns both versions of timestamp 15 (s=15 and s=2), i.e. de-duplication across files also breaks.

On disk, the device's region then contains two seq TsFiles whose range for the same series overlaps ([10,39] and [15,100]).

Root cause

1. INSERT INTO ... SELECT builds a tablet in the child operator's output order without sorting:
   TableInsertTabletStatementGenerator.processTsBlock()
   copies rows verbatim. The storage layer, however, documents that it assumes the batch has been sorted by the client (InsertTabletNode.checkTTLInternal, "assume that batch has been sorted by client").

2. DataRegion.split() latches isSequence:
   DataRegion.java#L1268-L1296
   once a row is classified as sequence, every following row of the same device is treated as sequence as well, regardless of its timestamp. With the tablet [100, 15], row 15 (which is older than the device's last flush time) is therefore written into a sequence TsFile, producing an overlap.

3. SeriesScanUtil discovers an overlapping page only when it reaches the head of its page list, which is too late to merge it with pages already returned:
   unpackAllOverlappedChunkMetadataToPageReaders()
   appends the pages of every overlapping seq chunk to the append-only seqPageReaders list, while
   firstPageOverlapped()
   only compares the current page with seqPageReaders.get(0). A page of the second (overlapping) file therefore becomes visible only when it reaches the head of that list.

   With P1[10,20] P2[21,30] P3[31,39] from S1 and P4[15,100] from S2, the ascending check isOverlapped(left, right) = left.getEndTime() >= right.getStartTime() gives:

   current page	head of list	check	outcome
   P1[10,20]	P2[21,30]	20 >= 21 false	returned directly
   P2[21,30]	P3[31,39]	30 >= 31 false	returned directly
   P3[31,39]	P4[15,100]	39 >= 15 true	P3 and P4 merged

   So P4 is merged — but only after P1 and P2 have already been emitted. The reader cannot retract them, so the overlap of P4 with P1/P2 is lost, and 15 lands after 30.

   A unit test with f1 = [10,19][20,29][30,39] and f2 = [25,27] returns 33 points instead of 30, with 25 26 27 repeated after 29.

Suggested fix (in priority order)

1. DataRegion.split(): drop the isSequence latch and re-evaluate time > lastFlushTime per row, or sort the tablet by (device, time) before splitting. This removes the root cause and also closes every other ingress that can produce overlapping seq files.
2. TableIntoOperator: sort rows by (device, time) before building the insert tablet, so the documented "sorted by client" precondition holds for the engine's own SQL path.
3. SeriesScanUtil (defensive): select the page reader with the smallest start time instead of always seqPageReaders.get(0), or at least assert the ordering invariant, so that an overlap is detected while the earlier pages can still be merged into the same reader.

Impact

Silent wrong query results (duplicated rows and broken time order) on a plain SELECT; no error, no warning in the log. Any client that assumes ORDER BY time-equivalent ordering of a raw series scan will produce incorrect downstream results.