Against SQL • Hacker News Board

Re: Against SQL

#241

Post by goto11 » Sat, Jul 10, 2021, 2:29 PM UTC

laszlokorte wrote: post #213

laszlokorte wrote: post #174

Earlier quoted context omitted.

But if I have a circle relation and a triangle relation, how do I create a foreign key for the favourite_shape column in my user relation?

Thinking a bit more about it I think this would be a very elegant solution for Tagged-Unions in SQL: - simply allow tagged unions for foreign key constraints: CONSTRAINT my_fk FOREIGN KEY (own_column) REFERENCES EITHER table_a (a_id) OR table_b (b_id) OR table_c (c_id) - add join syntax to join via defined foreign key: FROM own_table JOIN VIA my_fk The union FK would store an additional flag determining the target ta…

If you put the foreign keys on the table_a/b/c tables, then you can express the same without having to introduce tagged unions.

Re: Against SQL

#242

Post by paavohtl » Sat, Jul 10, 2021, 2:30 PM UTC

thom wrote: post #92

OskarS wrote: post #91

Earlier quoted context omitted.

You’re probably thinking of C/C++ unions, and they do indeed have quite tricky semantics and are difficult to use correctly. What the article and the other commentators are talking about is more properly called a “sum type”, like Rust’s enums, for instance. Those are different things from C unions. In languages with first class support for sum types, they are used everywhere, it’s an incredibly useful concept.

Gotcha, ta. That becomes a tricky problem when we're talking about something that's primarily a storage engine though, right?

Well, sum types are not magic - they must be stored in the memory somehow. Rust's enums tend to use a simple approach: there's a large enough integer tag (usually an 8-bit integer), followed by enough space to store any of the variants. The same exact encoding could be used with a database storage engine too.

Re: Against SQL

#243

Post by barrkel » Sat, Jul 10, 2021, 2:32 PM UTC

jiggawatts wrote: post #220

spicybright wrote: post #182

Earlier quoted context omitted.

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

> On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use. That's precisely because of some the flaws of SQL outlined in the article. Generating SQL is complex . Generating portable SQL is impossible. Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene…

There's a lot more wrong with DDL than the inability of expressing it as DML. Some SQL doesn't scale well, but DDL fundamentally scales really badly. It needs either an async or resumable execution model, for one thing, which doesn't map well to connection-oriented transactions. Big migrations take days to complete. That's too long to be very reliable in a distributed system when operating on a synchronous basis.

Re: Against SQL

#244

Post by laszlokorte » Sat, Jul 10, 2021, 2:33 PM UTC

goto11 wrote: post #234

laszlokorte wrote: post #174

Earlier quoted context omitted.

But if I have a circle relation and a triangle relation, how do I create a foreign key for the favourite_shape column in my user relation?

You would have the foreign key in the circle/triangle relations pointing to favourite_shape rather than the other way around.

But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.

Re: Against SQL

#245

Post by laszlokorte » Sat, Jul 10, 2021, 2:34 PM UTC

goto11 wrote: post #241

laszlokorte wrote: post #213

Earlier quoted context omitted.

Thinking a bit more about it I think this would be a very elegant solution for Tagged-Unions in SQL: - simply allow tagged unions for foreign key constraints: CONSTRAINT my_fk FOREIGN KEY (own_column) REFERENCES EITHER table_a (a_id) OR table_b (b_id) OR table_c (c_id) - add join syntax to join via defined foreign key: FROM own_table JOIN VIA my_fk The union FK would store an additional flag determining the target ta…

If you put the foreign keys on the table_a/b/c tables, then you can express the same without having to introduce tagged unions.

As I said in response to a sibling: But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.

Re: Against SQL

#246

Post by MetaWhirledPeas » Sat, Jul 10, 2021, 2:35 PM UTC

thrusong wrote: post #240

slx26 wrote: post #67

I think the problem of this essay is that it's overly technical: only those versed well enough in SQL will really care to read the whole thing, and if they are already at that level, either they accepted that "SQL will get the job done in the end", or they learned to live along it and now even kinda embrace it, and are happy to write about how the examples are very poor and dismiss the critique based on that, when th…

I completely agree with you. Is SQL perfect? No. Have I accepted and embraced it? Yes, because it'll get the job done. I also happen to really dislike how the author hasn't capitalized the syntax like SELECT FROM WHERE or CREATE TABLE which, to me, poorly affects the legibility and therefore makes me less interested in reading the argument overall.

Among the various languages I use, why is SQL the only one that favors ALL CAPS EVERYTHING? When writing ad hoc queries I ignore that convention just to be ornery.

Re: Against SQL

#247

Post by goto11 » Sat, Jul 10, 2021, 2:36 PM UTC

NavinF wrote: post #149

goto11 wrote: post #102

Earlier quoted context omitted.

You can already do that by having circle and rectangle relations. Union types dos not give you any additional power compared to relations. Your proposal might be more convenient though than creating multiple relations, so we should look into making it just as convenient to create the necessary relations to express this. So lets say your syntax proposal creates multiple relations under the hood - then I'm all aboard!…

Agreed, that would solve my aesthetic concerns and kinda solve OP's XY problem with JSON. But the performance of auto-created tables for every union member would kinda suck if you never take advantage of the relation and still pay the cost. You'd also have to auto-create indexes so that queries that read full objects is as fast (IOPS) as storing BLOBs. I was imagining a future database that takes this a step further…

The basic premise of the relational model is that the physical storage structure and optimizations (including indexes) are independent of the logical model. A table could be stored row-by-row or column-by-column or any other clever way without it affecting the schema and queries - although it will affect the performance of the queries.

Re: Against SQL

#248

Post by erezsh » Sat, Jul 10, 2021, 2:39 PM UTC

dvdkon wrote: post #228

erezsh wrote: post #203

Earlier quoted context omitted.

Python has a data structure like this called NamedTuple. I also noticed it's popping up everywhere, in this case sql rows (kind of). Conceptually I like the idea of using it as the mechanism for function arguments, but I didn't want the syntax to be too confusing for beginners. Although yours seems a little bit different, because different fields can have the same name. Just for the heck of it, here's how your second…

It's not quite that arg-tuples can contain multiple values under the same name, but rather ":a 1, :a 2" is actually an arg-tuple containing two nested arg-tuples as unnamed values. I agree that basing all of syntax on a single element (especially one that's so "light" on syntax), it's hard making the resulting syntax intuitive. I think it's worth it, but I can see other approaches' benefits.

It's definitely an interesting approach. I imagine it makes the parsing much easier.

Re: Against SQL

#249

Post by chakkepolja » Sat, Jul 10, 2021, 2:40 PM UTC

Annatar wrote: post #124

This web site is amazing: every so often some webshit dipstick who doesn't grok SQL writes an essay bitching about it (instead of learning it!) and it ends up here. Enough with the bitching against SQL and promoting JSON webshit already! If you can't grok SQL, you should consider a career completely unrelated to computers! What the hell has this industry come down to!

If anything, he is complete opposite of webshit.

Re: Against SQL

#250

Post by goto11 » Sat, Jul 10, 2021, 2:41 PM UTC

laszlokorte wrote: post #244

goto11 wrote: post #234

Earlier quoted context omitted.

You would have the foreign key in the circle/triangle relations pointing to favourite_shape rather than the other way around.

But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.

You can do that with a check constraint, although I don't know if you consider it simple. It would be cool with better support for common constraint patterns.