Seems fair to me. Just add a note to https://github.com/joomla-framework/database/blob/2.0-dev/docs/v1-to-v2-update.md and I'd say this should be fine, we just need to sort out deprecation notices then in the CMS repo and this repo's master (1.x) branch.

I'm not sure about variable name '$merge' but I have not found a better name.

If necessary, correct my documentation.

look nice to me too, IIRC UNION in core is used only in com_finder

Nice, I think about it "too much" and now I have another solution, where we can put ORDER BY and LIMIT before UNION statement. I will prepare another PR for that.

[UPDATED]
Ups, I did it on this PR:)

👍
despite i'm not a fan of "LIMIT" clause on such kind of analytics query 😨

To get ORDER BY or/and LIMIT before the first UNION statement I added to DatabaseQuery class a new type querySet. This is something like subquery in DatabaseQuery object.

Option 1:

$tbl = $db->quoteName('#__foo');

$query = $db->getQuery(true);
$query2 = $db->getQuery(true);
$query3 = $db->getQuery(true);

$query->querySet($query2->select('name')->from($tbl)->order('id DESC')->setLimit(1))
	->unionAll($query3->select('name')->from($tbl)->order('id')->setLimit(1))
	->order('name')
	->setLimit(1)

Option 2:

$tbl = $db->quoteName('#__foo');

$query = $db->getQuery(true);
$query2 = $db->getQuery(true);

$query->select('name')->from($tbl)->order('id DESC')->setLimit(1)
	->toQuerySet()
	->unionAll($query2->select('name')->from($tbl)->order('id')->setLimit(1))
	->order('name')
	->setLimit(1);

Common result:

(
	SELECT name FROM `#__foo` ORDER BY id DESC LIMIT 1
)
UNION (
	SELECT name FROM `#__foo` ORDER BY id LIMIT 1
)
ORDER BY name LIMIT 1

At the end I added new unit tests for PostgreSQL and SQLite.

SqlSrv may still does not work if you use ORDER BY or LIMIT in union query. This is more complicated fix and I would not add it to this PR.

What do you thing about this? @mbabker @alikon @Quy

Seems fine to me.

SqlSrv may still does not work if you use ORDER BY or LIMIT in union query. This is more complicated fix and I would not add it to this PR.

Well luckily the testing infrastructure here is a lot better off (and you can somewhat copy the current Travis config to get a working SQL Server instance to test against). No tests are failing for now so let's roll with it and we can work on updating the test suite to better cover cases like this.

Tested successfully.

I will spend a little time to fix sqlsrv stuff, I need to back to my previous PR #104

My tests:

require 'vendor/autoload.php';
$db = Joomla\Database\DatabaseDriver::getInstance(['driver' => 'sqlsrv', 'database' => '', 'user' => '', 'password' => '', 'prefix' => 'j38_']);
$q = $db->getQuery(1)->select('id')->from('j38_modules')->where('id <= 5')->order('id DESC')->setLimit(2, 1)->toQuerySet()->unionAll($db->getQuery(1)->select('id')->from('j38_modules')->where('id = 3 OR id = 4')->order('0+id DESC')->setLimit(1, 1))->order('id DESC');

echo $q;

SELECT * FROM (
SELECT * FROM (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber_4c1bcee9724ef8f6f37135d9596d299a
FROM (
SELECT TOP 3 id
FROM j38_modules
WHERE id <= 5
ORDER BY id DESC) AS A
) AS A
WHERE RowNumber_4c1bcee9724ef8f6f37135d9596d299a > 1) AS merge_0
UNION ALL SELECT * FROM (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber_b0af95c67b1589f1db7914ea2e26f658
FROM (
SELECT TOP 2 id
FROM j38_modules
WHERE id = 3 OR id = 4
ORDER BY 0+id DESC) AS A
) AS A
WHERE RowNumber_b0af95c67b1589f1db7914ea2e26f658 > 1) AS merge_1
) AS merges
ORDER BY id DESC

echo $q->setLimit(5, 2);

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber_e89d72665d4b94533c8b495e1eebf7ee
FROM (
SELECT TOP 7 * FROM (
SELECT * FROM (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber_4c1bcee9724ef8f6f37135d9596d299a
FROM (
SELECT TOP 3 id
FROM j38_modules
WHERE id <= 5
ORDER BY id DESC) AS A
) AS A
WHERE RowNumber_4c1bcee9724ef8f6f37135d9596d299a > 1) AS merge_0
UNION ALL SELECT * FROM (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber_b0af95c67b1589f1db7914ea2e26f658
FROM (
SELECT TOP 2 id
FROM j38_modules
WHERE id = 3 OR id = 4
ORDER BY 0+id DESC) AS A
) AS A
WHERE RowNumber_b0af95c67b1589f1db7914ea2e26f658 > 1) AS merge_1
) AS merges
ORDER BY id DESC) AS A
) AS A
WHERE RowNumber_e89d72665d4b94533c8b495e1eebf7ee > 2

It looks a little ugly but we can only remove ROW_NUMBER() after we bump to SQL Server 2012

Since the minimum version of SQL Server is now 2012, it would be good to wait for the new SqlsrvQuery::processList () from #124

Now unit tests execute real query tests for UNIONs on sqlite, mysqli, pgsql and sqlsrv.

Unit tests passed.

Also, we shouldn't cast the exception code to integer here. It is correct that the DB drivers don't strictly use numeric codes and Exception codes aren't type enforced.

Yes, but try to do a bug in code:

diff --git a/src/Sqlite/SqliteQuery.php b/src/Sqlite/SqliteQuery.php
index 940d030..b196fcc 100644
--- a/src/Sqlite/SqliteQuery.php
+++ b/src/Sqlite/SqliteQuery.php
@@ -73,7 +73,7 @@ class SqliteQuery extends PdoQuery
                        case 'querySet':
                                $query = $this->querySet;
 
-                               if ($query->order || $query->limit || $query->offset)
+                               if (0)//$query->order || $query->limit || $query->offset)

Then run phpunit by php phpunit Tests/Sqlite/SqliteDriverTest.php

Then I got:

1. Joomla\Database\Tests\Sqlite\SqliteDriverTest::testQuerySetWithUnionAll
   Error: Wrong parameters for Joomla\Database\Exception\PrepareStatementFailureException([string $message [, long $code [, Throwable $previous = NULL]]])

Maybe override constructor for PrepareStatementFailureException could be better?

If it really comes down to that yes the constructor should be overloaded. Now I'm curious what is overloading core PHP's RuntimeException, or even root Exception, definition because last I was unaware the root constructor was not typed.

:eyeroll:

It seems to be a known issue http://php.net/manual/en/class.pdoexception.php#95812

Should I add the constructor for PrepareStatementFailureException at this PR?

Might as well.

Done