Why does MySQL LEFT JOIN not return all rows unless there is WHERE clause - phpMyAdmin issue

Question

Consider the following:

eventTypes table has 163 rows.

events has 43,000 rows.

SELECT events.eventTypeID, eventTypes.eventTypeName 
FROM events 
LEFT JOIN eventTypes ON events.eventTypeID = eventTypes.eventTypeID

This returns 163 rows. However, if I add "WHERE events.eventID >= 0"

SELECT events.eventTypeID, eventTypes.eventTypeName 
FROM events 
LEFT JOIN eventTypes ON events.eventTypeID = eventTypes.eventTypeID 
WHERE events.eventID >= 0

I get all 43,000 rows. I would expect the lack of a WHERE clause would give me everything. Am I thinking about this wrong?

Update: I just tried this on another server and same result. My exact query copied and pasted is:

SELECT events.eventTypeID, eventTypes.eventTypeName FROM events LEFT JOIN eventTypes ON events.eventTypeID = eventTypes.eventTypeID

This only returns the first 163 records. MySQL Versions are 5.5.29 and 5.1.61. I looked in the bug list and found nothing.

Update #2: EXPLAIN gives the same output with either query (i.e. with or without WHERE 1=1)

mysql> EXPLAIN(SELECT events.eventTypeID, eventTypes.eventTypeName FROM events LEFT JOIN eventTypes ON events.eventTypeID = eventTypes.eventTypeID);
+----+-------------+------------+--------+---------------+-------------+---------+-------------------------------+-------+-------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref                           | rows  | Extra       |
+----+-------------+------------+--------+---------------+-------------+---------+-------------------------------+-------+-------------+
|  1 | SIMPLE      | events     | index  | NULL          | eventTypeID | 4       | NULL                          | 37748 | Using index |
|  1 | SIMPLE      | eventTypes | eq_ref | PRIMARY       | PRIMARY     | 4       | casefriend.events.eventTypeID |     1 |             |
+----+-------------+------------+--------+---------------+-------------+---------+-------------------------------+-------+-------------+

Update#3 Testing on a 3rd system produces results I expect though I have no idea why. The 3rd system is another CentOS6 running MySQL 5.1.69. I imported the exact dumps from my development system that I imported into the 2nd test system that did not produce the correct results.

Update#4 Found the issue. This is NOT a MySQL issue. This is a phpMyAdmin issue. When testing on the command line with mysql client I get the correct results on all systems.

Answer 1

I tried this at SqlFiddle: http://sqlfiddle.com/#!2/c9908b/1

create table event (id int,type_id int);
create table type (type_id int, type_name varchar(30));

insert into type values(1, 'type 1');
insert into type values(2, 'type 2');
insert into type values(3, 'type 3');
insert into type values(4, 'type 4');
insert into type values(5, 'type 5');

insert into event values( 1,1);
insert into event values( 2,1);
insert into event values( 3,1);
insert into event values( 4,1);
insert into event values( 5,2);
insert into event values( 6,2);
insert into event values( 7,2);
insert into event values( 8,2);
insert into event values( 9,3);
insert into event values(10,3);
insert into event values(11,3);
insert into event values(12,3);
insert into event values(13,4);
insert into event values(14,4);
insert into event values(15,4);
insert into event values(16,4);
insert into event values(17,5);
insert into event values(18,5);
insert into event values(19,5);
insert into event values(20,5);

select event.id, type.type_name from event left join type
on event.type_id=type.type_id

I get 20 rows back as expected