4 ms·
Quiz: -- Find all customers with total orders -- less than $100 in 2019 SELECT c.name FROM customers c LEFT JOIN orders o ON (c.id = o.
by jeffdavis 7y ago
Quiz:
-- Find all customers with total orders
-- less than $100 in 2019
SELECT c.name
FROM customers c LEFT JOIN orders o
ON (c.id = o.customer_id)
WHERE DATE_TRUNC('year', o.ts) = '2019-01-01'::timestamptz
GROUP BY c.name
HAVING SUM(o.price) < 100;
Can you find the problem?
[ SPOILER ]
The problem is if there is a customer with no orders. The left join will produce NULLs for the attributes of "orders", and SUM() will sum them up to NULL because it thinks NULL means "nothing". The predicate "NULL < 100" will then think the NULL means "unknown" and produce the result "unknown". Then, the HAVING clause treats NULL like FALSE, and does not produce the row.
- briffle 7y agoYou are only looking at Jan 1st 2019. (And really, a single second of that day, I believe)
- jeffdavis 7y agoDATE_TRUNC('year',...) cuts out the lower-order parts of the timestamp, so I believe my query is correct. More precisely, I believe my query is incorrect in the way I intended but not in other ways ;-)
- ilikepi 7y agoPersonally I like: WHERE EXTRACT(YEAR FROM o.ts) = 2019 ...though I imagine some people dislike the way EXTRACT treats words like YEAR as keywords. Always interesting to see which solution people choose when there are several options. :)
- uhoh-itsmaciek 7y agoHey Jeff! Isn't it also incorrect because WHERE DATE_TRUNC('year', o.ts) = '2019-01-01'::timestamptz will filter out customers without orders even before the HAVING (because DATE_TRUNC of NULL is NULL)? I mean, I guess you're still demonstrating the perils of NULL with that ;)
- jeffdavis 7y agoAh, you got me!
- skrebbel 7y agoThis is not really a problem with NULLs though, but primarily with how SQL forces you to put any kind of result in a rectangular table-shaped resultset. Because of this, a LEFT JOIN returns a single "order" row both if there is exactly 1 order for that customer, but also when there are no orders. That's just nuts. If a JOIN could somehow return hierarchically structured data then there would've been no gotchas here.
- jeffdavis 7y agoWell, it's many problems, that's the fun of the example. But yes, I think left join is fundamentally about grouping, and grouping would be better solved with set-valued attributes or relation-valued attributes. I wouldn't call that "hierarchically-structured data" because that has the connotation that the data is actually stored in a hierarchy, which I think is generally a bad idea (though not always).
- deleted 7y ago[deleted]
- bobwaycott 7y agoThe problem is likely being unaware COALESCE exists. :)
- jeffdavis 7y agoI don't think that's quite fair. It's pretty easy to know that COALESCE exists and still make the mistake. A language should help you avoid making mistakes, not trick you into making them. The path of least resistance should be the safest and most normal thing, and SQL fails here.
- bobwaycott 7y agoI think it’s more unfair to say SQL “tricks” people into making mistakes than to lightheartedly see such a query and think whoever wrote it might not know about COALESCE. I see that query and the lack of COALESCE—in the context given where it is known the column we care about has NULLs—and assume there’s an opportunity to introduce someone to COALESCE: “Hey, check this out—it’s made for this situation!” But saying SQL is tricking people into making mistakes? Nah. The real issue seems to be writing a query without a proper understanding of NULL, not being tricked by the language.
- pjot 7y ago'HAVING sum(coalesce(o.price, 0)) < 100'
- jeffdavis 7y agoYou are correct. The coalesce can go inside or outside the SUM().
- jeffdavis 7y agoI'd like to add that, in the above example, you get bitten by NULL semantics even though you don't have any NULLs in your data at all. So if you don't start out with any NULLs, and you have complete and unambiguous knowledge of your entire data set, how does "unknown" get injected into such an innocent-looking example?
- magicalhippo 7y agoI'm self-taught when it comes to SQL so I'm probably doing stupid stuff, but I tend to use subqueries when I can. So in your case I'd do SELECT c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE c.id = o.customer_id AND DATE_TRUNC('year', o.ts) = '2019-01-01'::timestamptz HAVING SUM(o.price) < 100 ) Again, might be dumb, but I usually find it easier to think in terms of subqueries rather than joins. Not that I find joining hard per se, just feels more natural and to the point to use subqueries. Database I use also seems to optimize it better most of the time.
- iimblack 7y agoDoesn't this produce different output? I could be wrong but I think your code is more equivalent to an inner join, not a left outer join.
- magicalhippo 7y agoOh yeah you're right, I read the comment above the query and assumed he wanted only customers which had orders. Could just modify it to exclude the orders above 100 ("where not exists"...), no?
- iimblack 7y agoYes, that should work.
- Tostino 7y agoSadly, I spotted it immediately. God I hate debugging queries at this point. Edit: just read what you said was wrong with it, and realized you missed the one in the where clause. Like you said, really hammers home how hard to deal with null is with properly all the time.