ORA-01791: not a SELECTed expression 一个不是 bug 的 bug！，notbug
[ora11@lixora ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 27 09:50:54 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select distinct sal, empno from scott.emp order by deptno;
SAL EMPNO
---------- ----------
2450 7782
5000 7839
1300 7934
2975 7566
3000 7902
1100 7876
800 7369
3000 7788
1250 7521
1500 7844
1600 7499
SAL EMPNO
---------- ----------
950 7900
2850 7698
1250 7654
14 rows selected.
------把 empno 换成 ename
SQL> select distinct sal,ename from scott.emp order by deptno;
select distinct sal,ename from scott.emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
-----把,ename,empno 都加到select 中：
SQL> select distinct sal,ename,empno from scott.emp order by deptno;
SAL ENAME EMPNO
---------- -------------------- ----------
2450 CLARK 7782
5000 KING 7839
1300 MILLER 7934
2975 JONES 7566
3000 FORD 7902
1100 ADAMS 7876
800 SMITH 7369
3000 SCOTT 7788
1250 WARD 7521
1500 TURNER 7844
1600 ALLEN 7499
SAL ENAME EMPNO
---------- -------------------- ----------
950 JAMES 7900
2850 BLAKE 7698
1250 MARTIN 7654
14 rows selected.
SQL> select distinct sal,empno,sal from scott.emp order by deptno;
SAL EMPNO SAL
---------- ---------- ----------
2450 7782 2450
5000 7839 5000
1300 7934 1300
2975 7566 2975
3000 7902 3000
1100 7876 1100
800 7369 800
3000 7788 3000
1250 7521 1250
1500 7844 1500
1600 7499 1600
SAL EMPNO SAL
---------- ---------- ----------
950 7900 950
2850 7698 2850
1250 7654 1250
14 rows selected.
这里为啥 empno 换成ename 后就无法执行了呢？
在做下以下测试：
---去掉empno 列上的主键
SQL> select distinct sal, empno from scott.t_emp order by deptno;
select distinct sal, empno from scott.t_emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
---添加empno 列上的主键
SQL> alter table t_emp add constraint pk_t_emp primary key(empno) ;
Table altered.
SQL> desc t_emp
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select distinct sal, empno from scott.t_emp order by deptno;
SAL EMPNO
---------- ----------
2450 7782
5000 7839
1300 7934
2975 7566
3000 7902
1100 7876
800 7369
3000 7788
1250 7521
1500 7844
1600 7499
950 7900
2850 7698
1250 7654
14 rows selected.
问题总结：
至于为什么会有这个不是bug 的bug 存在，其实是开发给挖的坑，然后让 dba 往里跳：
FYI：
ORA-01791: not a SELECTed expression after upgrade to 11.2.0.4 (Doc ID 1600974.1)
http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#SQLRF20039
##########################################################
Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:
•If you have specified the DISTINCT operator in this statement, then this clause cannot refer to columns unless they appear in the select list.
•An order_by_clause can contain no more than 255 expressions.
•You cannot order by a LOB, LONG, or LONG RAW column, nested table, or varray.
•If you specify a group_by_clause in the same statement, then this order_by_clause is restricted to the following expressions:
◦Constants
◦Aggregate functions
◦Analytic functions
◦The functions USER, UID, and SYSDATE
◦Expressions identical to those in the group_by_clause
◦Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group
##################################################################
ORA-01791: not a SELECTed expression after upgrade to 11.2.0.4 (Doc ID 1600974.1) To Bottom
________________________________________
In this Document
Symptoms
Changes
Cause
Solution
References
________________________________________
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
1.故障现象：网络适配器（网卡）设置与计算机资源有冲突。
分析、排除：通过调整网卡资源中的IRQ和I/O值来避开与计算机其它资源的冲突。有些情况还需要通过设置主板的跳线来调整与其它资源的冲突。
2.故障现象：网吧局域网中其他客户机在“网上邻居”上都能互相看见，而只有某一台计算机谁也看不见它，它也看不见别的计算机。（前提：该网吧的局域网是通过HUB或交换机连接成星型网络结构）
分析、排除：检查这台计算机系统工作是否正常；检查这台计算机的网络配置；检查这台计算机的网卡是否正常工作；检查这台计算机上的网卡设置与其他资源是否有冲突；检查网线是否断开；检查网线接头接触是否正常。
3.故障现象：网吧局域网中有两个网段，其中一个网网段的所有计算机都不能上因特网。（前提：该网吧的局域网通过两个HUB或交换机连接着两个的网段）
分析、排除：两个网段的干线断了或干线两端的接头接处不良。检查服务器中对该网段的设置项。
4.故障现象：网吧局域网中所有的计算机在“网上邻居”上都能互相看见。（前提：该网吧的局域网是通过HUB或交换机连接成星型网络结构）
分析、排除：检查HUB或交换机工作是否正常。
5.故障现象：网吧局域网中某台客户机在“网上邻居”上都能看到服务器，但就是不能上因特网。（前提：服务器指代理网吧局域网其他客机上因特网的那台计算机，以下同）
分析、排除：检查这台客户机TCP/IP协议的设置，检查这台客户机中IE浏览器的设置，检查服务器中有关对这台客户机的设置项。
6.故障现象：网吧整个局域网上的所有的计算机都不能上因特网。
分析、排除：服务器系统工作是否正常；服务器是否掉线了；调制解调器工作是否正常；局端工作是否正常。
7.故障现象：网吧局域网中除了服务器能上网其他客户机都不能上网。
分析、排除：检查HUB或交换机工作是否正常；检查服务器与HUB或交换机连接的网络部分（含：网卡、网线、接头、网络配置）工作是否正常；检查服务器上代理上网的软件是否正常启动运行；设置是否正常。
8.故障现象：进行拨号上网操作时，MODEN没有拨号声音，始终连接不上因特网，MODEN上指示灯也不闪。
分析、排除：电话线路是否占线；接MODEN的服务器的连接（含：连线、接头）是否正常；电话线路是否正常，有无杂音干扰；拨号网络配置是否正确；MODEN的配置设置是否正确，检查拨号音的音频或脉冲方式是否正常。
9.故障现象：系统检测不到MODEN（若MODEN是正常的）。
分析、排除：重新安装一遍MODEN，注意通讯端口的正确位置。
10.故障现象：连接因特网速度过慢。
分析、排除：检查服务器系统设置在“拨号网络”中的端口连接速度是否是设置的最大值；线路是否正常；可通过优化MODEN的设置来提高连接的速度；通过修改注册表也可以提高上网速度；同时上网的客户机是否很多；若是很多，而使连接速度过慢是正常现象。
11.故障现象：计算机屏幕上出现“错误 678” 或“错误 650” 的提示框。
分析、排除：一般是你所拨叫的服务器线路较忙、占线，暂时无法接通，你可进一会后继续重拨。
12.故障现象：计算机屏幕上出现“错误680：没有拨号音。请检测调制解调器是否正确连到电话线。”或者“There is no dialtone. Make sure your Modem is connected to the phone line properly.”的提示框。
分析、排除：检测调制解调器工作是否正常，是否开启；检查电话线路是否正常，是否正确接入调制解调器，接头有无松动。
13.故......余下全文>>