TSQL - make a literal float value

Question

I understand the host of issues in comparing floats, and lament their use in this case - but I'm not the table author and have only a small hurdle to climb...

Someone has decided to use floats as you'd expect GUIDs to be used. I need to retrieve all the records with a specific float value.

sp_help MyTable

-- Column_name  Type    Computed    Length  Prec
-- RandomGrouping   float   no  8   53   

Here's my naive attempt:

--yields no results
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping = 0.867153569942739

And here's an approximately working attempt:

--yields 2 records
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.867153569942739 - 0.00000001
      AND 0.867153569942739 + 0.00000001

--  0.867153569942739
--  0.867153569942739

In my naive attempt, is that literal a floating point literal? Or is it really a decimal literal that gets converted later?

If my literal is not a floating point literal, what is the syntax for making a floating point literal?

EDIT: Another possibility has occurred to me... it may be that a more precise number than is displayed is stored in this column. It may be impossible to create a literal that represents this number. I will accept answers that demonstrate that this is the case.

---

EDIT: response to DVK.

TSQL is MSSQLServer's dialect of SQL.

This script works, and so equality can be performed deterministically between float types:

DECLARE @X float
SELECT top 1 @X = RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.839110948199148 - 0.000000000001
  AND 0.839110948199148 + 0.000000000001
  --yields two records
SELECT *
FROM MyTable
WHERE RandomGrouping = @X

I said "approximately" because that method tests for a range. With that method I could get values that are not equal to my intended value.

The linked article doesn't apply because I'm not (intentionally) trying to straddle the world boundaries between decimal and float. I'm trying to work with only floats. This isn't about the non-convertibility of decimals to floats.

---

Response to Zinglon:

A literal value that can find my records, thanks.

DECLARE @Y binary(8)
SET @Y = 0x3FEAD9FF34076378

SELECT *
FROM MyTable
WHERE convert(binary(8), RandomGrouping) = @Y

Answer 1

The problem is not whether it's a floating point literal or not.

The problem is that comparing two floats for equality in Sybase (or any DB server) is not deterministic, since 4.00000000000000000000... and 3.99999999999999999999... are the same exact number but aren't equal.

Your second solution is the only correct way to compare floats for "equality" (that is, are they the same up to a precision).

Why are you saying "approximately working" about your second approach?

Since you didn't provide the specific DB server you use, here's a fairly decent write-up of the problem (with basically the same conclusions as above) for MySQL

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Answer 2

It is possible that the values are being truncated on display. I'm assuming the column doesn't have a unique constraint on it, otherwise the question would be moot. On my setup, SSMS truncates the more precise value in this script.

create table flt ( f float not null primary key )
insert into flt
select 0.111111111111111
union all
select 0.1111111111111111
select f, cast(f as binary(8)) from flt

Similarly, if these values are distinct you can cast them to binary(8) and identify them based on that value, like this:

select f from flt
where cast(f as binary(8)) = 0x3FBC71C71C71C71C