Обсуждение: Bug in queries ??
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_time) as abstime), cast(max(that_time) 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 thefollowing result: min max Totaal in Megabytes "2004-09-01 00:00:01+02" "2004-11-23 11:50:01+01"; "82768.623353004456" 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_time) as abstime), cast(max(that_time) 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.078444480896" 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 50out'" gives 5524 Megabytes. How does that compare to the queries above? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
Joost Kraaijeveld wrote: > > If I run the following query: > > select cast(min(that_time) as abstime), cast(max(that_time) 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
"Joost Kraaijeveld" <J.Kraaijeveld@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_time) as abstime), cast(max(that_time) 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.623353004456" > > 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_time) as abstime), cast(max(that_time) 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.078444480896" > > 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
Hi Jim and Richard, Thank for pointing out something that I should have known. Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl