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

PRQL – A proposal for a better SQL

github.com

Post reply on HN
Print view View original

271–280 of 302 posts

Re: PRQL – A proposal for a better SQL

#271

Post by dotancohen »

dspillett wrote: post #264
dotancohen wrote: post #260

Earlier quoted context omitted.

> SQL null handling rules are terrible. I understand them, I work with them, but at > the same time, they are so different from other languages concept of "null" that > they are easy to trip over. Could you elaborate? I'm really only versed in the MySQL accent, but I don't find anything unusual or unexpected about NULLS in MySQL. If there are any pitfalls that I should be aware of, I'd love to know about it here befo…

Not the person you replied to, but I don't think by “from other languages” he means other dialects of SQL. Instead, I think other languages away from the database are being referred to - in many of those NULL is treated like any other value², for instance in Javascript¹ null==null is true and null!=null is false, and due to type coercion null on its own is “falsey”. Personally I have no problem with SQLs handling of…

  > The one thing that I have occasionally tripped over...this doesn't seem intuitive.
This paragraph led me to discovering this oddity:

  > select 'true' from dual where 3 not in (1, 2, null)
  > Empty set (0.00 sec)
That's unexpected, and definitely something that I should be aware of. Thank you.

Re: PRQL – A proposal for a better SQL

#272

Post by makeitdouble »

Maxion wrote: post #249
makeitdouble wrote: post #221

Earlier quoted context omitted.

In my experience, a lot of very semantically reasonable and readable code end up with very penalizing SQL at the end, and it's a real challenge to then rewrite the whole into decent queries. There can be part of an app where a very bad query here and there is not important, but more often than not it creeps up in key parts of the user experience, and it becomes very hard to untangle when it becomes something importan…

Im not so sure it's always best to optimize for absolute performance, how you should code a solution to a specific problem is always dependant on it's context IMO. I work on a lot of smaller IT projects for SME's, internal tools and platforms that are made on a small budget and, thus, end up having a tight deadline in order to not go over budget. The vast majority of these projects are versions of CRUD apps for this…

I think it’s all nice and fine if performance has no impact (which can be the case). For instance if your client can wait 30s for a report, no big deal.

Things go down when a query that used to take 4s now takes 30s as the product has taken off, handles 10x more data, and it’s not one user but a few hundreds having their request queued.

You wont have the luxury to not re-write that part in (probably) hard to read performant code. It can be a huge enough effort to blow away your deadlines and budget and sour pretty hard the relationship if your team struggles on something they aren’t used to do at all.

Re: PRQL – A proposal for a better SQL

#274

Post by phailhaus »

BeefWellington wrote: post #243
phailhaus wrote: post #116

Earlier quoted context omitted.

SELECT id, name, author Quick, what is this query about? What's ironic is that I think you have it backwards: the columns are the implementation detail, not the table. The table is the context: you can't change that without having to change everything else. But columns are the last step, the selection after the filters, joins, etc. They can be changed at any time without affecting the logic.

It's query asking for the id, name, and author fields. Very straightforward, I have no idea how this is confusing. > The table is the context: you can't change that without having to change everything else. Except even in the provided single-table example this isn't true - you're getting subselected/CTEd results. No functional joins are demonstrated unfortunately. For example: from employees left_join positions [id=e…

You restated the query; I was asking what it's about. Is it a query across publications? Or is it a query over news articles? That context changes everything: how the query is written, what it can be joined with, how it can be filtered, how it is used, etc. Putting the FROM clause first means that you immediately have context to understand the rest of the query.

Re: PRQL – A proposal for a better SQL

#275

Post by yencabulator »

dotancohen wrote: post #270
Sankozi wrote: post #267

Earlier quoted context omitted.

In SQL NULL does not mean "no value" it means "unknown value". Existence of such value introduces three value logic because expression "NULL = " is neither true nor false. This makes queries harder to understand without any benefit. Additionally "unknown value" concept is not used consistently. Things like DISTINCT, or UNIQUE indexes (in some databases) treat NULL as single "no value".

> Existence of such value introduces three value logic because expression "NULL = " is neither true nor false. Could you elaborate on that? I'm thought that in SQL `NULL=NULL` returns FALSE, much like the floats `NAN==NAN` returns false: > select if(null=null, "Yes", "No") > +----------------------------+ > | No | > +----------------------------+ What does it mean that this is neither TRUE or FALSE? I very much appre…

The value in your if is not false, it's NULL. NULL=NULL behaves exactly like NULL=42, the value is NULL. Which is what the parent was trying to explain.

With Postgres:

  postgres=# \pset null 
  postgres=# select null, null=null, null=42;
   ?column? | ?column? | ?column?
  ----------+----------+----------
      |    | 
  (1 row)

Re: PRQL – A proposal for a better SQL

#276

Post by yencabulator »

dotancohen wrote: post #271
dspillett wrote: post #264

Earlier quoted context omitted.

Not the person you replied to, but I don't think by “from other languages” he means other dialects of SQL. Instead, I think other languages away from the database are being referred to - in many of those NULL is treated like any other value², for instance in Javascript¹ null==null is true and null!=null is false, and due to type coercion null on its own is “falsey”. Personally I have no problem with SQLs handling of…

> The one thing that I have occasionally tripped over...this doesn't seem intuitive. This paragraph led me to discovering this oddity: > select 'true' from dual where 3 not in (1, 2, null) > Empty set (0.00 sec) That's unexpected, and definitely something that I should be aware of. Thank you.

SQL NULL is like NaN, it never equals anything. (And on top of that, the result of any comparison with NULL isn't a boolean, it's NULL.)

Similarly (except the result is a boolean),

  $ deno eval --print 'NaN == NaN'
  false

Re: PRQL – A proposal for a better SQL

#277

Post by paulhodge »

jseban wrote: post #258
cribwi wrote: post #248

Earlier quoted context omitted.

In some cases for removing repeating (intermediate) calculations, I generally find it easier to use a lateral join (in postgres), like select title, country, avg(salary) as average_salary, sum(salary) as sum_salary, avg(gross_salary) as average_gross_salary, sum(gross_salary) as sum_gross_salary, avg(gross_cost) as average_gross_cost, sum(gross_cost) as sum_gross_cost, count(*) as emp_count from employees, lateral (…

So now we have easily come up with three different ways of rewriting the query to avoid that duplication (which obviously was not a problem at all to begin with): subquery, CTE and lateral join. And there are also several more well known ways (views, custom functions, computed columns etc) so the whole premise now for even inventing a "better" language than SQL is then false? Or what am I missing. It's also weird how…

If there's multiple ways to do the same thing that's usually a BAD thing in terms of language design. Especially if some approaches are just newbie traps that experts learn to avoid, or if deciding the best method is a really subtle context-dependent decision. The ideal design is that the language encourages the one obviously "good" way to do it.

Re: PRQL – A proposal for a better SQL

#278

Post by MarkLowenstein »

maximilianroos wrote: post #2

I wrote this over the holidays, because I find SQL wonderfully elegant in its function, but really frustrating in its form. Let me know any feedback — as you can see it's still at the proposal stage. If it gains some traction I'll write an implementation.

IMO you are at the forefront of where query languages need to and will go.

Some programmers like you see that SQL ordering is backwards to human thinking, except in the simplest cases. But many people with practice and sunk costs in their SQL expertise will be resistant. The resistance usually wins the day.

But sometimes, a useful tool gets created by one person, and a rift is created in that resistance. Think John Resig creating jQuery, leading to LINQ and many other similar patterns. You could be that person for database query languages, but how do you ensure that?

Maybe imagine what made jQuery easy to adopt and indispensable for programmers: easy availability as a simple .js download; solved the problem of DOM differences between browsers. Good luck to you, and thanks for sharing.

Re: PRQL – A proposal for a better SQL

#279

Post by MarkLowenstein »

MarkLowenstein wrote: post #278
maximilianroos wrote: post #2

I wrote this over the holidays, because I find SQL wonderfully elegant in its function, but really frustrating in its form. Let me know any feedback — as you can see it's still at the proposal stage. If it gains some traction I'll write an implementation.

IMO you are at the forefront of where query languages need to and will go. Some programmers like you see that SQL ordering is backwards to human thinking, except in the simplest cases. But many people with practice and sunk costs in their SQL expertise will be resistant. The resistance usually wins the day. But sometimes, a useful tool gets created by one person, and a rift is created in that resistance. Think John R…

Also PRQL/Prequel is a great name. Just that can take you far.

Re: PRQL – A proposal for a better SQL

#280

Post by victorNicollet »

There are plenty of good ideas in here, I really like it.

Here's a few things I can say from my experience (I have been working on a "better SQL" for almost 10 years now):

- Your "aggregate by" taking a set of calculations to be performed on the groups is an excellent idea: the fact that "group by X" isn't a separate statement from the "select" that describes the calculations, means you can keep the idea that each line is an operation applied to the result of the previous line.

- Be very careful with auto-generated column names, they will bite you when you implement "Go To Definition" or "Rename Symbol" tools.

- The use of "filter" instead of "where" is a missed opportunity to be easily understood by people who know SQL. I suspect that you wanted to avoid the WHERE/HAVING confusion, but I'm not sure it is worth it. I do appreciate "sort" instead of "order by" (it's good to have only one keyword).

- To support a space-based call convention `f a b` you will pay a very heavy price in the language grammar. Also, allowing optional arguments in this convention will prevent you from implementing first-class, higher-order or partial functions. This may be a price you have decided to pay, if not, look into the OCaml rules for optional arguments, they are very well thought-out (the general idea being, you must have at least one non-optional positional argument _after_ the optional ones).

- Having [X, Y] be your list syntax, and X be a shorthand notation for [X], works out pretty well in practice, so long as you have specific positions in your grammar where lists are expected, so there is no ambiguity between X-as-a-column and X-as-a-shorthand-for-[X]

- General syntax opinions: "from" is fine, your CTE syntax (non-point-free) is fine. Boolean operators should be usable as infix keywords (or prefix if unary), just like arithmetic ones, because that is the first thing users will attempt.

- Raw syntax: for the sake of your syntax highlighter, I recommend having asymmetrical delimiter pairs like [|SQL|] instead of symmetrical pairs like `SQL`.

In addition to the above, one of the issues I have with SQL in an analytics context is that it tends to "lose" the actual tables: since it operates by joining/filtering/aggregating tables into new tables, your "products" table from the database schema is quickly replaced by a CTE named "products5" which (in theory) contains the same lines as the original, plus some additional columns, but (in practice) some lines have been lost or duplicated in the middle. I called this the "vanishing schema" paradox[1]. Your design, with the "let" statements to add columns to a table, solves one portion of the problem, but I wonder if you would consider this worth solving more fully.

[1]: https://nicollet.net/blog/vanishing-schema-paradox.html

Post reply on HN