Subject: building indices on float4's...

From: John Peter Lee <[email-masked]>
Date: Mon, 29 Nov 1993 18:05:32 -0500 (EST)

Hi:

   I don't think my previous message made it, so here goes again...

   I've built my DB, and defined indexes on a certain relation. The 
   values are float4's between 0.0 and 1.0. There are ~260 distinct
   values, and a total of 55,000 entries in all for this particular
   field. I built a btree index on this field, using the float4_ops
   operator.

   Now I am trying to issue some very simple queries using this field,
   and am getting anomalous results. I am not getting anything back in
   my results when issuing queries that I know should.

   First, I tried a very simple query:

* retrieve (comp_real_data.id_number)
where comp_real_data.date > 0.5
\g

Query sent to backend is "retrieve (comp_real_data.id_number)   where
comp_real_data.date > 0.5 "
NOTICE:Nov 29 17:59:50:there is no operator > for types float4 and float8
NOTICE:Nov 29 17:59:50:You will either have to retype this query using an
NOTICE:Nov 29 17:59:50:explicit cast, or you will have to define the
       operator
WARN:Nov 29 17:59:50:> for float4 and float8 using DEFINE OPERATOR

  this might have something to do with not having quotes around the 
  floating point number. What does this notice mean, and how would I
  perform an explicit cast ??? The manual says that the btree ops exist
  for float4s. It this true ???

  So then I tried:

* retrieve (comp_real_data.id_number)
  where comp_real_data.date > "0.5"\g

  Query sent to backend is "retrieve (comp_real_data.id_number)   where
  comp_real_data.date > "0.5""
  ---------------
  | id_number   |
  ---------------

  Nothing. I know that there should be at least 100 values returned.

  Is there anything wrong with building btrees on floating point numbers?

  thanks,

  jp

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
JP Lee               Institute for Visualization and Perception Research	
[email-masked]                    University of Massachusetts at Lowell 
(508) 934-3384			      1 University Ave. Lowell, MA 01854

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: Sunita Sarawagi <[email-masked]>
Date: Mon, 29 Nov 1993 17:07:13 -0800

We are aware of this problem and will try to fix it in the next release.
float4_ops *are* defined for btrees, so that is not a problem. Using quotes
around constants is a good work around.

I tried this on a small relation (with the proper index) myself and it worked 
fine. I made sure that index scan is used. So apparently for small relations 
there is no problem.  Recently there was a bug found (and corrected) in the 
btree insert routines. So, one guess is, it is related to the btree bug. Do
you want to try on a smaller relation to isolate the problem better?