UNION SELECT fails in zend-db because of wrong syntax

Question

I have a problem using zend-db to construct a UNION SELECT. I would like to combine three SELECTs together:

( SELECT `id1` AS `id` FROM `table1` ) UNION ( SELECT `id2` AS `id2` FROM `table2` ) UNION ( SELECT `id3` AS `id` FROM `table3` )

The count, order and data type of the ID fields is equal and the query is executed without any errors.
Then I tried to build this query via zend-db:

$select1 = new Select();
$select1->from('table1');
$select1->columns(['id1']);

$select2 = new Select();
$select2->from('table2');
$select2->columns(['id2']);

$select3 = new Select();
$select3->from('table3');
$select3->columns(['id3']);

$select2->combine($select1, Select::COMBINE_UNION);
$select3->combine($select2, Select::COMBINE_UNION);

This results in the following query (taken from the log):

( SELECT `id1` AS `id` FROM `table1` ) UNION (( SELECT `id2` AS `id2` FROM `table2` ) UNION ( SELECT `id3` AS `id` FROM `table3` ))

And in an SQL syntax error because the second UNION is wrong.
The exact error message is the following:

"exception": {
    "class": "Zend\\Db\\Adapter\\Exception\\InvalidQueryException",
    "code": 0,
    "message": "Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ( SELECT `id3` AS `id`' at line 1)"
}

I also tried to combine the SELECTs in this way:

$select1->combine($select2, Select::COMBINE_UNION)->combine($select3, Select::COMBINE_UNION);

But then I get the following exception:

"exception": {
    "class": "Zend\\Db\\Sql\\Exception\\InvalidArgumentException",
    "code": 0,
    "message": "This Select object is already combined and cannot be combined with multiple Selects objects"
}

[possible duplicate](https://stackoverflow.com/questions/8181783/how-to-use-union-in-zend-db) — danish-khan-I, Aug 15 '19 at 14:17
Just a thought, maybe `$select1->combine($select2, Select::COMBINE_UNION)->combine($select3, Select::COMBINE_UNION);` — Patrick Q, Aug 15 '19 at 14:18

score 0 · Answer 1 · answered Aug 15 '19 at 14:24

What is the actual error message?

Not sure if this will have any effect, but try explicitly CASTing the data types returned in your "top" queries:

( 
  SELECT CAST(`id1` AS <data_type>) AS `id` FROM `table1` 
) 
UNION 
(
  ( SELECT CAST(`id2` AS <data_type>) AS `id2` FROM `table2` ) 
  UNION 
  ( SELECT `id3` AS `id` FROM `table3` )
)

UNION SELECT fails in zend-db because of wrong syntax

1 Answers1