4 ms·

...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 parr

...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...
ErwinSmout 5y ago
Imagine you need to write a query to answer "what are the cities with more than 4000 inhabitants". What more natural could there be than to write this as (old 1992 correlated query style) SELECT cityName FROM Cities C WHERE ((SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName) > 4000) Agreed ??? YOU CAN'T DO THIS IN SQL [standard version] !!! You must write SELECT cityName FROM Cities C WHERE (4000 < (SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName)) This makes SQL about the only language in the world that allows you to write "a<b" in certain places but forbids you to write "b>a" in those very same places. Some professional piece of language design. (Disclaimer : I don't know how matters are in SQL:2016 wrt to this issue, but it drove a former IBM employee who was at the time a member of the SQL standards committee to (a) start work on TTM and (b) leave IBM as soon as he could.)

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...

ErwinSmout 5y ago

Imagine you need to write a query to answer "what are the cities with more than 4000 inhabitants". What more natural could there be than to write this as (old 1992 correlated query style) SELECT cityName FROM Cities C WHERE ((SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName) > 4000) Agreed ??? YOU CAN'T DO THIS IN SQL [standard version] !!! You must write SELECT cityName FROM Cities C WHERE (4000 < (SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName)) This makes SQL about the only language in the world that allows you to write "a<b" in certain places but forbids you to write "b>a" in those very same places. Some professional piece of language design. (Disclaimer : I don't know how matters are in SQL:2016 wrt to this issue, but it drove a former IBM employee who was at the time a member of the SQL standards committee to (a) start work on TTM and (b) leave IBM as soon as he could.)