AssertionError in using UNION and JOIN - DataJoint 0.13.* error

[ttngu207]

Bug Report

Description

Performing a JOIN operation on a UNION results in AssertionError in DataJoint 0.13.* - this is OK in 0.12.*
May be related to #926

Reproducibility

Include:

• OS (WIN)
• Python Version 3.7
• MySQL Version 5.7
• MySQL Deployment Strategy (local-native)
• DataJoint Version 0.13.2

Example case to reproduce the issue:

import datajoint as dj

schema = dj.schema('test')


@schema
class TableA(dj.Manual):
    definition = """
    table_a: int
    """


TableA.insert(zip([1, 2, 3, 4, 5, 6]))


@schema
class TableB(dj.Manual):
    definition = """
    -> TableA
    table_b: int
    """


TableB.insert([(1, 11), (2, 22), (3, 33), (4, 44)])


q1 = TableB & 'table_a < 3'
q2 = TableB & 'table_a > 3'

(q1 + q2) * TableA

Error stack:

Traceback (most recent call last):
  File "C:\ProgramData\Miniconda3\envs\workflow-ephys\lib\site-packages\IPython\core\interactiveshell.py", line 3437, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-13-e158f40fe65d>", line 1, in <module>
    (q1 + q2) * TableA
  File "C:\ProgramData\Miniconda3\envs\workflow-ephys\lib\site-packages\datajoint\expression.py", line 235, in __mul__
    return self.join(other)
  File "C:\ProgramData\Miniconda3\envs\workflow-ephys\lib\site-packages\datajoint\expression.py", line 284, in join
    assert len(result.support) == len(result._left) + 1
AssertionError

In DataJoint 0.12.7, this yields the results as expected:

(q1 + q2) * TableA
Out[6]: 
*table_a    *table_b   
+---------+ +---------+
1           11         
2           22         
4           44         
 (Total: 3)

[jverswijver]

I am not quite sure why you would join (q1 + q2) and TableA.
(q1 + q2) already produces the result:

*table_a    *table_b   
+---------+ +---------+
1           11         
2           22         
4           44         
 (Total: 3)

Are you sure you should be able to do this? There may be a reason it is throwing an assertion error since (q1 + q2) already produces the result you are looking for. I would appreciate some clarification as I am looking into this @ttngu207.

[ttngu207]

Hi @jverswijver , thanks for taking on this.
Two points:

1. that same query works in 0.12., so throwing the assertion error essentially breaks all 0.12. code using similar queries
2. If TableA does have other non-primary attribute, then (q1 + q2) * TableA would not be the same as q1 + q2, instead, yielding more information

[jverswijver]

posting the make sqls between the different datajoint releases using the example given

(q1 + q2).make_sql() #0.13.2

(SELECT DISTINCT `table_a`,`table_b` FROM `test`.`table_b` WHERE(table_a < 3)) 
UNION 
(SELECT DISTINCT `table_a`,`table_b` FROM `test`.`table_b` WHERE(table_a > 3))

((q1 + q2) * TableA).make_sql() #0.13.2

SELECT DISTINCT `table_a`,`table_b` 
FROM (
    SELECT DISTINCT `table_a`,`table_b` 
        FROM `test`.`table_b` 
        WHERE(table_a < 3)) as `$8` 
NATURAL JOIN (
    SELECT DISTINCT `table_a`,`table_b` FROM `test`.`table_b` 
    WHERE(table_a > 3)
    ) as `$9`

(q1 + q2).make_sql() #0.12.9

SELECT `table_a`,`table_b` 
FROM (
    SELECT `table_a`,`table_b` 
    FROM `test`.`table_b` WHERE (table_a < 3) 
    UNION 
    SELECT `table_a`,`table_b` 
    FROM `test`.`table_b` 
    WHERE (table_a > 3)
    ) as `_u4`

((q1 + q2) * TableA).make_sql() #12.9

SELECT `table_a`,`table_b` 
FROM (
    SELECT `table_a`,`table_b` 
    FROM `test`.`table_b` 
    WHERE (table_a < 3) 
    UNION 
    SELECT `table_a`,`table_b` 
    FROM `test`.`table_b` 
    WHERE (table_a > 3)
    ) as `_u5` 
NATURAL JOIN `test`.`table_a`

In 0.13.2 we somehow lose the union when joining a union and a table. I had to comment out the assertion to get the make_sql in 0.13.2