Problem

When using the round() function in Databricks SQL with floating point numbers, you notice the output does not adhere to the parameters. 

Example query

In the following query, you indicate a parameter of three decimal places, but you receive not-three in the output. 

```sql 
spark.sql("select round(float(0.9200000166893005), 3)").display() 
``` 

returns 0.9200000166893005 instead of 0.920. 

You notice when using decimal numbers the output aligns to the parameter of three decimal places. 

```sql 
spark.sql("select round(0.9200000166893005, 3)").display() 
``` 

returns 0.920.

Cause

Decimal numbers are represented and processed with exact precision. Floating point numbers are represented and processed in binary using the IEEE 754 standard. 

The IEEE 754 standard cannot accurately represent many decimal fractions, leading to small precision errors when converting between binary and decimal representations. 

In the example provided, float(0.9200000166893005) retains more precision than a standard floating point number, causing the round() function to behave unexpectedly.

Solution

Use string formatting in Python or cast the result to a DECIMAL type in SQL.

In Python, you can use string formatting like "{:.3f}".format(number) to display the number as 0.920, regardless of minor floating point inaccuracies.

In SQL, casting the result to a DECIMAL(p,s) type ensures the number is displayed with the desired precision. 

```sql
spark.sql("SELECT CAST(ROUND(float(0.9200000166893005), 3) AS DECIMAL(10, 3))").display()
```