Incorrect timestamp handling when filter_pushdown is enabled

[LMnet]

What happens?

It looks like the filter pushdown incorrectly converts dates into timestamps.

This is my DuckDB query:

set mysql_experimental_filter_pushdown=true;

select *
from mysql.infohash_update_log
where updated_on <= '2021-09-30'
limit 100;

The updated_on column has timestamp null type.

As a result I see this error message:

IO Error: Failed to run query "SELECT `updated_on`, `id`, `infohash`, `value` FROM `helpdesk`.`infohash_update_log` WHERE (`updated_on` <= '2021-09-29 11:00:00+00' AND `updated_on` IS NOT NULL) LIMIT 100": Incorrect TIMESTAMP value: '2021-09-29 11:00:00+00'

MySQL itself supports these syntaxes:

-- Date literal
`updated_on` <= '2021-09-29'

-- Timestamp literal
`updated_on` <= '2021-09-29 11:00:00'

I think it somehow relates to #36 But I tried the latest MySQL extension (with force install) and my problem persists:

+--------------+-----------------+--------------+
|extension_name|extension_version|installed_from|
+--------------+-----------------+--------------+
|mysql_scanner |4dd5963          |core          |
+--------------+-----------------+--------------+

To Reproduce

1. Create a MySQL table with a column with timestamp type.
2. Try to run a DuckDB query like

   select *
   from mysql.my_table
   where timestamp_column <= '2021-09-30'; # any date here

OS:

linux

MySQL Version:

8.0.32

DuckDB Version:

1.0.0

DuckDB Client:

Python

Full Name:

Yury Badalyants

Affiliation:

Parrot Analytics

Have you tried this on the latest main branch?

• [X] I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] I agree

[Mytherin]

Thanks for the report! I've pushed a fix in https://github.com/duckdb/duckdb_mysql/pull/84