search

duckdb / duckdb_mysql

MIT License
45 stars 10 forks source link

Extension is not sending the original query to MySQL #31

Open ulissescappato opened 7 months ago

ulissescappato commented 7 months ago

What happens?

I'm trying to update a DuckDB database with only incremental data, but the extension searches the entire table in the source table (MySQL).

To Reproduce

Dbeaver:

ATTACH '...' AS mysql_test (TYPE mysql_scanner, READ_ONLY); SELECT * FROM mysql_test.sales WHERE id > 10000000

MySQL:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command = 'Query' order by time desc

Listed query:

SELECT id, product, total FROM schema_test.sales

Note that the query running in MySQL does not have the 'WHERE' clause.

OS:

Windows 11

MySQL Version:

8.0.32

DuckDB Version:

0.9.2

DuckDB Client:

DBeaver

Full Name:

Ulisses

Affiliation:

Developer

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 7 months ago

Thanks for the report! Filter pushdown is not enabled by default currently, try SET mysql_experimental_filter_pushdown=true and re-running.

ulissescappato commented 7 months ago

Thanks for the report! Filter pushdown is not enabled by default currently, try SET mysql_experimental_filter_pushdown=true and re-running.

Thanks, Mark!

rlancer commented 5 months ago

Thanks for your work on the extension!

Found https://github.com/duckdb/duckdb_mysql/pull/50

Should loading the entire table be the desired behavior? My use case is connecting many tables together from different data sources, these tables are way larger than I could fit in memory

My initial query which didn't work:

`select * from mysql.table limit = 10` still fails for me even with `SET mysql_experimental_filter_pushdown=true`

`where in (list of ids...)` does work

Note I am running these queries on PlantScale which fails after 100k results