Details
-
Type:
Bug
-
Status: In Progress
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.0.16
-
Fix Version/s: 10.0
-
Component/s: Storage Engine - Connect
-
Labels:
Description
CONNECT returns the following error when performing ORDER BY queries on some longer columns:
MariaDB [tmp]> SELECT * FROM long_column_test ORDER BY col1; ERROR 1032 (HY000): Can't find record in 'long_column_test'
For example, I'll create a table on MS SQL Server and insert some data:
[gmontee@localhost ~]$ isql connect_test_azure connect_test 'Password1'
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> CREATE TABLE dbo.long_column_test ( id int primary key, col1 varchar(510) );
SQLRowCount returns -1
SQL> INSERT INTO dbo.long_column_test VALUES(1, 'a');
SQLRowCount returns 1
SQL> SELECT * FROM dbo.long_column_test;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | col1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | a |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
Then I'll create the CONNECT table:
[gmontee@localhost ~]$ mysql -u root tmp Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 35 Server version: 10.0.15-MariaDB-log MariaDB Server Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [tmp]> CREATE TABLE long_column_test -> ENGINE=CONNECT -> TABLE_TYPE=ODBC -> TABNAME='dbo.long_column_test' -> CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1'; Query OK, 0 rows affected (2.41 sec) MariaDB [tmp]> SHOW CREATE TABLE long_column_test; +------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | long_column_test | CREATE TABLE `long_column_test` ( `id` int(10) NOT NULL, `col1` varchar(510) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1' `TABLE_TYPE`='ODBC' `TABNAME`='dbo.long_column_test' | +------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
And then I'll try to query it. Some queries succeed, while others fail:
MariaDB [tmp]> SELECT * FROM long_column_test ORDER BY col1; ERROR 1032 (HY000): Can't find record in 'long_column_test' MariaDB [tmp]> SELECT * FROM long_column_test; +----+------+ | id | col1 | +----+------+ | 1 | a | +----+------+ 1 row in set (0.35 sec) MariaDB [tmp]> SELECT * FROM long_column_test ORDER BY id; +----+------+ | id | col1 | +----+------+ | 1 | a | +----+------+ 1 row in set (4.84 sec) MariaDB [tmp]> SELECT * FROM long_column_test ORDER BY id, col1; ERROR 1032 (HY000): Can't find record in 'long_column_test' MariaDB [tmp]> SELECT * FROM long_column_test ORDER BY col1, id; ERROR 1032 (HY000): Can't find record in 'long_column_test'
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This is not technically a "bug" but a limitation due to CONNECT not implementing yet random access to ODBC tables. (For instance this is why ODBC tables are not indexable) Depending on a column being long or short, MariaDB uses different techiques to order the result and this is why it works on some cases and not in some other cases.
Implementing ODBC tables random access is planned and this issue will be automatically fixed when done. This does not mean that ODBC tables will be indexables. Indeed, making a local index on a table that can be modified externally without CONNECT knowing it makes no sense.
Meanwhile, there is no easy workaround. One possible one is to create a "srcdef" table that will send the SELECT .. ORDER BY... query to the data source. Doing so, the ordering will be done by the data source.
Note that similar problems occur when joining a local table to an ODBC table, but with more possible workaround.