Subquery inside CASE...WHEN... should not have alias added

[lephuongbg]

Given following example of using a subquery inside a CASE...WHEN... expression:

class OrderItems(p.Model):
    quantity = p.IntegerField()
    metadata = BinaryJSONField()

products_recordset = fn.jsonb_populate_recordset(
    p.SQL("NULL::universal.al_items"), OrderItems.metadata["products"].as_json()
).alias("products_recordset")

print(
    OrderItems.select(
        p.Case(
            OrderItems.metadata["price"].is_null(),
            [
                (
                    True,
                    ##### The subquery is here ######
                    p.Select(
                        columns=[
                            fn.sum(
                                products_recordset.c.price
                                * products_recordset.c.quantity
                            )
                        ],
                        from_list=[products_recordset],
                    ),
                )
            ],
            OrderItems.metadata["price"].cast("text").cast("double precision")
            * OrderItems.quantity,
        ),
    )
)

Expected:

The desired SQL output should be as follow:

SELECT CASE ("t1"."metadata" ->> 'price' IS NULL)
           WHEN 1 THEN (SELECT sum("products_recordset"."price" * "products_recordset"."quantity")
                        FROM jsonb_populate_recordset(NULL::universal.al_items,
                                                      "t1"."metadata" -> 'products') AS "products_recordset")
           ELSE (CAST(CAST("t1"."metadata" ->> 'price' AS text) AS double precision) * "t1"."quantity") END
FROM "orderitems" AS "t1";

Current output:

An extra alias was added to the subquery, causing invalid syntax.

SELECT CASE ("t1"."metadata" ->> 'price' IS NULL)
           WHEN 1 THEN (SELECT sum("products_recordset"."price" * "products_recordset"."quantity")
                        FROM jsonb_populate_recordset(NULL::universal.al_items, "t1"."metadata" ->
                                                                                'products') AS "products_recordset") AS "t2"  --- <---- Note the extra 'as "t2"' here
            ELSE (CAST(CAST("t1"."metadata"->>'price' AS text) AS double precision) * "t1"."quantity") END
FROM "orderitems" AS "t1";

Database is PostgreSQL.

[lephuongbg]

Adding in_query=True to the context here would solve this particular issue, but I don't know whether it would cause other regressions.

https://github.com/coleifer/peewee/blob/c5aa497b1365b583bf372fe16f5fc24d07db3e09/peewee.py#L1845

[coleifer]

Thanks, this should be fixed now.

SELECT 
  CASE ("t1"."metadata"->>'price' IS NULL) 
  WHEN 1 THEN (
    SELECT sum("products_recordset"."price" * "products_recordset"."quantity")
    FROM jsonb_populate_recordset(NULL::universal.al_items, "t1"."metadata"->'products') AS "products_recordset") 
  ELSE (CAST(CAST("t1"."metadata"->>'price' AS text) AS double precision) * "t1"."quantity") 
  END
FROM "orderitems" AS "t1"