queryset.exists() returns False when queryset is distinct and sliced

[bitrut]

Consider this:

User.objects.count()
3

This queryset

User.objects.all()[1:].exists()
True

produces SQL

SELECT (1) AS "a" FROM "user" LIMIT 1 OFFSET 1

so the result is correct.
But when you add distinct():

User.objects.distinct()[1:].exists()
False

the SQL is

SELECT DISTINCT (1) AS "a" FROM "user" LIMIT 1 OFFSET 1

which returns nothing, so the exists() returns False which is incorrect.

DISTINCT narrows results to just one 1 and OFFSET omits the first result and goes to the next one which does not exist.
It's because DISTINCT has the higher priority than OFFSET in SQL.

I don't know the perfect solution. Maybe in the case of using distinct(), slicing and exists() together an exception should be thrown. Or the exists() function should notice this case and call count(), but this would lead to unexpected DB usage.

[Luke Plant]

QuerySet.exists() should throw an exception for this case, and possibly for any case involving slicing - I don't think it was designed to be correct in the presence of LIMIT/OFFSET. However, for the moment probably best to limit to the distinct()+slicing()+exists() combination.

[err]

Here is my pull request: ​https://github.com/django/django/pull/161

[Anssi Kääriäinen]

The pull request doesn't seem to do what Luke asked - it disallows the usage in any filtered situation, not just in conjunction with .distinct(). It seems likely there are users relying on this currently.

As for fixing the distinct() + limit + .exists() - it should be done by not doing select distinct(1) but instead select distinct {normal select fields} instead. I wonder if this would be straightforward to do?

[Anssi Kääriäinen]

It seems this will be relatively easy to fix, just do

if not q.distinct:
    q.clear_select_clause()

instead of always doing clear_select_clause(). I'll test this & commit if things seem to work OK.

[Anssi Kääriäinen <akaariai@…>]

In 93cc6dcdac6fc3e506640fa38dd1798c3cd61cff:

Fixed #18414 -- qs.exists() for sliced distinct queries

[Mariusz Felisiak <felisiak.mariusz@…>]

In d2263b7b:

Refs #18414 -- Added tests for selected columns of sliced distinct querysets.