Insert/Update queries don't work on foreign tables which require quoting

[mattico]

I have a mariadb database called csv-test, which was wrapped in foreign tables with the same dbname. This has worked fine for select queries so far. However, when trying to run an insert or update query I run into what looks like a quoting issue.

insert into "csv-test".kdot_intervals_counties [...]

SQL Error [HV00L]: ERROR: failed to execute the MySQL query: 
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-test.kdot_intervals_counties' at line 1

I tried forcing backticks into the dbname, but this did not work.

alter foreign table "csv-test".kdot_intervals_counties 
options ( set dbname '`csv-test`' );

SQL Error [HV00N]: ERROR: failed to connect to MySQL: Unknown database '`csv-test`'

[surajkharage19]

Hi,

Thanks for reporting this issue.

When we perform any insert/update command, first we check whether MySQL table has a primary/unique key column. While doing further analysis, I found that we missed adding quotes while generating EXPLAIN command which checks for the primary/unique column key. I have added that logic in the attached patch (Fix_quote_issue.txt), it would be good if you can confirm if this can resolve your issue.

Thanks in advance.

Fix_quote_issue.txt

[mattico]

@surajkharage19 Thanks for responding so quickly. I should have time to look into this tomorrow.

[mattico]

Hey, I had time to test this today and it worked! Insert, Update, and Delete all worked fine. Thanks!

[surajkharage19]

Hi @mattico,

Glad to know that the patch resolved the issue at your end. We will take this further and fix the issue in the repository.

Thanks again for reporting and testing this issue.

[surajkharage19]

Hi,

As we have fixed this issue, closing this case.