Hi,

I am trying to understand how SQL Anywhere decides on the scale when evaluating an expression.

Take this for example:

select 5.922733 * 30/365 from dummy

Returns => 0.4867999726 (dbisql)

It looks like it's using 10 decimal places. If I used Windows (Standard) calc the above expression returns 0.4867999726027397.

If I do this however

select 30/365 from dummy

Returns => 0 (dbisql)

Windows calc returns 0.0821917808219178 for 30/365.

My 2 questions are.

  1. Does SQL Anywhere use 10 decimal places by default?
  2. Why does it return 0 for: select 30/365 from dummy

Thanks,

Shane

asked 30 Sep '13, 12:37

Shane%20McEneaney's gravatar image

Shane McEneaney
66339
accept rate: 0%

I believe the answer to #2 is that both of your numbers are assumed to be integers and therefore it is also assumed you are doing integer arithmetic. Try 30.0/365 instead.

(30 Sep '13, 13:19) John Smirnios

The scale that is used by SQLAnywhere for a calculation is dependent on the inputs to the calculations.

For you examples:

  • 30/365 : here both inputs are integers to the calculation is done using integral arithmetic hence the resultant value of 0.
  • 5.922733 * 30/365 : this one is a bit more complicated to explain so lets go through the process:
    • first you need to realize that the computation uses the normal order-of-operation rules, so this implies that 5.922733 * 30 is computed first.
    • The result of computing 5.922733 * 30 is a numeric( 9, 6 ) - i.e. scale of 6 - since the inputs have a total scale of 6 ( i.e. 6 plus 0 )
    • SQLA will then do the division by 365 increasing the scale by a reasonable amount (*) to keep the precision of the result. In this case the result is a numeric( 13, 10 )

HTH to explain. You can use the exprtype() function if you want to determine what the resultant type of a calculation would be.

(*) The resultant scale is a simple computation left as an exercise for the reader - I didn't bother to look it up!

permanent link

answered 30 Sep '13, 13:26

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

edited 30 Sep '13, 13:27

Many thanks for the response.

I am still unclear about the scale used in the division part of the expression. I understand from Mark's post that 5.922733 * 30 is done first and the scale is 6. That makes sense.

select 5.922733 * 30/365 from dummy

I don't understand why a scale of 10 is chosen for the '/365' part. Mark mentioned that "SQLA will then do the division by 365 increasing the scale by a reasonable amount". How does it decide what is reasonable? The standard Windows calculator has a scale of 16 by default but can be extended using the Scientific option.

Thanks again,

Shane

(01 Oct '13, 06:51) Shane McEneaney
Replies hidden
Comment Text Removed

Windows calculator is simply doing all of its calculations using doubles and then blindly displays all of the digits regardless of whether they are significant or not. SQLA only displays the significant digits. For example 1.1 / 365 will give five significant digits (1.1 has 1 and the reciprocal of 365 has 4 giving a total of 5); displaying any more is just misleading.

(01 Oct '13, 08:08) Mark Culp
1

@Mark: Can you adjust the link on significant digits, please? It does not seem to work.

(01 Oct '13, 14:44) Volker Barth

Done. Thanks for pointing it out.

(02 Oct '13, 03:58) Mark Culp

FWIW, here's a white paper from Ivan T. Bowman on the subject - based on ASA 9 but I guess it should still apply (and I guess the rules for comparisons and arithmetic operations are similar):

iAnywhere Solutions Technical Whitepaper: Mixed-Typed Comparisons In Adaptive Server Anywhere 9

Note, when one of the numeric operands is of a floating point type (float, real or double) - say, because you use a column of that type -, then the result is always of type double.

permanent link

answered 30 Sep '13, 15:26

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

Thanks for your response Volker. I had a look at the Whitepaper. It discusses comparison of different data types. I can't find any mention of scale when perfoming division.

(01 Oct '13, 06:52) Shane McEneaney
Replies hidden

Yes, as stated, the paper doesn't discuss arithmetic operations. However, it does discuss what types are used when using operands of different types, e.g. when using int and float, and I had thought that your question has had a broader scope. (And it would also tell that operations between int operands still return ints, not fractional values.)

But clearly Mark's explanations on the rules of fixed point arithmetics are more appropriate to your particular concern.

(01 Oct '13, 08:13) Volker Barth

Thanks again Volker for taking the time.

(01 Oct '13, 15:01) Shane McEneaney
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 30 Sep '13, 12:37

question was seen: 2,210 times

last updated: 02 Oct '13, 03:58