Subject: b-tree bug?

From: Jeff Sidell <[email-masked]>
Date: Wed, 1 May 1996 08:35:06 -0700

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                            [email-masked]
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 "[email-masked]".
   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================
              URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/