-1

Using Microsoft SQL Server 2019

SQL evaluates @number as 0 in the following query:

DECLARE
    @number int,
    @engine int = 49
DECLARE
    @quantity float = 1.0 / CAST(@engine AS FLOAT)

SELECT
        @quantity * @engine

SELECT
        @number = @quantity * @engine

SELECT
        @number

Results of Query :

enter image description here

I don't understand why this is. All numbers 1 - 48 are giving me the expected result of 1. The only other @engine input I've found that results in a 0 is 98. I've found a workaround so I don't need a solution for this issue right now, I just want to understand why only specific numbers do this.

I understand the issue with floating point numbers in computers. But it would make sense that this issue would happen with other larger numbers like 48 or 53 for example. The floating point issue does not explain why this happens in only certain situations that match this pattern.

This only happens when the @engine variable is a multiple of 7 in some way.

7 * 7 = 49

7 * 14 = 98

7 * 28 = 196 (also results in a 0 for this query)

7 * 56 = 392 (also 0)

7 / 7 = 1

14 / 7 = 2

28 / 7 = 4

56 / 7 = 8

This goes on and on. Multiply 7 by 16 and multiply that result again by 7 you'll get another number that results in 0 with this query. Every other number between gives me the expected result of 1.

ZerefSan
  • 11
  • 4
  • Are you sure your code is for mysql? In muysql you can only use declare in stored procedures and the declared variable names cannot be prefixed by @. – Shadow Aug 31 '23 at 15:22
  • 1
    integer math. Number is defined as int. So it can't be a decimal. so if the reuslt is 0.3814 it will round to 0. define number as decmial 12,4 and see what happens. – xQbert Aug 31 '23 at 15:22
  • 1
    Floating point calculations are never precise. So if you calc 1/49 * 49 you might get 0.99999999999999 and when you cast this to int it is 0. you need to round the result – Thallius Aug 31 '23 at 15:23
  • nod if precision is important don't use float or double, specify decimal (precision,scale) float/double work great when working on large scale or extremely small scale numbers; but are terrible to use when precision matters – xQbert Aug 31 '23 at 15:26
  • I edited the question to be more precise. Precision doesn't matter here. I only want to work in whole numbers. @xQbert – ZerefSan Aug 31 '23 at 15:36
  • Can you also add info about the DBMS you are using ? Because sql-server and MySQL are not liking the code you typed .... – Luuk Aug 31 '23 at 15:37
  • Yes @Luuk, done. Sorry for the mistake. – ZerefSan Aug 31 '23 at 15:42
  • @Quantity is `~0.0204081632653061` - and multiplying by 49 gives a float just under `1` - so when you assign it to an `int` it gets truncated - so nothing to do with integer division but to do with floating point precision https://dbfiddle.uk/D89Hq0-M – Martin Smith Aug 31 '23 at 15:44
  • Or TBH not even floating point precision as you would get the same with `decimal` too. In general in Maths you can't expect to have a number with an infinitely long representation after the decimal point and then truncate that and expect it to round trip to the original value - if you want to treat decimals like `0.9999` as `1` when you assign back to an integer then `ROUND` to the nearest integer first – Martin Smith Aug 31 '23 at 16:00
  • Switched your floats and integers to decimal: https://dbfiddle.uk/yoifMcGz works fine for me. The issue is the combination of floats which are not stored as whole numbers and are imprecise. and integer math truncating those vaules to a whole number you can't only work in whole nubmers, you're doing math specifically division which will restult in fractions. those fractions get truncated if you're using integer math. – xQbert Aug 31 '23 at 16:18
  • You can see here which numbers generate errors: https://dbfiddle.uk/tb7fz1w5 it's not just 49. etc. Basically, don't mix integers and floating numbers in sql server – siggemannen Aug 31 '23 at 16:20
  • This is fascinating @siggemannen. I wonder why it's only these numbers. – ZerefSan Aug 31 '23 at 16:31
  • @z3refsan it all has to do with how numbers are stored internally for each of the data types you've choosen to use; and the fact float can't represent many whole numbers accurately; and then when you assign a result to a integer data type which can only have whole numbers, it truncates the decimals... – xQbert Aug 31 '23 at 16:47

0 Answers0