Can someone please try the SQLs below in 9i?
I tried in 8i, different 10g boxes and 11g...
The blank-padded comparison semantics works with outer joins in 8i, but it doesn't work in 10g or 11g --a bug?
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------
----------
----------
----------
--------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Elapsed: 00:00:00.00
SQL> SELECT 'X' COL FROM DUAL WHERE 'X' = 'X '
2 /
C
-
X
Elapsed: 00:00:00.00
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X, (SELECT 'X ' COL FROM DUAL) Y
3 WHERE X.COL = Y.COL
4 /
C COL
- ----
X X
Elapsed: 00:00:00.00
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X, (SELECT 'X ' COL FROM DUAL) Y
3 WHERE X.COL = Y.COL(+)
4 /
C COL
- ----
X X
Elapsed: 00:00:00.00
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X, (SELECT 'X ' COL FROM DUAL) Y
3 WHERE X.COL(+) = Y.COL
4 /
C COL
- ----
X X
Elapsed: 00:00:00.00
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------
----------
----------
----------
--------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Elapsed: 00:00:00.25
SQL> SELECT 'X' COL FROM DUAL WHERE 'X' = 'X '
2 /
C
-
X
Elapsed: 00:00:00.01
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X, (SELECT 'X ' COL FROM DUAL) Y
3 WHERE X.COL = Y.COL
4 /
C COL
- ----
X X
Elapsed: 00:00:00.03
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X, (SELECT 'X ' COL FROM DUAL) Y
3 WHERE X.COL = Y.COL(+)
4 /
C COL
- ----
X
Elapsed: 00:00:00.03
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X, (SELECT 'X ' COL FROM DUAL) Y
3 WHERE X.COL(+) = Y.COL
4 /
C COL
- ----
X
Elapsed: 00:00:00.03
SQL> SELECT X.COL, Y.COL FROM
2 (SELECT 'X' COL FROM DUAL) X LEFT OUTER JOIN (SELECT 'X ' COL FROM DUAL) Y
3 ON(X.COL = Y.COL)
4 /
C COL
- ----
X
Elapsed: 00:00:00.03
SQL>
Thanks a lot!
Alex
