From 57c8e957c2dd1a476eac0d2a7e45204cd427f99c Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Thu, 15 Jun 2023 19:56:34 -0700
Subject: [PATCH] CRM_Utils_SQL_Select - The default alias for subquery should
 be more distinctive

1. If you're writing basic SQL from scratch, you would expect the symbol `a` to be available to you
   (e.g. for use in column-aliases or join-aliases). It shouldn't be a reserved word.

2. In the case of Api4EntitySetQuery, it's important to use the alias as `a` - because that matches the
   rest of the api4 query-building.
---
 CRM/Utils/SQL/Select.php                   | 15 +++++++++++++--
 Civi/Api4/Query/Api4EntitySetQuery.php     |  2 +-
 tests/phpunit/CRM/Utils/SQL/SelectTest.php |  4 ++--
 3 files changed, 16 insertions(+), 5 deletions(-)

diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php
index d7a52b002e..0937b8aa71 100644
--- a/CRM/Utils/SQL/Select.php
+++ b/CRM/Utils/SQL/Select.php
@@ -73,6 +73,7 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
   private $selects = [];
   private $from;
   private $setOps;
+  private $setAlias;
   private $joins = [];
   private $wheres = [];
   private $groupBys = [];
@@ -95,12 +96,21 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
   }
 
   /**
-   * Create a new SELECT-like query where.
+   * Create a new SELECT-like query by performing set-operations (e.g. UNION).
+   *
+   * For example, if you want to query two tables and treat the results as one combined-set, then
+   * this is s a set-operation.
+   *
+   * $queryA = CRM_Utils_SQL_Select::from('table_a');
+   * $queryB = CRM_Utils_SQL_Select::from('table_b');
+   * $querySet = CRM_Utils_SQL_Select::fromSet()->union('DISTINCT', [$queryA, $queryB])->toSQL();
    *
    * @param array $options
+   *   Ex: ['setAlias' => 'uniondata']
    * @return CRM_Utils_SQL_Select
    */
   public static function fromSet($options = []) {
+    $options = array_merge(['setAlias' => '_sql_set'], $options);
     $result = new self(NULL, $options);
     $result->setOps = [];
     return $result;
@@ -126,6 +136,7 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
   public function __construct($from, $options = []) {
     $this->from = $from;
     $this->mode = $options['mode'] ?? self::INTERPOLATE_AUTO;
+    $this->setAlias = $options['setAlias'] ?? NULL;
   }
 
   /**
@@ -561,7 +572,7 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
         $sql .= $setOp[0];
         $sql .= '(' . (is_object($setOp[1]) ? $setOp[1]->toSQL() : $setOp[1]) . ')';
       }
-      $sql .= ") a\n";
+      $sql .= ") {$this->setAlias}\n";
     }
     foreach ($this->joins as $join) {
       $sql .= $join . "\n";
diff --git a/Civi/Api4/Query/Api4EntitySetQuery.php b/Civi/Api4/Query/Api4EntitySetQuery.php
index 4c0b32e66e..bd960d2473 100644
--- a/Civi/Api4/Query/Api4EntitySetQuery.php
+++ b/Civi/Api4/Query/Api4EntitySetQuery.php
@@ -27,7 +27,7 @@ class Api4EntitySetQuery extends Api4Query {
   public function __construct($api) {
     parent::__construct($api);
 
-    $this->query = \CRM_Utils_SQL_Select::fromSet();
+    $this->query = \CRM_Utils_SQL_Select::fromSet(['setAlias' => static::MAIN_TABLE_ALIAS]);
     $isAggregate = $this->isAggregateQuery();
 
     foreach ($api->getSets() as $index => $set) {
diff --git a/tests/phpunit/CRM/Utils/SQL/SelectTest.php b/tests/phpunit/CRM/Utils/SQL/SelectTest.php
index 386a522b6f..399b01b696 100644
--- a/tests/phpunit/CRM/Utils/SQL/SelectTest.php
+++ b/tests/phpunit/CRM/Utils/SQL/SelectTest.php
@@ -314,7 +314,7 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase {
     $u = CRM_Utils_SQL_Select::fromSet()->union('ALL', [$a, $b])->limit(100)->orderBy('a_name');
     $expectA = 'SELECT a_name FROM a WHERE (a1 = 100) ';
     $expectB = 'SELECT b_name FROM b WHERE (b2 = "ab cd") ';
-    $expectUnion = "SELECT * FROM (($expectA) UNION ALL ($expectB)) a ORDER BY a_name LIMIT 100 OFFSET 0";
+    $expectUnion = "SELECT * FROM (($expectA) UNION ALL ($expectB)) _sql_set ORDER BY a_name LIMIT 100 OFFSET 0";
     $this->assertLike($expectUnion, $u->toSQL());
   }
 
@@ -330,7 +330,7 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase {
     $expectA = 'SELECT a_name FROM a WHERE (a1 = 100) ';
     $expectB = 'SELECT b_name FROM b WHERE (b2 = "bb bb") ';
     $expectC = 'SELECT c_name FROM c WHERE (c3 = "cc cc") ';
-    $expectUnion = "SELECT * FROM (($expectA) UNION ALL ($expectB) UNION DISTINCT ($expectC)) a ORDER BY a_name LIMIT 100 OFFSET 0";
+    $expectUnion = "SELECT * FROM (($expectA) UNION ALL ($expectB) UNION DISTINCT ($expectC)) _sql_set ORDER BY a_name LIMIT 100 OFFSET 0";
     $this->assertLike($expectUnion, $u->toSQL());
   }
 
-- 
2.25.1