rowid not found

[simeonackermann]

What happens?

trying to use the pseudo column rowid in sql("SELECT ...") clause

To Reproduce

import duckdb
r1 = duckdb.sql("SELECT 42")
duckdb.sql("SELECT rowid,* FROM r1").show()

OS:

Linux

DuckDB Package Version:

1.3.2

Python Version:

3.13.7

Full Name:

Simeon

Affiliation:

KMI Leipzig

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?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[evertlammerts]

I'm not sure what the rule is but I think rowid doesn't work for non base tables. Or at least not for replacement scanned tables. It won't work with values, subqueries, etc either:

select rowid, 42;
select rowid, * from VALUES(1,2,3), (4,5,6), (7,8,9) as tbl;
select rowid,* from (select * from VALUES(1,2,3), (4,5,6), (7,8,9) as tbl);

... nor for views:

D select rowid,* from test;
┌───────┬────────────────────────┐
│ rowid │           s            │
├───────┼────────────────────────┤
│ 0     │ {'i': 1, 'j': 1}       │
│ 1     │ {'i': 2, 'j': 2}       │
│ 2     │ NULL                   │
│ 3     │ {'i': 4, 'j': NULL}    │
│ 4     │ {'i': NULL, 'j': 5}    │
│ 5     │ {'i': NULL, 'j': NULL} │
└───────┴────────────────────────┘
D create view view1 as select * from test;
D select rowid,* from view1;
Binder Error:
Referenced column "rowid" not found in FROM clause!

What does of course work is just adding a row id like this:

import duckdb
r1 = duckdb.sql("SELECT 42")
duckdb.sql("SELECT row_number() OVER () AS rn,* FROM r1").show()