MySQL Compatibility: JOIN column reference remapping error in nested correlated subqueries

## Description

MatrixOne encounters a query optimizer error when processing nested correlated subqueries in SELECT clauses. The error occurs when a correlated subquery contains an IN subquery, causing the query optimizer to fail in mapping column references during JOIN operations.

## Error Message

```
ERROR 1064 (HY000) at line 1: SQL parser error: remapInfo step 0 nodeId 11 nodeType JOIN tip OnList colRefCnt:[[12 0] : 0][[18 1] : 0][[18 3] : 0][[3 0] : 1][[3 1] : 1][[3 2] : 1][[1 0] : 1][[2 0] : 1][[6 21] : 0][[7 0] : 0][[20 0] : 0][[8 0] : 2][[20 1] : 0][[12 21] : 0]colRefBool:[[0 1] : true][[0 2] : true][[0 0] : true]sinkColRef:ColRefRemapping{globalToLocallocalToGlobal}empty ColRefRemapping srcExprIdx 0 ; can't find column [6 21] in context's map { [1 0], [2 0], [7 0], [8 0] }
```

## Affected Cases

This issue affects **2 test cases** from our nested subquery compatibility testing on the `industry_radar_test` database.

## Related Table DDL

### Table: company

```sql
CREATE TABLE `company` (
`id` int NOT NULL,
`full_name` varchar(200) DEFAULT NULL,
`short_name` varchar(128) DEFAULT NULL,
`update_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最后修改时间',
`address` varchar(255) DEFAULT NULL,
`board_id` int DEFAULT NULL,
`business_scope` text DEFAULT NULL,
`city` varchar(32) DEFAULT NULL,
`company_profile` text DEFAULT NULL,
`company_type_tags` varchar(255) DEFAULT NULL,
`country` varchar(20) DEFAULT NULL,
`directory_label` varchar(512) DEFAULT NULL,
`district` varchar(32) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`english_name` varchar(255) DEFAULT NULL,
`enterprises_type` varchar(50) DEFAULT NULL,
`founded_time` datetime(6) DEFAULT NULL,
`industry` varchar(32) DEFAULT NULL,
`industry_paqu` varchar(1000) DEFAULT NULL,
`insured_num` varchar(32) DEFAULT NULL,
`product_label` varchar(512) DEFAULT NULL,
`province` varchar(32) DEFAULT NULL,
`registered_capital` varchar(50) DEFAULT NULL,
`representative` varchar(32) DEFAULT NULL,
`taxpayer_num` varchar(50) DEFAULT NULL,
`telephone` varchar(255) DEFAULT NULL,
`website` varchar(500) DEFAULT NULL,
`industry_web_label` varchar(512) DEFAULT NULL COMMENT '国标行业标签',
`industry_domain_label` varchar(512) DEFAULT NULL COMMENT '素问产业链标签',
`scale` varchar(255) DEFAULT NULL COMMENT '规模标签',
`policy_type` varchar(512) DEFAULT NULL COMMENT '政策类型标签',
PRIMARY KEY (`id`),
UNIQUE KEY `company_full_name_idx` (`full_name`),
UNIQUE KEY `company_short_name_idx` (`short_name`),
KEY `company_board_id_idx` (`board_id`),
KEY `company_city_idx` (`city`),
KEY `company_province_idx` (`province`)
)
```

### Table: company_patent

```sql
CREATE TABLE `company_patent` (
`id` int NOT NULL,
`company_id` int DEFAULT NULL,
`name` varchar(512) DEFAULT NULL,
`status` varchar(50) DEFAULT NULL,
`patent_type` varchar(50) DEFAULT NULL,
`num` varchar(100) DEFAULT NULL,
`public_date` date DEFAULT NULL,
`application_date` date DEFAULT NULL,
`update_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最后修改时间',
PRIMARY KEY (`id`),
KEY `company_id_idx` (`company_id`),
KEY `status_idx` (`status`)
)
```

### Table: news_company

```sql
CREATE TABLE `news_company` (
`id` int NOT NULL,
`company_id` int DEFAULT NULL,
`news_id` int DEFAULT NULL,
`release_time` datetime DEFAULT NULL,
`extract_type` varchar(50) DEFAULT NULL,
`is_hot` smallint DEFAULT NULL,
`polarity` smallint DEFAULT NULL,
`update_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最后修改时间',
PRIMARY KEY (`id`),
KEY `company_id_idx` (`company_id`),
KEY `news_id_idx` (`news_id`)
)
```

## Failing SQL Statements

### Example 1: Correlated subquery with IN subquery in SELECT

```sql
SELECT c.province,
(SELECT COUNT(DISTINCT c2.id)
FROM company c2
WHERE c2.province = c.province
AND c2.id IN (
SELECT company_id FROM company_patent WHERE status = '授权'
)
) as companies_with_patents,
COUNT(*) as total_companies
FROM company c
WHERE c.province IS NOT NULL
GROUP BY c.province
LIMIT 10;
```

**Error**: `ERROR 1064 (HY000): SQL parser error: remapInfo step 0 nodeId 11 nodeType JOIN tip OnList ... can't find column [6 21] in context's map`

### Example 2: JOIN with derived table containing correlated subquery

```sql
SELECT c.full_name,
subq.patent_count,
subq.news_count
FROM company c
INNER JOIN (
SELECT cp.company_id,
COUNT(cp.id) as patent_count,
(SELECT COUNT(*)
FROM news_company nc
WHERE nc.company_id = cp.company_id
) as news_count
FROM company_patent cp
WHERE cp.company_id IN (
SELECT id FROM company WHERE province = '广东'
)
GROUP BY cp.company_id
) as subq ON c.id = subq.company_id
LIMIT 10;
```

**Error**: `ERROR 1064 (HY000): SQL parser error: remapInfo step 0 nodeId 12 nodeType JOIN tip OnList ... can't find column [12 1] in context's map`

## Expected Behavior

In MySQL, nested correlated subqueries with IN subqueries in SELECT clauses execute successfully. The query optimizer should correctly map column references across all levels of nesting.

## Impact

- **MySQL Compatibility**: This is a standard MySQL feature that should work correctly
- **Migration**: Applications using nested correlated subqueries will fail when migrating to MatrixOne
- **Functionality**: Users cannot use correlated subqueries with nested IN subqueries in SELECT clauses
- **Query Complexity**: Limits the complexity of analytical queries that can be executed

## Root Cause Analysis

The error occurs in the query optimizer's column reference remapping logic:
1. When processing JOIN operations in nested subqueries
2. The optimizer fails to correctly map column references (`[6 21]`, `[12 1]`) to the context map
3. This suggests an issue in the `remapInfo` logic during query plan generation
4. The problem appears to be in how column references are tracked across nested query levels

## Test Context

This issue was discovered during nested subquery compatibility testing using the `industry_radar_test` database, which contains:
- `company` table with ~766K records
- `company_patent` table with ~1.2M records
- `news_company` table with ~1.5M records

## Suggested Fix

1. **Query Optimizer**: Review and fix the column reference remapping logic in the query optimizer
2. **Column Reference Tracking**: Ensure column references are correctly tracked across nested query levels
3. **JOIN Processing**: Fix the JOIN column reference mapping when processing nested correlated subqueries
4. **Error Handling**: Improve error messages to provide more context about which column reference failed
5. **Testing**: Add comprehensive tests for nested correlated subqueries with IN subqueries
6. **Code Review**: Review `remapInfo` logic in the query optimizer, particularly for JOIN operations with nested subqueries