6 ms·

Looks like theyre headed in the right direction. ORMs are tricky to get right, when I wrote ClojureQL I looked more towards combinatorics than SQL: https://cloj

by lbj 6y ago

Looks like theyre headed in the right direction. ORMs are tricky to get right, when I wrote ClojureQL I looked more towards combinatorics than SQL: https://clojureql.sabrecms.com/en/examples https://clojureql.sabrecms.com/en/examples

throwaway_pdp09 6y ago
Your example of multiple sorts is problematic. > SELECT * FROM (SELECT users.* FROM users ORDER BY users.id asc) ORDER BY users.id desc A sort should never (for some value of never) appear in a subquery because it's meaningless; it can't affect the result. In tsql it's explicitly checked for and reported. Not having a go, just fyi. Edit: now I'm more confused by that. The bracketed subquery doesn't have a name but it's apparnetly called 'users' because the last clause is 'ORDER BY users.id desc' - but that's illegal. Again tsql correctly rejects that (once I comment out the illegal inner order by). Edit 2: sorry about this but FYI, I'd expect the inner sort order to be disregarded anyway, and assuming this is mysql it explicitly is. "If ORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version" https://dev.mysql.com/doc/refman/5.7/en/select.html https://dev.mysql.com/doc/refman/5.7/en/select.html (Actually, what the heck is it saying? An outer order by in the presence of an inner order by is undefined overall??)
- dkubb 6y ago
  I agree in this specific example the inner sort is meaningless. There are valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified. I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET. In the absence of an ORDER BY the database is free to sort the rows any way it likes, and this could change between versions or based on any number of implementation details. If it appears to sort determinsitically with no ORDER BY it should not be relied upon.
  - throwaway_pdp09 6y ago
    > valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified. Oh, quite true! However the output of that subquery, despite having an order by, will not have a guaranteed order. Ordering is lost as the result set leaves the subquery. This is in the sql standard and in most if not all implementations. > I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET Wholly agreed.
- lbj 6y ago
  You're absolutely right, the inner sort example makes no sense. I hope I'll have to time to work on this again in the near future. I use CQL every day but there's still a lot I'd like to do with it.

by lbj 6y ago

throwaway_pdp09 6y ago

Your example of multiple sorts is problematic. > SELECT * FROM (SELECT users.* FROM users ORDER BY users.id asc) ORDER BY users.id desc A sort should never (for some value of never) appear in a subquery because it's meaningless; it can't affect the result. In tsql it's explicitly checked for and reported. Not having a go, just fyi. Edit: now I'm more confused by that. The bracketed subquery doesn't have a name but it's apparnetly called 'users' because the last clause is 'ORDER BY users.id desc' - but that's illegal. Again tsql correctly rejects that (once I comment out the illegal inner order by). Edit 2: sorry about this but FYI, I'd expect the inner sort order to be disregarded anyway, and assuming this is mysql it explicitly is. "If ORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version" https://dev.mysql.com/doc/refman/5.7/en/select.html https://dev.mysql.com/doc/refman/5.7/en/select.html (Actually, what the heck is it saying? An outer order by in the presence of an inner order by is undefined overall??)

dkubb 6y ago
I agree in this specific example the inner sort is meaningless. There are valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified. I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET. In the absence of an ORDER BY the database is free to sort the rows any way it likes, and this could change between versions or based on any number of implementation details. If it appears to sort determinsitically with no ORDER BY it should not be relied upon.
- throwaway_pdp09 6y ago
  > valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified. Oh, quite true! However the output of that subquery, despite having an order by, will not have a guaranteed order. Ordering is lost as the result set leaves the subquery. This is in the sql standard and in most if not all implementations. > I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET Wholly agreed.
lbj 6y ago
You're absolutely right, the inner sort example makes no sense. I hope I'll have to time to work on this again in the near future. I use CQL every day but there's still a lot I'd like to do with it.