MS SQL, Shades of Vista

[baavgai]

I've bitched about MS SQL Server before. This shows no sign of lessening...

Consider this simple little code example.


create table #FloatTest (seq [int] IDENTITY(1,1), a float(24), b float(24) )

insert into #FloatTest(a) values(0.1)
insert into #FloatTest(a) values(0.2)
insert into #FloatTest(a) values(0.7)
insert into #FloatTest(a) values(0.1)

update #FloatTest
set b = ( select sum(a) from #FloatTest b where b.Seq<=a.Seq )
from #FloatTest a

select a,b from #FloatTest order by seq

a b
------------- -----
0.1 0.1
0.2 0.3
0.7 1
0.1 1.1


These are expected results. However, float is hiding something. It's hiding the sad fact that Microsoft's flagship Enterprise product is confused by simple addition.

Change one line like so:
create table #FloatTest (seq [int] IDENTITY(1,1), a float(24), b float(53) )

This is isn't a change to stored value a, but rather just the bucket size. The results should be identical. Of course, they're not.

a b
------------- -----------------
0.1 0.100000001490116
0.2 0.300000004470348
0.7 0.999999992549419
0.1 1.09999999403954


I was, of course, made aware of this when attempting do something like "select * from #FloatTest where floor(b)=b" and getting no values. One fix, the one I'll probably have to go with, is a casting hack like "select * from #FloatTest where floor(cast(b as float(24)))=cast(b as float(24))".

Yeah, b is still 0.999999992549419, but that's what I have.

MS SQL Server sure is easy to maintain. That GUI is cool. Backup and restore is a no brainer. Too bad it's buggier than picnic potato salad at high noon.

Anyway, that's my Microsoft journey for today. For an easy, play at home version, try this: select cast(cast(0.1 as float(12)) as float(53))

Yep, still buggy.

Comments

[woodwindy.livejournal.com]

I don't understand this at all, but it sounds funny anyway. :)

[baavgai.livejournal.com]

LOL. I could explain binary math, if you like. It's kind of neat, particularly if you like cyphers. Hmm... I think I have an amusing abstraction for the problem.

Imagine my information is a shot of espresso. The cafe only sells small cups and large cups. The espresso doesn't fill the cups, so they are always topped up with milk. I order a small; shot, milk, fine. I order a large; shot, milk, fine. With me so far?

I order a small. Then decide a want a large. My small is poured into a large cup, but it's not full. Rather than topping it up with milk, my beleaguered barrista tops it up with what's left over from different cup. Now, instead of exactly one shot, I have a shot plus some unknown dregs from a different cup.

In a nutshell, that's the problem; poor service at the coffee shop. ;)

[h3salthea.livejournal.com]

When I worked for Reuters and used a DB using SQL Svr I think I drove the British co-workers who maintained the DB batty with my almost-daily complaints and suggestions for corrections.

Y'no...I really don't miss that damned DB....

(and ironically? It was dropped from usage shortly after my Dept was let go...seems the outsourced company considered it too cumbersome)