field=None in get_or_create

[David Cramer]

There is a problem when using get_or_create in the following situation:

MyModel.objects.get_or_create(field=None, field1=Hi, defaults=dict(x=1, y=2))

One would expect this to insert field as None, if one doesnt match where field is NULL (how else would propose to approach that?)

The problem is it treats the insert as NULL, but not the select.

This goes along the same lines in Django where None != NULL, which I believe it should.

[James Bennett]

MyModel.objects.get_or_create(field__isnull=True, field1=Hi, defaults=dict(x=1, y=2))

[Malcolm Tredinnick]

I'm going to mark this as a dupe of #2737, since it looks like just a consequence of the select behaviour, as you note. That behaviour's been changed in queyrset-refactor.

[mccc]

Sorry to bring this up again, but I don't think this is either fixed nor a duplicate:
I cannot get the get_or_create operation to create non-existing object with nullable field, getting a DataError: integer out of range error.

The query goes like so: Test.objects.get_or_create(**{u'device_preference__isnull': True, u'test_type': u'TextTest', u'master_id': 1234, u'testset_id__isnull': True}) where device_preference is a nullable PositiveIntegerField and testset_id is a ForeignKey.

Attached traceback shows that the issue happens on creation, I believe.

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/query.py", line 407, in get_or_create
    return self._create_object_from_params(lookup, params)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/query.py", line 439, in _create_object_from_params
    obj = self.create(**params)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/query.py", line 348, in create
    obj.save(force_insert=True, using=self.db)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/base.py", line 734, in save
    force_update=force_update, update_fields=update_fields)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/base.py", line 762, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/base.py", line 846, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/base.py", line 885, in _do_insert
    using=using, raw=raw)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/query.py", line 920, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 974, in execute_sql
    cursor.execute(sql, params)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/mccc/Dev/venvs/tools/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
DataError: integer out of range

[Shai Berger]

Replying to cicuz:

The query goes like so: Test.objects.get_or_create(**{u'device_preference__isnull': True, u'test_type': u'TextTest', u'master_id': 1234, u'testset_id__isnull': True}) where device_preference is a nullable PositiveIntegerField and testset_id is a ForeignKey.

A) Try:

Test.objects.get_or_create(device_preference=None, test_type=u'TextTest', master_id=1234, testset_id=None)

B) In the future, please ask on the django-users mailing list rather than on Trac; you're likely to get faster replies there, as that list is read by many more people than those who read comments on closed tickets.

[mccc]

A) turns out it was actually the master_id foreign key that was being set to garbage, hence the DataError;
everything besides me is working as intended.

B) I did, I just felt that this issue looked really close to what I was experiencing - now I'm glad I chose to investigate more instead of reopening it. Thank you, Shai.