Understanding the Problem: IoTDB Table Model and TAG Columns

In the Apache IoTDB table model, the identity of a time-series sequence is defined by its TAG columns. When you define revision as a TAG in your flow_calibration table:

CREATE TABLE flow_calibration (
  time TIMESTAMP TIME,
  pump_id STRING TAG,
  revision STRING TAG,
  factor DOUBLE FIELD
);

You are telling IoTDB that ('pump_7', 'r1') and ('pump_7', 'r2') are two completely distinct time-series streams. They are not multiple updates to the same entity; they are separate entities altogether.

When you perform an ASOF JOIN on s.pump_id = c.pump_id, IoTDB searches for the closest preceding record for each distinct series matching the join condition. Because both r1 and r2 are valid, distinct time-series streams that match the pump_id and satisfy the timestamp condition (s.time >= c.time), the ASOF JOIN matches both of them. This is why your single sample row is duplicated into two output rows.

How to Resolve the Duplicate Rows

To get exactly one row per sample with the last confirmed revision, you need to reduce the right-side table so that there is only one record per (pump_id, time) combination before performing the ASOF JOIN.

Here are the two best ways to solve this problem.

Solution 1: Use a Subquery with Window Functions (Recommended)

If you need to keep revision as a TAG to preserve the historical records of different revisions at the exact same timestamp, you can use a Common Table Expression (CTE) with ROW_NUMBER() to filter for the latest revision before joining:

WITH latest_calibration AS (
    SELECT time, pump_id, revision, factor
    FROM (
        SELECT time, pump_id, revision, factor,
               ROW_NUMBER() OVER (PARTITION BY pump_id, time ORDER BY revision DESC) as rn
        FROM flow_calibration
    ) t
    WHERE rn = 1
)
SELECT
    s.time,
    s.pump_id,
    s.raw_flow,
    c.revision,
    c.factor,
    s.raw_flow * c.factor AS fixed_flow
FROM flow_samples s
ASOF LEFT JOIN latest_calibration c
    ON s.pump_id = c.pump_id AND s.time >= c.time;

This query ensures that only the "last" revision (sorted alphabetically or by your business logic) is passed to the ASOF JOIN, resulting in exactly one corrected flow row per sample.

Solution 2: Redesign the Schema (If Revisions Happen at Different Times)

If revisions do not actually occur at the exact same millisecond, or if you only care about the most recently written value for a given pump_id at any point in time, you should change revision from a TAG to a FIELD:

CREATE TABLE flow_calibration (
  time TIMESTAMP TIME,
  pump_id STRING TAG,
  revision STRING FIELD, -- Changed from TAG to FIELD
  factor DOUBLE FIELD
);

Why this works: Since pump_id is now the only TAG, there is only one time-series stream per pump. If an operator writes r1 and then r2 at different times, the ASOF JOIN will naturally pick the latest active FIELD values (both revision and factor) without duplicating rows.

Note: If two insertions are made with the exact same timestamp and pump_id, the latter write will overwrite the former because they share the same identity key. If you must support identical timestamps for different revisions, stick to Solution 1.