Affects Version/s: 4.14.0
Fix Version/s: 4.14.0
If upsert select has a where condition that is using any comparison operator (including ANY/SOME/etc), the whole WHERE clause just ignored. Table:
Query that should not upsert anything because we have a condition in where that I1 should be greater than any value we already have as well as not existing ID:
Now with ANY. Should not upsert anything as well because ID is [1,2], while I1 are all '4':
A similar query with IN works just fine:
The reason for this behavior is that for IN we convert subselect to semi-join and execute upsert on the client side. For comparisons, we don't perform any transformations and query is considered flat and finally executed on the server side. Not sure why, but we also completely ignore the second condition in WHERE clause as well and that may lead to a serious data loss.
James Taylor, Maryann Xue any thoughts or suggestions how to fix that are really appreciated.