PostgreSQL changes result when i play with offset, limit

Question

Hi guys im using the below query
If i change limit to 10 or 20 the results are different
I'm learning postgresql i couldn't figure it out what is the problem
Is there postgresql specific warning ?

 WITH RECURSIVE children AS (
        SELECT id, name, 1 AS depth
        FROM wapi_categories
        WHERE parentid = 1 AND visible = true
        UNION ALL
        SELECT a.id, a.name, depth+1
        FROM wapi_categories a
        JOIN children b ON(a.parentid = b.id)
        WHERE a.visible = true
        ) SELECT wapi_issues.* FROM (SELECT DISTINCT ON(wapi_issues.publicationid) wapi_issues.* FROM wapi_publications
                                RIGHT JOIN wapi_category_publication ON wapi_category_publication.publication_id = wapi_publications.id RIGHT JOIN (
            SELECT *,
                max(wapi_issues.issue_identifier) OVER (PARTITION BY wapi_issues.publicationid) max_id FROM wapi_issues WHERE wapi_issues.status = true ORDER BY wapi_issues.issue_identifier DESC
                                ) AS wapi_issues ON wapi_issues.publicationid = wapi_category_publication.publication_id
                                AND wapi_issues.issue_identifier = max_id
                                RIGHT JOIN wapi_issue_files ON wapi_issue_files.issueid = wapi_issues.id WHERE wapi_publications.status = true AND (wapi_category_publication.category_id IN (
            SELECT id FROM children
        ) OR wapi_category_publication.category_id = 1) AND wapi_issue_files.pdf = true) AS wapi_issues ORDER BY wapi_issues.issue_identifier DESC
        OFFSET 0 LIMIT 20

wapi_categories  
------------------
id, name, parentid  
------------------
1, "Root", 0  
2, "Child 1", 1  
3, "Child 2", 1  

wapi_publication  
------------------
id, name, status  
------------------
1, "Publication 1", true    
2, "Publication 2", true  

wapi_categories_publication  
------------------
id, category_id, publication_id  
------------------
1, 2, 1  
1, 3, 2  

wapi_issues  
------------------
id, publicationid, title, issue_identifier, status  
------------------
1, 1, "Issue 1", 1, true  
2, 1, "Issue 2", 3, true  
3, 2, "Issue 3", 1, true  
4, 2, "Issue 4", 2, true  

wapi_issue_files  
------------------
id, issueid, file, status  
------------------
1, 1, "file1", true  
2, 1, "file2", true  
3, 2, "file3", true  
4, 2, "file4", true

Expected Result

Simply i want to get distinct wapi_publications and their last issues sorting by wapi_issues.issue_identifier

id, publicationid, title, issue_identifier, status  
------------------
3, 1, "Issue 2", 3, true  
4, 2, "Issue 4", 2, true

What i mean by saying the results are different

When the limit is 10;

id, title  
1, "A"  
2, "B"  
3, "C"  
4, "D"  
5, "E"  
.  
.  
.

When the limit is 20;

id, title
2, "B"
4, "D"
1, "A"
5, "E"
3, "C"  
.
.
.

"*If i change limit to 10 or 20 the results are different*" - yes of course they are. You just asked Postgres to display a different result. Please add sample data of the expected and actual output — , Apr 01 '15 at 14:28
lol @a_horse_with_no_name i got what you mean now ^^ I updated question describing what i mean — Can Tecim, Apr 01 '15 at 18:52

score 1 · Accepted Answer · answered Apr 10 '15 at 12:27

1

I ended up changing order by clause with this

ORDER BY wapi_issues.id ASC, wapi_issues.issue_identifier DESC

What i understand postgresql needs explicit sortings

answered Apr 10 '15 at 12:27

Can Tecim

577
8
18

score 0 · Answer 2 · answered Apr 01 '15 at 14:29

0

Maybe you have multiple wapi_issues.issue_identifier, and this prevents your ordering clause to create deterministic output

answered Apr 01 '15 at 14:29

Jack

1,488
11
21

So with this; wapi_category_publication.category_id IN ( SELECT id FROM children ) i get multipe times a row because it belongs to multiple category How do i prevent that ? – Can Tecim Apr 01 '15 at 18:56

PostgreSQL changes result when i play with offset, limit

Expected Result

What i mean by saying the results are different

2 Answers2