10 ms·

I don't disagree that SQL can be improved. It's one of the biggest reasons I use Postgres in the first place because there are so many improvements available on

I don't disagree that SQL can be improved. It's one of the biggest reasons I use Postgres in the first place because there are so many improvements available on top of SQL. All that said...SQL is pretty darn effective. As a language, it's the true backbone of the internet today. It's readable, explicit, fairly concise and naturally translates to how data should be broken down for efficient storage...or make some trade-offs to allow for more efficient retrieval. There are differences with different vendor implementations...but that's what different vendors are for - to find things the other guys are doing wrong and improving on them to build a better product. I wish the folks luck in their work to improve things, but the language I've been able to rely on consistently over the last 17 years or so has been SQL...and I've worked with a lot of languages. SQL is the one that gets the most done reliably and lets me down the least often.

tathougies 8y ago
> I use Postgres in the first place because there are so many improvements available on top of SQL. Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.
- inferiorhuman 8y ago
  Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well. Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.
  - jade12 8y ago
    Doesn't every major RDBMS support JSON out of the box these days?
    - zaarn 8y ago
      (Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG. In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.
      - atwebb 8y ago
        MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.
  - wtetzner 8y ago
    SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016 https://en.wikipedia.org/wiki/SQL:2016
    - inferiorhuman 8y ago
      Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.
      - crazysmoove 8y ago
        I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1]. [0] https://modern-sql.com/ https://modern-sql.com/ [1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example (Edited for formatting.)
    - ioltas 8y ago
      Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72b6460336e86ad5cafd3426af6013c7d8457367 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... The release is planned for roughly next September/October.
  - irrational 8y ago
    Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?
    - ken 8y ago
      SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.
      - matharmin 8y ago
        While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.
      - nbevans 8y ago
        And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.
    - marcolussetti 8y ago
      If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.
      - GordonS 8y ago
        Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.
    - lefred 8y ago
      Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mysql-80-document-store https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-nosql-sql-mysql https://www.slideshare.net/lefred.descamps/oracle-code-roma-...
- brightball 8y ago
  The various extensions / plugins that allow for custom data types, indexes, use of multiple programming languages to write functions, ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function, varieties of powerful search capabilities, etc It's got a lot of stuff going on in there.
  - cagmz 8y ago
    > ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function, This sounds amazing. Does anyone have a link to some docs for this?
- ken 8y ago
  That "most" is a pretty loaded word! Last I checked, even CREATE INDEX is not part of any ANSI or ISO SQL specification. (That's why every RDBMS has such different features and syntax for this.) Good luck building a system with just "standard SQL".
  - mbreese 8y ago
    Why should it be? Indexing is an implementation detail for each RDBMS, so it doesn’t make sense to add it to the language spec.
    - garmaine 8y ago
      It does make sense when the goal of the language spec is interoperability, and it's something that everyone has to do, in practice, no matter the platform. Call it a "hint", if you will, the meaning of which is implementation defined. But standardize the syntax for pete's sake.
      - ErwinSmout 8y ago
        Interoperability is achieved if the same query with the same inputs yields the same result. "In the same amount of time" (which is the part that indexes are aimed at) is manifestly not part of that picture, and that's by design. And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?
    - ken 8y ago
      That sounds backwards to me. It's only lack of standardization that forces it to be an "implementation detail". To any application that needs to be able to query data in reasonable time, it's fundamental and necessary functionality.
  - paulddraper 8y ago
    > even CREATE INDEX is not part of any ANSI or ISO SQL specification That's correct, and that's why PostgreSQL has ADD CONSTRAINT. The constraints describe the actual schema of the data; INDEX is an DBMS-specific implementation* for improving performance (including index types, etc.). * Those since the standard does not yet cover some thing partial unique constraints, these have to be done as INDEX in PostgreSQL.
- jtms 8y ago
  I feel like Postgres is so powerful I could damn near build an entire web app backend with JUST Postgres (i'm only sorta kidding here). If that's standard SQL, well then I really like standard SQL :-)
  - paulddraper 8y ago
    You think you're kidding. https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest https://wiki.postgresql.org/wiki/Python https://wiki.postgresql.org/wiki/Python https://github.com/plv8/plv8 https://github.com/plv8/plv8 https://www.graphile.org/ https://www.graphile.org/
    - jtms 8y ago
      Graphile looks quite intriguing! any experience with it? Recommended?
      - Munksgaard 8y ago
        I'm using Graphile in deployment at the moment, and it works quite nicely. Granted, we have a limited amount of users thus far, so I don't really know how well it handles big loads, but it allowed us to get off the ground with a PoC _really_ quickly.
      - dafrie 8y ago
        I would also have a look at Hasura GraphQLEngine. Have used it now for various projects and is extremely nice, especially with the superb subscriptions support and eventing for the odd requirement that cant be handled within Postgres
    - ar-jan 8y ago
      I'm keeping an eye on https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"
  - CuriouslyC 8y ago
    I've done pretty much this a few times, and it's amazing if you're working in the context of enterprise data systems that need to provide extensive capabilities to a "small" userbase (i.e. concurrent in the thousands). You just need a small shim layer for security, to transform results sets, and handle browser -> database connectivity. Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.
closeparen 8y ago
I’ve been writing some pretty ambitious Hive queries at work lately. As I learn more about SQL and pull off more complex queries, my respect for it deepens. To have such power and support so many use cases with so few constructs is really an engineering feat. It’s timeless for a reason. Some of my relatively common access patterns are awkward to express, but they can still be expressed in a few lines + a CTE or two, which is really impressive for a language so small. This is not to say we can’t do better. But SQL has achieved a deep resonance with its problem space that most tools don’t even come close to. The brightest minds and most effective tooling shops in our field would be lucky merely to do as well.
- osrec 8y ago
  It supports so many use cases primarily because of the small number of fundamental "constructs". I mean, if I gave you protons, electrons and neutrons, you could build the universe out of them!
  - OJFord 8y ago
    That doesn't make sense, if I give you more things (I'm avoiding saying 'atom', but you know what I mean) then it's not the case that you can suddenly do less. Expressiveness isn't inversely correlated to number of constructs.
    - osrec 8y ago
      Let's say you gave me an atom. In this situation, you can't create other atoms. When you give me protons, electrons and neutrons, I can create any atom I like, in addition to anything else in the universe. Thus, when you give me an atom, you actually reduce the possibilities somewhat. In reality, it's not just about the small number of constructs, but also about how fundamental they are. In one sense, atoms are less fundamental than protons/neutrons/electrons, and therefore reduce the number of possible creations.
      - vageli 8y ago
munk-a 8y ago
Having used splunk's query language... I'd hope that in general vendors start with an assumption of SQL support and maybe try tweaking it at the edges rather than burning the house down - SQL has survived this long because it's extremely expressive and any replacement for it is going to need to match that expressiveness. All that said I think it was originally structured to partially be a human readable language and it fails pretty hard at that - that's a facet I'm sure smart people could revise to make more natural.
- james-mcelwain 8y ago
  To be fair, Splunk excels at dealing with unstructured logs, which is in many ways a harder problem. Obviously in a perfect world, all our data would be structured, which would make querying much simpler.
  - jeremyjh 8y ago
    Typically the earlier part of your pipeline turns the logs into data records via regex captures. So you could have had a pipeline of what are essentially shell commands that produce a table that SQL operates on.
  - munk-a 8y ago
    I don't buy that, splunk has a clear concept of columns and rows and could have chosen to expose support for a SQL like grammar for assembling them - they instead chose a less structured format that makes data assembly quite difficult. More legitimately splunk may simply be unable to deliver performant data expression if a user is typing a query complex enough to justify SQL.
  - madhadron 8y ago
    I used to work for Splunk. Querying Splunk with SQL is completely plausible, and something that Splunk has made a number of attempts at over the years. The problem isn't SQL. It's that Splunk's query engine is tied up internally with a "grammar" that is a direct port of a shell pipeline into C++ with no intermediate representation or anything a compiler guy would recognize as a grammar. There was no design, no mathematical underpinning to it. Splunk's unstructured log capabilities are really domain knowledge about making them semistructured as fast as possible: token indexing, a lot of effort on recognizing character encodings and timestamps intelligently, looking for key=value pairs, and letting people write regexes to extract fields themselves. The query language isn't somehow designed for a different data model. In EWD1123, Dijkstra showed that the relational calculus and the regularity calculus (which governs regexes) are basically the same thing. My takeaway from that is that the relational model can be reinterpreted as a model over anything you want to match and manipulate with regexes by just changing the field selectors.
setr 8y ago
Afaict, the state of SQL as a grammar with tooling is kind of pathetic. As a standardized language, it doesn’t really exist; everyone implements numerous extensions, and almost no one is fully ansi compliant Almost all formatters attempt to be generic (believing standardization exists), and fail to support the full grammar for any dialect. Across the board, all parsers have pathetic error message support (error on line 3, which is actually just the start of the statement). The schema offers type constraints, but querying/ide’s extract no value from that (that is, types are statically specified/constrained, but query editors all pretend its fully dynamic) Theres a lot of awkward nonsense, like where clauses are parsed before the select in most parsing engines, causing alias usage to fail without wrapping in a subselect/with clause The grammars themselves are an inconsistent, ad-hoc mess The grammar is also unnecessarily context-dependent (eg from must follow select, and where after that), making programmatic composition unnecessarily difficult I don’t know how much of the tooling issue is a result of SQL as a language versus the history itself, but I can at least confirm that trying to parse multiple dialects is absolute hell, which would at least explain the sorry state of affairs for eg formatters. But the majority of its expressive power derives from the relational algebra, and has nothing to do with the SQL grammar, and thats the majority of its value. It seems obvious to me that at the very least the compositional issue of SQL, and its self-inconsistent grammar, should be vulnerable to near-lossless improvement without too much struggle, though I can’t say what the alternative would actually look like. But it seems like its riddled with a lot of unnecessary flaws
- jeremyjh 8y ago
  Actually standards compliance is really good these days. There are a lot of custom functions that are hard to do without in some reporting applications but behavior of SQL clauses is consistent across every engine.
- namibj 8y ago
  JetBrains tooling does use schema metadata for it's autocompletion. I'm not sure though if it's anywhere close to full-on Haskell autocompletion in the Atom editor (very fiddly and prone to break on minor version changes, I must say).
roystonvassey 8y ago
Same. As a data scientist, I have seen multiple 'analytics workbench' solutions come, go and occasionally stay - Clementine (what is now IBM SPSS), Stata, SAS and its many variants, Statistica and in the recent years, tools built off and designed to make working with data using Python/R. But, the common workhorse tool that has stayed strong through all these has been the common SQL. Elegant, simple, powerful and thoroughly reliable, it is my primary go-to tool. In an otherwise changing ecosystem, its simplicity and reliability is a boon. Yes, it is primarily because of the nostalgic familiarity but I also believe it continues to be extremely powerful, one that will serve you very well.
masklinn 8y ago
> I use Postgres […] SQL is pretty darn effective. FWIW postgres used to have its own query language derived from QUEL[0] rather than SQL. And findings that SQL is kinda shit are not exactly recent, e.g. C.J. Date's "A Critique of the SQL Language" (1983) lists the following sections * lack of orthogonality: expressions * lack of orthogonality: builtin functions * lack of orthogonality: miscellaneous items formal definition * mismatch with host languages * missing function * mistakes * aspects of the relational model not supported The conclusion was, obviously, prescient: > if SQL is adopted on a wide scale in its present fortm~ then we will to some degree have missed the relational boat~ or at least failed to capitalize to the fullest possible extent on the potential of the relational model. That would be a pity, because we had an opportunity to do it right, and with a little effort we could have done so. The question is whether it is now too late. I sincerely hope not. SQL succeeded not because it's "pretty darn effective" but because IBM decided on it (at a time where it drove technology) and Oracle are great at sales and marketing (whereas Ingres definitely wasn't). [0] https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
dkersten 8y ago
I came here to write something similar, but you said it better than I could. Sure, SQL has the problems that the article mentions and their solution looks nice on the surface, but SQL has worked much better for me than any alternatives, especially if it’s slightly extended SQL like in Postgres, as you mention, and any attempts to improve it will always be an uphill battle as SQL is pervasive and well supported. That doesn't mean people shouldn't try, but it does mean that their likelihood of success depends on factors other than whether the solution is an improvement over SQL or not.

tathougies 8y ago

> I use Postgres in the first place because there are so many improvements available on top of SQL. Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

inferiorhuman 8y ago
Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well. Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.
- jade12 8y ago
  Doesn't every major RDBMS support JSON out of the box these days?
  - zaarn 8y ago
    (Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG. In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.
    - atwebb 8y ago
      MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.
- wtetzner 8y ago
  SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016 https://en.wikipedia.org/wiki/SQL:2016
  - inferiorhuman 8y ago
    Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.
    - crazysmoove 8y ago
      I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1]. [0] https://modern-sql.com/ https://modern-sql.com/ [1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example (Edited for formatting.)
  - ioltas 8y ago
    Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72b6460336e86ad5cafd3426af6013c7d8457367 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... The release is planned for roughly next September/October.
- irrational 8y ago
  Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?
  - ken 8y ago
    SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.
    - matharmin 8y ago
      While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.
    - nbevans 8y ago
      And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.
  - marcolussetti 8y ago
    If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.
    - GordonS 8y ago
      Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.
  - lefred 8y ago
    Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mysql-80-document-store https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-nosql-sql-mysql https://www.slideshare.net/lefred.descamps/oracle-code-roma-...
brightball 8y ago
The various extensions / plugins that allow for custom data types, indexes, use of multiple programming languages to write functions, ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function, varieties of powerful search capabilities, etc It's got a lot of stuff going on in there.
- cagmz 8y ago
  > ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function, This sounds amazing. Does anyone have a link to some docs for this?
ken 8y ago
That "most" is a pretty loaded word! Last I checked, even CREATE INDEX is not part of any ANSI or ISO SQL specification. (That's why every RDBMS has such different features and syntax for this.) Good luck building a system with just "standard SQL".
- mbreese 8y ago
  Why should it be? Indexing is an implementation detail for each RDBMS, so it doesn’t make sense to add it to the language spec.
  - garmaine 8y ago
    It does make sense when the goal of the language spec is interoperability, and it's something that everyone has to do, in practice, no matter the platform. Call it a "hint", if you will, the meaning of which is implementation defined. But standardize the syntax for pete's sake.
    - ErwinSmout 8y ago
      Interoperability is achieved if the same query with the same inputs yields the same result. "In the same amount of time" (which is the part that indexes are aimed at) is manifestly not part of that picture, and that's by design. And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?
  - ken 8y ago
    That sounds backwards to me. It's only lack of standardization that forces it to be an "implementation detail". To any application that needs to be able to query data in reasonable time, it's fundamental and necessary functionality.
- paulddraper 8y ago
  > even CREATE INDEX is not part of any ANSI or ISO SQL specification That's correct, and that's why PostgreSQL has ADD CONSTRAINT. The constraints describe the actual schema of the data; INDEX is an DBMS-specific implementation* for improving performance (including index types, etc.). * Those since the standard does not yet cover some thing partial unique constraints, these have to be done as INDEX in PostgreSQL.
jtms 8y ago
I feel like Postgres is so powerful I could damn near build an entire web app backend with JUST Postgres (i'm only sorta kidding here). If that's standard SQL, well then I really like standard SQL :-)
- paulddraper 8y ago
  You think you're kidding. https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest https://wiki.postgresql.org/wiki/Python https://wiki.postgresql.org/wiki/Python https://github.com/plv8/plv8 https://github.com/plv8/plv8 https://www.graphile.org/ https://www.graphile.org/
  - jtms 8y ago
    Graphile looks quite intriguing! any experience with it? Recommended?
    - Munksgaard 8y ago
      I'm using Graphile in deployment at the moment, and it works quite nicely. Granted, we have a limited amount of users thus far, so I don't really know how well it handles big loads, but it allowed us to get off the ground with a PoC _really_ quickly.
    - dafrie 8y ago
      I would also have a look at Hasura GraphQLEngine. Have used it now for various projects and is extremely nice, especially with the superb subscriptions support and eventing for the odd requirement that cant be handled within Postgres
  - ar-jan 8y ago
    I'm keeping an eye on https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"
- CuriouslyC 8y ago
  I've done pretty much this a few times, and it's amazing if you're working in the context of enterprise data systems that need to provide extensive capabilities to a "small" userbase (i.e. concurrent in the thousands). You just need a small shim layer for security, to transform results sets, and handle browser -> database connectivity. Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.

closeparen 8y ago

I’ve been writing some pretty ambitious Hive queries at work lately. As I learn more about SQL and pull off more complex queries, my respect for it deepens. To have such power and support so many use cases with so few constructs is really an engineering feat. It’s timeless for a reason. Some of my relatively common access patterns are awkward to express, but they can still be expressed in a few lines + a CTE or two, which is really impressive for a language so small. This is not to say we can’t do better. But SQL has achieved a deep resonance with its problem space that most tools don’t even come close to. The brightest minds and most effective tooling shops in our field would be lucky merely to do as well.

osrec 8y ago
It supports so many use cases primarily because of the small number of fundamental "constructs". I mean, if I gave you protons, electrons and neutrons, you could build the universe out of them!
- OJFord 8y ago
  That doesn't make sense, if I give you more things (I'm avoiding saying 'atom', but you know what I mean) then it's not the case that you can suddenly do less. Expressiveness isn't inversely correlated to number of constructs.
  - osrec 8y ago
    Let's say you gave me an atom. In this situation, you can't create other atoms. When you give me protons, electrons and neutrons, I can create any atom I like, in addition to anything else in the universe. Thus, when you give me an atom, you actually reduce the possibilities somewhat. In reality, it's not just about the small number of constructs, but also about how fundamental they are. In one sense, atoms are less fundamental than protons/neutrons/electrons, and therefore reduce the number of possible creations.
    - vageli 8y ago

munk-a 8y ago

Having used splunk's query language... I'd hope that in general vendors start with an assumption of SQL support and maybe try tweaking it at the edges rather than burning the house down - SQL has survived this long because it's extremely expressive and any replacement for it is going to need to match that expressiveness. All that said I think it was originally structured to partially be a human readable language and it fails pretty hard at that - that's a facet I'm sure smart people could revise to make more natural.

james-mcelwain 8y ago
To be fair, Splunk excels at dealing with unstructured logs, which is in many ways a harder problem. Obviously in a perfect world, all our data would be structured, which would make querying much simpler.
- jeremyjh 8y ago
  Typically the earlier part of your pipeline turns the logs into data records via regex captures. So you could have had a pipeline of what are essentially shell commands that produce a table that SQL operates on.
- munk-a 8y ago
  I don't buy that, splunk has a clear concept of columns and rows and could have chosen to expose support for a SQL like grammar for assembling them - they instead chose a less structured format that makes data assembly quite difficult. More legitimately splunk may simply be unable to deliver performant data expression if a user is typing a query complex enough to justify SQL.
- madhadron 8y ago
  I used to work for Splunk. Querying Splunk with SQL is completely plausible, and something that Splunk has made a number of attempts at over the years. The problem isn't SQL. It's that Splunk's query engine is tied up internally with a "grammar" that is a direct port of a shell pipeline into C++ with no intermediate representation or anything a compiler guy would recognize as a grammar. There was no design, no mathematical underpinning to it. Splunk's unstructured log capabilities are really domain knowledge about making them semistructured as fast as possible: token indexing, a lot of effort on recognizing character encodings and timestamps intelligently, looking for key=value pairs, and letting people write regexes to extract fields themselves. The query language isn't somehow designed for a different data model. In EWD1123, Dijkstra showed that the relational calculus and the regularity calculus (which governs regexes) are basically the same thing. My takeaway from that is that the relational model can be reinterpreted as a model over anything you want to match and manipulate with regexes by just changing the field selectors.

setr 8y ago

Afaict, the state of SQL as a grammar with tooling is kind of pathetic. As a standardized language, it doesn’t really exist; everyone implements numerous extensions, and almost no one is fully ansi compliant Almost all formatters attempt to be generic (believing standardization exists), and fail to support the full grammar for any dialect. Across the board, all parsers have pathetic error message support (error on line 3, which is actually just the start of the statement). The schema offers type constraints, but querying/ide’s extract no value from that (that is, types are statically specified/constrained, but query editors all pretend its fully dynamic) Theres a lot of awkward nonsense, like where clauses are parsed before the select in most parsing engines, causing alias usage to fail without wrapping in a subselect/with clause The grammars themselves are an inconsistent, ad-hoc mess The grammar is also unnecessarily context-dependent (eg from must follow select, and where after that), making programmatic composition unnecessarily difficult I don’t know how much of the tooling issue is a result of SQL as a language versus the history itself, but I can at least confirm that trying to parse multiple dialects is absolute hell, which would at least explain the sorry state of affairs for eg formatters. But the majority of its expressive power derives from the relational algebra, and has nothing to do with the SQL grammar, and thats the majority of its value. It seems obvious to me that at the very least the compositional issue of SQL, and its self-inconsistent grammar, should be vulnerable to near-lossless improvement without too much struggle, though I can’t say what the alternative would actually look like. But it seems like its riddled with a lot of unnecessary flaws

jeremyjh 8y ago
Actually standards compliance is really good these days. There are a lot of custom functions that are hard to do without in some reporting applications but behavior of SQL clauses is consistent across every engine.
namibj 8y ago
JetBrains tooling does use schema metadata for it's autocompletion. I'm not sure though if it's anywhere close to full-on Haskell autocompletion in the Atom editor (very fiddly and prone to break on minor version changes, I must say).

roystonvassey 8y ago

Same. As a data scientist, I have seen multiple 'analytics workbench' solutions come, go and occasionally stay - Clementine (what is now IBM SPSS), Stata, SAS and its many variants, Statistica and in the recent years, tools built off and designed to make working with data using Python/R. But, the common workhorse tool that has stayed strong through all these has been the common SQL. Elegant, simple, powerful and thoroughly reliable, it is my primary go-to tool. In an otherwise changing ecosystem, its simplicity and reliability is a boon. Yes, it is primarily because of the nostalgic familiarity but I also believe it continues to be extremely powerful, one that will serve you very well.

masklinn 8y ago

> I use Postgres […] SQL is pretty darn effective. FWIW postgres used to have its own query language derived from QUEL[0] rather than SQL. And findings that SQL is kinda shit are not exactly recent, e.g. C.J. Date's "A Critique of the SQL Language" (1983) lists the following sections * lack of orthogonality: expressions * lack of orthogonality: builtin functions * lack of orthogonality: miscellaneous items formal definition * mismatch with host languages * missing function * mistakes * aspects of the relational model not supported The conclusion was, obviously, prescient: > if SQL is adopted on a wide scale in its present fortm~ then we will to some degree have missed the relational boat~ or at least failed to capitalize to the fullest possible extent on the potential of the relational model. That would be a pity, because we had an opportunity to do it right, and with a little effort we could have done so. The question is whether it is now too late. I sincerely hope not. SQL succeeded not because it's "pretty darn effective" but because IBM decided on it (at a time where it drove technology) and Oracle are great at sales and marketing (whereas Ingres definitely wasn't). [0] https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages

dkersten 8y ago

I came here to write something similar, but you said it better than I could. Sure, SQL has the problems that the article mentions and their solution looks nice on the surface, but SQL has worked much better for me than any alternatives, especially if it’s slightly extended SQL like in Postgres, as you mention, and any attempts to improve it will always be an uphill battle as SQL is pervasive and well supported. That doesn't mean people shouldn't try, but it does mean that their likelihood of success depends on factors other than whether the solution is an improvement over SQL or not.