sql - examples - Tema curioso con Oracle UNION y ORDER BY

union oracle examples

Question

La siguiente consulta es perfectamente válida en casi todas las bases de datos (dar o tomar una tabla ficticia dual ), incluido Oracle:

select ''A'' as x from dual union all
select ''B''      from dual
order by x asc

Volviendo

| X |
|---|
| A |
| B |

Ahora esta consulta sigue siendo bastante estándar de SQL, pero no funciona en Oracle

select ''A'' as x from dual union all
select ''B''      from dual union all
select ''C''      from dual
order by x asc

Me estoy poniendo

ORA-00904: "X": invalid identifier

Esto, sin embargo, works :

select ''A'' as x from dual union all
select ''B'' as x from dual union all
select ''C''      from dual
order by x asc

He estado jugando con este problema y he descubierto que al parecer, al menos la primera subselección y la segunda subselección (??) necesitan tener una columna llamada x . En el primer ejemplo, las dos subselecciones parecían simplemente coincidir. Ejemplo de trabajo :

select ''A'' as x from dual union all
select ''B''      from dual union all
select ''C''      from dual union all
select ''D''      from dual union all
select ''E''      from dual union all
select ''F'' as x from dual union all
select ''G''      from dual
order by x asc

Como habrás adivinado, esto no funcionaría :

select ''A'' as x from dual union all
select ''B''      from dual union all
select ''C''      from dual union all
select ''D''      from dual union all
select ''E'' as x from dual union all
select ''F''      from dual union all
select ''G''      from dual
order by x asc

Nota interesante:

Las tablas derivadas parecen no sufrir de esta limitación. Esto funciona

select * from (
  select ''A'' as x from dual union all
  select ''B''      from dual union all
  select ''C''      from dual
)
order by x asc

Pregunta:

¿Se trata de un error (conocido?) En el analizador de Oracle SQL, o hay algún detalle muy sutil en la sintaxis del lenguaje que requiere absolutamente la primera y la segunda subselección última para contener una columna del nombre como se menciona en la cláusula ORDER BY ?

Answer 1

Esto no responde por qué está obteniendo un comportamiento inconsistente de su consulta actual, pero en Oracle puede volver a escribir fácilmente lo siguiente (que nunca debería fallar con un error de identificador no válido):

with t(x) as (
  select ''A'' from dual
  union all
  select ''B'' from dual
  union all
  select ''C'' from dual
)
select * from t
order by x asc

Con la ventaja añadida de que solo se especifica el alias de columna (x) una vez.

Answer 2

Esto realmente no responde a la pregunta, pero parece ser un error del analizador (o "característica") en lugar de un requisito de idioma.

De acuerdo con My Oracle Support, esto parece haberse planteado como error 14196463 pero se cerró sin resolución. También se menciona en el hilo de la comunidad 3561546 . Necesitas una cuenta MOS, o al menos una cuenta Oracle, para ver cualquiera de esas sin embargo.

También se ha discutido en un hilo OTN que requiere un inicio de sesión Oracle básico en lugar de una cuenta MOS, por lo que puedo decir. Eso tampoco tiene mucha información, pero repite sus hallazgos y también sugiere que el comportamiento ha existido al menos hasta 9.2.0.8 y quizás mucho antes.

La documentación es un poco vaga pero no indica que se espera que esto sea un problema:

Para consultas compuestas que contienen operadores de conjuntos UNION , INTERSECT , MINUS o UNION ALL , la cláusula ORDER BY debe especificar posiciones o alias en lugar de expresiones explícitas. Además, la cláusula ORDER BY solo puede aparecer en la última consulta del componente. La cláusula ORDER BY ordena todas las filas devueltas por la consulta compuesta completa.

Alias ​​tu expresión y la usas, y no dice que tengas que asignar alias a componentes particulares (aunque, por supuesto, tampoco dice que no tengas que hacerlo ).

El comportamiento parece ser inconsistente con el alias que es válido para la proyección final, y la regla usual acerca de que el alias solo es válido en la cláusula orden por orden, esto parece estar cayendo en algún punto intermedio.