Pivot statement unsupported? #57
Comments
|
Ah, yes, this is a known problem in the interaction between now PIVOT is implemented and the We can probably address this by using |
|
Is there a workaround currently? |
|
There's no workaround that I'm aware of. |
Standard SQL PIVOT syntax can still be used in the UI, but it is very clumsy compared to the simplified syntax. -- example using Simplified PIVOT syntax
-- https://duckdb.org/docs/stable/sql/statements/pivot.html#pivot-on-and-using
PIVOT cities
ON year
USING sum(population);Becomes -- example using SQL Standard PIVOT Syntax
-- https://duckdb.org/docs/stable/sql/statements/pivot.html#examples
SELECT *
FROM cities
PIVOT (
sum(population)
FOR
year IN (2000, 2010, 2020)
GROUP BY country, name
);I don't know how to get around having to know all the possible years in data when writing the query. |
|
Right, one of the main features of DuckDB's PIVOT is its ability to generate the necessary columns. Unfortunately it is exactly this feature that results in multiple statements being run behind the scenes, which leads to the issue. Some kind of change will be needed to either DuckDB or the UI extension (or both) for this to work. |
Thnak you! Hopefully, i guess this is a subject that you have been discussing with the DuckDB team to find a solution? Meanwhile, it is a little bit difficult to advocate UI as a generic interface for DuckDB without Pivot/Unpivot support |
|
If this is something that is not easily fixed, can I propose adding a hardcoded error message for cells that use duckdb pivot as a temporary measure? As an end-user, it is hard to understand that |
|
This is definitely something we're looking into, but it's not trivial to fix so it may take some time. Unfortunately even detecting when this will happen isn't straightforward, so displaying a better error message isn't easy, either. We'll try to understand how long this might take to address, which will help inform whether investing in producing a better error message is worthwhile. |
|
I learned something recently (how the Python client addresses this problem) that points towards a solution that wouldn't involve changes to DuckDB core. That increases the chances this can be fixed in the near future (time permitting). (Details: The Python client uses ExtractStatements to split input text into parsed statements before running each through PendingQuery. I believe this handles the case of PIVOT, which looks like a single statement but generates multiple behind the scenes (which is the cause of this issue); ExtractStatements should return multiple parsed statement objects in this case. This pattern shouldn't be hard to implement in the UI extension.) |
Here's the statement it generates behind the hood: CREATE TEMPORARY TYPE __pivot_enum_0_0 AS ENUM (
SELECT DISTINCT
year::VARCHAR
FROM cities
ORDER BY
year
);
PIVOT cities
ON year IN __pivot_enum_0_0
USING sum(population);
|
Second cell:
=>
Invalid Input Error: Cannot prepare multiple statements at once!The text was updated successfully, but these errors were encountered: