Closed Bug 612130 Opened 14 years ago Closed 14 years ago

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

Categories

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

Product:
Cloud Services Graveyard
Component:
Server: Sync
Type:
defect
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: rysiek, Assigned: tarek)

Details

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
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.
Assignee: nobody → 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.
flat queries work flawlessly, I can confirm that.
Done in http://hg.mozilla.org/services/server-storage/rev/13352b0adbb7

Thanks for all the tests, Michal
Status: UNCONFIRMED → RESOLVED
Closed: 14 years ago
Resolution: --- → FIXED
Michal, can you verify this is fixed?  Thanks ahead of time.
confirming, works now.
Product: Cloud Services → Cloud Services Graveyard
You need to log in before you can comment on or make changes to this bug.