Return-Path: owner-postman Received: from localhost.Berkeley.EDU (localhost.Berkeley.EDU [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.10/8.6.3) with SMTP id IAA18974 for postgres-redist; Wed, 1 May 1996 08:35:33 -0700 Resent-From: POSTGRES mailing list Resent-Message-Id: <199605011535.IAA18974@nobozo.CS.Berkeley.EDU> X-Authentication-Warning: nobozo.CS.Berkeley.EDU: Host localhost.Berkeley.EDU didn't use HELO protocol Sender: owner-postman@postgres.Berkeley.EDU X-Return-Path: owner-postman Received: from chocho (chocho.CS.Berkeley.EDU [128.32.34.121]) by nobozo.CS.Berkeley.EDU (8.6.10/8.6.3) with SMTP id IAA13979; Wed, 1 May 1996 08:35:06 -0700 Date: Wed, 1 May 1996 08:35:06 -0700 Message-Id: <199605011535.IAA13979@nobozo.CS.Berkeley.EDU> X-Sender: jsidell@nobozo.cs.berkeley.EDU X-Mailer: Windows Eudora Version 2.1.1 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" To: postgres@postgres.Berkeley.EDU From: Jeff Sidell Subject: b-tree bug? Cc: mariposa@postgres.Berkeley.EDU Resent-To: postgres-redist@postgres.Berkeley.EDU Resent-Date: Wed, 01 May 96 08:35:33 -0700 Resent-XMts: smtp Has anyone seen this? I noticed some weird answers and it seems there's a problems with b-trees?! The same query gets a different answer when using a b-tree index scan than when using a regular sequential scan. In the examples below, there was a b-tree index created over LINEITEM using the following CREATE INDEX command: create index LINEITEM_SDATE on LINEITEM using btree(L_SHIPDATE int4_ops); (and yes, L_SHIPDATE is an int4) ********************** With index scans allowed ******************************* Go * select count(*) from LINEITEM where L_SHIPDATE >= 8767 and L_SHIPDATE < 9132; Query sent to backend is "select count(*) from LINEITEM where L_SHIPDATE >= 8767 and L_SHIPDATE < 9132;" --------------- | count | --------------- | 10393 | --------------- Go * select count(*) from LINEITEM where L_SHIPDATE = 8767; Query sent to backend is "select count(*) from LINEITEM where L_SHIPDATE = 8767; " --------------- | count | --------------- | 18 | --------------- ********************** With index scans forbidden ******************************* * parkcity:jsidell 8% monitor Welcome to the POSTGRES95 terminal monitor Go * select count(*) from LINEITEM where L_SHIPDATE >= 8767 and L_SHIPDATE < 9132; Query sent to backend is "select count(*) from LINEITEM where L_SHIPDATE >= 8767 and L_SHIPDATE < 9132;" --------------- | count | --------------- | 91947 | --------------- * select count(*) from LINEITEM where L_SHIPDATE = 8767; Query sent to backend is "select count(*) from LINEITEM where L_SHIPDATE = 8767; " --------------- | count | --------------- | 231 | --------------- Go ============================================================================= JEFF SIDELL Computer Science Division jsidell@cs.berkeley.edu University of California at Berkeley http://http.cs.berkeley.edu/~jsidell/ ============================================================================= ============================================================================== To add/remove yourself to/from the POSTGRES mailing list: send mail with the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU". If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and a human will deal with it. DO NOT post to the "postgres" mailing list. ============================================================================== URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/