“no such column”/“column does not exist” errors in Django 4.2 #66
Comments
dimagi/django-cte#66 Signed-off-by: Anders Kaseorg <anders@zulip.com>
dimagi/django-cte#66 Signed-off-by: Anders Kaseorg <anders@zulip.com>
|
Recording some notes, mostly thinking out loud. Unfortunately the In some cases Scenario 1: compiler adding the alias is passed to def make_root_mapping(rootmap):
return Region.objects.filter(
parent__isnull=True
).values(
"name", # SQLCompiler adds an alias to this column because of `with_col_aliases=True`
root=F("name"),
).union(
# Reference to rootmap.col.name, which is aliased by SQLCompiler.
# Here would be possible for <CTEColumn rootmap.name> to get the alias from
# the compiler, although it would require a deep reach.
rootmap.join(Region, parent=rootmap.col.name).values(
"name",
root=rootmap.col.root,
),
all=True,
)Scenario 2: compiler adding the alias is not passed to # Reference to rootmap.col.name, which has been aliased by SQLCompiler.
# There is no easy way for CTEColumn to get the alias because it does not have
# a reference to the compiler that created the alias in this context.
rootmap.join(Order, region_id=rootmap.col.name) |
dimagi/django-cte#66 Signed-off-by: Anders Kaseorg <anders@zulip.com>
|
Just commenting to document I am seeing the same problem with Django 4.2 and django_cte manifesting itself as |
|
I don't have a timeline. Pull requests are welcome. |
|
I just tried following the README, and running tests and indeed it fails with: and I can see: As an aside nose is also broken with Python 3.10 and so: #73 |
|
Reading @millerdev's notes to date, I took a look at the SQL generated in WITH RECURSIVE "rootmap" AS
(SELECT "region"."name" AS "col1",
"region"."name" AS "root"
FROM "region"
WHERE "region"."parent_id" IS NULL
UNION ALL SELECT "region"."name" AS "col1",
"rootmap"."root" AS "root"
FROM "region"
INNER JOIN "rootmap" ON "region"."parent_id" = ("rootmap"."name")),
"totals" AS
(SELECT "rootmap"."root" AS "root",
COUNT("orders"."id") AS "orders_count",
SUM("orders"."amount") AS "region_total"
FROM "orders"
INNER JOIN "rootmap" ON "orders"."region_id" = ("rootmap"."name")
GROUP BY 1)
SELECT "region"."name",
"region"."parent_id",
"totals"."orders_count" AS "orders_count",
"totals"."region_total" AS "region_total"
FROM "region"
INNER JOIN "totals" ON "region"."name" = ("totals"."root")And I suspect the issue (am learning here) is that pre-Django4.2 it was perhaps: WITH RECURSIVE "rootmap" AS
(SELECT "region"."name",
"region"."name" AS "root"
FROM "region"
WHERE "region"."parent_id" IS NULL
UNION ALL SELECT "region"."name" AS,
"rootmap"."root" AS "root"
FROM "region"
INNER JOIN "rootmap" ON "region"."parent_id" = ("rootmap"."name")),
"totals" AS
(SELECT "rootmap"."root" AS "root",
COUNT("orders"."id") AS "orders_count",
SUM("orders"."amount") AS "region_total"
FROM "orders"
INNER JOIN "rootmap" ON "orders"."region_id" = ("rootmap"."name")
GROUP BY 1)
SELECT "region"."name",
"region"."parent_id",
"totals"."orders_count" AS "orders_count",
"totals"."region_total" AS "region_total"
FROM "region"
INNER JOIN "totals" ON "region"."name" = ("totals"."root")That is visible in And the challenge as I read the out-loud thinking is that
Anyhow, I too am just thinking out load, and leaving some notes. |
|
More notes: The breaking change was added in django commit 70499b2 and related to 8c3046d You can just add the following to CTEQueryCompiler.as_sql to fix all the tests but i have the feeling this is not a good idea I have a set of fixes that, improves alias support to the cte columns and link them so changing the cte alias changes the col alias This is needed so that the joins and where clauses are updated. |
|
To prevent anyone starting afresh. Whilst it passes all the check the test coverage isn't as good. Ideally if someone what to write a test against where the CTE query triggers any of the related django bugs, i would be grateful. I yet to identify them but here are two that i found so far,
I need to ensure that the new changes dont revert these fixes, the last one is significant as i had to manipulate the col aliases to ensure they match the column names. |
|
My company is hitting this issue as well. What is needed for review on #78? |
|
the solution of removing # simple model
class Population(models.Model):
class Meta:
db_table = "population"
year = models.PositiveIntegerField()
population = models.BigIntegerField()
objects = CTEManager()def get_population_queryset(
start_year: int,
) -> QuerySet:
population_queryset_one = Population.objects.filter(
year=start_year,
).order_by("year")
population_queryset_second = Population.objects.filter(
year=start_year + 10,
).order_by("year")
union_queryset = population_queryset_one.union(
population_queryset_second,
).order_by("year")
print(
'--DJANGO QUERY : \n',
union_queryset.query
)
return union_queryset
def calculate_population(
start_year: int,
):
queryset = get_population_queryset(
start_year=start_year,
)
cte = With(
queryset,
name="population_cte",
)
print(
'--CTE (WITH) QUERY : \n',
cte.query
)
populations = (
cte.queryset().with_cte(
cte
).order_by('year')
)
print(
'--POPULATIONS QUERY : \n',
populations.query
)
print(populations)--DJANGO QUERY :
(
SELECT
"population"."id" AS "col1",
"population"."year" AS "col2",
"population"."population" AS "col3"
FROM
"population"
WHERE
"population"."year" = 1976
ORDER BY
"population"."year" ASC
)
UNION
(
SELECT
"population"."id" AS "col1",
"population"."year" AS "col2",
"population"."population" AS "col3"
FROM
"population"
WHERE
"population"."year" = 1986
ORDER BY
"population"."year" ASC
)
ORDER BY
"col2" ASC
--CTE (WITH) QUERY :
(
SELECT
"population"."id" AS "col1",
"population"."year" AS "col2",
"population"."population" AS "col3"
FROM
"population"
WHERE
"population"."year" = 1976
ORDER BY
"population"."year" ASC
)
UNION
(
SELECT
"population"."id" AS "col1",
"population"."year" AS "col2",
"population"."population" AS "col3"
FROM
"population"
WHERE
"population"."year" = 1986
ORDER BY
"population"."year" ASC
)
ORDER BY
"col2" ASC
--POPULATIONS QUERY :
WITH RECURSIVE "population_cte" AS (
(
SELECT
"population"."id",
"population"."year",
"population"."population"
FROM
"population"
WHERE
"population"."year" = 1976
ORDER BY
"population"."year" ASC
)
UNION
(
SELECT
"population"."id",
"population"."year",
"population"."population"
FROM
"population"
WHERE
"population"."year" = 1986
ORDER BY
"population"."year" ASC
)
ORDER BY
"col2" ASC
)
SELECT
"population_cte"."id",
"population_cte"."year",
"population_cte"."population"
FROM
"population_cte"
ORDER BY
"population_cte"."year" ASCas you can see on the |
With Django 4.2, django-cte causes SQL errors in production as well as its own test suite. I’ve bisected the failure to django/django@70499b2 which addressed https://code.djangoproject.com/ticket/34123. That commit results in extra aliases in the generated SQL:
which breaks the references to
rootmap.name:The text was updated successfully, but these errors were encountered: