search

etianen / django-watson

Full-text multi-table search application for Django. Easy to install and use, with good performance.
BSD 3-Clause "New" or "Revised" License
1.2k stars 130 forks source link

OperationalError when used with OuterRef and Exists subquery #232

Open OskarPersson opened 6 years ago

OskarPersson commented 6 years ago

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 commented 6 years ago

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 notifications@github.com 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)

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 []>

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 []>

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 "", line 1, in 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 https://github.com/etianen/django-watson/issues/232, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJFCLkXLH3vD6QSOcpF0oKbLVOD7x7Sks5tGhBNgaJpZM4RQgkD .

moggers87 commented 5 years ago

@OskarPersson are you able to test the latest version from git? I think https://github.com/etianen/django-watson/pull/245 may have fixed your issue.

OskarPersson commented 5 years ago

I tried it with master (https://github.com/etianen/django-watson/commit/047007f4550bbb55464b4bf08ffae63d04a61ccf) 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 commented 5 years ago

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 commented 5 years ago

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 notifications@github.com 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 https://github.com/etianen/django-watson/pull/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 https://github.com/etianen/django-watson/issues/232#issuecomment-450411824, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJFCPYAhoF9gbLxxfDruccrOS96tAXFks5u9mzwgaJpZM4RQgkD .