FIX: sp_MSexpandnotbelongs May Be Called at Publisher Due to Invalid Evaluation of Join Filter (300180)

CAUSE

When setting up merge replication, join filters can be specified for various reasons, such as minimizing the amount of data sent over the network, reducing the amount of storage space required at the subscriber, and many others. The stored procedures sp_MSsetupbelongs, sp_MSbelongs, sp_MSexpandbelongs, and sp_MSexpandnotbelongs are used to determine the set of rows that need to be published to the subscriber based on the filter specified. These filters can also be made dynamic by specifying a system function or a user-defined function that is evaluated differently for each subscriber. This also improves performance because the subscriber receives only the information needed based on the connection properties of the merge agent for the subscription.

To determine whether a filter is dynamic, a check is made internally using a LIKE clause that looks for patterns. However, under certain conditions, there may be columns with names like USERID, USER_ID, and so on, that are used in the filter. In this case, the check incorrectly evaluates the filter to be dynamic, interpreting USERID or USER_ID column as a USER_ID() function. This leads to an expansion of deletes by sp_MSexpandnotbelongs at the publisher. Expansion of deletes is normally done at the subscriber, but with functions in join filters it is done at the publisher, because those functions are supposed to be evaluated in the context of the publisher.