Hi! It looks like I found a strange behaviour of the MySQL (3.23.36 on
windows and linux). Probably it's due to my own mistake but I really
can't catch what's up.
Look:
I have two tables linked w/ the 'one-to-many' relation. They look like
follows:
mysql> describe MessageType;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| Id | int(11) | | PRI | NULL | auto_increment |
| Owner | int(11) | | | 0 | |
| Name | varchar(30) | | | | |
....
+--------------+--------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)
mysql> describe Message;
+------------+------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+------------+------------+------+-----+---------------------+----------------+
| Id | int(11) | | PRI | NULL |
auto_increment |
| Type | int(11) | | | 0 |
|
...
+------------+------------+------+-----+---------------------+----------------+
4 rows in set (0.01 sec)
So each message has a type, each message type has an owner (described
elsewhere). Now I try to get a list w/ all message types for a
particular owner w/ currently selected MessageType 'highlighted' for
given owner and message (Message 8 does have type 3 and this type 3 has
owner 3, believe me :-)):
mysql> SELECT T.Id, T.Owner, M.Id, M.Type FROM MessageType T LEFT JOIN Message M on
T.Id = M.Type and M.Id=8 where T.Owner=3;
+----+-------+------+------+
| id | owner | Id | Type |
+----+-------+------+------+
| 1 | 3 | NULL | NULL |
| 3 | 3 | NULL | NULL |
+----+-------+------+------+
2 rows in set (0.00 sec)
Quite strange, right? But if I narrow the filter I get reasonable result:
mysql> SELECT T.Id, T.Owner, M.Id, M.Type FROM MessageType T LEFT JOIN Message M on
T.Id = M.Type and M.Id=8 where T.Owner=3 _*AND T.Id = 3*;_
+----+-------+----+------+
| Id | Owner | Id | Type |
+----+-------+----+------+
| 3 | 3 | 8 | 3 |
+----+-------+----+------+
1 row in set (0.01 sec)
That's it. I can't explain such behaviour.
Yours respectfully,
Alexey Morozov.
P.S. When replying please CC to me directly 'cause I'm not yet a member
of the list. Thank you.
