Projection removes antijoin constraints

[fabiansinz]

I have a collection stimuli that I wanted to extend. The imagenet.Album tables have a field called collection_id. In the first line, I identify new images that are not yet in the existing set of stimuli. Note that the stimulus.StaticImage.ImageNet tables does not have collection_id.

In the second line I create a selection of new stimuli and directly give them a new collection_id. However, when I do that, datajoint forgets about the antijoin constraints above. When I remove the collection_id=3 call the antijoin constraints are applied correctly.

candidates = stimulus.StaticImage.ImageNet()  - imagenet.Album.Single() - imagenet.Album.Oracle()
selection = candidates.proj('image_class', 'image_id', collection_id='3')```

[eywalker]

Here is a minimally reproducing case:

schema = dj.schema('test')

@schema
class A(dj.Lookup):
    definition = """
    id: int
    """
    contents = zip(range(10))
    
@schema
class B(dj.Lookup):
    definition = """
    id: int
    id2: int
    """
    contents = zip(range(5), range(5, 10))

>>> len(A-B)
5   # correct behavior
>>> len((A - B).proj(id2='3'))
10  # this is a bug!

Looking at the underlying query:
A-B gives

SELECT * FROM `test`.`#a` WHERE ((`id`) not in (SELECT `id` FROM `test`.`#b`))

but (A-B).proj(id2='3') gives

SELECT * FROM (SELECT `id`,3 as `id2` FROM `test`.`#a`) as `_sb` WHERE ((`id`,`id2`) not in (SELECT `id`,`id2` FROM `test`.`#b`))

Note that issue lies in what's in the restriction. In the working version, it restricts by B (which is test.#b) only matching on the shared primary key attribute id. The incorrect behavior results because now the mechanism believes that A and B shares both id and id2. This suggests that the current handling treats the query (A-B).proj(id2='3') the same as A.proj(id2='3')-B which is obviously not equivalent.

[eywalker]

After studying the code, here is an even simpler (and worse) example of failure caused by the same issue:

@schema
class X(dj.Lookup):
    definition = """
    id: int
    """
    contents = zip(range(10))
 
d = dict(id=3, id2=5)

Given these,

>>> len(X & d)
1  # as expected
>>> len((X & d).proj(id2='3')
0 # incorrect result!

[dimitri-yatsenko]

These are two different and somewhat unusual cases.

[eywalker]

The underlying cause is the same - projecting in an attribute that actually exists in the restricting dictionary or expression. It does point out logical error in the handling of projection on a restricted expression

[dimitri-yatsenko]

Yes, this is clearly an urgent bug but adding more subqueries is not a good solution. We are working on updating query processing.

[eywalker]

Whatever the form will be, we will put some form of a workaround by the end of this week. Hopefully we don’t have to put in the subquery workaround, although it will be temporary anyway.