Hacker News Reader

Comment by Supply5411 | Hacker News Reader

Having a deeper understanding of the different JOIN flavors is critical. For example, I don't think many people realize how deceptively dangerous OUTER joins are:

  SELECT user.user_id
  FROM users
  RIGHT JOIN purchases
    ON purchases.user_id = user.user_id
    AND user.user_id=123

By leaving the user_id=123 constraint in the JOIN instead of putting it in the WHERE, you've just exposed everyone's purchase data to the user. Easy to miss too if your tests don't fully create fixtures for multiple users. This becomes even more dangerous as we're entering the world of LLMs generating SQL[1]

1. https://docs.heimdallm.ai/en/main/attack-surface/sql.html#ou...

View on HN

I mean yes writing the wrong query will give you the wrong results. But there’s nothing inherently risky with a left/right/full join.

I could make the same argument about inner joins giving the risk of dropping records you intended to keep (very common in analytics). Just have to know what you’re doing and what data you got.

Outer joins are inherently risky because they make it very easy to put conditions in a place that looks correct (read: passes review) but isn't, and the results are disastrous (information leak).

In this case the query doesn't make much sense to me and hopefully wouldn't pass review. If you're only looking up purchases for a single user, why would you be joining on the user table at all? I suppose I could see a bad ORM or query builder somehow come up with a bad query like this.

I disagree, as the previous commenter said it’s not the tool, it’s the user and if you don’t know what you’re doing you’re prone to doing damage. Non trivial amounts of auth are made by “junior” engineers. I find that a lot more disastrous than a potential outer join + where accident *that also requires* the application code to leak the data to the outside world to be damaging *and* the underlying data to be sensitive.

So tools carry the same level of risk if the only way they can do harm is user error, even if that user error is easier to miss by design? I guess I just fundamentally disagree because I think risk analysis is holistic. Is the tool fundamentally correct (free of bugs) and is it easier to misuse.

I'm going to look at an outer join FAR more critically, regardless of who wrote it, because it's easy to mess up the conditions.

The same could be said about clueless programming in general. Tbh I didn’t immediately catch what that query does, because it’s non-regular(?) looking so there’s no quick pattern for it, but it still makes you think what it does actually. And no, there’s no way I could think of putting a filter into right join. It’s a join. It’s like… idk. Being clueless.

> This becomes even more dangerous as we're entering the world of LLMs generating SQL[1]

I am beginning to push back against the idea of LLMs authoring SQL. Determinism is really important for most realistic use cases. SQL is one of those "all or nothing" experiences. Pretty much the last thing you want to have a temperature/spiciness slider over.

Granted, if your use case is to simply memorize a handful of canonical queries and to provide a UX-friendly way for business administrators to retrieve these queries, then have at it.

But, if you are hoping to ask the LLM to write novel, sophisticated, domain-specific queries involving joins across 10+ tables, aggregations, recursion, windowing, etc., you are going to have a very bad time. In my experience, this is what everyone is dreaming about doing, and I think it is an unproductive fantasy at this point. Fine tuning will not give you what you seek, but don't let me stop you from trying. We burned over 200 hours on it with nothing to show for our time.

For me, LLMs are far more interesting for determining the user's intentions than emitting a final query. Classification seems like the actual superpower here. Classification can feed a very powerful deterministic query building engine that will actually give you what you want and you can prove it will work. It just takes a lot more tedious work than most humans are willing to endure, so here we are talking about various shortcuts.

>We burned over 200 hours on it with nothing to show for our time.

Can you talk more about this? I'm working in this space now, but not from the "make an LLM give you the right query" perspective, but from the "make the LLM-produced query be safe" perspective[1]

1. https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...

> "make an LLM give you the right query"

> "make the LLM-produced query be safe"

I think these are the same problem.

They're not. A query produced by an LLM may do exactly what the user asks for, but it is not safe to execute.

You're right. Now that I'm thinking about it, making a query that works is less difficult than also proving it is safe.

Good luck with your project.

How about an LLM talking to a semantic layer that then writes the SQL? I added experimental support for that in a project of mine, but using a semantic layer makes most querying so simple that natural language based approaches just seem kinda silly unless you have interface constraints (like on a mobile device).

https://github.com/totalhack/zillion

SQLite doesn't support right joins, which is a virtue in this case?

The article really ought to filter everything through SQLite, for how pervasive it is.

Not any more. SQLite received support for RIGHT and FULL OUTER joins in 3.39.0, released over a year ago.

why do people use right joins. i'll never understand it. especially in this case you when you can just query purchases directly and joining to users is pointless.

Why doesn’t that work?

It takes all purchases records and joins a corresponding user to each record, but only if it has a specific id. Otherwise it’s still every purchases record. But only one user gets joined. Join condition is not a filter, it is a join condition. Left join will always yield all left-table rows. Right join will always yield all right-table rows. “Where” works on a final result of select+joins (but before group by), so if you want to filter by user id, the condition should go in where.

Reply on news.ycombinator.com