title: "Unknown column in 'field list'" when prepared statement's placeholder is in subquery
body: <p>I'm using PHP 5.5.9 and MySQL 5.5.44 with mysqlnd 5.0.11-dev on Ubuntu 14.04 LTS. The following statement fails to prepare:</p>
  
  <pre><code>$db-&gt;prepare("SELECT nr.x FROM (SELECT ? AS x) AS nr")
  </code></pre>
  
  <p>This is despite the fact that the following statement prepares successfully:</p>
  
  <pre><code>$db-&gt;prepare("SELECT nr.x FROM (SELECT '1337' AS x) AS nr")
  </code></pre>
  
  <p>What causes this difference? <a href="https://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html" rel="nofollow">The manual</a> says "Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth." But this is for a data value.</p>
  
  <h2>Not PDO's fault</h2>
  
  <p>The same thing happens in the stand-alone client:</p>
  
  <pre><code>mysql -uredacted -predacted redacted
  -- Type 'help;' or '\h' for help.
  SELECT nr.x FROM (SELECT '1337' AS x) AS nr;
  -- x
  -- 1337
  -- 1 row in set (0.00 sec)
  PREPARE workingstmt FROM 'SELECT nr.x FROM (SELECT ''1337'' AS x) AS nr';
  -- Query OK, 0 rows affected (0.00 sec)
  -- Statement prepared
  DEALLOCATE PREPARE workingstmt;
  -- Query OK, 0 rows affected (0.00 sec)
  PREPARE brokenstmt FROM 'SELECT nr.x FROM (SELECT ? AS x) AS nr';
  -- ERROR 1054 (42S22): Unknown column 'nr.x' in 'field list'
  ^D
  -- Bye
  </code></pre>
  
  <h2>My motivation</h2>
  
  <p>I'm trying to add a row to a table that has an auto-incrementing primary key. In InnoDB's default auto-increment locking mode, which <a href="https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html" rel="nofollow">the manual</a> calls "consecutive", InnoDB skips an auto-increment value when a row might be inserted but is not, as is the case with <code>INSERT IGNORE</code> or <code>ON DUPLICATE KEY UPDATE</code> that runs into an existing row whose <code>UNIQUE</code> values match those of the row being inserted. (These are called "mixed-mode inserts" in the manual.)</p>
  
  <p>Every few hours, I import a feed from my supplier. This has about 200,000 rows, and all but on average 200 of these rows have unique values that correspond to values already present in the table. So if I were to use <code>INSERT IGNORE</code> or <code>ON DUPLICATE KEY UPDATE</code> all the time, I'd burn through 199,800 IDs every few hours. So I don't want to use <code>INSERT IGNORE</code> or <code>ON DUPLICATE KEY UPDATE</code> for fear that I might exhaust the 4.2 billion limit of <code>INTEGER UNSIGNED</code> with repeated inserts over time to a table with the same <code>UNIQUE</code> key. I don't want to switch the column to <code>BIGINT</code> type because 32-bit PHP has no type with the same semantics as MySQL <code>BIGINT</code>. The server administrator is unwilling to switch to 64-bit PHP or to change <code>innodb_autoinc_lock_mode</code> for all users of the server.</p>
  
  <p>So instead, I decided to try <code>INSERT INTO ... SELECT</code>, creating a 1-row table with the unique key columns in a subquery and left joining it to the main table to reject unique key values that already exist. (The manual says <code>INSERT INTO ... SELECT</code> is a "bulk insert", which does not burn IDs.) The intent is to do something like this:</p>
  
  <pre><code>INSERT INTO the_table
  (uniquecol, othercol1, othercol2)
  SELECT nr.uniquecol, :o1 AS othercol1, :o2 AS othercol2
  FROM (
    SELECT ? AS uniquecol
  ) AS nr
  LEFT JOIN the_table ON nr.settlement_id = the_table.settlement_id
  WHERE the_table.row_id IS NULL
  </code></pre>
  
  <p>This failed, giving the PDO error:
  <code>["42S22",1054,"Unknown column 'settlement_id' in 'field list'"]</code></p>
  
  <pre><code>&lt;?php // MCVE follows
  
  /* Connect to database */
  $pdo_dsn = 'mysql:host=127.0.0.1;dbname=redacted';
  $pdo_username = 'redacted';
  $pdo_password = 'redacted';
  $pdo_options = [PDO::MYSQL_ATTR_INIT_COMMAND =&gt; 'SET NAMES utf8',];
  $db = new PDO($pdo_dsn, $pdo_username, $pdo_password, $pdo_options);
  $pdo_dsn = $pdo_username = $pdo_password = 'try harder';
  
  // ensure that PDO doesn't convert everything to strings
  // per http://stackoverflow.com/a/15592818/2738262
  $db-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  $db-&gt;setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
  
  /* Create mock data with which to test the statements */
  $prep_stmts = ["
  CREATE TEMPORARY TABLE sotemp (
    file_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    settlement_id VARCHAR(30) NOT NULL,
    num_lines INTEGER UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE (settlement_id)
  )
  ","
  INSERT INTO sotemp (settlement_id, num_lines) VALUES
  ('15A1', 150),
  ('15A2', 273),
  ('15A3', 201)
  "];
  foreach ($prep_stmts as $stmt) $db-&gt;exec($stmt);
  
  /* Now the tests */
  
  $working_stmt = $db-&gt;prepare("
  SELECT nr.settlement_id
  FROM (
    -- change this to either a value in sotemp or one not in sotemp
    -- and re-run the test program
    SELECT '15A3' AS settlement_id
  ) AS nr
  LEFT JOIN sotemp ON nr.settlement_id = sotemp.settlement_id
  WHERE sotemp.file_id IS NULL
  ");
  if ($working_stmt) {
    $working_stmt-&gt;execute();
    $data = $working_stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
    echo "Working: ".json_encode($data)."\n";
  } else {
    echo "Working statement failed: ".json_encode($db-&gt;errorInfo())."\n";
  }
  
  $broken_stmt = $db-&gt;prepare("
  SELECT nr.settlement_id
  FROM (
    SELECT ? AS settlement_id
  ) AS nr
  LEFT JOIN sotemp ON nr.settlement_id = sotemp.settlement_id
  WHERE sotemp.file_id IS NULL
  ");
  if ($broken_stmt) {
    $broken_stmt-&gt;execute(['15A4']);
    $data = $broken_stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
    echo "Broken: ".json_encode($data)."\n";
  } else {
    echo "Broken statement failed: ".json_encode($db-&gt;errorInfo())."\n";
  }
  </code></pre>
  
  <p>What is causing this error? And is there a better way to insert a row only if the primary key does not exist without exhausting auto-increment IDs?</p>