Will this work even if the original (by "original" I mean the one the user submits) query includes sub selects in the where clause that reference tables not in the remote system?

Will the following work? (Bob's has two single quotes)

UPDATE remoteT1 SET col1 = 'Bob''s fence is 24" high' WHERE id = (select distinct id from localT2 where a > 17)

The above is not our immediate use case, but super great if supported.

Of course it won't work and reply table localT2 does not exist.

Supporting it would require a complete change. Something like replacing the query by something like:

SELECT ... FOR UPDATE

I don't even dream of trying implementing this which, by the way, would not work in our case. Remember that the WHERE clause is parsed and must be understood locally but is applied by the remote server.

Well, the following works today (I just tested it):

select * from MyConnectTable where id in (select distinct Customer_ID from MyLocalNonConnect)

But an UPDATE statement with the same WHERE clause fails.

In theory (I realize not how it works today) couldn't sub queries be processed by MariaDB locally (select distinct Customer_ID from MyLocalNonConnect) and the VALUES from the result be passed to the remote server? To be clear this is not a feature request - just thinking.

I am trying to keep things simple and working, even warning that UPDATE and DELETE are supported "in a simplified way".

For instance, how could CONNECT decide whether the sub-query applies to a local or remote table?

I can reopen this discussion in another MDEV at a later date, as I don't want to slow down the fixing of the single and double quote issue itself... those are important core use cases as we can't control what a user types in, and many of our customers have product descriptions using both characters (e.g. Gold 8" long pendant from Kyle's Jewlery). And stability is most important at this stage.

One final thought ... but since I have no idea about the internal workings of MariaDB and how queries interact with storage engines, all I can do is speculate on what a solution might look like. But one solution regarding how CONNECT could decide if sub-query is local or remote --> don't decide If you could take any subquery and pass it back up to the top MariaDB server (local table or remote), get the results as a derived table with a single column, then use those in a hard-coded query... seems like that would handle a lot of use cases (not correlated sub queries, but others).

So this:

UPDATE remoteTable SET col1 = 'abc' WHERE id IN (SELECT id from localTable where a > 7)

takes the sub-query and passes it to mariadb top level to execute, and the results of that are used to re-write the sub-query to something like this:

UPDATE remoteTable SET col1 = 'abc' WHERE id IN (45,27,1234,5554,67)

Right now sub-query in update is purely a speculative use case for us whereas the single and double quote handling (and the Ntext datatype support) are real-life immediate needs. The rest seems to be working very well, and stability of the Java connect stuff is excellent.