Repository: duckdb/duckdb (language: python)

Issue:
import + pivot + export creates un-importable dump 
### What happens?

If you create a table from a pivot command, export the db, import it again elsewhere, do another pivot, then export again, the exported database will not be importable because of a bug in the schema.sql wherein there are multiple lines that start with `CREATE TYPE __pivot_enum_0_0 AS ENUM`

### To Reproduce

```py
# demonstrate bug
import numpy as np
import pandas as pd
import duckdb
from pathlib import Path

TEMP_PATH0 = "/tmp/duckdemo0"
TEMP_PATH1 = "/tmp/duckdemo1"
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),})
conn0 = duckdb.connect(":memory:0")

ddf = conn0.from_df(df)
ddf.create("input_data")

xx = conn0.query("pivot input_data on a using max(c) group by b;")
xx.create("xx")
conn0.execute(f"EXPORT DATABASE '{TEMP_PATH0}'  (FORMAT PARQUET);")
conn1 = duckdb.connect(":memory:1")
conn1.execute(f"IMPORT DATABASE '{TEMP_PATH0}';")
yy = conn1.query("pivot input_data on b using max(c) group by a;")
yy.create("yy")
conn1.execute(f"EXPORT DATABASE '{TEMP_PATH1}'  (FORMAT PARQUET);")
with (Path(TEMP_PATH1) / "schema.sql").open("r") as f:
    print(f.read())

# CREATE TYPE __pivot_enum_0_0 AS ENUM ( 'x', 'y', 'z' );
# CREATE TYPE __pivot_enum_0_0 AS ENUM ( 'u', 'v', 'w' );

# CREATE TABLE input_data(a VARCHAR, b VARCHAR, c DOUBLE);
# CREATE TABLE xx(b VARCHAR, u DOUBLE, v DOUBLE, w DOUBLE);
# CREATE TABLE yy(a VARCHAR, x DOUBLE, y DOUBLE, z DOUBLE);

# Renames the pivot type. Breaks importing even though it's never used.

conn2 = duckdb.connect(":memory:2")
conn2.execute(f"IMPORT DATABASE '{TEMP_PATH1}';")
# Fails with message:
# CatalogException: Catalog Error: Type with name "__pivot_enum_0_0" already exists!
```

### 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
Fix #14600: use UUID to generate unique pivot enum names
Fixes #14600


Discussion / hints:

Fix patch:
diff --git a/src/execution/operator/persistent/physical_export.cpp b/src/execution/operator/persistent/physical_export.cpp
index 2e0f4bbf4fb4..733b01f76551 100644
--- a/src/execution/operator/persistent/physical_export.cpp
+++ b/src/execution/operator/persistent/physical_export.cpp
@@ -127,6 +127,10 @@ void PhysicalExport::ExtractEntries(ClientContext &context, vector<reference<Sch
                                     ExportEntries &result) {
 	for (auto &schema_p : schema_list) {
 		auto &schema = schema_p.get();
+		auto &catalog = schema.ParentCatalog();
+		if (catalog.IsSystemCatalog() || catalog.IsTemporaryCatalog()) {
+			continue;
+		}
 		if (!schema.internal) {
 			result.schemas.push_back(schema);
 		}
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..d78df656d6dd
--- /dev/null
+++ b/tools/pythonpkg/tests/fast/relational_api/test_pivot.py
@@ -0,0 +1,30 @@
+import duckdb
+import pytest
+import os
+import tempfile
+
+
+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)
+
+    def test_pivot_issue_14601(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_1.create("pivot_1")
+        export_dir = tempfile.mkdtemp()
+        duckdb_cursor.query(f"EXPORT DATABASE '{export_dir}'")
+        with open(os.path.join(export_dir, "schema.sql"), "r") as f:
+            assert 'CREATE TYPE' not in f.read()