#706248 by bellHead and Damien Tournoud: Fixed field_sql_storage_field_storage_query() with 'count' option breaks on PostgreSQL and SQLite. (with tests)

3 files changed, 38 insertions, 12 deletions

diff --git a/includes/database/select.inc b/includes/database/select.inc
index f3fd8368a..b071c7454 100644
--- a/includes/database/select.inc
+++ b/includes/database/select.inc
@@ -1250,17 +1250,19 @@ class SelectQuery extends Query implements SelectQueryInterface {
     // Create our new query object that we will mutate into a count query.
     $count = clone($this);
 
-    // Zero-out existing fields and expressions.
-    $fields =& $count->getFields();
-    $fields = array();
-    $expressions =& $count->getExpressions();
-    $expressions = array();
-
+    if (!$count->distinct) {
+      // When not executing a distinct query, we can zero-out existing fields
+      // and expressions.
+      $fields =& $count->getFields();
+      $fields = array();
+      $expressions =& $count->getExpressions();
+      $expressions = array();
 
-    // Also remove 'all_fields' statements, which are expanded into tablename.*
-    // when the query is executed.
-    foreach ($count->tables as $alias => &$table) {
-      unset($table['all_fields']);
+      // Also remove 'all_fields' statements, which are expanded into tablename.*
+      // when the query is executed.
+      foreach ($count->tables as $alias => &$table) {
+        unset($table['all_fields']);
+      }
     }
 
     // Ordering a count query is a waste of cycles, and breaks on some
@@ -1268,6 +1270,13 @@ class SelectQuery extends Query implements SelectQueryInterface {
     $orders = &$count->getOrderBy();
     $orders = array();
 
+    if ($count->distinct) {
+      // If the query is distinct, we need to execute it in a subquery,
+      // because SQL99 does not support counting on distinct multiple fields.
+      $count = db_select($count);
+      $count->distinct = FALSE;
+    }
+
     // COUNT() is an expression, so we add that back in.
     $count->addExpression('COUNT(*)');
 
diff --git a/modules/field/modules/field_sql_storage/field_sql_storage.module b/modules/field/modules/field_sql_storage/field_sql_storage.module
index b0d0ed4c4..8229c523f 100644
--- a/modules/field/modules/field_sql_storage/field_sql_storage.module
+++ b/modules/field/modules/field_sql_storage/field_sql_storage.module
@@ -539,8 +539,12 @@ function field_sql_storage_field_storage_query($field_id, $conditions, $options)
 
   // For a count query, return the count now.
   if ($options['count']) {
-    $query->addExpression('COUNT(DISTINCT e.type,t.entity_id,t.revision_id)');
-    return $query->execute()->fetchField();
+    return $query
+      ->fields('t', array('etid', 'entity_id', 'revision_id'))
+      ->distinct()
+      ->countQuery()
+      ->execute()
+      ->fetchField();
   }
 
   // For a data query, add fields.
diff --git a/modules/simpletest/tests/database_test.test b/modules/simpletest/tests/database_test.test
index 19ecbe6e9..cf569f7cc 100644
--- a/modules/simpletest/tests/database_test.test
+++ b/modules/simpletest/tests/database_test.test
@@ -1909,6 +1909,19 @@ class DatabaseSelectComplexTestCase extends DatabaseTestCase {
   }
 
   /**
+   * Test that we can generate a count query from a query with distinct.
+   */
+  function testCountQueryDistinct() {
+    $query = db_select('test_task');
+    $task_field = $query->addField('test_task', 'task');
+    $query->distinct();
+
+    $count = $query->countQuery()->execute()->fetchField();
+
+    $this->assertEqual($count, 6, t('Counted the correct number of records.'));
+  }
+
+  /**
    * Confirm that we can properly nest conditional clauses.
    */
   function testNestedConditions() {