ORDER BY multiple columns results in error when using ASC or DESC

[WhiteG00se]

Hello there,

Here are some examples:

The following all work:
SELECT * ORDER BY a.title DESC
SELECT * ORDER BY a.title, a.attribute
SELECT * ORDER BY a.title, a.attribute DESC

These result in an error:
SELECT * ORDER BY a.title ASC, a.attribute DESC
SELECT * ORDER BY a.title ASC, a.attribute
SELECT * ORDER BY a.title DESC, a.attribute

The error says:

---

Error while executing RBQL query!
Error type: "JS syntax error"
Details: Unexpected identifier

---

Is my syntax just incorrect?
I'm kinda questioning my sanity at this point and so googled for it, in SQL my syntax should work:
https://stackoverflow.com/questions/2051162/sql-multiple-column-ordering
top answer: 'ORDER BY column1 DESC, column2'

BR
Tobias

[mechatroner]

Hi,
Thank you very much for reporting this!
Your syntax must be correct, but I honestly didn't know that it is possible to have ASC/DESC in the middle of ORDER BY expression (but now I know it, thanks to you!), and because of this I didn't add support for this syntax in RBQL. In my defense I can also add that this seems to be one of the less popular SQL features, e.g. top 2 Google results for “sql order by syntax” don’t mention it at all, see https://www.w3schools.com/sql/sql_orderby.asp and https://www.tutorialspoint.com/sql/sql-order-by.htm
The good thing is that queries like this will fail in RBQL instead of producing incorrect output, so this luckily doesn’t need a hotfix. Also realistically I think RBQL would never be fully ANSI-SQL compatible, but if it is relatively easy to add certain features it should certainly be done, so I will look into this and will try to support this syntax.
Another thing is that the error message is not very helpful, in this case, it is most likely because eval in V8 doesn’t report a lot of details ( https://bugs.chromium.org/p/v8/issues/detail?id=2589 ), but I am looking for a workaround.

[lukas879]

Has there been any work on this? I would venture to say this is very common SQL syntax and not as unpopular as noted. In fact, both of the cited links do in fact make mention of the order by directionality, and provide examples of mixed column directionality. Perhaps, they didn't 2 years ago, though.

I understand RBQL never becoming fully ANSI-SQL compatible, but I do feel like this syntax in particular is very commonly used. For example, say you have two CSV columns: "book title", and "read count". You want to sort the records with the highest read counts first (desc), then by the book titles, alphabetically (asc). As RBQL sits today, it's only accepting the directionality at the end of the ORDER BY clause and applying it to all columns listed in the clause, instead of the one it sits next to.