[SOLVED] With clause does not work as expected in Oracle Database

With clause does not work as expected in Oracle Database

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.

Solution

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