List:General Discussion« Previous MessageNext Message »
From:Michael Shulman Date:April 11 2003 10:50pm
Subject:Trouble with left joins
View as plain text  
I'm using both MySQL 4.0.12 and MySQL 4.0.2-alpha.

My problem is that, after a while, LEFT JOINs start giving me the wrong
answer. I have a repro case that involves running my server for quite a
while, but I haven't extracted out the minimal case for the server.

In any case, this is the query that's causing trouble:

SELECT DISTINCT p.name, p.description, p.server_name, p.host_name,
p.domain_name, cp.active, t.roles, c.name 
FROM peer p, community c, community_peer cp   
LEFT JOIN transport t ON (t.oid_sender = cp.oid)    
WHERE cp.oid_peer = p.oid 
AND cp.oid_community = c.oid 
AND c.name = 'zulu' order by 2

Early in a session, the t.roles column is filled in correctly with values.
After the server has been running for a while, t.roles returns all NULLs.
Restarting (but not reloading the data) causes the server to start working
again. 

Here's the result late in the session:
mysql> select p.name, p.host_name, cp.active, t.roles, c.name
    -> FROM peer p, community c, community_peer cp
    -> LEFT JOIN transport t ON (t.oid_sender = cp.oid)
    -> where cp.oid_peer = p.oid and cp.oid_community = c.oid and c.name =
'zulu'
    -> ;
+-----------------+----------------+--------+-------+------+
| name            | host_name      | active | roles | name |
+-----------------+----------------+--------+-------+------+
| zulu pub        | zulu           |      0 |  NULL | zulu |
| zulu_cm         | mshulman-gx110 |      1 |  NULL | zulu |
| David           | david          |      0 |  NULL | zulu |
| mshulman_laptop | mshulman       |      0 |  NULL | zulu |
+-----------------+----------------+--------+-------+------+
4 rows in set (0.00 sec)

Note that all of the "roles" are NULL.

Then, I stop the MySQL Service and restart it, and re-issue the query.

+-----------------+----------------+--------+-------+------+
| name            | host_name      | active | roles | name |
+-----------------+----------------+--------+-------+------+
| David           | david          |      0 |     1 | zulu |
| mshulman_laptop | mshulman       |      0 |  NULL | zulu |
| zulu pub        | zulu           |      0 |     1 | zulu |
| zulu_cm         | mshulman-gx110 |      1 |     2 | zulu |
+-----------------+----------------+--------+-------+------+
4 rows in set (0.05 sec) 

The EXPLAINS tell the whole story. Sorry about the formatting.
When the query gives me the wrong answer, EXPLAIN looks like this:

mysql> explain select p.name, p.host_name, cp.active, t.roles, c.name
    -> FROM peer p, community c, community_peer cp
    -> LEFT JOIN transport t ON (t.oid_sender = cp.oid)
    -> where cp.oid_peer = p.oid and cp.oid_community = c.oid and c.name =
'zulu';
+-------+--------+----------------------------+-----------------+---------+-
------------+------+-------------+
| table | type   | possible_keys              | key             | key_len |
ref         | rows | Extra       |
+-------+--------+----------------------------+-----------------+---------+-
------------+------+-------------+
| t     | system | sender_index               | NULL            |    NULL |
NULL        |    1 |             |
| c     | ALL    | PRIMARY                    | NULL            |    NULL |
NULL        |    1 | Using where |
| cp    | ref    | peer_index,community_index | community_index |      36 |
c.oid       |    1 |             |
| p     | eq_ref | PRIMARY                    | PRIMARY         |      36 |
cp.oid_peer |    1 |             |
+-------+--------+----------------------------+-----------------+---------+-
------------+------+-------------+
4 rows in set (0.00 sec)

Note that it thinks that "t" is a system (or const) table, and that it has
to do a table scan of "c".

Then, after restarting the service:
mysql> explain select p.name, p.host_name, cp.active, t.roles, c.name
    -> FROM peer p, community c, community_peer cp
    -> LEFT JOIN transport t ON (t.oid_sender = cp.oid)
    -> where cp.oid_peer = p.oid and cp.oid_community = c.oid and c.name =
'zulu';
+-------+--------+----------------------------+--------------+---------+----
---------+------+-------------+
| table | type   | possible_keys              | key          | key_len | ref
| rows | Extra       |
+-------+--------+----------------------------+--------------+---------+----
---------+------+-------------+
| c     | ALL    | PRIMARY                    | NULL         |    NULL |
NULL        |    1 | Using where |
| cp    | ALL    | peer_index,community_index | NULL         |    NULL |
NULL        |    4 | Using where |
| p     | eq_ref | PRIMARY                    | PRIMARY      |      36 |
cp.oid_peer |    1 |             |
| t     | ref    | sender_index               | sender_index |      36 |
cp.oid      |    1 |             |
+-------+--------+----------------------------+--------------+---------+----
---------+------+-------------+
4 rows in set (0.00 sec)

My questions:
1. I need to use a left join because a record might not be in the
"transport" table. Is there another way to write this query?
2. Are there known issues with LEFT JOINs? I see scattered references on
devshed. Would it be helpful to have a full repro case for this problem?

Thanks in advance!
-ms
 

Thread
Trouble with left joinsMichael Shulman12 Apr
  • RE: Trouble with left joinsMichael Shulman12 Apr
    • RE: Trouble with left joinsMichael Shulman12 Apr