Hi Weihao,
Thanks for the proposal. Push-down is only safe where IoTDB and Trino
give the same answer, so I ran the same queries on IoTDB 2.0.11 (table
model) and Trino 435 to see where they already agree.
They agree on the default null ordering (NULLS LAST for both ASC and
DESC), integer division and modulo, arithmetic overflow and integer
division by zero (both raise an error), double division by zero
(Infinity), VARCHAR ordering, and length/substring counting characters,
not bytes.
They differ in five places:
1. Aggregate result types. In IoTDB, sum() over INT32, INT64 and FLOAT
and avg() over FLOAT all return DOUBLE. In Trino, sum() over integer
and bigint returns bigint, and sum() and avg() over real return real.
For BIGINT the value changes too: the sum of 9007199254740993, 2 and
0 is 9007199254740995 in Trino and 9.007199254740994E15 in IoTDB, and
a sum past the BIGINT range fails in Trino but returns
9.223372036854776E18 in IoTDB.
2. round() on .5. IoTDB rounds half to even, Trino rounds half away
from zero:
round(0.5) IoTDB 0.0 Trino 1.0
round(2.5) IoTDB 2.0 Trino 3.0
round(-0.5) IoTDB -0.0 Trino -1.0
3. CAST(DOUBLE AS INTEGER) on -2.5 gives -2 in IoTDB and -3 in Trino
(2.5 gives 3 in both).
4. substring() past the end. substring('abc', 4) is an empty string in
Trino and an error in IoTDB ("beginPosition must not be greater than
the string length"), so pushing it down could turn an empty value
into a failed query.
5. Time literals follow the session time zone. With the same data,
SELECT count(*) FROM t WHERE time > 1970-01-01T00:00:00.002
returns 2 in a session with zone UTC and 4 in a session with zone
Asia/Shanghai. A numeric predicate such as time > 2 gives the same
result in both. Which zone will the connector use to convert between
IoTDB time values and Trino TIMESTAMP(p)? If time filters are pushed
down as epoch values, the session zone stops mattering.
Best regards,
Zihan
