bug.sql

SELECT id, name, punishments_count FROM "profiles"  ORDER BY punishments_count ASC LIMIT 2 OFFSET 0;
 id  |  name  | punishments_count
-----+--------+-------------------
 618 | 王靖茹 |                 0
 617 | 王詩銘 |                 0
(2 rows)


SELECT id, name, punishments_count FROM "profiles"  ORDER BY punishments_count ASC LIMIT 2 OFFSET 10;
 id  |  name  | punishments_count
-----+--------+-------------------
 604 | 王沛雷 |                 0
 617 | 王詩銘 |                 0
(2 rows)


SELECT id, name, punishments_count FROM "profiles"  ORDER BY punishments_count ASC LIMIT 2 OFFSET 100;
 id  |  name  | punishments_count
-----+--------+-------------------
 695 | 何菁莪 |                 0
 617 | 王詩銘 |                 0
(2 rows)


SELECT id, name, punishments_count FROM "profiles"  ORDER BY (punishments_count is null) ASC LIMIT 2 OFFSET 10;
 id  |  name  | punishments_count
-----+--------+-------------------
 604 | 王沛雷 |                 0
 617 | 王詩銘 |                 0
(2 rows)


SELECT id, name, punishments_count FROM "profiles"  ORDER BY punishments_count ASC, name ASC LIMIT 2 OFFSET 0;
  id  | name  | punishments_count
------+-------+-------------------
 3829 | 張 菁 |                 0
 1093 | 張 蘭 |                 0
(2 rows)


SELECT id, name, punishments_count FROM "profiles"  ORDER BY punishments_count ASC, name ASC LIMIT 2 OFFSET 10;
  id  | name | punishments_count
------+------+-------------------
 3661 | 周玉 |                 0
 2338 | 唐玥 |                 0
(2 rows)


\d+ profiles;
                                                             Table "public.profiles"
       Column       |            Type             |                       Modifiers                       | Storage  | Stats target | Description
--------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
 id                 | integer                     | not null default nextval('profiles_id_seq'::regclass) | plain    |              |
 name               | character varying(255)      |                                                       | extended |              |
 current            | character varying(255)      |                                                       | extended |              |
 avatar             | character varying(255)      |                                                       | extended |              |
 gender             | character varying(255)      |                                                       | extended |              |
 gender_source      | text                        |                                                       | extended |              |
 birth_year         | integer                     |                                                       | plain    |              |
 birth_year_source  | text                        |                                                       | extended |              |
 stage              | integer                     |                                                       | plain    |              |
 stage_source       | text                        |                                                       | extended |              |
 appointment        | character varying(255)      |                                                       | extended |              |
 appointment_source | text                        |                                                       | extended |              |
 memo               | text                        |                                                       | extended |              |
 created_at         | timestamp without time zone |                                                       | plain    |              |
 updated_at         | timestamp without time zone |                                                       | plain    |              |
 current_court      | character varying(255)      |                                                       | extended |              |
 is_active          | boolean                     |                                                       | plain    |              |
 is_hidden          | boolean                     |                                                       | plain    |              |
 punishments_count  | integer                     | default 0                                             | plain    |              |
Indexes:
    "profiles_pkey" PRIMARY KEY, btree (id)
    "index_profiles_on_current" btree (current)
    "index_profiles_on_current_court" btree (current_court)
    "index_profiles_on_is_active" btree (is_active)
    "index_profiles_on_is_hidden" btree (is_hidden)

資料庫為 PostgreSQL 加上 Postgis 。

經過檢查發現，問題出在 ORDER BY punishments_count ，只要有他，無論 OFFSET 為何，都會跳出特定的項目在裡面。
目前整個 table 的 punishments_count 均為 0 。