Re: [Haskelldb-users] haskelldb, LIMIT, OFFSET, DISTINCT

[Peter G. <pe...@gm...>]

On 04/04/2008, at 10:09 PM, Justin Bailey wrote:

> On Thu, Apr 3, 2008 at 10:32 PM, Peter Gammie <pe...@gm...>  
> wrote:
>> Sorry, the problem has been debugged out of existence, so I no  
>> longer know
>> what precisely the problem was. In my case the SQL was definitely  
>> incorrect.
>> I still have that (I think) if you want to see it.
>
> Please!

OK, here it is, it's huge:

SELECT item_id6 as item_id,
        parent6 as parent,
        root6 as root,
        date_posted6 as date_posted,
        date_last_update6 as date_last_update,
        item_type6 as item_type,
        lang6 as lang,
        owner6 as owner,
        title6 as title,
        summary6 as summary,
        contents6 as contents,
        intfield6 as intfield
FROM (SELECT item_id5 as item_id6,
              parent3 as parent6,
              root3 as root6,
              date_posted3 as date_posted6,
              date_last_update3 as date_last_update6,
              item_type5 as item_type6,
              lang5 as lang6,
              owner5 as owner6,
              title5 as title6,
              summary5 as summary6,
              contents5 as contents6,
              COUNT(item_id4) as intfield6
       FROM (SELECT item_id as item_id4,
                    tag as tag4
             FROM items_tags as T1) as T1,
            (SELECT item_id2 as item_id3,
                    parent1 as parent3,
                    root1 as root3,
                    date_posted1 as date_posted3,
                    date_last_update1 as date_last_update3,
                    item_type2 as item_type3,
                    title2 as title3
             FROM (SELECT item_id as item_id2,
                          item_type as item_type2,
                          lang as lang2,
                          title as title2,
                          date_posted as date_posted1,
                          date_last_update as date_last_update1
                   FROM items_descriptions as T1) as T1,
                  (SELECT item_id as item_id1,
                          parent as parent1,
                          root as root1
                   FROM items as T1) as T2
             WHERE ((item_type2 = 'tag') AND (lang2 = 'en')) AND  
(item_id1 = item_id2)) as T2,
            (SELECT item_id as item_id5,
                    item_type as item_type5,
                    lang as lang5,
                    owner as owner5,
                    title as title5,
                    summary as summary5,
                    contents as contents5
             FROM items_descriptions as T1) as T3
       WHERE (item_id3 = item_id5) AND (item_id3 = tag4)
       GROUP BY item_id6,
                parent6,
                root6,
                date_posted6,
                date_last_update6,
                item_type6,
                lang6,
                owner6,
                title6,
                summary6,
                contents6
       ORDER BY item_type3 ASC,
                title3 ASC) as T1

Putting this into PostgreSQL's psql, I get:

ERROR:  column "t2.item_type3" must appear in the GROUP BY clause or  
be used in an aggregate function

I can send you the SQL that creates the database if you want to play  
with it more.

cheers
peter