Skip to content

incorrect motion policy for inner join

Consider tables:

t1:
a1 b1 c1
 1, 1, 1,
 2, 1, 2,
 3, 2, 3,
 4, 2, 3

t2: 
a2 b2 c2
 1, 2, 1,
 2, 2, 2,
 3, 1, 1,
 4, 1, 1

The sharding keys are: a1 for t1, a2 for t2.

select "a1", "a2" from "t1" inner join "t2" 
on "t1"."a1" = "t2"."b2"

Gives: (2, 2)

sbroad.execute([[select "a1", "a2" from "t1" inner join "t2" on "t1"."a1" = "t2"."b2"]], {})
---
- {'metadata': [{'name': 't1.a1', 'type': 'integer'}, {'name': 't2.a2', 'type': 'integer'}],
  'rows': [[2, 2]]}
...

Expected: (a1, a2): (1, 1), (1, 2), (2, 3), (2, 4)

The problem is in motion distribution for right join child, it must be Full, not Segment

sbroad.execute([[explain select "a1", "a2" from "t1" inner join "t2" on "t1"."a1" = "t2"."b2"]], {})
---
- ['projection ("t1"."a1" -> "a1", "t2"."a2" -> "a2")', 
- '    join on ROW("t1"."a1") = ROW("t2"."b2")', 
- '        scan "t1"', 
- '            projection ("t1"."a1" -> "a1", "t1"."b1" -> "b1", "t1"."c1" -> "c1")',
- '                scan "t1"', 
- '        motion[policy: segment([ref("a2")]), generation: none]', 
- '            scan "t2"', 
- '                projection ("t2"."a2" -> "a2", "t2"."b2" -> "b2", "t2"."c2" -> "c2")', 
- '                    scan "t2"']
...
Edited by Arseniy Volynets