View Issue Details

IDProjectCategoryView StatusLast Update
0029760FPCDatabasepublic2016-03-20 23:53
ReporterGernot RieboldAssigned ToLacaK 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionno change required 
PlatformWindowsOSWindows 10OS Version10 x64
Product Version3.0.0Product BuildLazarus 1.6 
Target VersionFixed in Version3.1.1 
Summary0029760: Heavy Error in Firebird-DB with Numeric / Decimal Fields - value is multiplied with 10000 insert and update
DescriptionFirebird-DB Version 2.5.5 /UTF8 has a Numeric(10,2) and / or a Decimal(10,2) Field.
Insert and update multiplies numeric and decimal value with 10000. This happens until values are too big to store in the DB.

Problem happens reproducable with Lazarus 1.6. Works with Lazarus 1.4.

Steps To ReproduceFirebird-DB with a Numeric(10,2) and / or a Decimal(10,2) Field. Create a small project with 1 IBConnection, SqlTransaction, SQLLibraryLoader, Query, DataSource, DB-Navigator, DBGrid.
Insert / Update the Numeric and / or Decimal Field. In the DB value is inserted / updated multiplied with 10000.

Problem only with Lazarus 1.6
Additional InformationAttached you find a Lazarus project with a readme.txt and a PDF with 4 screenshots to reproduce the problem.

Please change the credentials (Hostname, Database Path) for the DB in unit1 - procedure TForm1.FormCreate.

I use Numeric for amount fields.
TagsNo tags attached.
Fixed in Revision32054
FPCOldBugId
FPCTarget
Attached Files
  • TestNumericDecimal.zip (451,809 bytes)
  • tnd.pas (905 bytes)
    program tnd;
    
    uses db, sqldb, ibconnection;
    
    Procedure DisplayData(Q : TSQLQUery);
    
    Var
      F : TField;
    
    
    begin
      Q.SQL.Text:='Select * from a';
      Q.OPen;
      While not Q.EOF do
        begin
        For F in Q.Fields do
          Writeln(F.FieldName,' : ',F.AsString);
        Q.Next;
        end;
    end;
    
    Var
      C : TIBConnection;
      T : TSQLTransaction;
      Q : TSQLQuery;
    
    begin
      C:=TIBConnection.Create(Nil);
      try
        C.DatabaseName:='localhost:/home/firebird/wisa/testdb.fb';
        C.UserName:='***';
        C.Password:='***';
        T:=TSQLTransaction.Create(C);
        C.Transaction:=T;
        C.Connected:=True;
        Q:=TSQLQuery.Create(C);
        Q.DataBase:=C;
        Writeln('Before:');
        DisplayData(Q);
        Q.SQL.Text:='INSERT INTO a values (:b,:c)';
        Q.ParamByName('b').AsCurrency:=5.6;
        Q.ParamByName('c').AsCurrency:=7.8;
        Q.ExecSQL;
        T.Commit;
        Writeln('After:');
        DisplayData(Q);
      finally
        C.Free;
      end;
    end.
    
    
    tnd.pas (905 bytes)
  • bug29760.lpr (1,453 bytes)
  • TestNumericDecimal1.zip (453,737 bytes)

Relationships

related to 0028748 resolvedYuriy Sydorov Adding Double to Currency - wrong result 

Activities

Gernot Riebold

2016-03-01 00:24

reporter  

TestNumericDecimal.zip (451,809 bytes)

LacaK

2016-03-01 12:42

developer   ~0090521

Last edited: 2016-03-01 18:34

View 2 revisions

Cann't reproduce with Firebird 2.5.5 Embeded and trunk version of fcl-db under Win64, but with Lazarus 1.4.4.
Also cann't reproduce with Lazarus 1.7 + FPC 3.0 as installed from getlazarus.org under Win32
So it seems, that bug is out of fcl-db and is x64 specific ?

Can somebody else reproduce it ?

Gernot Riebold

2016-03-02 00:36

reporter   ~0090537

You are right. I tested with Win32 / Lazarus 1.6. It works.
The problem is with Win64 / Lazarus 1.6.

Mark Morgan Lloyd

2016-03-03 23:37

reporter   ~0090620

> Can somebody else reproduce it ?

FPC 3.0.0, Laz 1.6, Debian (not Raspbian) "Jessie", on RPi2 connecting to PostgreSQL backend returning floats x10,000 (integers are OK).

LacaK

2016-03-04 07:33

developer   ~0090623

@Mark: how is defined column in table on PostgreSQL, where x10000 occurs

If we have two independent connectors, where "same error" happens it leads me to idea, that error is deeper than in fcl-db.

I remember that there was similar error when "currency" data type was used on Win64 platform.

Mark Morgan Lloyd

2016-03-04 10:21

reporter   ~0090626

Last edited: 2016-03-04 10:32

View 2 revisions

The affected columns describe themselves as numeric(4,1) or numeric(5,1) although my notes suggest that they were defined as decimal(4,1) etc. Integers, timestamps etc. appear unaffected.

I've not noticed this in the past when this same program has been run on various platforms, I'd suggest that there's still a slight possibility that it could be related to an underlying library problem rather than FPC.

Looking at the numbers involved this probably isn't a locale issue, but for completeness I'm in the UK where the decimal marker is dot and the thousands separator is comma.

I can't realistically do further testing for at least a few hours, depending on other committments.

Michael Van Canneyt

2016-03-04 12:35

administrator   ~0090633

We need to establish whether the error is in the GUI layer or not.

I created a sample db:

cadwal: >isql-fb
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 'localhost:/home/firebird/wisa/testdb.fb';
SQL> create table a (b numeric(4,1), c decimal(5,1));
SQL> insert into a values (1.2,3.4);
SQL> select *from a;

      B C
======= ============
    1.2 3.4

SQL> commit;

Then I ran the attached tnd.pas program a couple of times.

Output:

cadwal: >./tnd
Before:
B : 1.2
C : 3.4
B : 5.6
C : 7.8
After:
B : 1.2
C : 3.4
B : 5.6
C : 7.8
B : 5.6
C : 7.8

The output is as expected.
This is a linux 64 bit machine, I tried FPC versions 2.6.4, 3.0.0 and 3.1.1

Also, please don't ever include actual database files in a sample.
Firebird databases are not transportable across OS-es and CPU architectures, so the results may well be wrong when you copy the db.

LacaK

2016-03-04 13:59

developer   ~0090634

So in both cases error happens with numeric SQL data type, which is in sql-db connectors handled using "currency" data type.
So it confirms my suspection, that this error is not related to fcl-db alone.

Michael Van Canneyt

2016-03-04 14:05

administrator  

tnd.pas (905 bytes)
program tnd;

uses db, sqldb, ibconnection;

Procedure DisplayData(Q : TSQLQUery);

Var
  F : TField;


begin
  Q.SQL.Text:='Select * from a';
  Q.OPen;
  While not Q.EOF do
    begin
    For F in Q.Fields do
      Writeln(F.FieldName,' : ',F.AsString);
    Q.Next;
    end;
end;

Var
  C : TIBConnection;
  T : TSQLTransaction;
  Q : TSQLQuery;

begin
  C:=TIBConnection.Create(Nil);
  try
    C.DatabaseName:='localhost:/home/firebird/wisa/testdb.fb';
    C.UserName:='***';
    C.Password:='***';
    T:=TSQLTransaction.Create(C);
    C.Transaction:=T;
    C.Connected:=True;
    Q:=TSQLQuery.Create(C);
    Q.DataBase:=C;
    Writeln('Before:');
    DisplayData(Q);
    Q.SQL.Text:='INSERT INTO a values (:b,:c)';
    Q.ParamByName('b').AsCurrency:=5.6;
    Q.ParamByName('c').AsCurrency:=7.8;
    Q.ExecSQL;
    T.Commit;
    Writeln('After:');
    DisplayData(Q);
  finally
    C.Free;
  end;
end.

tnd.pas (905 bytes)

Michael Van Canneyt

2016-03-04 14:05

administrator   ~0090635

We will only know when the command-line program is run on the database of the OP.

Mark Morgan Lloyd

2016-03-04 15:50

reporter   ~0090637

Last edited: 2016-03-04 15:58

View 2 revisions

In my case I'm not using any LCL DB-aware components, I'm parsing the values straight out of a TSQLQuery:

PROCEDURE TDefaultViewForm.SyncForm(db: TSQLQuery; syncControl: TSyncControl= scEverything;
                                        latitude: DOUBLE= 51.5; longitude: DOUBLE= 0.0);

...

  FUNCTION vl1(CONST now: STRING; predicate: boolean= true): STRING;

  (* Display a floating point value with one decimal. *)

  VAR nowVal: SINGLE;

  BEGIN
    nowVal:= db.FieldValues[now];
    RESULT:= FloatToStrF(nowVal, ffFixed, 7, 1);
    IF NOT predicate THEN
      RESULT:= StringOfChar(strikeout, Length(RESULT))
  END { vl1 } ;

The result is then placed in a (non-DB-aware) grid for presentation. The object of doing it like this was to explore whether any glitches to the UI could be avoided if a DB query took a noticeable time to run.

Michael Van Canneyt

2016-03-04 16:46

administrator   ~0090638

Aha. You use variants.

Didn't you know that there is a special department in the deepest pits of hell for people that use variants ? :-)

Well, it gives us a trail to work with. I tried a modified version of tnd.pas, but it still works correctly on the platform/versions I tried.

Mark Morgan Lloyd

2016-03-04 23:19

reporter   ~0090644

I confess! I have used variants gratuitously! I am unclean!

Now are you seriously suggesting that it's something to do with the fix for 24863, which I notice went in for the release being discussed?

Michael Van Canneyt

2016-03-04 23:39

administrator   ~0090645

I am not suggesting anything.
 
But the main difference between your and my code (both non-gui) is the use of variants.

I don't know how the GUI controls work, but I wouldn't be surprised to learn they use variants as well.

It therefor seems logical to search in that direction rather than in fcl-db itself. We need some direction to be able to reproduce this, after all...

Gernot Riebold

2016-03-05 01:30

reporter   ~0090646

My testing same Win10 x64 machine, Lazarus 1.6 with FPC 3.0.0, same database but using ZEOSLib everything works well. Same with Lazarus 1.6 32 bit.
Problem was found on Win7 and Win10 x64 machines with Lazarus 1.6.
I hope it helps to point out the problem.

Gernot Riebold

2016-03-09 00:35

reporter   ~0090782

Please, is there any chance to fix this problem? I have a few business applications and want to move them from lazarus 1.4 to 1.6.

I will be very happy if someone can fix the problem.

Michael Van Canneyt

2016-03-09 08:51

administrator   ~0090784

We need to be able to reproduce the problem.

The database you provided is not usable. Firebird databases are not transportable accross OSes or CPUs.

Can we have a transportable backup of your database, or the metadata used to create it ?

Secondly, the machine is 64-bit, but is the lazarus you used 32 or 64 bit ?

The sentence
"Same with Lazarus 1.6 32 bit"
means what ? "works well", or "has the bug" ?

LacaK

2016-03-09 09:49

developer   ~0090785

I have created test program without LCL dependency, which mimics your example program. Can you test with attached bug29760 and report results ?

LacaK

2016-03-09 09:49

developer  

bug29760.lpr (1,453 bytes)

Gernot Riebold

2016-03-09 20:51

reporter  

TestNumericDecimal1.zip (453,737 bytes)

Gernot Riebold

2016-03-09 21:04

reporter   ~0090812

First of all - thanks for you help.

Post No. 0090784 -Michael Van Canneyt
I upload a new file "TestNumericDecimal1.zip" which contains a transportable *.fbk file a sql-file for create the db an the table and the project.

The problem occurs only with Lazarus 1.6 64bit reproducable on a Win10 64bit and Win7 64bit machine.

Compiling the same code with Lazarus 1.6 32bit and test, there is NO problem. Everything works as expected. So the problem is only with Lazarus 1.6 64bit.

Gernot Riebold

2016-03-09 21:23

reporter   ~0090814

Last edited: 2016-03-09 21:24

View 3 revisions

Thanks LacaK for helping - This is the result of your bug29760.lpr programm
see the difference "After Refresh" compiled with
Lazarus 1.6. 64bit/32bit

=====================================================
Lazarus 1.6 64bit on a Win10 / 64bit machine.
=====================================================
Before Edit:
0: TBL1_KEY(Integer) = 1
1: TBL1_VARCHAR2(String) =
2: TBL1_NUMERIC(BCD) =
3: TBL1_DECIMAL(BCD) =
4: TBL1_DATE(Date) =
5: TBL1_TIMESTAMP(DateTime) =
6: TBL1_TIME(Time) =
7: TBL1_BLOB(Memo) =
8: TBL1_SMALLINT(Smallint) = 0
After Post:
0: TBL1_KEY(Integer) = 1
1: TBL1_VARCHAR2(String) =
2: TBL1_NUMERIC(BCD) = 123,45
3: TBL1_DECIMAL(BCD) = 67,89
4: TBL1_DATE(Date) =
5: TBL1_TIMESTAMP(DateTime) =
6: TBL1_TIME(Time) =
7: TBL1_BLOB(Memo) =
8: TBL1_SMALLINT(Smallint) = 0
After Refresh:
0: TBL1_KEY(Integer) = 1
1: TBL1_VARCHAR2(String) =
2: TBL1_NUMERIC(BCD) = 1234500
3: TBL1_DECIMAL(BCD) = 678900
4: TBL1_DATE(Date) =
5: TBL1_TIMESTAMP(DateTime) =
6: TBL1_TIME(Time) =
7: TBL1_BLOB(Memo) =
8: TBL1_SMALLINT(Smallint) = 0

=====================================================
Lazarus 1.6 32bit on a Win10 64bit machine
=====================================================
Before Edit:
0: TBL1_KEY(Integer) = 1
1: TBL1_VARCHAR2(String) =
2: TBL1_NUMERIC(BCD) =
3: TBL1_DECIMAL(BCD) =
4: TBL1_DATE(Date) =
5: TBL1_TIMESTAMP(DateTime) =
6: TBL1_TIME(Time) =
7: TBL1_BLOB(Memo) =
8: TBL1_SMALLINT(Smallint) = 0
After Post:
0: TBL1_KEY(Integer) = 1
1: TBL1_VARCHAR2(String) =
2: TBL1_NUMERIC(BCD) = 123,45
3: TBL1_DECIMAL(BCD) = 67,89
4: TBL1_DATE(Date) =
5: TBL1_TIMESTAMP(DateTime) =
6: TBL1_TIME(Time) =
7: TBL1_BLOB(Memo) =
8: TBL1_SMALLINT(Smallint) = 0
After Refresh:
0: TBL1_KEY(Integer) = 1
1: TBL1_VARCHAR2(String) =
2: TBL1_NUMERIC(BCD) = 123,45
3: TBL1_DECIMAL(BCD) = 67,89
4: TBL1_DATE(Date) =
5: TBL1_TIMESTAMP(DateTime) =
6: TBL1_TIME(Time) =
7: TBL1_BLOB(Memo) =
8: TBL1_SMALLINT(Smallint) = 0

LacaK

2016-03-10 21:34

developer   ~0090872

This is compiler bug already fixed in trunk (in rev.32054).
You can reproduce it for example by:

var
 c: currency; d: double;
begin
  c:=123.45;
  d:=100;
  writeln(c*d);
end.

Issue History

Date Modified Username Field Change
2016-03-01 00:24 Gernot Riebold New Issue
2016-03-01 00:24 Gernot Riebold File Added: TestNumericDecimal.zip
2016-03-01 07:57 LacaK Assigned To => LacaK
2016-03-01 07:57 LacaK Status new => assigned
2016-03-01 12:42 LacaK Note Added: 0090521
2016-03-01 18:34 LacaK Note Edited: 0090521 View Revisions
2016-03-02 00:36 Gernot Riebold Note Added: 0090537
2016-03-03 23:37 Mark Morgan Lloyd Note Added: 0090620
2016-03-04 07:33 LacaK Note Added: 0090623
2016-03-04 10:21 Mark Morgan Lloyd Note Added: 0090626
2016-03-04 10:32 Mark Morgan Lloyd Note Edited: 0090626 View Revisions
2016-03-04 12:29 Michael Van Canneyt File Added: tnd.pas
2016-03-04 12:35 Michael Van Canneyt Note Added: 0090633
2016-03-04 13:59 LacaK Note Added: 0090634
2016-03-04 14:04 Michael Van Canneyt File Deleted: tnd.pas
2016-03-04 14:05 Michael Van Canneyt File Added: tnd.pas
2016-03-04 14:05 Michael Van Canneyt Note Added: 0090635
2016-03-04 15:50 Mark Morgan Lloyd Note Added: 0090637
2016-03-04 15:58 Mark Morgan Lloyd Note Edited: 0090637 View Revisions
2016-03-04 16:46 Michael Van Canneyt Note Added: 0090638
2016-03-04 23:19 Mark Morgan Lloyd Note Added: 0090644
2016-03-04 23:39 Michael Van Canneyt Note Added: 0090645
2016-03-05 01:30 Gernot Riebold Note Added: 0090646
2016-03-09 00:35 Gernot Riebold Note Added: 0090782
2016-03-09 08:51 Michael Van Canneyt Note Added: 0090784
2016-03-09 09:49 LacaK Note Added: 0090785
2016-03-09 09:49 LacaK File Added: bug29760.lpr
2016-03-09 20:51 Gernot Riebold File Added: TestNumericDecimal1.zip
2016-03-09 21:04 Gernot Riebold Note Added: 0090812
2016-03-09 21:23 Gernot Riebold Note Added: 0090814
2016-03-09 21:24 Gernot Riebold Note Edited: 0090814 View Revisions
2016-03-09 21:24 Gernot Riebold Note Edited: 0090814 View Revisions
2016-03-10 21:34 LacaK Note Added: 0090872
2016-03-10 21:34 LacaK Relationship added related to 0028748
2016-03-10 21:35 LacaK Fixed in Revision => 32054
2016-03-10 21:35 LacaK Status assigned => resolved
2016-03-10 21:35 LacaK Fixed in Version => 3.1.1
2016-03-10 21:35 LacaK Resolution open => no change required