mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.00 sec) mysql> select version(); +------------------+ | version() | +------------------+ | 5.0.96-community | +------------------+ 1 row in set (0.00 sec) ----------------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+------------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+------------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | -16:39:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | -330:13:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | -16:38:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 00:00:00 | +---------+---------------------+---------------------+------------+ 5 rows in set (0.00 sec) mysql> select version(); +------------------+ | version() | +------------------+ | 5.1.67-community | +------------------+ 1 row in set (0.00 sec) ------------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+------------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+------------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | -16:39:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | -330:13:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | -16:38:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 00:00:00 | +---------+---------------------+---------------------+------------+ 5 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.29 | +-----------+ 1 row in set (0.00 sec) -------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.00 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.6.11-debug | +--------------+ 1 row in set (0.00 sec) -------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.00 sec) mysql> select version(); +----------------+ | version() | +----------------+ | 5.6.9-rc-debug | +----------------+ 1 row in set (0.00 sec) ------------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.00 sec) mysql> select version(); +-----------------+ | version() | +-----------------+ | 5.7.1-m11-debug | +-----------------+ 1 row in set (0.00 sec) ----------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.00 sec) mysql> select version(); +--------------------------------------+ | version() | +--------------------------------------+ | 5.1.30-enterprise-gpl-advanced-debug | +--------------------------------------+ 1 row in set (0.00 sec) ----------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.02 sec) mysql> select version(); +--------------------------------------+ | version() | +--------------------------------------+ | 5.1.40-enterprise-gpl-advanced-debug | +--------------------------------------+ 1 row in set (0.00 sec) ---------------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+------------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+------------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | -16:39:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | -330:13:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | -16:38:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 00:00:00 | +---------+---------------------+---------------------+------------+ 5 rows in set (0.00 sec) mysql> select version(); +--------------------------------------+ | version() | +--------------------------------------+ | 5.1.50-enterprise-gpl-advanced-debug | +--------------------------------------+ 1 row in set (0.00 sec) -------------------------------- mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+----------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+----------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 31:45:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | 06:15:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | 05:01:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 06:22:00 | +---------+---------------------+---------------------+----------+ 5 rows in set (0.01 sec) mysql> select version(); +--------------------------------------+ | version() | +--------------------------------------+ | 5.1.45-enterprise-gpl-advanced-debug | +--------------------------------------+ 1 row in set (0.00 sec) ------------------------------------ mysql> /* unexpected result on MySQL 5.1, 5.5; correct on MySQL 5.0 */ mysql> select a.card_id,a.event_time,min(b.event_time),timediff(min(b.event_time),a.event_time) as d -> from test_entry_card_events a join test_entry_card_events b on a.card_id=b.card_id and a.event_type='entry' and b.event_type='exit' -> and b.event_time>a.event_time group by a.card_id,a.event_time order by a.card_id,a.event_time; +---------+---------------------+---------------------+------------+ | card_id | event_time | min(b.event_time) | d | +---------+---------------------+---------------------+------------+ | 4403 | 2012-12-04 12:10:00 | 2012-12-05 19:55:00 | 07:18:00 | | 4403 | 2012-12-05 12:37:00 | 2012-12-05 19:55:00 | -16:39:00 | | 4403 | 2012-12-06 12:34:00 | 2012-12-06 18:49:00 | -330:13:00 | | 4403 | 2012-12-20 13:02:00 | 2012-12-20 18:03:00 | -16:38:00 | | 4403 | 2012-12-21 10:41:00 | 2012-12-21 17:03:00 | 00:00:00 | +---------+---------------------+---------------------+------------+ 5 rows in set (0.00 sec) mysql> select version(); +--------------------------------------+ | version() | +--------------------------------------+ | 5.1.46-enterprise-gpl-advanced-debug | +--------------------------------------+ 1 row in set (0.00 sec)