Error with postgresql (GROUP BY)
wa2nlinux - March 5, 2009 - 02:20
| Project: | User Stats |
| Version: | 6.x-1.0-beta2 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Jump to:
Description
* warning: pg_query() [function.pg-query]: Query failed: ERROR: column "user_stats_ips.first_seen_timestamp" must appear in the GROUP BY clause or be used in an aggregate function in /home/forum/drupal-6.10/includes/database.pgsql.inc on line 139.
* user warning: query: SELECT MAX(ip_address) FROM user_stats_ips WHERE uid = 1 ORDER BY first_seen_timestamp in /home/forum/drupal-6.10/sites/all/modules/user_stats/user_stats.module on line 98.
#1
What does MAX(ip_address) address for?
No? In fact, I don't understand the meaning of this SQL query.
Could you explain what it does ?
In user_stats.module, this is this line:
$query = db_query("SELECT MAX(ip_address)FROM {user_stats_ips} WHERE uid = %d
ORDER BY first_seen_timestamp", $uid);
I think the SQL command **could be*** wrong in both MySQL and PostgreSQL.
The SQL command is trying to select the fist row. This should be:
$query = db_query("SELECT ip_addressFROM {user_stats_ips} WHERE uid = %d
ORDER BY first_seen_timestamp LIMIT 1", $uid);
#2
Status patch reviewed by the community.
#3
$query = db_query("SELECT ip_addressFROM {user_stats_ips} WHERE uid = %d
ORDER BY first_seen_timestamp LIMIT 1", $uid);
The original query may be wrong, we are not trying to select the maximum value of an IP, but the first row.
#4
I ran into this as well. The GROUP BY problem is typical in PostgreSQL since MySQL conveniently ignores what is normally bad logic when GROUP BYs are involved.
I too however was wondering what the rationale for the MAX(ip_address) call was. It seems to me that the desired behavior is to return the most recently used IP address which as others above have pointed out can be done with an ORDER BY and LIMIT.
#5
Ok it seem to be work :D, I'll report if anything goes wrong
#6
Fixed. Thanks. :)
#7
Wait ... seem still error on it
# user warning: Statistic "ip_address" does not exist. in /home/forum/drupal-6.10/sites/all/modules/user_stats/user_stats.module on line 168.#8
Error was gone using dev version
#9
Automatically closed -- issue fixed for 2 weeks with no activity.