Generate range of dates using CTE Oracle

Question

I want to generate a range of days between two different dates using recursive WITH clause in Oracle.

WITH CTE_Dates (cte_date) AS
  ( SELECT CAST(TO_DATE('10-02-2017', 'DD-MM-YYYY') AS DATE) cte_date FROM dual
  UNION ALL
  SELECT CAST( (cte_date + 1) AS DATE) cte_date
  FROM CTE_Dates
  WHERE TRUNC(cte_date) + 1 <= TO_DATE('20-02-2017', 'DD-MM-YYYY')
  )
SELECT * FROM CTE_Dates

The returned results are completely other than expected:

10-02-2017
09-02-2017
08-02-2017
07-02-2017
06-02-2017
... (unlimited)

The expected results:

10-02-2017
11-02-2017
...
19-02-2017
20-02-2017

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

Edit: As I understood, this is a known Bug in Oracle, the bug exists through Oracle 11.2.0.2 and it was fixed in 11.2.0.3.

Altarnative solution:

SELECT TRUNC (TO_DATE('10-02-2017', 'DD-MM-YYYY') + ROWNUM -1) dt
  FROM DUAL
 CONNECT BY ROWNUM  <= (TO_DATE('20-02-2017', 'DD-MM-YYYY') - (TO_DATE('10-02-2017', 'DD-MM-YYYY')))

Answer 1

This was a known bug in recursive CTE's in Oracle 11 (specifically with regard to date arithmetic). Fixed in Oracle 12. Exactly that behavior: whether you add or subtract in your code, the engine always subtracts, it never adds.

EDIT: Actually, as Alex Poole pointed out in a Comment to the original post, the bug exists through Oracle 11.2.0.2 and it was fixed in 11.2.0.3. End edit

Alas I am not a paying customer, so I can't quote chapter and verse, but with a little bit of Googling you will find links to this (including on OTN where I was involved in a few threads discussing this and other bugs in recursive CTEs - some were fixed, some are still bugs in Oracle 12.1).

Added - here is one of those discussions: https://community.oracle.com/thread/3974408

Answer 2

Unless you really need to use the WITH clause there is another solution to get the expected result by using the CONNECT BY clause.

SELECT TO_DATE('10-02-2017', 'DD-MM-YYYY') AS date_range
  FROM dual
UNION ALL
SELECT TO_DATE('10-02-2017', 'DD-MM-YYYY') + LEVEL  
  FROM dual 
  CONNECT BY LEVEL <= (TO_DATE('20-02-2017', 'DD-MM-YYYY') - TO_DATE('10-02-2017', 'DD-MM-YYYY'));