Potential bug in optimize when including schemas in sql query

[raeudigerRaeffi]

Hi I have the following SQL Query:

SELECT
u.id AS user_id,
u.email AS user_email,
u.username,
u.first_name,
u.last_name,
v.id AS visualization_id,
v.createdat AS visualization_createdat,
v.sql AS visualization_sql,
v.config AS visualization_config,
(SELECT COALESCE(SUM(costs), 0)
FROM public.apitransaction
WHERE vis_command_id = v.id) AS total_api_costs,
(SELECT COUNT(*)
FROM public.chatmessage
WHERE is_error = TRUE AND chatid IN
(SELECT id
FROM public.chat
WHERE usermodelid = u.id)) AS total_error_messages
FROM
public.usermodel AS u
JOIN
public.visualization AS v ON u.id = v.userid
WHERE
v.createdat >= (CURRENT_DATE - INTERVAL '30 days')
ORDER BY
v.createdat DESC;

After using optimize on this query it is transformed to:
WITH "_u_2" AS (SELECT "chat"."id" AS "id" FROM "public"."chat" AS "chat" WHERE "chat"."usermodelid" = "chat"."u"."id" GROUP BY "chat"."id"), "_u_0" AS (SELECT COALESCE(SUM("apitransaction"."
costs"), 0) AS "_col_0", "apitransaction"."vis_command_id" AS "_u_1" FROM "public"."apitransaction" AS "apitransaction" GROUP BY "apitransaction"."vis_command_id") SELECT "u"."id" AS "user_id", "u"."em
ail" AS "user_email", "u"."username" AS "username", "u"."first_name" AS "first_name", "u"."last_name" AS "last_name", "v"."id" AS "visualization_id", "v"."createdat" AS "visualization_createdat", "v"."
sql" AS "visualization_sql", "v"."config" AS "visualization_config", "_u_0"."_col_0" AS "total_api_costs", (SELECT COUNT(*) AS "_col_0" FROM "public"."chatmessage" AS "chatmessage" LEFT JOIN "_u_2" AS
"_u_2" ON "_u_2"."id" = "chatmessage"."chatid" WHERE "chatmessage"."is_error" = TRUE AND NOT "_u_2"."id" IS NULL) AS "total_error_messages" FROM "public"."usermodel" AS "u" JOIN "public"."visualization" AS "v" ON "u"."id" = "v"."userid" AND "v"."createdat" >= (CAST(CURRENT_DATE AS TIMESTAMP) - INTERVAL '30 DAYS') LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."_u_1" = "v"."id" ORDER BY "v"."createdat" DESC

Notice how in the first line WHERE "chat"."usermodelid" = "chat"."u"."id", which is incorrect as chat is a table but here it incorrectly assumes chat to be a schema containing a table u

[tobymao]

please provide a fully reproducible code snippet in the future.

[tobymao]

seems like a bug in your code, your correlated subquery references a subquery in a later scope