MySQL connector stopped pushdown TIMESTAMP filters

[guyco33]

SHOW CREATE TABLE mysql.test.tab1

CREATE TABLE mysql.test.tab1 (
   id integer NOT NULL,
   created_at timestamp(3)
);

EXPLAIN 
SELECT created_at 
FROM mysql.test.tab1 
WHERE created_at >= now() - interval '1' HOUR 
LIMIT 1

In Trino 352 (pushdown works)

Fragment 0 [SINGLE]
    Output layout: [created_at]
    Output partitioning: SINGLE []
    Stage Execution Strategy: UNGROUPED_EXECUTION
    Output[created_at]
    │   Layout: [created_at:timestamp(3)]
    │   Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}
    └─ RemoteSource[1]
           Layout: [created_at:timestamp(3)]

Fragment 1 [SOURCE]
    Output layout: [created_at]
    Output partitioning: SINGLE []
    Stage Execution Strategy: UNGROUPED_EXECUTION
    TableScan[mysql:test.tab1 test.tab1 limit=1 columns=[created_at:timestamp(3):DATETIME], grouped = false]
        Layout: [created_at:timestamp(3)]
        Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}
        created_at := created_at:timestamp(3):DATETIME

In Trino 353+ (no pushdown)

Fragment 0 [SINGLE]
    Output layout: [created_at]
    Output partitioning: SINGLE []
    Stage Execution Strategy: UNGROUPED_EXECUTION
    Output[created_at]
    │   Layout: [created_at:timestamp(3)]
    │   Estimates: {rows: 1 (9B), cpu: ?, memory: 0B, network: 9B}
    └─ Limit[1]
       │   Layout: [created_at:timestamp(3)]
       │   Estimates: {rows: 1 (9B), cpu: ?, memory: 0B, network: 9B}
       └─ LocalExchange[SINGLE] ()
          │   Layout: [created_at:timestamp(3)]
          │   Estimates: {rows: 1 (9B), cpu: ?, memory: 0B, network: 9B}
          └─ RemoteSource[1]
                 Layout: [created_at:timestamp(3)]

Fragment 1 [SOURCE]
    Output layout: [created_at]
    Output partitioning: SINGLE []
    Stage Execution Strategy: UNGROUPED_EXECUTION
    LimitPartial[1]
    │   Layout: [created_at:timestamp(3)]
    │   Estimates: {rows: 1 (9B), cpu: ?, memory: 0B, network: 0B}
    └─ ScanFilter[table = mysql:test.tab1 test.tab1 columns=[created_at:timestamp(3):DATETIME], grouped = false, filterPredicate = ("created_at" >= TIMESTAMP '2021-03-24 17:08:44.111')]
           Layout: [created_at:timestamp(3)]
           Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}
           created_at := created_at:timestamp(3):DATETIME

[hashhar]

Can you verify if pushdown works on a table with datetime column instead of timestamp?

EDIT: Nevermind, I misread. See https://github.com/trinodb/trino/pull/6893/files#diff-3883710ef20dbb312f5760e3bfaae9358190b8559507ed9f986a0ef8f11fd03fR457-R460.

Unfortunately I don't think there's any way at all to support this without fixing #6910 first.

cc: @findepi

[findepi]

See https://github.com/trinodb/trino/pull/6893/files#diff-3883710ef20dbb312f5760e3bfaae9358190b8559507ed9f986a0ef8f11fd03fR457-R460.

Unfortunately I don't think there's any way at all to support this without fixing #6910 first.

#6910 shouldn't be hard though. @guyco33 do you want to give it a try?

[guyco33]

I would expect that for MySQL columns created as TIMESTAMP without precision (DATETIME precision is 0) pushdown will happen with a millisecond truncation (actually for any table precision that is less than Trino precision)