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

Question

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?

see these link, it might help. stackoverflow.com/questions/426221/… — Bharat
– Bharat, Commented Apr 7, 2016 at 8:18
WHERE t2.id = t1.id)) as avgdur -->> WHERE t2.id = t.id)) as avgdur But the query can probably be simplified. — joop
– joop, Commented Apr 7, 2016 at 8:34

Pholochtairze · Accepted Answer · 2016-04-07 08:37:44Z

0

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:

t1
t
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

answered Apr 7, 2016 at 8:37

Pholochtairze

1,8542 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2016-04-07 08:38:46Z

0

Looks to me as you're simply trying to do a group by in a different way...

SELECT id, AVG(dur) as avgdur
FROM data t1 
WHERE t1.b<10000
GROUP BY id
ORDER BY avgdur 
DESC LIMIT 1;

answered Apr 7, 2016 at 8:38

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

1 Comment

ivankiss Over a year ago

yes, but i have to do more things, i just simplified the question, i have to find the id where the 5 max element average is the maximum

Venkatesh Raghavan · Accepted Answer · 2016-04-07 18:50:39Z

0

Pivotal Query Optimizer (available in 4.3.5) supports queries with multi-level correlation. To enable Pivotal Query Optimizer, run the following commands.

set optimizer =on;

I would encourage you to upgrade to the latest GPDB version of 4.3.8 which has several improvements and bug fixes.

Let me know if this helps!

answered Apr 7, 2016 at 18:50

Venkatesh Raghavan

1

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related