title: Two queries returning different results when they should be equivalent?
body: <p>Our dataset is fundamentally joining a set of dates (weeks from the current week into the past) to a set of sections based on whether those sections started on or before and ended on or after that week. While originally this query gave us the results we expected, this week it began providing us incorrect results. After a bunch of tinkering, we discovered that if we changed the query to a <code>LEFT JOIN</code> and then filtered the query using a <code>WHERE</code> clause, it gave us correct results again.</p>
  
  <p>What's the difference? Why does one work and the other doesn't? (<strong>Bonus points:</strong> why did the original query work for weeks before suddenly experiencing this error?) Performing the same inner join on Redshift delivers correct results, so it seems to be a Snowflake nuance that we don't understand.</p>
  
  <p><strong>Original query:</strong></p>
  
  <pre><code>WITH week_list AS
  (
      SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value
  
      UNION ALL
  
      SELECT DATEADD(week, 1, week_value)
      FROM week_list
      WHERE DATEADD(week, 1, week_value) &lt; CURRENT_DATE()
  ),
  active_sections_per_week AS
  (
      SELECT 
          wl.week_value, s.id section_id
      FROM week_list wl
      JOIN schema.sections s ON wl.week_value &gt;= DATE_TRUNC(week, s.starts_at)
                             AND wl.week_value &lt;= DATE_TRUNC(week, s.ends_at)
  )
  SELECT 
      aspw.week_value,
      COUNT(DISTINCT aspw.section_id) count_sections
  FROM 
      active_sections_per_week aspw
  GROUP BY 1
  ORDER BY 1 DESC
  </code></pre>
  
  <p><strong>Results:</strong> One row, dated 2019-12-30 (4 weeks ago). No data for the past three weeks.</p>
  
  <p>Note: If you adjust the <code>DATEADD</code> in the first CTE, whatever is the first date returned will always seem to join successfully. This behavior started only within the last week--previously, this query provided the expected number of rows (in other words, the number of weeks specified in that first <code>DATEADD</code>).</p>
  
  <p><strong>"Fixed" query:</strong></p>
  
  <pre><code>WITH week_list AS
  (
      SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value
  
      UNION ALL
  
      SELECT DATEADD(week, 1, week_value)
      FROM week_list
      WHERE DATEADD(week, 1, week_value) &lt; CURRENT_DATE()
  ),
  active_sections_per_week AS
  (
      SELECT wl.week_value, s.id section_id
      FROM week_list wl
      LEFT JOIN schema.sections s ON wl.week_value &gt;= DATE_TRUNC(week, s.starts_at)
                                  AND wl.week_value &lt;= DATE_TRUNC(week, s.ends_at)
      WHERE s.id IS NOT NULL
  )
  SELECT aspw.week_value, COUNT(DISTINCT aspw.section_id) count_sections
  FROM active_sections_per_week aspw
  GROUP BY 1
  ORDER BY 1 DESC
  </code></pre>
  
  <p><strong>Results:</strong> returns four rows, weeks dated 2019-12-30 to 2020-01-20, with appropriate section counts.</p>