can't convert sql == SELECT COUNT(*) AS "__count" FROM "user_user" WHERE "user_user"."is_superuser"

[Laurel-rao]

One line description of the issue

Python script

<copy and paste code here>

access url  /admin/user/user/?is_superuser__exact=1

DATABASES = {
    'default': {
        'ENGINE': 'djongo',
        'NAME': 'testDB2',
        'ENFORCE_SCHEMA': True,
        'CLIENT': {
            'host': '1.1.1.1',
            'port': 27017,
            'username': 'root',
            'password': 'root',
        }
    }
}

Traceback

[29/Mar/2022 15:06:04] "POST /admin/login/?next=/admin/user/user/%3Fis_superuser__exact%3D1 HTTP/1.1" 302 0
Internal Server Error: /admin/user/user/
Traceback (most recent call last):
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 857, in parse
    return handler(self, statement)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 933, in _select
    return SelectQuery(self.db, self.connection_properties, sm, self._params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 116, in __init__
    super().__init__(*args)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 62, in __init__
    self.parse()
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 152, in parse
    self.where = WhereConverter(self, statement)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\converters.py", line 27, in __init__
    self.parse()
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\converters.py", line 122, in parse
    params=self.query.params
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\operators.py", line 475, in __init__
    self._statement2ops()
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\operators.py", line 438, in _statement2ops
    if prev_op.lhs is None:
AttributeError: 'NoneType' object has no attribute 'lhs'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\cursor.py", line 56, in execute
    params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 784, in __init__
    self._query = self.parse()
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\sql2mongo\query.py", line 885, in parse
    raise exe from e
djongo.exceptions.SQLDecodeError: 

	Keyword: None
	Sub SQL: None
	FAILED SQL: SELECT COUNT(*) AS "__count" FROM "user_user" WHERE "user_user"."is_superuser"
	Params: ()
	Version: 1.3.6

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\cursor.py", line 59, in execute
    raise db_exe from e
djongo.database.DatabaseError

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
    response = get_response(request)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\contrib\admin\options.py", line 616, in wrapper
    return self.admin_site.admin_view(view)(*args, **kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\utils\decorators.py", line 130, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\views\decorators\cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\contrib\admin\sites.py", line 232, in inner
    return view(request, *args, **kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\utils\decorators.py", line 43, in _wrapper
    return bound_method(*args, **kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\utils\decorators.py", line 130, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\contrib\admin\options.py", line 1697, in changelist_view
    cl = self.get_changelist_instance(request)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\contrib\admin\options.py", line 749, in get_changelist_instance
    sortable_by,
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\contrib\admin\views\main.py", line 100, in __init__
    self.get_results(request)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\contrib\admin\views\main.py", line 235, in get_results
    result_count = paginator.count
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\utils\functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\core\paginator.py", line 97, in count
    return c()
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\models\query.py", line 412, in count
    return self.query.get_count(using=self.db)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\models\sql\query.py", line 519, in get_count
    number = obj.get_aggregation(using, ['__count'])['__count']
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\models\sql\query.py", line 504, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\models\sql\compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\backends\utils.py", line 98, in execute
    return super().execute(sql, params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\backends\utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\backends\utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "D:\mypro\workdir\reeko_oa_project\venv\lib\site-packages\djongo\cursor.py", line 59, in execute
    raise db_exe from e
django.db.utils.DatabaseError
[29/Mar/2022 15:06:05] "GET /admin/user/user/?is_superuser__exact=1 HTTP/1.1" 500 235473

[keder-code-hash]

Hey can you be more specific about the error?

[Jcbobo]

I have a very similar issue with a similar query:

       Keyword: None
       Sub SQL: None
       FAILED SQL: ('SELECT (1) AS "a" FROM "account_emailaddress" WHERE ("account_emailaddress"."user_id" = %(0)s AND "account_emailaddress"."verified") LIMIT 1',)
       Params: ((1,),)
       Version: 1.3.6

 Traceback (most recent call last):
   File "/usr/local/lib/python3.9/site-packages/djongo/cursor.py", line 51, in execute
     self.result = Query(
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 784, in __init__
     self._query = self.parse()
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 876, in parse
     raise e
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 857, in parse
     return handler(self, statement)
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 933, in _select
     return SelectQuery(self.db, self.connection_properties, sm, self._params)
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 116, in __init__
     super().__init__(*args)
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 62, in __init__
     self.parse()
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 152, in parse
     self.where = WhereConverter(self, statement)
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/converters.py", line 27, in __init__
     self.parse()
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/converters.py", line 119, in parse
     self.op = WhereOp(
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/operators.py", line 476, in __init__
     self.evaluate()
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/operators.py", line 465, in evaluate
     op.evaluate()
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/operators.py", line 465, in evaluate
     op.evaluate()
   File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/operators.py", line 279, in evaluate
     raise SQLDecodeError
 djongo.exceptions.SQLDecodeError: 
 
        Keyword: None
        Sub SQL: None
        FAILED SQL: ('SELECT (1) AS "a" FROM "account_emailaddress" WHERE ("account_emailaddress"."user_id" = %(0)s AND "account_emailaddress"."verified") LIMIT 1',)
        Params: ((1,),)
        Version: 1.3.6
 
 The above exception was the direct cause of the following exception:
 
 Traceback (most recent call last):
   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
     return self.cursor.execute(sql, params)
   File "/usr/local/lib/python3.9/site-packages/djongo/cursor.py", line 59, in execute
     raise db_exe from e
 djongo.database.DatabaseError
 
 The above exception was the direct cause of the following exception:
 
 Traceback (most recent call last):
   File "/usr/local/lib/python3.9/site-packages/django/core/handlers/exception.py", line 55, in inner
     response = get_response(request)
   File "/usr/local/lib/python3.9/site-packages/django/core/handlers/base.py", line 197, in _get_response
     response = wrapped_callback(request, *callback_args, **callback_kwargs)
   File "/usr/local/lib/python3.9/site-packages/django/views/generic/base.py", line 84, in view
     return self.dispatch(request, *args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/django/utils/decorators.py", line 46, in _wrapper
     return bound_method(*args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/allauth/decorators.py", line 20, in wrap
     resp = function(request, *args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/django/utils/decorators.py", line 46, in _wrapper
     return bound_method(*args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/django/views/decorators/debug.py", line 92, in sensitive_post_parameters_wrapper
     return view(request, *args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/allauth/account/views.py", line 234, in dispatch
     return super(SignupView, self).dispatch(request, *args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/allauth/account/views.py", line 77, in dispatch
     response = super(RedirectAuthenticatedUserMixin, self).dispatch(
   File "/usr/local/lib/python3.9/site-packages/allauth/account/views.py", line 207, in dispatch
     return super(CloseableSignupMixin, self).dispatch(request, *args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/django/views/generic/base.py", line 119, in dispatch
     return handler(request, *args, **kwargs)
   File "/usr/local/lib/python3.9/site-packages/allauth/account/views.py", line 105, in post
     response = self.form_valid(form)
   File "/usr/local/lib/python3.9/site-packages/allauth/account/views.py", line 252, in form_valid
     return complete_signup(
   File "/usr/local/lib/python3.9/site-packages/allauth/account/utils.py", line 186, in complete_signup
     return perform_login(
   File "/usr/local/lib/python3.9/site-packages/allauth/account/utils.py", line 168, in perform_login
     response = adapter.pre_login(request, user, **hook_kwargs)
   File "/usr/local/lib/python3.9/site-packages/allauth/account/adapter.py", line 411, in pre_login
     if not has_verified_email(user, email):
   File "/usr/local/lib/python3.9/site-packages/allauth/account/utils.py", line 135, in has_verified_email
     ret = EmailAddress.objects.filter(user=user, verified=True).exists()
   File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 892, in exists
     return self.query.has_results(using=self.db)
   File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 589, in has_results
     return compiler.has_results()
   File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1329, in has_results
     return bool(self.execute_sql(SINGLE))
   File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1361, in execute_sql
     cursor.execute(sql, params)
   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 103, in execute
     return super().execute(sql, params)
   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
     return self._execute_with_wrappers(
   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
     return executor(sql, params, many, context)
   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
     return self.cursor.execute(sql, params)
   File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
     raise dj_exc_value.with_traceback(traceback) from exc_value
   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
     return self.cursor.execute(sql, params)
   File "/usr/local/lib/python3.9/site-packages/djongo/cursor.py", line 59, in execute
     raise db_exe from e
 django.db.utils.DatabaseError

thi come out during the email verification check performed by django-allauth app.

i'm running mongo in docker container (for development purpose).
this is the DATABASE configuration

DATABASES = {
    'default': {
        'ENGINE': 'djongo',
        'NAME': dev-db,
        'ENFORCE_SCHEMA': True,
        'CLIENT': {
            'host': mongodb://root:example@database/dev-db?retryWrites=true&w=majority&authSource=admin
        }
    }
}

requirements.txt with libs version

asgiref==3.5.1
beautifulsoup4==4.11.1
certifi==2021.10.8
cffi==1.15.0
charset-normalizer==2.0.12
cryptography==37.0.2
defusedxml==0.7.1
Django==4.0.4
django-allauth==0.50.0
django-bootstrap-v5==1.0.11
djongo==1.3.6
dnspython==2.2.1
idna==3.3
oauthlib==3.2.0
pycparser==2.21
PyJWT==2.3.0
pymongo==3.12.1
python3-openid==3.2.0
pytz==2022.1
requests==2.27.1
requests-oauthlib==1.3.1
soupsieve==2.3.2.post1
sqlparse==0.2.4
urllib3==1.26.9

this is the piece of code in the djangoa-allouth library that trigger the error:

def has_verified_email(user, email=None):
    from .models import EmailAddress

    emailaddress = None
    if email:
        ret = False
        try:
            emailaddress = EmailAddress.objects.get_for_user(user, email)
            ret = emailaddress.verified
        except EmailAddress.DoesNotExist:
            pass
    else:
        ret = EmailAddress.objects.filter(user=user, verified=True).exists()
    return ret

the error comes out from the filter

if you need some more information just ask and i will try to provide.

same error persist also using Django==3.2.13.
could you please provide some hint? ( the same query fails also without the exists) is this related to the fact that the models are create from "standard django field" so the djongo package can not be used as ORM for any external apps?

[KonRatt]

I had quite a similar problem with a query like this one:
SELECT * FROM "table" WHERE "table"."bool_field"

It seems like the problem is that djongo can't handle the "boolean condition" and expects "table"."bool_field" = TRUE.
I found that Django has an option for enabling these "boolean condition"s:
https://github.com/django/django/blob/main/django/db/backends/base/operations.py#L648-L653

If i override this config by adding the following to djongo/operations.py, it is compared against TRUE in the SQL and djongo can handle it!

    def conditional_expression_supported_in_where_clause(self, expression):
        return False

I hope this is useful for others finding this issue.

I might open a pull request containing this soon.

[pktiuk]

In my case, solution provided by @KonRatt fixed this issue.

Thanks :D

[yash3004]

thanks @KonRatt you have resolved my issue

[syhanjin]

thanks @KonRatt you have resolved my issue

[Chichiboof]

I had quite a similar problem with a query like this one: SELECT * FROM "table" WHERE "table"."bool_field"

It seems like the problem is that djongo can't handle the "boolean condition" and expects "table"."bool_field" = TRUE. I found that Django has an option for enabling these "boolean condition"s: https://github.com/django/django/blob/main/django/db/backends/base/operations.py#L648-L653

If i override this config by adding the following to djongo/operations.py, it is compared against TRUE in the SQL and djongo can handle it!

    def conditional_expression_supported_in_where_clause(self, expression):
        return False

I hope this is useful for others finding this issue.

I might open a pull request containing this soon.

Thanks very much for the help ^^

[begoon]

@KonRatt's hack is great, and it works for me in Django 4.0. However, in 4.1, it does not. Django SQL compiler goes into an infinite recursion somewhere. I had to downgrade from 4.1 to 4.0 to be able to use allauth with Djongo.

[begoon]

Also, the way how I applied the @KonRatt's patch seamlessly without patching djongo/operations.py manually is this -- I added the following snippet to my settings.py:

from djongo.operations import DatabaseOperations

DatabaseOperations.conditional_expression_supported_in_where_clause = (
    lambda *args, **kwargs: False
)
 

[HardlyForeal]

@begoon, once you implemented @KonRatt's fix, is your infinite loop issue still with the same SQL statement? I have a PR #647 submitted that fixes an infinite loop caused by a new-ish django sql line. I thought it was happening in 4.0 as well, but maybe I skipped a version. For me, it's happening with foreign keys where django used to do "select (1) as a from table.field" whereas now it is doing "select %s as a from table.field" and djongo couldn't handle the parameterized "column". It pulled it as an identifier with the alias "a", but the identifier only referenced itself, so it went into an infinite loop trying to find the table the column was associated with.

[Archie818]

@KonRatt's hack is great, and it works for me in Django 4.0. However, in 4.1, it does not. Django SQL compiler goes into an infinite recursion somewhere. I had to downgrade from 4.1 to 4.0 to be able to use allauth with Djongo.

Thank you begoo! downgrade Django to 4.0 is work for me!