Strange apparent value in decimal column using dbaccess
A DECIMAL(12,2) column displayed as "//.//" in dbaccess, and arithmetic on it (+1-1+0.5-0.5) returned a bogus 9988.89 instead of the expected value. Carsten Haese explained that Informix stores decimals as base-100 "hyperdigits" (0-99), and the output is what you'd see if an illegal value such as -11 landed in the ones/hundredths positions - i.e. corrupt data. Jonathan Leffler agreed, noting only an UPDATE with the correct value will fix it. The cause of the corruption was never identified (the poster lacked privileges and logical logs), so no root-cause resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Anyone ever seen anything like this before?
> select <col>, <col2> from <table> where <col3> = 17718120;
<col> <col2>
6285416 0.05
6285417 //.//
2 row(s) retrieved.
> select <col>, <col2> + 1 -1 + 0.5 - 0.5 from <table> where <col3> = 17718120;
<col> (expression)
6285416 0.05
6285417 9988.89
2 row(s) retrieved.
The value "should" be 0.5 but seems to have been altered and is not
displayed properly in dbaccess and is causing problems within our
apps. Anyone know why this might be? And what the significance of
9988.89 is?
Check the DDL for the table, or view, in question.
j.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of ian.miell@gmail.com
Sent: Wednesday, September 19, 2007 7:39 AM
To: informix-list@iiug.org
Subject: Strange apparent value in decimal column using dbaccess
Anyone ever seen anything like this before?
> select <col>, <col2> from <table> where <col3> = 17718120;
<col> <col2>
6285416 0.05
6285417 //.//
2 row(s) retrieved.
> select <col>, <col2> + 1 -1 + 0.5 - 0.5 from <table> where <col3> =
17718120;
<col> (expression)
6285416 0.05
6285417 9988.89
2 row(s) retrieved.
The value "should" be 0.5 but seems to have been altered and is not
displayed properly in dbaccess and is causing problems within our
apps. Anyone know why this might be? And what the significance of
9988.89 is?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
info columns shows decimal(12,2) for that column.
Haven't got perms to do dbschema! Logical logs gone too.
On 19 Sep, 12:58, "Jack Parker" <jack.park...@verizon.net> wrote:
> Check the DDL for the table, or view, in question.
>
> j.
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of ian.mi...@gmail.com
> Sent: Wednesday, September 19, 2007 7:39 AM
> To: informix-l...@iiug.org
> Subject: Strange apparent value in decimal column using dbaccess
>
> Anyone ever seen anything like this before?
>
> > select <col>, <col2> from <table> where <col3> = 17718120;
>
> <col> <col2>
>
> 6285416 0.05
> 6285417 //.//
>
> 2 row(s) retrieved.
>
> > select <col>, <col2> + 1 -1 + 0.5 - 0.5 from <table> where <col3> =
> 17718120;
>
> <col> (expression)
>
> 6285416 0.05
> 6285417 9988.89
>
> 2 row(s) retrieved.
>
> The value "should" be 0.5 but seems to have been altered and is not
> displayed properly in dbaccess and is causing problems within our
> apps. Anyone know why this might be? And what the significance of
> 9988.89 is?
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list
On Wed, 2007-09-19 at 04:38 -0700, ian.miell@gmail.com wrote: > Anyone ever seen anything like this before? > > > select <col>, <col2> from <table> where <col3> = 17718120; > > > <col> <col2> > > 6285416 0.05 > 6285417 //.// > > 2 row(s) retrieved. My educated guess is that you have corrupt data. Informix stores decimals as base-100 numbers with exponent, i.e. internally a decimal is a string of "hyperdigits" between 0 and 99. The above is what Informix would display if you sneak an illegal -11 into the ones and one-hundredths positions. You're not saying anything about what your environment is, so it's impossible to know what could have caused this data corruption and/or how it can be fixed. If you know what the value should be, you could try updating the table to put the correct value in, but that won't tell you how the table got messed up in the first place and if it could happen again. To identify possible causes, you should tell us what kind and version of engine you're using and how the data is accessed, i.e. what client language(s) the application is using. HTH, -- Carsten Haese http://informixdb.sourceforge.net
Carsten, Thanks very much for your help on this. It's what I figured, but it was little more than a guess. I don't have the privileges to really get into this, and the customer is happy for us to update and not happy to hand over the LL's. If there's any kind of pattern I will be more insistent, but this info will help me if I get into a debate with them about what it might be a symptom of. Cheers, Ian On 19 Sep, 14:02, Carsten Haese <cars...@uniqsys.com> wrote: > On Wed, 2007-09-19 at 04:38 -0700, ian.mi...@gmail.com wrote: > > Anyone ever seen anything like this before? > > > > select <col>, <col2> from <table> where <col3> = 17718120; > > > <col> <col2> > > > 6285416 0.05 > > 6285417 //.// > > > 2 row(s) retrieved. > > My educated guess is that you have corrupt data. Informix stores > decimals as base-100 numbers with exponent, i.e. internally a decimal is > a string of "hyperdigits" between 0 and 99. The above is what Informix > would display if you sneak an illegal -11 into the ones and > one-hundredths positions. > > You're not saying anything about what your environment is, so it's > impossible to know what could have caused this data corruption and/or > how it can be fixed. If you know what the value should be, you could try > updating the table to put the correct value in, but that won't tell you > how the table got messed up in the first place and if it could happen > again. To identify possible causes, you should tell us what kind and > version of engine you're using and how the data is accessed, i.e. what > client language(s) the application is using. > > HTH, > > -- > Carsten Haesehttp://informixdb.sourceforge.net
ian.miell@gmail.com wrote:
> Anyone ever seen anything like this before?
>
>> select <col>, <col2> from <table> where <col3> = 17718120;
>
> <col> <col2>
>
> 6285416 0.05
> 6285417 //.//
>
> 2 row(s) retrieved.
>
>> select <col>, <col2> + 1 -1 + 0.5 - 0.5 from <table> where <col3> = 17718120;
>
>
> <col> (expression)
>
> 6285416 0.05
> 6285417 9988.89
>
> 2 row(s) retrieved.
>
> The value "should" be 0.5 but seems to have been altered and is not
> displayed properly in dbaccess and is causing problems within our
> apps. Anyone know why this might be? And what the significance of
> 9988.89 is?
Which version of IDS, on which platform?
I've seen some vaguely similar issues before, but only on rather old
versions of IDS on rather old versions of operating systems (not very
surprisingly) and it wasn't slashes that I saw.
If you are able to dump the pages which contain the rows (oncheck and
some obscure options like -pT), I'd be curious to look at the hex values.
As someone else said, you appear to have some corruption in the data,
and nothing short of an update is likely to fix it. There are a couple
of puzzling things - why did the formatting code not object to the
invalid data, and how did the data get corrupted. The latter may be
forever unanswerable.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/
publictimestamp.org/ptb/PTB-1341 ripemd256 2007-09-20 03:00:06
F394FA8EAB0CB5A68689DAB982CE93F1A5099B2FD833420A89D486131CC5DEEB