> That's the yucky part, that is not needed.
>
> I just pushed a cleanup that should at least put us back to where we
> were before the headertable change in terms of correctness. There are
> very likely still some issues with search as Michael reported, but
> hopefully no new issues.
>
I had the sql like that on purpose. If you do not use left joins, then
when you try to sort messages based upon a header in which the message
doesn't have one, then the message will no longer be in the result set.
Thus my test of
A001 SORT (SUBJECT TO CC) UTF-8 ALL
confirms.
How is this supposed to return the correct result or any result?
SELECT message_idnr FROM dbmail_messages m LEFT JOIN dbmail_physmessage
p ON m.physmessage_id=p.id WHERE m.mailbox_idnr = 2030 AND m.status IN
(0,1) ORDER BY v.headervalue,v.sortfield,v.sortfield,message_id
While the below is ugly, it works.
mysql> explain extended SELECT message_idnr FROM dbmail_messages m LEFT
JOIN dbmail_physmessage p ON m.physmessage_id=p.id JOIN
dbmail_headername hnsubject ON hnsubject.headername=lower('subject')
LEFT JOIN dbmail_header hsubject ON
(m.physmessage_id=hsubject.physmessage_id AND
hsubject.headername_id=hnsubject.id) LEFT JOIN dbmail_headervalue
hvsubject ON hsubject.headervalue_id=hvsubject.id JOIN
dbmail_headername hnto ON hnto.headername=lower('to') LEFT JOIN
dbmail_header hto ON (m.physmessage_id=hto.physmessage_id AND
hto.headername_id=hnto.id) LEFT JOIN dbmail_headervalue hvto ON
hto.headervalue_id=hvto.id JOIN dbmail_headername hncc ON
hncc.headername=lower('cc') LEFT JOIN dbmail_header hcc ON
(m.physmessage_id=hcc.physmessage_id AND hcc.headername_id=hncc.id)
LEFT JOIN dbmail_headervalue hvcc ON hcc.headervalue_id=hvcc.id WHERE
m.mailbox_idnr = 2030 AND m.status IN (0,1) ORDER BY
hvsubject.headervalue,hvto.sortfield,hvcc.sortfield,message_idnr;
+----+-------------+-----------+--------+-----------------------------------------------------------------------+----------------+---------+--------------------------------------+------+----------------------------------------------+
| id | select_type | table | type |
possible_keys |
key | key_len | ref | rows |
Extra |
+----+-------------+-----------+--------+-----------------------------------------------------------------------+----------------+---------+--------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | hnsubject | const |
headername |
headername | 302 | const | 1 |
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | hnto | const |
headername |
headername | 302 | const | 1 |
Using index |
| 1 | SIMPLE | hncc | const |
headername |
headername | 302 | const | 1 |
Using index |
| 1 | SIMPLE | m | range |
mailbox_idnr_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 442 |
Using where |
| 1 | SIMPLE | hcc | ref |
PRIMARY,headername_id,physmessage_id,physmessage_id_2,headername_id_2 |
PRIMARY | 16 | dbmail_2_3_5.m.physmessage_id,const | 1 |
Using index |
| 1 | SIMPLE | hto | ref |
PRIMARY,headername_id,physmessage_id,physmessage_id_2,headername_id_2 |
PRIMARY | 16 | dbmail_2_3_5.m.physmessage_id,const | 1 |
Using index |
| 1 | SIMPLE | p | eq_ref |
PRIMARY |
PRIMARY | 8 | dbmail_2_3_5.m.physmessage_id | 1 |
Using index |
| 1 | SIMPLE | hsubject | ref |
PRIMARY,headername_id,physmessage_id,physmessage_id_2,headername_id_2 |
PRIMARY | 16 | dbmail_2_3_5.m.physmessage_id,const | 1 |
Using index |
| 1 | SIMPLE | hvsubject | eq_ref |
PRIMARY |
PRIMARY | 8 | dbmail_2_3_5.hsubject.headervalue_id | 1
| |
| 1 | SIMPLE | hvto | eq_ref |
PRIMARY |
PRIMARY | 8 | dbmail_2_3_5.hto.headervalue_id | 1
| |
| 1 | SIMPLE | hvcc | eq_ref |
PRIMARY |
PRIMARY | 8 | dbmail_2_3_5.hcc.headervalue_id | 1
| |
+----+-------------+-----------+--------+-----------------------------------------------------------------------+----------------+---------+--------------------------------------+------+----------------------------------------------+
11 rows in set, 1 warning (0.00 sec)
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
