Repository: duckdb/duckdb (language: python)

Issue:
Combining function chaining and reusable aliases fails in where clause
### What happens?

Howdy folks! 

I found a small issue when combining 2 of my favorite friendly SQL features. 

If I combine the use of function chaining and reusable aliases together in my select clause, then use that column in my where clause, I receive an error. 

### To Reproduce

It seems to be something to do with the combination of the two. Using just function chaining or just reusable aliases works correctly in the where clause. However, both together fails. 

```sql
-- Fails
with test as (
    select 'woot' as my_column
)
from test
select 
    my_column.substr(2) as partial_woot,
    partial_woot.substr(2) as more_partially_woot
where 
    more_partially_woot = 'ot'
;
```
```sql
Catalog Error: Scalar Function with name substr does not exist!
Did you mean "main.substr"?
LINE 7:     partial_woot.substr(2) as more_partially_woot
where 
    more_partially_woot = 'ot'
;...
            ^
```
```sql
-- Works: Multiple chains without reusable aliases
with test as (
    select 'woot' as my_column
)
from test
select 
    my_column.substr(2) as partial_woot,
    my_column.substr(2).substr(2) as more_partially_woot
where 
    more_partially_woot = 'ot'
;

-- Works: Single chain and reusing the alias in the where clause only
with test as (
    select 'woot' as my_column
)
from test
select 
    my_column.substr(2) as partial_woot,
where 
    partial_woot = 'oot'
;

-- Works: Reusable aliases that don't use function chaining
with test as (
    select 'woot' as my_column
)
from test
select 
    substr(my_column,2) as partial_woot,
    substr(partial_woot,2) as more_partially_woot
where 
    more_partially_woot = 'ot'
;
```

### OS:

MacOS

### DuckDB Version:

0.10.2, 0.10.3-dev388

### DuckDB Client:

CLI

### Full Name:

Alex Monahan

### Affiliation:

DuckDB Labs and MotherDuck (lol at Oracle placeholder)

### 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:
This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.
Worth keeping open for sometime in the future!
I have a similar issue which seems to be related to this one. Using function chaining in a macro produced the exact same error message. For example:
```
CREATE OR REPLACE TEMP MACRO
    SINGLE_SPACE (VALUE) AS
VALUE.REGEXP_REPLACE(' {2,}', ' ', 'g')
;
SELECT
        SINGLE_SPACE('HELLO     THERE')
    AS RESULT
;
```
```
Catalog Error: Scalar Function with name regexp_replace does not exist! Did you mean "main.regexp_replace"? LINE 3: AS RESULT
```
@chris-dailey  

```sql 
CREATE OR REPLACE TEMP MACRO
     SINGLE_SPACE (VALUE) AS
 (VALUE).REGEXP_REPLACE(' {2,}', ' ', 'g')
 ;

SELECT SINGLE_SPACE('HELLO     THERE') AS RESULT ;
```

Adding parentheses around the `VALUE` .   This should work ! 
@Alex-Monahan  I have a patch fix this. I'll finish this weekend

Fix patch:
diff --git a/src/include/duckdb/planner/expression_binder/column_alias_binder.hpp b/src/include/duckdb/planner/expression_binder/column_alias_binder.hpp
index 4f1e2c34abda..d70e65c7d202 100644
--- a/src/include/duckdb/planner/expression_binder/column_alias_binder.hpp
+++ b/src/include/duckdb/planner/expression_binder/column_alias_binder.hpp
@@ -23,6 +23,8 @@ class ColumnAliasBinder {
 
 	bool BindAlias(ExpressionBinder &enclosing_binder, unique_ptr<ParsedExpression> &expr_ptr, idx_t depth,
 	               bool root_expression, BindResult &result);
+	// Check if the column reference is an SELECT item alias.
+	bool QualifyColumnAlias(const ColumnRefExpression &colref);
 
 private:
 	SelectBindState &bind_state;
diff --git a/src/include/duckdb/planner/expression_binder/where_binder.hpp b/src/include/duckdb/planner/expression_binder/where_binder.hpp
index e04651e1524d..20c4431204c6 100644
--- a/src/include/duckdb/planner/expression_binder/where_binder.hpp
+++ b/src/include/duckdb/planner/expression_binder/where_binder.hpp
@@ -24,6 +24,7 @@ class WhereBinder : public ExpressionBinder {
 	                          bool root_expression = false) override;
 
 	string UnsupportedAggregateMessage() override;
+	bool QualifyColumnAlias(const ColumnRefExpression &colref) override;
 
 private:
 	BindResult BindColumnRef(unique_ptr<ParsedExpression> &expr_ptr, idx_t depth, bool root_expression);
diff --git a/src/planner/expression_binder/column_alias_binder.cpp b/src/planner/expression_binder/column_alias_binder.cpp
index fbe0f5a377fb..c4477f9e348a 100644
--- a/src/planner/expression_binder/column_alias_binder.cpp
+++ b/src/planner/expression_binder/column_alias_binder.cpp
@@ -43,4 +43,11 @@ bool ColumnAliasBinder::BindAlias(ExpressionBinder &enclosing_binder, unique_ptr
 	return true;
 }
 
+bool ColumnAliasBinder::QualifyColumnAlias(const ColumnRefExpression &colref) {
+	if (!colref.IsQualified()) {
+		return bind_state.alias_map.find(colref.column_names[0]) != bind_state.alias_map.end();
+	}
+	return false;
+}
+
 } // namespace duckdb
diff --git a/src/planner/expression_binder/where_binder.cpp b/src/planner/expression_binder/where_binder.cpp
index b8cf24ea27e0..9b25c7930b63 100644
--- a/src/planner/expression_binder/where_binder.cpp
+++ b/src/planner/expression_binder/where_binder.cpp
@@ -43,4 +43,11 @@ string WhereBinder::UnsupportedAggregateMessage() {
 	return "WHERE clause cannot contain aggregates!";
 }
 
+bool WhereBinder::QualifyColumnAlias(const ColumnRefExpression &colref) {
+	if (column_alias_binder) {
+		return column_alias_binder->QualifyColumnAlias(colref);
+	}
+	return false;
+}
+
 } // namespace duckdb


Test patch:
diff --git a/test/sql/binder/test_function_chainging_alias.test b/test/sql/binder/test_function_chainging_alias.test
index b93a8b409e93..f5921416951b 100644
--- a/test/sql/binder/test_function_chainging_alias.test
+++ b/test/sql/binder/test_function_chainging_alias.test
@@ -2,8 +2,6 @@
 # description: referencing an alias or a function chaining alias that exists earlier on
 # group: [binder]
 
-#              in the SELECT clause (Issue #7190)
-
 statement ok
 PRAGMA enable_verification
 
@@ -91,3 +89,43 @@ query III
 EXECUTE v1('Hello World', 'test function chainging')
 ----
 [hello, world]	[TEST, FUNCTION, CHAINGING]	[hello, world, TEST, FUNCTION, CHAINGING]
+
+statement ok
+INSERT INTO varchars VALUES ('Another longggggg String');
+
+# alias in where clause
+query IIII
+SELECT  v.split(' ') strings,
+        strings.apply(x -> x.lower()).filter(x -> x[1] == 't' or x[1] == 'a') lower,
+        strings.apply(x -> x.upper()).filter(x -> x[1] == 'T' or x[1] == 'A') upper,
+        lower + upper  as mix_case_srings
+FROM varchars
+WHERE mix_case_srings[1] = 'test'
+----
+[Test, Function, Chainging, Alias]	[test, alias]	[TEST, ALIAS]	[test, alias, TEST, ALIAS]
+
+
+query IIII
+SELECT  v.split(' ') strings,
+        strings.apply(x -> x.lower()).filter(x -> x[1] == 't' or x[1] == 'a') lower,
+        strings.apply(x -> x.upper()).filter(x -> x[1] == 'T' or x[1] == 'A') upper,
+        lower + upper  as mix_case_srings
+FROM varchars
+WHERE mix_case_srings[1] = 'another'
+----
+[Another, longggggg, String]	[another]	[ANOTHER]	[another, ANOTHER]
+
+# CTE with function chaining alias
+query II
+with test as (
+    select 'woot' as my_column
+)
+from test
+select 
+    my_column.substr(2) as partial_woot,
+    partial_woot.substr(2) as more_partially_woot
+where 
+    more_partially_woot = 'ot'
+;
+----
+oot	ot
\ No newline at end of file