Thread: BUG #10207: Simple select query returns duplicate row - Critical issue

The following bug has been logged on the website:

Bug reference:      10207
Logged by:          Vetri
Email address:      vetri_2020@yahoo.com
PostgreSQL version: 9.2.4
Operating system:   Windows 7
Description:

I'd encountered an issue in select query when using order by and limit.

When I sort by a column which have same values for some data,then the row
that appears in first page also appears in next page.

For example,

select * from table order by col2 limit 10;

| col1                                   | col2   |
+----------------------------------------+--------+
| {74414578-F65E-48D1-BB4A-8350A6688989} | test1  |
| {B2695B59-FE6D-4CA3-AB6C-CB773FF02A34} | test10 |
| {11239516-1A8B-4028-AA54-0E1F091F439E} | test10 |
| {CE68934B-72C7-4D5A-99F3-819A5293785F} | test13 |
| {BDAFF881-CA4A-4F0F-85AF-36B7C2200A9D} | test13 |
| {6C5EC468-25C9-49AC-8FFA-F27DB2DACDF3} | test13 |
| {B87C76A9-F450-47FC-8727-8C7F3657F885} | test13 |
| {811B5B81-4D75-4B6E-91E7-E791E56A223C} | test2  |
| {2A6C9270-ADC5-4059-A0A1-677D27E26AB9} | test3  |
| {A2F91E6F-7589-4239-BDF8-C90B11E35487} | test4  |
| {B435F056-59FE-46F3-9CE0-7C9019F38885} | test4  |
| {4EDAC233-BA9C-4058-80E0-374E02B36D06} | test5  |
| {A28B6BC7-BAED-4C7E-90E1-06A253B83D20} | test7  |
| {B8201EF9-DCEC-407E-A8E2-775CB246A0BE} | test8  |
| {1D0A1EB4-9DCD-4CAE-B66C-13EE853E41D7} | test9  |
+----------------------------------------+--------+

returns 10 rows correctly where 5th and 6th row contains same data for
col2.

Now, when I execute this query ,

select * from testsort order by col2 limit 5
;

+----------------------------------------+--------+
| col1                                   | col2   |
+----------------------------------------+--------+
| {74414578-F65E-48D1-BB4A-8350A6688989} | test1  |
| {B2695B59-FE6D-4CA3-AB6C-CB773FF02A34} | test10 |
| {11239516-1A8B-4028-AA54-0E1F091F439E} | test10 |
| {6C5EC468-25C9-49AC-8FFA-F27DB2DACDF3} | test13 |
| {BDAFF881-CA4A-4F0F-85AF-36B7C2200A9D} | test13 |
+----------------------------------------+--------+

And when I execute the below query, the issue occurs.

+----------------------------------------+--------+
| col1                                   | col2   |
+----------------------------------------+--------+
| {6C5EC468-25C9-49AC-8FFA-F27DB2DACDF3} | test13 |
| {B87C76A9-F450-47FC-8727-8C7F3657F885} | test13 |
| {811B5B81-4D75-4B6E-91E7-E791E56A223C} | test2  |
| {2A6C9270-ADC5-4059-A0A1-677D27E26AB9} | test3  |
| {A2F91E6F-7589-4239-BDF8-C90B11E35487} | test4  |
+----------------------------------------+--------+

Look at the results.This row '{6C5EC468-25C9-49AC-8FFA-F27DB2DACDF3}' occurs
two times..One in first page and also in next page.

This key '{CE68934B-72C7-4D5A-99F3-819A5293785F}' is missing in results.

Appreciate your prompt response !

Col datatype - citext

On 5/3/14, 1:04 PM, vetri_2020@yahoo.com wrote:
> I'd encountered an issue in select query when using order by and limit.
>
> When I sort by a column which have same values for some data,then the row
> that appears in first page also appears in next page.

This is not a bug.  The sort order of identical values is undefined
unless disambiguated by adding more sort expressions.

> And when I execute the below query, the issue occurs.

You didn't show the query you used here, but I'm assuming it had OFFSET
5 or something similar.  And yes, that's still undefined behaviour, and
not a bug.



Regards,
Marko Tiikkaja

Marko Tiikkaja <marko@joh.to> writes:
> On 5/3/14, 1:04 PM, vetri_2020@yahoo.com wrote:
>> I'd encountered an issue in select query when using order by and limit.
>>
>> When I sort by a column which have same values for some data,then the row
>> that appears in first page also appears in next page.

> This is not a bug.  The sort order of identical values is undefined
> unless disambiguated by adding more sort expressions.

Somebody else asked this identical question just a couple days ago:

http://www.postgresql.org/message-id/flat/20140428192320.2661.1832@wrigleys.postgresql.org

It's not random in the sense of having any indeterminate behavior,
but the output ordering of equal-keyed elements *can* change depending
on the LIMIT value.

            regards, tom lane

The fact that two executions of a query can return the same data is not
anything like what your subject describes.  Now, if a single execution
returned a duplicate row (based on a PK) then what you said would be true.

If you need definite order you need to sort on a primary key - or something
equivalent.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-10207-Simple-select-query-returns-duplicate-row-Critical-issue-tp5802310p5802319.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.