SAP HANA -count(*) giving different outputs

[former_member565459]

select count(*)from(SELECT
A.MATNR ARTICLE,
A.WERKS SITE, 
ROUND((SUM(COALESCE((CASEWHEN A.BWART IN('551','951')THEN(A.DMBTR)END),0))-SUM(COALESCE((CASEWHEN A.BWART IN('552','952')THEN(A.DMBTR)END),0)))) DUMP_VALUE, 
ROUND((SUM(COALESCE((CASEWHEN A.BWART IN('551','951')THEN(A.MENGE)END),0))-SUM(COALESCE((CASEWHEN A.BWART IN('552','952')THEN(A.MENGE)END),0)))) DUMP_QTY                   
FROM MSEG A 
INNERJOIN S_SITE_MASTER B ON A.WERKS=B.STORE_NO         
INNERJOIN S_PRODUCT_MASTER C ON A.MATNR=C.MATERIAL                
WHERE A.BUDAT_MKPF>=ADD_MONTHS(NEXT_DAY(LAST_DAY(ADD_DAYS(CURRENT_DATE,-1))),-1)AND A.BUDAT_MKPF<=ADD_DAYS(CURRENT_DATE,-1)AND A.BWART IN('551','552','951','952')AND C.LEVEL2=10AND B.FORMAT_CD IN('1')GROUPBY A.MATNR,A.WERKS);

Output count is - 129790

IF i just change clause AND B.FORMAT_CD IN ('1') to AND B.FORMAT_CD IN (1), like below -
select count(*) from(
SELECT  
A.MATNR ARTICLE,
A.WERKS SITE, 
ROUND((SUM(COALESCE((CASE WHEN A.BWART IN('551','951') THEN(A.DMBTR) END),0))-SUM(COALESCE((CASE WHEN A.BWART IN('552','952') THEN(A.DMBTR) END),0)))) DUMP_VALUE, 
ROUND((SUM(COALESCE((CASE WHEN A.BWART IN('551','951') THEN(A.MENGE) END),0))-SUM(COALESCE((CASE WHEN A.BWART IN('552','952') THEN(A.MENGE) END),0)))) DUMP_QTY                   
FROM MSEG A 
INNER JOIN S_SITE_MASTER B ON A.WERKS=B.STORE_NO         
INNER JOIN S_PRODUCT_MASTER C ON A.MATNR=C.MATERIAL                
WHERE A.BUDAT_MKPF>=ADD_MONTHS(NEXT_DAY(LAST_DAY(ADD_DAYS(CURRENT_DATE,-1))),-1)
AND A.BUDAT_MKPF<=ADD_DAYS(CURRENT_DATE,-1)                       
AND A.BWART IN ('551','552','951','952')                                       
AND C.LEVEL2=10                                                
AND B.FORMAT_CD IN (1)      
GROUP BY A.MATNR,A.WERKS);

Ouput count - 29403 (which is correct count)


Datatype of B.FORMAT_CD is NVARCHAR(3).


How does count behave differently?

[lbreddemann]

You gave the cause for the effect away with

Datatype of B.FORMAT_CD is NVARCHAR(3).

When you filter the query with a non-matching data type, then HANA will perform an implicit type conversion.

That means, it either converts the parameter of the IN clause into NVARCHAR(3) or the contents of the FORMAT_CD column to INTEGER (that was what happened on my test system).

The filtering is then performed based on the result of the implicit conversion.

You can check on this by using EXPLAIN PLAN.

In the column OPERATOR_DETAILS you should find an entry similar to

FILTER CONDITION: TO_INT(B.FORMAT_CD) = 1

You can even check on how the values of the NVARCHAR column look like after the conversion by selecting TO_INT(FORMAT_CD).

[former_member565459]

Hello Lars,

Yes I had checked the explain plan before. To verify this behavior I tried testing the same on a dummy table. But, i got similar counts for both the filters, back then. Also, I tried replacing the MSEG table used with some other table and applied the same filter, yet the counts match. Can you explain what must be happening exactly ? I'm attaching the screenshot for the query I've mentioned.

[lbreddemann]

Can you show the explain plan instead? The screenshot doesn’t have any new information.

Also, why didn’t you check the converted column values like I’ve indicated?

[former_member565459]

Format_cd in ('1')captur4.png

[former_member565459]

Format_cd in (1)capture5.png

[lbreddemann]

Ok, you posted screenshots where the OPERATOR_DETAILS are not visible (instead the OPERATOR_PROPERTIES are).

As mentioned before, please do check the effects of the type conversion on the single source table.

Break the problem down as far as possible. For example, the join operation is likely not contributing to the issue, as the changed column is not part of the join condition. Therefore, create a test statement without the join and focus on the column FORMAT_CD specifically.

[former_member565459]

Hello Lars, as you said, I tried taking count(*) with the same condition on the base table where the particular Format_cd column resides. However, I got exact same count with both Format_cd in ('1') and Format_cd in (1) filters. As requested, I'm attaching the screenshots for the OPERATOR_DETAILS column. I'll also share with you the execute plan for the same. format-nvarchar.png

[former_member565459]

Format in (1)format-int.png

[lbreddemann]

Ok, so you're saying that the effect does not occur when you filter on just one table.

Also, the converted value filter yields less matches than the non-converted filter.

Both of these effects seem to be wrong. I recommend opening a support incident here for closer analysis.

[former_member565459]

Yes, we have raised a case for the same. Thank you, for your help, Lars. 🙂