The storage representation for number zero

Yesterday I found out something interesting in an Oracle 9205 database. One column called is_mandatory is defined as NUMBER(1,0). Based on the business rule, only 0 or 1 can be stored in the column. The strange things happen to 0. There are 2 different storage representations for the number zero!

09:18:50 > select is_mandatory, dump(is_mandatory) storage from t where is_mandatory != 1;

IS_MANDATORY STORAGE
------------ ------------------------------
0 Typ=2 Len=1: 128
0 Typ=2 Len=1: 128
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1

9 rows selected.

Elapsed: 00:00:00.01
09:19:00 > select is_mandatory, dump(is_mandatory) storage from t where is_mandatory = 0;

IS_MANDATORY STORAGE
------------ ------------------------------
0 Typ=2 Len=1: 128
0 Typ=2 Len=1: 128

2 rows selected.

Elapsed: 00:00:00.01

In a normal case, the 0 should be always stored in Oracle like:

11:14:16 > select dump(0) from dual;

DUMP(0)
------------------------------------------------
Typ=2 Len=1: 128

Elapsed: 00:00:00.08

However, there is another storage scheme for 0 in the table

Typ=2 Len=2: 193,1

This value should be zero as well based on Oracle storage internal. However, I couldn't fetch these rows using is_mandatory = 0 since 0 will be always interpreted as 'Typ=2 Len=1: 128'. Even I couldn't reproduce this scenario simply using SQL. Originally, these values are inserted/updated by a database stored procedure which takes a set of values as parameters from a front-end Java program via JDBC thin client. I am wondering under what kind of condition 0 would be stored this strange way. It seems like 'Typ=2 Len=2: 193,1' is something between 0 and 1.

11:42:02 > select is_mandatory from t where is_mandatory > 0 and is_mandatory <>
0
0
0
0
0
0
0
7 rows selected.
Elapsed: 00:00:00.00

I raised a question about this to ASKTOM. So this post will be updated once I got the answer from Tom.

The problem is caused by the incompatible JDBC and RDBMS version. We are running an Oracle 9205 database as we are using JDBC 8174 in the middle tier to access the database. After I point to JDBC 9205 driver and re-run the program, number zero can be stored correctly with 'Len=1: 128'. According to what one said:

'Len=2: 193,1' is definitely an invalid NUMBER; in 10.2.0.3 :

SQL> select dump ( utl_raw.cast_to_number ('C101') ) from dual;

DUMP(UTL_RAW.CAST_TO_NUMBER('C101'))
--------------------------------------------------------------------------------
Typ=2 Len=2: 193,1

SQL> select 1 / utl_raw.cast_to_number ('C101') from dual;
select 1 / utl_raw.cast_to_number ('C101') from dual
      *
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Just an illegal bit representation that doesn't map to any NUMBER, and so produces unpredictable results (including process termination) if used.

I also did some tests by myself in Oracle SQL Developer:

select utl_raw.cast_to_number('c101') from dual

UTL_RAW.CAST_TO_NUMBER('C101')
------------------------------
0

1 rows selected

select utl_raw.cast_to_number('C101')/1 from dual

UTL_RAW.CAST_TO_NUMBER('C101')/1
--------------------------------
146150163.7229892817193583822615272918645831532875

1 rows selected

If the 2nd SQL is executed in SQL*Plus, seems like it will never end. A different result! Completely unpredictable, indeed!