Re: Weird join result

On Wed, 16 Aug 2006, Peter Nixonn wrote:

> I am getting a result for an JOIN that I think is wrong. Maybe its my
> understanding that is wrong here however, so please be gentle :-)
>
> The "phones" table contains a list of phone numbers and an associated
> customer ID. The radacct table contains a list of all calls made (RADIUS
> Accounting records).
>
> I am doing the following:
>
> SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
> LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
> radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 GROUP BY
> phones.CALLINGSTATIONID;
>
> This query as expected returns 1386 rows (for customer ID 1) which includes
> a number of rows which have a NULL sum as they have not ever connected.
>
> Now, what I want to do is to return the same result set (of 1386 phones),
> but only for a particular time period. I therefore do the following:
>
> SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
> LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
> radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and radacct.ACCTSTARTTIME
> BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' GROUP BY
> phones.CALLINGSTATIONID;
>
> This returns 1280 rows, none of which are have a NULL value for sum. This
> surprised me at first as I thought the WHERE clause should apply before the
> OUTER JOIN but apparently not.

No, in fact it explicitly happens after the join (the order of evaluation
in the theoretical model is basically evaluate the from clause as a table
then apply where on that table to make a new table and so on). You can
either use a subselect in from list or put the condition into the ON to
make its evaluation earlier.

> I then tried the following:
>
> SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
> LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
> radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and
> (radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16
> 15:16:42' OR radacct.ACCTSTARTTIME ISNULL) GROUP BY
> phones.CALLINGSTATIONID;
>
> This query returns 1368 rows, which includes some NULL values for sum,
> however still short of the 1386 rows I am looking for. Close, but no cigar!

Imagine you had
phones (callingstationid = 1, custid = 1)
phones (callingstationid = 2, custid = 1)
phones (callingstationid = 3, custid = 1)
radacct (callingstationid = 1, acctstarttime 2006-05-10 00:00:00)
radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00)

Now, I believe the outer join should above give you
(callingstationid = 1, custid = 1, acctstarttime 2006-05-10 00:00:00)
(callingstationid = 2, custid = 1, acctstarttime 2006-08-10 00:00:00)
(callingstationid = 3, custid = 1, acctstarttime NULL)

Then apply the where clause
(callingstationid = 2, custid =1, acctstarttime 2006-08-10 00:00:00)
(callingstationid = 3, custid =1, acctstarttime NULL)

---

With the subselect in from you'd have
phones (callingstationid = 1, custid = 1)
phones (callingstationid = 2, custid = 1)
phones (callingstationid = 3, custid = 1)
radacct (callingstationid = 1, acctstarttime 2006-05-10 00:00:00)
radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00)

The first radacct row doesn't pass, so it's not in the subselect output
which should then look like:
subsel_radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00)

Then, I believe the outer join should give you
(callingstationid = 1, custid = 1, acctstarttime NULL)
(callingstationid = 2, custid = 1, acctstarttime 2006-08-10 00:00:00)
(callingstationid = 3, custid = 1, acctstarttime NULL)

And then you apply the where clause again and all the rows go through.