Repository: duckdb/duckdb (language: python)

Issue:
INTERNAL error when using a macro to expand json[] array (`json_each`)
### What happens?

When I use the macro defined below with a `json[]` column, it errors out, but text and json types convert correctly.

INTERNAL Error: Failed to cast logical operator to type - logical operator type mismatch
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors

### To Reproduce

From the cli:
```sh
duckdb -c "
  CREATE OR REPLACE MACRO json_each(input) AS
    TABLE (
      SELECT
          CASE json_type (val::json)
          WHEN 'ARRAY' THEN
              unnest(RANGE (json_array_length(val::json)::bigint)) ::varchar
          ELSE
              unnest(json_keys (val::json))
          END AS key,
          json_extract (val::json, key) AS value
      FROM (SELECT input as val)
    );

  FROM (SELECT '[1,2,3]' as message) CROSS JOIN json_each(message);
  FROM (SELECT '[1,2,3]'::json as message) CROSS JOIN json_each(message);
  FROM (SELECT '[1,2,3]'::json[] as message) CROSS JOIN json_each(message);
  "
```

Output:
```
┌─────────┬─────────┬───────┐
│ message │   key   │ value │
│ varchar │ varchar │ json  │
├─────────┼─────────┼───────┤
│ [1,2,3] │ 0       │ 1     │
│ [1,2,3] │ 1       │ 2     │
│ [1,2,3] │ 2       │ 3     │
└─────────┴─────────┴───────┘
┌─────────┬─────────┬───────┐
│ message │   key   │ value │
│  json   │ varchar │ json  │
├─────────┼─────────┼───────┤
│ [1,2,3] │ 0       │ 1     │
│ [1,2,3] │ 1       │ 2     │
│ [1,2,3] │ 2       │ 3     │
└─────────┴─────────┴───────┘
INTERNAL Error: Failed to cast logical operator to type - logical operator type mismatch
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors
```

### OS:

linux

### DuckDB Version:

1.0.0, 1.1.0

### DuckDB Client:

CLI

### Hardware:

_No response_

### Full Name:

Ashkan Kiani

### Affiliation:

Jane Street

### 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
INTERNAL error when using a macro to expand json[] array (`json_each`)
### What happens?

When I use the macro defined below with a `json[]` column, it errors out, but text and json types convert correctly.

INTERNAL Error: Failed to cast logical operator to type - logical operator type mismatch
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors

### To Reproduce

From the cli:
```sh
duckdb -c "
  CREATE OR REPLACE MACRO json_each(input) AS
    TABLE (
      SELECT
          CASE json_type (val::json)
          WHEN 'ARRAY' THEN
              unnest(RANGE (json_array_length(val::json)::bigint)) ::varchar
          ELSE
              unnest(json_keys (val::json))
          END AS key,
          json_extract (val::json, key) AS value
      FROM (SELECT input as val)
    );

  FROM (SELECT '[1,2,3]' as message) CROSS JOIN json_each(message);
  FROM (SELECT '[1,2,3]'::json as message) CROSS JOIN json_each(message);
  FROM (SELECT '[1,2,3]'::json[] as message) CROSS JOIN json_each(message);
  "
```

Output:
```
┌─────────┬─────────┬───────┐
│ message │   key   │ value │
│ varchar │ varchar │ json  │
├─────────┼─────────┼───────┤
│ [1,2,3] │ 0       │ 1     │
│ [1,2,3] │ 1       │ 2     │
│ [1,2,3] │ 2       │ 3     │
└─────────┴─────────┴───────┘
┌─────────┬─────────┬───────┐
│ message │   key   │ value │
│  json   │ varchar │ json  │
├─────────┼─────────┼───────┤
│ [1,2,3] │ 0       │ 1     │
│ [1,2,3] │ 1       │ 2     │
│ [1,2,3] │ 2       │ 3     │
└─────────┴─────────┴───────┘
INTERNAL Error: Failed to cast logical operator to type - logical operator type mismatch
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors
```

### OS:

linux

### DuckDB Version:

1.0.0, 1.1.0

### DuckDB Client:

CLI

### Hardware:

_No response_

### Full Name:

Ashkan Kiani

### Affiliation:

Jane Street

### 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:
I noticed that trying to cast it within the macro call, i.e. `json_each(obj::json)` also fails. Only casting it in a subquery before calling the macro seems to not error out.

It seems there's a conversion issue with json[]
Thanks for the issue and the additional context. I could reproduce this very easily.
I noticed that trying to cast it within the macro call, i.e. `json_each(obj::json)` also fails. Only casting it in a subquery before calling the macro seems to not error out.

It seems there's a conversion issue with json[]
Thanks for the issue and the additional context. I could reproduce this very easily.

Fix patch:
diff --git a/src/include/duckdb/optimizer/unnest_rewriter.hpp b/src/include/duckdb/optimizer/unnest_rewriter.hpp
index 3798cbd4bfde..842d5ef2c545 100644
--- a/src/include/duckdb/optimizer/unnest_rewriter.hpp
+++ b/src/include/duckdb/optimizer/unnest_rewriter.hpp
@@ -8,8 +8,8 @@
 
 #pragma once
 
-#include "duckdb/planner/logical_operator.hpp"
 #include "duckdb/common/pair.hpp"
+#include "duckdb/planner/logical_operator.hpp"
 
 namespace duckdb {
 
@@ -61,14 +61,14 @@ class UnnestRewriter {
 
 private:
 	//! Find delim joins that contain an UNNEST
-	void FindCandidates(unique_ptr<LogicalOperator> *op_ptr, vector<unique_ptr<LogicalOperator> *> &candidates);
+	void FindCandidates(unique_ptr<LogicalOperator> &op, vector<reference<unique_ptr<LogicalOperator>>> &candidates);
 	//! Rewrite a delim join that contains an UNNEST
-	bool RewriteCandidate(unique_ptr<LogicalOperator> *candidate);
+	bool RewriteCandidate(unique_ptr<LogicalOperator> &candidate);
 	//! Update the bindings of the RHS sequence of LOGICAL_PROJECTION(s)
-	void UpdateRHSBindings(unique_ptr<LogicalOperator> *plan_ptr, unique_ptr<LogicalOperator> *candidate,
+	void UpdateRHSBindings(unique_ptr<LogicalOperator> &plan, unique_ptr<LogicalOperator> &candidate,
 	                       UnnestRewriterPlanUpdater &updater);
 	//! Update the bindings of the BOUND_UNNEST expression of the LOGICAL_UNNEST
-	void UpdateBoundUnnestBindings(UnnestRewriterPlanUpdater &updater, unique_ptr<LogicalOperator> *candidate);
+	void UpdateBoundUnnestBindings(UnnestRewriterPlanUpdater &updater, unique_ptr<LogicalOperator> &candidate);
 
 	//! Store all delim columns of the delim join
 	void GetDelimColumns(LogicalOperator &op);
diff --git a/src/optimizer/unnest_rewriter.cpp b/src/optimizer/unnest_rewriter.cpp
index f5d91061f3d2..796a9338b946 100644
--- a/src/optimizer/unnest_rewriter.cpp
+++ b/src/optimizer/unnest_rewriter.cpp
@@ -1,13 +1,13 @@
 #include "duckdb/optimizer/unnest_rewriter.hpp"
 
 #include "duckdb/common/pair.hpp"
-#include "duckdb/planner/operator/logical_delim_get.hpp"
+#include "duckdb/planner/expression/bound_columnref_expression.hpp"
+#include "duckdb/planner/expression/bound_unnest_expression.hpp"
 #include "duckdb/planner/operator/logical_comparison_join.hpp"
-#include "duckdb/planner/operator/logical_unnest.hpp"
+#include "duckdb/planner/operator/logical_delim_get.hpp"
 #include "duckdb/planner/operator/logical_projection.hpp"
+#include "duckdb/planner/operator/logical_unnest.hpp"
 #include "duckdb/planner/operator/logical_window.hpp"
-#include "duckdb/planner/expression/bound_unnest_expression.hpp"
-#include "duckdb/planner/expression/bound_columnref_expression.hpp"
 
 namespace duckdb {
 
@@ -35,8 +35,8 @@ void UnnestRewriterPlanUpdater::VisitExpression(unique_ptr<Expression> *expressi
 unique_ptr<LogicalOperator> UnnestRewriter::Optimize(unique_ptr<LogicalOperator> op) {
 
 	UnnestRewriterPlanUpdater updater;
-	vector<unique_ptr<LogicalOperator> *> candidates;
-	FindCandidates(&op, candidates);
+	vector<reference<unique_ptr<LogicalOperator>>> candidates;
+	FindCandidates(op, candidates);
 
 	// rewrite the plan and update the bindings
 	for (auto &candidate : candidates) {
@@ -47,7 +47,7 @@ unique_ptr<LogicalOperator> UnnestRewriter::Optimize(unique_ptr<LogicalOperator>
 			// update the bindings of the BOUND_UNNEST expression
 			UpdateBoundUnnestBindings(updater, candidate);
 			// update the sequence of LOGICAL_PROJECTION(s)
-			UpdateRHSBindings(&op, candidate, updater);
+			UpdateRHSBindings(op, candidate, updater);
 			// reset
 			delim_columns.clear();
 			lhs_bindings.clear();
@@ -57,12 +57,11 @@ unique_ptr<LogicalOperator> UnnestRewriter::Optimize(unique_ptr<LogicalOperator>
 	return op;
 }
 
-void UnnestRewriter::FindCandidates(unique_ptr<LogicalOperator> *op_ptr,
-                                    vector<unique_ptr<LogicalOperator> *> &candidates) {
-	auto op = op_ptr->get();
+void UnnestRewriter::FindCandidates(unique_ptr<LogicalOperator> &op,
+                                    vector<reference<unique_ptr<LogicalOperator>>> &candidates) {
 	// search children before adding, so that we add candidates bottom-up
 	for (auto &child : op->children) {
-		FindCandidates(&child, candidates);
+		FindCandidates(child, candidates);
 	}
 
 	// search for operator that has a LOGICAL_DELIM_JOIN as its child
@@ -100,14 +99,15 @@ void UnnestRewriter::FindCandidates(unique_ptr<LogicalOperator> *op_ptr,
 		curr_op = &curr_op->get()->children[0];
 	}
 
-	if (curr_op->get()->type == LogicalOperatorType::LOGICAL_UNNEST) {
-		candidates.push_back(op_ptr);
+	if (curr_op->get()->type == LogicalOperatorType::LOGICAL_UNNEST &&
+	    curr_op->get()->children[0]->type == LogicalOperatorType::LOGICAL_DELIM_GET) {
+		candidates.push_back(op);
 	}
 }
 
-bool UnnestRewriter::RewriteCandidate(unique_ptr<LogicalOperator> *candidate) {
+bool UnnestRewriter::RewriteCandidate(unique_ptr<LogicalOperator> &candidate) {
 
-	auto &topmost_op = (LogicalOperator &)**candidate;
+	auto &topmost_op = *candidate;
 	if (topmost_op.type != LogicalOperatorType::LOGICAL_PROJECTION &&
 	    topmost_op.type != LogicalOperatorType::LOGICAL_WINDOW &&
 	    topmost_op.type != LogicalOperatorType::LOGICAL_FILTER &&
@@ -158,10 +158,10 @@ bool UnnestRewriter::RewriteCandidate(unique_ptr<LogicalOperator> *candidate) {
 	return true;
 }
 
-void UnnestRewriter::UpdateRHSBindings(unique_ptr<LogicalOperator> *plan_ptr, unique_ptr<LogicalOperator> *candidate,
+void UnnestRewriter::UpdateRHSBindings(unique_ptr<LogicalOperator> &plan, unique_ptr<LogicalOperator> &candidate,
                                        UnnestRewriterPlanUpdater &updater) {
 
-	auto &topmost_op = (LogicalOperator &)**candidate;
+	auto &topmost_op = *candidate;
 	idx_t shift = lhs_bindings.size();
 
 	vector<unique_ptr<LogicalOperator> *> path_to_unnest;
@@ -189,7 +189,7 @@ void UnnestRewriter::UpdateRHSBindings(unique_ptr<LogicalOperator> *plan_ptr, un
 	}
 
 	// update all bindings by shifting them
-	updater.VisitOperator(*plan_ptr->get());
+	updater.VisitOperator(*plan);
 	updater.replace_bindings.clear();
 
 	// update all bindings coming from the LHS to RHS bindings
@@ -212,7 +212,7 @@ void UnnestRewriter::UpdateRHSBindings(unique_ptr<LogicalOperator> *plan_ptr, un
 	unnest.expressions.clear();
 	unnest.children.clear();
 	// update the bindings of the plan
-	updater.VisitOperator(*plan_ptr->get());
+	updater.VisitOperator(*plan);
 	updater.replace_bindings.clear();
 	// add the children again
 	for (auto &temp_bound_unnest : temp_bound_unnests) {
@@ -253,9 +253,9 @@ void UnnestRewriter::UpdateRHSBindings(unique_ptr<LogicalOperator> *plan_ptr, un
 }
 
 void UnnestRewriter::UpdateBoundUnnestBindings(UnnestRewriterPlanUpdater &updater,
-                                               unique_ptr<LogicalOperator> *candidate) {
+                                               unique_ptr<LogicalOperator> &candidate) {
 
-	auto &topmost_op = (LogicalOperator &)**candidate;
+	auto &topmost_op = *candidate;
 
 	// traverse LOGICAL_PROJECTION(s)
 	auto curr_op = &topmost_op.children[0];


Test patch:
diff --git a/test/optimizer/unnest_rewriter_issue_14542.test b/test/optimizer/unnest_rewriter_issue_14542.test
new file mode 100644
index 000000000000..d55199997da7
--- /dev/null
+++ b/test/optimizer/unnest_rewriter_issue_14542.test
@@ -0,0 +1,28 @@
+# name: test/optimizer/unnest_rewriter_issue_14542.test
+# description: Test issue #14542: INTERNAL error when using a macro to expand json[] array (json_each)
+# group: [optimizer]
+
+require json
+
+statement ok
+CREATE OR REPLACE MACRO json_each(input) AS
+TABLE (
+  SELECT
+      CASE json_type (val::json)
+      WHEN 'ARRAY' THEN
+          unnest(RANGE (json_array_length(val::json)::bigint)) ::varchar
+      ELSE
+          unnest(json_keys (val::json))
+      END AS key,
+      json_extract (val::json, key) AS value
+  FROM (SELECT input as val)
+);
+
+statement ok
+FROM (SELECT '[1,2,3]' as message) CROSS JOIN json_each(message);
+
+statement ok
+FROM (SELECT '[1,2,3]'::json as message) CROSS JOIN json_each(message);
+
+statement ok
+FROM (SELECT '[1,2,3]'::json[] as message) CROSS JOIN json_each(message);