search

ArcadeData / arcadedb

ArcadeDB Multi-Model Database, one DBMS that supports SQL, Cypher, Gremlin, HTTP/JSON, MongoDB and Redis. ArcadeDB is a conceptual fork of OrientDB, the first Multi-Model DBMS. ArcadeDB supports Vector Embeddings.
https://arcadedb.com
Apache License 2.0
503 stars 62 forks source link

SQL: Setting "ORDER BY" Direction via Parameter #1842

Open gramian opened 3 days ago

gramian commented 3 days ago

ArcadeDB Version:

ArcadeDB Server v24.11.1 (build b466f487e42fbb39fd20965004ce88682335eaba/1730841732933/main)

OS and JDK Version:

Running on Linux 6.2.0-26-generic - OpenJDK 64-Bit Server VM 17.0.13

It is currently not possible to set the direction of sorting of ORDER BY via parameter (ie HTTP API, params object). I think this is due to the parameters in the params object being placed as data types into the query, so a string is enclosed in quotes. Hence the necessary keywords ASC or DESC cannot be set, as using a parameter would result in the strings "ASC" or "DESC".

To allow control somewhat safely without allowing keywords to be passed, the grammar could allow in addition to ASC and DESC also true and false? Alternatively the sort direction could be controlled by a top-level parameter like limit, but this would then be not SQL specific and maybe expected to work with all languages.

@lvca WDYT?

Steps to reproduce

wget -qO- --content-on-error "http://localhost:2480/api/v1/query/test" --post-data='{"language": "sql", "params": { "dir": "asc" }, "command": "SELECT num FROM Test ORDER BY num :dir"}' --user=root --password=password
gramian commented 3 days ago

The necessary changes look rather easy, every:

[ <DESC> { lastItem.type = OrderByItem.DESC; }| <ASC>  { lastItem.type = OrderByItem.ASC; }]

would become

[ (<DESC>|<FALSE>) { lastItem.type = OrderByItem.DESC; }| (<ASC>|<TRUE>)  { lastItem.type = OrderByItem.ASC; }]

And could be read as "use default direction? true / false".