[Django] #35077: Quering with int that has bigint in database no longer working.
Django
-------------------------------------+-------------------------------------
Reporter: Matej | Owner: nobody
Spiller Muys |
Type: Bug | Status: new
Component: Database | Version: 5.0
layer (models, ORM) |
Severity: Normal | Keywords: regression
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
If you have table with bigint column in DB but field is defined as int
(implicit auto field) it no longer selects the value from the database.
It silently returns 0 records without any warning. In 4.2 it returned all
the records regardless of defined type.
Database that is used is mysql 8.
{{{
class BalanceSessionStatus(models.Model):
class Meta(object):
db_table = 'market_balancesession_status'
app_label = 'market'
default_permissions = ('add',)
status = models.PositiveSmallIntegerField(null=False)
created_at = models.DateTimeField(null=False, auto_now=True)
insert_id = 5
# insert_id = 1477468537765888
market_models.BalanceSessionStatus(
id=insert_id,
status=0,
).save()
assert
market_models.BalanceSessionStatus.objects.filter(id=insert_id).exists()
}}}
Table is created with:
{{{
CREATE TABLE `market_balancesession_status` (
`id` bigint NOT NULL,
`status` tinyint NOT NULL,
`created_at` datetime(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
}}}
It works with id = 5 but fails with 1477468537765888. But in 4.2 it
worked.
Same for 4.2 and 5.0
{{{
DEBUG tests.readonly:readonly.py:98 (0.003) UPDATE
`market_balancesession_status` SET `status` = 0, `created_at` =
'2024-01-01 18:38:57.105947' WHERE `market_balancesession_status`.`id` =
5; args=(0, '2024-01-01 18:38:57.105947', 5)
DEBUG tests.readonly:readonly.py:98 (0.002) INSERT INTO
`market_balancesession_status` (`id`, `status`, `created_at`) VALUES (5,
0, '2024-01-01 18:38:57.122702'); args=[5, 0, '2024-01-01
18:38:57.122702']
DEBUG tests.readonly:readonly.py:98 (0.002) SELECT 1 AS `a` FROM
`market_balancesession_status` WHERE `market_balancesession_status`.`id` =
5 LIMIT 1; args=(1, 5)
}}}
5.x (for 1477468537765888)
{{{
DEBUG tests.readonly:readonly.py:98 (0.003) INSERT INTO
`market_balancesession_status` (`id`, `status`, `created_at`) VALUES
(1477468537765888, 0, '2024-01-01 18:42:22.524123');
args=[1477468537765888, 0, '2024-01-01 18:42:22.524123']
}}}
4.2 (for 1477468537765888)
{{{
DEBUG tests.readonly:readonly.py:98 (0.002) UPDATE
`market_balancesession_status` SET `status` = 0, `created_at` =
'2024-01-01 18:45:33.122244' WHERE `market_balancesession_status`.`id` =
1477468537765888; args=(0, '2024-01-01 18:45:33.122244', 1477468537765888)
DEBUG tests.readonly:readonly.py:98 (0.002) INSERT INTO
`market_balancesession_status` (`id`, `status`, `created_at`) VALUES
(1477468537765888, 0, '2024-01-01 18:45:33.129311');
args=[1477468537765888, 0, '2024-01-01 18:45:33.129311']
DEBUG tests.readonly:readonly.py:98 (0.002) SELECT 1 AS `a` FROM
`market_balancesession_status` WHERE `market_balancesession_status`.`id` =
1477468537765888 LIMIT 1; args=(1, 1477468537765888)
}}}
it does not check the if id exists in DB when saving and it does not
select it.
I understand that there is minor inconsistency between model and DB ...
However django shouldn't silently not select the value but should emit a
warning or error if value is invalid.
--
Ticket URL: <https://code.djangoproject.com/ticket/35077>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: regression | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
market_models.BalanceSessionStatus(
id=insert_id,
status=0,
).save()
assert
market_models.BalanceSessionStatus.objects.filter(id=insert_id).exists()
}}}
5.x (for 1477468537765888)
It does not check if the id already exists in DB when saving and it is not
able to select it after insert ... but insert does happen.
{{{
DEBUG tests.readonly:readonly.py:98 (0.003) INSERT INTO
`market_balancesession_status` (`id`, `status`, `created_at`) VALUES
(1477468537765888, 0, '2024-01-01 18:42:22.524123');
args=[1477468537765888, 0, '2024-01-01 18:42:22.524123']
}}}
4.2 (for 1477468537765888)
{{{
DEBUG tests.readonly:readonly.py:98 (0.002) UPDATE
`market_balancesession_status` SET `status` = 0, `created_at` =
'2024-01-01 18:45:33.122244' WHERE `market_balancesession_status`.`id` =
1477468537765888; args=(0, '2024-01-01 18:45:33.122244', 1477468537765888)
DEBUG tests.readonly:readonly.py:98 (0.002) INSERT INTO
`market_balancesession_status` (`id`, `status`, `created_at`) VALUES
(1477468537765888, 0, '2024-01-01 18:45:33.129311');
args=[1477468537765888, 0, '2024-01-01 18:45:33.129311']
DEBUG tests.readonly:readonly.py:98 (0.002) SELECT 1 AS `a` FROM
`market_balancesession_status` WHERE `market_balancesession_status`.`id` =
1477468537765888 LIMIT 1; args=(1, 1477468537765888)
}}}
I understand that there is minor inconsistency between model and DB ...
However django shouldn't silently not select the value but should emit a
warning or error if value is invalid.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/35077#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Matej Spiller Muys | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: regression =>
* type: Bug => Cleanup/optimization
Comment:
Maybe we could add a release note to this effect but I don't think we
should be emitting a warning at run time when this happens. Django won't
generate migration or introspect mismatching column and field types so I
think it's safe to expect that things might break if you venture down this
route.
This relates to #27397.
--
Ticket URL: <https://code.djangoproject.com/ticket/35077#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Matej Spiller Muys | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Matej Spiller Muys):
I agree that this is dangerous behaviour. But if you model django after
existing database it can happen.
It is perfectly fine and appreciated to expose such problems. But hidding
the problem into sometihing that seems like a valid result, it is not.
It seems that
https://github.com/django/django/commit/dde2537fbb04ad78a673092a931b449245a2d6ae
overflow or underflow triggers empty recordset esentially hiding the
problem.
I would expect under or overflow exception to be propagated to the caller.
--
Ticket URL: <https://code.djangoproject.com/ticket/35077#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Matej Spiller Muys | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
> I agree that this is dangerous behaviour. But if you model django after
existing database it can happen.
I would argue that creating your models by hand from an existing database
is dangerous by nature and the reported problem here is just a side
effect. There is [https://docs.djangoproject.com/en/5.0/ref/django-
admin/#inspectdb a documented way of generating models from a pre-existing
table] and it should generate the proper mapping.
> I would expect under or overflow exception to be propagated to the
caller.
It is propagated if you validate the data before hand through validation
errors. If you pass invalid data to a queryset lookup I would say that you
venture into undefined behaviour territory and changing it to raise an
exception would be backward compatible. For example, think of all
`IntegerField` (and it's derivative such as `AutoField` and
`PositiveIntegerField`) which would result in an unhandled exception when
doing `SomeModel.objects.get(pk=pk)` and `pk` is used controlled (e.g.
passed through an url pattern).
The one premise that the ORM and its query generation machinery must be
able to assume is that it has a proper Python level schema mapping to the
tables it's generating SQL for. It you break this premise you should
expect the unexpected.
--
Ticket URL: <https://code.djangoproject.com/ticket/35077#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Matej Spiller Muys | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 5.0
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
Thanks for the report, however I agree with Simon that it's not a valid
issue per se. Using a different data type in the underlying database is
not a ''"minor inconsistency"''.
--
Ticket URL: <https://code.djangoproject.com/ticket/35077#comment:5>