title: COLLATE clause cannot be used on expressions containing a COLLATE clause
body: <p>I have two tables: tab1 and tab2. Every table has only one VARCHAR(MAX) column.</p>
  
  <p>I need to obtain only those pairs of values, which are equals, but differs in only case.</p>
  
  <p>Example input:</p>
  
  <pre><code>tab1.t1     tab2.t2
  -----------------------
  fff     fff
  FFF     fff
  Fff     fff
  FFF     FFA
  FfA     FFF
  FFF     aaa
  bbb     aaa
  
  Related output:
  
  t1      t2
  -----------------------
  fff     FFF
  FFF     fff
  Fff     fff
  Fff     FFF
  FfA     FFA
  </code></pre>
  
  <p>Tables tab1 and tab2 are big enough in real our real database (~ 800-1000 rows). And there are about 500-600 columns for which I need to produce this operation.</p>
  
  <p>So I need to write a fast solution.
  I write an alghoritm:</p>
  
  <ol>
  <li>remove all duplicates using case sensitive collation from tab1</li>
  <li>remove all duplicates using case sensitive collation from tab2</li>
  <li>join result sets from previous steps using case insensitive collation</li>
  <li>filter out (WHERE clause) rows which values are not equals using case sensitive collation</li>
  <li>remove duplicate rows using case sensitive collation</li>
  </ol>
  
  <p>I tried so:</p>
  
  <pre><code>SELECT DISTINCT tt.t1 COLLATE Cyrillic_General_CS_AS, tt.t2 COLLATE Cyrillic_General_CS_AS
  FROM (
      SELECT tt1.t1, tt2.t2
      FROM 
      (
          SELECT tab1.t1 COLLATE Cyrillic_General_CS_AS
                         AS t1
          FROM (VALUES('fff'),('FFF'),('Fff'),('FFF'),('FfA'),('FFF'),('bbb')) AS tab1(t1)
          GROUP BY tab1.t1 COLLATE Cyrillic_General_CS_AS
      ) tt1 INNER JOIN
      (
          SELECT tab2.t2 COLLATE Cyrillic_General_CS_AS
                         AS t2
          FROM (VALUES('fff'),('fff'),('fff'),('FFA'),('FFF'),('aaa'),('aaa')) AS tab2(t2)
          GROUP BY tab2.t2 COLLATE Cyrillic_General_CS_AS
      ) tt2
      ON tt1.t1 = tt2.t2 COLLATE Cyrillic_General_CI_AS
  ) AS tt
  WHERE tt.t1 &lt;&gt; tt.t2 COLLATE Cyrillic_General_CS_AS
  </code></pre>
  
  <p>but it an error occur:</p>
  
  <blockquote>
    <p>"COLLATE clause cannot be used on expressions containing a COLLATE clause."</p>
  </blockquote>
  
  <p>Please suggest me how to avoid this problem without using user defined functions, temp tables, or removing group by clause (i tried them - they hardly slow down execution).</p>