sql - tablas - Ambigüedad en uniones a la izquierda(¿solo oráculo?)

oracle left join vs(+)

Question

Mi jefe encontró un error en una consulta que creé, y no entiendo el razonamiento detrás del error, aunque los resultados de la consulta prueban que es correcto. Aquí está la consulta (versión simplificada) antes de la corrección:

select PTNO,PTNM,CATCD
from PARTS
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 

y aquí está después de la corrección:

select PTNO,PTNM,PARTS.CATCD
from PARTS
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 

El error era que se mostraban valores nulos para la columna CATCD, es decir, los resultados de la consulta incluían los resultados de la tabla CATEGORÍAS en lugar de PARTS. Esto es lo que no entiendo: si había ambigüedad en la consulta original, ¿por qué Oracle no arrojó un error? Por lo que he entendido, en el caso de las uniones a la izquierda, la tabla "principal" en la consulta (PIEZAS) tiene precedencia en ambigüedad. ¿Estoy equivocado o simplemente no estoy pensando en este problema correctamente?

Actualizar:

Aquí hay un ejemplo revisado, donde no se arroja el error de ambigüedad:

CREATE TABLE PARTS (PTNO NUMBER, CATCD NUMBER, SECCD NUMBER);
CREATE TABLE CATEGORIES(CATCD NUMBER);
CREATE TABLE SECTIONS(SECCD NUMBER, CATCD NUMBER);
select PTNO,CATCD
from PARTS
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD)
left join SECTIONS on (SECTIONS.SECCD=PARTS.SECCD) ;

Alguien tiene una pista?

Answer 1

Aquí está la consulta (versión simplificada)

Creo que al simplificar la consulta eliminaste la causa real del error :-)

¿Qué versión de Oracle estás usando? Oracle 10g (10.2.0.1.0) da:

create table parts (ptno number , ptnm number , catcd number);
create table CATEGORIES (catcd number);
select PTNO,PTNM,CATCD from PARTS
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD);

Obten ORA-00918: columna ambiguamente definida

Answer 2

@Pat: obtengo el mismo error aquí para su consulta. Mi consulta es un poco más complicada de lo que originalmente publiqué. Estoy trabajando en un ejemplo simple reproducible ahora.

Answer 3

Al igual que HollyStyles, no puedo encontrar nada en los documentos de Oracle que pueda explicar lo que estás viendo.

PostgreSQL, DB2, MySQL y MSSQL se niegan a ejecutar la primera consulta, ya que es ambigua.

Answer 4

Desde mi experiencia, si creas una consulta como esta, el resultado de los datos tirará de CATCD del lado derecho de la unión no a la izquierda cuando haya una superposición de campo como esta.

Entonces, dado que esta unión tendrá todos los registros de PIEZAS con solo un atraso de CATEGORÍAS, tendrá NULO en el campo CATCD cada vez que no haya datos en el lado derecho.

Al definir explícitamente la columna como PARTS (es decir, el lado izquierdo) obtendrá un valor no nulo suponiendo que el campo tiene datos en PARTS.

Recuerde que con LEFT JOIN solo se le garantizan los datos en los campos de la tabla de la izquierda, es posible que haya columnas vacías a la derecha.

Answer 5

En general, se recomienda ser específico y calificar todos los nombres de las columnas de todos modos, ya que ahorra un poco de trabajo al optimizador. Ciertamente en SQL Server.

De lo que puedo deducir de los documentos de Oracle , parece que solo arrojará si selecciona el nombre de la columna dos veces en la lista de selección, o una vez en la lista de selección y luego en otra parte como una cláusula order by.

Tal vez has descubierto una ''función no documentada'' :)

Answer 6

Este es un error conocido con algunas versiones de Oracle cuando se utilizan combinaciones de estilo ANSI. El comportamiento correcto sería obtener un error ORA-00918.

Siempre es mejor especificar los nombres de su tabla de todos modos; De esta manera, sus consultas no se rompen cuando agrega una nueva columna con un nombre que también se usa en otra tabla.

Answer 7

Este es un error en Oracle 9i. Si unes más de 2 tablas usando notación ANSI, no detectará ambigüedades en los nombres de columna, y puede devolver la columna incorrecta si no se usa un alias.

Como ya se ha mencionado, se fija en 10g, por lo que si no se utiliza un alias, se devolverá un error.

Answer 8

Esto puede ser un error en el optimizador de Oracle. Puedo reproducir el mismo comportamiento en la consulta con 3 tablas. Intuitivamente parece que debería producir un error. Si lo reescribo de cualquiera de las siguientes maneras, genera un error:

(1) Usar combinación externa de estilo antiguo

select ptno, catcd
from parts, categories, sections
where categories.catcd (+) = parts.catcd
and sections.seccd (+) = parts.seccd

(2) Aislamiento explícito de las dos uniones

select ptno, catcd
from (
select ptno, seccd, catcd
from parts
left join categories on (categories.CATCD=parts.CATCD)
)
left join sections on (sections.SECCD=parts.SECCD)

Utilicé DBMS_XPLAN para obtener detalles sobre la ejecución de la consulta, que mostró algo interesante. El plan es básicamente unir PIEZAS y CATEGORÍAS, proyectar ese conjunto de resultados, luego unirlo a SECCIONES. La parte interesante es que en la proyección de la primera combinación externa, solo incluye PTNO y SECCD, NO incluye el CATCD de ninguna de las dos primeras tablas. Por lo tanto, el resultado final es obtener CATCD de la tercera tabla.

Pero no sé si esto es una causa o un efecto.

Answer 9

Estoy usando Oracle 9.2.0.8.0. y da el error "ORA-00918: columna ambiguamente definida".

Answer 10

Interesante en el servidor SQL que arroja un error (como debería)

select id
from sysobjects s
left join syscolumns c on s.id = c.id

Servidor: Msg 209, nivel 16, estado 1, línea 1 Nombre de columna ambiguo ''id''.

select id
from sysobjects
left join syscolumns on sysobjects.id = syscolumns.id

Servidor: Msg 209, nivel 16, estado 1, línea 1 Nombre de columna ambiguo ''id''.

Answer 11

Me temo que no puedo decirte por qué no recibes una excepción, pero puedo postular por qué eligió la versión de CATEGORÍAS de la columna sobre la versión de PARTS.

Por lo que entiendo, en el caso de las uniones a la izquierda, la tabla "principal" en la consulta (PIEZAS) tiene precedencia en la ambigüedad

No está claro si por "principal" se entiende simplemente la tabla izquierda en una combinación izquierda, o la tabla "conducción", como se ve la consulta conceptualmente ... Pero en cualquier caso, lo que se ve como la tabla "principal" en la consulta tal como la ha escrito no será necesariamente la tabla "principal" en la ejecución real de esa consulta.

Supongo que Oracle simplemente está usando la columna de la primera tabla a la que accede al ejecutar la consulta. Y dado que la mayoría de las operaciones individuales en SQL no requieren que una tabla sea golpeada antes que la otra, el DBMS decidirá en el tiempo de análisis que es el más eficiente para escanear primero. Intente obtener un plan de ejecución para la consulta. Sospecho que puede revelar que está golpeando primero CATEGORÍAS y luego PIEZAS.

Answer 12

Una pregunta más importante que debería hacerse es: ¿por qué tengo un código de categoría en la tabla de partes que no existe en la tabla de categorías?