CASE inet << inet ...

Just trying to summarize some traffic stats, and am either running the
query wrong, or you can't do this?

The query is:

SELECT CASE WHEN to_ip << '216.126.84.0/24' THEN to_ip ELSE from_ip END AS LocalAddr,
sum(bytes) as TotalBytes, date_trunc('day', runtime) AS Day
FROM stat_log
WHERE date_trunc('day', runtime) = '2001-01-02 00:00:00-05'
GROUP BY LocalAddr, Day;

returns:

localaddr | totalbytes | day
-----------------+------------+------------------------
24.6.125.174 | 13716 | 2001-01-02 00:00:00-05
24.43.137.113 | 13140 | 2001-01-02 00:00:00-05
24.128.201.128 | 14376 | 2001-01-02 00:00:00-05
64.39.38.43 | 14232 | 2001-01-02 00:00:00-05
128.11.44.16 | 25050 | 2001-01-02 00:00:00-05
130.149.17.13 | 14316 | 2001-01-02 00:00:00-05
142.177.197.180 | 179676 | 2001-01-02 00:00:00-05
151.164.30.54 | 13260 | 2001-01-02 00:00:00-05
166.84.192.39 | 13614 | 2001-01-02 00:00:00-05
192.67.198.32 | 13872 | 2001-01-02 00:00:00-05
192.245.12.7 | 14676 | 2001-01-02 00:00:00-05
193.228.80.12 | 13092 | 2001-01-02 00:00:00-05
194.126.24.131 | 21642 | 2001-01-02 00:00:00-05
194.209.182.36 | 14448 | 2001-01-02 00:00:00-05
195.46.202.129 | 73518 | 2001-01-02 00:00:00-05
195.117.86.253 | 13056 | 2001-01-02 00:00:00-05
196.38.110.24 | 15012 | 2001-01-02 00:00:00-05
202.160.254.40 | 38178 | 2001-01-02 00:00:00-05
207.123.82.5 | 15240 | 2001-01-02 00:00:00-05
207.136.80.247 | 25290 | 2001-01-02 00:00:00-05
208.158.96.110 | 17940 | 2001-01-02 00:00:00-05
209.47.145.10 | 2881400 | 2001-01-02 00:00:00-05
209.47.148.2 | 3263955 | 2001-01-02 00:00:00-05
209.223.182.2 | 222180 | 2001-01-02 00:00:00-05
212.43.217.25 | 22974 | 2001-01-02 00:00:00-05
216.126.72.6 | 1265472 | 2001-01-02 00:00:00-05
216.126.72.30 | 94615 | 2001-01-02 00:00:00-05
216.126.84.1 | 201733744 | 2001-01-02 00:00:00-05
216.126.84.10 | 151665 | 2001-01-02 00:00:00-05
216.126.84.11 | 103630 | 2001-01-02 00:00:00-05
216.126.84.14 | 752305 | 2001-01-02 00:00:00-05

Yet:

select * from stat_log_holding where from_ip << '216.126.84.0/24';

returns what I'd expect:

from_ip | to_ip | port | bytes | runtime
----------------+-----------------+------+----------+------------------------
216.126.84.1 | 212.7.160.126 | 873 | 16091760 | 2001-01-16 10:53:14-05
216.126.84.28 | 195.176.0.212 | 80 | 10247530 | 2001-01-16 10:53:14-05
216.126.84.73 | 193.172.127.85 | 80 | 7856477 | 2001-01-16 10:53:14-05
216.126.84.73 | 195.149.181.21 | 80 | 6343572 | 2001-01-16 10:53:14-05
216.126.84.1 | 216.126.84.253 | 53 | 4401161 | 2001-01-16 10:53:14-05
216.126.84.28 | 195.230.44.100 | 80 | 3157811 | 2001-01-16 10:53:14-05
216.126.84.95 | 194.206.159.140 | 80 | 3140439 | 2001-01-16 10:53:14-05

So, am I doing something wrong here, as far as that CASE statement is
concerned, or is this a bug in v7.0.3 that is fixed in v7.1?

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org