OperationalError when used with OuterRef and Exists subquery

[OskarPersson]

When using watson and a OuterRef filter inside a Exists subquery, OperationalError is raised.

Here is an example:

class Author(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=200)

class Book(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, null=True)

Python 2.7.14 (default, Sep 29 2017, 15:33:24)
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.37)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)

>>> from django.db.models import Exists, OuterRef
>>> from watson import search as watson
>>> from books.models import Author, Book

>>> author = Author.objects.create(name="Me")
>>> Book.objects.create(title="My book", author=author)
<Book: Book object>

>>> # try only django filter
>>> nested_exists_query = Book.objects.filter(author_id=OuterRef('id'))
>>> Author.objects.annotate(watson_nested_exists=Exists(nested_exists_query)).filter(watson_nested_exists=True)
<QuerySet [<Author: Author object>]>

>>> # try only watson filter
>>> nested_exists_query = watson.filter(Book, "book")
>>> Author.objects.annotate(watson_nested_exists=Exists(nested_exists_query)).filter(watson_nested_exists=True)
<QuerySet [<Author: Author object>]>

>>> # try both
>>> nested_exists_query = watson.filter(Book, "book").filter(author_id=OuterRef('id'))
>>> Author.objects.annotate(watson_nested_exists=Exists(nested_exists_query)).filter(watson_nested_exists=True)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 226, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 250, in __iter__
    self._fetch_all()
  File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 1118, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
  File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 894, in execute_sql
    raise original_exception
OperationalError: no such column: books_book.id

[etianen]

Ouch. I'm not sure how to go about fixing this. Watson uses queryset.extra() to perform filtering, which necessitates referring to the table name in raw SQL. It looks like using OuterRef results in the books_book table being given an alias name, which breaks the raw SQL generated by watson.

…

On 2 January 2018 at 11:17, Oskar Persson ***@***.***> wrote: When using watson and a OuterRef <https://docs.djangoproject.com/en/2.0/ref/models/expressions/#django.db.models.OuterRef> filter inside a Exists <https://docs.djangoproject.com/en/2.0/ref/models/expressions/#exists-subqueries> subquery, OperationalError <https://docs.djangoproject.com/en/2.0/ref/exceptions/#django.db.OperationalError> is raised. Here is an example: class Author(models.Model): id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False) name = models.CharField(max_length=200) class Book(models.Model): id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False) title = models.CharField(max_length=200) author = models.ForeignKey(Author, on_delete=models.CASCADE, null=True) Python 2.7.14 (default, Sep 29 2017, 15:33:24) [GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.37)] on darwin Type "help", "copyright", "credits" or "license" for more information. (InteractiveConsole) >>> from django.db.models import Exists, OuterRef>>> from watson import search as watson>>> from books.models import Author, Book >>> author = Author.objects.create(name="Me")>>> Book.objects.create(title="My book", author=author)<Book: Book object> >>> # try only django filter>>> nested_exists_query = Book.objects.filter(author_id=OuterRef('id'))>>> Author.objects.annotate(watson_nested_exists=Exists(nested_exists_query)).filter(watson_nested_exists=True)<QuerySet [<Author: Author object>]> >>> # try only watson filter>>> nested_exists_query = watson.filter(Book, "book")>>> Author.objects.annotate(watson_nested_exists=Exists(nested_exists_query)).filter(watson_nested_exists=True)<QuerySet [<Author: Author object>]> >>> # try both>>> nested_exists_query = watson.filter(Book, "book").filter(author_id=OuterRef('id'))>>> Author.objects.annotate(watson_nested_exists=Exists(nested_exists_query)).filter(watson_nested_exists=True) Traceback (most recent call last): File "<console>", line 1, in <module> File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 226, in __repr__ data = list(self[:REPR_OUTPUT_SIZE + 1]) File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 250, in __iter__ self._fetch_all() File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 1118, in _fetch_all self._result_cache = list(self._iterable_class(self)) File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/query.py", line 53, in __iter__ results = compiler.execute_sql(chunked_fetch=self.chunked_fetch) File "/Users/Oskar/.pyenv/versions/watson/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 894, in execute_sql raise original_exception OperationalError: no such column: books_book.id — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#232>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJFCLkXLH3vD6QSOcpF0oKbLVOD7x7Sks5tGhBNgaJpZM4RQgkD> .

[moggers87]

@OskarPersson are you able to test the latest version from git? I think #245 may have fixed your issue.

[OskarPersson]

I tried it with master (047007f) and I'm still getting the same error (note that I now used Python 3.7 instead of 2.7 and Django 2.1.4 instead of 1.11):

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/models/query.py", line 244, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/models/query.py", line 268, in __iter__
    self._fetch_all()
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/models/query.py", line 1186, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/models/query.py", line 54, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1065, in execute_sql
    cursor.execute(sql, params)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/Oskar/.pyenv/versions/django-watson/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 296, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: books_book.id

[moggers87]

OK, so we need to remove the remaining QuerySet.extra. That's going to be quite a bit of work!

The main issue is that we're adding things to the WHERE clause, e.g. https://github.com/etianen/django-watson/blob/master/watson/backends.py#L262

I don't think we can put that as an annotation (as I did in #245) and MyModel.objects.filter(RawSQL("blah blah blah")) results in errors. Any ideas?

[etianen]

Yes, I think it'll be really tricky to do. I'll gratefully review a merge request that removes the use of `extra`.

…

On Fri, 28 Dec 2018 at 19:11, Matt Molyneaux ***@***.***> wrote: OK, so we need to remove the remaining QuerySet.extra. That's going to be quite a bit of work! The main issue is that we're adding things to the WHERE clause, e.g. https://github.com/etianen/django-watson/blob/master/watson/backends.py#L262 I don't think we can put that as an annotation (as I did in #245 <#245>) and MyModel.objects.filter(RawSQL("blah blah blah")) results in errors. Any ideas? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#232 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJFCPYAhoF9gbLxxfDruccrOS96tAXFks5u9mzwgaJpZM4RQgkD> .