Oracle: ORA-01722: 无效数字

Question

我有一个查询，当我在 sqlplus 中运行它时效果很好:

SQL> SELECT  T_0.ID AS ATTR_1_, T_0_0.ID AS ATTR_2_,  
CASE  WHEN   ( T_0.ID=1 AND  ( T_0_0.ID=3 OR T_0_1.ID='val_1')  )  
THEN  'val_1' ELSE  'val_2' END  AS TXT, T_0_1.ID,  
CASE  WHEN  T_0.ID='boo' THEN  'boo' END  AS EXTRA_FIELD 
FROM TEST_TABLE T_0  
INNER JOIN TEST_TABLE_2 T_0_0  ON  ( T_0_0.ATTR=T_0.ID )  
INNER JOIN TEST_TABLE_3 T_0_1  ON  ( T_0_1.ID = T_0_0.ID )  
WHERE  (  ( T_0.ID=1 AND T_0_0.ID=3 )  
OR T_0_1.ID=2 OR T_0_0.TXT='val_2');

no rows selected

尽管它没有返回任何内容，但它仍然有效并且不会导致错误。但是，当我在 Python 中使用绑定(bind)做同样的事情时，我收到了这条错误消息:

 cx_Oracle.DatabaseError: ORA-01722: invalid number

在执行 cursor.execute 之前，这是我的查询在 Python 中的样子:

SELECT  T_0.ID AS ATTR_1_, T_0_0.ID AS ATTR_2_,  
CASE  WHEN   ( T_0.ID=:TXT_ AND  ( T_0_0.ID=:TXT__ OR T_0_1.ID=:TXT___ )  )  
THEN  :TXT___ ELSE  :TXT____ END  AS TXT, T_0_1.ID,  
CASE  WHEN  T_0.ID=:EXTRA_FIELD THEN  :EXTRA_FIELD END  AS EXTRA_FIELD 
FROM TEST_TABLE T_0  
INNER JOIN TEST_TABLE_2 T_0_0  ON  ( T_0_0.ATTR=T_0.ID )  
INNER JOIN TEST_TABLE_3 T_0_1  ON  ( T_0_1.ID = T_0_0.ID )  
WHERE  (  ( T_0.ID=:ID AND T_0_0.ID=:ID_ )  
OR T_0_1.ID=:ID__ OR T_0_0.TXT=:TXT )

查询只是一个用双引号引起来的字符串 "SELECT ..."。这就是带有绑定(bind)变量的字典的样子:

OrderedDict([('TXT_', 1), ('TXT__', 3), ('TXT___', 'val_1'), 
('TXT____', 'val_2'), ('EXTRA_FIELD', 'boo'), ('ID', 1), 
('ID_', 3), ('ID__', 2), ('TXT', 'val_2')])

因此，如您所见，我有一个完美的字典 - 数字值只是没有引号的数字，字符串值只是带有单引号的字符串。我知道，您会询问表的架构。所以，这里是:

SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE
TABLE_NAME = 'TEST_TABLE';

COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------
ID
NUMBER

SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE
TABLE_NAME = 'TEST_TABLE_2';

COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------
ATTR
NUMBER

ID
NUMBER

TXT
VARCHAR2

SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'TEST_TABLE_3';

COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------
ID
NUMBER

因此，同一个查询似乎在控制台中运行良好，但在使用 Python 时却不起作用。这是为什么？

编辑

这是一个证明 - 两个控制台窗口的屏幕。在第一个控制台中，我在 sqlplus 中运行查询，在第二个控制台中，我打印 sql 查询和用于绑定(bind)变量的字典:

编辑

哦，这更有趣。我能够在 Oracle shell 中重现此错误，它看起来像 Oracle 11c 错误。所以，看看这个:

请注意 ID 字段的类型是 NUMBER。然后注意这两个屏幕:

在上面的屏幕中，您可以看到一切正常。但是，如果我们通过将 OR T_0_1.ID=2 添加到 WHERE 部分来稍微更改它，那么它就会中断:

因此，即使在 Oracle shell 中也可以重现此问题。您可以使用我上面提供的模式来做到这一点。

编辑

我更新了我的问题主题，因为它与 Python 无关。 Oracle 本身的整个问题。

编辑

顺便说一句。我最后的评论与我调查的开始部分并不矛盾。问题是，如果我在 TEST_TABLE_3 中有一些数据，那么查询就会中断。如果我删除数据，则开始工作。这里有一个很大的证明:

数据如何影响查询的正确性？

Answer 1

在最后一个屏幕上，就在您所拥有的声明的最后一行下方

CASE WHEN ( T_O.ID=1 AND ( T_0_0.ID=3 OR T_0_1.ID='VAL_1') )  

有一个星号(现在它有帮助，但有时它可能会导致错误的方向)显示遇到问题的位置

T_0_1.ID='VAL_1' 

在你的表中ID列是Number类型的。 'VAL_1' - 是 Varchar。

如比较规则所述:

When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.

参见(https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF00214)

当 oracle 遇到这个问题时，它会尝试将您的字符串转换为数字 - 而您会得到错误

How can data affect correctness of the query??

当表中没有数据时 - 表中没有返回记录，因此不需要检查列的值是否相等 - 不会执行此比较并且不会显示错误