[MAILBOX-228] StoreMessageResultIterator returns only a part of messages with JPA backend on some databases

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.5
• Fix Version/s: 0.6
• Component/s: jpa
• Environment: James3 which acquired from SVN trunk on Feburuary 2015; PostgreSQL 9.2.6; Thunderbird 31.6.0
• Assignee: Eric Charles
• Reporter: Kohei Nozaki
• Created: 27/Apr/15 10:30
• Updated: 06/May/15 11:12
• Resolved: 06/May/15 11:12

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.