search

duckdb / duckdb_mysql

https://duckdb.org/docs/extensions/mysql
MIT License
47 stars 11 forks source link

date 0000-00-00 from mysql default null not supported #8

Closed dpavlin closed 10 months ago

dpavlin commented 10 months ago

What happens?

When I try to select data from mysql database I get following error:

Error: Conversion Error: date field value out of range: "0000-00-00", expected format is (YYYY-MM-DD)

To Reproduce

When mysql has null as default for date, query will return 0000-00-00 as date and generate error above.

OS:

Linux

MySQL Version:

5.6.43

DuckDB Version:

0.9.1

DuckDB Client:

cli

Full Name:

Dobrica Pavlinušić

Affiliation:

rot13

Have you tried this on the latest main branch?

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

Mytherin commented 10 months ago

Thanks for the report!

I cannot reproduce this locally, at least in a later version of MySQL. Could you provide a reproducible example?

thbley commented 10 months ago

I'm not sure if it's related, older mysql versions (pre 8.x) allowed 0000-00-00 as date: https://blogs.oracle.com/mysql/post/mysql-80-and-wrong-dates

Screenshot_20231112_185137

Mytherin commented 10 months ago

Looks like this behavior is specific to 5.6 as this has also been fixed in 5.7. I've pushed a fix for this in #11 which just converts invalid values to NULL.