php - "Unknown column in 'field list'" when prepared statement's placeholder is in subquery

Question

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:

$db->prepare("SELECT nr.x FROM (SELECT ? AS x) AS nr")

This is despite the fact that the following statement prepares successfully:

$db->prepare("SELECT nr.x FROM (SELECT '1337' AS x) AS nr")

What causes this difference? The manual 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.

Not PDO's fault

The same thing happens in the stand-alone client:

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

My motivation

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 the manual calls "consecutive", InnoDB skips an auto-increment value when a row might be inserted but is not, as is the case with INSERT IGNORE or ON DUPLICATE KEY UPDATE that runs into an existing row whose UNIQUE values match those of the row being inserted. (These are called "mixed-mode inserts" in the manual.)

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 INSERT IGNORE or ON DUPLICATE KEY UPDATE all the time, I'd burn through 199,800 IDs every few hours. So I don't want to use INSERT IGNORE or ON DUPLICATE KEY UPDATE for fear that I might exhaust the 4.2 billion limit of INTEGER UNSIGNED with repeated inserts over time to a table with the same UNIQUE key. I don't want to switch the column to BIGINT type because 32-bit PHP has no type with the same semantics as MySQL BIGINT. The server administrator is unwilling to switch to 64-bit PHP or to change innodb_autoinc_lock_mode for all users of the server.

So instead, I decided to try INSERT INTO ... SELECT, 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 INSERT INTO ... SELECT is a "bulk insert", which does not burn IDs.) The intent is to do something like this:

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

This failed, giving the PDO error: ["42S22",1054,"Unknown column 'settlement_id' in 'field list'"]

<?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 => '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->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->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->exec($stmt);

/* Now the tests */

$working_stmt = $db->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->execute();
  $data = $working_stmt->fetchAll(PDO::FETCH_ASSOC);
  echo "Working: ".json_encode($data)."
";
} else {
  echo "Working statement failed: ".json_encode($db->errorInfo())."
";
}

$broken_stmt = $db->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->execute(['15A4']);
  $data = $broken_stmt->fetchAll(PDO::FETCH_ASSOC);
  echo "Broken: ".json_encode($data)."
";
} else {
  echo "Broken statement failed: ".json_encode($db->errorInfo())."
";
}

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?

Answer 1

Your latest edit made the question very clear, so I'll attempt an answer: the cause of this difference is the placeholder.

As documented here, placeholders can only be used in certain places in the query. In particular:

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

Now you might have noticed that SELECT ? as x prepares fine, but not SELECT nr.x FROM (SELECT ? AS x) AS nr. Why is that? Well this is best explained by an anonymous author on PHP's doc, so let me copy/paste:

There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

So simply put: because you are using a placeholder in a subquery in the FROM clause, MySQL cannot calculate the execution plan of the query.

In other words, since your query will always change, there is not "template" that can be prepared for it.

Therefore if you really want to use this query, you need to use a normal (non-prepared) query, or turn back on PDO's emulated prepared statements.

That being said, please, do consider the various alternatives offered in the comments section. There are much better solutions for what you are trying to achieve.