pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTPCH Benchmark query result invalid
5+ messages / 4 participants
[nested] [flat]
* TPCH Benchmark query result invalid
@ 2006-12-12 14:09 Cronje Fourie <cfourie@e-intelligence.com>
0 siblings, 1 reply; 5+ messages in thread
From: Cronje Fourie @ 2006-12-12 14:09 UTC (permalink / raw)
To: pgsql-sql
When running the following query against a TPCH db I get 0 results
returned
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
The same query runs correctly under mysql. Has anyone expierenced this
before?
Cronje
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
MailScanner thanks transtec Computers for their support.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TPCH Benchmark query result invalid
@ 2006-12-12 15:05 Richard Huxton <dev@archonet.com>
parent: Cronje Fourie <cfourie@e-intelligence.com>
0 siblings, 2 replies; 5+ messages in thread
From: Richard Huxton @ 2006-12-12 15:05 UTC (permalink / raw)
To: Cronje Fourie <cfourie@e-intelligence.com>; +Cc: pgsql-sql
Cronje Fourie wrote:
> When running the following query against a TPCH db I get 0 results
> returned
> and o_orderdate < date '1993-07-01' + interval '3' month
That's the problem line.
richardh=# SELECT interval '3' month;
interval
----------
00:00:00
(1 row)
It's got a zero-length date-range it's comparing against. If you have
interval '3 months' that should work, but I'm afraid I haven't got time
to check against the specs to see what the correct format should be.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TPCH Benchmark query result invalid
@ 2006-12-12 16:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-12-12 16:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Cronje Fourie <cfourie@e-intelligence.com>; pgsql-sql
Richard Huxton <dev@archonet.com> writes:
> richardh=# SELECT interval '3' month;
> interval
> ----------
> 00:00:00
> (1 row)
> It's got a zero-length date-range it's comparing against. If you have
> interval '3 months' that should work, but I'm afraid I haven't got time
> to check against the specs to see what the correct format should be.
I think this syntax is supposed to work according to the SQL spec. Tom
Lockhart was fooling around with making that stuff spec-compliant before
he left the project, and no one has bothered to pick it up since :-(.
Perhaps everyone thinks the spec syntax for interval constants is so
bizarre and non-orthogonal it's not worth dealing with ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TPCH Benchmark query result invalid
@ 2006-12-13 05:13 Michael Glaesemann <grzm@seespotcode.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2006-12-13 05:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; Cronje Fourie <cfourie@e-intelligence.com>; pgsql-sql
On Dec 13, 2006, at 1:23 , Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> richardh=# SELECT interval '3' month;
>> interval
>> ----------
>> 00:00:00
>> (1 row)
>
>> It's got a zero-length date-range it's comparing against. If you have
>> interval '3 months' that should work, but I'm afraid I haven't got
>> time
>> to check against the specs to see what the correct format should be.
>
> I think this syntax is supposed to work according to the SQL spec.
> Tom
> Lockhart was fooling around with making that stuff spec-compliant
> before
> he left the project, and no one has bothered to pick it up since :-(.
> Perhaps everyone thinks the spec syntax for interval constants is so
> bizarre and non-orthogonal it's not worth dealing with ...
Well, for what it's worth, I'm interested. I wasn't aware of this
particular problem, and I can't say when I'll get around to doing
something about it, but now I've at least written it down on my
personal todo.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TPCH Benchmark query result invalid
@ 2006-12-13 05:53 Cronje Fourie <cfourie@e-intelligence.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Cronje Fourie @ 2006-12-13 05:53 UTC (permalink / raw)
To: pgsql-sql
Thanks guys. Richard your fix didn't seem to work. But it's got me in
the right direction :) Time to RTFM ;)
Cronje
On Tue, 2006-12-12 at 15:05 +0000, Richard Huxton wrote:
> Cronje Fourie wrote:
> > When running the following query against a TPCH db I get 0 results
> > returned
>
> > and o_orderdate < date '1993-07-01' + interval '3' month
>
> That's the problem line.
>
> richardh=# SELECT interval '3' month;
> interval
> ----------
> 00:00:00
> (1 row)
>
> It's got a zero-length date-range it's comparing against. If you have
> interval '3 months' that should work, but I'm afraid I haven't got time
> to check against the specs to see what the correct format should be.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
MailScanner thanks transtec Computers for their support.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-12-13 05:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-12 14:09 TPCH Benchmark query result invalid Cronje Fourie <cfourie@e-intelligence.com>
2006-12-12 15:05 ` Richard Huxton <dev@archonet.com>
2006-12-12 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-13 05:13 ` Michael Glaesemann <grzm@seespotcode.net>
2006-12-13 05:53 ` Cronje Fourie <cfourie@e-intelligence.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox