Hacker News Reader

Comment by wireprotocol | Hacker News Reader

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.

View on HN

I'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?

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

Well, 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).

'HAVING sum(coalesce(o.price, 0)) < 100'

You are correct. The coalesce can go inside or outside the SUM().

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

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

Oh 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?

Yes, that should work.

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

You are only looking at Jan 1st 2019. (And really, a single second of that day, I believe)

DATE_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 ;-)

Personally 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. :)

Hey 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 ;)

Ah, you got me!

The problem is likely being unaware COALESCE exists. :)

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

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

Reply on news.ycombinator.com