Status: Closed (View Workflow)
Affects Version/s: 10.0.12, 10.0.14, 10.1.1
Fix Version/s: 10.0.15
Component/s: Storage Engine - Connect
Environment: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)
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`:
|id INT||value VARCHAR(255)|
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.:
Now try the following query:
|a.id||(SELECT b.value FROM proxy AS b WHERE a.id = b.id)|
It should return something like the following, which you can verify on the origin DB with the "real" table:
|a.id||(SELECT b.value FROM demo.subselectdemo AS b WHERE a.id = b.id)|
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.:
A second OUTER JOIN in the same query stops even earlier, e.g.:
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.