CTE statement is not being generated

[dimagi]

CTE statement is not being generated

[millerdev]

This is caused by a "feature" of django that removes a UNION clause if django can deduce that the unioned query will return no rows (when the WHERE clause will always evaluate to false, for example). Prior to django-cte, this was probably a safe thing to do since standard union queries do not reference names defined outside of the query as recursive CTEs do.

My guess is the filter on the first CTE query has an empty list:

return Annotation.objects.filter(
    # start with root nodes
    id__in=annotation_ids  # <---- annotation_ids is probably an empty list

You can work around this by checking if the query is empty first, and not doing the CTE in that case:

roots = Annotation.objects.filter(id__in=annotation_ids)

if roots.query.is_empty():
    # construct a query that does not use a CTE since the CTE is empty
    ...
else:
    # construct CTE query as in your example
    ...

Another way to work around it would be to add an expression that will fool django. Something like this (although I'm fairly certain Literal doesn't work this way so this is not a working example):

return Annotation.objects.filter(
    # start with root nodes
    Q(id__in=annotation_ids) |
    Q(Literal('1 = 2'))  # <--- broken example. Literal doesn't work like that
).values(
    ...

[nirizr]

Thanks! That clarification really made it a lot clearer!
I've managed to notice I had a typo in the parameter I was reading from the query.

I'm wondering if you think it's worth the trouble of trying to detect (and warn users) somehow?

[millerdev]

Adding a warning is good idea. I'll see what I can do.

[nirizr]

Cool!

I would have given it a shot myself, but to be honest I don't quite understand how this works behind the scenes, definitely not well enough to do something like that.