rendered paste bodyQuery: No Group Clause:
select b.date_stamp, b.user_id, z.id as user_wage_id, z.effective_date as user_wage_effective_date
from user_date_total as a
LEFT JOIN user_date as b ON a.user_date_id = b.id
LEFT JOIN user_wage as z ON z.id = (select z.id
from user_wage as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.deleted = 0
order by z.effective_date desc limit 1)
where
b.user_id = '40'
AND b.pay_period_id = '1103'
AND b.date_stamp <= '2006-01-02'
AND a.status_id in (10,30)
AND ( a.deleted = 0 AND b.deleted = 0)
+----+--------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------+------+-----------------------------+
| 1 | PRIMARY | b | ref | PRIMARY,user_date_id,user_date_user_id_user_date_deleted,user_date_date_stamp,user_date_pay_period_id,user_date_user_id | user_date_user_id_user_date_deleted | 4 | const | 2 | Using where |
| 1 | PRIMARY | z | eq_ref | PRIMARY,user_wage_id | PRIMARY | 4 | func | 1 | |
| 1 | PRIMARY | a | ref | user_date_total_user_date_id | user_date_total_user_date_id | 4 | timetrex31.b.id | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | z | ref | user_wage_user_id_effective_date | user_wage_user_id_effective_date | 4 | timetrex31.b.user_id | 2 | Using where; Using filesort |
+----+--------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------+------+-----------------------------+
4 rows in set (0.00 sec)
+------------+---------+--------------+--------------------------+
| date_stamp | user_id | user_wage_id | user_wage_effective_date |
+------------+---------+--------------+--------------------------+
| 2006-01-01 | 40 | 156 | 2006-01-01 |
| 2006-01-01 | 40 | 156 | 2006-01-01 |
| 2006-01-01 | 40 | 156 | 2006-01-01 |
| 2006-01-01 | 40 | 156 | 2006-01-01 |
| 2006-01-01 | 40 | 156 | 2006-01-01 |
| 2006-01-01 | 40 | 156 | 2006-01-01 |
| 2006-01-02 | 40 | 156 | 2006-01-01 |
| 2006-01-02 | 40 | 156 | 2006-01-01 |
| 2006-01-02 | 40 | 156 | 2006-01-01 |
+------------+---------+--------------+--------------------------+
9 rows in set (0.00 sec)
Same Query: Simple Group Clause:
select b.date_stamp, b.user_id, z.id as user_wage_id, z.effective_date as user_wage_effective_date
from user_date_total as a
LEFT JOIN user_date as b ON a.user_date_id = b.id
LEFT JOIN user_wage as z ON z.id = (select z.id
from user_wage as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.deleted = 0
order by z.effective_date desc limit 1)
where
b.user_id = '40'
AND b.pay_period_id = '1103'
AND b.date_stamp <= '2006-01-02'
AND a.status_id in (10,30)
AND ( a.deleted = 0 AND b.deleted = 0)
group by b.date_stamp
+----+--------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+-----------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+-----------------+------+-----------------------------+
| 1 | PRIMARY | b | ref | PRIMARY,user_date_id,user_date_user_id_user_date_deleted,user_date_date_stamp,user_date_pay_period_id,user_date_user_id | user_date_user_id_user_date_deleted | 4 | const | 2 | Using where |
| 1 | PRIMARY | z | eq_ref | PRIMARY,user_wage_id | PRIMARY | 4 | func | 1 | |
| 1 | PRIMARY | a | ref | user_date_total_user_date_id | user_date_total_user_date_id | 4 | timetrex31.b.id | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | z | ref | user_wage_user_id_effective_date | user_wage_user_id_effective_date | 4 | func | 2 | Using where; Using filesort |
+----+--------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+-----------------+------+-----------------------------+
4 rows in set (0.00 sec)
+------------+---------+--------------+--------------------------+
| date_stamp | user_id | user_wage_id | user_wage_effective_date |
+------------+---------+--------------+--------------------------+
| 2006-01-01 | 40 | NULL | NULL |
| 2006-01-02 | 40 | 156 | 2006-01-01 |
+------------+---------+--------------+--------------------------+
2 rows in set (0.01 sec)
wtf?