Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I have this query:

SELECT DISTINCT "spree_products".id FROM "spree_products"
INNER JOIN "spree_variants"
   ON "spree_variants"."product_id" = "spree_products"."id"
  AND "spree_variants"."is_master" = 't'
INNER JOIN "spree_prices"
   ON "spree_prices"."variant_id" = "spree_variants"."id"
INNER JOIN "spree_products_taxons"
   ON "spree_products_taxons"."product_id" = "spree_products"."id" 
INNER JOIN "spree_taxons"
   ON "spree_taxons"."id" = "spree_products_taxons"."taxon_id"
WHERE "spree_taxons"."id" IN (13)
AND "spree_prices"."currency" = 'USD'
AND ("spree_products".deleted_at IS NULL
  or "spree_products".deleted_at >= '2013-08-30 06:23:45.881488')
AND ("spree_products".available_on <= '2013-08-30 06:23:45.881974')
AND (spree_prices.amount IS NOT NULL) LIMIT 12 OFFSET 36

I get some records returned in different offsets. For example, I see a record with ID=53 in both OFFSET 24 and OFFSET 36.

If I add an ORDER BY spree_products.id clause to this statement, it makes the order determinate and I never see the same record returned in different offsets.

share|improve this question
1  
Of course you need ORDER BY to make Postgresql give you the records back in a certain order. But returning the same ID in different "offsets" when you have specified DISTINCT would seem like a bug to me. Or do you mean that in different executions of the same query the same ID can be returned at different positions? There's only one solution to that: add that ORDER BY clause like you have to. –  Colin 't Hart Aug 30 '13 at 7:07
    
All those double-quotes are not necessary... –  a_horse_with_no_name Aug 30 '13 at 8:20
    
@a_horse_with_no_name: That's what Active Record puts in place. –  Ryan Bigg Aug 30 '13 at 22:44
add comment

closed as off-topic by dezso, Max Vernon, Paul White, Kin, Mark Storey-Smith Aug 30 '13 at 17:56

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Max Vernon, Paul White, Kin, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

That's because the SQL standard doesn't require PostgreSQL to return rows in any particular order without an ORDER BY clause. It can return them however it feels like. See the manual for SELECT. In particular:

If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

and

When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.

This makes LIMIT and/or OFFSET without an ORDER BY unsafe in almost all cases. It's permitted in case you've forced the ordering in an inner query, as a bit of a performance hack, but it's rarely a good idea.

You must add an ORDER BY clause to make your statement deterministic.

In this case, with the DISTINCT there it's probably doing hash aggregation - which tends to return results in an inconsistent order from execution to execution. Even if you were doing an index scan that usually returns results in order, you cannot assume that it will unless you tell PostgreSQL you require the rows in a particular order.

This is closely related to people who ask how to select rows in the "natural" order of a table; tables and result sets don't have a natural order. They're sets not lists. If you want an ordering you must specify one on an operation by operation basis.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.