Repository: duckdb/duckdb (language: python)

Issue:
Multiple pivots prior to create call have wrong/unexpected behavior
### What happens?

when you run multiple pivot statements before turning their relations into tables, all but the last get clobbered with nonsense.
I'm running linux but the same problem occurs on windows.

details in reproduction code.

### To Reproduce

```python
# demonstrate bug
import numpy as np
import pandas as pd
import duckdb
N = 1000
df = pd.DataFrame({"a":np.random.choice(["u", "v", "w"], N, True),
                   "b":np.random.choice(["x", "y", "z"], N, True),
                   "c":np.random.randn(N),})
conn = duckdb.connect()

ddf = conn.from_df(df)
ddf.create("input_data")

xx = conn.query("pivot input_data on a using max(c) group by b;")
yy = conn.query("pivot input_data on b using max(c) group by a;")
xx.create("xx")
yy.create("yy")
print(conn.query("SHOW ALL TABLES;").df())
xx_table = conn.table("xx")
yy_table = conn.table("yy")
assert set(yy.columns)==set(yy_table.columns) # Succeeds.
assert set(xx.columns)==set(xx_table.columns)  # Fails. 
# xx_table shows up with cols [b, x, y, z] and all nans in the x,y,z cols
# The first col should indeed by b, but the next 3 should be u,v,w and they shouldn't have all nans
```

### OS:

Ubuntu 22.04.5 LTS x86_64

### DuckDB Version:

1.1.1

### DuckDB Client:

python

### Hardware:

_No response_

### Full Name:

Michael Hankin

### Affiliation:

Mothball Labs

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### 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
Multiple pivots prior to create call have wrong/unexpected behavior
### What happens?

when you run multiple pivot statements before turning their relations into tables, all but the last get clobbered with nonsense.
I'm running linux but the same problem occurs on windows.

details in reproduction code.

### To Reproduce

```python
# demonstrate bug
import numpy as np
import pandas as pd
import duckdb
N = 1000
df = pd.DataFrame({"a":np.random.choice(["u", "v", "w"], N, True),
                   "b":np.random.choice(["x", "y", "z"], N, True),
                   "c":np.random.randn(N),})
conn = duckdb.connect()

ddf = conn.from_df(df)
ddf.create("input_data")

xx = conn.query("pivot input_data on a using max(c) group by b;")
yy = conn.query("pivot input_data on b using max(c) group by a;")
xx.create("xx")
yy.create("yy")
print(conn.query("SHOW ALL TABLES;").df())
xx_table = conn.table("xx")
yy_table = conn.table("yy")
assert set(yy.columns)==set(yy_table.columns) # Succeeds.
assert set(xx.columns)==set(xx_table.columns)  # Fails. 
# xx_table shows up with cols [b, x, y, z] and all nans in the x,y,z cols
# The first col should indeed by b, but the next 3 should be u,v,w and they shouldn't have all nans
```

### OS:

Ubuntu 22.04.5 LTS x86_64

### DuckDB Version:

1.1.1

### DuckDB Client:

python

### Hardware:

_No response_

### Full Name:

Michael Hankin

### Affiliation:

Mothball Labs

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### 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:
I'd be more than happy to help with this issue and https://github.com/duckdb/duckdb/issues/14601 if someone with some experience here would be willing to hold my hand a bit.
I'd be more than happy to help with this issue and https://github.com/duckdb/duckdb/issues/14601 if someone with some experience here would be willing to hold my hand a bit.

Fix patch:
diff --git a/src/parser/transform/statement/transform_pivot_stmt.cpp b/src/parser/transform/statement/transform_pivot_stmt.cpp
index fee41aada399..8b798a83c9fb 100644
--- a/src/parser/transform/statement/transform_pivot_stmt.cpp
+++ b/src/parser/transform/statement/transform_pivot_stmt.cpp
@@ -15,6 +15,7 @@
 #include "duckdb/parser/expression/function_expression.hpp"
 #include "duckdb/parser/result_modifier.hpp"
 #include "duckdb/parser/tableref/subqueryref.hpp"
+#include "duckdb/common/types/uuid.hpp"
 
 namespace duckdb {
 
@@ -168,7 +169,6 @@ unique_ptr<QueryNode> Transformer::TransformPivotStatement(duckdb_libpgquery::PG
 	// generate CREATE TYPE statements for each of the columns that do not have an IN list
 	bool is_pivot = !pivot->unpivots;
 	auto columns = TransformPivotList(*pivot->columns, is_pivot);
-	auto pivot_idx = PivotEntryCount();
 	for (idx_t c = 0; c < columns.size(); c++) {
 		auto &col = columns[c];
 		if (!col.pivot_enum.empty() || !col.entries.empty()) {
@@ -177,7 +177,7 @@ unique_ptr<QueryNode> Transformer::TransformPivotStatement(duckdb_libpgquery::PG
 		if (col.pivot_expressions.size() != 1) {
 			throw InternalException("PIVOT statement with multiple names in pivot entry!?");
 		}
-		auto enum_name = "__pivot_enum_" + std::to_string(pivot_idx) + "_" + std::to_string(c);
+		auto enum_name = "__pivot_enum_" + UUID::ToString(UUID::GenerateRandomUUID());
 
 		auto new_select = make_uniq<SelectNode>();
 		ExtractCTEsRecursive(new_select->cte_map);


Test patch:
diff --git a/tools/pythonpkg/tests/fast/relational_api/test_pivot.py b/tools/pythonpkg/tests/fast/relational_api/test_pivot.py
new file mode 100644
index 000000000000..43b64d4072e7
--- /dev/null
+++ b/tools/pythonpkg/tests/fast/relational_api/test_pivot.py
@@ -0,0 +1,18 @@
+import duckdb
+import pytest
+from duckdb import ColumnExpression
+
+
+class TestPivot(object):
+    def test_pivot_issue_14600(self, duckdb_cursor):
+        duckdb_cursor.sql(
+            "create table input_data as select unnest(['u','v','w']) as a, unnest(['x','y','z']) as b, unnest([1,2,3]) as c;"
+        )
+        pivot_1 = duckdb_cursor.query("pivot input_data on a using max(c) group by b;")
+        pivot_2 = duckdb_cursor.query("pivot input_data on b using max(c) group by a;")
+        pivot_1.create("pivot_1")
+        pivot_2.create("pivot_2")
+        pivot_1_tbl = duckdb_cursor.table("pivot_1")
+        pivot_2_tbl = duckdb_cursor.table("pivot_2")
+        assert set(pivot_1.columns) == set(pivot_1_tbl.columns)
+        assert set(pivot_2.columns) == set(pivot_2_tbl.columns)