ORA-01791: 不是 SELECTed 表达式

davis987

在oracle 11.2.0.3上执行是完全没有问题的，但是11.2.0.4（RAC）上执行就是会报0ra-01791
select distinct dd.id, b.proj_name, dd.chrg_card, dd.holder_name, b.proj_id
  from tk_mem_creditcard_his_dt dd, bd_datadeta_dt b
where dd.chrg_cardnm = b.proj_id
   and b.datatype_id = '00008'
   and dd.paytype_id = '0000300001'
   and dd.member_card_no = '105664728'
   order by dd.update_time desc

ORA-01791: 不是 SELECTed 表达式

当然我测试如果在select里加上 dd.update_time 在11.2.0.4也不会报错，如下：
select distinct dd.id, b.proj_name, dd.chrg_card, dd.holder_name, b.proj_id,dd.update_time
  from tk_mem_creditcard_his_dt dd, bd_datadeta_dt b
where dd.chrg_cardnm = b.proj_id
   and b.datatype_id = '00008'
   and dd.paytype_id = '0000300001'
   and dd.member_card_no = '105664728'
   order by dd.update_time desc


但是奇怪的是在高版本里为什么倒还报错呢，应该向后兼容才对呀，11.2.0.4版本为什么不行呢？
我们现在上一个升级的系统，如果旧版里存在这样的语句会出潜在的问题。。。

谢谢！

davis987

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

Maclean Liu(刘相兵

做一个hard parse +10053 我看下

Maclean Liu(刘相兵

odm finding:
This issue is fixed in       
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)
Symptoms:

Related To:

Wrong Results
ORA-1791
Optimizer
Constraint Related
_optimizer_eliminate_filtering_join
Description

Invalid query which should raise ORA-1791 is working fine without any error starting from 11.2.0.1.

There are 2 cases this fix addresses

1) A combination of NOT NULL constraint , UNIQUE index and invalid SELECT DISTINCT

create table t (x varchar2(10),y date) ;
create unique index i on t(x) ;
alter table t modify (x not null enable);

prompt Invalid SELECT DISTINCT raises no error

select distinct x
from t
order by y;

2) An invalid query undergoing "optimizer_eliminate_filtering_join" transformation.

prompt Invalid SELECT DISTINCT raises no error

select   distinct t2.col1
from     tab1 t0,
         tab2 t2,
         tab2 t3
where    t0.col1=t2.col2
and      t0.col1=t3.col2
order by t3.col1;

In the above DISTINCT statement case the ORDER BY clause cannot refer to column (t3.col1)
unless they appear in the SELECT list item. This is a documented restriction of the ORDER BY clause.

Workaround
Correct the query , as it will fail with this bug fixed anyway.

davis987

SQL> alter session set tracefile_identifier='licz';

Session altered

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

Session altered

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

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

ORA-01791: 不是 SELECTed 表达式

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

      SAL EMPNO DEPTNO
--------- ----- ------
  2450.00  7782     10
  5000.00  7839     10
  1300.00  7934     10
  2975.00  7566     20
  3000.00  7902     20
  1100.00  7876     20
   800.00  7369     20
  3000.00  7788     20
  1250.00  7521     30
  1500.00  7844     30
  1600.00  7499     30
   950.00  7900     30
  2850.00  7698     30
  1250.00  7654     30

14 rows selected

SQL>  ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered

SQL>

davis987

在11.2.0.3中发现，如果select 中去掉主键empno 也依然会报错，也就是说在旧版本中这种情况没有报错，说明select后中列组本身就是唯一的，所以完成可以去年distinct的用法，结果是一样的！
SQL> select distinct sal, empno from scott.emp order by deptno;

      SAL EMPNO
--------- -----
  2450.00  7782
  5000.00  7839
  1300.00  7934
  2975.00  7566
  3000.00  7902
  1100.00  7876
   800.00  7369
  3000.00  7788
  1250.00  7521
  1500.00  7844
  1600.00  7499
   950.00  7900
  2850.00  7698
  1250.00  7654

14 rows selected
SQL> select distinct sal from scott.emp order by deptno;

select distinct sal from scott.emp order by deptno

ORA-01791: 不是 SELECTed 表达式

这时我们去掉distinct用法，其实和第一次结果是一样的
SQL> select sal, empno from scott.emp order by deptno;

      SAL EMPNO
--------- -----
  2450.00  7782
  5000.00  7839
  1300.00  7934
  2975.00  7566
  3000.00  7902
  1100.00  7876
   800.00  7369
  3000.00  7788
  1250.00  7521
  1500.00  7844
  1600.00  7499
   950.00  7900
  2850.00  7698
  1250.00  7654

14 rows selected

Maclean Liu(刘相兵

没有搞懂 你5楼 的内容，具体过程请给直接的日志 不要手工处理。

_optimizer_eliminate_filtering_join = true


----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |     4 |           |
| 1   |  SORT ORDER BY      |         |    14 |   154 |     4 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | EMP     |    14 |   154 |     3 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------

Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : SYSTEM
  plan_hash      : 150391907
  plan_hash_2    : 3338220364
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
    END_OUTLINE_DATA
  */