Where clause (subquery) is not working as expected

Data Service
1

Hello
I have the following two tables
CampaignsProduct and TasksProducts
TasksProducts has a column called campaign which is 1:1 relationship to CampaignsProduct

I am using this Query on CampaignsProduct (in the Dashboard and in code)

objectId not in (TasksProducts[ownedBy.objectId = ‘FD9A33A2-A4AF-76FA-FFDC-DE2A686D5F00’].campaign.objectid)

Which is intending to give me all the CampaignsProduct where a user does not own a task that points to that campaign.

I am getting empty results! I should be getting at least 4 entries because TasksProducts has only one match in TasksProducts that matches only one campaign

Any idea why this is happening or what I am doing that is wrong. I “think” it used to work before unless I am forgetting something

The API ID is 40A7AC6F-A4BF-02C0-FFBD-1EEC13921300 so you can feel free to use it in there.

2

Hello @snakeeyes

We will investigate the issue and respond to you as soon as possible.

Regards,
Inna

3

Hello @snakeeyes

TasksProducts[ownedBy.objectId = ‘FD9A33A2-A4AF-76FA-FFDC-DE2A686D5F00’]
= TasksProducts with objectId AA09DAD7-D6E0-44AF-98C5-39AB953C8EAD where campaign is empty, that’s why
TasksProducts[ownedBy.objectId = ‘FD9A33A2-A4AF-76FA-FFDC-DE2A686D5F00’].campaign.objectid
is null.
objectId not in (TasksProducts[ownedBy.objectId = ‘FD9A33A2-A4AF-76FA-FFDC-DE2A686D5F00’].campaign.objectid)
is equivalent to
objectId not in (null)
x NOT IN (…) is defined as a series of comparisons between x and each of the values returned by the subquery. Uses three-value logic, for which the three possible values of a logical expression are true , false or unknown . Comparison of a value to a NULL is unknown and if any one of those NOT IN comparisons is unknown then the result is also deemed to be unknown .
That’s why objectId not in (null) return empty list.

To get the result you want, you need to add a null check, this is how the query would look:

objectId not in (TasksProducts[ownedBy.objectId = ‘FD9A33A2-A4AF-76FA-FFDC-DE2A686D5F00’ and campaign is not null].campaign.objectid)

Please check and let us know the result.

1 Like
4

Thank you Vladimir. ooh okay I see. I applied your suggestion and it works. But I have to disagree with the interpretation of logic here. When you say

Comparison of a value to a NULL is unknown and if any one of those NOT IN comparisons is unknown then the result is also deemed to be unknown .
That’s why objectId not in (null) return empty list.

Then this shouldn’t be the case because comparing a value to null to should be true if value is null and false other wise. Null is actually a valid parameter at the end of the day . Infact in a lot of queries we say “AND XYZ is not null” and it works as expected. Why would this specific case be treated differently and generate third possibility outside the typical true/false results? I think the behavior should be consistent.

Thank you very much for suggesting a solution meanwhile

5

Hello @snakeeyes

Yes, AND XYZ is not null and it works as expected, but is not null not equal to not in (null)

6

umm , is not in is basically is not (null) being performed on every element of the list. When the number of elements is one then is not in becomes the same as is not (null)

To convince you lets take Java example

ArrayList list = new ArrayList();
list.add(null);

if(list.get(0) == null) {
println(“true”);
}else{
println(“false”);
}

As you can see the null is in the list and when compared to another value (null) the comparison is true and it will print true.

Anyways :slight_smile: , regardless the main thing is that this is fixed and I have to adapt all my queries now to handle null in the list so I don’t get empty responses. Thank you again