Repository: duckdb/duckdb (language: python)

Issue:
Random generator gives repeated results 
### What happens?

random() gives repeated results even when run only 100k times and single-threaded.

In comparison, numpy gives unique results even when asked for 100M random numbers.

### To Reproduce

```sql
SET THREADS to 1;
SELECT count() FROM (SELECT DISTINCT random() AS a FROM range(100_000));
```
```
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│        99998 │
└──────────────┘
```


### OS:

Ubuntu22.04

### DuckDB Version:

1.1.0

### DuckDB Client:

Python3.9

### Hardware:

i5, x64

### Full Name:

Soeren Wolfers

### Affiliation:

G-Research

### 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?

Not applicable - the reproduction does not require a data set

### 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
Random generator gives repeated results 
### What happens?

random() gives repeated results even when run only 100k times and single-threaded.

In comparison, numpy gives unique results even when asked for 100M random numbers.

### To Reproduce

```sql
SET THREADS to 1;
SELECT count() FROM (SELECT DISTINCT random() AS a FROM range(100_000));
```
```
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│        99998 │
└──────────────┘
```


### OS:

Ubuntu22.04

### DuckDB Version:

1.1.0

### DuckDB Client:

Python3.9

### Hardware:

i5, x64

### Full Name:

Soeren Wolfers

### Affiliation:

G-Research

### 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?

Not applicable - the reproduction does not require a data set

### 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:

Fix patch:
diff --git a/scripts/apply_extension_patches.py b/scripts/apply_extension_patches.py
index c76b900ae20f..8f4c7c263eb1 100644
--- a/scripts/apply_extension_patches.py
+++ b/scripts/apply_extension_patches.py
@@ -13,7 +13,7 @@
 
 
 def raise_error(error_msg):
-    sys.stderr.write(error_message + '\n')
+    sys.stderr.write(error_msg + '\n')
     sys.exit(1)
 
 
diff --git a/src/common/random_engine.cpp b/src/common/random_engine.cpp
index e51f71001024..2dc0df1894d0 100644
--- a/src/common/random_engine.cpp
+++ b/src/common/random_engine.cpp
@@ -29,12 +29,18 @@ double RandomEngine::NextRandom(double min, double max) {
 }
 
 double RandomEngine::NextRandom() {
-	return std::ldexp(random_state->pcg(), -32);
+	auto uint64 = NextRandomInteger64();
+	return std::ldexp(uint64, -64);
 }
+
 uint32_t RandomEngine::NextRandomInteger() {
 	return random_state->pcg();
 }
 
+uint64_t RandomEngine::NextRandomInteger64() {
+	return (static_cast<uint64_t>(NextRandomInteger()) << UINT64_C(32)) | static_cast<uint64_t>(NextRandomInteger());
+}
+
 uint32_t RandomEngine::NextRandomInteger(uint32_t min, uint32_t max) {
 	return min + static_cast<uint32_t>(NextRandom() * double(max - min));
 }
diff --git a/src/include/duckdb/common/random_engine.hpp b/src/include/duckdb/common/random_engine.hpp
index 224a5a20322d..ab91f3684a04 100644
--- a/src/include/duckdb/common/random_engine.hpp
+++ b/src/include/duckdb/common/random_engine.hpp
@@ -30,6 +30,7 @@ struct RandomEngine {
 	double NextRandom();
 	uint32_t NextRandomInteger();
 	uint32_t NextRandomInteger(uint32_t min, uint32_t max);
+	uint64_t NextRandomInteger64();
 
 	void SetSeed(uint32_t seed);
 


Test patch:
diff --git a/.github/patches/extensions/spatial/random_test_fix.patch b/.github/patches/extensions/spatial/random_test_fix.patch
new file mode 100644
index 000000000000..da793ab49562
--- /dev/null
+++ b/.github/patches/extensions/spatial/random_test_fix.patch
@@ -0,0 +1,158 @@
+diff --git a/test/sql/index/rtree_basic_points.test b/test/sql/index/rtree_basic_points.test
+index c4de65b..047ad35 100644
+--- a/test/sql/index/rtree_basic_points.test
++++ b/test/sql/index/rtree_basic_points.test
+@@ -7,7 +7,7 @@ FROM st_generatepoints({min_x: 0, min_y: 0, max_x: 10000, max_y: 10000}::BOX_2D,
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-352
++390
+ 
+ statement ok
+ CREATE INDEX my_idx ON t1 USING RTREE (geom);
+@@ -20,4 +20,4 @@ physical_plan	<REGEX>:.*RTREE_INDEX_SCAN.*
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-352
+\ No newline at end of file
++390
+diff --git a/test/sql/index/rtree_block_reclaim.test_slow b/test/sql/index/rtree_block_reclaim.test_slow
+index f474266..903f634 100644
+--- a/test/sql/index/rtree_block_reclaim.test_slow
++++ b/test/sql/index/rtree_block_reclaim.test_slow
+@@ -21,7 +21,7 @@ CREATE INDEX idx ON tbl USING RTREE (geom);
+ query II
+ SELECT i, geom FROM tbl WHERE i = 50000;;
+ ----
+-50000	POINT (4732.4060765095055 128.3674407750368)
++50000	POINT (1345.636742415942 425.3127709233669)
+ 
+ statement ok
+ CHECKPOINT;
+@@ -51,7 +51,7 @@ CHECKPOINT;
+ query II
+ SELECT i, geom FROM tbl WHERE i = 50000;
+ ----
+-50000	POINT (4732.4060765095055 128.3674407750368)
++50000	POINT (1345.636742415942 425.3127709233669)
+ 
+ # now loop and always DROP INDEX, then recreate (reusing the same blocks)
+ 
+@@ -87,6 +87,6 @@ FROM pragma_database_size() current, blocks_idx;
+ query II
+ SELECT i, geom FROM tbl WHERE i = 50000;
+ ----
+-50000	POINT (4732.4060765095055 128.3674407750368)
++50000	POINT (1345.636742415942 425.3127709233669)
+ 
+ endloop
+\ No newline at end of file
+diff --git a/test/sql/index/rtree_persistence.test b/test/sql/index/rtree_persistence.test
+index a971dd5..926ab36 100644
+--- a/test/sql/index/rtree_persistence.test
++++ b/test/sql/index/rtree_persistence.test
+@@ -10,7 +10,7 @@ FROM st_generatepoints({min_x: 0, min_y: 0, max_x: 10000, max_y: 10000}::BOX_2D,
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-352
++390
+ 
+ statement ok
+ CREATE INDEX my_idx ON t1 USING RTREE (geom);
+@@ -31,4 +31,4 @@ physical_plan	<REGEX>:.*RTREE_INDEX_SCAN.*
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-352
+\ No newline at end of file
++390
+diff --git a/test/sql/index/rtree_persistence_load.test b/test/sql/index/rtree_persistence_load.test
+index 15ca910..056d8ce 100644
+--- a/test/sql/index/rtree_persistence_load.test
++++ b/test/sql/index/rtree_persistence_load.test
+@@ -10,7 +10,7 @@ FROM st_generatepoints({min_x: 0, min_y: 0, max_x: 10000, max_y: 10000}::BOX_2D,
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-43
++36
+ 
+ statement ok
+ CREATE INDEX my_idx ON t1 USING RTREE (geom);
+@@ -39,4 +39,4 @@ physical_plan	<REGEX>:.*RTREE_INDEX_SCAN.*
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-43
+\ No newline at end of file
++36
+diff --git a/test/sql/index/rtree_persistence_wal.test b/test/sql/index/rtree_persistence_wal.test
+index 14b3c53..6d310ed 100644
+--- a/test/sql/index/rtree_persistence_wal.test
++++ b/test/sql/index/rtree_persistence_wal.test
+@@ -13,7 +13,7 @@ FROM st_generatepoints({min_x: 0, min_y: 0, max_x: 10000, max_y: 10000}::BOX_2D,
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-43
++36
+ 
+ statement ok
+ CREATE INDEX my_idx ON t1 USING RTREE (geom);
+@@ -34,4 +34,4 @@ physical_plan	<REGEX>:.*RTREE_INDEX_SCAN.*
+ query I
+ SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));
+ ----
+-43
+\ No newline at end of file
++36
+diff --git a/test/sql/index/rtree_projection.test b/test/sql/index/rtree_projection.test
+index b60c922..23488e4 100644
+--- a/test/sql/index/rtree_projection.test
++++ b/test/sql/index/rtree_projection.test
+@@ -15,30 +15,30 @@ CREATE INDEX my_idx ON points USING RTREE(geom);
+ query II rowsort
+ SELECT * FROM points WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500));
+ ----
+-351	POINT (359.812940005213 406.6655575297773)
+-472	POINT (169.11179292947054 129.24372218549252)
+-775	POINT (173.61568519845605 455.52933821454644)
++249	POINT (42.208750495658 488.510431689545)
++760	POINT (16.631746553017916 252.64710064171524)
++907	POINT (88.77047830284079 251.89159351805182)
+ 
+ query II rowsort
+ SELECT geom, id FROM points WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500));
+ ----
+-POINT (169.11179292947054 129.24372218549252)	472
+-POINT (173.61568519845605 455.52933821454644)	775
+-POINT (359.812940005213 406.6655575297773)	351
++POINT (16.631746553017916 252.64710064171524)	760
++POINT (42.208750495658 488.510431689545)	249
++POINT (88.77047830284079 251.89159351805182)	907
+ 
+ query I rowsort
+ SELECT id FROM points WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500));
+ ----
+-351
+-472
+-775
++249
++760
++907
+ 
+ query III rowsort
+ SELECT id, geom, ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500)) as contained FROM points WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500));
+ ----
+-351	POINT (359.812940005213 406.6655575297773)	true
+-472	POINT (169.11179292947054 129.24372218549252)	true
+-775	POINT (173.61568519845605 455.52933821454644)	true
++249	POINT (42.208750495658 488.510431689545)	1
++760	POINT (16.631746553017916 252.64710064171524)	1
++907	POINT (88.77047830284079 251.89159351805182)	1
+ 
+ query I rowsort
+ SELECT ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500)) as contained FROM points WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 500, 500));
diff --git a/test/api/test_api.cpp b/test/api/test_api.cpp
index 3452604e4066..767c6bbb0abb 100644
--- a/test/api/test_api.cpp
+++ b/test/api/test_api.cpp
@@ -618,7 +618,7 @@ TEST_CASE("Issue #6284: CachingPhysicalOperator in pull causes issues", "[api][.
 		count += chunk->size();
 	}
 
-	REQUIRE(951468 - count == 0);
+	REQUIRE(951698 == count);
 }
 
 TEST_CASE("Fuzzer 50 - Alter table heap-use-after-free", "[api]") {
diff --git a/test/issues/general/test_9399.test_slow b/test/issues/general/test_9399.test_slow
index 86dac620f801..5327aa648c49 100644
--- a/test/issues/general/test_9399.test_slow
+++ b/test/issues/general/test_9399.test_slow
@@ -29,4 +29,4 @@ FROM test GROUP BY c0)
 SELECT * FROM cte
 ORDER BY c0
 ----
-299445 values hashing to 851e651ae21c938efff67bf148818639
+299406 values hashing to e9b6d718674413df6f4d2c3c3c5bd853
diff --git a/test/optimizer/topn/complex_top_n.test b/test/optimizer/topn/complex_top_n.test
index 6e81978f7c5f..6a366b4fd911 100644
--- a/test/optimizer/topn/complex_top_n.test
+++ b/test/optimizer/topn/complex_top_n.test
@@ -44,7 +44,7 @@ LEFT JOIN (
   LEFT JOIN ORDERITEMVIEW ON ORDERVIEW.ORDER_ID = ORDERITEM_ORDERID
 ) AS Q3J ON (Q3J.Q3P = CTE.CUSTOMER_ID) order by all;
 ----
-285 values hashing to 9b1c4d195c0e3c4de5b190b1ab7b357b
+261 values hashing to 5a4c1b428d36bcb30b04222a764fbafc
 
 query II
 explain WITH CTE AS (
diff --git a/test/sql/copy/parquet/hive_timestamps.test b/test/sql/copy/parquet/hive_timestamps.test
index 6e29e0d9bc1e..533c8eb3499a 100644
--- a/test/sql/copy/parquet/hive_timestamps.test
+++ b/test/sql/copy/parquet/hive_timestamps.test
@@ -39,11 +39,11 @@ FROM timeseries
 ORDER BY ALL
 LIMIT 5
 ----
-2023-11-01 00:00:00	15134
-2023-11-01 01:00:00	16968
-2023-11-01 02:00:00	13882
-2023-11-01 03:00:00	14317
-2023-11-01 04:00:00	14905
+2023-11-01 00:00:00	15848
+2023-11-01 01:00:00	13946
+2023-11-01 02:00:00	14636
+2023-11-01 03:00:00	13540
+2023-11-01 04:00:00	16269
 
 statement ok
 COPY (
@@ -58,8 +58,8 @@ FROM read_parquet('__TEST_DIR__/hive/*/*.parquet')
 ORDER BY ALL
 LIMIT 5
 ----
-2023-11-01 00:00:00	15134
-2023-11-01 01:00:00	16968
-2023-11-01 02:00:00	13882
-2023-11-01 03:00:00	14317
-2023-11-01 04:00:00	14905
+2023-11-01 00:00:00	15848
+2023-11-01 01:00:00	13946
+2023-11-01 02:00:00	14636
+2023-11-01 03:00:00	13540
+2023-11-01 04:00:00	16269
diff --git a/test/sql/join/iejoin/iejoin_projection_maps.test b/test/sql/join/iejoin/iejoin_projection_maps.test
index c4090b320968..ea3c7a771e21 100644
--- a/test/sql/join/iejoin/iejoin_projection_maps.test
+++ b/test/sql/join/iejoin/iejoin_projection_maps.test
@@ -27,7 +27,7 @@ query IIIIII
 SELECT SUM(id) AS id, SUM(id2) AS id2, SUM(id3) AS id3, SUM(value) AS sum_value, SUM(one_min_value) AS sum_one_min_value, sum_value + sum_one_min_value AS sum
 FROM df
 ----
-256630	29499	17606	2500.532	2499.468	5000.000
+255978	29751	17482	2504.293	2495.704	4999.997
 
 statement ok
 PRAGMA enable_verification
@@ -51,7 +51,7 @@ FROM (
 GROUP BY ALL
 ORDER BY ALL
 ----
-660 values hashing to 0acad7a6a360d48246479ab35572ecce
+660 values hashing to 7a9e98649bddd1ba74c34324caf4674c
 
 endloop
 
diff --git a/test/sql/join/iejoin/test_iejoin_events.test b/test/sql/join/iejoin/test_iejoin_events.test
index 8876036d5505..86de26c9a45b 100644
--- a/test/sql/join/iejoin/test_iejoin_events.test
+++ b/test/sql/join/iejoin/test_iejoin_events.test
@@ -43,4 +43,4 @@ SELECT COUNT(*) FROM (
 	  AND r.id <> s.id
 ) q2;
 ----
-6
+4
diff --git a/test/sql/sample/test_sample.test_slow b/test/sql/sample/test_sample.test_slow
index e56ebc09eacb..57a41394ea41 100644
--- a/test/sql/sample/test_sample.test_slow
+++ b/test/sql/sample/test_sample.test_slow
@@ -218,10 +218,10 @@ select * from integers using sample 10000%;
 query I
 select i from integers using sample (1 rows) repeatable (0);
 ----
-130
+152
 
 query I
 select i from integers using sample reservoir(1%) repeatable (0);
 ----
-83
-21
+51
+78
diff --git a/test/sql/window/test_window_wide_frame.test_slow b/test/sql/window/test_window_wide_frame.test_slow
index a9907fce83e9..9199a6540ded 100644
--- a/test/sql/window/test_window_wide_frame.test_slow
+++ b/test/sql/window/test_window_wide_frame.test_slow
@@ -25,4 +25,4 @@ window w as (order by timestamp asc range between interval 55 seconds preceding
 order by 3 desc
 limit 1;
 ----
-2020-10-15 16:58:10.318385	50.000000	3363
+2020-10-15 16:47:25.764708	49.0	3437