Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday December 07, 2009
10:38 AM

The Implications of the Bug

[ #39986 ]

mscolly correctly identified the SQL bug I posted. Sadly, no one discussed the implications of this bug and I think they're the most interesting part of this. Essentially, it comes down to the following:

SELECT    first_name,
          last_name,
          order_date,
          SUM(price) AS total                  -- what if there's no price?
FROM      customer
LEFT JOIN orders     ON customer.id = orders.customer_id
LEFT JOIN order_item ON orders.id   = order_item.order_id
GROUP BY  first_name, last_name, order_date
HAVING    total < 15                           --  what does NULL < 15 evalute to?
ORDER     BY order_date ASC;

The proper solution (as mscolly pointed out) is to change the "SUM" line to this:

COALESCE( SUM(price), 0 ) AS total

The English language, as we know, is ambiguous. If you boss had come in and asked for all customers whose orders (more accurately, whose orders with order items) totaled less than £15 pounds, then the above query would have actually been correct, but another programmer coming along to maintain it could be forgiven for thinking it's in error. If you ever write SQL which is likely to produce NULLs (e.g., outer joins), you should explicitly handle that case if you actually do anything with those NULLs.

But in this case, "customers whose orders total less than £15" is significantly different in meaning than "customers who spent less than £15" and the latter is what we want, but the former is what we have. While the above code seems logical, it gives a logically incorrect answer because it omits customers without orders (or order items), even though they're clearly intended. However, NULLs make it very difficult to identify what you actually mean because the database can't know why something is NULL.

Now consider a simpler, yet silly, example:

SELECT first_name
FROM   employee
WHERE  salary > 50000;

What happens if the salary field is NULL? You'll get a list of employees whose known salary is NOT NULL. Why might they not have a salary? Maybe they're an hourly employee and the salary field is not applicable. Maybe they're the CEO and he doesn't think you need to know his salary. Maybe they're an ex-employee and they have no salary.

Taking this a bit further, imagine that all employees in the table are current and all have salaries (no hourly workers), but the salary field is still sometimes NULL because the board of directors doesn't want you to know their salaries. With me so far? In this scenario, it is the case that everyone has a salary; you just don't know what some of them are. So here's the kicker:

SELECT first_name
FROM   employee
WHERE  salary = salary;

That won't return anyone on the board of directors, even though you know they have a salary. Furthermore, most would think it's self-evident that p = p, but in three value logic of databases, this is sometimes true and sometimes false. Heck, because of this, the following does not always evaluate correctly, even though we would think it does:

SELECT service_id,
    CASE WHEN master_brand_id =  master_brand_id THEN '='
         WHEN master_brand_id != master_brand_id THEN '!='
    END AS 'comparison'
FROM service

Sure, you say, but you're comparing something to itself. You don't do that in the real world. No? So look at this:

SELECT s.service_id,
    CASE WHEN m.master_brand_id =  s.master_brand_id THEN '='
         WHEN m.master_brand_id != s.master_brand_id THEN '!='
    END AS 'comparison'
FROM service      s
     master_brand m

If the s.master_brand_id is allowed to be NULL, than the comparison field will always have a NULL value when s.master_brand_id is NULL. It's easy to debug in this simple example, but what if that was a subquery? It looks fine, but it all breaks down in the presence of NULL values.

I didn't start with that example because people would say it's silly, but starting with the "order" example shows how NULLs in databases can return logically incorrect data and the reduction down to the simple p = p case not holding shows why this happens.

At this point, I can see people saying "yeah, but we already know that about databases." And this is true. It's well-known that certain types of queries can generate NULLs even though there are no NULL values in the database. Regrettably, many people assume the database logic is, well, logical. The p = p failure is a strong rebuttal, but I suppose some people assume that hitting themselves in the head with a hammer is normal.

If you really want to have some fun, read this blog entry about NULL values. In the comments, the author even explains how to deal with NULLs in outer joins, but it requires a relational database (very few databases really are) and that people understand what first normal form is really about. (If you think you know, please define "atomic values" in the comments below).

I wonder how database design would look today if, instead of 3VL, databases threw an exception when you tried to apply an operator or aggregation ('=', '+', 'SUM', etc.) to NULL values?

Note: I've discussed the problem with NULL values before, but in realizing I had a better real-world example, I thought it would make more sense to readers.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Shouldn't it be (as it appears was suggested in your earlier post):

    SUM( COALESCE( price, 0 ) ) AS total

    If the coalesce is outside the sume, it doesn't fix the underlying issue of apply an aggregate function on a set of data that may contain a NULL value. Or, perhaps I'm missing something...
    • SUM ignores NULLs. So the sum of 1 and NULL is 1.

      SUM's behavior, of course, makes perfect sense: as 1+NULL yields NULL, so clearly the sum is 1.</sarcasm>

  • Now consider a simpler, yet silly, example: SELECT first_name FROM employee WHERE salary > 50000; What happens if the salary field is NULL? You'll get a list of employees whose known salary is NULL.

    I can't figure out what you meant to say there, but the query doesn't seem to go with the text.

    In this scenario, it is the case that everyone has a salary; you just don't know what some of them are. So here's the kicker: SELECT first_name FROM employee WHERE salary = salary; Th

    • Ack, fixed the typo regard "known salary is null".

      You don't know that though. Not within the terms of the database. If you did, they'd have a numeric salary and not NULL.

      That's entirely the point. Database "logic", when combined with NULLs, returns answers that we really know are not true. I don't mean "within the terms of the database". p = p is true, regardless of whether or not a poorly designed query language says otherwise.