title: Strange behavior of temporary auto increment column in MySQL against HAVING clause
body: <p>Assume we have a table named <strong>Test</strong> with a single column <strong>col1</strong> (Varchar(10)).
  For the sake of this example, assume it has the following data:</p>
  
  <pre><code>col1
  a
  b
  c
  d
  </code></pre>
  
  <p>Now, I want to select the data in <strong>col1</strong> and add a temporary auto-increment column, which we will call <strong>Rank</strong>. The following query does the job:</p>
  
  <pre><code>SELECT
  (@cnt := @cnt + 1) AS Rank, Test.col1
  FROM Test 
  CROSS JOIN (SELECT @cnt := 0) AS tmp;
  </code></pre>
  
  <p>The result is</p>
  
  <pre><code>Rank col1
  1    a
  2    b
  3    c
  4    d
  </code></pre>
  
  <p>No problems so far. Now assume we need to select the rows with Rank larger than 1. I do the following:</p>
  
  <pre><code>SELECT
  (@cnt := @cnt + 1) AS Rank,  Test.col1
  FROM Test 
  CROSS JOIN (SELECT @cnt := 0) AS tmp
  having Rank &gt; 1;
  </code></pre>
  
  <p>The result becomes</p>
  
  <pre><code>Rank col1
  3    b
  5    c
  7    d
  </code></pre>
  
  <p>Notice that the first item in the Rank column is 3 rather than 2 as one would expect naturally. Can someone please point out a possible reason for this?</p>
  
  <blockquote>
    <p>Why does the Rank (the temporary auto-increment) jump form 2 to 3 ?</p>
  </blockquote>
  
  <p>The MySQL version is 5.6.32-78.1.</p>