I don't need your query language • Hacker News Board

Re: I don't need your query language

#171

Post by marcosdumay » Sat, Jun 17, 2023, 2:56 PM UTC

idoubtit wrote: post #88

ako wrote: post #16

Earlier quoted context omitted.

Always start with the end in mind, first what your goal is then how to achieve it. Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.

> Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. An experienced person won't do that. For any moderately complex SQL query, before writing it I already have in mind the several jointures I'll need, since I usually know the tables and FK I'm working with. It's like following the edges of a graph, all in my head. But I don't know all the fields of the…

If you mean a developer with deep and recent experience on the exact tables your query will use, then yeah, this one won't do that.

But if you mean any other kind of experience or expertise, you are wrong. Those do not correlate with how a person assembles a query.

Re: I don't need your query language

#172

Post by cerved » Sat, Jun 17, 2023, 3:04 PM UTC

ledgerdev wrote: post #64

tester756 wrote: post #9

Earlier quoted context omitted.

C#'s LINQ (query syntax, not methods) got it right var result = from s in stringList where s.Contains("Tutorials") select s;

I recall Anders saying on some podcast they did that so they could provide auto-complete. Funny thing is that I used it for a couple years, but almost never use the linq syntax any more, and not sure why.

Probably because if you wanted to write something like SQL, you would write it in SQL, and if you want to write C#, you write C#

Linq query syntax is just dumb

Re: I don't need your query language

#173

Post by marcosdumay » Sat, Jun 17, 2023, 3:07 PM UTC

masklinn wrote: post #69

hosteur wrote: post #58

I would love if sql would support a slight syntax change of accepting From table select col; as an optional alternative to select col from table; This would allow autocompleting col names in editors. Other than that I quite like sql being the standard db query language.

That only fixes trivial selects. But you still have issues with e.g. GROUP BY, especially since the dependency is circular: - barring extensions you can only select grouping expressions or aggregates - but instead of repeating grouping expressions you can refer to a select expression (by index, some databases also allow the alias) The "spec" order of evaluation for queries is WITH, FROM, WHERE, GROUP BY, HAVING, SELE…

You can easily change it to make `group by` behave much better, and `having` redundant with `where`, like it should always have been if you just evaluate from start to end, without any hidden reordering.

Re: I don't need your query language

#174

Post by ttfkam » Sat, Jun 17, 2023, 3:09 PM UTC

fbdab103 wrote: post #156

galaxyLogic wrote: post #136

The problem with SQL is that it is not a (very) composable language. The documentation for EdgeDb goes into some detail about that and shows an alternative better language for data-queries. https://www.edgedb.com/showcase/edgeql To understand why SQL is bad, you must first be shown something better, and EdgeDb seems to be such better more composable language.

I like PRQL [0]. It fixes a lot of the SQL warts and compiles down to regular SQL (think Typescript to JS). The syntax example on the PRQL homepage from invoices filter invoice_date >= @1970-01-16 derive [ transaction_fees = 0.8, income = total - transaction_fees ] filter income > 1 group customer_id ( aggregate [ average total, sum_income = sum income, ct = count, ] ) sort [-sum_income] take 10 join c=customers [==c…

I'd much rather debug an SQL query with CTEs, which is what that is approximating.

The notion that SQL is not composable is either a lie or simply repeated by folks with only a cursory knowledge of SQL from 20 years ago.

Views, set-returning functions, CTEs, and more: all examples of composability in SQL.

Then of course there's the issue of security where folks tend to put all of their access constraints into their middleware AFTER the data has already been returned over the wire in bulk. Take a moment to consider role-based access control and row-level security policies. Instead of trying to track down every possible spot where a JOIN could have crept in past the code reviews (you code review your DDL and DML, right?), you set your GRANTs, REVOKEs, and POLICYs at the points in your data model that need them; restricted data never makes it into intermediate result sets let alone the final result set and the wire.

Don't misunderstand me. GRANT, REVOKE, and POLICY can be a real PITA, but that's because *security* is a PITA, not the SQL syntax to enforce it. Anyone who tells you their app solves your data security problems in the app tier with a point and click is a lying salesman.

Re: I don't need your query language

#175

Post by divan » Sat, Jun 17, 2023, 3:11 PM UTC

FancyQL, which he mentions, is, of course, EdgeQL – an insanely good query language of EdgeDB.

The truth is EdgeQL is so good that you never want to go back to SQL ever after. It's even a bit depressing when you realize how much time has been spent crafting SQL queries and dancing around it. EdgeQL renders most of those struggles obsolete.

The author of this post has written a book about SQL Window Functions, and probably developed an attachment with his SQL expertise. He probably doesn't need another query language – nobody likes to return to the "beginner" level after their identity has been attached to the "expert" level.

But people who hadn't developed abusive relationships with SQL expertise, they absolutely need "your query language".

Re: I don't need your query language

#176

Post by divan » Sat, Jun 17, 2023, 3:16 PM UTC

divan wrote: post #175

FancyQL, which he mentions, is, of course, EdgeQL – an insanely good query language of EdgeDB. The truth is EdgeQL is so good that you never want to go back to SQL ever after. It's even a bit depressing when you realize how much time has been spent crafting SQL queries and dancing around it. EdgeQL renders most of those struggles obsolete. The author of this post has written a book about SQL Window Functions, and pro…

Shameless plug – I wrote a post with my experiences with EdgeDB last year. It's a bit outdated already, EdgeDB 3.0 launch is happening next week, but I can only add good things to the post so far.

My experience with EdgeDB (Jul 26, 2022)

https://divan.dev/posts/edgedb/

Re: I don't need your query language

#177

Post by ttfkam » Sat, Jun 17, 2023, 3:18 PM UTC

ayhanfuat wrote: post #41

LunicLynx wrote: post #31

Earlier quoted context omitted.

Exactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need. What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry. My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.

That's valid DuckDB syntax

Because the DuckDB folks used the Postgres query parser.

It's been an alternative in Postgres for decades.

Re: I don't need your query language

#178

Post by ttfkam » Sat, Jun 17, 2023, 3:19 PM UTC

LunicLynx wrote: post #31

ako wrote: post #16

Earlier quoted context omitted.

Always start with the end in mind, first what your goal is then how to achieve it. Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.

Exactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need. What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry. My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.

In Postgres, it's

   TABLE foo
   LIMIT 100;

No SELECT with columns and no FROM keyword.

Re: I don't need your query language

#179

Post by t8sr » Sat, Jun 17, 2023, 3:21 PM UTC

frognumber wrote: post #27

I'm working on a query language right now! Why not SQL? Lack of tooling for SQL. Yes, SQL lacks tooling. There's a ton of stuff to build a SQL client, obviously. However, on the other side: - I have no sane way to parse SQL - I have no sane way to comprehend SQL Writing a SQL query system would be many months of work. Tossing together a good-enough query language with standards like JSON or YAML means I can json.load…

This comment is full of hyperbole. Writing a precedence-climbing SQL parser should take a few days. I know because I've done it. I don't know where you're getting "months of work" - mine ended up being like 600 lines of Python.

And I don't know what you mean by "no sane way to comprehend SQL" - I guess the millions of data people in the industry are just insane?

Cobbling it together with YAML or JSON is a reasonable trade off if you're in a hurry, but I don't understand how we got to the point where we're throwing out estimates like "writing a parser is months of work" and "it's a PhD project to render some glyphs [1]"

1: https://news.ycombinator.com/item?id=28743687

Re: I don't need your query language

#180

Post by littlestymaar » Sat, Jun 17, 2023, 3:23 PM UTC

tomnipotent wrote: post #153

littlestymaar wrote: post #147

Earlier quoted context omitted.

> Which costs very little CPU in 2023. That's a very bad argument for an RDBMS, since you want to make it scale vertically as much as you can (unlike your application server, horizontally scaling your database is a completely different matter, and isn't straightforward at all).

The added cost of JSON serialization is easily offset by reducing the total number of overall queries and implicit (or explicit) transactions. The additional parallelization the RDBMS can achieve is generally greater than the added JSON serialization and extra network bandwidth.

Maybe, but this isn't the same as saying CPU cost doesn't matter because it's cheap.