Help ... Unexpected results when using limit/offset with select statement..DB corruption?

From: "Barbara Cosentino" <bcosentino(at)ncircle(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Help ... Unexpected results when using limit/offset with select statement..DB corruption?
Date: 2007-01-18 23:51:12
Message-ID: 8A72E69E1F79004B82F76F228B3F29230637A623@corp-mail01.ncircle.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I got some weird results when processing select statements with limit
and offset. I think its some kind of database corruption but I was
wondering what other's think.


Background:

The table I'm having the issue with is described below. The thing to
note is the primary key

ice=# \d nc_host_datum
Table "public.nc_host_datum"
Column | Type | Modifiers
----------------------+---------+-----------
host_id | bigint | not null
host_datum_type_id | integer | not null
host_datum_source_id | integer | not null
data | text | not null
Indexes:
"nc_host_datum_pkey" PRIMARY KEY, btree (host_id,
host_datum_type_id)
Foreign-key constraints:
"foreign_key_01" FOREIGN KEY (host_id) REFERENCES nc_host(host_id)
ON UPDATE CASCADE ON DELETE CASCADE
"foreign_key_02" FOREIGN KEY (host_datum_type_id) REFERENCES
nc_host_datum_type(host_datum_type_id) ON UPDATE RESTRICT ON DELETE
RESTRICT
"foreign_key_03" FOREIGN KEY (host_datum_source_id) REFERENCES
nc_host_datum_source(host_datum_source_id) ON UPDATE RESTRICT ON DELETE
RESTRICT

Problem:

I perform the following select (notice that the group by is by the
primary key).

select host_id, host_datum_type_id, count(*)
from nc_host_datum where host_id in
( select host_id
from nc_host
where audit_id=2041)
group by host_id, host_datum_type_id;

and get the following result (There are many more rows but these are all
the rows for host_id = 963711):

host_id | host_datum_type_id | count
-------------+------------------------------+---------
963711 | 58 | 1
963711 | 54 | 1
963711 | 39 | 1
963711 | 28 | 1
963711 | 27 | 1


Notice that there are 5 rows for host_id 963711 and the
host_datum_type_id's are all unique

Then I perform the following selects

SELECT host_id, host_datum_type_id, host_datum_source_id, data
FROM nc_host_datum INNER JOIN nc_host USING (host_id)
WHERE audit_id=2041
ORDER BY host_id
LIMIT 49 OFFSET 1372;

And

SELECT host_id, host_datum_type_id, host_datum_source_id, data
FROM nc_host_datum INNER JOIN nc_host USING (host_id)
WHERE audit_id=2041
ORDER BY host_id
LIMIT 49 OFFSET 1421;

A portion of the output follows.

host_id | host_datum_type_id | host_datum_source_id | data
---------+--------------------+----------------------+--------------
:
:

963710 | 58 | 17| harrish
963711 | 27 | 3 | 1
963711 | 28 | 3 | 1
(49 rows)


host_id | host_datum_type_id | host_datum_source_id | data
---------+--------------------+----------------------+--------------
963711 | 28 | 3 | 1
963711 | 58 | 17 | lmitchel
963711 | 39 | 3 | us.aegon.com
:
:
(49 rows)

Notice that host_id = 963711 and host_datum_type_id = 28 is repeated
twice. Since the offset is not overlapping, how can this happen? Any
ideas on how to fix this?

Thanks,

Barbara

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2007-01-19 00:40:56 Re: Help ... Unexpected results when using limit/offset with
Previous Message John DeSoi 2007-01-18 13:47:19 Re: Some help with functions-syntax