Repository: duckdb/duckdb (language: python)

Issue:
`SELECT * SIMILAR TO <regexp> AS <name>` ignores `<name>`
### What happens?

The newly introduced `SELECT * SIMILAR TO <pattern>` syntax (and the related `LIKE` / `GLOB` versions) ignores subsequent `AS` clauses.

Ideally, it'd treat them just like `COLUMNS(<pattern>) AS <name>` including allowing for matching group references.

### To Reproduce

```sql
SELECT * SIMILAR TO 'number(\d+)' AS '\1'
FROM (SELECT 1 AS number1, 2 AS number2, 3 AS end)
```
and
```sql
SELECT * SIMILAR TO 'number(\d+)' AS test
FROM (SELECT 1 AS number1, 2 AS number2, 3 AS end)
```
both return columns `number1` and `number2`, completely ignoring the `AS` clause.

### OS:

Linux

### DuckDB Version:

1.1.4

### DuckDB Client:

Python

### Hardware:

i5, AMD64

### 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 nightly build

### 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
`* RENAME (...) LIKE ...` is broken
### What happens?

The combination of `RENAME` and `LIKE` in a star expression currently ignores the `RENAME`.

### To Reproduce

```sql
SELECT * RENAME (number2 as test) LIKE '%number%'
FROM (SELECT 1 AS number1, 2 AS number2)
```
```
┌─────────┬─────────┐
│ number1 │ number2 │
│  int32  │  int32  │
├─────────┼─────────┤
│       1 │       2 │
└─────────┴─────────┘
```
but should return only `number1`. 

Alternatively, the combination of `RENAME` and `LIKE` should simply be disallowed.

(Currently, combinations of `RENAME` / `REPLACE` / `EXCLUDE` / `LIKE` are only allowed if [the first modifier is `EXCLUDE` and there is only one additional modifier] or [the combination in this issue, which is broken].) 


### OS:

Linux

### DuckDB Version:

1.1.4-devxxxx

### DuckDB Client:

Python

### Hardware:

i5, AMD64

### 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 nightly build

### 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:

On DuckDB 1.1.3 this throws a parse error:
```sql
SELECT * RENAME (number2 as test) LIKE '%number%'
FROM (SELECT 1 AS number1, 2 AS number2);
----
Parser Error: syntax error at or near "RENAME"
LINE 1: SELECT * RENAME (number2 as test) LIKE '%number%...
```
Oh sorry I'm on the latest `pip install duckdb --upgrade --pre ` release, not 1.1.0 as originally stated in my issue. 

Fix patch:
diff --git a/src/planner/binder/expression/bind_star_expression.cpp b/src/planner/binder/expression/bind_star_expression.cpp
index 2a15d0c8d4ad..40704836cb4f 100644
--- a/src/planner/binder/expression/bind_star_expression.cpp
+++ b/src/planner/binder/expression/bind_star_expression.cpp
@@ -44,6 +44,11 @@ bool Binder::FindStarExpression(unique_ptr<ParsedExpression> &expr, StarExpressi
 				throw BinderException(
 				    "STAR expression with REPLACE list is only allowed as the root element of COLUMNS");
 			}
+			if (!current_star.rename_list.empty()) {
+				// '*' inside COLUMNS can not have a REPLACE list
+				throw BinderException(
+				    "STAR expression with RENAME list is only allowed as the root element of COLUMNS");
+			}
 
 			// '*' expression inside a COLUMNS - convert to a constant list of strings (column names)
 			vector<unique_ptr<ParsedExpression>> star_list;
@@ -162,26 +167,37 @@ void TryTransformStarLike(unique_ptr<ParsedExpression> &root) {
 	if (right->expression_class != ExpressionClass::CONSTANT) {
 		throw BinderException(*root, "Pattern applied to a star expression must be a constant");
 	}
+	if (!star.rename_list.empty()) {
+		throw BinderException(*root, "Rename list cannot be combined with a filtering operation");
+	}
 	if (!star.replace_list.empty()) {
 		throw BinderException(*root, "Replace list cannot be combined with a filtering operation");
 	}
-	// generate a columns expression
-	// "* LIKE '%literal%'
-	// -> COLUMNS(list_filter(*, x -> x LIKE '%literal%'))
+	auto original_alias = root->alias;
 	auto star_expr = std::move(left);
+	unique_ptr<ParsedExpression> child_expr;
+	if (function.function_name == "regexp_full_match" && star.exclude_list.empty()) {
+		// * SIMILAR TO '[regex]' is equivalent to COLUMNS('[regex]') so we can just move the expression directly
+		child_expr = std::move(right);
+	} else {
+		// for other expressions -> generate a columns expression
+		// "* LIKE '%literal%'
+		// -> COLUMNS(list_filter(*, x -> x LIKE '%literal%'))
+		auto lhs = make_uniq<ColumnRefExpression>("__lambda_col");
+		function.children[0] = lhs->Copy();
 
-	auto lhs = make_uniq<ColumnRefExpression>("__lambda_col");
-	function.children[0] = lhs->Copy();
-
-	auto lambda = make_uniq<LambdaExpression>(std::move(lhs), std::move(root));
-	vector<unique_ptr<ParsedExpression>> filter_children;
-	filter_children.push_back(std::move(star_expr));
-	filter_children.push_back(std::move(lambda));
-	auto list_filter = make_uniq<FunctionExpression>("list_filter", std::move(filter_children));
+		auto lambda = make_uniq<LambdaExpression>(std::move(lhs), std::move(root));
+		vector<unique_ptr<ParsedExpression>> filter_children;
+		filter_children.push_back(std::move(star_expr));
+		filter_children.push_back(std::move(lambda));
+		auto list_filter = make_uniq<FunctionExpression>("list_filter", std::move(filter_children));
+		child_expr = std::move(list_filter);
+	}
 
 	auto columns_expr = make_uniq<StarExpression>();
 	columns_expr->columns = true;
-	columns_expr->expr = std::move(list_filter);
+	columns_expr->expr = std::move(child_expr);
+	columns_expr->alias = std::move(original_alias);
 	root = std::move(columns_expr);
 }
 


Test patch:
diff --git a/test/sql/projection/select_star_like.test b/test/sql/projection/select_star_like.test
index fcb3a552632f..40969744dfa1 100644
--- a/test/sql/projection/select_star_like.test
+++ b/test/sql/projection/select_star_like.test
@@ -45,6 +45,22 @@ SELECT * EXCLUDE (col1) SIMILAR TO '.*col.*' FROM integers
 ----
 2
 
+# SIMILAR TO alias can use regex groups
+query II
+SELECT c2, c1 FROM (
+SELECT * SIMILAR TO 'number(\d+)' AS 'c\1' FROM (SELECT 1 AS number1, 2 AS number2, 3 AS end)
+)
+----
+2	1
+
+# LIKE
+query I
+SELECT val FROM (
+SELECT * NOT LIKE '%number%' AS val FROM (SELECT 1 AS number1, 2 AS number2, 3 AS end)
+)
+----
+3
+
 # non-constant pattern
 statement error
 SELECT * SIMILAR TO pattern FROM integers, (SELECT '.*col.*') t(pattern)
@@ -62,3 +78,9 @@ statement error
 SELECT * REPLACE (col1 + 42 AS col1) SIMILAR TO '.*col.*' FROM integers
 ----
 Replace list cannot be combined with a filtering operation
+
+# rename
+statement error
+SELECT * RENAME (col1 AS other_) SIMILAR TO '.*col.*' FROM integers
+----
+Rename list cannot be combined with a filtering operation