With clause does not work as expected in Oracle Database

Question

I'm using pypika to build some queries. It used to work great but I have an issue with subqueries on an oracle database. The query reads as follow

fake_query = Query().from_(my_table).where(my_table.ID == "12345").select(my_table.ID)

QN = AliasedQuery("fake_query_with")

query = (
    Query()
    .with_(fake_query, "fake_query_with")
    .from_(QN)
    .select(
        QN.star. # problematic line
    )
)

df_temp = claim_conn.read_dataframe(query.get_sql())

So when I run the script, I got an error:

DatabaseError: ORA-00904: "fake_query_with": invalid identifier

The pypika query is translated as expected as the string

WITH fake_query_with AS 
(
   SELECT "ID" 
   FROM "MYTABLE" 
   WHERE "ID"=12345
) 
SELECT "fake_query_with".* 
FROM fake_query_with

So this query fails, but if I replace QN.star by simply *, it works, but of course I loose the interesting usage of the alias. I know this is just a dummy snippet but the it is just to demonstrate the error.

Answer 1

As @astentx said in a comment, this looks like a bug in PyPika.

You could (probably) work around it by making the name uppercase in your code - i.e. "FAKE_QUERY_WITH" in both places.

QN = AliasedQuery("FAKE_QUERY_WITH")

query = (
    Query()
    .with_(fake_query, "FAKE_QUERY_WITH")
    .from_(QN)
    .select(
        QN.star. # problematic line
    )
)

Then the generated SQL should look like:

WITH FAKE_QUERY_WITH AS 
(
   SELECT "ID" 
   FROM "MYTABLE" 
   WHERE "ID"=12345
) 
SELECT "FAKE_QUERY_WITH".* 
FROM FAKE_QUERY_WITH

and the distinction between the quoted and unquoted identifiers will be moot.

fiddle showing that modified SQL works - untested in PyPika but hopefully that is actually what it will generate...