ORACLE（11.2.0.1.0）-具有日期表达式的递归CTE

象山

以下问题的正确答案：
& A: z  |: y/ A) z8 q如果我没有记错的话，那是在11.2.0.3或更高版本中修复的错误。（无论如何不再支持11.2.0.1.11.2.0.4是唯一仍受支持的11.2版本）-@a_horse_with_no_name
错误号为11840579，已在11.2.0.3和12.1.0.1中进行了修复-@a_horse_with_no_name问题
I have a table
* Y: i' Q- M- f$ }. {# rCREATE TABLE test(
  from_date date,
  to_date date
( Z$ Z0 K6 K- [/ r1 ~7 y( ]);
INSERT INTO test(from_date,to_date)
--VALUES('20171101','20171115');
VALUES(TO_DATE('20171101','YYYYMMDD'),TO_DATE('20171115','YYYYMMDD'));
  Y2 V  _2 g5 |3 h2 J9 r9 tThe following query in Oracle return only one row (expected 15 rows)
' K4 A  K. h6 z2 K1 h1 lWITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
# q. k4 W* B; [$ H. r: b- M0 e; V  FROM test
/ s7 P, `2 F/ z% u5 z8 [. P7 e. W7 l  UNION ALL
' |% v0 z6 R' B+ {, ]! h2 F5 r  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
  FROM dateCTE
) ~& N" x  V% c6 h0 H, |8 I  WHERE dSQL Fiddle - http://sqlfiddle.com/#!4/36907/8
For test I changed the condition to i
WITH dateCTE(from_date,to_date,d,i) AS(
7 V7 C$ Y, M: g! b2 O, x1 g. a  SELECT from_date,to_date,from_date AS d,1 AS i
  i$ }) M( A: m  L$ O' K  FROM test
1 A( u  \6 b% _. ]! L  UNION ALL
  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
' t( n' _& R: T+ n& C7 `3 K, r  FROM dateCTE
1 v$ Z  }! r; O$ e) C( ~  --WHERE dAnd get the next result
! [9 p1 f/ K4 o4 w, {# x: A  Q| D          | I  |
  J8 y/ ?9 i0 I% N|------------|----|
/ d, F$ ]5 _4 C1 K6 D. B| 2017-11-01 |  1 |
. i" b  V& p4 T/ C4 A- K| 2017-10-31 |  2 |
| 2017-10-30 |  3 |
) w6 k/ v- E  r# f  [% X| 2017-10-29 |  4 |
: _1 T& `2 `/ S0 w" || 2017-10-28 |  5 |
1 |# }9 g; @- B* L" L| 2017-10-27 |  6 |
$ A* m$ O7 S$ n5 g' W| 2017-10-26 |  7 |
| 2017-10-25 |  8 |
: y. q  Y. c  l% f| 2017-10-24 |  9 |
| 2017-10-23 | 10 |
. |7 M& U4 v$ Z* l/ ^0 p2 E8 LWhy do this recursive query returned bad result in Oracle?
SQL Fiddle - http://sqlfiddle.com/#!4/36907/5
. r. \; w: @% s3 K9 b* I为了进行测试，我将条件更改为 i
+ y+ U1 ?/ c+ @4 Y& i/ t0 gWITH dateCTE(from_date,to_date,d,i) AS(
7 U! x1 h- m2 m  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test
  UNION ALL
6 P' |3 U( V) _  SELECT from_date,to_date,DATEADD(DAY,1,d),i+1
- u! R- D! s6 O3 j; d  FROM dateCTE
8 ]2 V4 x- H# z4 H8 B  WHERE dThe right result
d           i
2017-11-01  1
2017-11-02  2
* o# \1 G/ d' T9 B  K) W% `0 f$ O2017-11-03  3
: M8 ]/ W& m7 t# D4 A' e2017-11-04  4
' @% Y1 e3 F& \7 n& r4 \* s2017-11-05  5
+ q; f! `# _5 `5 Z% Y6 R2017-11-06  6
9 r. J$ i! z- F2017-11-07  7
8 H4 Q- o, I( X2017-11-08  8
3 n0 A' y# n# k" Q; ]8 C1 {2017-11-09  9
2017-11-10  10
; Z2 `, y3 }% n9 Z, E2017-11-11  11
2017-11-12  12
2017-11-13  13
+ b/ E) t2 A2 R, C$ S2017-11-14  14
2017-11-15  15
) O6 W0 Q$ Y, x( P( K               
解决方案:
: p# p0 S9 W, W3 }2 L: x               
' v  b/ g) J& @
# [' f# T5 Z- Y7 r+ Q/ k
& `9 l6 Y4 y' j                如果您希望有一个连续的起始日期到最新日期，请使用以下选择：
SELECT  DATE '2017-11-01' + LEVEL - 1 AS D, LEVEL AS I
FROM DUAL
CONNECT BY LEVEL <= DATE '2017-11-15' - DATE '2017-11-01' + 1;