From: | vetri_2020(at)yahoo(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #10207: Simple select query returns duplicate row - Critical issue |
Date: | 2014-05-03 11:04:01 |
Message-ID: | 20140503110401.1394.82717@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 10207
Logged by: Vetri
Email address: vetri_2020(at)yahoo(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Tiikkaja | 2014-05-03 17:22:28 | Re: BUG #10207: Simple select query returns duplicate row - Critical issue |
Previous Message | Daniel Baston | 2014-05-02 20:08:39 | Re: BUG #10201: Invalid input accepted with IN expression |