serendipity_event_freetag & postgresql errors & fix
Posted: Thu Apr 19, 2007 3:49 pm
Hello,
S9Y and Freetag plugin and Postgresql (now respectively at 1.1.2 and 2.85 and 8.x) have been creating these errors for quite some time.
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
STATEMENT: SELECT DISTINCT e1.entryid,
e2.title
FROM {PREFIX}entrytags AS e1
LEFT JOIN {PREFIX}entries AS e2
ON e1.entryid = e2.id
WHERE e1.tag IN ('sometag1', 'othertag2', 'anothertag3')
AND e1.entryid != 21
AND e2.isdraft = 'false'
AND e2.timestamp <= 1176964710
ORDER BY e2.timestamp DESC
LIMIT 5
ERROR: column "two.entryid" must appear in the GROUP BY clause or be used in an aggregate function
STATEMENT: SELECT one.tag, two.entryid, count(two.tag) AS total
FROM {PREFIX}entrytags
AS one
JOIN {PREFIX}entrytags AS two
ON two.entryid = 21
AND one.tag = two.tag
GROUP BY one.tag
ORDER BY total DESC
LIMIT 5
I'm not good at sql, but solutions should be:
Fixing first one needs "SELECT DISTINCT e1.entryid, e2.title, e2.timestamp"
Fixing second one needs "GROUP BY one.tag, two.entryid".
S9Y and Freetag plugin and Postgresql (now respectively at 1.1.2 and 2.85 and 8.x) have been creating these errors for quite some time.
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
STATEMENT: SELECT DISTINCT e1.entryid,
e2.title
FROM {PREFIX}entrytags AS e1
LEFT JOIN {PREFIX}entries AS e2
ON e1.entryid = e2.id
WHERE e1.tag IN ('sometag1', 'othertag2', 'anothertag3')
AND e1.entryid != 21
AND e2.isdraft = 'false'
AND e2.timestamp <= 1176964710
ORDER BY e2.timestamp DESC
LIMIT 5
ERROR: column "two.entryid" must appear in the GROUP BY clause or be used in an aggregate function
STATEMENT: SELECT one.tag, two.entryid, count(two.tag) AS total
FROM {PREFIX}entrytags
AS one
JOIN {PREFIX}entrytags AS two
ON two.entryid = 21
AND one.tag = two.tag
GROUP BY one.tag
ORDER BY total DESC
LIMIT 5
I'm not good at sql, but solutions should be:
Fixing first one needs "SELECT DISTINCT e1.entryid, e2.title, e2.timestamp"
Fixing second one needs "GROUP BY one.tag, two.entryid".
