gpt4 book ai didi

sql - Oracle "Select Level from dual"无法按预期使用 to_number 结果

转载 作者:行者123 更新时间:2023-12-03 03:49:48 25 4
gpt4 key购买 nike

为什么

select *
from (
SELECT LEVEL as VAL
FROM DUAL
CONNECT BY LEVEL <= 1000
ORDER BY LEVEL
) n
left outer join (select to_number(trim(alphanumeric_column)) as nr from my_table
where NOT regexp_like (trim(alphanumeric_column),'[^[:digit:]]')) d
on n.VAL = d.nr
where d.nr is null
and n.VAL >= 100

抛出一个 ORA-01722 无效数字(原因是最后一行,n.VAL),而 my_table 中带有数字列的类似版本工作正常:

 select *
from (
SELECT LEVEL as VAL
FROM DUAL
CONNECT BY LEVEL <= 1000
ORDER BY LEVEL
) n
left outer join (select numeric_column as nr from my_table) d
on n.VAL = d.nr
where d.nr is null
and n.VAL >= 100

假定 numeric_column 的类型为 number,而 alphanumeric_column 的类型为 nvarchar_2。请注意,上面的示例无需进行数值比较即可正常工作 (n.VAL >= 100)。

有人知道吗?

最佳答案

这个问题快把我逼疯了。我将问题缩小到更简单的查询

SELECT *
FROM (SELECT TO_NUMBER(TRIM (alphanumeric_column)) AS nr
FROM my_table
WHERE NOT REGEXP_LIKE (TRIM (alphanumeric_column), '[^[:digit:]]')) d
WHERE d.nr > 1

alphanumeric_colum 值为 ('100','200','XXXX');运行上述语句给出了“无效数字”错误。然后,我对查询进行了轻微更改,以使用 CAST 函数而不是 TO_NUMBER:

SELECT *
FROM (SELECT CAST (TRIM (alphanumeric_column) AS NUMBER) AS nr
FROM my_table
WHERE NOT REGEXP_LIKE (TRIM (alphanumeric_column), '[^[:digit:]]')) d
WHERE d.nr > 1

并且正确返回了 - 100, 200。我认为这些函数的行为是相似的。看起来好像 oracle 试图在构造 View 之前评估 d.nr > 1 约束,这是没有意义的。如果有人能解释为什么会发生这种情况,我将不胜感激。请参阅SQLFiddle example

更新:我做了更多的挖掘,因为我不喜欢不知道为什么某些东西会起作用。我对这两个查询运行了 EXPLAIN PLAN 并得到了一些有趣的结果。

对于失败的查询,谓词信息如下所示:

   1 - filter(TO_NUMBER(TRIM("ALPHANUMERIC_COLUMN"))>1 AND  NOT 
REGEXP_LIKE (TRIM("ALPHANUMERIC_COLUMN"),'[^[:digit:]]'))

您会注意到,TO_NUMBER 函数首先在 AND 条件下被调用,然后排除 alpha 值的正则表达式。我想oracle可能会用AND条件进行短路评估,并且由于它首先执行TO_NUMBER,所以它失败了。

但是,当我们使用 CAST 函数时,计算顺序会被交换,并且首先评估正则表达式排除。由于对于 alpha 值来说,它是 false,那么AND 子句的第二部分未计算,查询有效。

   1 - filter( NOT REGEXP_LIKE (TRIM("ALPHANUMERIC_COLUMN"),'[^[:digit:]
]') AND CAST(TRIM("ALPHANUMERIC_COLUMN") AS NUMBER)>1)

Oracle 有时可能很奇怪。

关于sql - Oracle "Select Level from dual"无法按预期使用 to_number 结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21262641/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com