En un artículo publicado por Mike Dietrich a partir de un descubrimiento de Peter Lehmann en octubre 27 del 2022, Mike explica el comportamiento extraño de una consulta elaborada en código ANSI en una base de datos Oracle 19c con PSU 19.17.0.
En las actualizaciones posteriores a la publicación realizadas en noviembre del 2022, Mike ilustraba la manera en como resolver el problema y mencionaba que era posible que en 19.18.0 el problema sería resuelto.
En actualización del 16 de marzo del 2023, Mike menciona que el proceso va lento y que en 19.19.0 en adelante, el parámetro "_fix_control" no se debe configurar y que el valor predeterminado es "_fix_control='29015273:ON'.
"Para lo que sería la versión 23c, el parámetro predeterminado podría cambiar nuevamente, solucionando immediatamente el error.", menciona Mike.
Sin embargo, en la versión 23c Free for Developers, el parámetro no esta configurado de facto y el error se mantiene.
Luego de aplicar la solución dada por Peter para la versión de base de datos con PSU 19.17.0, si es posible en la versión 23c FREE, ejecutar la consulta sin errores.
El tema de fondo, es que hasta donde he logrado comprobar, en las versiones Beta, el parámetro no es configurable y el problema aún se mantiene.
A continuación el caso de estudio.
******************************************************************
# Oracle Database 23c Free for Developers
Using username "opc".
Authenticating with public key "rsa-key-20200507"
Passphrase for key "rsa-key-20200507":
Activate the web console with: systemctl enable --now cockpit.socket
Last login: Tue May 2 03:48:27 2023 from 201.204.89.63
[opc@oracle-database-server-free-for-developers ~]$ sudo su - oracle
Last login: Tue May 2 04:37:35 GMT 2023 on pts/0
[oracle@oracle-database-server-free-for-developers ~]$ sqlplus hr/oracle@pdb1
-bash: sqlplus: command not found
[oracle@oracle-database-server-free-for-developers ~]$ . oraenv
ORACLE_SID = [oracle] ? FREE
The Oracle base has been set to /opt/oracle
[oracle@oracle-database-server-free-for-developers ~]$ sqlplus hr/oracle@pdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Thu May 4 00:25:41 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Tue May 02 2023 04:39:57 +00:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> create table t1 (id int);
create table t2 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t2 values(1);
insert into t2 values(2);
commit;
Table created.
SQL>
Table created.
SQL> SQL>
1 row created.
SQL>
1 row created.
SQL> SQL>
1 row created.
SQL>
1 row created.
SQL> SQL>
Commit complete.
SQL> select * from t1;
ID
----------
1
2
SQL> select * from t2;
ID
----------
1
2
select a_t1.id from t1 a_t1
inner join t2 a_t2 on a_t1.id=a_t2.id
inner join t2 a_t2 on a_t1.id=a_t2.id;
ID
----------
1
2
select a_t2.id from t1 a_t1
inner join t2 a_t2 on a_t1.id=a_t2.id
inner join t2 a_t2 on a_t1.id=a_t2.id
;
select a_t2.id from t1 a_t1
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> show user
USER is "HR"
SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
[oracle@oracle-database-server-free-for-developers ~]$ cd $ORACLE_HOME
[oracle@oracle-database-server-free-for-developers dbhomeFree]$ cd OPatch
[oracle@oracle-database-server-free-for-developers OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.36
Copyright (c) 2023, Oracle Corporation. All rights reserved.
Oracle Home : /opt/oracle/product/23c/dbhomeFree
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/23c/dbhomeFree/oraInst.loc
OPatch version : 12.2.0.1.36
OUI version : 12.2.0.9.0
Log file location : /opt/oracle/product/23c/dbhomeFree/cfgtoollogs/opatch/opatch2023-05-04_03-36-20AM_1.log
Lsinventory Output file location : /opt/oracle/product/23c/dbhomeFree/cfgtoollogs/opatch/lsinv/lsinventory2023-05-04_03-36-20AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: oracle-database-server-free-for-developers.vcnlandb.vcntestinglab.oraclevcn.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 23c 23.0.0.0.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@oracle-database-server-free-for-developers OPatch]$ sqlplus sys/oracle@pdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Thu May 4 03:38:37 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> alter system set "_fix_control" = '29015273:ON' scope=both;
System altered.
SQL> connect / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
SQL> alter pluggable database FREEPDB1 close;
Pluggable database altered.
SQL> alter pluggable database FREEPDB1 open;
Pluggable database altered.
SQL> show parameter fix_control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_fix_control string 29015273:1
SQL> connect hr/oracle@pdb1
Connected.SQL> select a_t2.id from t1 a_t1
inner join t2 a_t2 on a_t1.id=a_t2.id
inner join t2 a_t2 on a_t1.id=a_t2.id;
ID
----------
1
2
SQL>
