Comments (6)
Thanks for bringing this to my attention.
Preql currently doesn't officially support MSSQL, but only Postgresql, MySql, and Sqlite.
I've just tested this code on Sqlite and Postgres and both work and return the correct results.
I must say MSSQL's behavior seems a bit perplexing to me. I see no reason that order by
won't work in CTEs. But I might consider adding support for MSSQL next.
from preql.
No problem. Just checked docs:
Bigquery https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause doesn't seem to specify in those circumstances, redshift https://docs.aws.amazon.com/redshift/latest/dg/r_ORDER_BY_clause.html likewise doesn't seem to.
That's not impressive of either of them. Perhaps you might contact them for clarification.
Anyway, I'd advise caution relying on it.
from preql.
Thanks! I'll take it under advisement.
I'm still confused about the intention behind it. After all, order is important in SQL. It affects UNION ALL, OFFSET-LIMIT, window functions, and tons of other stuff. And there is no way to predict how they will be composed. So ignoring a meaningful sorting.. is a questionable policy.
Anyway, I believe it won't be too hard to fix if necessary.
from preql.
Hi,
please, speaking as a DBA guy with ~25 years experience, you CANNOT assume ordering will do what you want here, and I emphasise experimentation won't help, in fact it'll make things worse because you get the results you expect... until you don't. Which will inevitably be in production (and it's horrible when that happens).
See https://dba.stackexchange.com/questions/184149/is-it-really-possible-that-the-order-will-not-be-guaranteed-for-this-particular
The reason MSSQL correctly forbids it, and the AQL spec says don't rely on any ordering unless orer by is given is to allow for optimisations. These are the same optimisations which may very well alter your execution plan as the load on the DB changes (or may alter it via predicate pushdown, that might be possible).
See https://www.mssqltips.com/sqlservertip/4472/sql-server-enterprise-advanced-scan-aka-merrygoround-scan/ for an example of how one query can affect the order of the results of another "When Query 1 and Query 2 reach page 200,000, Query 1 will complete, but Query 2 will wrap back to the first data page and continue to scan until it reaches page 100,000 and then completes"
So query 2 gets stuff out of order.
It's your choice but TL;DR I politely urge you not to rely on any assumption of ordering, nor to rely on ordering in subqueries.
from preql.
The less you're allowed to assume, the more leeway the optimiser has.
Cheers, and all the best!
from preql.
Thank you, I promise I will look into it.
Having a reliable implementation is a high priority for me.
from preql.
Related Issues (19)
- Add Automatic joins via attribute access
- Add support for VSCode
- Automatically generate GraphQL interface
- Documentation for "..." in projection HOT 1
- Grammar railroad diagram HOT 2
- Add support for BigQuery
- Error on saving table to BigQuery - required field id cannot be null HOT 1
- Error on saving table to BigQuery - type mismatch HOT 4
- Assertion error with BigQuery when running .pql scripts but not REPL HOT 1
- Add support for RedShift
- create conda package HOT 3
- ModuleNotFoundError: No module named 'preql.core' HOT 1
- Add support for JSON functions and operators in queries
- OracleInterface not working with TNSNAMES HOT 3
- Implement multiple-dispatch for functions
- `random()` is evaluated multiple times HOT 2
- Provide a way to specify data when executing a script directly HOT 4
- whitespace in table name HOT 12
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from preql.