When using a left join to join a CTE in Postgres, we receive a column "does not exist" error. This error occurs when one or several of the columns in the CTE are not used in the joined query. When inspecting the Execution Trace and reviewing the Postgres dialect, the translation to Postgres is incorrect and throws an error. Example below:
**Denodo query:**
WITH trace AS (
SELECT id, name
FROM trace
WHERE id IS NOT NULL
)
SELECT
code, revision
FROM tickets
LEFT JOIN trace
ON tickets.code = trace.id
**ERROR**
QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ERROR: column s0.name does not exist
When inspecting the Postgres dialect translation, there is an error in the Postgres query that the VDP sends to the Postgres data source. The CTE does not have a "name" column and the LEFT JOIN subquery is calling that missing "name" column from the CTE, thus throwing a Postgres error.
**Postgres Query**
WITH w0 AS (SELECT t1."id" AS id FROM
"public".trace t1 WHERE (t1."id") IS NOT NULL)
SELECT t0."code" AS "Code",
t0."revision" AS "Revision" FROM "public".tickets t0 LEFT JOIN
(SELECT s0.id AS Id, s0.name AS Name FROM w0 s0 ) s1 ON
((t0."code" = s1.Id) ) LIMIT 150
This seems like a bug. Can this be confirmed? Any help or workarounds would be appreciated.
