opt: constant value projections prevent join reordering

**Describe the problem**

We observe a query execution plan that is not optimal from our pov, since it's possible to restrict the subquery's rows by using the outer query's id.

**To Reproduce**

**Expected behavior**

Query optimizer doesn't do FULL SCANs for subquery (but rather uses filter from outer query).

**Additional data / screenshots**

```
explain
select workordere0_.id as col_0_0_,
workordere0_.actual_end as col_1_0_,
workordere0_.actual_quantity as col_2_0_,
workordere0_.actual_start as col_3_0_,
workordere0_.code as col_4_0_,
workordere0_.comment as col_5_0_,
consumedma2_.id as col_6_0_,
consumedma2_.identifier as col_7_0_,
consumedma2_.manufacturer_id as col_8_0_,
partnerent3_.code as col_9_0_,
partnerent3_.name as col_10_0_,
roles4_.roles as col_11_0_,
consumedma2_.product_id as col_12_0_,
productent5_.bill_of_materials_id as col_13_0_,
productent5_.code as col_14_0_,
productent5_.customer_drawing_code as col_15_0_,
productent5_.description as col_16_0_,
productent5_.drawing_code as col_17_0_,
consumedma2_.work_order_id as col_18_0_,
workordere0_.description as col_19_0_,
workordere0_.location_id as col_20_0_,
workordere0_.manufacturing_order_id as col_21_0_,
manufactur1_.comment as col_22_0_,
manufactur1_.partner_id as col_23_0_,
partnerent6_.code as col_24_0_,
partnerent6_.name as col_25_0_,
roles7_.roles as col_26_0_,
manufactur1_.customer_drawing_revision as col_27_0_,
manufactur1_.drawing_revision as col_28_0_,
(select json_agg(json_build_object('f0', '' || material11_.id, 'f1', '' || material11_.availability_date, 'f2',
'' || material11_.available_quantity, 'f3', '' || material11_.comment, 'f4',
'' || material11_.dimensions, 'f5', '' || material11_.number, 'f6',
'' || material11_.product_id, 'f7', '' || productent12_.bill_of_materials_id,
'f8', '' || productent12_.code, 'f9',
'' || productent12_.customer_drawing_code, 'f10',
'' || productent12_.description, 'f11', '' || productent12_.drawing_code,
'f12', '' || material11_.quantity, 'f13', '' || material11_.specification))
from manufacturing_order manufactur10_
inner join material material11_ on manufactur10_.id = material11_.manufacturing_order_id
left outer join product productent12_ on material11_.product_id = productent12_.id
where workordere0_.manufacturing_order_id = manufactur10_.id) as col_29_0_,
manufactur1_.material_availability_date as col_30_0_,
manufactur1_.material_availability_status as col_31_0_,
manufactur1_.number as col_33_0_,
manufactur1_.planned_end_date as col_34_0_,
manufactur1_.priority as col_35_0_,
manufactur1_.product_id as col_36_0_,
productent8_.bill_of_materials_id as col_37_0_,
productent8_.code as col_38_0_,
productent8_.customer_drawing_code as col_39_0_,
productent8_.description as col_40_0_,
productent8_.drawing_code as col_41_0_,
manufactur1_.quantity as col_42_0_,
manufactur1_.reference_number as col_43_0_,
manufactur1_.sale_order_id as col_44_0_,
saleordere9_.number as col_45_0_,
manufactur1_.status as col_46_0_,
workordere0_.number as col_48_0_,
workordere0_.operation_time as col_49_0_,
workordere0_.parallelism as col_50_0_,
workordere0_.pinned as col_51_0_,
workordere0_.planned_end as col_52_0_,
workordere0_.planned_quantity as col_53_0_,
workordere0_.planned_start as col_54_0_,
workordere0_.scheduled_end as col_56_0_,
workordere0_.scheduled_start as col_57_0_,
workordere0_.sequence as col_58_0_,
workordere0_.setup_time as col_59_0_,
workordere0_.status as col_60_0_,
workordere0_.take_over as col_62_0_,
workordere0_.task_id as col_63_0_,
workordere0_.title as col_65_0_,
workordere0_.wait_time_after as col_66_0_
from work_order workordere0_
left outer join manufacturing_order manufactur1_ on workordere0_.manufacturing_order_id = manufactur1_.id
left outer join material_consumption consumedma2_ on manufactur1_.id = consumedma2_.manufacturing_order_id
left outer join partner partnerent3_ on consumedma2_.manufacturer_id = partnerent3_.id
left outer join partner_roles roles4_ on partnerent3_.id = roles4_.partner_id
left outer join product productent5_ on consumedma2_.product_id = productent5_.id
left outer join partner partnerent6_ on manufactur1_.partner_id = partnerent6_.id
left outer join partner_roles roles7_ on partnerent6_.id = roles7_.partner_id
left outer join product productent8_ on manufactur1_.product_id = productent8_.id
left outer join sale_order saleordere9_ on manufactur1_.sale_order_id = saleordere9_.id
where (workordere0_.location_id in ('d1c7579a-14ba-4e9c-9a84-55fd00c070ee'))
and (workordere0_.status in ('READY', 'STARTED', 'FINISHED', 'PAUSED'))
```

Results in the following plan:

```
distribution: local
vectorized: true

• render
│
└── • group (hash)
│ estimated row count: 496
│ group by: rownum
│
└── • hash join (right outer)
│ estimated row count: 3,983
│ equality: (id) = (manufacturing_order_id)
│
├── • render
│ │
│ └── • hash join
│ │ estimated row count: 99,398
│ │ equality: (manufacturing_order_id) = (id)
│ │ right cols are key
│ │
│ ├── • merge join (left outer)
│ │ │ estimated row count: 101,946
│ │ │ equality: (product_id) = (id)
│ │ │ right cols are key
│ │ │
│ │ ├── • scan
│ │ │ estimated row count: 101,946 (100% of the table; stats collected 2 hours ago; using stats forecast for 3 hours in the future)
│ │ │ table: material@material_product_id_idx
│ │ │ spans: FULL SCAN
│ │ │
│ │ └── • scan
│ │ estimated row count: 1,818 (100% of the table; stats collected 7 hours ago)
│ │ table: product@pk_product
│ │ spans: FULL SCAN
│ │

*** what's the point of doing a FULL SCAN here? We have the WHERE clause workordere0_.manufacturing_order_id = manufactur10_.id ***

│ └── • scan
│ estimated row count: 12,367 (100% of the table; stats collected 3 hours ago)
│ table: manufacturing_order@idx_771633f1154a4481517988b30
│ spans: FULL SCAN
│
└── • ordinality
│ estimated row count: 496
│
└── • hash join (left outer)
│ estimated row count: 496
│ equality: (sale_order_id) = (id)
│ right cols are key
│
├── • hash join (left outer)
│ │ estimated row count: 496
│ │ equality: (id) = (partner_id)
│ │
│ ├── • hash join (left outer)
│ │ │ estimated row count: 496
│ │ │ equality: (id) = (manufacturing_order_id)
│ │ │
│ │ ├── • hash join (left outer)
│ │ │ │ estimated row count: 496
│ │ │ │ equality: (partner_id) = (id)
│ │ │ │
│ │ │ ├── • hash join (right outer)
│ │ │ │ │ estimated row count: 496
│ │ │ │ │ equality: (id) = (product_id)
│ │ │ │ │ left cols are key
│ │ │ │ │
│ │ │ │ ├── • scan
│ │ │ │ │ estimated row count: 1,818 (100% of the table; stats collected 7 hours ago)
│ │ │ │ │ table: product@pk_product
│ │ │ │ │ spans: FULL SCAN
│ │ │ │ │
│ │ │ │ └── • lookup join (left outer)
│ │ │ │ │ estimated row count: 496
│ │ │ │ │ table: manufacturing_order@pk_manufacturingorder
│ │ │ │ │ equality: (manufacturing_order_id) = (id)
│ │ │ │ │ equality cols are key
│ │ │ │ │
│ │ │ │ └── • index join
│ │ │ │ │ estimated row count: 496
│ │ │ │ │ table: work_order@pk_workorder
│ │ │ │ │
│ │ │ │ └── • scan
│ │ │ │ estimated row count: 95 (0.19% of the table; stats collected 3 hours ago)
│ │ │ │ table: work_order@idx_ec04181182785271d9052715d
│ │ │ │ spans: [/'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'FINISHED' - /'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'FINISHED'] [/'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'PAUSED' - /'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'PAUSED'] [/'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'READY' - /'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'READY'] [/'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'STARTED' - /'d1c7579a-14ba-4e9c-9a84-55fd00c070ee'/'STARTED']
│ │ │ │
│ │ │ └── • merge join (left outer)
│ │ │ │ estimated row count: 15
│ │ │ │ equality: (id) = (partner_id)
│ │ │ │ left cols are key
│ │ │ │
│ │ │ ├── • scan
│ │ │ │ estimated row count: 15 (100% of the table; stats collected 2 hours ago; using stats forecast for 2 days in the future)
│ │ │ │ table: partner@pk_partner
│ │ │ │ spans: FULL SCAN
│ │ │ │
│ │ │ └── • scan
│ │ │ estimated row count: 15 (100% of the table; stats collected 2 hours ago; using stats forecast for 2 days in the future)
│ │ │ table: partner_roles@idx_f2cfa9c609734ac09afa2d6c04e1a717
│ │ │ spans: FULL SCAN
│ │ │
│ │ └── • lookup join (left outer)
│ │ │ estimated row count: 1
│ │ │ table: partner@pk_partner
│ │ │ equality: (manufacturer_id) = (id)
│ │ │ equality cols are key
│ │ │
│ │ └── • lookup join (left outer)
│ │ │ estimated row count: 1
│ │ │ table: product@pk_product
│ │ │ equality: (product_id) = (id)
│ │ │ equality cols are key
│ │ │
│ │ └── • scan
│ │ estimated row count: 1 (100% of the table; stats collected 17 hours ago; using stats forecast for 17 hours ago)
│ │ table: material_consumption@pk_materialconsumption
│ │ spans: FULL SCAN
│ │
│ └── • scan
│ estimated row count: 15 (100% of the table; stats collected 2 hours ago; using stats forecast for 2 days in the future)
│ table: partner_roles@partner_roles_pkey
│ spans: FULL SCAN
│
└── • scan
estimated row count: 173 (100% of the table; stats collected 1 day ago)
table: sale_order@pk_saleorder
spans: FULL SCAN
```

**Environment:**
- CockroachDB version 23.1.4
- Server OS: Debian Linux

**Additional context**

Very slow queries for a little # of rows.

Jira issue: CRDB-37697