Thread: BUG #9041: Strange error message with LATERAL and mixed WHERE/ON conditions

The following bug has been logged on the website:

Bug reference:      9041
Logged by:          David Leverton
Email address:      levertond@googlemail.com
PostgreSQL version: 9.3.2
Operating system:   Oracle Enterprise Linux 5 x86_64
Description:

The following query fails with the message "JOIN qualification cannot refer
to other relations":

SELECT *
FROM (SELECT 1 AS x) x
CROSS JOIN (SELECT 1 AS y) y
LEFT JOIN LATERAL (
  SELECT *
  FROM (SELECT 1 AS z) z
  WHERE z.z = x.x
) z ON z.z = y.y;

I don't see why this shouldn't be legal, but if it isn't it should at least
give a less vague error message.

(For the record, the mixture of WHERE and ON is because in the real query,
the LATERAL subquery including the WHERE clause is actually an inlineable
SRF call.)

Postgres was installed using the pgrpms.org packages, and all settings other
than listen_addresses are at their default values.

levertond@googlemail.com writes:
> The following query fails with the message "JOIN qualification cannot refer
> to other relations":

> SELECT *
> FROM (SELECT 1 AS x) x
> CROSS JOIN (SELECT 1 AS y) y
> LEFT JOIN LATERAL (
>   SELECT *
>   FROM (SELECT 1 AS z) z
>   WHERE z.z = x.x
> ) z ON z.z = y.y;

> I don't see why this shouldn't be legal, but if it isn't it should at least
> give a less vague error message.

Yeah, that query looks legal to me too.  It's tripping over a planner bug,
and the error report is from an internal consistency check, which explains
its not-too-user-friendly nature.  Will fix, thanks for the report!

            regards, tom lane