James Mailbox
  1. James Mailbox
  2. MAILBOX-228

StoreMessageResultIterator returns only a part of messages with JPA backend on some databases

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 0.5
    • Fix Version/s: 0.6
    • Component/s: jpa
    • Labels:
      None
    • Environment:
      James3 which acquired from SVN trunk on Feburuary 2015
      PostgreSQL 9.2.6
      Thunderbird 31.6.0

      Description

      Consider you have following dataset.

      Query:

      SELECT t0.mailbox_id, t0.mail_uid, t0.mail_is_answered, t0.mail_body_start_octet, t0.mail_content_octets_count, t0.mail_is_deleted, t0.mail_is_draft, t0.mail_is_flagged, t0.mail_date, t0.mail_mime_type, t0.mail_modseq, t0.mail_is_recent, t0.mail_is_seen, t0.mail_mime_subtype, t0.mail_textual_line_count FROM public.james_mail t0 WHERE (t0.mailbox_id = 1 AND t0.mail_uid >= 1 AND t0.mail_uid <= 18) LIMIT 200
      

      Result against the preceding query:

      mailbox_id	mail_uid	mail_is_answered	mail_body_start_octet	mail_content_octets_count	mail_is_deleted	mail_is_draft	mail_is_flagged	mail_date	mail_mime_type	mail_modseq	mail_is_recent	mail_is_seen	mail_mime_subtype	mail_textual_line_count
      1	1	false	1450	1464	false	false	false	"2015-04-25 08:28:40.920000"	text	4	false	true	plain	1
      1	2	false	3536	4237	false	false	false	"2015-04-25 09:56:13.350000"	text	8	false	true	plain	23
      1	3	false	4574	9174	false	false	false	"2015-04-25 17:26:48.450000"	multipart	12	false	true	alternative	NULL
      1	4	false	2901	3646	false	false	false	"2015-04-26 06:28:19.710000"	multipart	16	false	true	alternative	NULL
      1	6	false	1603	2645	false	false	false	"2015-04-26 12:06:53.350000"	text	22	false	true	plain	31
      1	5	false	1603	2264	false	false	false	"2015-04-26 12:06:53.350000"	text	21	false	true	plain	22
      1	17	false	1612	2509	false	false	false	"2015-04-27 03:52:02.870000"	text	64	false	true	plain	21
      1	18	false	1605	2200	false	false	false	"2015-04-27 04:14:57.180000"	text	62	false	true	plain	20
      1	7	false	1646	2572	false	false	false	"2015-04-26 13:52:56.720000"	text	41	false	true	plain	28
      1	8	false	1645	2958	false	false	false	"2015-04-26 13:58:01.320000"	text	42	false	true	plain	34
      1	9	false	1645	3093	false	false	false	"2015-04-26 14:07:01.740000"	text	43	false	true	plain	36
      1	10	false	1603	2428	false	false	false	"2015-04-26 14:25:03.150000"	text	44	false	true	plain	27
      1	11	false	1603	2483	false	false	false	"2015-04-26 14:25:51.300000"	text	45	false	true	plain	31
      1	12	false	1603	2428	false	false	false	"2015-04-26 14:31:57.130000"	text	46	false	true	plain	27
      1	13	false	1603	2459	false	false	false	"2015-04-26 14:32:51.110000"	text	47	false	true	plain	29
      1	14	false	1604	2471	false	false	false	"2015-04-26 14:49:51.700000"	text	52	false	true	plain	29
      1	15	false	1604	2490	false	false	false	"2015-04-26 14:49:51.730000"	text	53	false	true	plain	31
      1	16	false	1646	3082	false	false	false	"2015-04-26 18:24:56.440000"	text	57	false	true	plain	38
      

      Note that the dataset doesn't ordered by mail_uid. with that dataset, the IMAP server returns following response:

      3 uid fetch 1:* (FLAGS)
      * 1 FETCH (FLAGS (\Seen NonJunk) UID 1)
      * 2 FETCH (FLAGS (\Seen NonJunk) UID 2)
      * 3 FETCH (FLAGS (\Seen NonJunk) UID 3)
      * 4 FETCH (FLAGS (\Seen NonJunk) UID 4)
      * 5 FETCH (FLAGS (\Seen NonJunk) UID 6)
      * 6 FETCH (FLAGS (\Seen NonJunk) UID 5)
      * 7 FETCH (FLAGS (\Seen) UID 17)
      * 8 FETCH (FLAGS (\Seen NonJunk) UID 18)
      3 OK FETCH completed.
      

      I believe the server should return all of dataset, but records that after the record which has mail_uid=18 were skipped.

        Activity

        Hide
        Kohei Nozaki added a comment -

        The problem is that StoreMessageResultIterator assumes underlying dataset is always ordered by the uid, while RDBMS doesn't guarantee it (at least with PostgreSQL).

        This patch adds ORDER BY message.uid ASC to all of JPQLs that for retrieving Message entity.

        Show
        Kohei Nozaki added a comment - The problem is that StoreMessageResultIterator assumes underlying dataset is always ordered by the uid, while RDBMS doesn't guarantee it (at least with PostgreSQL). This patch adds ORDER BY message.uid ASC to all of JPQLs that for retrieving Message entity.
        Hide
        Eric Charles added a comment -

        Patch committed, thx Kohei Nozaki.

        Show
        Eric Charles added a comment - Patch committed, thx Kohei Nozaki.

          People

          • Assignee:
            Eric Charles
            Reporter:
            Kohei Nozaki
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development