Is it possible to consider adding support for Oracle Database?

[smhegde-1979]

Currently, django-cte hard codes "WITH RECURSIVE" based on postgres recommendations. This is just a straight up syntax error. This unfortunately restricts use to postgres (and other DBs that support RECURSIVE).

Is it possible / feasible to consider adding support for Oracle back-end?

Also, we already have a custom Manager for our models, so are there any examples of using CTEManager in addition to our manager?
I think inheriting from django_cte classes has some issues. For e.g. in "With.queryset", an object of CTEQuery is directly instantiated so any subclass I have created from CTEQuery gets ignored.

[millerdev]

@smhegde-1979 would it work on Oracle if the RECURSIVE keyword were added on recursive queries only, or are other changes required in addition to that?

Also, we already have a custom Manager for our models, so are there any examples of using CTEManager in addition to our manager?

Have you looked at the Custom QuerySets and Managers documentation? Is it possible to make your custom manager extend CTEManager?

in "With.queryset", an object of CTEQuery is directly instantiated so any subclass I have created from CTEQuery gets ignored.

Can you provide a code example that demonstrates the issue and report it as a new issue, please?

[smhegde-1979]

Regarding your first question - If the RECURSIVE keyword is removed, I believe it works for my straightforward use cases.
Regarding the custom-managers, yes I looked into that and that is what I am doing. Good to know that is the way.

For the RECURSIVE keyword realted changes itself, would you need a code example? Really, its any of your examples but the back-end needs to be oracle to reproduce this issue.

[mardukbp]

WITH RECURSIVE is not supported by Oracle, SQL Server and DB2. However, they do support CTEs using WITH.

The documentation of this library should include the database- and version-compatibility information from the link above, so that prospective users know what its limitations are.

Even better would be if it supported CTEs in all databases using WITH and for databases that support WITH RECURSIVE the functionality is provided separately.

Thank you for this great library!

[ilkkao]

Additional reason why it would be great to be able to manually force just WITH instead of WITH RECURSIVE is:

    pre_filter = With(
       User.objects.all().order_by('id')[:100], name=User._meta.db_table
    )
    User.objects.with_cte(pre_filter).filter(...main_filter...)

Simplified example. Slightly hacky but the Idea is to shadow the real table with CTE. This currently fails because of built-in django check:

django.db.utils.ProgrammingError: recursive query "user_user" does not have the form non-recursive-term UNION [ALL] recursive-term

[millerdev]

@ilkkao is that an Oracle error, or are you seeing that on Postgres or SQLite?

[ilkkao]

@millerdev sorry forgot to mention that. Mine is a postgres error. Example does what I like when I change WITH RECURSIVE to WITH in query.py

[millerdev]

@ilkkao I was able to reproduce the error, but it's because of the cte name, which you have set to the table name, resulting in an invalid recursive query (note I swapped User with Order in this test).

    def test_recursive_error(self):
        pre_filter = With(
            Order.objects.all().order_by('id')[:100],
            name=Order._meta.db_table,
        )
        query = Order.objects.with_cte(pre_filter).filter(amount=42)
        print(query.query)

        self.assertEqual([o.region.name for o in query], ['mars'])

Printed SQL:

WITH RECURSIVE "orders" AS (
    SELECT
        "orders"."id",
        "orders"."region_id",
        "orders"."amount",
        "orders"."user_id"
    FROM
        "orders"  -- NOTE invalid recursive reference to "orders" CTE
    ORDER BY
        "orders"."id" ASC
    LIMIT
        100
)
SELECT
    "orders"."id",
    "orders"."region_id",
    "orders"."amount",
    "orders"."user_id"
FROM
    "orders"
WHERE
    "orders"."amount" = 42

The error from Postgres makes it seem like the RECURSIVE keyword is the problem (it is not). SQLite gives a more informative error:

OperationalError: circular reference: orders

SQLite produces this error even if the RECURSIVE keyword is removed from the query. Postgres does not, but I think that's because it ignores the invalid CTE query since it is not referenced in the main query.

The query works on both Postgres and SQLite when a unique CTE name is specified:

    def test_recursive_error(self):
        pre_filter = With(
            Order.objects.all().order_by('id')[:100],
            name=Order._meta.db_table + '_cte',
        )
        query = Order.objects.with_cte(pre_filter).filter(amount=42)
        print(query.query)

        self.assertEqual([o.region.name for o in query], ['mars'])

[ilkkao]

@millerdev that is the exact problem indeed. Recursion doesn't happen with just WITH. When you give a separate name for the pre_filter, it doesn't shadow the table name anymore. In my example I'm relying on name shadowing in search path. I want the main query to use the CTE without any modifications, not to use the real table as the source as it's doing by default.