Firebird

04.03.2010 22:12, Tomislav Avramovic wrote:
> Why not internali in Firebird Multiply have great priority then divide and keek scale rule
>
> 1.0 / 1440.0 * 1440.0 = 1.000

   Because in this case it would be

  1.0 / (1440.0*1440.0) = 1.0 / 2073600.00 = 0.000

-- 
   SY, SD.

On 04/03/2010 12:42, Ivan Prenosil wrote:
> CREATE EXCEPTION E 'Test';
> CREATE TABLE TAB(I INTEGER);
>
> SET TERM ^;
>
> CREATE OR ALTER TRIGGER T1_1
>    FOR TAB
>    INACTIVE BEFORE INSERT POSITION 20
> AS
> BEGIN
>    EXCEPTION E;
> END^
>
> CREATE OR ALTER TRIGGER T1_2
>    FOR TAB
>    ACTIVE BEFORE INSERT POSITION 20
> AS
> BEGIN
>    EXCEPTION E;
> END^
>
> SET TERM ;^
>
> INSERT INTO TAB VALUES(123);
>    
Could not reproduce:

SQL> CREATE EXCEPTION E 'Test';
SQL> CREATE TABLE TAB(I INTEGER);
SQL>
SQL> SET TERM ^;
SQL>
SQL> CREATE OR ALTER TRIGGER T1_1
CON>   FOR TAB
CON>   INACTIVE BEFORE INSERT POSITION 20
CON> AS
CON> BEGIN
CON>   EXCEPTION E;
CON> END^
SQL>
SQL> CREATE OR ALTER TRIGGER T1_2
CON>   FOR TAB
CON>   ACTIVE BEFORE INSERT POSITION 20
CON> AS
CON> BEGIN
CON>   EXCEPTION E;
CON> END^
SQL>
SQL> SET TERM ;^
SQL>
SQL> INSERT INTO TAB VALUES(123);
Statement failed, SQLSTATE = 42000
exception 1
-E
-Test
-At trigger 'T1_2' line: 6, col: 3


Adriano

"Adriano dos Santos Fernandes" wrote:
> Could not reproduce:
> 
> SQL> CREATE EXCEPTION E 'Test';
> SQL> CREATE TABLE TAB(I INTEGER);
> SQL>
> SQL> SET TERM ^;
> SQL>
> SQL> CREATE OR ALTER TRIGGER T1_1
> CON>   FOR TAB
> CON>   INACTIVE BEFORE INSERT POSITION 20
> CON> AS
> CON> BEGIN
> CON>   EXCEPTION E;
> CON> END^
> SQL>
> SQL> CREATE OR ALTER TRIGGER T1_2
> CON>   FOR TAB
> CON>   ACTIVE BEFORE INSERT POSITION 20
> CON> AS
> CON> BEGIN
> CON>   EXCEPTION E;
> CON> END^
> SQL>
> SQL> SET TERM ;^
> SQL>
> SQL> INSERT INTO TAB VALUES(123);
> Statement failed, SQLSTATE = 42000
> exception 1
> -E
> -Test
> -At trigger 'T1_2' line: 6, col: 3


Strange, I tried again with todays snapshot, Firebird-3.0.0.27817-0_Win32.zip


SQL> CREATE DATABASE 'c:\temp\tmp.fdb';
SQL>
SQL> CREATE EXCEPTION E 'Test';
SQL> CREATE TABLE TAB(I INTEGER);
SQL>
SQL> SET TERM ^;
SQL>
SQL> CREATE OR ALTER TRIGGER T1_1
CON>   FOR TAB
CON>   INACTIVE BEFORE INSERT POSITION 20
CON> AS
CON> BEGIN
CON>   EXCEPTION E;
CON> END^
SQL>
SQL> CREATE OR ALTER TRIGGER T1_2
CON>   FOR TAB
CON>   ACTIVE BEFORE INSERT POSITION 20
CON> AS
CON> BEGIN
CON>   EXCEPTION E;
CON> END^
SQL>
SQL> SET TERM ;^
SQL>
SQL> INSERT INTO TAB VALUES(123);
SQL>
SQL> DROP DATABASE;


The same result - no exception, and the result after changing POSITION number:


SQL> CREATE DATABASE 'c:\temp\tmp.fdb';
SQL>
SQL> CREATE EXCEPTION E 'Test';
SQL> CREATE TABLE TAB(I INTEGER);
SQL>
SQL> SET TERM ^;
SQL>
SQL> CREATE OR ALTER TRIGGER T1_1
CON>   FOR TAB
CON>   INACTIVE BEFORE INSERT POSITION 10
CON> AS
CON> BEGIN
CON>   EXCEPTION E;
CON> END^
SQL>
SQL> CREATE OR ALTER TRIGGER T1_2
CON>   FOR TAB
CON>   ACTIVE BEFORE INSERT POSITION 20
CON> AS
CON> BEGIN
CON>   EXCEPTION E;
CON> END^
SQL>
SQL> SET TERM ;^
SQL>
SQL> INSERT INTO TAB VALUES(123);
Statement failed, SQLSTATE = 42000
exception 1
-E
-Test
-At trigger 'T1_2' line: 6, col: 3
SQL>
SQL> DROP DATABASE;

Another example, both triggers active, but none of them fire :(


CREATE DATABASE 'c:\temp\tmp.fdb';

CREATE EXCEPTION E 'Test';
CREATE TABLE TAB(I INTEGER);

SET TERM ^;

CREATE OR ALTER TRIGGER T1_1
  FOR TAB
  ACTIVE BEFORE INSERT POSITION 20
AS
BEGIN
  EXCEPTION E;
END^

CREATE OR ALTER TRIGGER T1_2
  FOR TAB
  ACTIVE AFTER DELETE POSITION 20
AS
BEGIN
  EXCEPTION E;
END^

SET TERM ;^

INSERT INTO TAB VALUES(123);

DROP DATABASE;

At 08:32 PM 4/03/2010, Vlad Khorsun wrote:
>> I thought that the SQL standard specified that literals like 0.0 and 1440.0 
>> should be considered as doubles, not as decimal or numeric. In that case 
>> the current implementation is incorrect. Unfortunately I currently don't have 
>> the standard at hand to look it up.
>
>5.3 <literal>
>
><literal> ::=
>    <signed numeric literal>
>    | <general literal>
>...
><signed numeric literal> ::=
>    [ <sign> ] <unsigned numeric literal>
>
><unsigned numeric literal> ::=
>    <exact numeric literal>
>    | <approximate numeric literal>
>
><exact numeric literal> ::=
>    <unsigned integer> [ <period> [ <unsigned integer> ] ]
>    | <period> <unsigned integer>
>
><sign> ::= <plus sign> | <minus sign>
>
><approximate numeric literal> ::= <mantissa> E <exponent>
>
>
>    So, Firebird implementation is correct

OK, I'm convinced! ;-) 

1.00 /1440.0 = 0.00[0694444.......]
then
00.00 * 1440.0 = 0.000

Helen

From: Leyne, Sean 
Sent: 03 March 2010 23:28
To: For discussion among Firebird Developers
All,
 
Early today Igor Lobov posted a case to the Tracker, regarding the results
he was seeing for the following math operation in a dialect 3 database:
 
       select 1.0 / 1440.0 * 1440.0 from rdb$database
 
Firebird returns a result of 0.

Sean,

Isn't the problem here that the precendence or order of operation is
incorrect.  I was always taught to multiply before divide to avoid losing
accuracy at a fixed precision.  If you are to round(0), I had assumed that
is how all expression evaluators worked. i.e.

Multiply
Divide
Add
Subtract

Just my 2c.

Jason Chapman

04.03.2010 10:55, Vlad Khorsun wrote:
>      d) The precision and scale of the result of division are implementation-defined.
>
>      So, Firebird not violates standard and we have space for maneuver

   May be we could consider result of division to be always double 
precision to stop newbies' moaning... Because result of expression is 
casted to destination type, it shouldn't change much.

-- 
   SY, SD.

For what it's worth:

Oracle (dare I speak its name!) returns 1 for all of the following:

select 1.0 / 1440.0 * 1440.0 from dual;
select 1 / 1440.0 * 1440.0 from dual;
select 1 / 1440 * 1440 from dual;
select to_number(1.0) / to_number(1440.0) * to_number(1440.0) from dual;

But, with a slight bit of fiddling, I can get it to return
0.9999999999999999999999999999999999999994.



Cheers,
Norman.

Norman Dunbar
Contract Oracle DBA
CIS Engineering Services
Internal : 7 28 2051
External : 0113 231 2051 


Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.   We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation.  Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.  If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506.  Find out more about the Environment Agency at http://www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation.  Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506.  Find out more about the Environment Agency at http://www.environment-agency.gov.uk

> Isn't the problem here that the precendence or order of operation is
> incorrect.  I was always taught to multiply before divide to avoid losing
> accuracy at a fixed precision.  If you are to round(0), I had assumed that
> is how all expression evaluators worked. i.e.
> 
> Multiply
> Divide
> Add
> Subtract

Multiply and divide have the same priority and are therefor evaluated left to right if you don't apply parentheses.

Mark
-- 
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

Have seen this kind of topic serveral times here, but i agree the result is not what you expect by first look. Although result may be correct by standard SQL?

Vlad, does the standard say something about using the precision/scale already in parts of the formula?
Cause (1.0 / 1440.0 * 1440.0) could use internally high scale/precision for part (1.0 / 1440.0), but correct scale/precision for output?


Sean, use meanwhile:

select 1E0 / 1440.0 * 1440.0 from rdb$database    ;-)


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database with many powerful SQL features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  firebird-support@...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

> <Paraphrasing>
> Precision is the number of digits in a number. Scale is the
> number of digits to the right of the decimal point in a
> number. So where Expression e1, with precision p1 and scale
> s1, and Expression e2, with precision p2 and scale s2. The
> scale of the result shall be as follows:
>  
> Operation,       Result scale
> Addition,        max(s1, s2)
> Subtraction,     max(s1, s2)
> Multiplication,  s1 + s2
> Division,        max(6, s1 + p2 + 1)
> </Paraphrasing>
>  
> (the original link is
> http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx)
>

This is certainly interesting but as others have already noted
the arbitrary nature of values "6" and " + 1" are disturbing.

I would like to make a few observations...


  . I have largely skipped numeric and decimal data types
    in applications because their nature is much less well
    defined or understood - the SQL standard is much less
    specific in this regard than it needs to be to be useful.
    Any time I considered the advantages of numeric they were
    quickly cancelled by the fact that the answers could change
    when the implementation changed the way it decided to
    interpret the standard (as we are discussing right now).


  . Few standards are very clear about the distinction between
    a statement like:  result = a / b;
              versus:  result = x / y * z;
    Most of us have grown used to software  that automatically
    promote types to avoid data loss - where data type is not
    otherwise explicit and this should normally apply to
    interim results as in the second example.  Firebird tries
    this but the division implementation choices gives odd
    results (data loss) as in the original post.


  . Those that don't understand numeric/decimal quickly get
    lost when calculations raise exceptions because the
    particular implementation choices of Firebird hit the
    limits quite quickly.  Even when you understand what FB
    will do, the way that Firebird hits these limits so quickly
    means that you are sometimes forced to inappropriate
    rounding (before it is really appropriate).


  . In recent times I have been involved with an application
    where we implemented support for true Decimal data types.
    http://speleotrove.com/decimal/
    We store it as ASCII text and I developed a collation
    to sort and compare such fields appropriately regardless
    of scale etc. (We implemented just the 34 digit variation,
    although the collation can cope with even very very long
    variations (thousands of digits, scale to +/-9999).)

    The beauty of Decimal Arithmetic is that the users
    understand it!  It is what we were taught at school.

    Another beauty of this data type is it a well documented
    and accepted standard - it even has support already in
    many languages.  I have been considering implementing
    support in Delphi using more recent language features so
    that I can easily use it with my Delphi apps too.

    My experience with this makes me wonder if Firebird could
    benefit from the Decimal type in two ways:
      1. Binary support larger number types (precision of 34
         or even larger)
      2. Provide better and more intuitive handling of
         numeric calculations (allow interim values to
         go to much larger precisions and scales before
         cast back to the desired result).

    There is C code there to be used, and a large test library
    to help you get it right in the implementation.  It is very
    slow compared to hardware based binary floating point
    (double types) but I don't have any idea how it compares
    to Firebird's numeric implementation (accuracy and
    reliability were more important to me than speed).

    Some study may need to be made concerning the differences
    between the SQL standard expectation of scaled integers
    versus the IEEE 754 standard Decimal type... but if it is
    found to be compatible (or can be made compatible) then
    I would recommend it.  I see this much like the encryption
    situation - much better to rely on experts than to write
    code based on vague memories of high school mathematics.


-- 
Geoff Worboys
Telesis Computing

Sean Leyne wrote:
>> I would to suggest the current scale rules are not correct, because the current result is 
>> not logical.

Err, Sean, we're talking SQL standard here, not logic ...
> 
Vlad Khorsun wrote:
> 
>     I want to cite SQL standard, just to make things more clear :
> 
> 6.26 <numeric value expression>
> ...
> 
> 1) If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the
> declared type of the result is an implementation-defined exact numeric type, with precision and
> scale determined as follows:
> 
>     a) Let S1 and S2 be the scale of the first and second operands respectively.
>
>     d) The precision and scale of the result of division are implementation-defined.
 >
>     So, Firebird not violates standard and we have space for maneuver

I agree, but suggest that we use the maneuvering space to provide more
accurate results.  There are two issues here.  One is the result type.
For that, I'd suggest that the precision of the result be 18 and that
the scale be the max of S1 and S2.

So 1.0 / 144.0 would be 0.0.

The question is the internal type used for intermediate results.  For
reasons I have never understood, Borland's engineers decided that the
internal representation had to be the same as the external, leading to
absurd truncations.   Dialect 1 used double precision floating point
for internal results of division.  Why not go back to that.

So 1.0 / 144.0 * 144.0 would be 1.00


Regards,

Ann

04.03.2010 17:08, Ann W. Harrison wrote:
> The question is the internal type used for intermediate results.  For
> reasons I have never understood, Borland's engineers decided that the
> internal representation had to be the same as the external, leading to
> absurd truncations.   Dialect 1 used double precision floating point
> for internal results of division.  Why not go back to that.
>
> So 1.0 / 144.0 * 144.0 would be 1.00

   Perhaps, because it will result to 2/3 = 1.

-- 
   SY, SD.

Dimitry Sibiryakov wrote:
>>
>> So 1.0 / 144.0 * 144.0 would be 1.00
> 
>    Perhaps, because it will result to 2/3 = 1.
>

OK, then make the scale the sum of S1 and S2

and really, is it better to have 2/3 = 0 ?


Cheers,

Ann

Ann,

> The question is the internal type used for intermediate results.  For
> reasons I have never understood, Borland's engineers decided that the
> internal representation had to be the same as the external, leading to
> absurd truncations.   Dialect 1 used double precision floating point
> for internal results of division.  Why not go back to that.
> 
> So 1.0 / 144.0 * 144.0 would be 1.00

I would support this approach, the most accurate intermediate/internal representation possible with the result being cast to the final precision/scale.

Adriano dos Santos Fernandes wrote:
>   Hi!
> 
> Does anyone already tried to build FB with VS 2010 Beta?

Yes, some time ago, not with the actual rc, but with no real success.
But, I'm not really good in VS, so I believe your chances are much better.


mit freundlichen Grüßen
Frank Schlottmann-Gödde

-- 
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
  their tears have healing powers and they make highly faithful pets."
      - J.K. Rowling

>>> The only way we have to prevent this kind of situations is to have a way
>>> to lock the file while the Firebird
>>>  embedded is using it.
>>
>>   This is not a way. Some software can copy files regardless of it's
>> lock mode.
>>   The only way is to charge the customer for repairing broken database.
>> Money lost is the best teacher
>>
> Dimitry, the customers are not evil people trying to corrupt databases, 
> and
> whom should be taught lessons. It would be very good policy to protect the
> user from bad luck. And loosing a data base must be considered bad luck :)
>
> Poul

...but that said, and as far as I remember, it has always been invalid to do 
a file copy on a running database. In our user programs we expose a function 
for doing a backup or restore (either per API or by launching GBAK) so the 
customer hopefully never gets the idea to do a file copy.

Poul

On Thursday 04 March 2010 16:15:41 Poul Dige wrote:
> >>> The only way we have to prevent this kind of situations is to have a
> >>> way to lock the file while the Firebird
> >>>  embedded is using it.
> >>
> >>   This is not a way. Some software can copy files regardless of it's
> >> lock mode.
> >>   The only way is to charge the customer for repairing broken database.
> >> Money lost is the best teacher
> >
> > Dimitry, the customers are not evil people trying to corrupt databases,
> > and
> > whom should be taught lessons. It would be very good policy to protect
> > the user from bad luck. And loosing a data base must be considered bad
> > luck :)
> >
> > Poul
>
> ...but that said, and as far as I remember, it has always been invalid to
> do a file copy on a running database. In our user programs we expose a
> function for doing a backup or restore (either per API or by launching
> GBAK) so the customer hopefully never gets the idea to do a file copy.

Moreover, currently we have standard tool to perform file copy - nbackup.