Generates invalid MySQL query

[jelder]

What happens?

duckdb-mysql will generate invalid WHERE clauses for trivial SELECT queries.

To Reproduce

Attach a MySQL database and issue a SELECT query which includes a WHERE clause, ORDER clause, and LIMIT clause. The bug seems to require all three clauses to be present.

attach 'host=1.2.3.4 db=my_db' AS remote_mysql (TYPE mysql, READ_ONLY);

-- This succeeds:
from mysql_query(abacus, 'select * from users where id > 10 order by id limit 10');

-- This fails:
from remote_mysql.users where id > 10 order by id limit 10;

IO Error:
Failed to run query "SELECT `id`, `email`, `name`, `created_at`, `updated_at` FROM 
`my_db`.`users` WHERE (`id` > 10 AND )": You have an error in your SQL syntax; check 
the manual that corresponds to your MySQL server version for the right syntax to use 
near ')' at line 1

OS:

macOS

MySQL Version:

8.0.26-google

DuckDB Version:

v1.2.2 7c039464e4

DuckDB Client:

CLI

Full Name:

Jacob Elder

Affiliation:

Paccurate

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

[jelder]

Additionally, if this error is encountered as part of an insert into my_local_table from query, the DuckDB process will hang indefinitely without printing an error.

[BsoBird]

@Mytherin hello,sir. We are using DuckDB version 1.2.2.0, as this is the last version that supports GCC4, and our production environment still heavily relies on CentOS7 systems. Therefore, we cannot upgrade DuckDB. Could you port this patch to the mysql_scanner corresponding to version 1.2.2.0? If possible, that would be great.

[Mytherin]

We only offer support for the latest version of DuckDB, perhaps you can compile DuckDB from source yourself for your production environment?

[BsoBird]

@Mytherin Thank you for your reply, sir. From which commit ID should I start to split the branch for compilation? I noticed that the main branch is currently adapted to duckdb1.3.2.

[Mytherin]

The release tag has a commit associated with it

[BsoBird]

@Mytherin Sir, although I admit that my wish may be somewhat untimely, there are still too many old operating systems remaining in the actual production environment. Upgrading the versions of these operating systems is a huge hassle, and we may need a long transition period, which makes it difficult for us to freely upgrade the duckdb version in the production environment. It would be great if the community could extend the maintenance period for version 1.2.2.0 (only fixing critical bugs) for some time.

[Mytherin]

You can reach out to DuckDB Labs for commercial support - we could discuss such an option in that context. For the community support, we have no plans of supporting End-of-Life operating systems.