Q+E Corrupting Numbers in Oracle Database

I have found Q+E is capable of corrupting number fields in an Oracle Database.

Create table x (y number(4));

Use Q+E to enter the value 100 in column Y.

Try to query it back giving 100 as the value to be matched.

Nothing returned.

Try the following queries

select y from x where y = 100;

no rows returned.

select y from x where y > 99 and y < 100.

The row comes back.

select y from x where y + 0 != y; /* generic bad representation test */

The row shows up.

update y set x = x + 0; /* general purpose fix */

select x from y where x = 100;

the row comes back.

I guess that Q+E is using native number format (as opposed to coercing to int, float, or double) and getting it close, but not spot on. any arbitrarily close range works such as
select y from x where y > 99.999999999 and y < 100.000000001 but exact matches don't.

In any event, you have been given the cause, a generic test for data corruption and a fix.    Received on Tue Jul 25 1995 - 00:00:00 CEST