Bug in queries ??

[Joost Kraaijeveld]

Hi all,
I have three questions about 1 table

CREATE TABLE public.logs
(
rule_name varchar(32) NOT NULL,
bytes int8 NOT NULL,
pkts int8 NOT NULL,
hostname varchar(100),
that_time int4 NOT NULL
) WITH OIDS;

Question 1.

If I run the following query:

select cast(min(that_t ime) as abstime), cast(max(that_t ime) as abstime), (sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
that_time between cast( abstime('2004-10-1 00:00') as int4) and cast( abstime('2004-11-1 00:00') as int4)
and
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'

I expect that the outcome will be between "2004-10-1 00:00" and "2004-11-1 00:00" (the month october). However, I get the following result:

min max Totaal in Megabytes
"2004-09-01 00:00:01+02" "2004-11-23 11:50:01+01"; "82768.62335300 4456"

The min date is the date of the first entry ever, the max entry the last entry ever. Why is this?
Question 2.

If I refrase the above query to:

select cast(min(that_t ime) as abstime), cast(max(that_t ime) as abstime), (sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'
and
that_time between cast( abstime('2004-10-1 00:00') as int4) and cast( abstime('2004-11-1 00:00') as int4)

I get a diffent answer (see the Totaal in Megabytes):

min max Totaal in Megabytes
"2004-09-01 00:00:01+02" "2004-11-23 12:00:01+01" "92406.07844448 0896"

My question why is this?

Question 3.

Querying for just "rule_name = 'Incoming 83 50 in'" gives 34990 Megabytes, just querying for "rule_name = 'Outgoing 83 50 out'" gives 5524 Megabytes. How does that compare to the queries above?
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: [phone]
fax: [phone]
e-mail: J.***********@A skesis.nl
web: www.askesis.nl

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Richard Huxton]

Joost Kraaijeveld wrote:

If I run the following query:

select cast(min(that_t ime) as abstime), cast(max(that_t ime) as abstime), (sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
that_time between cast( abstime('2004-10-1 00:00') as int4) and cast( abstime('2004-11-1 00:00') as int4)
and
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'

Do brackets solve your problem?

WHERE that_time betweeen ...
AND (
rule_name = ...
OR rule_name = ...
)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Jim Seymour]

"Joost Kraaijeveld" <J.***********@ Askesis.nl> wrote:

Hi all,
I have three questions about 1 table

CREATE TABLE public.logs
(
rule_name varchar(32) NOT NULL,
bytes int8 NOT NULL,
pkts int8 NOT NULL,
hostname varchar(100),
that_time int4 NOT NULL
) WITH OIDS;

Question 1.

If I run the following query:

select cast(min(that_t ime) as abstime), cast(max(that_t ime) as abstime),
(sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
that_time between cast( abstime('2004-10-1 00:00') as int4) and
cast( abstime('2004-11-1 00:00') as int4)
and
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'

I expect that the outcome will be between "2004-10-1 00:00" and
"2004-11-1 00:00" (the month october). However, I get the following result:

min max Totaal in Megabytes
"2004-09-01 00:00:01+02" "2004-11-23 11:50:01+01"; "82768.62335300 4456"

The min date is the date of the first entry ever, the max entry the
last entry ever. Why is this?
Because you're asking "between 1st date and second date and rule_name
equals something," OR rule_name equals something_else.

What you have is equivilent to

where (that between ... and ... and rule = ...) or rule = ...

You want

where time between ... and ... and (rule = ... or rule = ...)


Question 2.

If I refrase the above query to:

select cast(min(that_t ime) as abstime), cast(max(that_t ime) as abstime),
(sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'
and
that_time between cast( abstime('2004-10-1 00:00') as int4) and
cast( abstime('2004-11-1 00:00') as int4)

I get a diffent answer (see the Totaal in Megabytes):

min max Totaal in Megabytes
"2004-09-01 00:00:01+02" "2004-11-23 12:00:01+01" "92406.07844448 0896"

My question why is this?
You have

where rule = ... or (rule = ... and time between ...)

Question 3.

Querying for just "rule_name = 'Incoming 83 50 in'" gives 34990
Megabytes, just querying for "rule_name = 'Outgoing 83 50 out'" gives
5524 Megabytes. How does that compare to the queries above?

The answer is probably clear by now ;).

Jim

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster