agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: Jeff Sidell <jsidell@cs.berkeley.edu>
To: postgres@postgres.Berkeley.EDU
Cc: mariposa@postgres.Berkeley.EDU
Subject: b-tree bug?
Date: Wed, 1 May 1996 08:35:06 -0700
Message-ID: <199605011535.IAA13979@nobozo.CS.Berkeley.EDU> (raw)

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/

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: postgres@postgres.berkeley.edu
  Cc: jsidell@cs.berkeley.edu, mariposa@postgres.Berkeley.EDU
  Subject: Re: b-tree bug?
  In-Reply-To: <199605011535.IAA13979@nobozo.CS.Berkeley.EDU>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox