奇怪的隐式转换

xteitxu

困惑很久的问题：
version：11.1.0.7.0 - 64bit   
现象：执行如下sql报ORA-01722 INVALID NUMBER.

SELECT ATTACHID, UPLBY, UPLDT, FILENM, TABNM, KEY1, KEY2, KEY3, KEY4, KEY5, UPLREM, DELBY, DELDT, DELREM
FROM SYT_ATTACHDOC
WHERE TABNM = 'CLAIM'
AND FILENM = 'F.PDF'
AND DELBY IS NULL
AND KEY1 = 30050977

表SYT_ATTACHDOC结构：
ATTACHID                                  NOT NULL NUMBER(18)
UPLBY                                     NOT NULL VARCHAR2(30)
UPLDT                                     NOT NULL DATE
FILENM                                    NOT NULL VARCHAR2(100)
TABNM                                     NOT NULL VARCHAR2(30)
KEY1                                      NOT NULL VARCHAR2(100)
KEY2                                               VARCHAR2(100)
KEY3                                               VARCHAR2(100)
KEY4                                               VARCHAR2(100)
KEY5                                               VARCHAR2(100)
UPLREM                                             VARCHAR2(500)
DELBY                                              VARCHAR2(30)
DELDT                                              DATE
DELREM                                             VARCHAR2(500)


个人想法:
在KEY1进行过滤时，正常会做隐式转换。但为什么会报错呢？加上引号后查询正常。也就是没有做隐式转换。更奇怪的是
FILENM = 'F.PDF' 引号中任意添加、改动大小写则不报错。难道这个跟sql的执行顺序有关系？前边的满足了，就不执行后边的了？

xteitxu

附执行计划：
SQL> SELECT ATTACHID, UPLBY, UPLDT, FILENM, TABNM, KEY1, KEY2, KEY3, KEY4, KEY5, UPLREM, DELBY, DELDT, DELREM
  2  FROM SYT_ATTACHDOC
  3  WHERE TABNM = 'CLAIM'
  4  AND FILENM = 'F.PDF'
  5  AND DELBY IS NULL
  6  AND KEY1 = 30050977
  7  /
ERROR:
ORA-01722: invalid number



no rows selected

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2634608109

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    93 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| SYT_ATTACHDOC      |     1 |    93 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYT_ATTACHDOC_IDX1 |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("KEY1")=30050977 AND "TABNM"='CLAIM' AND "DELBY" IS NULL)
   2 - access("FILENM"='F.PDF')


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
       1067  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

看样子是做了隐式转换。已经做了隐式转换与手工加下引号是一样的，也会报错？

zhm2815

没看明白，到底有什么疑问。
但是根据你写的SQL语句，KEY1 VARCHAR2(100)，在条件里直接用的是数字，这个时候不会进行隐式转换。
如果字段是number类型，条件用的字符，只要能转换就会进行隐式转换。
隐式转换是有条件的。

linou

没有问题,应该不是oracle的BUG
参看
http://docs.oracle.com/cd/B28359 ... sql_elements002.htm
在The following rules govern the direction in which Oracle Database makes implicit datatype conversions:下面第二条
During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.
对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型。正如楼主的例子：
TO_NUMBER("KEY1")=30050977
你的表里面可没有TO_NUMBER("KEY1")这个column

zhm2815

linou，你前面解释是对的。但是“你的表里面可没有TO_NUMBER("KEY1")这个column”，我认为不对。
实际是，oracle想要把表中列值转换成number，但是有部分不能转换导致报错。

linou

我理解你的意思，但是oracle是很死的，没有那么智能
执行计划就是
1-filter(TO_NUMBER("KEY1")=30050977 AND "TABNM"='CLAIM' AND "DELBY" IS NULL)
   2 - access("FILENM"='F.PDF')
他就把(TO_NUMBER("KEY1")=30050977 作为一个条件了不是吗？

xteitxu

to_number 是oracle做了隐式转换。但是key1的列是varchar2.应该to_char的隐式转换才对啊。
还有就是，整条SQL都不用动。只要我把FILENM条件引号里的F.PDF随意修改，比如我改成：f.PDF,这条sql就没有问题。不会报错。当然，修改后不会select出正确的数据。希望继续讨论。

linou

这个挺有意思的
我也试验了一下,不过版本和你的不一样,我是公司的内部测试版本12.1,回家我在11.2上面测试一下.
结果:
SQL> create table test(id number(2), name varchar2(20), key varchar2(20));

表が作成されました。

SQL> insert into test values(1,'F.PDF','123');

1行が作成されました。

SQL> desc test
名前                                    NULL?    型
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(2)
NAME                                               VARCHAR2(20)
KEY                                                VARCHAR2(20)

SQL> select * from test;

        ID NAME                 KEY
---------- -------------------- --------------------
         1 F.PDF                123

SQL> select * from test where id=1 and name='F.PDF' and key=123;

        ID NAME                 KEY
---------- -------------------- --------------------
         1 F.PDF                123


SQL> select * from test where key=123;

        ID NAME                 KEY
---------- -------------------- --------------------
         1 F.PDF                123

SQL> select * from test where key='123';

        ID NAME                 KEY
---------- -------------------- --------------------
         1 F.PDF                123


実行計画
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    37 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("KEY")=123)

Note
-----
   - dynamic sampling used for this statement (level=2)


統計
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        700  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


一样的,可是结果不一样.....回去再研究了哈