tSQLQuery/Firebird: incorrect results for negative numerics
lazarus / 16-Databases

----------------------------------------------------------------------
keith · 2009-01-16 07:11

I am having trouble getting FPC/Lazarus to return a correct value from
Firebird. I am running:
 * SUSE Linux 11.1
 * FPC 2.2.2-0
 * Lazarus 0.9.26-0
 * FireBird 2.1.1.17910
 * Pentium 4, 1700mgH

I have a table which contains a column of numbers in a particular
column. The numbers represent handicaps in a range from -3.5 to 24.
After loading the table, ISQL will list the numbers correctly.
However, neither tSQLQuery nor TDBGRid retrun the correct value for
the negative numbers, but zero and positive (unsigned?) numbers are
perfect. Examples follow.

Table Definition (via FlameRobin):
-----------
DROP TABLE HandicapTriggers;
COMMIT WORK;

CREATE TABLE HandicapTriggers
(
    TriggerPoint smallint     NOT NULL PRIMARY KEY,
    Handicap     numeric(4,1) NOT NULL
);
COMMIT WORK;
-----------

Table Load (via FlameRobin):
-----------
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 3050,
-3   );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 2800,
-2.5 );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 2600,
-2   );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 2400,
-1.5 );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 2250,
-1   );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 2100,
-0.5 );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 2000, 
0   );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 1950, 
0.5 );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 1900, 
1   );
INSERT INTO HANDICAPTRIGGERS (TRIGGERPOINT, HANDICAP) VALUES ( 1850, 
1.5 );
etc
-----------

ISQL listing after above load:
-----------
SQL> select * from handicaptriggers;

TRIGGERPOINT     HANDICAP
============ ============
        3050         -3.0
        2800         -2.5
        2600         -2.0
        2400         -1.5
        2250         -1.0
        2100         -0.5
        2000          0.0
        1950          0.5
        1900          1.0
        1850          1.5
etc
-----------

A DBGrid or a SQLQuery in a FPC/Lazarus program returns the negative
numbers as huge.

For example 3050 -> 429496726.6 (should be -3.0)

(I have a snapshot of the grid, but can't see how to make an
attachment).

This looks like the value is being treated as an unsigned integer by
the components.I cannot find anywhere that says NUMERIC or DECIMAL
columns are unsigned, and the fact that Firebird's ISQL interprets
them correctly indicates that they are not. I don't find a bug report
anywhere for this problem, but maybe my searching skills are
deficient.

Is there something I am missing here? Is this a problem only on Linux
or other platforms as well?

If someone can confirm the problem, I'll try an figure out how to file
a bug report.

Better yet, if you can tell me how to fix it, I'll go away and shut up
for a while.

----------------------------------------------------------------------
Marc · 2009-01-16 09:24

I don't know how to fix or what's wrong, but bugrep this anyway. If
you have found a fix you can attach the patch there too.

----------------------------------------------------------------------
arnix · 2009-01-16 20:44

Maybe it is related with this:
http://osdir.com/ml/ide.lazarus.general/2005-08/msg00028.html

As a temporary workaround try to use FLOAT (or DOUBLE, if you need
high precision) data type instead of numeric(4,1)

----------------------------------------------------------------------
keith · 2009-01-17 00:21

I will try FLOAT as a workaround.

I am trying to login to bugtracker to report the bug, but it won't
recognize my login ID. It says it doesn't exist or is blocked.

If I try to create a new one, just like my FPC one, it says it is
already in use. If I try to recover the password, it says there is no
such account. I can log on to the FPC Community forums just fine, but
it doesn't carry over onto the bugtracker.

Is there some initiation rite I have to pass through before I can
report bugs?

----------------------------------------------------------------------
Marc · 2009-01-17 00:37

IF you give me your login ID (here or private) then I can have a look

----------------------------------------------------------------------
keith · 2009-01-17 05:53

I have created a new logon ID just for bug tracker and have reported
this as bug number 12986.

----------------------------------------------------------------------
keith · 2009-01-19 12:09

Turns out this is a duplicate of 12862. It has been fixed (in the
trunk) and the author (Joost) is aiming it for FPC 2.2.4. Hopefully
that will happen sooner or later.