Informix: The meaning of dot (.)?

Question

Hey guys, I am wondering if anyone could tell me any special meaning of the dot (.) in Informix regarding expressions and etc.

For example in stored procedures I see it used with integers, decimals and chars and one thing that is bugging me quite a lot is this:

if value = '.' then
  //do something
end if

The above expression validates to true when value is of type numeric (5,1) and it is equal to 0.0

I have tried looking around and I can't find information on how a dot is treated but it seems " 0.0 = '.' " validates to true...

Can anyone enlighten me please?

Thanks

Answer 1

Can you show the data types and a working stored procedure that illustrates the problem?

There isn't supposed to be any special meaning to dot in that context. It is a string, and no numeric value should be equal to it; if the number is converted to a string, there will be either nothing (for NULL) or at least one digit, neither of which is the same as the string '.', and if the string '.' is converted to a number, that conversion should fail (arguably when the procedure is created, certainly at runtime).

One thing that puzzles me is that the syntax you are showing is not SPL syntax. SPL does not use 'end if', though I4GL does. Indeed, SPL (stored procedure language) only uses END in conjunction with a matching BEGIN.

---

It appears that my memory is failing and that I should not try reading manuals just before midnight.

It also appears that this code does what I would not expect...

+ set debug file to "/tmp/x1";
SET DEBUG FILE TO: Rows processed = 0
+ drop procedure so2139024();
DROP PROCEDURE: Rows processed = 0
+ create procedure so2139024() returning int as rv;

    define value numeric(5,1);
    define rv integer;
    trace on;

    let rv = 0;
    let value = 0.0;
    if value = '.' then
        let rv = 1;
    end if;

    return rv;

end procedure;
CREATE PROCEDURE: Rows processed = 0
+ execute procedure so2139024();
1
EXECUTE PROCEDURE: Rows processed = 1

So, for some reason, the comparison is working; the value zero compares equal to dot. This was tested on MacOS X 10.6.2 with IBM Informix Dynamic Server 11.50.FC6 (and SQLCMD 86.04, built with CSDK 3.50.FC4, but running with 3.50.FC6).

The debug file contains:

trace on

expression:(= value, ".")
evaluates to t 
let rv = 1 
expression:rv
evaluates to 1 
procedure so2139024 returns 1 

iteration of cursory procedure so2139024

A priori, this should be reported via IBM/Informix Tech Support. I think it is most likely a bug of some sort, but I don't know how it is coming up with the answer. I will check through back-door channels too.

---

Back-door channels show that the likely problem is that the function deccvasc() in the ESQL/C library (also used internally by the server) is mishandling the string '.'.

The ESQL/C test code here shows that:

#include <stdio.h>
#include "dumpesql.h"

int main(void)
{
    dec_t d;

    int rc = deccvasc(".", 1, &d);
    printf("rc = %d\n", rc);
    dump_decimal(stdout, "Decimal", &d);
    return(0);
}

The dump_decimal() function is non-standard, but prints information from the decimal structure. The output is:

rc = 0
DECIMAL: Decimal -- address 0x7FFF5FBFF090
E:  -64, S = 1 (+), N =  0, M = value is ZERO

Consequently, the server is (mistakenly) accepting '.' as a valid representation of zero, rather than getting an error reported. For the time being, you will have to edit the stored procedure to make more sense - it is not clear what the test was supposed to achieve, but it clearly isn't written correctly. (That is not denying that there is also a bug in the server.) Please report this to IBM/Informix Technical Support.