Difference between 12.70SR2 and 12.80SR1 when using "DROP FIELD" in Joins - Multiplication of data!

[sebastianQlik]

Hello,

After upgrading from 12.70 SR2 to 12.80 SR1, some applications show different values (Server and Desktop) compared to 12.70 SR2 when using "DROP Field" command!

Example:

table1.qvd:

%Key	Field1	Field2	Field3
1001	1000	2000	0005

table2.qvd:

%Key	FieldAA	FieldBB	FieldCC	FieldDD
1001	AA	BB	CC	DD
1001	AA	BB	CC	DD
1001	AA	BB	CC	DD
1001	AA	BB	CC	DD

Script: 

tab1:

LOAD DISTINCT 

      %Key, 

     Field1, 

     Field2, 

     Field3

FROM

[table1.qvd]

(qvd);

Drop Field Field3;

LEFT JOIN (tab1)

LOAD

      %Key, 

     FieldAA, 

     FieldBB, 

     FieldCC, 

     FieldDD

FROM

[table2.qvd]

(qvd);

Result in 12.70SR2:

Result in 12.80SR1:

If the "DROP Field" command is commented out, the result is identical in both versions. 

I have attached the example.

What has changed? Are you aware of anything?

Is there a possibility (setting etc.?) to get the result of 12.70 also in 12.80?

regards, Sebastian

[Daniele_Purrone]

Hi @sebastianQlik ,
I cannot find anything about this.
I see that you have logged a support case on this behaviour, we'll investigate it and report the outcome here as well.

[peterwh · Accepted solution]

Hi @sebastianQlik, 

I've found the same problem and created a bug report. To avoid this problem I got the information I have to insert

RemoveDistinctOnDropField=0 

into my settings.ini. This solved it for my use case.

Unfortunately this information was not posted before in this thread.

Another workaround I found was to use "DISTINCT" in every LOAD statement which causes a multiplication of the output table.

This is the reason I don't want to use this setting as default.

Kind regards

Peter

[Daniele_Purrone]

Yes @peterwh , thanks for mentioning that.

There was indeed a change of behaviour between May 2022 and 2023, and there is a reference on the help site, where we added the line "The "distinct" property of a table is removed after a drop field statement."

Unfortunately, for some reason, this was overlooked, and not documented further in the release notes nor a support article.

The setting RemoveDistinctOnDropField=0 will revert to the old behaviour.