When using union placeholders seem to be replaced with values from the initial select used in the union

Question

This is more or less related to this How to preserve the order of the fields to be selected when using $query->addExpression()

What I'm doing is making a union on multiple tables. But I also need to know from which table the values are coming from. So I just add something like this: $query->addExpression(':table', 'table_name', array(':table' => $table_name));

Problem is as soon as you make a union the table name is going to be replaced with that from the first query.

Check the example below:

$table_name = 'table_one'; 
$query_1 = db_select('table_one', 't1');
$query_1->fields('t1', array('field_1', 'field_2'));
$query_1->addExpression(':table', 'table_name', array(':table' => $table_name));

$table_name = 'table_two'; 
$query_2 = db_select('table_two', 't2');
$query_2->fields('t2', array('field_1', 'field_2'));
$query_2->addExpression(':table', 'table_name', array(':table' => $table_name));    

$table_name = 'table_three';
$query_3 = db_select('table_three', 't3');
$query_3->fields('t3', array('field_1', 'field_2'));
$query_3->addExpression(':table', 'table_name', array(':table' => $table_name));

If we check the selects individually they all look fine, you get the expected result, but when using union you will only see table_one all over the place.

// dpq($query_1) - OK 
SELECT t1.field_1 AS field_1, t1.field_2 AS field_2, 'table_one' AS table_name
FROM 
{table_one} t1

// dpq($query_2) - OK
SELECT t2.field_1 AS field_1, t2.field_2 AS field_2, 'table_two' AS table_name
FROM 
{table_two} t2

// dpq($query_3) - OK
SELECT t3.field_1 AS field_1, t3.field_2 AS field_2, 'table_three' AS table_name
FROM 
{table_three} t3

// dpq($query_1->union($query_2)->union($query_3)) - OOPS.. 'table_one' all over the place
SELECT t1.field_1 AS field_1, t1.field_2 AS field_2, 'table_one' AS table_name
FROM 
{table_one} t1 UNION SELECT t2.field_1 AS field_1, t2.field_2 AS field_2, 'table_one' AS table_name
FROM 
{table_two} t2 UNION SELECT t3.field_1 AS field_1, t3.field_2 AS field_2, 'table_one' AS table_name
FROM 
{table_three} t3

If you have ideas would be great because I've been banking my head for a while with this...

Answer 1

After a few hours of sleep I tracked it down to this function:

  // @file includes/database/select.inc
  public function arguments() {
    // ....

    // If there are any dependent queries to UNION,
    // incorporate their arguments recursively.
    foreach ($this->union as $union) {
      $args += $union['query']->arguments();
    }

    return $args;
  }

And since all the selects in the query have the same key :table then you'll end up with only one argument instead of thee.

Solution: Just make sure you use different keys for the placeholders.

So if we replace in the above example these lines (notice :table_1 & :table_2 instead of :table:

$query_2->addExpression(':table_1', 'table_name', array(':table_1' => $table_name));
$query_3->addExpression(':table_2', 'table_name', array(':table_2' => $table_name));

Voila, finally get the expected results. Hope it helps somebody, half a day lost trying to figure out why this was happening.

SELECT t1.field_1 AS field_1, t1.field_2 AS field_2, 'table_one' AS table_name
FROM 
{table_one} t1 UNION ALL SELECT t2.field_1 AS field_1, t2.field_2 AS field_2, 'table_two' AS table_name
FROM 
{table_two} t2 UNION ALL SELECT t3.field_1 AS field_1, t3.field_2 AS field_2, 'table_three' AS table_name
FROM 
{table_three} t3