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

Re: I don't need your query language

#301

Post by frognumber » Sun, Jun 18, 2023, 10:41 PM UTC

t8sr wrote: post #179

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…

I'm getting months of work because I need a production-grade system:

- Reasonable performance

- Test infrastructure

- Pretty comprehensive SQL support. A lot of computation happens in the queries.

- Maintainable

- Documented

Most things can be hacked together quickly, but that's different from correct production-grade code.

Re: I don't need your query language

#302

Post by t8sr » Mon, Jun 19, 2023, 8:12 AM UTC

frognumber wrote: post #301

t8sr wrote: post #179

Earlier quoted context omitted.

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…

I'm getting months of work because I need a production-grade system: - Reasonable performance - Test infrastructure - Pretty comprehensive SQL support. A lot of computation happens in the queries. - Maintainable - Documented Most things can be hacked together quickly, but that's different from correct production-grade code.

I was kind of assuming you had those things for a YAML based frontend, and just wanted to implement SQL support.

I can see that if your YAML solution doesn’t have a way to express GROUP BY, so the backend doesn’t support it, then of course that’ll be extra work, but then that’s IMO a different feature.

SQL itself is a tiny language - a parser that transforms it into your YAML based AST really would be pretty small. Here’s the one I made many years ago: https://github.com/google/dotty/blob/master/efilter/parsers/...

It’s not the best quality code, and it doesn’t implement SQL92, but we did run it in production.

Re: I don't need your query language

#303

Post by chrisjc » Mon, Jun 19, 2023, 2:06 PM UTC

mcdonje wrote: post #79

ayhanfuat wrote: post #41

Earlier quoted context omitted.

That's valid DuckDB syntax

DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases. I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language. https://duckdb.org/2022/05/04/friendlier-sql.html

Agreed... Their SQL is awesome and seemingly only getting better.

My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance.

> The three window functions will also share the data layout, which will improve performance.

https://duckdb.org/docs/sql/window_functions#window-clauses

I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.

Re: I don't need your query language

#304

Post by frognumber » Mon, Jun 19, 2023, 5:54 PM UTC

t8sr wrote: post #302

frognumber wrote: post #301

Earlier quoted context omitted.

I'm getting months of work because I need a production-grade system: - Reasonable performance - Test infrastructure - Pretty comprehensive SQL support. A lot of computation happens in the queries. - Maintainable - Documented Most things can be hacked together quickly, but that's different from correct production-grade code.

I was kind of assuming you had those things for a YAML based frontend, and just wanted to implement SQL support. I can see that if your YAML solution doesn’t have a way to express GROUP BY, so the backend doesn’t support it, then of course that’ll be extra work, but then that’s IMO a different feature. SQL itself is a tiny language - a parser that transforms it into your YAML based AST really would be pretty small. H…

My current JSON-based system doesn't implement full SQL semantics. We definitely don't have (or actually need) GROUP BY. Doing full SQL would require ASTs, a query optimizer, and similar. Right now, it's SQL-like, but the implementation is really quite dumb.

What I actually want is the whole dotty / efilter system, only with things like documentation.

We really do care about performance, though. From your code, this would not work:

api.apply("SELECT name FROM users WHERE age > 10", vars={"users": ({"age": 10, "name": "Bob"}, {"age": 20, "name": "Alice"}, {"age": 30, "name": "Eve"}))

It would really need to be:

query = api.compile("SELECT name FROM users WHERE age > 10")

query(vars={"users": ({"age": 10, "name": "Bob"}, {"age": 20, "name": "Alice"}, {"age": 30, "name": "Eve"})))

Or more likely, vars would be a closure which would allow it to interact with the proper data stores.

As a footnote, that looks like a really nice project. I wish it were supported, maintained, and finished.