Repository: duckdb/duckdb (language: python)

Issue:
`TO_JSON` results in weird number translation
### What happens?

`TO_JSON` translates `9007199254740993` into `9007199254740992.0`.

### To Reproduce

```sql
-- this translation creates a strange number
WITH t1 AS ( (
            SELECT 9007199254740993 AS id  )
        UNION ALL (
            SELECT 2.1 AS id  ) )
SELECT To_Json( t ) AS json_objects
FROM t1 AS t;
```
```text
┌───────────────────────────┐
│ JSON_OBJECTS              │
├───────────────────────────┤
│ {"ID":9007199254740992.0} │
├───────────────────────────┤
│ {"ID":2.1}                │
└───────────────────────────┘
```
```sql
-- although this works
(SELECT 9007199254740993 AS ID)UNION ALL(SELECT 2.1 AS ID);
```
```text
┌──────────────────────────┐
│ ID                       │
├──────────────────────────┤
│ 9,007,199,254,740,993.00 │
├──────────────────────────┤
│                     2.10 │
└──────────────────────────┘
```

### OS:

Linux

### DuckDB Version:

1.1.3

### DuckDB Client:

Java

### Hardware:

_No response_

### Full Name:

Andreas Reichel

### Affiliation:

Reichel

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
Thanks!
Hi @manticore-projects, this is expected (but unfortunate) behavior.

Your CTE:
```sql
D SELECT 9007199254740993 AS id UNION ALL SELECT 1.2 AS id;
┌────────────────────┐
│         id         │
│   decimal(20,1)    │
├────────────────────┤
│ 9007199254740993.0 │
│                1.2 │
└────────────────────┘
```
Results in type `DECIMAL(20,1)`.

JSON only supports integers, strings, floats, and booleans (and nested types object/array). So, to convert this to JSON, we are forced to choose a float. However, floats do not have enough precision to store your large number:
```sql
D WITH cte AS (SELECT 9007199254740993 AS id UNION ALL SELECT 1.2 AS id) SELECT id::DOUBLE FROM cte;
┌────────────────────┐
│ CAST(id AS DOUBLE) │
│       double       │
├────────────────────┤
│ 9007199254740992.0 │
│                1.2 │
└────────────────────┘
```
The cast to `DOUBLE` loses some precision, which is the cast that is used when you do `to_json`.
Thank you very much for the explanation @lnkuiper. I am able to understand what is happening.
Although: would it not be better to quote the Number when exceeding the Float? As it is right now it may crash your rocket.

Fix patch:
diff --git a/extension/json/json_functions/json_create.cpp b/extension/json/json_functions/json_create.cpp
index 3927daa1b87a..8c587fd0eccd 100644
--- a/extension/json/json_functions/json_create.cpp
+++ b/extension/json/json_functions/json_create.cpp
@@ -40,25 +40,36 @@ static LogicalType GetJSONType(StructNames &const_struct_names, const LogicalTyp
 	// These types can go directly into JSON
 	case LogicalTypeId::SQLNULL:
 	case LogicalTypeId::BOOLEAN:
-	case LogicalTypeId::BIGINT:
-	case LogicalTypeId::UBIGINT:
-	case LogicalTypeId::DOUBLE:
-		return type;
-	// We cast these types to a type that can go into JSON
 	case LogicalTypeId::TINYINT:
 	case LogicalTypeId::SMALLINT:
 	case LogicalTypeId::INTEGER:
-		return LogicalType::BIGINT;
+	case LogicalTypeId::BIGINT:
+	case LogicalTypeId::HUGEINT:
+	case LogicalTypeId::UHUGEINT:
 	case LogicalTypeId::UTINYINT:
 	case LogicalTypeId::USMALLINT:
 	case LogicalTypeId::UINTEGER:
-		return LogicalType::UBIGINT;
+	case LogicalTypeId::UBIGINT:
 	case LogicalTypeId::FLOAT:
+	case LogicalTypeId::DOUBLE:
+	case LogicalTypeId::BIT:
+	case LogicalTypeId::BLOB:
+	case LogicalTypeId::VARCHAR:
+	case LogicalTypeId::AGGREGATE_STATE:
+	case LogicalTypeId::ENUM:
+	case LogicalTypeId::DATE:
+	case LogicalTypeId::INTERVAL:
+	case LogicalTypeId::TIME:
+	case LogicalTypeId::TIME_TZ:
+	case LogicalTypeId::TIMESTAMP:
+	case LogicalTypeId::TIMESTAMP_TZ:
+	case LogicalTypeId::TIMESTAMP_NS:
+	case LogicalTypeId::TIMESTAMP_MS:
+	case LogicalTypeId::TIMESTAMP_SEC:
+	case LogicalTypeId::UUID:
+	case LogicalTypeId::VARINT:
 	case LogicalTypeId::DECIMAL:
-	case LogicalTypeId::UHUGEINT:
-	case LogicalTypeId::HUGEINT:
-		return LogicalType::DOUBLE;
-	// The nested types need to conform as well
+		return type;
 	case LogicalTypeId::LIST:
 		return LogicalType::LIST(GetJSONType(const_struct_names, ListType::GetChildType(type)));
 	case LogicalTypeId::ARRAY:
@@ -211,7 +222,7 @@ template <>
 struct CreateJSONValue<hugeint_t, string_t> {
 	static inline yyjson_mut_val *Operation(yyjson_mut_doc *doc, const hugeint_t &input) {
 		const auto input_string = input.ToString();
-		return yyjson_mut_strncpy(doc, input_string.c_str(), input_string.length());
+		return yyjson_mut_rawncpy(doc, input_string.c_str(), input_string.length());
 	}
 };
 
@@ -219,7 +230,7 @@ template <>
 struct CreateJSONValue<uhugeint_t, string_t> {
 	static inline yyjson_mut_val *Operation(yyjson_mut_doc *doc, const uhugeint_t &input) {
 		const auto input_string = input.ToString();
-		return yyjson_mut_strncpy(doc, input_string.c_str(), input_string.length());
+		return yyjson_mut_rawncpy(doc, input_string.c_str(), input_string.length());
 	}
 };
 
@@ -287,6 +298,22 @@ static void TemplatedCreateValues(yyjson_mut_doc *doc, yyjson_mut_val *vals[], V
 	}
 }
 
+static void CreateRawValues(yyjson_mut_doc *doc, yyjson_mut_val *vals[], Vector &value_v, idx_t count) {
+	UnifiedVectorFormat value_data;
+	value_v.ToUnifiedFormat(count, value_data);
+	auto values = UnifiedVectorFormat::GetData<string_t>(value_data);
+	for (idx_t i = 0; i < count; i++) {
+		idx_t val_idx = value_data.sel->get_index(i);
+		if (!value_data.validity.RowIsValid(val_idx)) {
+			vals[i] = yyjson_mut_null(doc);
+		} else {
+			const auto &str = values[val_idx];
+			vals[i] = yyjson_mut_rawncpy(doc, str.GetData(), str.GetSize());
+		}
+		D_ASSERT(vals[i] != nullptr);
+	}
+}
+
 static void CreateValuesStruct(const StructNames &names, yyjson_mut_doc *doc, yyjson_mut_val *vals[], Vector &value_v,
                                idx_t count) {
 	// Structs become values, therefore we initialize vals to JSON values
@@ -476,7 +503,8 @@ static void CreateValuesArray(const StructNames &names, yyjson_mut_doc *doc, yyj
 
 static void CreateValues(const StructNames &names, yyjson_mut_doc *doc, yyjson_mut_val *vals[], Vector &value_v,
                          idx_t count) {
-	switch (value_v.GetType().id()) {
+	const auto &type = value_v.GetType();
+	switch (type.id()) {
 	case LogicalTypeId::SQLNULL:
 		CreateValuesNull(doc, vals, count);
 		break;
@@ -550,17 +578,28 @@ static void CreateValues(const StructNames &names, yyjson_mut_doc *doc, yyjson_m
 	case LogicalTypeId::TIMESTAMP_NS:
 	case LogicalTypeId::TIMESTAMP_MS:
 	case LogicalTypeId::TIMESTAMP_SEC:
-	case LogicalTypeId::VARINT:
 	case LogicalTypeId::UUID: {
 		Vector string_vector(LogicalTypeId::VARCHAR, count);
 		VectorOperations::DefaultCast(value_v, string_vector, count);
 		TemplatedCreateValues<string_t, string_t>(doc, vals, string_vector, count);
 		break;
 	}
+	case LogicalTypeId::VARINT: {
+		Vector string_vector(LogicalTypeId::VARCHAR, count);
+		VectorOperations::DefaultCast(value_v, string_vector, count);
+		CreateRawValues(doc, vals, string_vector, count);
+		break;
+	}
 	case LogicalTypeId::DECIMAL: {
-		Vector double_vector(LogicalType::DOUBLE, count);
-		VectorOperations::DefaultCast(value_v, double_vector, count);
-		TemplatedCreateValues<double, double>(doc, vals, double_vector, count);
+		if (DecimalType::GetWidth(type) > 15) {
+			Vector string_vector(LogicalTypeId::VARCHAR, count);
+			VectorOperations::DefaultCast(value_v, string_vector, count);
+			CreateRawValues(doc, vals, string_vector, count);
+		} else {
+			Vector double_vector(LogicalType::DOUBLE, count);
+			VectorOperations::DefaultCast(value_v, double_vector, count);
+			TemplatedCreateValues<double, double>(doc, vals, double_vector, count);
+		}
 		break;
 	}
 	case LogicalTypeId::INVALID:
@@ -604,7 +643,6 @@ static void ObjectFunction(DataChunk &args, ExpressionState &state, Vector &resu
 	for (idx_t i = 0; i < count; i++) {
 		objects[i] = JSONCommon::WriteVal<yyjson_mut_val>(objs[i], alc);
 	}
-
 	if (args.AllConstant()) {
 		result.SetVectorType(VectorType::CONSTANT_VECTOR);
 	}
@@ -637,7 +675,6 @@ static void ArrayFunction(DataChunk &args, ExpressionState &state, Vector &resul
 	for (idx_t i = 0; i < count; i++) {
 		objects[i] = JSONCommon::WriteVal<yyjson_mut_val>(arrs[i], alc);
 	}
-
 	if (args.AllConstant()) {
 		result.SetVectorType(VectorType::CONSTANT_VECTOR);
 	}
@@ -651,22 +688,9 @@ static void ToJSONFunctionInternal(const StructNames &names, Vector &input, cons
 	CreateValues(names, doc, vals, input, count);
 
 	// Write JSON values to string
-	auto objects = FlatVector::GetData<string_t>(result);
-	auto &result_validity = FlatVector::Validity(result);
-	UnifiedVectorFormat input_data;
-	input.ToUnifiedFormat(count, input_data);
-	for (idx_t i = 0; i < count; i++) {
-		idx_t idx = input_data.sel->get_index(i);
-		if (input_data.validity.RowIsValid(idx)) {
-			objects[i] = JSONCommon::WriteVal<yyjson_mut_val>(vals[i], alc);
-		} else {
-			result_validity.SetInvalid(i);
-		}
-	}
-
-	if (input.GetVectorType() == VectorType::CONSTANT_VECTOR || count == 1) {
-		result.SetVectorType(VectorType::CONSTANT_VECTOR);
-	}
+	UnaryExecutor::ExecuteWithNulls<data_t, string_t>(input, result, count, [&](data_t, ValidityMask &, idx_t index) {
+		return JSONCommon::WriteVal<yyjson_mut_val>(vals[index], alc);
+	});
 }
 
 static void ToJSONFunction(DataChunk &args, ExpressionState &state, Vector &result) {
diff --git a/extension/json/json_functions/json_structure.cpp b/extension/json/json_functions/json_structure.cpp
index 7982003fc134..51652bae43bf 100644
--- a/extension/json/json_functions/json_structure.cpp
+++ b/extension/json/json_functions/json_structure.cpp
@@ -1,12 +1,11 @@
 #include "json_structure.hpp"
 
 #include "duckdb/common/enum_util.hpp"
+#include "duckdb/common/extra_type_info.hpp"
 #include "json_executors.hpp"
 #include "json_scan.hpp"
 #include "json_transform.hpp"
 
-#include <duckdb/common/extra_type_info.hpp>
-
 namespace duckdb {
 
 static bool IsNumeric(LogicalTypeId type) {


Test patch:
diff --git a/test/sql/json/issues/issue15038.test b/test/sql/json/issues/issue15038.test
new file mode 100644
index 000000000000..6275406c1cd5
--- /dev/null
+++ b/test/sql/json/issues/issue15038.test
@@ -0,0 +1,71 @@
+# name: test/sql/json/issues/issue15038.test
+# description: Test issue 15038 - TO_JSON results in weird number translation
+# group: [issues]
+
+require json
+
+# we support full precision in JSON - yyjson supports RAW values
+query I
+SELECT to_json(1::HUGEINT << 100)
+----
+1267650600228229401496703205376
+
+query I
+SELECT (1::HUGEINT << 100)::JSON
+----
+1267650600228229401496703205376
+
+query I
+SELECT to_json(1::UHUGEINT << 100)
+----
+1267650600228229401496703205376
+
+query I
+SELECT (1::UHUGEINT << 100)::JSON
+----
+1267650600228229401496703205376
+
+query I
+SELECT to_json((1::UHUGEINT << 100)::DECIMAL(38,0))
+----
+1267650600228229401496703205376
+
+query I
+SELECT (1::UHUGEINT << 100)::DECIMAL(38,0)::JSON
+----
+1267650600228229401496703205376
+
+query I
+SELECT to_json((1::HUGEINT << 100)::VARINT)
+----
+1267650600228229401496703205376
+
+query I
+SELECT (1::HUGEINT << 100)::VARINT::JSON
+----
+1267650600228229401496703205376
+
+# original issue (#15038)
+query I
+WITH t1 AS (
+    SELECT 9007199254740993 AS id
+    UNION ALL
+    SELECT 1.2 AS id
+)
+SELECT to_json(id) AS json_objects
+FROM t1 AS t;
+----
+9007199254740993.0
+1.2
+
+query I
+WITH t1 AS (
+    SELECT 9007199254740993 AS id
+    UNION ALL
+    SELECT 1.2 AS id
+)
+SELECT id::JSON AS json_objects
+FROM t1 AS t;
+----
+9007199254740993.0
+1.2