8000 Python register doesn't support schema · Issue #4723 · duckdb/duckdb · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Python register doesn't support schema #4723

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
2 tasks done
luke321321 opened this issue Sep 15, 2022 · 8 comments
Closed
2 tasks done

Python register doesn't support schema #4723

luke321321 opened this issue Sep 15, 2022 · 8 comments
Labels
stale

Comments

@luke321321
Copy link

What happens?

When registering a dataframe the schema isn't interpreted but the table is registered in the temp schema. Instead I'd like to be able to register tables in any schema.

To Reproduce

import pandas as pd
import duckdb
con = duckdb.connect()
df = pd.DataFrame(range(10))

con.execute("create schema stage")
con.register("stage.test_table", df)

When I try to access the table I get the following error

>>> con.execute('select * from stage.test_table').df()
Traceback (most recent call last):
     ...
    con.execute('select * from stage.test_table').df()
RuntimeError: Catalog Error: Table with name test_table does not exist!
Did you mean "pg_catalog.pg_tables"?
LINE 1: select * from stage.test_table

Inspecting the information_schema the table isn't saved in my stage schema but in the temp schema

>>> con.execute("select * from information_schema.tables").df()[["table_schema", "table_name"]]
  table_schema        table_name
0         temp  stage.test_table

Accessing the table using quote identifiers works because of this but isn't the expected behaviour

>>> con.execute('select * from "stage.test_table"').df()
....

If the table is created instead of registered as a view the it works as expected

con.execute(f"create table stage.test_table as select * from test_table")

I've tried this on master (15/9/22) and the issue is still there

OS:

MacOS

DuckDB Version:

0.4.0

DuckDB Client:

Python

Full Name:

Luke Dyer

Affiliation:

Peak AI

Have you tried this on the latest master branch?

  • I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

  • I agree
@Tishj
Copy link
Contributor
Tishj commented Sep 15, 2022

We don't currently support creating a view in a specific schema as far as I can see.
What we could do for now is add a schema layer to the temporary views we store in the connection object.

This way the object would still be stored in the temp schema, but when using the DuckDBPyConnection API that would be hidden from you, and querying 'a.view', 'b.view' would not result in the same view.

@lukedyer-peak
Copy link
Contributor

Currently

select * from "stage.test_table"

does work (with schema=temp and table_name=stage.test_table) as this is equivalent to

select * from temp."stage.test_table"

Just to help me understand, is this what you're suggesting to do (under the hood)?

@Tishj
Copy link
Contributor
Tishj commented Sep 15, 2022

Yes, I started looking into this, but it might be a bit harder than I anticipated, because we would need to intercept the query and check that it's asking for one of the views we registered.

@lukedyer-peak
Copy link
Contributor

It looks from the create view syntax in the docs 'normal' views can be created for a specific schema. I'm guessing this is a unusual view being created.

@Tishj
Copy link
Contributor
Tishj commented Sep 15, 2022

It looks from the create view syntax in the docs 'normal' views can be created for a specific schema. I'm guessing this is a unusual view being created.

Correct, I believe they fall under the temporary views

Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view.

@omo
Copy link
Contributor
omo commented May 12, 2023

Kind of related to #1736, #1848, and #3980.

@github-actions
Copy link

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.

@github-actions github-actions bot added the stale label Aug 11, 2023
@github-actions
Copy link

This issue was closed because it has been stale for 30 days with no activity.

@github-actions github-actions bot closed this as not planned Won't fix, can't repro, duplicate, stale Sep 10, 2023
hawkfish pushed a commit to hawkfish/duckdb that referenced this issue Apr 29, 2025
Internal duckdb#4723: Inequality Condition Pushdown
8a3ff62 
* Push down inequalities into Nested Loop Joins
* Included benchmark runs about 10x faster with the change.
hawkfish pushed a commit to hawkfish/duckdb that referenced this issue May 1, 2025
Internal duckdb#4723: Inequality Condition Pushdown
fbff78b 
* PR Feedback: optional_ptr

fixes: duckdblabs/duckdb-internal#4723
Mytherin added a commit that referenced this issue May 2, 2025
@Mytherin
Internal #4723: Inequality Condition Pushdown (#17317)
60efcc9 
* Push down inequalities into Nested Loop Joins
* Included benchmark runs about 10x faster with the change.
hawkfish pushed a commit to hawkfish/duckdb that referenced this issue May 7, 2025
Internal duckdb#4723: PWMJ Inequality Pushdown
7fd1654 
* Push down inequalities into Piecewise Merge Joins
* Included benchmark runs about 6x (0.19 vs 0.03) faster with the change.

fixes: duckdblabs/duckdb-internal#4797
Mytherin added a commit that referenced this issue May 8, 2025
@Mytherin
Internal #4723: PWMJ Inequality Pushdown (#17400)
e144689 
* Push down inequalities into Piecewise Merge Joins
* Included benchmark runs about 6x (0.19 vs 0.03) faster with the
change.

fixes: duckdblabs/duckdb-internal#4797
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@60efcc9
683b0b2 
Internal duckdb/duckdb#4723: Inequality Condition Pushdown (duckdb/duckdb#17317)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@e144689
1050a5b 
Internal duckdb/duckdb#4723: PWMJ Inequality Pushdown (duckdb/duckdb#17400)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@60efcc9
9320f86 
Internal duckdb/duckdb#4723: Inequality Condition Pushdown (duckdb/duckdb#17317)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@e144689
fdcbd7f 
Internal duckdb/duckdb#4723: PWMJ Inequality Pushdown (duckdb/duckdb#17400)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 19, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@60efcc9
62b620a 
Internal duckdb/duckdb#4723: Inequality Condition Pushdown (duckdb/duckdb#17317)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 19, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@e144689
a48614f 
Internal duckdb/duckdb#4723: PWMJ Inequality Pushdown (duckdb/duckdb#17400)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 19, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@60efcc9
15d18d5 
Internal duckdb/duckdb#4723: Inequality Condition Pushdown (duckdb/duckdb#17317)
krlmlr added a commit to duckdb/duckdb-r that referenced this issue May 19, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@e144689
ed15f00 
Internal duckdb/duckdb#4723: PWMJ Inequality Pushdown (duckdb/duckdb#17400)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
stale
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@omo @luke321321 @Tishj @lukedyer-peak
0