Repository: knex/knex
Pull request #5030 (2022-02-19 18:13:20)

Rework Union Wrapping
- Rework union construction to work with order by, limit, offset, etc.
- Before this PR, union is done before group by, limit, ... so statements position make syntax errors.
- With this PR, union is done after other statements but, as exposed in https://github.com/knex/knex/issues/913#issuecomment-334481807, is not only an order problem.
I also wrapped all queries in parenthesis to avoid syntax error  in PG.
By the way, reordering components (as intended in the issue) dont work also because the order of bindings is not changed.
- Closes https://github.com/knex/knex/issues/913
- Closes https://github.com/knex/knex/issues/5047

OlivierCavadenti: I change wrap logic (main query is wrapped is union query are wrapped) but I still need to investigate how to generate valid SQL with limit, group by, etc. in unions (not so trivial).

Patch:
diff --git a/lib/query/querycompiler.js b/lib/query/querycompiler.js
index 75a3d800..a7a4997a 100644
--- a/lib/query/querycompiler.js
+++ b/lib/query/querycompiler.js
@@ -124,8 +124,36 @@ class QueryCompiler {
   select() {
     let sql = this.with();
 
-    const statements = components.map((component) => this[component](this));
-    sql += compact(statements).join(' ');
+    let unionStatement = '';
+    // Compute all statements to main query
+    const statements = components.map((component) => {
+      const statement = this[component](this);
+      // We store the 'union' statement to append it at the end.
+      // We still need to call the component sequentially because of
+      // order of bindings.
+      if (component === 'union') {
+        unionStatement = statement;
+      } else {
+        return statement;
+      }
+    });
+
+    // Check if we need to wrap the main query.
+    // We need to wrap main query if one of union have wrap options to true
+    // to avoid error syntax (in PostgreSQL for example).
+    const wrapMainQuery =
+      this.grouped.union &&
+      this.grouped.union.map((u) => u.wrap).some((u) => u);
+    const allStatements =
+      (wrapMainQuery ? '(' : '') +
+      compact(statements).join(' ') +
+      (wrapMainQuery ? ')' : '');
+
+    if (this.onlyUnions()) {
+      sql += unionStatement + ' ' + allStatements;
+    } else {
+      sql += allStatements + (unionStatement ? ' ' + unionStatement : '');
+    }
     return sql;
   }
 
@@ -706,9 +734,10 @@ class QueryCompiler {
         this.bindingsHolder
       );
       if (statement) {
-        if (union.wrap) sql += '(';
+        const wrap = union.wrap;
+        if (wrap) sql += '(';
         sql += statement;
-        if (union.wrap) sql += ')';
+        if (wrap) sql += ')';
       }
     }
     return sql;


Test patch:
diff --git a/test/integration/query/aggregate.js b/test/integration/query/aggregate.js
index f0765c18..95bd6a02 100644
--- a/test/integration/query/aggregate.js
+++ b/test/integration/query/aggregate.js
@@ -23,7 +23,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'sum(`logins`)': 10,
+                'sum(`logins`)': 15,
               },
             ]
           );
@@ -33,7 +33,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'sum(`logins`)': '10',
+                'sum(`logins`)': '15',
               },
             ]
           );
@@ -43,7 +43,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                sum: '10',
+                sum: '15',
               },
             ]
           );
@@ -53,7 +53,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                sum: '10',
+                sum: '15',
               },
             ]
           );
@@ -63,7 +63,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'sum(`logins`)': 10,
+                'sum(`logins`)': 15,
               },
             ]
           );
@@ -73,7 +73,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'SUM("LOGINS")': 10,
+                'SUM("LOGINS")': 15,
               },
             ]
           );
@@ -83,7 +83,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                '': 10,
+                '': 15,
               },
             ]
           );
@@ -100,7 +100,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -110,7 +110,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: '10',
+                login_sum: '15',
               },
             ]
           );
@@ -120,7 +120,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: '10',
+                login_sum: '15',
               },
             ]
           );
@@ -130,7 +130,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: '10',
+                login_sum: '15',
               },
             ]
           );
@@ -140,7 +140,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -150,7 +150,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -160,7 +160,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -178,7 +178,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: 0,
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -189,7 +189,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: 0,
-                login_sum: '10',
+                login_sum: '15',
               },
             ]
           );
@@ -200,7 +200,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: 0,
-                login_sum: '10',
+                login_sum: '15',
               },
             ]
           );
@@ -211,7 +211,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: '0',
-                login_sum: '10',
+                login_sum: '15',
               },
             ]
           );
@@ -222,7 +222,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: 0,
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -233,7 +233,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: 0,
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -244,7 +244,7 @@ module.exports = function (knex) {
             [
               {
                 balance_sum: 0,
-                login_sum: 10,
+                login_sum: 15,
               },
             ]
           );
@@ -256,7 +256,7 @@ module.exports = function (knex) {
         .avg('logins')
         .testSql(function (tester) {
           function checkResRange(key, resp) {
-            return Math.abs(10 / 6 - +resp[0][key]) < 0.001;
+            return Math.abs(15 / 8 - +resp[0][key]) < 0.001;
           }
           function checkResRangeMssql(key, resp) {
             return +resp[0][key] === 1;
@@ -317,7 +317,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(`id`)': 6,
+                'count(`id`)': 8,
               },
             ]
           );
@@ -327,7 +327,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
+                count: '8',
               },
             ]
           );
@@ -337,7 +337,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
+                count: '8',
               },
             ]
           );
@@ -347,7 +347,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(`id`)': 6,
+                'count(`id`)': 8,
               },
             ]
           );
@@ -357,7 +357,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'COUNT("ID")': 6,
+                'COUNT("ID")': 8,
               },
             ]
           );
@@ -367,7 +367,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                '': 6,
+                '': 8,
               },
             ]
           );
@@ -386,8 +386,8 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(`id`)': 6,
-                'max(`logins`)': 2,
+                'count(`id`)': 8,
+                'max(`logins`)': 3,
                 'min(`logins`)': 1,
               },
             ]
@@ -398,8 +398,8 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
-                max: 2,
+                count: '8',
+                max: 3,
                 min: 1,
               },
             ]
@@ -410,8 +410,8 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
-                max: 2,
+                count: '8',
+                max: 3,
                 min: 1,
               },
             ]
@@ -422,8 +422,8 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(`id`)': 6,
-                'max(`logins`)': 2,
+                'count(`id`)': 8,
+                'max(`logins`)': 3,
                 'min(`logins`)': 1,
               },
             ]
@@ -434,8 +434,8 @@ module.exports = function (knex) {
             [],
             [
               {
-                'COUNT("ID")': 6,
-                'MAX("LOGINS")': 2,
+                'COUNT("ID")': 8,
+                'MAX("LOGINS")': 3,
                 'MIN("LOGINS")': 1,
               },
             ]
@@ -446,7 +446,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                '': [6, 2, 1],
+                '': [8, 3, 1],
               },
             ]
           );
@@ -465,9 +465,9 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(distinct `id`)': 6,
-                'sum(distinct `logins`)': 3,
-                'avg(distinct `logins`)': 1.5,
+                'count(distinct `id`)': 8,
+                'sum(distinct `logins`)': 6,
+                'avg(distinct `logins`)': 2.0,
               },
             ]
           );
@@ -477,9 +477,9 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
-                sum: '3',
-                avg: '1.5000000000000000',
+                count: '8',
+                sum: '6',
+                avg: '2.0000000000000000',
               },
             ]
           );
@@ -489,8 +489,8 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
-                sum: '3',
+                count: '8',
+                sum: '6',
                 avg: '1',
               },
             ]
@@ -501,9 +501,9 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(distinct `id`)': 6,
-                'sum(distinct `logins`)': 3,
-                'avg(distinct `logins`)': 1.5,
+                'count(distinct `id`)': 8,
+                'sum(distinct `logins`)': 6,
+                'avg(distinct `logins`)': 2.0,
               },
             ]
           );
@@ -513,9 +513,9 @@ module.exports = function (knex) {
             [],
             [
               {
-                'COUNT(DISTINCT"ID")': 6,
-                'SUM(DISTINCT"LOGINS")': 3,
-                'AVG(DISTINCT"LOGINS")': 1.5,
+                'COUNT(DISTINCT"ID")': 8,
+                'SUM(DISTINCT"LOGINS")': 6,
+                'AVG(DISTINCT"LOGINS")': 2.0,
               },
             ]
           );
@@ -525,7 +525,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                '': [6, 3, 1],
+                '': [8, 6, 2],
               },
             ]
           );
@@ -548,7 +548,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                'count(distinct `id`, `logins`)': 6,
+                'count(distinct `id`, `logins`)': 8,
               },
             ]
           );
@@ -558,7 +558,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
+                count: '8',
               },
             ]
           );
@@ -579,7 +579,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: 6,
+                count: 8,
               },
             ]
           );
@@ -589,7 +589,7 @@ module.exports = function (knex) {
             [],
             [
               {
-                count: '6',
+                count: '8',
               },
             ]
           );
@@ -611,7 +611,10 @@ module.exports = function (knex) {
                 'count(`id`)': 2,
               },
               {
-                'count(`id`)': 4,
+                'count(`id`)': 5,
+              },
+              {
+                'count(`id`)': 1,
               },
             ]
           );
@@ -624,7 +627,10 @@ module.exports = function (knex) {
                 count: '2',
               },
               {
-                count: '4',
+                count: '5',
+              },
+              {
+                count: '1',
               },
             ]
           );
@@ -637,7 +643,10 @@ module.exports = function (knex) {
                 count: '2',
               },
               {
-                count: '4',
+                count: '5',
+              },
+              {
+                count: '1',
               },
             ]
           );
@@ -650,7 +659,10 @@ module.exports = function (knex) {
                 'count(`id`)': 2,
               },
               {
-                'count(`id`)': 4,
+                'count(`id`)': 5,
+              },
+              {
+                'count(`id`)': 1,
               },
             ]
           );
@@ -663,7 +675,10 @@ module.exports = function (knex) {
                 'COUNT("ID")': 2,
               },
               {
-                'COUNT("ID")': 4,
+                'COUNT("ID")': 5,
+              },
+              {
+                'COUNT("ID")': 1,
               },
             ]
           );
@@ -676,7 +691,10 @@ module.exports = function (knex) {
                 '': 2,
               },
               {
-                '': 4,
+                '': 5,
+              },
+              {
+                '': 1,
               },
             ]
           );
@@ -694,6 +712,9 @@ module.exports = function (knex) {
                   {
                     'count(`id`)': 6,
                   },
+                  {
+                    'count(`id`)': 2,
+                  },
                 ]
               );
               tester(
@@ -704,6 +725,9 @@ module.exports = function (knex) {
                   {
                     count: '6',
                   },
+                  {
+                    count: '2',
+                  },
                 ]
               );
               tester(
@@ -714,6 +738,9 @@ module.exports = function (knex) {
                   {
                     count: '6',
                   },
+                  {
+                    count: '2',
+                  },
                 ]
               );
               tester(
@@ -724,6 +751,9 @@ module.exports = function (knex) {
                   {
                     'count(`id`)': 6,
                   },
+                  {
+                    'count(`id`)': 2,
+                  },
                 ]
               );
               tester(
@@ -734,6 +764,9 @@ module.exports = function (knex) {
                   {
                     'COUNT("ID")': 6,
                   },
+                  {
+                    'COUNT("ID")': 2,
+                  },
                 ]
               );
               tester(
@@ -744,6 +777,9 @@ module.exports = function (knex) {
                   {
                     '': 6,
                   },
+                  {
+                    '': 2,
+                  },
                 ]
               );
             });
diff --git a/test/integration2/query/select/joins.spec.js b/test/integration2/query/select/joins.spec.js
index fb755812..2c178687 100644
--- a/test/integration2/query/select/joins.spec.js
+++ b/test/integration2/query/select/joins.spec.js
@@ -446,6 +446,32 @@ describe('Joins', function () {
                   phone: null,
                   details: null,
                 },
+                {
+                  id: 7,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
+                {
+                  id: 8,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
               ]
             );
             tester(
@@ -533,6 +559,32 @@ describe('Joins', function () {
                   phone: null,
                   details: null,
                 },
+                {
+                  id: '7',
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
+                {
+                  id: '8',
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
               ]
             );
             tester(
@@ -620,6 +672,32 @@ describe('Joins', function () {
                   phone: null,
                   details: null,
                 },
+                {
+                  id: '7',
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
+                {
+                  id: '8',
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
               ]
             );
             tester(
@@ -707,6 +785,32 @@ describe('Joins', function () {
                   phone: null,
                   details: null,
                 },
+                {
+                  id: 7,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
+                {
+                  id: 8,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
               ]
             );
             tester(
@@ -794,6 +898,32 @@ describe('Joins', function () {
                   phone: null,
                   details: null,
                 },
+                {
+                  id: 7,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
+                {
+                  id: 8,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
               ]
             );
             tester(
@@ -881,6 +1011,32 @@ describe('Joins', function () {
                   phone: null,
                   details: null,
                 },
+                {
+                  id: '7',
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
+                {
+                  id: '8',
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  details: null,
+                },
               ]
             );
           });
@@ -992,6 +1148,36 @@ describe('Joins', function () {
                   details: null,
                   status: null,
                 },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
               ]
             );
             tester(
@@ -1091,6 +1277,36 @@ describe('Joins', function () {
                   details: null,
                   status: null,
                 },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
               ]
             );
             tester(
@@ -1190,6 +1406,36 @@ describe('Joins', function () {
                   details: null,
                   status: null,
                 },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
               ]
             );
             tester(
@@ -1289,6 +1535,36 @@ describe('Joins', function () {
                   details: null,
                   status: null,
                 },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
+                {
+                  id: null,
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
               ]
             );
             tester(
@@ -1388,6 +1664,36 @@ describe('Joins', function () {
                   details: null,
                   status: null,
                 },
+                {
+                  id: ['7', null],
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test7@example.com',
+                  logins: 2,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
+                {
+                  id: ['8', null],
+                  first_name: 'Test2',
+                  last_name: 'User2',
+                  email: 'test8@example.com',
+                  logins: 3,
+                  balance: 0,
+                  about: 'Lorem ipsum Dolore labore incididunt enim.',
+                  created_at: TEST_TIMESTAMP,
+                  updated_at: TEST_TIMESTAMP,
+                  phone: null,
+                  account_id: null,
+                  details: null,
+                  status: null,
+                },
               ]
             );
           });
@@ -1760,7 +2066,7 @@ describe('Joins', function () {
               'select `account_id` from `accounts` cross join `test_table_two` order by `account_id` asc',
               [],
               function (res) {
-                return res.length === 18;
+                return res.length === 24;
               }
             );
             tester(
@@ -1768,7 +2074,7 @@ describe('Joins', function () {
               'select "account_id" from "accounts" cross join "test_table_two" order by "account_id" asc',
               [],
               function (res) {
-                return res.length === 18;
+                return res.length === 24;
               }
             );
             tester(
@@ -1777,7 +2083,7 @@ describe('Joins', function () {
               [],
               function (res) {
                 // redshift, not supporting insert...returning, had to fake 6 of these in previous tests
-                return res.length === 12;
+                return res.length === 24;
               }
             );
             tester(
@@ -1785,7 +2091,7 @@ describe('Joins', function () {
               'select "account_id" from "accounts" cross join "test_table_two" order by "account_id" asc',
               [],
               function (res) {
-                return res.length === 18;
+                return res.length === 24;
               }
             );
             tester(
@@ -1793,7 +2099,7 @@ describe('Joins', function () {
               'select `account_id` from `accounts` cross join `test_table_two` order by `account_id` asc',
               [],
               function (res) {
-                return res.length === 18;
+                return res.length === 24;
               }
             );
             tester(
@@ -1801,7 +2107,7 @@ describe('Joins', function () {
               'select [account_id] from [accounts] cross join [test_table_two] order by [account_id] asc',
               [],
               function (res) {
-                return res.length === 18;
+                return res.length === 24;
               }
             );
           });
@@ -1824,7 +2130,7 @@ describe('Joins', function () {
               'select `account_id` from `accounts` left join `test_table_two` on `accounts`.`id` = (select `id` from `test_table_two` limit ?)',
               [1],
               function (res) {
-                return res.length === 8;
+                return res.length === 10;
               }
             );
             tester(
@@ -1832,7 +2138,7 @@ describe('Joins', function () {
               'select "account_id" from "accounts" left join "test_table_two" on "accounts"."id" = (select "id" from "test_table_two" limit ?)',
               [1],
               function (res) {
-                return res.length === 8;
+                return res.length === 10;
               }
             );
             tester(
@@ -1840,7 +2146,7 @@ describe('Joins', function () {
               'select "account_id" from "accounts" left join "test_table_two" on "accounts"."id" = (select "id" from "test_table_two" limit ?)',
               [1],
               function (res) {
-                return res.length === 8;
+                return res.length === 10;
               }
             );
             tester(
@@ -1848,7 +2154,7 @@ describe('Joins', function () {
               'select "account_id" from "accounts" left join "test_table_two" on "accounts"."id" = (select * from (select "id" from "test_table_two") where rownum <= ?)',
               [1],
               function (res) {
-                return res.length === 8;
+                return res.length === 10;
               }
             );
             tester(
@@ -1856,7 +2162,7 @@ describe('Joins', function () {
               'select `account_id` from `accounts` left join `test_table_two` on `accounts`.`id` = (select `id` from `test_table_two` limit ?)',
               [1],
               function (res) {
-                return res.length === 8;
+                return res.length === 10;
               }
             );
             tester(
@@ -1864,7 +2170,7 @@ describe('Joins', function () {
               'select [account_id] from [accounts] left join [test_table_two] on [accounts].[id] = (select top (?) [id] from [test_table_two])',
               [1],
               function (res) {
-                return res.length === 8;
+                return res.length === 10;
               }
             );
           });
diff --git a/test/integration2/query/select/selects.spec.js b/test/integration2/query/select/selects.spec.js
index 46547fa1..335658a1 100644
--- a/test/integration2/query/select/selects.spec.js
+++ b/test/integration2/query/select/selects.spec.js
@@ -82,43 +82,43 @@ describe('Selects', function () {
               'mysql',
               'select `id` from `accounts` order by `id` asc',
               [],
-              [1, 2, 3, 4, 5, 6]
+              [1, 2, 3, 4, 5, 6, 7, 8]
             );
             tester(
               'pg',
               'select "id" from "accounts" order by "id" asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
             tester(
               'pgnative',
               'select "id" from "accounts" order by "id" asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
             tester(
               'pg-redshift',
               'select "id" from "accounts" order by "id" asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
             tester(
               'sqlite3',
               'select `id` from `accounts` order by `id` asc',
               [],
-              [1, 2, 3, 4, 5, 6]
+              [1, 2, 3, 4, 5, 6, 7, 8]
             );
             tester(
               'oracledb',
               'select "id" from "accounts" order by "id" asc',
               [],
-              [1, 2, 3, 4, 5, 6]
+              [1, 2, 3, 4, 5, 6, 7, 8]
             );
             tester(
               'mssql',
               'select [id] from [accounts] order by [id] asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
           });
       });
@@ -133,43 +133,43 @@ describe('Selects', function () {
               'mysql',
               'select `accounts`.`id` from `accounts` order by `accounts`.`id` asc',
               [],
-              [1, 2, 3, 4, 5, 6]
+              [1, 2, 3, 4, 5, 6, 7, 8]
             );
             tester(
               'pg',
               'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
             tester(
               'pgnative',
               'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
             tester(
               'pg-redshift',
               'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
             tester(
               'sqlite3',
               'select `accounts`.`id` from `accounts` order by `accounts`.`id` asc',
               [],
-              [1, 2, 3, 4, 5, 6]
+              [1, 2, 3, 4, 5, 6, 7, 8]
             );
             tester(
               'oracledb',
               'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
               [],
-              [1, 2, 3, 4, 5, 6]
+              [1, 2, 3, 4, 5, 6, 7, 8]
             );
             tester(
               'mssql',
               'select [accounts].[id] from [accounts] order by [accounts].[id] asc',
               [],
-              ['1', '2', '3', '4', '5', '6']
+              ['1', '2', '3', '4', '5', '6', '7', '8']
             );
           });
       });
@@ -185,43 +185,43 @@ describe('Selects', function () {
               'mysql',
               'select `id` from `accounts` order by `id` asc limit 18446744073709551615 offset ?',
               [2],
-              [3, 4, 5, 6]
+              [3, 4, 5, 6, 7, 8]
             );
             tester(
               'pg',
               'select "id" from "accounts" order by "id" asc offset ?',
               [2],
-              ['3', '4', '5', '6']
+              ['3', '4', '5', '6', '7', '8']
             );
             tester(
               'pgnative',
               'select "id" from "accounts" order by "id" asc offset ?',
               [2],
-              ['3', '4', '5', '6']
+              ['3', '4', '5', '6', '7', '8']
             );
             tester(
               'pg-redshift',
               'select "id" from "accounts" order by "id" asc offset ?',
               [2],
-              ['3', '4', '5', '6']
+              ['3', '4', '5', '6', '7', '8']
             );
             tester(
               'sqlite3',
               'select `id` from `accounts` order by `id` asc limit ? offset ?',
               [-1, 2],
-              [3, 4, 5, 6]
+              [3, 4, 5, 6, 7, 8]
             );
             tester(
               'oracledb',
               'select * from (select row_.*, ROWNUM rownum_ from (select "id" from "accounts" order by "id" asc) row_ where rownum <= ?) where rownum_ > ?',
               [10000000000002, 2],
-              [3, 4, 5, 6]
+              [3, 4, 5, 6, 7, 8]
             );
             tester(
               'mssql',
               'select [id] from [accounts] order by [id] asc offset ? rows',
               [2],
-              ['3', '4', '5', '6']
+              ['3', '4', '5', '6', '7', '8']
             );
           });
       });
@@ -283,7 +283,16 @@ describe('Selects', function () {
               'mysql',
               'select /*+ invalid() */ `id` from `accounts` order by `id` asc',
               [],
-              [{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }]
+              [
+                { id: 1 },
+                { id: 2 },
+                { id: 3 },
+                { id: 4 },
+                { id: 5 },
+                { id: 6 },
+                { id: 7 },
+                { id: 8 },
+              ]
             );
             tester(
               'pg',
@@ -296,6 +305,8 @@ describe('Selects', function () {
                 { id: '4' },
                 { id: '5' },
                 { id: '6' },
+                { id: '7' },
+                { id: '8' },
               ]
             );
             tester(
@@ -309,6 +320,8 @@ describe('Selects', function () {
                 { id: '4' },
                 { id: '5' },
                 { id: '6' },
+                { id: '7' },
+                { id: '8' },
               ]
             );
             tester(
@@ -322,19 +335,39 @@ describe('Selects', function () {
                 { id: '4' },
                 { id: '5' },
                 { id: '6' },
+                { id: '7' },
+                { id: '8' },
               ]
             );
             tester(
               'sqlite3',
               'select /*+ invalid() */ `id` from `accounts` order by `id` asc',
               [],
-              [{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }]
+              [
+                { id: 1 },
+                { id: 2 },
+                { id: 3 },
+                { id: 4 },
+                { id: 5 },
+                { id: 6 },
+                { id: 7 },
+                { id: 8 },
+              ]
             );
             tester(
               'oracledb',
               'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
               [],
-              [{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }]
+              [
+                { id: 1 },
+                { id: 2 },
+                { id: 3 },
+                { id: 4 },
+                { id: 5 },
+                { id: 6 },
+                { id: 7 },
+                { id: 8 },
+              ]
             );
             tester(
               'mssql',
@@ -347,6 +380,8 @@ describe('Selects', function () {
                 { id: '4' },
                 { id: '5' },
                 { id: '6' },
+                { id: '7' },
+                { id: '8' },
               ]
             );
           });
@@ -415,7 +450,7 @@ describe('Selects', function () {
             });
           })
           .then(function () {
-            assert(count === 6, 'Six rows should have been streamed');
+            assert(count === 8, 'Eight rows should have been streamed');
           });
       });
 
@@ -454,7 +489,7 @@ describe('Selects', function () {
             });
           })
           .then(function () {
-            expect(rows).to.have.lengthOf(6);
+            expect(rows).to.have.lengthOf(8);
             rows.forEach((row) => {
               ['first_name', 'last_name', 'email'].forEach((field) =>
                 expect(row[field]).to.equal(row[field].toUpperCase())
@@ -556,13 +591,13 @@ describe('Selects', function () {
               'oracledb',
               'select "id" from "accounts" order by "id" desc',
               [],
-              [6, 5, 4, 3, 2, 1]
+              [8, 7, 6, 5, 4, 3, 2, 1]
             );
             tester(
               'mssql',
               'select [id] from [accounts] order by [id] desc',
               [],
-              ['6', '5', '4', '3', '2', '1']
+              ['8', '7', '6', '5', '4', '3', '2', '1']
             );
           });
       });
@@ -789,6 +824,14 @@ describe('Selects', function () {
                 email: 'test6@example.com',
                 logins: 2,
               },
+              {
+                email: 'test7@example.com',
+                logins: 2,
+              },
+              {
+                email: 'test8@example.com',
+                logins: 3,
+              },
             ]
           );
           tester(
@@ -820,6 +863,14 @@ describe('Selects', function () {
                 email: 'test6@example.com',
                 logins: 2,
               },
+              {
+                email: 'test7@example.com',
+                logins: 2,
+              },
+              {
+                email: 'test8@example.com',
+                logins: 3,
+              },
             ]
           );
         });
diff --git a/test/integration2/query/select/unions.spec.js b/test/integration2/query/select/unions.spec.js
index 8e8f6fc0..8a01a688 100644
--- a/test/integration2/query/select/unions.spec.js
+++ b/test/integration2/query/select/unions.spec.js
@@ -6,6 +6,7 @@ const {
   isOracle,
   isPgBased,
   isSQLite,
+  isPostgreSQL,
 } = require('../../../util/db-helpers');
 const { assertNumberArray } = require('../../../util/assertHelper');
 const {
@@ -162,6 +163,30 @@ describe('unions', function () {
           );
       });
 
+      it('handles nested unions with group by and limit', async function () {
+        if (!isPostgreSQL(knex)) {
+          return this.skip();
+        }
+        const results = await knex('accounts')
+          .count('logins')
+          .select('last_name')
+          .limit(1)
+          .groupBy('last_name')
+          .unionAll(function () {
+            this.count('logins')
+              .select('last_name')
+              .from('accounts')
+              .limit(1)
+              .groupBy('last_name')
+              .orderBy('last_name')
+              .where('logins', '>', '1');
+          }, true);
+        expect(results).to.eql([
+          { count: '2', last_name: 'User2' },
+          { count: '4', last_name: 'User' },
+        ]);
+      });
+
       describe('intersects', function () {
         before(async function () {
           await knex.schema.createTable('intersect_test', function (t) {
diff --git a/test/integration2/query/select/where.spec.js b/test/integration2/query/select/where.spec.js
index ba502de4..5c5a626e 100644
--- a/test/integration2/query/select/where.spec.js
+++ b/test/integration2/query/select/where.spec.js
@@ -598,13 +598,15 @@ describe('Where', function () {
             .select('*')
             .whereLike('first_name', 'Te%')
             .andWhereLike('email', '%example.com');
-          expect(result.length).to.equal(6);
+          expect(result.length).to.equal(8);
           expect(result[0].email).to.equal('test1@example.com');
           expect(result[1].email).to.equal('test2@example.com');
           expect(result[2].email).to.equal('test3@example.com');
           expect(result[3].email).to.equal('test4@example.com');
           expect(result[4].email).to.equal('test5@example.com');
           expect(result[5].email).to.equal('test6@example.com');
+          expect(result[6].email).to.equal('test7@example.com');
+          expect(result[7].email).to.equal('test8@example.com');
         });
 
         it('finds data using orWhereILike', async () => {
@@ -621,13 +623,15 @@ describe('Where', function () {
             .select('*')
             .whereILike('first_name', 'te%')
             .andWhereILike('email', '%examPle.COm');
-          expect(result.length).to.equal(6);
+          expect(result.length).to.equal(8);
           expect(result[0].email).to.equal('test1@example.com');
           expect(result[1].email).to.equal('test2@example.com');
           expect(result[2].email).to.equal('test3@example.com');
           expect(result[3].email).to.equal('test4@example.com');
           expect(result[4].email).to.equal('test5@example.com');
           expect(result[5].email).to.equal('test6@example.com');
+          expect(result[6].email).to.equal('test7@example.com');
+          expect(result[7].email).to.equal('test8@example.com');
         });
 
         it("doesn't find data using whereLike when different case sensitivity", async () => {
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index 12bd461e..2986d6b9 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -1977,7 +1977,7 @@ describe('QueryBuilder', () => {
       });
     testsql(wrappedChain, {
       mysql: {
-        sql: 'select * from `users` where `id` in (select max(`id`) from `users` union (select min(`id`) from `users`))',
+        sql: 'select * from `users` where `id` in ((select max(`id`) from `users`) union (select min(`id`) from `users`))',
         bindings: [],
       },
       mssql: {
@@ -1985,11 +1985,11 @@ describe('QueryBuilder', () => {
         bindings: [],
       },
       pg: {
-        sql: 'select * from "users" where "id" in (select max("id") from "users" union (select min("id") from "users"))',
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") union (select min("id") from "users"))',
         bindings: [],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" in (select max("id") from "users" union (select min("id") from "users"))',
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") union (select min("id") from "users"))',
         bindings: [],
       },
     });
@@ -2010,7 +2010,7 @@ describe('QueryBuilder', () => {
       );
     testsql(multipleArgumentsWrappedChain, {
       mysql: {
-        sql: 'select * from `users` where `id` = ? union (select * from `users` where `id` = ?) union (select * from `users` where `id` = ?)',
+        sql: '(select * from `users` where `id` = ?) union (select * from `users` where `id` = ?) union (select * from `users` where `id` = ?)',
         bindings: [1, 2, 3],
       },
       mssql: {
@@ -2018,11 +2018,11 @@ describe('QueryBuilder', () => {
         bindings: [1, 2, 3],
       },
       pg: {
-        sql: 'select * from "users" where "id" = ? union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
     });
@@ -2044,7 +2044,7 @@ describe('QueryBuilder', () => {
       );
     testsql(arrayWrappedChain, {
       mysql: {
-        sql: 'select * from `users` where `id` = ? union (select * from `users` where `id` = ?) union (select * from `users` where `id` = ?)',
+        sql: '(select * from `users` where `id` = ?) union (select * from `users` where `id` = ?) union (select * from `users` where `id` = ?)',
         bindings: [1, 2, 3],
       },
       mssql: {
@@ -2052,11 +2052,11 @@ describe('QueryBuilder', () => {
         bindings: [1, 2, 3],
       },
       pg: {
-        sql: 'select * from "users" where "id" = ? union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union (select * from "users" where "id" = ?) union (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
     });
@@ -2075,7 +2075,7 @@ describe('QueryBuilder', () => {
       });
     testsql(wrappedChain, {
       mysql: {
-        sql: 'select * from `users` where `id` in (select max(`id`) from `users` union all (select min(`id`) from `users`))',
+        sql: 'select * from `users` where `id` in ((select max(`id`) from `users`) union all (select min(`id`) from `users`))',
         bindings: [],
       },
       mssql: {
@@ -2083,11 +2083,11 @@ describe('QueryBuilder', () => {
         bindings: [],
       },
       pg: {
-        sql: 'select * from "users" where "id" in (select max("id") from "users" union all (select min("id") from "users"))',
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") union all (select min("id") from "users"))',
         bindings: [],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" in (select max("id") from "users" union all (select min("id") from "users"))',
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") union all (select min("id") from "users"))',
         bindings: [],
       },
     });
@@ -2108,7 +2108,7 @@ describe('QueryBuilder', () => {
       );
     testsql(multipleArgumentsWrappedChain, {
       mysql: {
-        sql: 'select * from `users` where `id` = ? union all (select * from `users` where `id` = ?) union all (select * from `users` where `id` = ?)',
+        sql: '(select * from `users` where `id` = ?) union all (select * from `users` where `id` = ?) union all (select * from `users` where `id` = ?)',
         bindings: [1, 2, 3],
       },
       mssql: {
@@ -2116,11 +2116,11 @@ describe('QueryBuilder', () => {
         bindings: [1, 2, 3],
       },
       pg: {
-        sql: 'select * from "users" where "id" = ? union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
     });
@@ -2142,7 +2142,7 @@ describe('QueryBuilder', () => {
       );
     testsql(arrayWrappedChain, {
       mysql: {
-        sql: 'select * from `users` where `id` = ? union all (select * from `users` where `id` = ?) union all (select * from `users` where `id` = ?)',
+        sql: '(select * from `users` where `id` = ?) union all (select * from `users` where `id` = ?) union all (select * from `users` where `id` = ?)',
         bindings: [1, 2, 3],
       },
       mssql: {
@@ -2150,133 +2150,168 @@ describe('QueryBuilder', () => {
         bindings: [1, 2, 3],
       },
       pg: {
-        sql: 'select * from "users" where "id" = ? union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?) union all (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
     });
   });
 
-  it('union alls', () => {
-    const chain = qb()
-      .select('*')
-      .from('users')
-      .where('id', '=', 1)
-      .unionAll(function () {
-        this.select('*').from('users').where('id', '=', 2);
+  describe('union alls', () => {
+    it('union alls', () => {
+      const chain = qb()
+        .select('*')
+        .from('users')
+        .where('id', '=', 1)
+        .unionAll(function () {
+          this.select('*').from('users').where('id', '=', 2);
+        });
+      testsql(chain, {
+        mysql: {
+          sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ?',
+          bindings: [1, 2],
+        },
+        mssql: {
+          sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
+          bindings: [1, 2],
+        },
+        pg: {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
+          bindings: [1, 2],
+        },
+        'pg-redshift': {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
+          bindings: [1, 2],
+        },
       });
-    testsql(chain, {
-      mysql: {
-        sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ?',
-        bindings: [1, 2],
-      },
-      mssql: {
-        sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
-        bindings: [1, 2],
-      },
-      pg: {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
-        bindings: [1, 2],
-      },
-      'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
-        bindings: [1, 2],
-      },
-    });
 
-    const multipleArgumentsChain = qb()
-      .select('*')
-      .from('users')
-      .where({ id: 1 })
-      .unionAll(
-        function () {
-          this.select('*').from('users').where({ id: 2 });
+      const multipleArgumentsChain = qb()
+        .select('*')
+        .from('users')
+        .where({ id: 1 })
+        .unionAll(
+          function () {
+            this.select('*').from('users').where({ id: 2 });
+          },
+          function () {
+            this.select('*').from('users').where({ id: 3 });
+          }
+        );
+      testsql(multipleArgumentsChain, {
+        mysql: {
+          sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ? union all select * from `users` where `id` = ?',
+          bindings: [1, 2, 3],
         },
-        function () {
-          this.select('*').from('users').where({ id: 3 });
-        }
-      );
-    testsql(multipleArgumentsChain, {
-      mysql: {
-        sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ? union all select * from `users` where `id` = ?',
-        bindings: [1, 2, 3],
-      },
-      mssql: {
-        sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
-        bindings: [1, 2, 3],
-      },
-      pg: {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
-        bindings: [1, 2, 3],
-      },
-      'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
-        bindings: [1, 2, 3],
-      },
+        mssql: {
+          sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
+          bindings: [1, 2, 3],
+        },
+        pg: {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
+          bindings: [1, 2, 3],
+        },
+        'pg-redshift': {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
+          bindings: [1, 2, 3],
+        },
+      });
+
+      const arrayChain = qb()
+        .select('*')
+        .from('users')
+        .where({ id: 1 })
+        .unionAll([
+          function () {
+            this.select('*').from('users').where({ id: 2 });
+          },
+          function () {
+            this.select('*').from('users').where({ id: 3 });
+          },
+        ]);
+      testsql(arrayChain, {
+        mysql: {
+          sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ? union all select * from `users` where `id` = ?',
+          bindings: [1, 2, 3],
+        },
+        mssql: {
+          sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
+          bindings: [1, 2, 3],
+        },
+        pg: {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
+          bindings: [1, 2, 3],
+        },
+        'pg-redshift': {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
+          bindings: [1, 2, 3],
+        },
+      });
     });
 
-    const arrayChain = qb()
-      .select('*')
-      .from('users')
-      .where({ id: 1 })
-      .unionAll([
-        function () {
-          this.select('*').from('users').where({ id: 2 });
+    it('with array of callbacks Issue #4364', () => {
+      // Issue #4364
+      const firstUnionAll = qb()
+        .unionAll([
+          function () {
+            this.select().from('users').where({ id: 1 });
+          },
+          function () {
+            this.select().from('users').where({ id: 2 });
+          },
+        ])
+        .first();
+      testsql(firstUnionAll, {
+        mysql: {
+          sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ? limit ?',
+          bindings: [1, 2, 1],
         },
-        function () {
-          this.select('*').from('users').where({ id: 3 });
+        mssql: {
+          sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
+          bindings: [1, 2],
         },
-      ]);
-    testsql(arrayChain, {
-      mysql: {
-        sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ? union all select * from `users` where `id` = ?',
-        bindings: [1, 2, 3],
-      },
-      mssql: {
-        sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
-        bindings: [1, 2, 3],
-      },
-      pg: {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
-        bindings: [1, 2, 3],
-      },
-      'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? union all select * from "users" where "id" = ?',
-        bindings: [1, 2, 3],
-      },
+        pg: {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? limit ?',
+          bindings: [1, 2, 1],
+        },
+        'pg-redshift': {
+          sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? limit ?',
+          bindings: [1, 2, 1],
+        },
+      });
     });
 
-    // Issue #4364
-    const firstUnionAll = qb()
-      .unionAll([
-        function () {
-          this.select().from('users').where({ id: 1 });
+    it('with array of callbacks Issue #5030', () => {
+      // Issue #4364
+      const firstUnionAll = qb()
+        .select('*')
+        .from('users')
+        .where('id', '=', 1)
+        .groupBy('id')
+        .unionAll(function () {
+          this.select('*').from('users').where('id', '=', 2);
+        }, true)
+        .first();
+      testsql(firstUnionAll, {
+        mysql: {
+          sql: '(select * from `users` where `id` = ? group by `id` limit ?) union all (select * from `users` where `id` = ?)',
+          bindings: [1, 2, 1],
         },
-        function () {
-          this.select().from('users').where({ id: 2 });
+        mssql: {
+          sql: 'select top (?) * from [users] where [id] = ? union all (select * from [users] where [id] = ?) group by [id]',
+          bindings: [1, 1, 2],
         },
-      ])
-      .first();
-    testsql(firstUnionAll, {
-      mysql: {
-        sql: 'select * from `users` where `id` = ? union all select * from `users` where `id` = ? limit ?',
-        bindings: [1, 2, 1],
-      },
-      mssql: {
-        sql: 'select * from [users] where [id] = ? union all select * from [users] where [id] = ?',
-        bindings: [1, 2],
-      },
-      pg: {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? limit ?',
-        bindings: [1, 2, 1],
-      },
-      'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? union all select * from "users" where "id" = ? limit ?',
-        bindings: [1, 2, 1],
-      },
+        pg: {
+          sql: '(select * from "users" where "id" = ? group by "id" limit ?) union all (select * from "users" where "id" = ?)',
+          bindings: [1, 2, 1],
+        },
+        'pg-redshift': {
+          sql: '(select * from "users" where "id" = ? group by "id" limit ?) union all (select * from "users" where "id" = ?)',
+          bindings: [1, 2, 1],
+        },
+      });
     });
   });
 
@@ -2565,11 +2600,11 @@ describe('QueryBuilder', () => {
         bindings: [],
       },
       pg: {
-        sql: 'select * from "users" where "id" in (select max("id") from "users" intersect (select min("id") from "users"))',
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") intersect (select min("id") from "users"))',
         bindings: [],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" in (select max("id") from "users" intersect (select min("id") from "users"))',
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") intersect (select min("id") from "users"))',
         bindings: [],
       },
     });
@@ -2594,11 +2629,11 @@ describe('QueryBuilder', () => {
         bindings: [1, 2, 3],
       },
       pg: {
-        sql: 'select * from "users" where "id" = ? intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
     });
@@ -2624,11 +2659,11 @@ describe('QueryBuilder', () => {
         bindings: [1, 2, 3],
       },
       pg: {
-        sql: 'select * from "users" where "id" = ? intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
       'pg-redshift': {
-        sql: 'select * from "users" where "id" = ? intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
+        sql: '(select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?) intersect (select * from "users" where "id" = ?)',
         bindings: [1, 2, 3],
       },
     });
diff --git a/test/util/dataInsertHelper.js b/test/util/dataInsertHelper.js
index 2141e1b7..1f95e1dc 100644
--- a/test/util/dataInsertHelper.js
+++ b/test/util/dataInsertHelper.js
@@ -99,6 +99,32 @@ async function insertAccounts(knex) {
     },
     'id'
   );
+
+  await knex('accounts').insert(
+    {
+      first_name: 'Test2',
+      last_name: 'User2',
+      email: 'test7@example.com',
+      about: 'Lorem ipsum Dolore labore incididunt enim.',
+      logins: 2,
+      created_at: TEST_TIMESTAMP,
+      updated_at: TEST_TIMESTAMP,
+    },
+    'id'
+  );
+
+  await knex('accounts').insert(
+    {
+      first_name: 'Test2',
+      last_name: 'User2',
+      email: 'test8@example.com',
+      about: 'Lorem ipsum Dolore labore incididunt enim.',
+      logins: 3,
+      created_at: TEST_TIMESTAMP,
+      updated_at: TEST_TIMESTAMP,
+    },
+    'id'
+  );
 }
 
 function insertAccount(knex, overrides) {