AWS Redshift EXISTS Condition Error
<p>My schema is just for testing something and table has only 1 row:</p>

<pre><code>create table public.simon_test (MaxID bigint);
insert into public.simon_test (MaxID) values (6129498);
</code></pre>

<p><code>transactionsale</code> has numerous columns but the ones of interest are: </p>

<ul>
<li>transactionsaleid bigint PK</li>
<li>Amount decimal</li>
<li>storeid bigint</li>
</ul>

<p>I am trying to run this SQL in Amazon Redshift:</p>

<pre><code>select 
  storeid, 
  sum(Amount) as SalesAmount
from transactionsale ts 
where exists (select 1 from simon_test s where s.maxid &lt; ts.transactionsaleid)
group by storeid;
</code></pre>

<p>I get the error:</p>

<blockquote>
  <p>'Invalid operation: This type of correlated subquery pattern is not supported due to internal error;'.</p>
</blockquote>

<p>However, this works fine:</p>

<pre><code>select
  storeid, 
  sum(saleitemcount) as CntItems,
  sum(Amount) as SalesAmount
from transactionsale ts inner join simon_test s on ts.transactionsaleid &gt; s.MaxID
group by storeid;
</code></pre>

<p>But I would have thought both should work and wanted to test performance.</p>

<p>What am I missing? It doesn't seem to qualify for any of the <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_correlated_subqueries.html" rel="nofollow">unsupported subquery patterns</a>.</p>