EN Pagination and Native Queries

Home · GitHub

English | 简体中文

TsGate 2.1.0: This guide uses com.alandevise.tsgate.*. Upgrading from 2.0.0 requires updating imports, reflection names and package scanning, then recompiling. Central 2.0.0 retains com.alandevise.tsdb.*. See migration steps and release status.

The validation, configuration-snapshot and startup-failure-policy fixes documented here are included in 2.1.0. See 2.1.0 change history.

Time-cursor pagination

Omit cursorTime on the first page:

PageResult<AccrueRecord> firstPage = tgTemplate.query(AccrueRecord.class)
        .database("tsdb")
        .whereTag("source", "annotation-pojo")
        .timeRange(startTime, endTime)
        .orderByTimeAsc()
        .limit(100)
        .page();

Pass the previous page's nextCursorTime for the second page:

PageResult<AccrueRecord> secondPage = tgTemplate.query(AccrueRecord.class)
        .database("tsdb")
        .whereTag("source", "annotation-pojo")
        .timeRange(startTime, endTime)
        .cursorTime(firstPage.getNextCursorTime())
        .orderByTimeAsc()
        .limit(100)
        .page();

Ascending pagination adds time > cursorTime; descending pagination adds time < cursorTime. If rows sharing one timestamp cross a page boundary, the next page skips the remaining rows with that timestamp. IoTDB / InfluxDB 3 can use strictCursorPage() instead. InfluxDB 1.x does not support composite cursors; use offset pagination or ensure timestamps are unique.

Composite-cursor pagination

This section applies to IoTDB and InfluxDB 3. InfluxDB 1.x returns UNSUPPORTED_OPERATION for strictCursorPage().

Omit cursor(...) on the first page:

PageResult<AccrueRecord> firstPage = tgTemplate.query(AccrueRecord.class)
        .database("tsdb")
        .whereTag("source", "annotation-pojo")
        .timeRange(startTime, endTime)
        .orderByTimeAsc()
        .limit(100)
        .strictCursorPage();

Pass the previous page's nextCursor for the second page:

PageResult<AccrueRecord> secondPage = tgTemplate.query(AccrueRecord.class)
        .database("tsdb")
        .whereTag("source", "annotation-pojo")
        .timeRange(startTime, endTime)
        .cursor(firstPage.getNextCursor())
        .orderByTimeAsc()
        .limit(100)
        .strictCursorPage();

By default, strictCursorPage() uses time + @TGTag columns as its composite cursor. For the annotated POJO example, these are time/device_code/point_type/source. Lexicographic predicates prevent remaining tag rows at the same time from being skipped on the next page.

Strict cursors also support mixed ordering:

PageResult<AccrueRecord> page = tgTemplate.query(AccrueRecord.class)
        .orderByTimeDesc()
        .thenByFieldAsc("device_code")
        .thenByFieldDesc("point_type")
        .cursor(previousCursor)
        .limit(100)
        .strictCursorPage();

The template also appends the missing source DESC key. The next-page predicate compares keys lexicographically according to each direction, reaching source only when all earlier keys are equal. If cursorColumns(...) is explicitly set, its columns and order must exactly match the custom ordering before completion; missing keys are then added to both.

Completion applies to SELECT, ORDER BY and the returned cursor. Even explicit cursorColumns cannot omit these keys.

var page = tgTemplate.query(AccrueRecord.class)
        .orderByFieldAsc("value").limit(1).strictCursorPage();
// Break equal-value ties using value, time, device_code, point_type and source.
var next = tgTemplate.query(AccrueRecord.class)
        .orderByFieldAsc("value").cursor(page.getNextCursor()).limit(1).strictCursorPage();

Legacy cursors containing only a custom FIELD lack the added keys and must be replaced by restarting from the first page. Missing keys produce an explicit error rather than continuing pagination that could skip data.

Annotated query fields match only the annotation's physical column name, case-insensitively, consistently with writing. A NULL physical column never falls back to another column matching the Java field name. Use an unannotated DTO for query aliases; it retains same-name, snake_case-to-camelCase and common time-alias matching.

Exact cursor columns and time values

Pass the returned nextCursor unchanged. Strict pagination uses the physical column names of the selected backend: InfluxDB 3 preserves case (value, VALUE, Time and time can identify different columns), while IoTDB unquoted column names are normalized with Locale.ROOT to lowercase. The core SPI method normalizeColumnIdentifier defaults to preserving case for other adapters.

For select("VALUE").orderByFieldDesc("value"), the template includes the real value in its internal projection and reads that exact value into the cursor. It does not substitute a similarly spelled column. Backend cursor-column absence produces QUERY_ERROR; invalid cursor keys or mismatched explicit cursor/order definitions produce ARGUMENT_ERROR. The actual time key must also be present; loose DTO time aliases are not substitutes for strict cursor keys. SELECT completion, tie-break keys, n+1 probing and query budgets continue to apply. This strict-pagination rule is separate from the ordinary DTO mapping behavior above.

IoTDB cursor time values are integral epoch milliseconds within the signed-long range. 1.0 is accepted, while 1.9, NaN, infinity and out-of-range integers are rejected with ARGUMENT_ERROR before SQL execution. Integer strings and Instant values retain their documented millisecond behavior; use Long when serializing numeric cursors so precision is not lost upstream. This does not add nanosecond pagination or a cross-page database snapshot.

Every returned strict-page row, including the last page, must contain non-null values for every final cursor column; otherwise the template fails with QUERY_ERROR instead of returning a page with an unusable continuation. InfluxDB 3 preserves real _time and TIME fields independently of time, including an explicit null _time; the compatibility _time alias is added only when that key is absent. Missing, additional or duplicated normalized input cursor keys fail with ARGUMENT_ERROR.

A null cursor map or an empty map starts the first strict page. A nonempty map is preserved until physical-key validation: null/blank keys, null values, additional keys (including additional null-valued keys), and keys that become duplicates after trimming or backend normalization produce ARGUMENT_ERROR before querying. For example, {time: null} never silently restarts at page one, and {time: 1, " time ": 2} never silently selects one boundary. InfluxDB 3 still distinguishes case-different physical keys; IoTDB applies its lowercase identity rule. Pass the returned cursor unchanged. Valid surrounding whitespace is normalized during query validation without modifying the caller's query; it must not hide invalid entries or collisions. For direct adapter queries, explicit cursorColumns defines the exact key set; omitting it retains backend fallback rules rather than adding entity metadata.

InfluxDB 3 cursor SQL strategies

InfluxDB 3 has a backend-wide tsdb.influxdb.strict-cursor-sql setting. The default or preserves the existing single lexicographic OR condition. Explicit union-all renders structured strict-cursor continuation as one SQL statement containing mutually exclusive branches. An initial strictCursorPage() without a cursor uses the same ordinary SELECT in either mode. IoTDB is unaffected, and InfluxDB 1.x still rejects strict composite cursors.

For a simplified two-key time ASC, device ASC cursor, the next page after (2026-06-11T03:10:00Z, b) has this shape. The example uses a business filter and a two-row page, so the outer limit includes one probe row:

SELECT "time", "device", "value" FROM (
  SELECT "time", "device", "value" FROM "telemetry"
  WHERE "source" = 'sensor' AND "time" > timestamp '2026-06-11T03:10:00Z'
  UNION ALL
  SELECT "time", "device", "value" FROM "telemetry"
  WHERE "source" = 'sensor' AND "time" = timestamp '2026-06-11T03:10:00Z'
    AND "device" > 'b'
) AS "__tsgate_cursor_rows"
ORDER BY "time" ASC, "device" ASC
LIMIT 3;

Every branch uses the same projection, table, original business predicates and time bounds. Branch i requires equality on all preceding cursor keys and a strict comparison on key i: > for ASC and < for DESC. For value DESC, device ASC, time DESC, the three branches are value < V, value = V AND device > D, and value = V AND device = D AND time < T, each combined with all original filters. This also handles FIELD-first orderings; time need not be the first key.

The branches cannot overlap, so UNION ALL preserves the original predicate's rows without a separate deduplication step. Complete mixed-direction sorting and the page limit apply after the union; an offset supplied directly through TSDBQuery is also applied outside the union, never independently to its branches. Branches proven empty by the cursor time and explicit timeRange / startTime / endTime bounds are omitted to avoid contradictory time predicates. Native probes reproduced the planner error on both Core 3.0.3 and 3.11.5, so this is not limited to old servers. This pruning does not infer arbitrary QueryFilter conditions or rewrite native SQL; all cursor values are still validated. If all branches are empty, a query with an explicit false predicate still goes to the server; ordinary database, lifecycle and query-error handling is retained. The template still completes missing time/tag keys in the projection, ordering and returned cursor. Explicit projections must therefore retain the keys the template adds; a direct adapter-level TSDBQuery does not gain entity metadata automatically. For direct queries, any sort keys absent from an explicit projection are added only inside the UNION branches, leaving the requested outer SQL projection unchanged; hidden cursor keys are not added to its output columns.

There is still one SQL request per continuation page, but m final cursor keys produce at most m branches and may require repeated scans and an outer sort. Use bounded time ranges, selective filters and reasonable page sizes; a small returned page does not bound server-side work. Both strategies retain max-query-rows, the single next-page probe row and max-query-response-bytes; failures do not become partial successful pages.

Strict cursors combined with aggregation are rejected with UNSUPPORTED_OPERATION in union-all mode, including on the first page. This setting does not rewrite ordinary time-cursor, list, count, aggregate, offset or native queries, and is not a workaround for arbitrary OR expressions supplied through native SQL. There is no automatic version detection, strategy switch or HTTP 500 retry. Choose the explicit server/strategy combinations in compatibility and validation.

Both strategies require complete, non-null cursor values and a stable total ordering. Keep all identity tags in the cursor; changing data between pages is not protected by a cross-page snapshot. The shared timestamp contract remains milliseconds; selecting union-all does not add nanosecond-precision cursor support.

Limit/offset pagination

PageResult<AccrueRecord> page = tgTemplate.query(AccrueRecord.class)
        .database("tsdb")
        .whereTag("source", "annotation-pojo")
        .timeRange(startTime, endTime)
        .orderByTimeAsc()
        .page(pageNum, pageSize);

Long total = page.getTotal();           // Total matching rows
Long totalPages = page.getTotalPages(); // Total pages for the page size

For ordinary detail queries, IoTDB / InfluxDB 3 use a direct COUNT(*); grouped/aggregate pagination wraps the unpaginated aggregate result in an outer COUNT(*). InfluxDB 1.x counts rows by streaming the corresponding query response, subject to max-query-response-bytes.

Time-cursor and composite-cursor pagination do not issue a total-count query: total and totalPages are both null. Prefer cursor pagination for deep traversal of large datasets. Offset pagination is better suited to small result sets or management views that need total rows and pages.

Native queries

executeQuery(...) executes native queries. It does not accept a database argument or rewrite application SQL to add database names.

Before sending a request, the InfluxDB 1.x native-query entry point permits only a single SELECT, SHOW or EXPLAIN [ANALYZE] SELECT, with one optional trailing semicolon. It rejects unquoted INTO, multiple statements and mutation/administrative commands. Keywords, semicolons and escaped quotes inside quoted text are preserved correctly. To avoid lexical ambiguity, comments, backticks and unquoted /, including regular expressions and division, are unsupported. Use the borrowed official client directly for such advanced syntax and manage its read/write semantics yourself. InfluxDB 1.x GET /query does not reliably prevent mutations; the HTTP method cannot replace this validation.

The local OpenGemini in 2.1.0 uses the same single-statement InfluxQL entry-point restrictions. Only structured common query(...) and count(...), with an explicit measurement, normalize the exact measurement not found query error to empty rows or zero. Native executeQuery(...) preserves query errors because a single statement may contain multiple measurements or subqueries. Borrowed compatible-client queries retain the server error DTO and bypass adapter limits. The stable org.influxdb.InfluxDB proxy bridges only ping() / version() to the real X-Geminidb-Version header. New measurement/tag-series visibility after acknowledgment is asynchronous; establish the expected point's visibility with a deadline before paginating when required. Adapter queries do not transparently retry empty results. See OpenGemini integration.

Both native SQL and fluent queries obey adapter result limits. Native queries exceeding max-query-rows, default 10000, throw QUERY_ERROR instead of returning a truncated success. Time-cursor / composite-cursor pagination may read one additional probe row to determine whether another page exists; ordinary lists and offset pages receive no extra allowance. Application limit/pageSize remains restricted to 1–10000, and actual results must also fit the selected backend's max-query-rows.

IoTDB reads incrementally, closing the result handle and returning the Session when the limit is exceeded. InfluxDB parses JSON incrementally and limits the decompressed response body to 16 MiB by default, including whitespace and syntax characters, without first loading the whole HTTP body into memory. Failure never returns collected partial rows. Error responses are read only up to 8 KiB. Use a time range and LIMIT in SQL as well; client protection does not impose a database execution-resource quota.

These entry points still return bounded Lists. There is no unbounded streaming-export API for callers. Direct official-client users must manage their own pagination, memory and close timing.

IoTDB example:

List<AccrueRecord> rows = tgTemplate.executeQuery(
        "SELECT * FROM tsdb.ACCRUE "
                + "WHERE source = 'annotation-pojo' ORDER BY time DESC LIMIT 5",
        AccrueRecord.class
);

InfluxDB example:

List<AccrueRecord> rows = tgTemplate.executeQuery(
        "SELECT * FROM \"ACCRUE\" WHERE \"source\" = 'annotation-pojo' ORDER BY time DESC LIMIT 5",
        AccrueRecord.class
);

Omit the result type to receive List<Map<String, Object>>:

List<Map<String, Object>> rows = tgTemplate.executeQuery(
        "SELECT * FROM tsdb.ACCRUE LIMIT 5"
);

Common native SQL patterns for the IoTDB table model:

-- List tables
SHOW TABLES FROM tsdb;

-- Describe the table
DESCRIBE tsdb.ACCRUE;

-- Query rows
SELECT time, device_code, point_type, source, value, status
FROM tsdb.ACCRUE
WHERE time >= 1781147400000
  AND time <= 1781148600000
  AND device_code = 'device_001'
ORDER BY time DESC
LIMIT 20;

-- LIMIT/OFFSET pagination
SELECT *
FROM tsdb.ACCRUE
WHERE source = 'annotation-pojo'
ORDER BY time ASC
LIMIT 100
OFFSET 200;

-- Aggregate fixed-offset time windows
SELECT date_bin(5m, time, 1970-01-01T00:00:00+08:00) AS window_start,
       device_code,
       AVG(value)                                    AS avg_value,
       MAX(value)                                    AS max_value,
       COUNT(value)                                  AS sample_count
FROM tsdb.ACCRUE
WHERE time >= 1781147400000
  AND time <= 1781148600000
GROUP BY date_bin(5m, time, 1970-01-01T00:00:00+08:00),
         device_code
ORDER BY window_start ASC, device_code ASC
LIMIT 100;

Common native SQL patterns for InfluxDB 3 Core:

-- List tables
SHOW TABLES;

-- List columns
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'ACCRUE';

-- Query rows; quote case-sensitive InfluxDB table and column names
SELECT time, "device_code", "point_type", "source", "value", "status"
FROM "ACCRUE"
WHERE time >= timestamp '2026-06-11T03:10:00Z'
  AND time <= timestamp '2026-06-11T03:30:00Z'
  AND "device_code" = 'device_001'
ORDER BY time DESC
LIMIT 20;

-- LIMIT/OFFSET pagination
SELECT *
FROM "ACCRUE"
WHERE "source" = 'annotation-pojo'
ORDER BY time ASC
LIMIT 100
OFFSET 200;

-- Aggregate fixed-offset time windows
SELECT date_bin(interval '5 minutes', time, timestamp '1970-01-01T00:00:00+08:00') AS window_start,
       "device_code",
       AVG("value")                                                                AS "avg_value",
       MAX("value")                                                                AS "max_value",
       COUNT("value")                                                              AS "sample_count"
FROM "ACCRUE"
WHERE time >= timestamp '2026-06-11T03:10:00Z'
  AND time <= timestamp '2026-06-11T03:30:00Z'
GROUP BY window_start, "device_code"
ORDER BY window_start ASC, "device_code" ASC
LIMIT 100;

---

← Fluent queries and aggregation · Mapping and backend limits →

OpenGemini in 2.1.0

TsGate 2.1.0 includes the independent OpenGemini adapter and starter. See OpenGemini integration for configuration, default-engine limits and exact-version testing, and 2.1.0 release notes for migration requirements.