1

I have a very peculiar situation happening while doing a simple division using GoogleSQL.

For example, variables involved:

   Fruit_name -- String
   price_purchased -- String
   used_discount -- double

Example values:

fruit_name price_purchased used_discount
apple 5 0.8
pear 6 0.76

A simple division through SQL to find out original price:

SELECT DISTINCT
  fruit_name,
  CAST(price_purchased AS double),
  used_discount,
  CAST(price_purchased AS double) / (1 - used_discount) as original_price
FROM
  fruit_table

I'm weirdly getting such a result:

fruit_name price_purchased used_discount original_price
apple 5 0.8 25.000000000000007
pear 6 0.76 25

The original price for the apple should also give me a value of 25 exactly, just like how the pear's value was also at 25.

How is it that the value of the apple's original price could have that weird minuscule amount? Is this due to some type casting issue?

Thanks.

lyk
  • 1,578
  • 5
  • 25
  • 49

1 Answers1

0

The division result is floating point value, you need to it to NUMERIC type:

SELECT
  CAST(5 AS FLOAT64) / (1 - 0.8) AS float_original_price,
  CAST(CAST(5 AS FLOAT64) / (1 - 0.8) AS NUMERIC) AS numeric_original_price
float_original_price numeric_original_price
25.000000000000007 25

You can refer to the docs for Bigquery Numeric Types. Also, check this post : What is the difference between NUMERIC and FLOAT in BigQuery?

blackbishop
  • 30,945
  • 11
  • 55
  • 76
  • Thanks @bloackbishop for the tip! But I'm still a little confused, why the DOUBLE data type get translated to FLOAT in this case? In typical mysql, casting it as double usually won't end up with such results. Why is it that type casting price_purchased as a NUMERIC right from the start then won't work as well? – lyk Feb 13 '21 at 14:27
  • @lyk which database are you using? Bigquery has no data type Double but Float – blackbishop Feb 13 '21 at 14:31
  • 1
    I'm using GoogleSQL on Dremel Engine, maybe I got a little bit confused back there but I will try to read up into this, thank you so much! – lyk Feb 13 '21 at 14:37