MySQL Explains Anomaly

Question

Consider the following query:

select FEE_NUMBER
from CARRIER_FEE CF
left outer join CONTYPE_FEE_LIST cfl on CF.CAR_FEE_ID=cfl.CAR_FEE_ID and cfl.CONT_TYPE_ID=3
where CF.SEQ_NO = (
    select max(CF2.SEQ_NO) from CARRIER_FEE CF2 
    where CF2.FEE_NUMBER=CF.FEE_NUMBER 
    and CF2.COMPANY_ID=CF.COMPANY_ID 
    group by CF2.FEE_NUMBER) 
group by CF.CAR_FEE_ID 

On my laptop, this does not return any results. Using the exact same (dumped) database on my servers, it returns the results.

If I run EXPLAIN on my laptop, I get this

| id | select_type        | table | type  | possible_keys                               | key                   | key_len | ref                    | rows | Extra                                        |
+----+--------------------+-------+-------+---------------------------------------------+-----------------------+---------+------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | CF    | index | NULL                                        | PRIMARY               | 8       | NULL                   |  132 | Using where                                  | 
|  1 | PRIMARY            | cfl   | ref   | FK_CONTYPE_FEE_LIST_1,FK_CONTYPE_FEE_LIST_2 | FK_CONTYPE_FEE_LIST_1 | 8       | odysseyB.CF.CAR_FEE_ID |    6 |                                              | 
|  2 | DEPENDENT SUBQUERY | CF2   | ref   | FK_SURCHARGE_1                              | FK_SURCHARGE_1        | 8       | func                   |   66 | Using where; Using temporary; Using filesort | 

While on all my other servers it gives this (note the difference in the ref column)

| id | select_type        | table | type  | possible_keys                               | key                   | key_len | ref                    | rows | Extra                                        |
+----+--------------------+-------+-------+---------------------------------------------+-----------------------+---------+------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | CF    | index | NULL                                        | PRIMARY               | 8       | NULL                   |  132 | Using where                                  | 
|  1 | PRIMARY            | cfl   | ref   | FK_CONTYPE_FEE_LIST_1,FK_CONTYPE_FEE_LIST_2 | FK_CONTYPE_FEE_LIST_1 | 8       | odysseyB.CF.CAR_FEE_ID |    6 |                                              | 
|  2 | DEPENDENT SUBQUERY | CF2   | ref   | FK_SURCHARGE_1                              | FK_SURCHARGE_1        | 8       | odysseyB.CF.COMPANY_ID |   66 | Using where; Using temporary; Using filesort | 

If I remove the join, subquery, or last group, I get the expected results.

I assume this is a configuration issue, however this is not the one I saw before. Does anyone know what might cause this?

My laptop is running OSX 10.6 with MySQL 5.0.41. Another laptop running OSX 10.5.7 and MySQL 5.0.37 works just fine, as do Linux servers running MySQL 5.0.27.

- , ref = func, - ref = odysseyB.CF.COMPANY_ID?

.

Answer 1

:

mysql> SHOW CREATE TABLE CARRIER_FEE CF;

, .

, OS X 10.6 ? , , 10.5.x.

, . MySQL 5.4 10.6.

http://forums.mysql.com/read.php?10,278942,278942#msg-278942

Answer 2

, . , , EXPLAIN, . , .

, SQL- . , , .

, , " " :

select FEE_NUMBER
from CARRIER_FEE CF
left outer join CARRIER_FEE CF2
  on CF.FEE_NUMBER = CF2.FEE_NUMBER and CF.COMPANY_ID = CF.COMPANY_ID 
     and CF.SEQ_NO < cf2.SEQ_NO
left outer join CONTYPE_FEE_LIST cfl 
  on CF.CAR_FEE_ID=cfl.CAR_FEE_ID and cfl.CONT_TYPE_ID=3
where CF2.SEQ_NO IS NULL 
group by CF.CAR_FEE_ID;

, , . , , .