ORA-01791: Not a selected expression a bug not a bug!

[[Email protected] ~] $! SQL
Sqlplus/As sysdba

SQL * Plus: Release 11.2.0.1.0 production on Wed Aug 27 09:50:54 2014

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected:
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.

------ Replace empno with 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

 

----- Add ename and empno to 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.

 

Why can't I execute empno after it is changed to ename?
Perform the following tests:

--- Remove the primary key from the column 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

 

--- Add the primary key of the empno Column
SQL> ALTER TABLE t_emp add constraint pk_t_emp primary key (empno );

Table altered.

SQL> DESC t_emp
Name null? Type
Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------
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.

 

Summary:

As to why there is a bug that is not a bug, it is actually a pitfall for development, and then let the DBA jump inside:
 
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:
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
Bug 13768663-select works in 10.2 and 11.2.0.3 fails 11.1.0.7 ORA-01791
Invalid query which shocould 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 shocould 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 reserved ented 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

 

 

 

ORA-01791: Not a selected expression a bug not a bug!