Thread: TPCH Benchmark query result invalid
When running the following query against a TPCH db I get 0 results returned selecto_orderpriority,count(*) as order_count fromorders whereo_orderdate >= date '1993-07-01'and o_orderdate < date '1993-07-01' + interval '3' monthand exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate) group byo_orderpriority order byo_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.
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
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
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
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.