Comments - Data limit on joins with connect tables?
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
SELECT * FROM innodb_table LEFT JOIN connect_table ON innodb_table.attrib = connect_table.attrib;
... gives the same problems.
This join can not be build on the external server because innodb_table is local and only connect_table's source is external.
[Tested with MariaDB v10.0.9 & v10.0.10 (both 64-bit) and ODBC drivers Informix v3.70.FC8 & v4.10.FC3 and MySQL Connector/ODBC v5.2.6 (all three 64-bit) on Windows 7 Enterprise SP1 (64-bit).]
Perhaps you have not a recent version of connect or because the on line CONNECT documentation is outdated, but there another possibility to join remote tables when they belong to the same data source. It is to create a table that make the remote server to the join. Here is an example:
create table categories engine=connect table_type=ODBC block_size=10 Connection='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;';
create table products engine=connect table_type=ODBC block_size=10 Connection='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;';
If you do:
select productid, productName, p.categoryid, categoryname, description from products p natural join categories;
MariaDB has to retrieve the relevant parts of the two tables and make the join locally. This is not good performance wise (except for small tables) and can be limited by the used storage. To have the join done by the data source you can do:
create table prodcat engine=connect table_type=ODBC block_size=10 Connection='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;' srcdef='select p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, c.Description from products p inner join categories c on p.CategoryID=c.CategoryID';
This table is based on a query specified by the SRCDEF option that will be executed remotely by the data source (note that the syntax must be the data source syntax) Now you can get the result of the join by:
select * from prodcat;
In your case, perhaps this will avoid loosing result rows.
Probably this won't help you, but... try checking the value of sql_select_limit. If it's != 0, it is the max number of rows that can be returned by a select.
Otherwise, I'd do a simple check. If the number of rows returned by
SELECT * FROM table;
is != the result of
SELECT COUNT(*) FROM table;
it's supposed to be a bug in MariaDB or in the CONNECT engine. In this case, it would be useful if you file a bug at https://mariadb.atlassian.net/browse/MDEV