title: Presto self join matching with a date column
body: <p>I'm trying to figure out why presto returns the following results. </p>
  
  <p>I have the following table of 4 dates: </p>
  
  <pre><code>pickup_week (date)
  '2019-10-28'
  '2019-11-04'
  '2019-11-06'
  '2019-12-16'
  </code></pre>
  
  <p>When I try this simple self join:</p>
  
  <pre><code>select 
  
  w1.pickup_week as pu_w1,
  w2.pickup_week as pu_w2
  
  from week_user_list w1
  join week_user_list w2 on w1.pickup_week = w2.pickup_week + interval '7' day 
  
  order by w1.pickup_week
  
  </code></pre>
  
  <p>I get the following results:</p>
  
  <pre><code>w1.pickup_week   w2.pickup_week
  2019-11-04       2019-10-28 
  2019-11-11       2019-11-04 
  
  </code></pre>
  
  <p>As you can see the join condition stated:</p>
  
  <pre><code>w1.pickup_week = w2.pickup_week + interval '7' day
  
  </code></pre>
  
  <p>But what I see in the return table is the opposite, w2.pickup_week is the former week and not the later as stated in the condition. </p>
  
  <p>In this case, my desired output would be:</p>
  
  <pre><code>w1.pickup_week   w2.pickup_week
  2019-10-28       2019-11-04       
  2019-11-04       2019-11-11        
  
  </code></pre>
  
  <p>I understand that reverting the condition to </p>
  
  <pre><code>w1.pickup_week = w2.pickup_week - interval '7' day
  </code></pre>
  
  <p>would provide the desired output, but I'm confused as to why and how come is that presto is returning these types of results, the condition stating seems un-intuitive to me. </p>
  
  <p>What is happening here and what would be the correct way to think about this?</p>
  
  <p>Thanks!</p>