[I] Equality comparison between TIMESTAMP column and epoch_ms literal silently returns no rows in multi-stage engine [pinot]

["piby180 (via GitHub)" <gi...@apache.org>]

piby180 opened a new issue, #18881:
URL: https://github.com/apache/pinot/issues/18881

   ### Summary
   
   After upgrading from 1.5.0 to a recent 1.6.0-SNAPSHOT, equality comparison between a `TIMESTAMP` column and an epoch-millis `LONG` literal silently returns **no rows** in the **multi-stage engine** when the literal has millisecond precision. This used to work in 1.5.0.
   
   ### Possible Root cause
   
   PR #18396  changed `PinotTypeCoercion.binaryComparisonCoercion()` so that for `tsColumn <op> bigintLiteral`, the cast is now placed on the **literal** side (`tsColumn <op> CAST(literal AS TIMESTAMP)`) instead of on the column side (`CAST(tsColumn AS BIGINT) <op> literal`), to keep the column unwrapped.
   
   The new cast is created without a precision:
   
   ```java
   // pinot-query-planner/.../validate/PinotTypeCoercion.java
   return coerceOperandType(binding.getScope(), binding.getCall(), intIdx,
       factory.createSqlType(SqlTypeName.TIMESTAMP));   // no precision -> Calcite default = 0
   ```
   
   Pinot's `TypeSystem.getDefaultPrecision()` only overrides `DECIMAL`, so `TIMESTAMP` falls through to Calcite's default precision of **0 (seconds, no
   fractional part)**. When the literal cast is constant-folded, the sub-second portion is truncated:
   
   ```
   1761667561482  ->  TIMESTAMP '2025-10-28 14:06:01'  ->  1761667561000
   ```
   
   Stored rows hold `...561482`; the folded literal is `...561000`, so the predicate never matches.
   
   This is consistent with the existing tests passing: `TypeCoercion.json` and `PinotTypeCoercionTest` only use **whole-second** literals (e.g. `TS_LITERAL_MS = 1746022135000L` = `2025-04-30 14:08:55.000`), so the precision-0 truncation is invisible. No test covers a sub-second epoch literal.
   
   ### Schema
   
   ```json
   {
     "name": "processing_time",
     "dataType": "TIMESTAMP",
     "format": "1:MILLISECONDS:EPOCH",
     "granularity": "1:MILLISECONDS"
   }
   ```
   
   ### Steps to reproduce (multi-stage engine)
   
   Assume a row exists where `processing_time = 1761667561482` (`2025-10-28 14:06:01.482Z`).
   
   **Returns no rows (regression):**
   ```sql
   SELECT * FROM tbl WHERE processing_time = 1761667561482;
   SELECT * FROM tbl WHERE processing_time = fromTimestamp(fromISO8601('2025-10-28T14:06:01.482Z'));
   ```
   
   **Works (workarounds):**
   ```sql
   -- forces the old column->LONG path
   SELECT * FROM tbl WHERE CAST(processing_time AS LONG) = 1761667561482;
   
   -- normalizes both sides to the same granularity
   SELECT * FROM tbl
   WHERE datetrunc('millisecond', processing_time)
       = datetrunc('millisecond', fromTimestamp(fromISO8601('2025-10-28T16:06:01.482000Z')));
   ```
   
   ### Expected behavior
   
   `processing_time = 1761667561482` should match rows whose stored millis equal
   `1761667561482`, preserving millisecond precision — as it did in 1.5.0.
   
   ### Actual behavior
   
   The BIGINT literal is cast to a precision-0 `TIMESTAMP` and constant-folded, truncating sub-second milliseconds; the equality silently matches nothing.
   
   ### Suggested fix
   
   Preserve millisecond precision on the implicit cast. Either:
   
   1. In `PinotTypeCoercion`, build the cast type with precision 3:
      ```java
      factory.createSqlType(SqlTypeName.TIMESTAMP, 3)
      ```
      (apply to both the literal->TIMESTAMP coercion and, for symmetry, anywhere a
      TIMESTAMP type is synthesized for comparison/folding), **or**
   
   2. Override `TypeSystem.getDefaultPrecision(TIMESTAMP)` to return `3`, since
      Pinot's execution type for `TIMESTAMP` is millisecond-precision `LONG`.
   
   ### Environment
   
   - Affected: 1.6.0-SNAPSHOT (commit `dc4e957717`, and any build containing #18396 / `a915903593`)
   - Not affected: 1.5.0
   - Engine: multi-stage (single-stage engine is unaffected — it treats TIMESTAMP/LONG as interchangeable longs)
   
   cc @yashmayya 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org