Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Short Story About SQL’s Biggest Rival

holistics.io

Post reply on HN
Print view View original

101–110 of 128 posts

Re: A Short Story About SQL’s Biggest Rival

#101

Post by SPBS »

wenc wrote: post #70
exabrial wrote: post #16

Really the the only issue I have with SQL is NULL != NULL. This creates an impedance mismatch with most languages... MySQL sort of solves this problem with a operator, which I wish was the default for ORMs to use. There are a lot of other minor nitpicks but a lot of criticisms come down to the actual RDMS not SQL itself.

I consider myself a fan of SQL, but my nitpick is more around named calculated columns. For instance: SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1 FROM tbl WHERE AVG(col1) OVER (PARTITION BY col2) > 10.0 I wish I could just do: SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1 FROM tbl WHERE partcol1 > 10.0 But I can't, because the WHERE clause is processed before the SELECT clause. So if I have a bunch…

Yeah it's mentioned in this blog post https://blog.jooq.org/2014/01/06/the-sql-languages-most-miss... that SQL is missing Common Column Expressions, and that using Common Table Expressions reuse columns is simply a band-aid fix over the lack of those.

For example, your example would have been more naturally expressed as

    SELECT partcol1
    FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2))
    WHERE partcol1 > 10.0

Re: A Short Story About SQL’s Biggest Rival

#102

Post by wenc »

SPBS wrote: post #101
wenc wrote: post #70

Earlier quoted context omitted.

I consider myself a fan of SQL, but my nitpick is more around named calculated columns. For instance: SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1 FROM tbl WHERE AVG(col1) OVER (PARTITION BY col2) > 10.0 I wish I could just do: SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1 FROM tbl WHERE partcol1 > 10.0 But I can't, because the WHERE clause is processed before the SELECT clause. So if I have a bunch…

Yeah it's mentioned in this blog post https://blog.jooq.org/2014/01/06/the-sql-languages-most-miss... that SQL is missing Common Column Expressions, and that using Common Table Expressions reuse columns is simply a band-aid fix over the lack of those. For example, your example would have been more naturally expressed as SELECT partcol1 FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2)) WHERE partcol1 > 10…

Wow thanks. I've never heard it expressed as a Common Column Expression. It makes sense to include it in the FROM statement because that's usually parsed and executed first.

Re: A Short Story About SQL’s Biggest Rival

#103

Post by nl »

Composition might be desirable but in the 1980s and early 90s it didn't really matter because the databases of the day weren't powerful enough to do the multiple table joins where composition is useful with enough speed to be usable.

DBAs of the day would spend a long time optimising physical storage of tables and building aggregation tables to make up for this performance deficit.

Re: A Short Story About SQL’s Biggest Rival

#104

Post by ilaksh »

This is a fundamental difference that I have with almost all of humanity.

People do not know the difference between popularity and merit. They don't realize that the reason we do things is because that is how we do things. And they put a lot of effort into rationalizing the way we do things, without realizing the subconscious psychological (rather than rational) basis for that.

Most people fundamentally are generally unable to question assumptions about technology or how the world works.

This is one reason why, even though I am rooting for the human species, I am doubtful we will be able to stay relevant for long as autonomous general machine intelligence is built and deployed.

Even with extensive augmentation, it's obvious that there are just severe limitations to the human mind.

The nice thing is that we have the opportunity to design successors that will not be limited in so many ways.

Re: A Short Story About SQL’s Biggest Rival

#105

Post by enjo »

_eigenfoo wrote: post #100
jhallenworld wrote: post #58

I don't like either syntax. Wikipedia has this example: QUEL: range of E is EMPLOYEE retrieve into W (COMP = E.Salary / (E.Age - 18)) where E.Name = "Jones" SQL: select (e.salary / (e.age - 18)) as comp from employee as e where e.name = "Jones" I would prefer an operator syntax that directly mimics relational algebra. Something like: w = employee(name == "Jones")[comp = salary / (age - 18)] So () is "where", [] is "p…

One major strength of SQL is its readability - it reads so much like English that a non-technical stakeholder could conceivably understand queries. Do you not find that this is a valuable thing that's lost with relational-algebra-esque syntax?

I haven’t one time in my 20 years of development had a time where that readability mattered tho. SQL very quickly becomes too complex for people who don’t intimately understand the language to make any sense out of it. Show a layman an INNER JOIN and see if they can make any sense out of what’s happening...they’ll just do what they do in real life: ask an engineer.

Re: A Short Story About SQL’s Biggest Rival

#106

Post by andy-p »

exabrial wrote: post #16

Really the the only issue I have with SQL is NULL != NULL. This creates an impedance mismatch with most languages... MySQL sort of solves this problem with a operator, which I wish was the default for ORMs to use. There are a lot of other minor nitpicks but a lot of criticisms come down to the actual RDMS not SQL itself.

Pascal's book [1] highlights various issues with the language & is worth reading if you are not familiar with it.

Example:

select * from customers c inner join orders o on o.order_id = c.customer_id

Legal syntax but clearly incorrect.

[1] https://books.google.co.uk/books?id=t9ZQAAAAMAAJ&source=gbs_...

Re: A Short Story About SQL’s Biggest Rival

#107

Post by prostodata »

jhallenworld wrote: post #58

I don't like either syntax. Wikipedia has this example: QUEL: range of E is EMPLOYEE retrieve into W (COMP = E.Salary / (E.Age - 18)) where E.Name = "Jones" SQL: select (e.salary / (e.age - 18)) as comp from employee as e where e.name = "Jones" I would prefer an operator syntax that directly mimics relational algebra. Something like: w = employee(name == "Jones")[comp = salary / (age - 18)] So () is "where", [] is "p…

It should be noted that the expression like

    comp = salary / (age - 18)
is (strictly speaking) not part of the relational algebra because it is not using set operations (like join or union). It was added to the relational model because we hardly can process data without such expressions.

A better way to formally describe such calculated columns is to introduce functions and treat them as first class elements of the data model. In particular, function operations are better than set operations in these cases [1]:

- Calculating data using calculated columns. We describe a calculated column as a function without generating new relations (as opposed to select-from)

- Aggregating data. We can add an aggregate column without generating new relations (as opposed to groupby)

- Linking data. We can add derived link columns without generating new relations (as opposed to join)

This function-based approach to data modeling and data processing was implemented in Prosto [2] which demonstrates how many typical relational tasks can be solved using functions.

[1] Why functions and column-orientation? https://prosto.readthedocs.io/en/latest/text/why.html

[2] Functions matter! No join-groupby, No map-reduce. https://github.com/prostodata/prosto

Re: A Short Story About SQL’s Biggest Rival

#108

Post by sergeykish »

jhallenworld wrote: post #58

I don't like either syntax. Wikipedia has this example: QUEL: range of E is EMPLOYEE retrieve into W (COMP = E.Salary / (E.Age - 18)) where E.Name = "Jones" SQL: select (e.salary / (e.age - 18)) as comp from employee as e where e.name = "Jones" I would prefer an operator syntax that directly mimics relational algebra. Something like: w = employee(name == "Jones")[comp = salary / (age - 18)] So () is "where", [] is "p…

Unbiased samples should match typography

QUEL:

    range of e is employee
    retrieve into w (comp = e.salary / (e.age - 18))
    where e.name = "Jones"
SQL:

    select
    (E.Salary / (E.Age - 18)) as COMP
    from EMPLOYEE as E
    where E.Name = "Jones"
Now QUEL looks like modern language while SQL is jarred mess.

Re: A Short Story About SQL’s Biggest Rival

#109

Post by sergeykish »

alquemist wrote: post #69
didibus wrote: post #46

Earlier quoted context omitted.

This argues that composability is the most important consideration of a domain specific language. But I think, as proved by SQL taking over, the UX is more important. Any programming language must consider the programmer and its humanity and natural way of thinking and reasoning to win in getting the most adoption and mindshare. Usability does matter as the user of any programming language is a human being. Was QUEL…

There are a handful of examples on Wikipedia: https://en.wikipedia.org/wiki/QUEL_query_languages . One example: retrieve (a=count(y.i by y.d where y.str = "ii*" or y.str = "foo"), b=max(count(y.i by y.d))) Not a particularly clear 'jumps at you' obvious semantic: * Are a and b aggregation functions or window functions? If aggregations, how do they compose if the 'by' scopes are different? * What does max(count(... by…

> The following table lists aggregate functions:

> count() Number of entries in column

> max() Maximum value in column

> The by clause causes the function to return a set of results, as opposed to a single result. One result is returned for each grouping specified by the by clause. Think of by as meaning "for each."

I assume it evaluates like retrieving set and scalar.

      a   |   b
    --------------
    set 1 | scalar
    set 2 | scalar
[1] http://docs.huihoo.com/ingres/9.3/QUELRef.pdf

Re: A Short Story About SQL’s Biggest Rival

#110

Post by masklinn »

enjo wrote: post #105
_eigenfoo wrote: post #100

Earlier quoted context omitted.

One major strength of SQL is its readability - it reads so much like English that a non-technical stakeholder could conceivably understand queries. Do you not find that this is a valuable thing that's lost with relational-algebra-esque syntax?

I haven’t one time in my 20 years of development had a time where that readability mattered tho. SQL very quickly becomes too complex for people who don’t intimately understand the language to make any sense out of it. Show a layman an INNER JOIN and see if they can make any sense out of what’s happening...they’ll just do what they do in real life: ask an engineer.

I'm in full agreement. Trivial SQL is somewhat readable, but anything non-trivial is not, and the order of SQL queries, that there is essentially no relationship between the syntactic and semantic orderings, make it hard to understand.

QUEL is better there but still not great unless its execution semantics are different than SQL's (aka semantically does it filter before or after selection?)

Post reply on HN