Hi. I was on my way to backport commit 210485dd5592bf23ac9335a2d3f5711e62753605
to my local 3.0.2 branch but
faced and inconvenience between old and new SQL query results:
Mailbox contents:
mysql> select * from dbmail_messages m where m.mailbox_idnr = 109 and status <
2;
+--------------+--------------+----------------+-----------+---------------+--------------+--------------+-------------+------------+----------------------------------+--------+
| message_idnr | mailbox_idnr | physmessage_id | seen_flag | answered_flag |
deleted_flag | flagged_flag | recent_flag | draft_flag | unique_id
| status |
+--------------+--------------+----------------+-----------+---------------+--------------+--------------+-------------+------------+----------------------------------+--------+
| 3207880 | 109 | 1231385 | 0 | 0 | 0
| 0 | 0 | 0 |
a89c585c62c43da903184d5687ae6e88 | 0 |
| 5753917 | 109 | 2200466 | 0 | 0 | 0
| 0 | 0 | 0 |
cbca1bbc680d212ef560881a9c48f0e0 | 0 |
| 5753933 | 109 | 2200470 | 1 | 0 | 0
| 0 | 1 | 0 |
2946d44c63735bd63c1d0fd05ad7ef82 | 0 |
| 5753945 | 109 | 2200476 | 1 | 0 | 0
| 0 | 1 | 0 |
1b99495471242f987e72613822836ad8 | 0 |
| 5753968 | 109 | 2200479 | 0 | 0 | 0
| 0 | 1 | 0 |
c47d5c483a0e16181dc2fabfbe5e27a6 | 0 |
| 9127808 | 109 | 3615822 | 0 | 0 | 0
| 0 | 1 | 0 |
67c2758788ab44d91a6ed431c786dc4e | 0 |
| 9127809 | 109 | 3615823 | 0 | 0 | 0
| 0 | 1 | 0 |
0c8f7b0b65d18df3705ef1a132a68fc6 | 0 |
| 9129655 | 109 | 3617138 | 0 | 0 | 0
| 0 | 1 | 0 |
e87aff5fe5cad35f55b366ca92c21e50 | 0 |
+--------------+--------------+----------------+-----------+---------------+--------------+--------------+-------------+------------+----------------------------------+--------+
Old query:
mysql> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=109 AND (status
< 2) UNION SELECT 1,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=109
AND (status < 2) AND seen_flag=1 UNION SELECT 2,COUNT(*) FROM dbmail_messages
WHERE mailbox_idnr=109 AND (status < 2) AND recent_flag=1;
+---+----------+
| 0 | COUNT(*) |
+---+----------+
| 0 | 8 | <-- exists
| 1 | 2 | // unseen = exists - this = 8 - 2 = 6
| 2 | 6 | <-- recent
+---+----------+
New query:
mysql> SELECT CASE WHEN seen_flag+recent_flag = 0 THEN 0 WHEN seen_flag = 1
THEN 1 WHEN recent_flag = 1 THEN 2 END AS flag, COUNT(*) FROM dbmail_messages
WHERE mailbox_idnr=109 AND (status < 2) GROUP BY seen_flag, recent_flag;
+------+----------+
| flag | COUNT(*) |
+------+----------+
| 0 | 2 | // exists = 2 + 4 + 2 = 8
| 2 | 4 | <-- recent
| 1 | 2 | // unseen = exists - this = 8 - 2 = 6
+------+----------+
As you can see recent message number differs. What is the correct number of
recent messages?
Should they be counted according to recent_flag value only?
--
Best regards,
Pavlo Lavrenenko,
PortaOne, Inc., Junior Software Developer
Tel: +1-866-SIP VOIP (+1 866 747 8647) ext. 7624
PortaOne - VoIP Solutions Company
Visit our Website: http://www.portaone.com
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
