Sub-Queries and Outer Joins may return NULL instead of (existing) value over ODBC

On certain conditions, a sub-select or outer join may return NULL instead of the actually existing value.

You can test that simply with a MySQL table, e.g. `demo.subselectdemo`:

Ideally on another computer, create a DSN with the MySQL ODBC driver (the MariaDB ODBC driver didn't work for me at all) to that database instance. Then create a database on that computer with a CONNECT-Table, e.g.:

CREATE TABLE `proxy` ENGINE=CONNECT TABLE_TYPE=ODBC BLOCK_SIZE=10 TABNAME='demo.subselectdemo' CONNECTION='DSN=<your DSN>;UID=<username>;PWD=<password>';

Now try the following query:

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

It returns:

It should return something like the following, which you can verify on the origin DB with the "real" table:

SELECT a.id, (SELECT b.value FROM demo.subselectdemo AS b WHERE a.id = b.id) FROM demo.subselectdemo AS a;

This might have to do with CONNECT's limitation to "forward cursors".

But similar happens to me with at least Oracle ODBC with a "LEFT OUTER JOIN" on two different, large (>5000 entries) tables, where the "key" used to correlate the entries is pretty random. After a few thousand entries, the OUTER JOIN is always NULL, although there is a corresponding entry in the other table - often it was actually found already for an earlier entry, e.g.:

SELECT a.id, a.ref, b.ref FROM a LEFT OUTER JOIN b ON a.ref=b.ref;

A second OUTER JOIN in the same query stops even earlier, e.g.:

SELECT a.id, a.ref, b.ref, b.otherref, c.otherref FROM a LEFT OUTER JOIN b ON a.ref=b.ref LEFT OUTER JOIN c ON b.otherref=c.otherref;

I hope you can solve both issues by fixing the "sub-select" issue; if this ticket needs to be splitted, I can try to create a proper test case for the JOIN-issue.