Repository: duckdb/duckdb (language: python)

Issue:
Inconsistent Rounding Behavior When Casting String to DECIMAL
### What happens?

When casting a string to DECIMAL with various precisions and scales, DuckDB exhibits inconsistent rounding behavior. Specifically, casting the string `'1.0029999999999999'` to DECIMAL types with different precisions and scales sometimes results in unexpected rounding, e.g., `1.0031` instead of the anticipated `1.0030`.
In contrast, PostgreSQL consistently produces expected `1.0030`.

I've tested on two duckdb environments, both produce the same results:
1. execute duckdb raw sql in Python environment, with both stable version `0.10.1` and latest build `0.10.2-dev311`;
2. execute duckdb raw sql in duckdb-wasm at https://shell.duckdb.org/

### To Reproduce

Execute the following queries in DuckDB:

```sql
SELECT '1.0029999999999999'::DECIMAL(38, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD
SELECT '1.0029999999999999'::DECIMAL(10, 4);  -- Expected: 1.0030,  Actual: 1.0031,  BAD
SELECT '1.0029999999999999'::DECIMAL( 5, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD
SELECT '1.0029999999999999'::DECIMAL(19, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD
SELECT '1.0029999999999999'::DECIMAL(18, 4);  -- Expected: 1.0030,  Actual: 1.0031,  BAD
SELECT '1.002999999999999' ::DECIMAL(10, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD
SELECT '1.0029999999999999'::DECIMAL(10, 5);  -- Expected: 1.00300, Actual: 1.00301, BAD
SELECT '1.00299999999999'  ::DECIMAL(10, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD
```

### OS:

Ubuntu 22.04.4 LTS on Windows 10 x86_64 (WSL2)

### DuckDB Version:

0.10.1

### DuckDB Client:

Python

### Full Name:

Junjie Wang

### Affiliation:

dsm-firmenich

### Have you tried this on the latest [nightly build](https://duckdb.org/docs/installation/?version=main)?

I have tested with a nightly build

### Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

- [X] Yes, I have


Discussion / hints:
**What happens?**
A similar problem:
Assuming that the data type of a column of a table is decimal(x, y), when a number that exceeds the decimal accuracy (that is, the number of decimal places is greater than y) is inserted, the excess part of the decimal will be directly ignored instead of rounding (the implementation of mysql).
When just cast this number into decimal(x, y), the same problem happens.

**To Reproduce**
```sql
CREATE TABLE IF NOT EXISTS t (d decimal(14, 4));
INSERT INTO t VALUES (1234567890.12345);
SELECT * FROM t;
SELECT CAST(1234567890.1235 AS DECIMAL(13, 3));
```

**Expected results**
1234567890.1235
1234567890.124

**Screenshots**
<img width="356" alt="image" src="https://github.com/duckdb/duckdb/assets/42789438/681365e7-753a-4944-9854-0feeffd4bc2b">

**DuckDB Version:**
v0.10.2
This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.
This is still happening in v1 as well. Anyone working on a fix for this?
@microdou This appears to be working on `HEAD`:

D SELECT '1.0029999999999999'::DECIMAL(38, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD

| CAST('1.0029999999999999' AS DECIMAL(38,4)) |
|--------------------------------------------:|
| 1.0030                                      |

D SELECT '1.0029999999999999'::DECIMAL(10, 4);  -- Expected: 1.0030,  Actual: 1.0031,  BAD

| CAST('1.0029999999999999' AS DECIMAL(10,4)) |
|--------------------------------------------:|
| 1.0030                                      |

D SELECT '1.0029999999999999'::DECIMAL( 5, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD
| CAST('1.0029999999999999' AS DECIMAL(5,4)) |
|-------------------------------------------:|
| 1.0030                                     |

D SELECT '1.0029999999999999'::DECIMAL(19, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD

| CAST('1.0029999999999999' AS DECIMAL(19,4)) |
|--------------------------------------------:|
| 1.0030                                      |

D SELECT '1.0029999999999999'::DECIMAL(18, 4);  -- Expected: 1.0030,  Actual: 1.0031,  BAD

| CAST('1.0029999999999999' AS DECIMAL(18,4)) |
|--------------------------------------------:|
| 1.0030                                      |

D SELECT '1.002999999999999' ::DECIMAL(10, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD

| CAST('1.002999999999999' AS DECIMAL(10,4)) |
|-------------------------------------------:|
| 1.0030                                     |

D SELECT '1.0029999999999999'::DECIMAL(10, 5);  -- Expected: 1.00300, Actual: 1.00301, BAD

| CAST('1.0029999999999999' AS DECIMAL(10,5)) |
|--------------------------------------------:|
| 1.00300                                     |

D SELECT '1.00299999999999'  ::DECIMAL(10, 4);  -- Expected: 1.0030,  Actual: 1.0030,  GOOD

| CAST('1.00299999999999' AS DECIMAL(10,4)) |
|------------------------------------------:|
| 1.0030                                    |

Likewise, @DDDDDstar it looks like the `INSERT ` is working now:
```sql
D CREATE OR REPLACE TABLE t (d decimal(14, 4));
D INSERT INTO t VALUES (1234567890.12345);
D SELECT * FROM t;
```

|        d        |
|----------------:|
| 1234567890.1234 |

But the last cast is not:

```sql
SELECT CAST(1234567890.1235 AS DECIMAL (13, 3));
```

| CAST(1234567890.1235 AS DECIMAL(13,3)) |
|---------------------------------------:|
| 1234567890.123                         |


Fix patch:
diff --git a/src/function/cast/decimal_cast.cpp b/src/function/cast/decimal_cast.cpp
index 7f9f2b493518..21f80bf0475a 100644
--- a/src/function/cast/decimal_cast.cpp
+++ b/src/function/cast/decimal_cast.cpp
@@ -155,7 +155,7 @@ struct DecimalScaleDownCheckOperator {
 			                                data->result.GetType().ToString());
 			return HandleVectorCastError::Operation<RESULT_TYPE>(std::move(error), mask, idx, data->vector_cast_data);
 		}
-		return Cast::Operation<INPUT_TYPE, RESULT_TYPE>(input / data->factor);
+		return DecimalScaleDownOperator::Operation<INPUT_TYPE, RESULT_TYPE>(input, mask, idx, dataptr);
 	}
 };
 


Test patch:
diff --git a/test/sql/types/decimal/test_decimal.test b/test/sql/types/decimal/test_decimal.test
index 8b1aa3ed7f2c..4b13a3af4039 100644
--- a/test/sql/types/decimal/test_decimal.test
+++ b/test/sql/types/decimal/test_decimal.test
@@ -128,6 +128,11 @@ select CAST(-33.846 AS DECIMAL(5,0)) d0;
 ----
 -34
 
+query I
+SELECT CAST(1234567890.1235 AS DECIMAL (13, 3));
+----
+1234567890.124
+
 # various error conditions
 # scale must be bigger than or equal to width
 statement error
@@ -161,4 +166,4 @@ SELECT '0.1'::DECIMAL(1, 2, 3);
 statement error
 SELECT '1'::INTEGER(1000);
 ----
-<REGEX>:Parser Error.*syntax error.*
\ No newline at end of file
+<REGEX>:Parser Error.*syntax error.*