Architect's Log

I'm a Cloud Architect. I'm highly motivated to reduce toils with driving DevOps.

【AWS】Redshiftの副問い合わせのネストの上限

この記事は、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.

この制約についての公式ドキュメントは見つけられませんでした。