SAP IQ - CAST and CONVERT strange behavior in JOIN condition

[stefano_bolli]

Hi all,

I noticed a strange (and potentially dangerous) behavior using CAST and CONVERT function in JOIN condition.

I have a JOIN statement that includes an equal comparison (=) between a varchar(40) field and an int column of another table.

The strange thing is that if I don't specify the varchar dimension (in my case 40) the comparison results valid for a bigger set of records respect to the ones that are effectively equals.

No errors are reported and I checked also iqlog and iqmsg files, but no more infos and no stacktraces are available.

If I put the varhcar dimension (eg. 40) or if I invert the JOIN converting VARCHAR to INT all works fine.

Following I report a complete example.

The IQ version is:

select @@version
-- SAP IQ/16.0.0.809/150721/P/sp08.35/MS/Windows 2003/64bit/2015-07-21 14:57:13

I found this archived answer, but it refers only to CONVERT <format> missing parameters changed behaviour in IQ 16 which is different from my case. https://archive.sap.com/discussions/thread/3568750

******** ERROR EXAMPLE ********
CREATE TABLE if not exists TMP_TABLE1 (     
		ID int NOT NULL
)

Definition of FACT_TABLE
- CODE 		varchar(12)
- ID_VCHAR 	varchar(40)
- LOCAL_ID 	varchar(24)
- NUMBER	int(4) 
- DATE 		datetime

declare
    @from datetime =null,    
    @to datetime =null


set @from ='2015-03-20' 
set @to ='2016-11-21'
truncate table TMP_RESULT


INSERT INTO TMP_RESULT (CODE, ID_VCHAR, LOCAL_ID, NUMBER)
SELECT MAX(CODE), F.ID_VCHAR, F.LOCAL_ID, SUM(NUMBER)
FROM FACT_TABLE F
-- KO INNER JOIN TMP_TABLE1 N ON F.ID_VCHAR = cast(N.ID as varchar)
-- KO INNER JOIN TMP_TABLE1 N ON F.ID_VCHAR = convert(varchar, N.ID)
-- OK INNER JOIN TMP_TABLE1 N ON convert(int, F.ID_VCHAR) = N.ID
-- OK INNER JOIN TMP_TABLE1 N ON convert(varchar(40), N.ID) = F.ID_VCHAR
-- OK (following) 
INNER JOIN TMP_TABLE1 N ON cast(N.ID as varchar(40)) = F.ID_VCHAR
WHERE F.DATE between @from AND @to     
AND F.LOCAL_ID in (SELECT a.LOCAL_ID FROM TMP_LOCAL a)
GROUP BY F.ID_VCHAR, F.LOCAL_ID

Is this behavior by design or is it a bug? Are available some hotfixes for my version?

Best regards,

Stefano

[former_member194571]

Stefano,

can you provide sample values for [N.]ID where one join condition matches and the other doesn't?

Best regards

Volker