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
