ORA-01791: not a SELECTed expression after upgrade to 11.2.0.4 (文档 ID 1600974.1)

最新推荐文章于 2025-01-08 08:30:00 发布

原创最新推荐文章于 2025-01-08 08:30:00 发布 · 2.7k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 错误解决专栏收录该内容

45 篇文章

订阅专栏

本文讨论了在升级到Oracle数据库11.2.0.4版本后，使用select distinct查询时遇到的问题，特别是在order by子句中不引用select列表项的情况下出现ORA-01791错误。提供了正确的解决方案和原因分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ORA-01791: not a SELECTed expression after upgrade to 11.2.0.4 (文档 ID 1600974.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS

a select DISTINCT query and the order by column does not reference a select list item after upgrade to 11.2.0.4

                   SQL> select distinct sal, empno from scott.emp order by deptno;
                  
 select distinct sal, empno from scott.emp order by deptno
                  
                                                    *
                  
 ERROR at line 1:
                  
 ORA-01791: not a SELECTed expression

But it was working on previous release ..

                   SQL> select distinct sal, empno from scott.emp order by deptno;
                  
        SAL      EMPNO
                  
 ---------- ----------
                  
       2450       7782
                  
       5000       7839

CHANGES

upgrade to 11.2.0.4

CAUSE

The issue have been investigated in the following bug:

Bug:17655864 - ORA-01791: NOT A SELECTED EXPRESSION AFTER 11.2.0.4 PATCH

which is closed as not a bug. and this is expected behvior .

so the correct behavior is on 11.2.0.4 and not older versions.

This is due to

BUG 13768663 - SELECT WORKS IN 10.2 AND 11.2.0.3 FAILS 11.1.0.7 ORA-01791

Invalid query which should raise ORA-1791 is working fine without any error starting from 11.2.0.1.This is fixed in 11.2.0.4 and hence you may get the error ORA-1791 in 11.2.0.4.

SOLUTION

The expected behaviour for this statement is that it should report ORA-01791 That is, this is a select DISTINCT query and the order by column does not reference a select list item. This is a documented restriction of the order by clause.

http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#SQLRF20039

This behaviour is corrected through bugfix 13768663.

so please add the orderby column in the select statement

SQL> select distinct sal, empno, deptno from scott.emp order by deptno;

       SAL      EMPNO     DEPTNO
---------- ---------- ----------
      2450       7782         10
      5000       7839         10
      1300       7934         10

REFERENCES

BUG:17655864 - ORA-01791: NOT A SELECTED EXPRESSION AFTER 11.2.0.4 PATCH
NOTE:13768663.8 - Bug 13768663 - ORA-1791 not reported in 11.2 when expected
BUG:13768663 - SELECT WORKS IN 10.2 AND 11.2.0.3 FAILS 11.1.0.7 ORA-01791