#35077: Quering with int that has bigint in database no longer working.
-------------------------------------+-------------------------------------
Reporter: Matej Spiller Muys | Owner: nobody
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
-------------------------------------+-------------------------------------
Description changed by Matej Spiller Muys:
Old description:
> 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.
New description:
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)
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018cc65c07a4-eb0539ba-da2c-4103-ade3-3f97d72cb7ff-000000%40eu-central-1.amazonses.com.
