I cannot reproduce this on my machine.
Anyway, CONNECT does not parse the queries and I don't see how a syntax error could be due to CONNECT.
This might be caused by the used JDBC driver or the used server. Did you try this with a direct connection to JDBC not using CONNECT?
Can you reproduce it using connector/J 5.1.39 ?
Very strange behavior as it was never an issue with ODBC connections.
How would i see the sql statement being fed to the JDBC driver?
You are right. As a matter of fact the command sent by the client is firstly parsed by MariaDB, but the external server parses the command made by CONNECT. This one is slightly different because the table name can have to be changed and the backsticks replaced by what the external server requires (it is not always a MySQL or MariaDB server)
Concerning the table name, the way this command is actually made is different for SELECT, INSERT or UPDATE/DELETE commands. SELECT and INSERT are entirely constructed and CONNECT quotes the table name only if the QUOTED option is set in the CREATE TABLE of the JDBC table.
UPDATE and DELETE commands are just edited up to the WHERE clause but backsticks are replaced by the quoting character which, because most DBMS use the double quote as identifier delimiter, is a double quotes by default.
I was assuming that MariaDB (and MySQL?) also accepted double quotes as delimiters but I realize now that this is true only If the ANSI_QUOTES SQL_MODE flag is set.
With ODBC this quoting character is retrieved from the data source, I'll check whether this is possible with JDBC.
Until this is fixed, the only turnaround is: don't use backsticks when not required. Or, if possible, set the ANSI_QUOTES SQL_MODE flag in the target server.
.
Confirmed. Turning on ANSI_QUOTES for the remote server does in fact allow SELECT/INSERT/UPDATE/DELETE to work! It is a great short term work around until the quote char can be determined from the JDBC driver.
Thank you.
Also note, since all you see here is problems, that so far I have found no other issues with the JDBC implementation other than those listed 
This appears to be very solid compared with ODBC where a single bug in the driver can bring down the entire server. Zero server crashes so far.
Now CONNECT get the identifier quote char from the JDBC data source. Note that the QCHAR option was not used, and still not.
Setting the ANSI_QUOTE was a temporary solution but MySQL and MariaDB normally use backsticks so literal field can be quoted by simple or double quotes. This is no more possible when the ANSI_QUOTE is set.
I also fixed another bug in the making of UPDATE and DELETE commands that in some case make a wrong command. To avoid this until you get the fix, don't quote the original table name in your query but set the option QUOTED if it must be quoted in the command sent to JDBC (for instance if TABNAME contains blanks or is a keyword).
Thank you!
You say "Now CONNECT get the identifier quote char from the JDBC data source"
Does this mean it will also be fixed if not using a datasource and using the driver directly?
Or if using the Apache data source?
I call the function just after establishing the connection. Therefore the driver should normally be able to obtain this information from the server.
I tried it with Apache and it works.
BTW, you can see the generated query sent to JDBC in the trace to be started by:
set connect_xtrace=1;
|