Query: 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?