title: INSERT + SELECT + ON DUPLICATE KEY with column aliases in SELECT clause
body: <p>I'm hitting a rather surprising roadblock when attempting to chain together INSERT, SELECT and ON DUPLICATE KEY in a query where the SELECT clause has column aliases. For example, consider the following situation:</p>
  
  <p><strong>Tables</strong>:</p>
  
  <pre><code>CREATE TABLE source ( 
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      v INT NOT NULL
  );
  INSERT INTO source (v) VALUES (1), (2), (3);
  CREATE TABLE dest (
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      v INT NOT NULL
  );
  </code></pre>
  
  <p>Suppose I'm trying to fill <code>dest.v</code> with the values of <code>POW(source.v,2)</code> regardless of if values exist in <code>dest</code> already. Naturally, I tried:</p>
  
  <pre><code>INSERT INTO dest 
      SELECT id, POW(v, 2) AS p FROM source 
      ON DUPLICATE KEY UPDATE dest.v=source.p;
  </code></pre>
  
  <p>However, MySQL insists that source.p doesn't exist:</p>
  
  <blockquote>
    <p>ERROR 1054 (42S22): Unknown column 'source.p' in 'field list'</p>
  </blockquote>
  
  <p>Rather inconveniently, I have to resort to using the slower and more cumbersome query:</p>
  
  <pre><code>INSERT INTO dest 
      SELECT * FROM ( 
           SELECT id, POW(v, 2) AS p FROM source 
      ) s
      ON DUPLICATE KEY UPDATE dest.v=s.p;
  </code></pre>
  
  <p>which differs very little from the original query, but works. Why is this the case?</p>