Extended type confused ? lazarus / 21-General ---------------------------------------------------------------------- mirce.vladimirov · 2022-11-11 23:28 I use Lazarus 2.2.4, 32bit, windows10. This code calculates sums of columns from a TSQLQuery (connected to Mysql db) and then puts results into Tedits : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var plastika, hartija, metal, aluminium, drvo, staklo, kompozit :Extended;  ......   plastika:=0;  hartija:=0;  metal:=0;  aluminium:=0;  drvo:=0;  staklo:=0;  kompozit:=0;   q1.first;  while not q1.eof do begin     plastika:=plastika+q1.FieldByName('plastika').AsFloat;     hartija:=hartija+q1.FieldByName('hartija').AsFloat;     metal:=metal+q1.FieldByName('metal').AsFloat;     aluminium:=aluminium+q1.FieldByName('aluminium').AsFloat;     staklo:=staklo+q1.FieldByName('staklo').AsFloat;     drvo:=drvo+q1.FieldByName('drvo').AsFloat;     kompozit:=kompozit+q1.FieldByName('kompozit').AsFloat;     q1.Next;  end;   edit_plastika.Text:=FloatToStr(plastika);  edit_hartija.Text:=FloatToStr(hartija);  edit_metal.Text:=FloatToStr(metal);  edit_aluminium.Text:=FloatToStr(aluminium);  edit_staklo.Text:=FloatToStr(staklo);  edit_drvo.Text:=FloatToStr(drvo);  edit_kompoziti.Text:=FloatToStr(kompozit);  edit_vkupno.Text:=FloatToS tr(plastika+hartija+metal+aluminium+staklo+drvo+kompozit);  Please see picture attached, the data in mysql table are identical like the ones in the grid. And the summed result is a comedy, it should be zero for all columns, but it's not, there are weird numbers with many decimal places. Since I use this version of Lazarus for the last seven days, i've found an earlier binary of my app, developed under Lazarus 1.4.4, and runned it against same filters and got the propper result. ---------------------------------------------------------------------- KodeZwerg · 2022-11-12 00:50 It looks good from point of what I do see there. I don't know from where it happens, maybe you have an chance to log each entry to see if the db send correct positive/negative values... An quick and dirty way could be by doing something like this Quotefunction FlipIt(const AEx: Extended): Extended; begin   if (AEx < 0) then     Result := AEx + (AEx * 2)     else     Result := AEx - (AEx * 2); end; ...your code      q1.Next;   end;   plastika := FlipIt(plastika);   hartija := FlipIt(hartija);   metal := FlipIt(metal);   aluminium := FlipIt(aluminium);   drvo := FlipIt(drvo);   staklo := FlipIt(staklo);   kompozit := FlipIt(kompozit);   edit_plastika.Text:=FloatToStr(plastika); ...your code(...untested, just written in here...) ---------------------------------------------------------------------- Thaddy · 2022-11-12 07:09 You should not use FloatToStr, but FloatToStrF if you want to avoid the rounding errors: https://www.freepascal.org/docs-html/rtl/sysutils/floattostrf.html That is because not all extended values can be completely expressed, in fact it is an approximation. With FloatToStrF you can specify the number of digits/precision that you want. When applied properly the sum errors will disappear. Also note that except for WIN32, Extended is almost always a 64 bit double, not 80 bit extended depending on ABI. E.G. the win64 ABI specifies extended as a 64 bit double. Keep that in mind if you want to compile for multiple OS's. In that case there can still be rounding errors possible and you need to apply FloatToStrF in the same way as described above, but for double. It is sufficient to ignore the smallest 1 or 2 digits from the fraction. Note that it is just a display format. the extended value itself is never affected. ( except when you convert the string back to extended of course) Using the fixed format gives usually the best results with a 15 digit fraction. See the example in the manual. ---------------------------------------------------------------------- dseligo · 2022-11-12 07:42 Try to change lines 26 to 33 with: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   edit_plastika.Text := FormatFloat('#,##0.00', plastika);  edit_hartija.Text := FormatFloat('#,##0.00', hartija);  edit_metal.Text := FormatFloat('#,##0.00', metal);  edit_aluminium.Text := FormatFloat('#,##0.00', aluminium);  edit_staklo.Text := FormatFloat('#,##0.00', staklo);  edit_drvo.Text := FormatFloat('#,##0.00', drvo);  edit_kompoziti.Text := FormatFloat('#,##0.00', kompozit);  edit_vkupno.Text := FormatFloat('#,##0.00', plastika+hartija+metal+aluminium+staklo+drvo+kompozit); ---------------------------------------------------------------------- Thaddy · 2022-11-12 09:20 Or:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   edit_plastika.Text := plastika.ToString(ffFixed,10,2); // 10 and 2 are arbitrary, adjust to taste  edit_hartija.Text  := hartija.ToString(ffFixed,10,2);  edit_metal.Text    := meta.ToString(ffFixed,10,2);  edit_aluminium.Text:= aluminium.ToString(ffFixed,10,2);  edit_staklo.Text   := staklo.ToString(ffFixed,10,2);  edit_drvo.Text     := drvo.ToString(ffFixed,10,2);  edit_kompoziti.Text:= kompozit.ToString(ffFixed,10,2);  edit_vkupno.Text   := (plastika+hart ija+metal+aluminium+staklo+drvo+kompozit).ToString(ffFixed,10,2); ---------------------------------------------------------------------- Zvoni · 2022-11-12 10:04 What i don‘t understand: why are you even doing the calculation in the frontend instead of building the sum in your SQL-Statement? The way the aggregate-functions are designed/built in the database servers, you‘re never going to beat them with own code performance- wise EDIT: and why even using Extended? IIRC, MySQL has Double (64 bit floating point), so what do you expect to gain from using extended? ---------------------------------------------------------------------- dseligo · 2022-11-12 10:24 Quote from: Zvoni on November 12, 2022, 11:04:39 amWhat i don‘t understand: why are you even doing the calculation in the frontend instead of building the sum in your SQL-Statement? The way the aggregate-functions are designed/built in the database servers, you‘re never going to beat them with own code performance- wise Then he would need 2 queries. He has 7 values, he won't see performance issues (and it won't be much more values in the future in this query, he is probably calculating waste quantities). QuoteEDIT: and why even using Extended? IIRC, MySQL has Double (64 bit floating point), so what do you expect to gain from using extended? If there is no need to use more than 3 (or 4) decimal places, I would use Currency type. There is no rounding issues with adding and subtracting there. ---------------------------------------------------------------------- dseligo · 2022-11-12 10:25 Quote from: Thaddy on November 12, 2022, 10:20:54 amOr:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   edit_plastika.Text := plastika.ToString(ffFixed,10,2); // 10 and 2 are arbitrary, adjust to taste  edit_hartija.Text  := hartija.ToString(ffFixed,10,2);  edit_metal.Text    := meta.ToString(ffFixed,10,2);  edit_aluminium.Text:= aluminium.ToString(ffFixed,10,2);  edit_staklo.Text   := staklo.ToString(ffFixed,10,2);  edit_drvo.Text     := drvo.ToString(ffFixed,10,2);  edit_kompoziti.Text:= kompozit.ToString(ffFixed,10,2);  edit_vkupno.Text   := (plastika+hart ija+metal+aluminium+staklo+drvo+kompozit).ToString(ffFixed,10,2);  I like FormatFloat because it is visually easier to see what format will look like and it's simpler to use. In your example there is no grouping of digits (although OP didn't ask for it). ---------------------------------------------------------------------- Thaddy · 2022-11-12 10:37 As suggested above, MSSQL stores float as double. As suggested above, a select sum() query will be easier and probably faster. ---------------------------------------------------------------------- Zvoni · 2022-11-12 11:16 Quote from: Thaddy on November 12, 2022, 11:37:06 amAs suggested above, MSSQL stores float as double. As suggested above, a select sum() query will be easier and probably faster. OP uses MySQL, not MSSQL, but yeah. That‘s the idea. ---------------------------------------------------------------------- Zvoni · 2022-11-12 11:16 Quote from: dseligo on November 12, 2022, 11:24:24 amQuote from: Zvoni on November 12, 2022, 11:04:39 amWhat i don‘t understand: why are you even doing the calculation in the frontend instead of building the sum in your SQL-Statement? The way the aggregate-functions are designed/built in the database servers, you‘re never going to beat them with own code performance- wise Then he would need 2 queries. He has 7 values, he won't see performance issues (and it won't be much more values in the future in this query, he is probably calculating waste quantities). QuoteEDIT: and why even using Extended? IIRC, MySQL has Double (64 bit floating point), so what do you expect to gain from using extended? If there is no need to use more than 3 (or 4) decimal places, I would use Currency type. There is no rounding issues with adding and subtracting there. Why would he need 2 queries????? ---------------------------------------------------------------------- Thaddy · 2022-11-12 11:17 Quote from: Zvoni on November 12, 2022, 12:16:09 pmOP uses MySQL, not MSSQL, but yeah. That‘s the idea. In this case that does not matter. And yes, you need just the one query. ---------------------------------------------------------------------- dseligo · 2022-11-12 12:32 Quote from: Zvoni on November 12, 2022, 12:16:43 pmQuote from: dseligo on November 12, 2022, 11:24:24 amQuote from: Zvoni on November 12, 2022, 11:04:39 amWhat i don‘t understand: why are you even doing the calculation in the frontend instead of building the sum in your SQL- Statement? The way the aggregate-functions are designed/built in the database servers, you‘re never going to beat them with own code performance- wise Then he would need 2 queries. He has 7 values, he won't see performance issues (and it won't be much more values in the future in this query, he is probably calculating waste quantities). QuoteEDIT: and why even using Extended? IIRC, MySQL has Double (64 bit floating point), so what do you expect to gain from using extended? If there is no need to use more than 3 (or 4) decimal places, I would use Currency type. There is no rounding issues with adding and subtracting there. Why would he need 2 queries????? In his screenshot he shows rows in grid, so he need a query to get individual rows. Can you show how it can be done (simply) with one query? ---------------------------------------------------------------------- dseligo · 2022-11-12 12:51 Quote from: Thaddy on November 12, 2022, 11:37:06 amAs suggested above, MSSQL stores float as double. I was just suggesting that he could use Currency type and with that type he wouldn't have rounding issues. MySQL has 'Decimal' type which plays very nicely with Currency type in FPC. QuoteAs suggested above, a select sum() query will be easier and probably faster. He already has code written. So you are suggesting to write additional query to speed up adding values in 7 rows? And I doubt it will be faster, especially if MySQL is reached over some slow network. Don't get me wrong. I understand that with thousands of rows and with no need to access individual row, it's easier and faster to use aggregate functions, but in this particular case it doesn't matter (and OP don't have problems because of that). I'll give you my example: I write some rows to printer and calculate totals in the same go. At the end I print sums. So you say it's easier for me and faster to add another query (or to make complicated query) just to have totals? ---------------------------------------------------------------------- Zvoni · 2022-11-12 12:58 Quote from: dseligo on November 12, 2022, 01:32:45 pmQuote from: Zvoni on November 12, 2022, 12:16:43 pmQuote from: dseligo on November 12, 2022, 11:24:24 amQuote from: Zvoni on November 12, 2022, 11:04:39 amWhat i don‘t understand: why are you even doing the calculation in the frontend instead of building the sum in your SQL-Statement? The way the aggregate-functions are designed/built in the database servers, you‘re never going to beat them with own code performance- wise Then he would need 2 queries. He has 7 values, he won't see performance issues (and it won't be much more values in the future in this query, he is probably calculating waste quantities). QuoteEDIT: and why even using Extended? IIRC, MySQL has Double (64 bit floating point), so what do you expect to gain from using extended? If there is no need to use more than 3 (or 4) decimal places, I would use Currency type. There is no rounding issues with adding and subtracting there. Why would he need 2 queries????? In his screenshot he shows rows in grid, so he need a query to get individual rows. Can you show how it can be done (simply) with one query? Keyword „ROLLUP“ Exact syntax depends on his MySQL-Version ---------------------------------------------------------------------- dseligo · 2022-11-12 13:04 Quote from: Zvoni on November 12, 2022, 01:58:38 pmQuote from: dseligo on November 12, 2022, 01:32:45 pmQuote from: Zvoni on November 12, 2022, 12:16:43 pmQuote from: dseligo on November 12, 2022, 11:24:24 amQuote from: Zvoni on November 12, 2022, 11:04:39 amWhat i don‘t understand: why are you even doing the calculation in the frontend instead of building the sum in your SQL-Statement? The way the aggregate-functions are designed/built in the database servers, you‘re never going to beat them with own code performance- wise Then he would need 2 queries. He has 7 values, he won't see performance issues (and it won't be much more values in the future in this query, he is probably calculating waste quantities). QuoteEDIT: and why even using Extended? IIRC, MySQL has Double (64 bit floating point), so what do you expect to gain from using extended? If there is no need to use more than 3 (or 4) decimal places, I would use Currency type. There is no rounding issues with adding and subtracting there. Why would he need 2 queries????? In his screenshot he shows rows in grid, so he need a query to get individual rows. Can you show how it can be done (simply) with one query? Keyword „ROLLUP“ Exact syntax depends on his MySQL-Version Yes, very simple. And how would DBGrid look like then? ---------------------------------------------------------------------- mirce.vladimirov · 2022-11-12 13:45 QuoteKeyword „ROLLUP“ Rollup works when aggregating, needs "group by",  And in this query I dont aggregate, just simple select the basic rows, want to represent the rows as they are written in the table. And also show the sum of some columns. I know I can do another query, but my form lets the user to choose 10-14 different filters which I use in the "where..." part of the query, so I could present exactly what the user wanted. If I have two queries for the same records, one query to jus select rows and another one to calculate the sum, this complicates maintenance, because in future maybe I will apply a new filter to the grid-query and potentialy forget to apply the same filter in the summary-query, and nobody wants difference between the grid and the sums. Maintain two queries complicates your life,  maintaining one query is easier. When I use a DBGrid , Rollup is messing the last line of my Grid, doesnot look good when you see it. If there is another way of having a single query and get separate rows PLUS the summary of same rows, please let me know that. I am very open to learning, so I am very thankfull to all people who answered. ---------------------------------------------------------------------- Zvoni · 2022-11-12 14:10 Quote from: mirce.vladimirov on November 12, 2022, 02:45:41 pmQuoteKeyword „ROLLUP“ Rollup works when aggregating, needs "group by",  And in this query I dont aggregate, just simple select the basic rows, want to represent the rows as they are written in the table. And also show the sum of some columns. I know I can do another query, but my form lets the user to choose 10-14 different filters which I use in the "where..." part of the query, so I could present exactly what the user wanted. If I have two queries for the same records, one query to jus select rows and another one to calculate the sum, this complicates maintenance, because in future maybe I will apply a new filter to the grid-query and potentialy forget to apply the same filter in the summary-query, and nobody wants difference between the grid and the sums. Maintain two queries complicates your life,  maintaining one query is easier. When I use a DBGrid , Rollup is messing the last line of my Grid, doesnot look good when you see it. If there is another way of having a single query and get separate rows PLUS the summary of same rows, please let me know that. I am very open to learning, so I am very thankfull to all people who answered. And that‘s one of the reasons why i‘ve never used DB-bound controls in 25 years of programming. You have to decide what‘s easier: juggling around values in your frontend with every pitfalls that entails, or getting everything you want ready made, in worst case with a second query. EDIT: just thinking about it because of DBGrid i might have an idea how to get everything in one query, but would need testdata to play with. IIRC, in DBGrid you can choose which columns to display, even if the query has more fields than the DBGrid ---------------------------------------------------------------------- Thaddy · 2022-11-12 14:14 It does not need a group by but a double select in the query. Pseudo code: select sum() where select ... ---------------------------------------------------------------------- PascalDragon · 2022-11-12 14:59 Quote from: Thaddy on November 12, 2022, 08:09:56 amAlso note that except for WIN32, Extended is almost always a 64 bit double, not 80 bit extended depending on ABI. Extended is an alias to Double on all non-x86 targets as well as x86_64-win64. On all other x86 targets (no matter if i8086, i386 or x86_64) Extended is 10-Byte. ---------------------------------------------------------------------- Zvoni · 2022-11-14 08:50 Quote from: mirce.vladimirov on November 12, 2022, 02:45:41 pmQuoteKeyword „ROLLUP“ Rollup works when aggregating, needs "group by",  And in this query I dont aggregate, just simple select the basic rows, want to represent the rows as they are written in the table. And also show the sum of some columns. I know I can do another query, but my form lets the user to choose 10-14 different filters which I use in the "where..." part of the query, so I could present exactly what the user wanted. If I have two queries for the same records, one query to jus select rows and another one to calculate the sum, this complicates maintenance, because in future maybe I will apply a new filter to the grid-query and potentialy forget to apply the same filter in the summary-query, and nobody wants difference between the grid and the sums. Maintain two queries complicates your life,  maintaining one query is easier. When I use a DBGrid , Rollup is messing the last line of my Grid, doesnot look good when you see it. If there is another way of having a single query and get separate rows PLUS the summary of same rows, please let me know that. I am very open to learning, so I am very thankfull to all people who answered. Right, here is some sample SQL how i would go about it, if i had to use db-bound-controls (Grid, Edit, whatever). I used SQLite to create/test it, and i used here a CTE to provide base-values. That would be the OP's "real" table. The Important part is after the cte. NotaBene: I'm guessing at the table-structure and filters involved Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i WITH cte(ID, Plastic, Composite, Metal, SomeDate) AS (        SELECT * FROM         (VALUES ('1', '2.5', '852.36', '1258.4', '2022-08-15'),                       ('2', '4.7', '123.56', '852.3', '2022-08-20'),                       ('3', '7.3', '452.1', '645.745', '2022-09-13'),                      ('4', '1.89', '358.49', '198.75', '2022-10-09'),                      ('5', '6.85', '745.4', '332.58', '2022-11-10')))  SELECT T.Plastic, T.Composite, T.Metal, T.SomeDate, I.S1, I.S2, I.S3 FROM CTE AS TINNER JOIN (SELECT SUM(Plastic) AS S1, SUM(Composite) AS S2, SUM(Metal) AS S3 FROM CTE) AS ION 1=1You can bind the first three columns to the DBGrid, the DBEdit's to S1 to S3. Note: Of course, if your User is allowed to change Values in the DBGrid, all bet's are off, since i don't know, if DBGrid triggers a refresh of the underlying SQL (to update the sums). Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT T.Plastic, T.Composite, T.Metal, T.SomeDate, I.S1, I.S2, I.S3 FROM CTE AS TINNER JOIN (SELECT SUM(Plastic) AS S1, SUM(Composite) AS S2, SUM(Metal) AS S3 FROM CTE WHERE SomeDate BETWEEN :pDateFrom AND :pDateTo) AS ION 1=1WHERE T.SomeDate BETWEEN :pDateFrom AND :pDateTo No idea if Parameters can be used "multiple" times in MySQL, though i'd be surprised if not, since MySQL supports named parameters