Bug in VIEW in Oracle 8i Lite when using SYSDATE with + operator

From: Ruud Laanbroek <ruudl_at_commotio.nl>
Date: 15 Aug 2002 04:46:57 -0700
Message-ID: <72f6e38f.0208150346.335ff965@posting.google.com>

Oracle 8i Lite, RDBMS Version: 4.0.0.1.0, 4.0.0.2.19 Windows NT 4.0 WS, SP6a

On 2002-08-13 00:18:26 PST I posted a problem concerning a VIEW in Oracle 8i Lite, which turned out to be caused by using SYSDATE in the VIEW. Well.. that was my conclusion at least.

Some further investigation shows that there is definitely a bug in Oracle 8i Lite when using the SYSDATE function with the + operator in a VIEW.

Ruud Laanbroek

Here's the proof:

Direct Queries with correct results

---

SQL> SELECT TO_CHAR(SYSDATE,'YYYYMMDD') D1 2 FROM DUAL
3 ;

D1

---

20020815

SQL> SELECT TO_CHAR(SYSDATE + 1,'YYYYMMDD') D1 2 FROM DUAL
3 ;

D1

---

20020816

SQL> SELECT TO_CHAR(SYSDATE + 100,'YYYYMMDD') D1 2 FROM DUAL
3 ;

D1

---

20021123

Queries using VIEW with incorrect results

---

SQL> CREATE VIEW V_TEST_SYSDATE10
2 AS
3 SELECT TO_CHAR(SYSDATE,'YYYYMMDD') D1 4 FROM DUAL
5 ;

View created.

SQL> CREATE VIEW V_TEST_SYSDATE11
2 AS
3 SELECT TO_CHAR(SYSDATE + 1,'YYYYMMDD') D1 4 FROM DUAL
5 ;

View created.

SQL> CREATE VIEW V_TEST_SYSDATE12
2 AS
3 SELECT TO_CHAR(SYSDATE + 100,'YYYYMMDD') D1 4 FROM DUAL
5 ;

View created.

SQL> SELECT * FROM V_TEST_SYSDATE10
2 ;

D1

---

20020815

SQL> SELECT * FROM V_TEST_SYSDATE11
2 ;

D1

---

20011202

SQL> SELECT * FROM V_TEST_SYSDATE12
2 ;

D1

---

19321020 Received on Thu Aug 15 2002 - 06:46:57 CDT