612130 - PostgreSQL storage backend fails - subquery uses ungrouped column "wbo.username" from outer query

Status: RESOLVED FIXED

(Cloud Services Graveyard :: Server: Sync, defect)

Description

[Michał 'rysiek' Woźniak]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; pl-PL; rv:1.9.2.10) Gecko/20100915 Ubuntu/10.04 (lucid) Firefox/3.6.10
Build Identifier: 

PostgreSQL doesn't allow non-grouped-by columns in subqueries, if a GROUP BY column is present.

query:
SELECT wbo.collection, (SELECT wbo_1.modified 
        FROM wbo AS wbo_1 
        WHERE wbo_1.username = wbo.username AND wbo_1.collection = wbo.collection ORDER BY wbo_1.username DESC, wbo_1.collection DESC, wbo_1.modified DESC 
         LIMIT 1) AS anon_1 
        FROM wbo 
        WHERE wbo.username = E'20000' AND wbo.ttl > 1289749130 GROUP BY wbo.collection

Fails with:
subquery uses ungrouped column "wbo.username" from outer query

Reproducible: Always

Steps to Reproduce:
1. install python Sync server
2. configure it to use PostgreSQL as the storage backend
3. try to sync
Actual Results:  
error in PgSQL logs; "Invalid server configuration" message in FfSync Client

Expected Results:  
Proper synchronisation

Comment 1

[Michał 'rysiek' Woźniak]

it's a regression introduced in rev. 305

adding ", wbo.username" to the Group By clause fixes the problem for PgSQL:

SELECT wbo.collection, (SELECT wbo_1.modified 
        FROM wbo AS wbo_1 
        WHERE wbo_1.username = wbo.username AND wbo_1.collection = wbo.collection ORDER BY wbo_1.username DESC, wbo_1.collection DESC, wbo_1.modified DESC 
         LIMIT 1) AS anon_1 
        FROM wbo 
        WHERE wbo.username = E'20000' AND wbo.ttl > 1289749130 GROUP BY wbo.collection, wbo.username

I see no reason for them not to work on MySQL, SQLite et al.

not quite sure where to change and what, looking into it though.

Comment 2

[Tarek Ziadé (:tarek)]

We'll use the previous flat queries if postgres is the configured DB. The sub queries are optimizations I made, specific to MySQL. I'll write a patch.

Comment 3

[Michał 'rysiek' Woźniak]

flat queries work flawlessly, I can confirm that.

Comment 4

[Tarek Ziadé (:tarek)]

Done in http://hg.mozilla.org/services/server-storage/rev/13352b0adbb7

Thanks for all the tests, Michal

Comment 5

[Tracy Walker [:tracy]]

Michal, can you verify this is fixed?  Thanks ahead of time.

Comment 6

[Michał 'rysiek' Woźniak]

confirming, works now.