title: Missing row from self inner join SQL query
body: <p>I am using PostgreSQL 8.3.8.</p>
  
  <p>I have a list of times boundaries (by date), in a time_boundaries table :</p>
  
  <pre><code>CREATE TABLE role_times_boundaries
  (
    role_date DATE,
    time_boundary TIME
  );
  
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-24'::date, '09:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-24'::date, '10:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '07:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '08:50:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '09:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '12:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '13:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '16:00:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '17:30:00'::time);
  INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '20:00:00'::time);
  </code></pre>
  
  <p>And so, I have this table content :    </p>
  
  <pre><code> role_date  | time_boundary 
  ------------+---------------
   2013-04-24 | 09:00:00
   2013-04-24 | 10:00:00
   2013-04-25 | 07:00:00
   2013-04-25 | 08:50:00
   2013-04-25 | 09:00:00
   2013-04-25 | 12:00:00
   2013-04-25 | 13:00:00
   2013-04-25 | 16:00:00
   2013-04-25 | 17:30:00
   2013-04-25 | 20:00:00
  </code></pre>
  
  <h2>The goal</h2>
  
  <p>I would like to build a "time slice list" table by doing a self inner join on the "role_times_boundaries" by taking each time_boundary as "start_time", and the next time_boundary (by order) for the same date.
  The goal is to have that result :</p>
  
  <pre><code> role_date  | start_time | end_time 
  ------------+------------+----------
   2013-04-24 | 09:00:00   | 10:00:00
   2013-04-25 | 07:00:00   | 08:50:00
   2013-04-25 | 08:50:00   | 09:00:00
   2013-04-25 | 09:00:00   | 12:00:00
   2013-04-25 | 12:00:00   | 13:00:00
   2013-04-25 | 13:00:00   | 16:00:00
   2013-04-25 | 16:00:00   | 17:30:00
   2013-04-25 | 17:30:00   | 20:00:00
  </code></pre>
  
  <h2>The tentative</h2>
  
  <p>I tried to get the wished result via this SQL query</p>
  
  <pre><code>SELECT role_times_boundaries.role_date,
         role_times_boundaries.time_boundary AS start_time,
         end_time_boundaries.time_boundary AS end_time
  FROM role_times_boundaries
  INNER JOIN (
               SELECT role_date,
                      time_boundary
               FROM role_times_boundaries
             ) AS end_time_boundaries ON (
                                         role_times_boundaries.role_date = end_time_boundaries.role_date
                                         AND end_time_boundaries.time_boundary = (
                                                                                    SELECT MIN(a_list_of_end_boundaries.time_boundary)
                                                                                    FROM role_times_boundaries AS a_list_of_end_boundaries
                                                                                    WHERE a_list_of_end_boundaries.time_boundary &gt; role_times_boundaries.time_boundary
                                                                                  )
                                       )
  </code></pre>
  
  <p>Here's the result :</p>
  
  <pre><code> role_date  | start_time | end_time 
  ------------+------------+----------
   2013-04-24 | 09:00:00   | 10:00:00
   2013-04-25 | 07:00:00   | 08:50:00
   2013-04-25 | 08:50:00   | 09:00:00
   2013-04-25 | 12:00:00   | 13:00:00
   2013-04-25 | 13:00:00   | 16:00:00
   2013-04-25 | 16:00:00   | 17:30:00
   2013-04-25 | 17:30:00   | 20:00:00
  </code></pre>
  
  <p>If you see well, the <strong>09:00:00 to 12:00:00</strong> time slice is missing !
  But I still don't understand why, and still don't find my error.</p>