6 ms·

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of oper

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql https://github.com/prql/prql I hope in near future databases will come with better query languages...

minxomat 5y ago
KQL is a more LINQy language.
- tehlike 5y ago
  Looks interesting.
nickcox 5y ago
> The order of operations are out of whack and makes pipeline ing a little hard It makes it really hard to offer good autocompletions too.
tfigment 5y ago
This comes up for me in autocompleting editors. Have to start with "select * from table t;" then go back and then remove * and t.<ctrl-space> to get the hints to populate and then continue with joins which may require going back to select. Update and delete at least start with tables and joins.
userbinator 5y ago
It was probably influenced by COBOL, i.e. designed to read like English.
- manigandham 5y ago
  In fact, SQL (structured query language) was originally called SEQUEL (structured English query language).
  - tehlike 5y ago
    I find prql more English for some reason...
- ErwinSmout 5y ago
  It was probably also influenced by Codd's own preference for the data language in calculus-based style (Codd's roots as a mathematician are to "blame" here), as opposed to algebra-based (the style typically preferred by developers).
technion 5y ago
This feels a lot like why Kusto was invented. It's very close to SQL, but written the other way around with the table name first, then the 'where', and finally the columns. https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...
- tehlike 5y ago
  Correct looks like it's trying to be the linq of sql. Looks good. MSFT really knows how to make programming tools...
zmxz 5y ago
In my case it's the opposite - SQL is perfectly readable and fits my mental model while the project you linked feels awkward and hard to read. However, it's good to have options, I wonder if prql will get traction. It would be great if it did.
- 7952 5y ago
  A lot of the time people's mental model will be derived from the language though.
  - ErwinSmout 5y ago
    And in the case of SQL, that is precisely the problem.
    - zmxz 5y ago
      So the problem is that SQL can be picked up fairly easily by English speaking population? How's that the problem and not advantage?
      - ErwinSmout 4y ago
        Because "speaking SQL" [even before SQL as such was even invented, hence the scare quotes] was intended as a skill to be practiced only by those who also grasped the [mathematical] logic of what they were doing when "speaking SQL". None of the people who "speak SQL" these days fit that bill. And FWIW, what I wrote was in reply to "mental model". It was *you* who apparently equated that with "speaking English". The "mental model" I described as "exactly the problem" is the "mental model" *of the corresponding logic*, which, though mathematically consistent, is hopelessly unintuitive. Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language.
        zmxz 4y ago
        Having a language that already educated and skilled group of people can pick up fast is advantage. Targeting and working with people who grasp logic is good. Not bad. There's ever-repeating notion present that we need to invent tools that people, who are far from skilled, should be able to use to produce high quality results. Having a tool like SQL that's perfectly usable for a group of highly skilled people is excellent, not bad. > None of the people who "speak SQL" these days fit that bill. You can't prove this. This is what you believe is true. > And FWIW, what I wrote was in reply to "mental model". It was you who apparently equated that with "speaking English" Isn't it odd how we have the gift of being able to communicate, yet all we do is think we can wield telepathy and not use words to express ourselves. Unless you accurately express what it is you're thinking about, I can't fill in the gaps since I'm not telepathically enabled. > Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language. Of course it does.
vkazanov 5y ago
...and there's more to sql trouble than just troublesome syntax, typical for languages of its generation! 1. Sql is non-modular 2. Non-orthogonal 3. Its parroting of the (beautiful) relational data model is... opinionated at best. The standard is unreadable and not really implementable, one always has to resort to implementation docs. Realistically there are no alternatives, but... sql is no good.
- rzmmm 5y ago
  What do you think about using SQL views and functions to make composable, modular SQL code?
  - tbrownaw 5y ago
    It doesn't work very well. Table Out1 has translated versions of fields AA..AZ from table In1. Table Out2 has translated versions of fields BA..BZ from table In1. table Out3 has translated versions of fields X..Z from table In2, and the translation depends on some of the AA..BZ fields, in both translated and untranslated forms. We'd end up with views that depended on other views and after expanding had like a dozen self-joins to add more fields that weren't included in the base view. And views that were selecting a big list of columns unchanged, plus adding a couple. But somehow despite being an absolute ^%#@!^&$! it still worked better than the pointy-clicky ETL tooling we'd been using previously. Mostly because of the run->debug->modify cycle time being faster, and only a little because of avoiding pointy-clicky stuff.
  - vkazanov 5y ago
    1. Views are fine. They somewhat help with abstracting things away here and there, yes. But these are just macros, right? 2. What SQL functions are you talking about? There are builtins, e.g. avg, sum, and then there are User Defined Functions, which have nothing to do with SQL, mostly just a random external language or one of the non-portable PL/SQL flavours. Practically speaking, one cannot really write portable SQL outside of a very limited intersection of various dialects. And within this limit users just cannot build a reasonably portable code library. There is a reason why analysts working with SQL just copy massive query templates again and again and again...
ako 5y ago
Nice example of: always begin with the end in mind - first specify what data you want to see and then specify where it should come from. I think it helps you focus on the outcome required, and makes you more efficient deciding how to implement it. I use the same approach for methods in normal code: first decide on the signature of the method, and potentially a unit test to test it, and then think about the implementation.
- tehlike 5y ago
  I like to think of data transformation a chaining problem. Linq lambda syntax is kind of like that. db.Table.where(x=> x.name == 'abc').skip(10).take(20).groupBy() This is really hard in sql especially with temporary tables and nesting you need to do as soon as you need to do things a little out of order... Etc
  - ErwinSmout 5y ago
    That's because the true foundation is that relational algebra is (a) an algebra and (b) closed over relations. (a) means you should have an expressions system that allows arbitrary nesting and (b) means you should be able to arbitrarily nest relational expressions in particular. SQL fails heavily on (b) (it fails less so than it used to do with the SQL:1992 standard but even so, it's still far from where we should have been and SQL itself is still an abomination). Moreover, your very examples illustrate how "thinking SQL" is *NOT* the path to finding proper solutions. .skip() and .take() clearly originate from the world of ranking problems and it drips off of every word that you were thinking of finding a "modern" way to specify [things like] "TOP 10", but those solutions are themselves a fundamentally crippled way to attack the problem.
cryptonector 5y ago
I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs. Query q = db.from("foo").join("bar").using("id") .select("id","foothing","barthing"); However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the need for non-trivial SQL expressions in queries can't be avoided, nor can it be left to the host language. Also, I'd really like a SQL mode where no literal constants are allowed, as a mechanism to force the use of query parameters and prevent SQL injection.
- tehlike 5y ago
  Linq in c# fixes that.
  - ErwinSmout 5y ago
    I doubt that it does. There is way more to "integrating [host] language and queries" than the MIN() of what Micro$oft engineers are (a) capable of understanding and (b) allowed by their own management to put in the products they come up with.
    - antonvs 5y ago
      Your comment may be true of many things that Microsoft does, but not LINQ. The creator of LINQ was Erik Meijer (https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientist) https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientis...), who was a leading researcher in programming languages and functional programming before shifting into industry. He's well known in the Haskell world for e.g. his work on bananas and lenses, and many other things. LINQ (https://en.wikipedia.org/wiki/Language_Integrated_Query https://en.wikipedia.org/wiki/Language_Integrated_Query) is basically an embedding of a general monadic framework, augmented with a set of query-specific operators. It can process arbitrary data sources - quoting from the link: "arrays, enumerable classes, XML documents, relational databases, and third-party data sources." In addition, because it's fundamentally monadic, it can be used to express all sorts of computations, by: "utilizing query expressions as a general framework for readably composing arbitrary computations, include the construction of event handlers or monadic parsers." LINQ has been ported to PHP, JS & TS, and even Actionscript (although the ports aren't necessarily as capable as the version on .NET, which has language-level support for the features.) If you're interested in this general subject, I guarantee you you have a lot to learn from LINQ and the research work behind it.
tehlike 5y ago
Some discussion on prql https://news.ycombinator.com/item?id=30060784#30062329 https://news.ycombinator.com/item?id=30060784#30062329

minxomat 5y ago

KQL is a more LINQy language.

tehlike 5y ago
Looks interesting.

nickcox 5y ago

> The order of operations are out of whack and makes pipeline ing a little hard It makes it really hard to offer good autocompletions too.

tfigment 5y ago

This comes up for me in autocompleting editors. Have to start with "select * from table t;" then go back and then remove * and t.<ctrl-space> to get the hints to populate and then continue with joins which may require going back to select. Update and delete at least start with tables and joins.

userbinator 5y ago

It was probably influenced by COBOL, i.e. designed to read like English.

manigandham 5y ago
In fact, SQL (structured query language) was originally called SEQUEL (structured English query language).
- tehlike 5y ago
  I find prql more English for some reason...
ErwinSmout 5y ago
It was probably also influenced by Codd's own preference for the data language in calculus-based style (Codd's roots as a mathematician are to "blame" here), as opposed to algebra-based (the style typically preferred by developers).

technion 5y ago

This feels a lot like why Kusto was invented. It's very close to SQL, but written the other way around with the table name first, then the 'where', and finally the columns. https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...

tehlike 5y ago
Correct looks like it's trying to be the linq of sql. Looks good. MSFT really knows how to make programming tools...

zmxz 5y ago

In my case it's the opposite - SQL is perfectly readable and fits my mental model while the project you linked feels awkward and hard to read. However, it's good to have options, I wonder if prql will get traction. It would be great if it did.

7952 5y ago
A lot of the time people's mental model will be derived from the language though.
- ErwinSmout 5y ago
  And in the case of SQL, that is precisely the problem.
  - zmxz 5y ago
    So the problem is that SQL can be picked up fairly easily by English speaking population? How's that the problem and not advantage?
    - ErwinSmout 4y ago
      Because "speaking SQL" [even before SQL as such was even invented, hence the scare quotes] was intended as a skill to be practiced only by those who also grasped the [mathematical] logic of what they were doing when "speaking SQL". None of the people who "speak SQL" these days fit that bill. And FWIW, what I wrote was in reply to "mental model". It was *you* who apparently equated that with "speaking English". The "mental model" I described as "exactly the problem" is the "mental model" *of the corresponding logic*, which, though mathematically consistent, is hopelessly unintuitive. Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language.
      - zmxz 4y ago
        Having a language that already educated and skilled group of people can pick up fast is advantage. Targeting and working with people who grasp logic is good. Not bad. There's ever-repeating notion present that we need to invent tools that people, who are far from skilled, should be able to use to produce high quality results. Having a tool like SQL that's perfectly usable for a group of highly skilled people is excellent, not bad. > None of the people who "speak SQL" these days fit that bill. You can't prove this. This is what you believe is true. > And FWIW, what I wrote was in reply to "mental model". It was you who apparently equated that with "speaking English" Isn't it odd how we have the gift of being able to communicate, yet all we do is think we can wield telepathy and not use words to express ourselves. Unless you accurately express what it is you're thinking about, I can't fill in the gaps since I'm not telepathically enabled. > Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language. Of course it does.

vkazanov 5y ago

...and there's more to sql trouble than just troublesome syntax, typical for languages of its generation! 1. Sql is non-modular 2. Non-orthogonal 3. Its parroting of the (beautiful) relational data model is... opinionated at best. The standard is unreadable and not really implementable, one always has to resort to implementation docs. Realistically there are no alternatives, but... sql is no good.

rzmmm 5y ago
What do you think about using SQL views and functions to make composable, modular SQL code?
- tbrownaw 5y ago
  It doesn't work very well. Table Out1 has translated versions of fields AA..AZ from table In1. Table Out2 has translated versions of fields BA..BZ from table In1. table Out3 has translated versions of fields X..Z from table In2, and the translation depends on some of the AA..BZ fields, in both translated and untranslated forms. We'd end up with views that depended on other views and after expanding had like a dozen self-joins to add more fields that weren't included in the base view. And views that were selecting a big list of columns unchanged, plus adding a couple. But somehow despite being an absolute ^%#@!^&$! it still worked better than the pointy-clicky ETL tooling we'd been using previously. Mostly because of the run->debug->modify cycle time being faster, and only a little because of avoiding pointy-clicky stuff.
- vkazanov 5y ago
  1. Views are fine. They somewhat help with abstracting things away here and there, yes. But these are just macros, right? 2. What SQL functions are you talking about? There are builtins, e.g. avg, sum, and then there are User Defined Functions, which have nothing to do with SQL, mostly just a random external language or one of the non-portable PL/SQL flavours. Practically speaking, one cannot really write portable SQL outside of a very limited intersection of various dialects. And within this limit users just cannot build a reasonably portable code library. There is a reason why analysts working with SQL just copy massive query templates again and again and again...

ako 5y ago

Nice example of: always begin with the end in mind - first specify what data you want to see and then specify where it should come from. I think it helps you focus on the outcome required, and makes you more efficient deciding how to implement it. I use the same approach for methods in normal code: first decide on the signature of the method, and potentially a unit test to test it, and then think about the implementation.

tehlike 5y ago
I like to think of data transformation a chaining problem. Linq lambda syntax is kind of like that. db.Table.where(x=> x.name == 'abc').skip(10).take(20).groupBy() This is really hard in sql especially with temporary tables and nesting you need to do as soon as you need to do things a little out of order... Etc
- ErwinSmout 5y ago
  That's because the true foundation is that relational algebra is (a) an algebra and (b) closed over relations. (a) means you should have an expressions system that allows arbitrary nesting and (b) means you should be able to arbitrarily nest relational expressions in particular. SQL fails heavily on (b) (it fails less so than it used to do with the SQL:1992 standard but even so, it's still far from where we should have been and SQL itself is still an abomination). Moreover, your very examples illustrate how "thinking SQL" is *NOT* the path to finding proper solutions. .skip() and .take() clearly originate from the world of ranking problems and it drips off of every word that you were thinking of finding a "modern" way to specify [things like] "TOP 10", but those solutions are themselves a fundamentally crippled way to attack the problem.

cryptonector 5y ago

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs. Query q = db.from("foo").join("bar").using("id") .select("id","foothing","barthing"); However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the need for non-trivial SQL expressions in queries can't be avoided, nor can it be left to the host language. Also, I'd really like a SQL mode where no literal constants are allowed, as a mechanism to force the use of query parameters and prevent SQL injection.

tehlike 5y ago
Linq in c# fixes that.
- ErwinSmout 5y ago
  I doubt that it does. There is way more to "integrating [host] language and queries" than the MIN() of what Micro$oft engineers are (a) capable of understanding and (b) allowed by their own management to put in the products they come up with.
  - antonvs 5y ago
    Your comment may be true of many things that Microsoft does, but not LINQ. The creator of LINQ was Erik Meijer (https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientist) https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientis...), who was a leading researcher in programming languages and functional programming before shifting into industry. He's well known in the Haskell world for e.g. his work on bananas and lenses, and many other things. LINQ (https://en.wikipedia.org/wiki/Language_Integrated_Query https://en.wikipedia.org/wiki/Language_Integrated_Query) is basically an embedding of a general monadic framework, augmented with a set of query-specific operators. It can process arbitrary data sources - quoting from the link: "arrays, enumerable classes, XML documents, relational databases, and third-party data sources." In addition, because it's fundamentally monadic, it can be used to express all sorts of computations, by: "utilizing query expressions as a general framework for readably composing arbitrary computations, include the construction of event handlers or monadic parsers." LINQ has been ported to PHP, JS & TS, and even Actionscript (although the ports aren't necessarily as capable as the version on .NET, which has language-level support for the features.) If you're interested in this general subject, I guarantee you you have a lot to learn from LINQ and the research work behind it.

tehlike 5y ago

Some discussion on prql https://news.ycombinator.com/item?id=30060784#30062329 https://news.ycombinator.com/item?id=30060784#30062329