I'm in the process of trying to get this module to work with PostgreSQL. In the logs, I'm seeing these errors:

pg_query(): Query failed: ERROR: column "advagg_bundles.filename_md5" must appear in the GROUP BY clause or be used in an aggregate function LINE 4: SELECT * ^ in [path_to_drupal]/includes/database.pgsql.inc on line 138.

query: SELECT COUNT(*) FROM ( SELECT * FROM advagg_bundles WHERE timestamp < 1317347113 GROUP BY bundle_md5 ) AS advagg_count in [path_to_drupal]/sites/all/modules/advagg/advagg.module on line 315.

The offending SQL seems to be

$bundles_removed += db_result(db_query("
    SELECT COUNT(*)
    FROM (
      SELECT *
      FROM {advagg_bundles}
      WHERE timestamp < %d
      GROUP BY bundle_md5
    ) AS advagg_count", $max_bundle_time));

Unless I'm missing something, can't we get rid of the sub-select and just write it as the following?

$bundles_removed += db_result(db_query("
SELECT COUNT(*) AS advagg_count
FROM {advagg_bundles}
WHERE timestamp < %d
GROUP BY bundle_md5", $max_bundle_time));

Patch attached.

CommentFileSizeAuthor
#7 1338340-advagg_pgsql_group_by_filename_md5-2.patch684 bytesChrisLaFrancis
advagg_pgsql_group_by_filename_md5.patch689 bytesChrisLaFrancis

Comments

mikeytown2’s picture

mikeytown2 commented
Status: Active » Needs review

Didn't see this as it wasn't set to needs review.

mikeytown2’s picture

mikeytown2 commented

Your patch doesn't work correctly.

SELECT COUNT(*)
FROM (
  SELECT *
  FROM advagg_bundles
  WHERE timestamp > 10000
  GROUP BY bundle_md5
) AS advagg_count

returns 1,044

SELECT COUNT(*) AS advagg_count
FROM advagg_bundles
WHERE timestamp > 10000
GROUP BY bundle_md5

returns 1,044 ROWS.

Can you try this query?

SELECT COUNT(*) AS count
FROM (
  SELECT COUNT(*) AS sub_count
  FROM advagg_bundles AS advagg_bundles
  WHERE advagg_bundles.timestamp > 10000
  GROUP BY advagg_bundles.bundle_md5
) AS advagg_count
ChrisLaFrancis’s picture

ChrisLaFrancis commented

I actually disabled the module for now as there were several other PostgreSQL-related incompatibilities that I just don't have the time to work through at the moment. When I get a little time in the near future, I'll give it a try again.

ChrisLaFrancis’s picture

ChrisLaFrancis commented

I haven't tested anything, but just going by what you wrote, I think the difference you're seeing ("1044" vs "1044 ROWS") is just how whatever SQL client you're using is formatting the result of count(). Count() only returns a number, not the actual rows or the number of rows with the string "ROWS" appended to it.

In PostgreSQL, these two queries return the exact same result:

db=# select count(*) from blocks;
 count 
-------
    28
(1 row)

db=# select count(*) from (select * from blocks) as subcount;
 count 
-------
    28
(1 row)

This query, however, which is selecting the count() of a subquery with a count(), only returns one row, because the result of from count() is really just one row with the lone value of 28.

db=# select count(*) from (select count(*) from blocks) as subcount;
 count 
-------
     1
(1 row)
mikeytown2’s picture

mikeytown2 commented

I mean it returns 1,044 different values. The original query returns one value. The way the code is written, it is expecting only one value.

ChrisLaFrancis’s picture

ChrisLaFrancis commented

I'm curious now, and I would really like to be able to use this module. I'll try to look at this later tonight.

ChrisLaFrancis’s picture

ChrisLaFrancis commented
StatusFileSize
new 1338340-advagg_pgsql_group_by_filename_md5-2.patch684 bytes

I see what you're saying now. My mistake. I think the problem is that since it's using the GROUP BY, it's giving us a count of the occurrences of each distinct value. Here is the new query using a DISTINCT in the COUNT() instead of a GROUP BY:

SELECT COUNT(DISTINCT bundle_md5) AS advagg_count
FROM advagg_bundles
WHERE timestamp < 10000

Patch attached.

mikeytown2’s picture

mikeytown2 commented
Status: Needs review » Fixed

Thanks! Patch above has been committed.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.