この記事は、2017/03/07時点の情報です。
検証したところ、Redshiftの副問い合わせのネストの上限は30でした。ネストを31にするとエラーが発生します。
成功するクエリ
SELECT c_custkey FROM customer WHERE c_custkey IN (SELECT c_custkey FROM customer1 WHERE c_custkey IN (SELECT c_custkey FROM customer2 WHERE c_custkey IN (SELECT c_custkey FROM customer3 WHERE c_custkey IN (SELECT c_custkey FROM customer4 WHERE c_custkey IN (SELECT c_custkey FROM customer5 WHERE c_custkey IN (SELECT c_custkey FROM customer6 WHERE c_custkey IN (SELECT c_custkey FROM customer7 WHERE c_custkey IN (SELECT c_custkey FROM customer8 WHERE c_custkey IN (SELECT c_custkey FROM customer9 WHERE c_custkey IN (SELECT c_custkey FROM customer10 WHERE c_custkey IN (SELECT c_custkey FROM customer11 WHERE c_custkey IN (SELECT c_custkey FROM customer12 WHERE c_custkey IN (SELECT c_custkey FROM customer13 WHERE c_custkey IN (SELECT c_custkey FROM customer14 WHERE c_custkey IN (SELECT c_custkey FROM customer15 WHERE c_custkey IN (SELECT c_custkey FROM customer16 WHERE c_custkey IN (SELECT c_custkey FROM customer17 WHERE c_custkey IN (SELECT c_custkey FROM customer18 WHERE c_custkey IN (SELECT c_custkey FROM customer19 WHERE c_custkey IN (SELECT c_custkey FROM customer20 WHERE c_custkey IN (SELECT c_custkey FROM customer21 WHERE c_custkey IN (SELECT c_custkey FROM customer22 WHERE c_custkey IN (SELECT c_custkey FROM customer23 WHERE c_custkey IN (SELECT c_custkey FROM customer24 WHERE c_custkey IN (SELECT c_custkey FROM customer25 WHERE c_custkey IN (SELECT c_custkey FROM customer26 WHERE c_custkey IN (SELECT c_custkey FROM customer27 WHERE c_custkey IN (SELECT c_custkey FROM customer28 WHERE c_custkey IN (SELECT c_custkey FROM customer29 WHERE c_custkey IN (SELECT c_custkey FROM customer30 -- 30回ネスト ))))))))))))))))))))))))))))))
エラーが発生するクエリ
SELECT c_custkey FROM customer WHERE c_custkey IN (SELECT c_custkey FROM customer1 WHERE c_custkey IN (SELECT c_custkey FROM customer2 WHERE c_custkey IN (SELECT c_custkey FROM customer3 WHERE c_custkey IN (SELECT c_custkey FROM customer4 WHERE c_custkey IN (SELECT c_custkey FROM customer5 WHERE c_custkey IN (SELECT c_custkey FROM customer6 WHERE c_custkey IN (SELECT c_custkey FROM customer7 WHERE c_custkey IN (SELECT c_custkey FROM customer8 WHERE c_custkey IN (SELECT c_custkey FROM customer9 WHERE c_custkey IN (SELECT c_custkey FROM customer10 WHERE c_custkey IN (SELECT c_custkey FROM customer11 WHERE c_custkey IN (SELECT c_custkey FROM customer12 WHERE c_custkey IN (SELECT c_custkey FROM customer13 WHERE c_custkey IN (SELECT c_custkey FROM customer14 WHERE c_custkey IN (SELECT c_custkey FROM customer15 WHERE c_custkey IN (SELECT c_custkey FROM customer16 WHERE c_custkey IN (SELECT c_custkey FROM customer17 WHERE c_custkey IN (SELECT c_custkey FROM customer18 WHERE c_custkey IN (SELECT c_custkey FROM customer19 WHERE c_custkey IN (SELECT c_custkey FROM customer20 WHERE c_custkey IN (SELECT c_custkey FROM customer21 WHERE c_custkey IN (SELECT c_custkey FROM customer22 WHERE c_custkey IN (SELECT c_custkey FROM customer23 WHERE c_custkey IN (SELECT c_custkey FROM customer24 WHERE c_custkey IN (SELECT c_custkey FROM customer25 WHERE c_custkey IN (SELECT c_custkey FROM customer26 WHERE c_custkey IN (SELECT c_custkey FROM customer27 WHERE c_custkey IN (SELECT c_custkey FROM customer28 WHERE c_custkey IN (SELECT c_custkey FROM customer29 WHERE c_custkey IN (SELECT c_custkey FROM customer30 WHERE c_custkey IN (SELECT c_custkey FROM customer31 -- 31回ネスト )))))))))))))))))))))))))))))))
エラー内容
[Amazon](500310) Invalid operation: Assert Details: ----------------------------------------------- error: Assert code: 1000 context: (locus == LocusLeader) || (m_num_plans - m_stream_starts) <= (gconf_num_query_procs_per_slice - gconf_num_bs_query_procs_per_slice) - Number of segments is larger than maximum allowed: 31 > 30. Try to split the query in multiple independent units. query: 302823 location: query.cpp:1005 process: padbmaster [pid=3837] -----------------------------------------------; 1 statement failed.
この制約についての公式ドキュメントは見つけられませんでした。
