Help ... Unexpected results when using limit/offset with select statement..DB corruption? - Mailing list pgsql-sql
| From | Barbara Cosentino |
|---|---|
| Subject | Help ... Unexpected results when using limit/offset with select statement..DB corruption? |
| Date | |
| Msg-id | 8A72E69E1F79004B82F76F228B3F29230637A623@corp-mail01.ncircle.com Whole thread Raw |
| Responses |
Re: Help ... Unexpected results when using limit/offset with
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
|
| 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>