"Field is not on schema" error during subquery unnesting

[max-hoffman]

When scope unnesting creates name collisions, we have to re-alias and fixup name references in expressions.

TODO find simple repro case that we can check-in.

Example failure:

CREATE TABLE `dcim_site` (
  `id` char(32) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created` date,
  `last_updated` datetime,
  `_custom_field_data` json NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `_name` varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `slug` varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `facility` varchar(50) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `asn` bigint,
  `time_zone` varchar(63) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `description` varchar(200) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `physical_address` varchar(200) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `shipping_address` varchar(200) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `latitude` decimal(8,6),
  `longitude` decimal(9,6),
  `contact_name` varchar(50) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `contact_phone` varchar(20) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `contact_email` varchar(254) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `comments` longtext COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `region_id` char(32) COLLATE utf8mb4_0900_ai_ci,
  `status_id` char(32) COLLATE utf8mb4_0900_ai_ci,
  `tenant_id` char(32) COLLATE utf8mb4_0900_ai_ci,
  PRIMARY KEY (`id`),
  KEY `dcim_site__name_6144ca75` (`_name`),
  KEY `dcim_site_status_id_e6a50f56` (`status_id`),
  UNIQUE KEY `name` (`name`),
  KEY `region_id` (`region_id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `tenant_id` (`tenant_id`)
);
CREATE TABLE `dcim_powerpanel` (
  `id` char(32) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created` date,
  `last_updated` datetime,
  `_custom_field_data` json NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `rack_group_id` char(32) COLLATE utf8mb4_0900_ai_ci,
  `site_id` char(32) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `location_id` char(32) COLLATE utf8mb4_0900_ai_ci,
  PRIMARY KEY (`id`),
  KEY `dcim_powerpanel_name_09946067` (`name`),
  UNIQUE KEY `dcim_powerpanel_site_id_name_804df4c0_uniq` (`site_id`,`name`),
  KEY `location_id` (`location_id`),
  KEY `rack_group_id` (`rack_group_id`),
  KEY `site_id` (`site_id`)
);
SELECT `dcim_site`.`id` FROM `dcim_site` WHERE NOT EXISTS ( SELECT (1) AS `a` FROM `dcim_powerpanel` U1 WHERE U1.`name` = 'test-power-panel1' AND U1.`site_id` = `dcim_site`.`id` LIMIT 1 ) AND NOT EXISTS ( SELECT (1) AS `a` FROM `dcim_powerpanel` U1 WHERE U1.`name` = 'test-power-panel2' AND U1.`site_id` = `dcim_site`.`id` LIMIT 1 ) ORDER BY `dcim_site`.`_name` ASC;
failed to replan join: field "name" is not on schema

bad plan:

Project
 ├─ columns: [dcim_site.id:0!null]
 └─ Sort(dcim_site._name:5!null ASC nullsFirst)
     └─ AntiJoin
         ├─ Eq
         │   ├─ u1_1.site_id:23!null
         │   └─ dcim_site.id:0!null
         ├─ AntiJoin
         │   ├─ Eq
         │   │   ├─ U1.site_id:23!null
         │   │   └─ dcim_site.id:0!null
         │   ├─ Table
         │   │   ├─ name: dcim_site
         │   │   └─ columns: [id created last_updated _custom_field_data name _name slug facility asn time_zone description physical_address shipping_address latitude longitude contact_name contact_phone contact_email comments region_id status_id tenant_id]
         │   └─ Filter
         │       ├─ Eq
         │       │   ├─ U1.name:22!null
         │       │   └─ test-power-panel1 (longtext)
         │       └─ TableAlias(U1)
         │           └─ Table
         │               ├─ name: dcim_powerpanel
         │               └─ columns: [name site_id]
         └─ Filter
             ├─ Eq
-             │   ├─ U1.name:22!null
+             │   ├─ u1_1.name:22!null
             │   └─ test-power-panel2 (longtext)
             └─ TableAlias(u1_1)
                 └─ Table
                     ├─ name: dcim_powerpanel
                     └─ columns: [name site_id]

Code fix:

diff --git a/sql/analyzer/hoist_select_exists.go b/sql/analyzer/hoist_select_exists.go
index 8d7b9e60a..29d2a8f34 100644
--- a/sql/analyzer/hoist_select_exists.go
+++ b/sql/analyzer/hoist_select_exists.go
@@ -312,6 +312,11 @@ func decorrelateOuterCols(e *plan.Subquery, scopeLen int, aliasDisambig *aliasDi
                                return nil, err
                        }

+                       filtersToKeep, err = renameAliasesInExpressions(filtersToKeep, conflict, newAlias)
+                       if err != nil {
+                               return nil, err
+                       }