Changes between Initial Version and Version 1 of Ticket #1435


Ignore:
Timestamp:
Apr 9, 2007, 7:23:38 PM (17 years ago)
Author:
Noah Kantrowitz
Comment:

Fixing formatting

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #1435 – Description

    initial v1  
    11Saw this in my logs:
    2 
     2{{{
    33Apr  6 16:29:53 ops5 postgres[17289]: [2-1] ERROR:  column "topic.subject" must appear in the GROUP BY clause or be used in an aggregate function
    44Apr  6 16:29:53 ops5 postgres[17289]: [2-2] STATEMENT:  SELECT m.id, m.forum, m.topic, m.time, m.author, m.body, t.subject FROM message m LEFT JOIN (SELECT subject, id FROM
    55Apr  6 16:29:53 ops5 postgres[17289]: [2-3]  topic GROUP BY id) t ON t.id = m.topic WHERE body LIKE '%subversion%'
     6}}}
    67
    78Appears to be coming from:
    8 
     9{{{
    910sql = "SELECT m.id, m.forum, m.topic, m.time, m.author, m.body," \
    1011" t.subject FROM message m LEFT JOIN (SELECT subject, id FROM" \
    1112" topic GROUP BY id) t ON t.id = m.topic WHERE body LIKE '%%%s%%'" \
    1213 % (query)
     14}}}
    1315
    1416That subquery is not valid usage of a group by; ie, which subject should it be yielding for each id? Using min() or max() might be a reasonable stop gap. However, AFA Postgres is concerned, I think DISTINCT ON () would be the usual way to go. Of course, I'm not sure of DISTINCT ON's portability(dont use mysql or sqlite).