Repository: duckdb/duckdb
File: src/optimizer/unnest_rewriter.cpp (C++)

Code before:
	// RHS child must be projection(s) followed by an UNNEST
	auto curr_op = &delim_join.children[other_idx];
	while (curr_op->get()->type == LogicalOperatorType::LOGICAL_PROJECTION) {
		if (curr_op->get()->children.size() != 1) {
			break;
		}
		curr_op = &curr_op->get()->children[0];
	}

	// pattern1: delim_get -> unnest-> projection
	if (curr_op->get()->type == LogicalOperatorType::LOGICAL_UNNEST &&
	    curr_op->get()->children[0]->type == LogicalOperatorType::LOGICAL_DELIM_GET) {
		candidates.push_back(op);
		return;
	}

	curr_op = &delim_join.children[other_idx];
	if (curr_op->get()->type == LogicalOperatorType::LOGICAL_GET) {
		auto &get = curr_op->get()->Cast<LogicalGet>();
		if (get.function.name != "unnest") {
			return;
		}
		// pattern2: delim_get -> projection -> table_in_out(unnest)
		auto &unnest_get_ref = curr_op->get()->Cast<LogicalGet>();
		if (unnest_get_ref.ordinality_idx.IsValid()) {
			// we also unnest delim_index so cannot rewrite it
			return;
		}
		curr_op = &curr_op->get()->children[0];

		// find pattern2 and convert to pattern1
		if (curr_op->get()->type == LogicalOperatorType::LOGICAL_PROJECTION &&
		    curr_op->get()->children[0]->type == LogicalOperatorType::LOGICAL_DELIM_GET) {
			auto unnest_get = std::move(delim_join.children[other_idx]);
			unnest_get->ResolveOperatorTypes();
			ColumnBindingReplacer replacer;
			auto unnest_get_column = unnest_get->GetColumnBindings();
			auto &proj = curr_op->get()->Cast<LogicalProjection>();
			auto delim_get = std::move(proj.children[0]);
			auto unnest = make_uniq<LogicalUnnest>(unnest_get->GetTableIndex()[0]);
			unnest->children.push_back(std::move(delim_get));
			delim_join.children[other_idx] = std::move(*curr_op);
			for (idx_t i = 0; i < unnest_get_column.size(); i++) {
				auto &col_bind = unnest_get_column[i];
				D_ASSERT(col_bind.table_index == unnest_get->GetTableIndex()[0] ||
				         col_bind.table_index == proj.table_index);
				if (col_bind.table_index == unnest_get->GetTableIndex()[0]) {
					D_ASSERT(proj.expressions[col_bind.column_index]->GetExpressionClass() ==
					         ExpressionClass::BOUND_COLUMN_REF);
					auto &bind_col = proj.expressions[col_bind.column_index]->Cast<BoundColumnRefExpression>();
					auto unnest_expr = make_uniq<BoundUnnestExpression>(unnest_get->types[i]);

Review comment (performance):
Could you add a test that triggers this case, i.e., we don't optimize if the `ordinality_idx` is valid?

Code after:
	auto curr_op = &delim_join.children[other_idx];
	while (curr_op->get()->type == LogicalOperatorType::LOGICAL_PROJECTION) {
		if (curr_op->get()->children.size() != 1) {
			break;
		}
		curr_op = &curr_op->get()->children[0];
	}

	// pattern1: delim_get -> unnest-> projection
	if (curr_op->get()->type == LogicalOperatorType::LOGICAL_UNNEST &&
	    curr_op->get()->children[0]->type == LogicalOperatorType::LOGICAL_DELIM_GET) {
		candidates.push_back(op);
		return;
	}

	curr_op = &delim_join.children[other_idx];
	if (curr_op->get()->type == LogicalOperatorType::LOGICAL_GET) {
		auto &get = curr_op->get()->Cast<LogicalGet>();
		if (!ExpressionBinder::IsUnnestFunction(get.function.name)) {
			return;
		}
		// pattern2: delim_get -> projection -> table_in_out(unnest)
		auto &unnest_get_ref = curr_op->get()->Cast<LogicalGet>();
		if (unnest_get_ref.ordinality_idx.IsValid()) {
			// we also unnest delim_index so cannot rewrite it
			return;
		}
		curr_op = &curr_op->get()->children[0];

		// find pattern2 and convert to pattern1
		if (curr_op->get()->type == LogicalOperatorType::LOGICAL_PROJECTION &&
		    curr_op->get()->children[0]->type == LogicalOperatorType::LOGICAL_DELIM_GET) {
			auto unnest_get = std::move(delim_join.children[other_idx]);
			unnest_get->ResolveOperatorTypes();
			ColumnBindingReplacer replacer;
			auto unnest_get_column = unnest_get->GetColumnBindings();
			auto &proj = curr_op->get()->Cast<LogicalProjection>();
			auto delim_get = std::move(proj.children[0]);
			auto unnest = make_uniq<LogicalUnnest>(unnest_get->GetTableIndex()[0]);
			unnest->children.push_back(std::move(delim_get));
			delim_join.children[other_idx] = std::move(*curr_op);
			for (idx_t i = 0; i < unnest_get_column.size(); i++) {
				auto &col_bind = unnest_get_column[i];
				D_ASSERT(col_bind.table_index == unnest_get->GetTableIndex()[0] ||
				         col_bind.table_index == proj.table_index);
				if (col_bind.table_index == unnest_get->GetTableIndex()[0]) {
					D_ASSERT(proj.expressions[col_bind.column_index]->GetExpressionClass() ==
					         ExpressionClass::BOUND_COLUMN_REF);
					auto &bind_col = proj.expressions[col_bind.column_index]->Cast<BoundColumnRefExpression>();
					auto unnest_expr = make_uniq<BoundUnnestExpression>(unnest_get->types[i]);
					unnest_expr->child = proj.expressions[col_bind.column_index]->Copy();