[MDEV-12651] ORDER BY in DIR CONNECT tables does not always work Created: 2017-05-01  Updated: 2017-05-12  Resolved: 2017-05-12

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Storage Engine - Connect
Affects Version/s: 10.1.22
Fix Version/s: 10.1.24, 10.0.31, 10.2.6, 10.3.1

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Olivier Bertrand
Resolution: Done Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-12661 ORDER BY does not always work in ZIP ... Closed

 Description   

ORDER BY on 2 varchar columns of DIR CONNECT table shows error message
"Can't find record ".

create table t2 (
  fpath varchar(255) NOT NULL flag=1,
  fname varchar(255) NOT NULL flag=2,
  ftype varchar(255) NOT NULL flag=3,
  size double(12,0) NOT NULL flag=5
)engine=CONNECT table_type=DIR file_name='/home/alice/Downloads/*' option_list='subdir=1'
--------------
Query OK, 0 rows affected (0.06 sec)
 
--------------
select * from t2
--------------
+------------------------+---------------------+-------+------+
| fpath                  | fname               | ftype | size |
+------------------------+---------------------+-------+------+
| /home/alice/Downloads/ | Untitled Document 3 |       |    0 |
| /home/alice/Downloads/ | Untitled Document 2 |       |    0 |
| /home/alice/Downloads/ | Untitled Document   |       |    0 |
+------------------------+---------------------+-------+------+
3 rows in set (0.00 sec)
 
--------------
SELECT fname, ftype, size FROM t2 ORDER BY fname,ftype
--------------
ERROR 1032 (HY000) at line 15 in file: '/home/alice/t/c.sql': Can't find record in 't2'



 Comments   
Comment by Olivier Bertrand [ 2017-05-02 ]

Not only but all sort fail.
Indeed, the way result file names are retrieved does not permit position read. Therefore, DIR tables cannot be indexed or sorted.
This will be indicated in the documentation and a better error message returned.

Comment by Alice Sherepa [ 2017-05-02 ]

I got an impression that all other cases are working properly, there was just a problem with varchar types, e.g. when I add size in order by, results are correct.

Comment by Olivier Bertrand [ 2017-05-02 ]

It must be depending on how MariaDB does sorting. In some cases it is done internally on a result set kept in memory, in some other cases it is done by storing positions, sort them, and re-reading by position.
This depends on the memory size needed and perhaps on the column type.

Comment by Elena Stepanova [ 2017-05-11 ]

I have two suggestions regarding this issue.

First, I see that you have changed the error message to a more suitable "Not supported by this table type", which is good. However, it comes with the error code 1105, which is ER_UNKNOWN_ERROR, which might be not great for various tools which rely on the error code rather than the message. Maybe it's better to use ER_ILLEGAL_HA instead (Storage engine %s of the table %`s.%`s doesn't have this option)?

If on some reason you want to keep 1105, then – since you provide a custom message anyway – it makes sense to make a more comprehensive one. E.g. what is not supported? Although, I suppose it might be difficult to specify since it's a common function used in all types of queries.

Secondly, I think such limitations need to be documented, since it's really not obvious what exactly is wrong with a query, why a simple query like that does not work, but a seemingly identical query with somewhat different types works (and it's of course impossible to squeeze it all into the error message). So, I'm re-opening it for documentation purposes.

Generated at Thu Feb 08 07:59:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.