title: Sorted result differs with inner and left joins in mysql
body: <p>I just found this irregularitry in a query i made, why does the result differ when using inner and left joins? for one, the result is ordered both in the temporary hit table and in the final query?</p>
  
  <p>I made a little example to show the problem:</p>
  
  <pre><code># cleanup
  drop temporary table if exists ids;
  drop temporary table if exists arts;
  
  # create temporary tables
  create temporary table arts ( id int, title varchar(100), posted datetime );
  create temporary table ids ( id int, artid int );
  
  # insert dummy articles
  insert into arts ( id, title, posted ) VALUES ( 1, 'a', '2010-04-01' );
  insert into arts ( id, title, posted ) VALUES ( 2, 'b', '2010-07-01' );
  insert into arts ( id, title, posted ) VALUES ( 3, 'c', '2010-06-01' );
  insert into arts ( id, title, posted ) VALUES ( 4, 'd', '2010-08-01' );
  
  # insert ordered list of hits
  insert into ids ( id, artid ) values ( 1, 4 );
  insert into ids ( id, artid ) values ( 2, 2 );
  insert into ids ( id, artid ) values ( 3, 3 );
  insert into ids ( id, artid ) values ( 4, 1 );
  
  # execute queries
  select i.artid, a.posted from ids i left join arts a on a.id = i.artid;
  select i.artid, a.posted from ids i inner join arts a on a.id = i.artid;
  
  # cleanup
  drop temporary table if exists arts;
  drop temporary table if exists ids;
  </code></pre>
  
  <p>the first query returns: </p>
  
  <pre><code>4,2,3,1 (as expected, ordered by posted-column descending)
  </code></pre>
  
  <p>the second one returns:</p>
  
  <pre><code>1,2,3,4 (ordered by pk?)
  </code></pre>