Oracle UNION和ORDER BY的奇怪问题 [英] Curious issue with Oracle UNION and ORDER BY

问题描述

以下查询在几乎每个数据库中都是完全有效(给出或获取dual哑表) )，包括Oracle:

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

返回:

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

现在此查询仍然是非常标准的SQL，但在Oracle上不起作用

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

我要

ORA-00904: "X": invalid identifier

但是，这有效:

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

我一直在研究这个问题，并且发现，显然，至少第一个子选择和倒数第二个(??)子选择需要有一个名为x的列.在第一个示例中，两个子选择似乎完全重合. 工作示例:

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

您可能已经猜到了，这无效:

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

有趣的旁注:

派生表似乎不受此限制. 这有效:

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

问题:

这是Oracle SQL解析器中的一个(已知的)错误，还是在语言语法中有任何非常细微的细节，这些语法上绝对需要第一个和第二个最后一个子选择保存从ORDER BY子句?

解决方案

这并不能真正回答问题，但这似乎是解析器错误(或功能")，而不是语言要求.

根据My Oracle Support，这似乎已作为bug 14196463提出，但没有解决方法而关闭. 社区线程3561546 中也提到了此问题.您需要一个MOS帐户，或至少一个Oracle帐户，才能查看其中任何一个.

还已经在OTN线程中讨论了 ，这需要基本的Oracle登录名而不是MOS据我所知.那也没有太多信息，但是会重复您的发现，并且还表明这种行为至少可以追溯到9.2.0.8甚至更早.

文档有点含糊但这并不表示这应该是一个问题:

对于包含集合运算符UNION，INTERSECT，MINUS或UNION ALL的复合查询，ORDER BY子句必须指定位置或别名，而不是显式表达式.另外，ORDER BY子句只能出现在最后一个组件查询中. ORDER BY子句对整个复合查询返回的所有行进行排序.

您要为表达式加上别名，并使用它，它并没有说您必须为特定的组件别名(尽管当然也没有说您不必也要使用别名). /p>

该行为似乎与别名对最终投影有效并不相符，并且有关别名仅在order by子句中有效的通常规则-似乎介于两者之间.

The following query is perfectly valid in pretty much every database (give or take a dual dummy table), including Oracle:

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

Returning:

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

Now this query is still quite standard SQL, but doesn't work on Oracle

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

I'm getting

ORA-00904: "X": invalid identifier

This, however, 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

I've been playing around with this issue and figured out that apparently, at least the first subselect and the second-last (??) subselect need to have a column called x. In the first example, the two subselects seemed to simply coincide. Working example:

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

As you may have guessed, this wouldn't work:

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

Interesting side-note:

Derived tables seem not to suffer from this limitation. This works:

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

Question:

Is this a (known?) bug in the Oracle SQL parser, or is there any very subtle detail in the language syntax that absolutely requires the first and the second-last subselect to hold a column of the name as referenced from the ORDER BY clause?

解决方案

This doesn't really answer the question, but it seems to be a parser bug (or 'feature') rather than a language requirement.

According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It's also mentioned in community thread 3561546. You need a MOS account, or at least an Oracle account, to see either of those though.

It's also been discussed in an OTN thread which requires a basic Oracle login rather than a MOS account, as far as I can tell. That also doesn't have much information but repeats your findings, and also suggests the behaviour has existed back at least to 9.2.0.8 and perhaps much earlier.

The documentation is a bit vague but doesn't indicate this is expected to be a problem:

For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

You are aliasing your expression and using that, and it doesn't say you have to alias particular components (although of course it doesn't say you don't have to either).

The behaviour seems inconsistent with the alias being valid for the final projection, and the usual rule about the alias only being valid in the order by clause - this seems to be falling down somewhere in between.

这篇关于Oracle UNION和ORDER BY的奇怪问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！