exec-ddl
CREATE TABLE p (
  r INT,
  id INT,
  PRIMARY KEY (r, id),
  UNIQUE WITHOUT INDEX (id)
)
----

exec-ddl
CREATE TABLE c (
  r INT,
  p_id INT,
  id INT,
  PRIMARY KEY (r, p_id, id),
  UNIQUE INDEX c_p_id_id_key (p_id, id),
  FOREIGN KEY (r, p_id) REFERENCES p (r, id)
)
----

opt
SELECT *
FROM p LEFT LOOKUP JOIN c@c_p_id_id_key
ON c.p_id = p.id AND c.id = 1234
----
left-join (lookup c@c_p_id_id_key)
 ├── columns: r:1(int!null) id:2(int!null) r:5(int) p_id:6(int) id:7(int)
 ├── flags: force lookup join (into right side)
 ├── key columns: [2 10] = [6 7]
 ├── lookup columns are key
 ├── stats: [rows=1000, distinct(6)=9.56179, null(6)=990]
 ├── key: (2)
 ├── fd: (2)-->(1,5-7), (6)-->(5)
 ├── project
 │    ├── columns: "lookup_join_const_col_@7":10(int!null) p.r:1(int!null) p.id:2(int!null)
 │    ├── stats: [rows=1000, distinct(1)=100, null(1)=0, distinct(2)=1000, null(2)=0, distinct(10)=1, null(10)=0]
 │    ├── key: (2)
 │    ├── fd: ()-->(10), (2)-->(1)
 │    ├── scan p
 │    │    ├── columns: p.r:1(int!null) p.id:2(int!null)
 │    │    ├── stats: [rows=1000, distinct(1)=100, null(1)=0, distinct(2)=1000, null(2)=0]
 │    │    ├── key: (2)
 │    │    └── fd: (2)-->(1)
 │    └── projections
 │         └── 1234 [as="lookup_join_const_col_@7":10, type=int]
 └── filters
      └── c.r:5 = p.r:1 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]