我发现 DB2 v9.7 和 SQL 有一个有趣的问题LIKE
操作员。看一下这个:
-- this works and returns one record
select 1
from SYSIBM.DUAL
where 'abc' like concat('a', 'bc')
-- this doesn't work
select 1
from SYSIBM.DUAL
where 'abc' like concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))
-- It causes this error (from JDBC):
-- No authorized routine named "LIKE" of type "FUNCTION" having compatible
-- arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.7.85
我已经尝试过长度,似乎只要长度加起来大于4000
。如果我将整个连接字符串“截断”回长度4000
,问题就消失了:
select 1
from SYSIBM.DUAL
where 'abc' like
cast(concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))
as varchar(4000))
有趣的是,这似乎确实与CONCAT
功能。以下也适用:
select 1
from SYSIBM.DUAL
where 'abc' like cast('abc' as varchar(32672))
有人遇到过这样的问题吗?这是 DB2 中的错误吗?或者一些未记录的限制? N.B:我在这里发现了类似的问题:
https://www-304.ibm.com/support/docview.wss?uid=swg1PM18687 https://www-304.ibm.com/support/docview.wss?uid=swg1PM18687
鉴于另一个 IBM 产品在 2010 年为这个问题创建了一个解决方法,我想这并不是一个真正的错误,否则它会同时被修复?