Re: Reference to parent query from ANY sublink

From: Kevin Grittner <kgrittn(at)ymail(dot)com>
To: Antonin Houska <antonin(dot)houska(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reference to parent query from ANY sublink
Date: 2013-12-06 14:33:12
Message-ID: 1386340392.98648.YahooMailNeo@web162905.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Antonin Houska <antonin(dot)houska(at)gmail(dot)com> wrote:

> SELECT *
> FROM    tab1 a
>     LEFT JOIN
>     tab2 b
>     ON a.i = ANY (
>         SELECT  k
>         FROM    tab3 c
>         WHERE    k = a.i);

This query works with k in any or all tables, but the semantics
certainly vary depending on where k happens to be.  It would help a
lot if you showed SQL statements to create and populate the tables
involved and/or if you qualified all referenced column names with
the table alias to avoid ambiguity.

If I assume that the k reference is supposed to be a column in
tab3, what you have is a query where you always get all rows from
tab1, and for each row from tab1 you either match it to all rows
from tab2 or no rows from tab2 depending on whether the tab1 row
has a match in tab3.

test=# create table tab1 (i int);
CREATE TABLE
test=# create table tab2 (j int);
CREATE TABLE
test=# create table tab3 (k int);
CREATE TABLE
test=# insert into tab1 values (1), (2), (3);
INSERT 0 3
test=# insert into tab2 values (4), (5), (6);
INSERT 0 3
test=# insert into tab3 values (1), (3);
INSERT 0 2
test=# SELECT *
FROM    tab1 a
    LEFT JOIN
    tab2 b
    ON a.i = ANY (
        SELECT  k        
        FROM    tab3 c
        WHERE    k = a.i);
 i | j
---+---
 1 | 4
 1 | 5
 1 | 6
 2 |
 3 | 4
 3 | 5
 3 | 6
(7 rows)

> SELECT  *
> FROM    tab1 a
>     LEFT JOIN
>     (
>       SELECT *
>       tab2 b
>       SEMI JOIN
>       (  SELECT  k
>           FROM    tab3 c
>           WHERE  k = a.i
>       ) AS ANY_subquery
>       ON a.i = ANY_subquery.k
>     ) AS SJ_subquery
>     ON true;

It is hard to see what you intend here, since this is not valid
syntax.  I assume you want a FROM keyword before the tab2
reference, but it's less clear what you intend with the SEMI JOIN
syntax.  PostgreSQL supports semi-joins; but that is an
implementation detail for the EXISTS or IN syntax.  Could you
clarify your intent?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2013-12-06 14:34:13 Re: WITHIN GROUP patch
Previous Message MauMau 2013-12-06 14:16:35 [patch] Client-only installation on Windows