6 ms·

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 ar

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#outer-joins https://docs.heimdallm.ai/en/main/attack-surface/sql.html#ou...

bob1029 3y ago
> 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.
- Supply5411 3y ago
  >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-execution.html https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...
  - bob1029 3y ago
    > "make an LLM give you the right query" > "make the LLM-produced query be safe" I think these are the same problem.
    - Supply5411 3y ago
      They're not. A query produced by an LLM may do exactly what the user asks for, but it is not safe to execute.
      - bob1029 3y ago
        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.
- totalhack 3y ago
  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 https://github.com/totalhack/zillion
jumpman500 3y ago
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.
- Supply5411 3y ago
  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).
  - djoletina 3y ago
    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.
    - Supply5411 3y ago
      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.
  - tshaddox 3y ago
    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.
chasil 3y ago
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.
- bewaretheirs 3y ago
  Not any more. SQLite received support for RIGHT and FULL OUTER joins in 3.39.0, released over a year ago.
deleted 3y ago
[deleted]
jletienne 3y 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.
wodenokoto 3y ago
Why doesn’t that work?
- wruza 3y ago
  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.

bob1029 3y ago

> 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.

Supply5411 3y ago
>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-execution.html https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...
- bob1029 3y ago
  > "make an LLM give you the right query" > "make the LLM-produced query be safe" I think these are the same problem.
  - Supply5411 3y ago
    They're not. A query produced by an LLM may do exactly what the user asks for, but it is not safe to execute.
    - bob1029 3y ago
      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.
totalhack 3y ago
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 https://github.com/totalhack/zillion

jumpman500 3y ago

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.

Supply5411 3y ago
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).
- djoletina 3y ago
  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.
  - Supply5411 3y ago
    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.
- tshaddox 3y ago
  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.

chasil 3y ago

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.

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

deleted 3y ago

[deleted]

jletienne 3y 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.

wodenokoto 3y ago

Why doesn’t that work?

wruza 3y ago
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.