id,summary,reporter,owner,description,type,status,component,version,severity,resolution,keywords,cc,stage,has_patch,needs_docs,needs_tests,needs_better_patch,easy,ui_ux 13768,'exclude' does not work with lists containing a 'None' element.,Felipe 'chronos' Prenholato,nobody,"This ticket covers use of 'None' (NULL for DB) in list of one '__in' lookup. Postgres at least, when run a query with a 'WHERE' like ""object_id in (NULL,'other val')"" returns a query with no rows, and ORM too, what is a problem because developer can think that query is right, so as Alex Gaynor said in #django-dev 'we can rewrite it as (value IN (other, vals) OR value IS NULL)' or raise a error in database backend (since in ORM we can't thinking that mongodb would allow it as 'apollo13' said). My vode is to raise a error in database backend. Here is code in python shell that show the problem: '''In Django 1.1.X''' {{{ >>> LogEntry.objects.exclude(object_id=None).count() 274527 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT (""django_admin_log"".""object_id"" IS NULL)', 'time': '0.101'} >>> LogEntry.objects.exclude(object_id__in=(None,)).count() 0 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT (""django_admin_log"".""object_id"" IN (NULL) AND NOT (""django_admin_log"".""object_id"" IS NULL))', 'time': '0.085'} >>> LogEntry.objects.exclude(object_id=None,object_id='None').count() 274443 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT (""django_admin_log"".""object_id"" = E\'None\' AND NOT (""django_admin_log"".""object_id"" IS NULL))', 'time': '0.122'} >>> LogEntry.objects.exclude(object_id__in=(None,'None')).count() 0 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT (""django_admin_log"".""object_id"" IN (NULL, E\'None\') AND NOT (""django_admin_log"".""object_id"" IS NULL))', 'time': '0.126'} }}} '''Django 1.2''' {{{ >>> LogEntry.objects.exclude(object_id=None).count() 274527 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT (""django_admin_log"".""object_id"" IS NULL)', 'time': '0.104'} >>> LogEntry.objects.exclude(object_id__in=(None,)).count() 0 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT ((""django_admin_log"".""object_id"" IN (NULL) AND NOT (""django_admin_log"".""object_id"" IS NULL)))', 'time': '0.088'} >>> LogEntry.objects.exclude(Q(object_id=None),Q(object_id='None')).count() 274527 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT (""django_admin_log"".""object_id"" IS NULL AND ""django_admin_log"".""object_id"" = E\'None\' )', 'time': '0.114'} >>> LogEntry.objects.exclude(object_id__in=(None,'None')).count() 0 >>> connection.queries[-1] {'sql': 'SELECT COUNT(*) FROM ""django_admin_log"" WHERE NOT ((""django_admin_log"".""object_id"" IN (NULL, E\'None\') AND NOT (""django_admin_log"".""object_id"" IS NULL)))', 'time': '0.120'} }}} ",Bug,closed,"Database layer (models, ORM)",1.2,Normal,duplicate,"None, NULL, in, postgres, postgresql",niels.busch@…,Accepted,0,0,0,0,0,0