3 ms·

So I looked at these, I didn't notice any examples where the problem was with ANSI SQL? Please let me know if there were. It seems like the problems were with

by csours 7y ago

So I looked at these, I didn't notice any examples where the problem was with ANSI SQL? Please let me know if there were. It seems like the problems were with PostgreSQL flavored queries. Speaking of which - I know Microsoft has Transact-SQL and Oracle has PL/SQL, is PostgreSQL just PostgreSQL?

mst 7y ago
PL/SQL and T-SQL are the names of the stored procedure languages - postgres' on of those is PL/PGSQL. All three have variations from ANSI SQL in their non-sproc SQL too, oracle's far worse than the other two, but pg and SQL Server document where they deviate pretty damn well. There definitely were ANSI examples in there, "ORDER BY 2" is in ANSI (and IIRC plenty of pre-ANSI dialects as well) for a start, I'm too tired (and/or lazy) to go back and look for others, sorry.
jamii 7y ago
I think a lot of these are in the ANSI 2016 spec. I haven't looked at the older specs, maybe they were simpler. Eg here is the `a+1` vs `1+a` thing explained for ORDER BY - https://i.imgur.com/bklpjkP.png https://i.imgur.com/bklpjkP.png. Here are the conditions under which ORDER BY can reach inside SELECT to pick columns from the FROM clause - https://i.imgur.com/aoeqDGn.png https://i.imgur.com/aoeqDGn.png. Chapter 2 of the spec is 1732 pages of that kind of language, so it's hard to be sure that any particular behaviour is not in there somewhere. But I'm fairly sure that all the examples with set-valued functions are postgres-isms.
- csours 7y ago
  Wow, I never would have guessed that ORDER BY stuff was part of the standard. Seems like one of the special corners of SQL. From that brief read of the ANSI Spec it seems like one would need a great deal of time and some advanced mathematics to understand it.
  - jamii 7y ago
    There isn't really any math involved. It's just a lot of edge cases written out in lots of detail. I think a lot of what makes SQL hard to implement comes down to consistently prioritizing local convenience over global complexity, combined with not being able to break backwards compatibility to simplify the design.
sbuttgereit 7y ago
When I'm writing procedural code for PostgreSQL, I'm usually writing PL/pgSQL... which is very, very similar to Oracle PL/SQL (having written more than my share of PL/SQL); My recollection is the similarity is intentional. However, there are other PL's for PostgreSQL. PL/Python, PL/Perl, and PL/Tcl are also distributed with the database and there are others available (https://www.postgresql.org/docs/12/external-pl.html.. https://www.postgresql.org/docs/12/external-pl.html... though I've only used JavaScript)
sk5t 7y ago
Many of the cases seemed to be focused on arrays, which in my non-exhaustive experience you'll see more of in postgres than in other platforms. Same for lateral join, hstore, jsonb...
dragonwriter 7y ago
PL/SQL isn't Oracle’s SQL, it's Oracle's SQL-based procedural language for triggers, stored procs, etc.; the postgres equivalent is PL/pgSQL. In both Oracle and Postgres, the SQL dialect, as opposed to the procedural language, is just called “SQL”.
- csours 7y ago
  What are the differences between the SQL dialect implementations called then? Extensions?
  - dragonwriter 7y ago
    They are called either pieces of the standard one or the other hasn't implemented or extensions, depending on which they are, sure. This is pretty explicit in the Postgres docs which directly address standards compliance throughout the SQL reference and specifically address bits of the standard they don't support or Postgres extensions to the standard. Oracle, IIRC, doesn't talk about it as directly in the main docs (though it's been longer since I read Oracle docs.)

by csours 7y ago

mst 7y ago

PL/SQL and T-SQL are the names of the stored procedure languages - postgres' on of those is PL/PGSQL. All three have variations from ANSI SQL in their non-sproc SQL too, oracle's far worse than the other two, but pg and SQL Server document where they deviate pretty damn well. There definitely were ANSI examples in there, "ORDER BY 2" is in ANSI (and IIRC plenty of pre-ANSI dialects as well) for a start, I'm too tired (and/or lazy) to go back and look for others, sorry.

jamii 7y ago

I think a lot of these are in the ANSI 2016 spec. I haven't looked at the older specs, maybe they were simpler. Eg here is the `a+1` vs `1+a` thing explained for ORDER BY - https://i.imgur.com/bklpjkP.png https://i.imgur.com/bklpjkP.png. Here are the conditions under which ORDER BY can reach inside SELECT to pick columns from the FROM clause - https://i.imgur.com/aoeqDGn.png https://i.imgur.com/aoeqDGn.png. Chapter 2 of the spec is 1732 pages of that kind of language, so it's hard to be sure that any particular behaviour is not in there somewhere. But I'm fairly sure that all the examples with set-valued functions are postgres-isms.

csours 7y ago
Wow, I never would have guessed that ORDER BY stuff was part of the standard. Seems like one of the special corners of SQL. From that brief read of the ANSI Spec it seems like one would need a great deal of time and some advanced mathematics to understand it.
- jamii 7y ago
  There isn't really any math involved. It's just a lot of edge cases written out in lots of detail. I think a lot of what makes SQL hard to implement comes down to consistently prioritizing local convenience over global complexity, combined with not being able to break backwards compatibility to simplify the design.

sbuttgereit 7y ago

When I'm writing procedural code for PostgreSQL, I'm usually writing PL/pgSQL... which is very, very similar to Oracle PL/SQL (having written more than my share of PL/SQL); My recollection is the similarity is intentional. However, there are other PL's for PostgreSQL. PL/Python, PL/Perl, and PL/Tcl are also distributed with the database and there are others available (https://www.postgresql.org/docs/12/external-pl.html.. https://www.postgresql.org/docs/12/external-pl.html... though I've only used JavaScript)

sk5t 7y ago

Many of the cases seemed to be focused on arrays, which in my non-exhaustive experience you'll see more of in postgres than in other platforms. Same for lateral join, hstore, jsonb...

dragonwriter 7y ago

PL/SQL isn't Oracle’s SQL, it's Oracle's SQL-based procedural language for triggers, stored procs, etc.; the postgres equivalent is PL/pgSQL. In both Oracle and Postgres, the SQL dialect, as opposed to the procedural language, is just called “SQL”.

csours 7y ago
What are the differences between the SQL dialect implementations called then? Extensions?
- dragonwriter 7y ago
  They are called either pieces of the standard one or the other hasn't implemented or extensions, depending on which they are, sure. This is pretty explicit in the Postgres docs which directly address standards compliance throughout the SQL reference and specifically address bits of the standard they don't support or Postgres extensions to the standard. Oracle, IIRC, doesn't talk about it as directly in the main docs (though it's been longer since I read Oracle docs.)