Help ... Unexpected results when using limit/offset with select statement..DB corruption? - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I got some weird results when processing select statements with limit and offset.   I think its some
kindof database corruption but I was wondering what other’s think.</span></font><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><b><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial;font-weight:
bold">Background:</span></font></b></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Thetable I’m having the issue with is described below.  The thing to note is
theprimary key </span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><prestyle="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">ice=# \d nc_host_datum</span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">        Table "public.nc_host_datum"</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">        Column        |  Type   | Modifiers</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">----------------------+---------+-----------</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> host_id              | bigint  | not null</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> host_datum_type_id   | integer | not null</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> host_datum_source_id | integer | not null</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> data                 | text    | not null</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">Indexes:</span></font></pre><pre style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">    <b><span style="font-weight:bold">"nc_host_datum_pkey" PRIMARY KEY, btree (host_id,
host_datum_type_id)</span></b></span></font></pre><prestyle="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Foreign-key constraints:</span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">    "foreign_key_01" FOREIGN KEY (host_id) REFERENCES nc_host(host_id) ON UPDATE CASCADE ON DELETE
CASCADE</span></font></pre><prestyle="margin-left:.5in"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">    "foreign_key_02" FOREIGN KEY (host_datum_type_id) REFERENCES
nc_host_datum_type(host_datum_type_id)ON UPDATE RESTRICT ON DELETE RESTRICT</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">    "foreign_key_03" FOREIGN KEY (host_datum_source_id) REFERENCES
nc_host_datum_source(host_datum_source_id)ON UPDATE RESTRICT ON DELETE RESTRICT</span></font></pre><pre><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial"><br /> 
<b><span style="font-weight:bold">Problem:</span></b></span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Iperform the following select (notice that the group by is by the primary
key).</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><prestyle="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">select host_id, host_datum_type_id, count(*)   </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">from nc_host_datum where host_id in</span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  ( select host_id </span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  from nc_host </span></font></pre><pre style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  where audit_id=2041) </span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">group by host_id, host_datum_type_id;</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">andget the following result (There are many more rows but these are all the
rowsfor host_id =   963711):</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">host_id  | host_datum_type_id | count </span></font></pre><pre><font
face="Arial"size="2"><span
style="font-size:10.0pt;font-family:Arial">-------------+------------------------------+---------</span></font></pre><pre><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">  963711 |                 58           |    
1</span></font></pre><pre><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  963711
|                54           |     1</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> 963711 |                 39           |     1</span></font></pre><pre><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">  963711 |                 28           |    
1</span></font></pre><pre><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  963711
|                27           |     1</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Noticethat there are 5 rows for host_id 963711 and the host_datum_type_id's
areall unique </span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">ThenI perform the following selects</span></font></pre><pre><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">SELECT host_id, host_datum_type_id, host_datum_source_id, data  </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">FROM nc_host_datum  INNER JOIN nc_host USING (host_id)  </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">WHERE audit_id=2041  </span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">ORDER BY host_id  </span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">LIMIT 49 OFFSET 1372;</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">And </span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><prestyle="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">SELECT host_id, host_datum_type_id, host_datum_source_id, data  </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">FROM nc_host_datum  INNER JOIN nc_host USING (host_id)  </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">WHERE audit_id=2041  </span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">ORDER BY host_id  </span></font></pre><pre style="margin-left:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">LIMIT 49 OFFSET 1421;</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Aportion of the output follows.  </span></font></pre><pre><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> host_id | host_datum_type_id | host_datum_source_id |     data     </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">---------+--------------------+----------------------+--------------</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  :</span></font></pre><pre style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  :</span></font></pre><pre style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></pre><pre style="margin-left:
.5in"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  963710 |                 58
|                  17| harrish</span></font></pre><pre style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  963711 |                 27 |                    3 | 1</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  963711 |                 28 |                    3 | 1</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">(49 rows)</span></font></pre><pre style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></pre><pre style="margin-left:
.5in"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> host_id | host_datum_type_id | host_datum_source_id |     data     </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">---------+--------------------+----------------------+--------------</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  963711 |                 28 |                    3 | 1</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  963711 |                 58 |                   17 | lmitchel</span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  963711 |                 39 |                    3 | us.aegon.com </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">:</span></font></pre><pre style="margin-left:
.5in"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">: </span></font></pre><pre
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">(49 rows)</span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Noticethat host_id = 963711 and host_datum_type_id = 28 is repeated twice. 
Sincethe offset is not overlapping, how can this happen?  Any ideas on how to fix this?</span></font></pre><pre><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">Thanks,</span></font></pre><pre><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial"> </span></font></pre><pre><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Barbara</span></font></pre><pre><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font></pre><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></div>