Strange length restriction with the DB2 LIKE operator

Question

I found a funny issue with DB2 v9.7 and the SQL LIKE operator. Check this out:

-- 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

I've played around with the lengths and it seems that the problem appears as soon as lengths add up to be larger than 4000. If I "truncate" the whole concatenated string back to length 4000, the problem disappears:

select 1 
from SYSIBM.DUAL
where 'abc' like 
  cast(concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))
  as varchar(4000))

Interestingly, it really seems to be related to the CONCAT function. The following works as well:

select 1 
from SYSIBM.DUAL
where 'abc' like cast('abc' as varchar(32672))

Has anyone experienced such an issue? Is it a bug in DB2? Or some undocumented restriction? N.B: I found a similar issue here:

https://www-304.ibm.com/support/docview.wss?uid=swg1PM18687

Given that another IBM product creates a workaround for this issue in 2010, I guess it's not really a bug, otherwise it would have been fixed in the mean time?

Answer 1

The real Aha! is here.

Firstly, According to the rules for the concatenation operator result type, concatenating two VARCHARs with the combined length of 4000 bytes or less produces a VARCHAR of that combined length, e.g. concat(varchar(2000), varchar(2000)) = varchar(4000). Concatenating two VARCHARs with the combined length of 4001 bytes or more produces a LONG VARCHAR with the length of 32 700. Though the LONG VARCHAR type is deprecated, I guess the concatenation behaviour still uses the legacy logic.

$ db2 describe "values concat(cast('a' as varchar(2000)), cast('bc' as varchar(2000)))"

Column Information

Number of columns: 1

SQL type              Type length  Column name                     Name length
--------------------  -----------  ------------------------------  -----------
448   VARCHAR                4000  1                                         1


$ db2 describe "values concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))"

Column Information

Number of columns: 1

SQL type              Type length  Column name                     Name length
--------------------  -----------  ------------------------------  -----------
456   LONG VARCHAR          32700  1     

Secondly, the LIKE predicate expects the pattern expression to be a VARCHAR with the maximum length of 32672 bytes.

Subsequently, when you unknowingly attempt to use a LONG VARCHAR as the pattern expression, you get an error. It's not so much about the length of the operand as it is about its data type. The following should work:

select 1 
from SYSIBM.DUAL
where 'abc' like 
   cast(concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))
   as varchar(32672))