Search code examples
sqlpostgresqlgreenplum

SQL query error (correlated subquery with skip-level correlations is not supported )

I have a following sql query which run at:

PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit

but not at:

PostgreSQL 8.2.15 (Greenplum Database 4.3.5.4 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2

SELECT id,
(SELECT AVG(dur)
    FROM data t
    WHERE t.id = t1.id AND 
        t.id IN (SELECT id 
                    FROM data t2
                    WHERE t2.id = t1.id
                    ORDER BY dur
                    DESC LIMIT 10)) as avgdur
FROM data t1 
WHERE t1.b<10000
ORDER BY avgdur 
DESC LIMIT 1;

I get the following error: ERROR: correlated subquery with skip-level correlations is not supported (subselect.c:394)

How should i modify the query?

Solution

  • Your problems lies in WHERE t2.id = t1.id. You can use in a subquery the query at the parent level but not at upper levels. So here you have 3 levels:

    1. t1
    2. t
    3. t2

    You can use t from the "t2 subquery" (query where t2 is in the FROM clause) and you can use t1 in your "t subquery". BUT you CANNOT use t1 from your "t2 subquery".

    A fix could be to use WHERE t2.id = t.id)) as avgdur.

    Some infos here : http://www.greenplumdba.com/correlated-subqueries-csqs-in-greenplum