MySQL error on profile page

[st8st8]

Found a bug? Please fill out the sections below.

Issue Summary

Viewing the account profile page (/account/profile) brings about this error from Django:
ProgrammingError at /accounts/profile/
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."id" FROM "catalogue_category" AS "CATALOGUE_CATEGORY_BASE"\nLEFT JOIN "catalogu' at line 2')

Steps to Reproduce

Log in to Oscar
View the account profile page

Any other relevant information. For example, why do you consider this a bug and what did you expect to happen instead?

It seems like a query template isn't being resolved here. Here's the full query from the MySQL query log: The template seems to be defined in apps/catalogue/expressions.py

SELECT DISTINCT (1) AS a, catalogue_product.id, catalogue_product.structure, catalogue_product.is_public, catalogue_product.upc, catalogue_product.parent_id, catalogue_product.title, catalogue_product.slug, catalogue_product.description, catalogue_product.meta_title, catalogue_product.meta_description, catalogue_product.product_class_id, catalogue_product.rating, catalogue_product.date_created, catalogue_product.date_updated, catalogue_product.is_discountable FROM catalogue_product LEFT OUTER JOIN catalogue_productcategory ON (catalogue_product.id = catalogue_productcategory.product_id) LEFT OUTER JOIN catalogue_productclass ON (catalogue_product.product_class_id = catalogue_productclass.id) LEFT OUTER JOIN offer_range_classes ON (catalogue_productclass.id = offer_range_classes.productclass_id) LEFT OUTER JOIN offer_rangeproduct ON (catalogue_product.id = offer_rangeproduct.product_id) LEFT OUTER JOIN catalogue_product T9 ON (catalogue_product.parent_id = T9.id) LEFT OUTER JOIN catalogue_productcategory T10 ON (T9.id = T10.product_id) LEFT OUTER JOIN catalogue_productclass T12 ON (T9.product_class_id = T12.id) LEFT OUTER JOIN offer_range_classes T13 ON (T12.id = T13.productclass_id) LEFT OUTER JOIN offer_rangeproduct T15 ON (T9.id = T15.product_id) WHERE ((catalogue_productcategory.category_id IN
(SELECT "CATALOGUE_CATEGORY_JOIN"."id" FROM "catalogue_category" AS "CATALOGUE_CATEGORY_BASE"
LEFT JOIN "catalogue_category" AS "CATALOGUE_CATEGORY_JOIN" ON (
"CATALOGUE_CATEGORY_JOIN"."path" LIKE "CATALOGUE_CATEGORY_BASE"."path" || '%'
AND "CATALOGUE_CATEGORY_BASE"."depth" <= "CATALOGUE_CATEGORY_JOIN"."depth"
)
WHERE "CATALOGUE_CATEGORY_BASE"."id" IN (SELECT U0.id FROM catalogue_category U0 INNER JOIN offer_range_included_categories U1 ON (U0.id = U1.category_id) WHERE U1.range_id = 1))
OR offer_range_classes.range_id = 1 OR offer_rangeproduct.range_id = 1 OR T10.category_id IN
(SELECT "CATALOGUE_CATEGORY_JOIN"."id" FROM "catalogue_category" AS "CATALOGUE_CATEGORY_BASE"
LEFT JOIN "catalogue_category" AS "CATALOGUE_CATEGORY_JOIN" ON (
"CATALOGUE_CATEGORY_JOIN"."path" LIKE "CATALOGUE_CATEGORY_BASE"."path" || '%'
AND "CATALOGUE_CATEGORY_BASE"."depth" <= "CATALOGUE_CATEGORY_JOIN"."depth"
)
WHERE "CATALOGUE_CATEGORY_BASE"."id" IN (SELECT U0.id FROM catalogue_category U0 INNER JOIN offer_range_included_categories U1 ON (U0.id = U1.category_id) WHERE U1.range_id = 1))
OR T13.range_id = 1 OR T15.range_id = 1) AND NOT (EXISTS(SELECT (1) AS a FROM offer_range_excluded_products U1 WHERE (U1.range_id = 1 AND U1.product_id = catalogue_product.id) LIMIT 1)) AND NOT (EXISTS(SELECT (1) AS a FROM offer_range_excluded_products U2 WHERE (U2.range_id = 1 AND U2.product_id = catalogue_product.parent_id) LIMIT 1)) AND catalogue_product.id = 79) LIMIT 1
2022-12-23T22:36:33.014749Z 42 Query SELECT DISTINCT catalogue_product.id, catalogue_product.structure, catalogue_product.is_public, catalogue_product.upc, catalogue_product.parent_id, catalogue_product.title, catalogue_product.slug, catalogue_product.description, catalogue_product.meta_title, catalogue_product.meta_description, catalogue_product.product_class_id, catalogue_product.rating, catalogue_product.date_created, catalogue_product.date_updated, catalogue_product.is_discountable FROM catalogue_product LEFT OUTER JOIN catalogue_productcategory ON (catalogue_product.id = catalogue_productcategory.product_id) LEFT OUTER JOIN catalogue_productclass ON (catalogue_product.product_class_id = catalogue_productclass.id) LEFT OUTER JOIN offer_range_classes ON (catalogue_productclass.id = offer_range_classes.productclass_id) LEFT OUTER JOIN offer_rangeproduct ON (catalogue_product.id = offer_rangeproduct.product_id) LEFT OUTER JOIN catalogue_product T9 ON (catalogue_product.parent_id = T9.id) LEFT OUTER JOIN catalogue_productcategory T10 ON (T9.id = T10.product_id) LEFT OUTER JOIN catalogue_productclass T12 ON (T9.product_class_id = T12.id) LEFT OUTER JOIN offer_range_classes T13 ON (T12.id = T13.productclass_id) LEFT OUTER JOIN offer_rangeproduct T15 ON (T9.id = T15.product_id) WHERE ((catalogue_productcategory.category_id IN
(SELECT "CATALOGUE_CATEGORY_JOIN"."id" FROM "catalogue_category" AS "CATALOGUE_CATEGORY_BASE"
LEFT JOIN "catalogue_category" AS "CATALOGUE_CATEGORY_JOIN" ON (
"CATALOGUE_CATEGORY_JOIN"."path" LIKE "CATALOGUE_CATEGORY_BASE"."path" || '%'
AND "CATALOGUE_CATEGORY_BASE"."depth" <= "CATALOGUE_CATEGORY_JOIN"."depth"
)
WHERE "CATALOGUE_CATEGORY_BASE"."id" IN (SELECT U0.id FROM catalogue_category U0 INNER JOIN offer_range_included_categories U1 ON (U0.id = U1.category_id) WHERE U1.range_id = 1))
OR offer_range_classes.range_id = 1 OR offer_rangeproduct.range_id = 1 OR T10.category_id IN
(SELECT "CATALOGUE_CATEGORY_JOIN"."id" FROM "catalogue_category" AS "CATALOGUE_CATEGORY_BASE"
LEFT JOIN "catalogue_category" AS "CATALOGUE_CATEGORY_JOIN" ON (
"CATALOGUE_CATEGORY_JOIN"."path" LIKE "CATALOGUE_CATEGORY_BASE"."path" || '%'
AND "CATALOGUE_CATEGORY_BASE"."depth" <= "CATALOGUE_CATEGORY_JOIN"."depth"
)
WHERE "CATALOGUE_CATEGORY_BASE"."id" IN (SELECT U0.id FROM catalogue_category U0 INNER JOIN offer_range_included_categories U1 ON (U0.id = U1.category_id) WHERE U1.range_id = 1))
OR T13.range_id = 1 OR T15.range_id = 1) AND NOT (EXISTS(SELECT (1) AS a FROM offer_range_excluded_products U1 WHERE (U1.range_id = 1 AND U1.product_id = catalogue_product.id) LIMIT 1)) AND NOT (EXISTS(SELECT (1) AS a FROM offer_range_excluded_products U2 WHERE (U2.range_id = 1 AND U2.product_id = catalogue_product.parent_id) LIMIT 1)) AND catalogue_product.id = 79) ORDER BY catalogue_product.date_created DESC LIMIT 21

Technical details

• Python version: 3.10
• Django version: 3.2.16
• Oscar version: 3.2

[mghiglieri]

Seems similar to #4049

Just tried to fix it. Have a look in #4050