Combining function chaining and reusable aliases fails in where clause

[Alex-Monahan]

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.

-- 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'
;

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'
;...
            ^

-- 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?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have