Windows 2008 R2 Server (amd64), MariaDB 10.0.12 (Windows, amd64), Oracle ODBC 11.02.00.03 (with Oracle 09.02.0060 on another server) as well as MySQL ODBC 5.03.04.00 ANSI driver (with MySQL Server 5.5.40-0ubuntu on another server)

Thanks for the report.

I could reproduce it with e.g. postgresql for a remote table:

connect=> \d+ t1

                          Table "public.t1"

 Column |          Type          | Modifiers | Storage  | Description 

--------+------------------------+-----------+----------+-------------

 id     | integer                |           | plain    | 

 value  | character varying(255) |           | extended | 

Has OIDs: no

connect=> select * from t1;

 id | value  

----+--------

  1 | First

  2 | Second

  3 | Third

(3 rows)

MariaDB [test]> show create table proxy\G

*************************** 1. row ***************************

       Table: proxy

Create Table: CREATE TABLE `proxy` (

  `id` int(10) DEFAULT NULL,

  `value` varchar(255) DEFAULT NULL

) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='dsn=postgres' `TABLE_TYPE`='odbc' `TABNAME`='t1' `BLOCK_SIZE`=10

1 row in set (0.00 sec)

MariaDB [test]> SELECT a.id, (SELECT b.value FROM proxy AS b WHERE a.id = b.id) FROM proxy AS a;

+------+----------------------------------------------------+

| id   | (SELECT b.value FROM proxy AS b WHERE a.id = b.id) |

+------+----------------------------------------------------+

|    1 | First                                              |

|    2 | NULL                                               |

|    3 | NULL                                               |

+------+----------------------------------------------------+

3 rows in set (0.07 sec)

This is a general problem concerning correlated subqueries.
Unlike not correlated subqueries that are executed only once, correlated subqueries are executed many times.

What CONNECT does, when a table is accessed several times is to execute a "rewind" allowing to restart reading it from the beginning.
It is what ODBC calls a "requery" and currently this is not implemented for CONNECT ODBC tables although it is for practically all other types, for instance MYSQL.
BTW it is easy to do for MYSQL because the result set is stored in memory enabling to scroll in it.

Now, to deal with ODBC tables there are several solutions:

1. Not allowing "requery" but raise an error with a proper error message. This is just changing a critical bug (returning wrong result) to a documented restriction.
2. Implementing "requery" by discarding the current result set and re submitting the query (as MFC does)
3. Using a scrollable cursor.
4. Storing the result set in memory as MYSQL tables do.

All solutions raise some problems:

1. Does not really fix it.
2. Executing the query many times can be very time consuming if its execution is long (implying group by or join)
3. Scrolling cursors cannot be used with extended fetch. Bad for performance if the result set is large. This would be penalizing when the table is not used in a correlated subquery.
4. Best for performance but can fail with large result sets because of exhausted memory.

It would be nice to restrict these only when needed. However, I am not sure whether the storage engine can determine if the table is used in a correlated subquery.
I don't know either if the outer join fails for the same reason.
I shall experiment with these and propose a (temporary?) fix.

This fix implements solution (2) by default. The reason why is that it does not impact using the table out of correlated subquery.

However, correlated subqueries last very long on medium and big table, particularly when remote.

For instance, on a table having 4545 rows the query:

SELECT a.nom, (SELECT b.prenom FROM xdbemp AS b WHERE a.matricule = b.matricule) FROM xdbemp AS a;

takes 11 minutes on my machine. Slightly better is to use solution (3).
For this you must tell that the table to use a scrollable cursor:

ALTER TABLE xdbemp OPTION_LIST='scrollable=1';

Doing so the same query last 6 minutes 25 seconds. However the table will no more use extended fetch, which can impact all other usages.

A solution is to define a table to use only in correlated subqueries:

CREATE TABLE xdbemp2 LIKE xdbemp;

ALTER TABLE xdbemp2 OPTION_LIST='scrollable=1';

Of course, on big tables, those queries would last forever. However I did not implement solution (4) because it is very simple to make a local copy of the table to use in correlated subqueries, for instance:

CREATE TABLE locemp ENGINE=CONNECT TABLE_TYPE=FIX

AS SELECT * FROM xdbemp;

(It could use any other engine) Now the query:

SELECT a.nom, (SELECT b.prenom FROM locemp AS b WHERE a.matricule = b.matricule) FROM xdbemp AS a;

takes... 0.30 seconds!

I don' know whether this will also fix the outer join problem. As a matter of facts, I could not reproduce this bug, even before I implemented the fix.

Finally I added the implementation of solution (4) The main reason for doing so is that it is not penalising when the table is not used in a correlated sub-query. Indeed, the memory result set is not constructed on the first read (as the result set size is not known) It is allocated and populated during the second read that is still done normally. The third and subsequent read directly get the result from the stored set. As you will see it is about 6 times faster than the default method.

Using a release server (previously it was a debug one) the execution times and option setting are:

1. N.A.
2. 95.69 sec (default)
3. 24.35 sec (scrollable=1)
4. 15.67 sec (memory=1)

Note: It is still not the default to avoid memory problems with big tables.

Solution (3) should be reguarded as an alternative when getting memory problems. It might slow down the execution of all queries even not in correlated sub-queries (if using a scrollable cursor is slower than using a forward only cursor), and may not be supported by all drivers.

About the incredible execution time obtained using a local copy of the remote table, it is probably due to a MariaDB optimisation that transform the query to something else, probably a join. This should prompt you to avoid using correlated subqueries by all means and look for possible alternative.