[Django] #32593: Functional Index Based on Newly Created Field, invalid input syntax for type integer: "none"
Django
type integer: "none"
---------------------------------------+------------------------
Reporter: mureytasroc | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
---------------------------------------+------------------------
I'm trying to create a functional index based on a newly created field,
but keep getting the following error when migrations are run (using
postgres):
{{{
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type
integer: "none"
}}}
The relevant code (creating a new field "registration_volume" and a
functional index "raw_demand" which should equal
registration_volume/capacity if [capacity is not null and capacity>0],
capacity being another field of the model), in a model called "Section":
{{{
registration_volume = models.PositiveIntegerField(
default=0, help_text="The number of active PCA registrations
watching this section."
)
class Meta:
indexes = [
Index(
Case(
When(
Q(capacity__isnull=False) & Q(capacity__gt=0),
then=(
Cast("registration_volume",
models.DecimalField())
/ Cast("capacity", models.DecimalField())
),
),
default=None,
output_field=models.DecimalField(null=True,
blank=True),
),
name="raw_demand",
),
]
}}}
The auto-generated migration:
{{{
class Migration(migrations.Migration):
dependencies = [
("courses", "0031_userprofile_push_notifications"),
]
operations = [
migrations.AddField(
model_name="section",
name="registration_volume",
field=models.PositiveIntegerField(
default=0, help_text="The number of active PCA
registrations watching this section."
),
),
migrations.AddIndex(
model_name="section",
index=models.Index(
django.db.models.expressions.Case(
django.db.models.expressions.When(
models.Q(("capacity__isnull", False),
("capacity__gt", 0)),
then=django.db.models.expressions.CombinedExpression(
django.db.models.functions.comparison.Cast(
"registration_volume",
models.DecimalField()
),
"/",
django.db.models.functions.comparison.Cast(
"capacity", models.DecimalField()
),
),
),
default=None,
output_field=models.DecimalField(blank=True,
null=True),
),
name="raw_demand",
),
),
]
}}}
It seems to have an issue with this part of the AddIndex migration inside
models.Index(...):
{{{
django.db.models.functions.comparison.Cast(
"registration_volume", models.DecimalField()
)
}}}
The full traceback:
{{{
Run cd backend
cd backend
pipenv run coverage run --concurrency=multiprocessing manage.py test
--settings=PennCourses.settings.ci --parallel
pipenv run coverage combine
shell: sh -e {0}
env: DATABASE_URL: ***postgres:5432/postgres
}}}
{{{
Creating test database for alias 'default'...
Traceback (most recent call last):
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line
82, in _execute
return self.cursor.execute(sql)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type
integer: "none"
LINE 1: ...apacity" > 0) THEN (CAST("registration_volume" AS numeric(No...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "manage.py", line 21, in <module>
main()
File "manage.py", line 17, in main
execute_from_command_line(sys.argv)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/__init__.py",
line 419, in execute_from_command_line
utility.execute()
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/__init__.py",
line 413, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-
packages/django/core/management/commands/test.py", line 23, in
run_from_argv
super().run_from_argv(argv)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line
354, in run_from_argv
self.execute(*args, **cmd_options)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line
398, in execute
output = self.handle(*args, **options)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-
packages/django/core/management/commands/test.py", line 55, in handle
failures = test_runner.run_tests(test_labels)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/test/runner.py", line 723, in
run_tests
old_config = self.setup_databases(aliases=databases)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/test/runner.py", line 641, in
setup_databases
return _setup_databases(
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/test/utils.py", line 179, in
setup_databases
connection.creation.create_test_db(
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/base/creation.py",
line 74, in create_test_db
call_command(
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/__init__.py",
line 181, in call_command
return command.execute(*args, **defaults)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line
398, in execute
output = self.handle(*args, **options)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line
89, in wrapped
res = handle_func(*args, **kwargs)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-
packages/django/core/management/commands/migrate.py", line 244, in handle
post_migrate_state = executor.migrate(
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/executor.py",
line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake,
fake_initial=fake_initial)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/executor.py",
line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake,
fake_initial=fake_initial)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/executor.py",
line 227, in apply_migration
state = migration.apply(state, schema_editor)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/migration.py",
line 126, in apply
operation.database_forwards(self.app_label, schema_editor, old_state,
project_state)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-
packages/django/db/migrations/operations/models.py", line 761, in
database_forwards
schema_editor.add_index(model, self.index)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-
packages/django/db/backends/postgresql/schema.py", line 218, in add_index
self.execute(index.create_sql(model, self, concurrently=concurrently),
params=None)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/base/schema.py",
line 145, in execute
cursor.execute(sql, params)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-
packages/sentry_sdk/integrations/django/__init__.py", line 499, in execute
return real_execute(self, sql, params)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line
66, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line
75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line
82, in _execute
return self.cursor.execute(sql)
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/utils.py", line 90, in
__exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/github/home/.local/share/virtualenvs/backend-
6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line
82, in _execute
return self.cursor.execute(sql)
django.db.utils.DataError: invalid input syntax for type integer: "none"
LINE 1: ...apacity" > 0) THEN (CAST("registration_volume" AS numeric(No...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32593>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
type integer: "none"
Reporter: mureytasroc | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 3.2
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => invalid
Comment:
You cannot `Cast()` to `DecimalField`, or use it in `output_field`,
without providing `decimal_places` and `max_digits` arguments, e.g.
`DecimalField(decimal_places=2, max_digits=10)` works for me. Also, as far
as I'm aware there is no need to use `Cast()` in your case:
{{{
...
then=F("registration_volume") / F("capacity"),
...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32593#comment:1>
Django
-----------------------------+--------------------------------------
Reporter: mureytasroc | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 3.2
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32593#comment:2>
Django
Reporter: Charles Cunningham | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 3.2
Severity: Normal | Resolution: invalid
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 Charles Cunningham):
Replying to [comment:1 Mariusz Felisiak]:
> You cannot `Cast()` to `DecimalField`, or use it in `output_field`,
without providing `decimal_places` and `max_digits` arguments, e.g.
`DecimalField(decimal_places=2, max_digits=10)` works for me. Also, as far
as I'm aware there is no need to use `Cast()` in your case:
> {{{
> ...
> then=F("registration_volume") / F("capacity"),
> ...
> }}}
Wouldn't that result in integer division? Or if the output_field is
DecimalField, will it do decimal division?
--
Ticket URL: <https://code.djangoproject.com/ticket/32593#comment:3>
Django
------------------------------------+--------------------------------------
Type: Bug | Status: closed
Component: Migrations | Version: 3.2
Severity: Normal | Resolution: invalid
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 Mariusz Felisiak):
Replying to [comment:3 Charles Cunningham]:
> Wouldn't that result in integer division?
True, it depends on a database but on PostgreSQL you need to `Cast()` at
least one of them.
--
Ticket URL: <https://code.djangoproject.com/ticket/32593#comment:4>