Repository: vitessio/vitess (go)

Issue:
Bug Report: select on alias result in Unknown column \'xx\' in \'field list\' errors
### Overview of the Issue

I have a query with `SELECT (alias) as ..` that doesn't work. The same query works in v5 but not v13. We are jumping several versions and I am not sure where it exactly broke. 

Slack conversation: https://vitess.slack.com/archives/C0PQY0PTK/p1663334176821599



### Reproduction Steps

VSchema:
```
tables: <
  key: "folders"
  value: <
    auto_increment: <
      column: "id"
      use_v_tickets: true
    >
  >
>
tables: <
  key: "form_field_maps"
  value: <
    auto_increment: <
      column: "db_id"
      use_v_tickets: true
    >
  >
>
tables: <
  key: "permissions"
  value: <
    auto_increment: <
      column: "id"
      use_v_tickets: true
    >
  >
>
```

Schema:
```
CREATE TABLE `forms` (
  `portalId` bigint(20) unsigned NOT NULL,
  `formType` tinyint(3) unsigned NOT NULL,
  `createdAt` bigint(20) NOT NULL,
  `updatedAt` bigint(20) unsigned NOT NULL,
  `deletedAt` bigint(20) unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL,
  `campaignGuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `migratedFrom` varchar(45) DEFAULT NULL,
  `parentId` int(10) unsigned NOT NULL DEFAULT '0',
  `jsonPublished` json DEFAULT NULL,
  `jsonDraft` json DEFAULT NULL,
  `visibleToAll` tinyint(1) unsigned DEFAULT NULL,
  `formGuid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `customUid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `businessUnitId` bigint(20) unsigned NOT NULL DEFAULT '0',
  `portableKey` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  PRIMARY KEY (`formGuid`),
  UNIQUE KEY `unique_formGuid` (`formGuid`),
  UNIQUE KEY `unique_customUid` (`customUid`,`portalId`),
  UNIQUE KEY `unique_portal_id_portable_key` (`portalId`,`portableKey`),
  KEY `portal_id_updated_at_index` (`portalId`,`updatedAt`),
  KEY `portal_id_name_index` (`portalId`,`name`),
  KEY `createdAt_portalId` (`createdAt`,`portalId`),
  KEY `portalId_active_formType` (`portalId`,`active`,`formType`),
  KEY `parentId` (`parentId`,`portalId`,`active`),
  KEY `portalId_parentId_active_formType` (`portalId`,`parentId`,`active`,`formType`),
  KEY `deleted_at_idx` (`deletedAt`),
  KEY `portal_id_portable_key` (`portalId`,`portableKey`),
  KEY `portal_id_business_unit_id` (`portalId`,`businessUnitId`),
  KEY `portal_id_active_form_guid` (`portalId`,`active`,`formGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
```

Query:
```
SELECT /*+ MAX_EXECUTION_TIME(30000) */(SELECT COUNT(formGuid) from forms WHERE active = 1 AND portalId = 123 AND formType IN (_formTypes_0:0,__formTypes_1:2) 
AND name LIKE CONCAT('%',Folder,'%')) as formsTotal,
(SELECT COUNT(id) from folders WHERE deletedAt = 0 AND portalId = 123 AND name LIKE CONCAT('%',Folder,'%')) as foldersTotal,
(SELECT formsTotal) as formsInFolderTotal
```

if i instead do this, it works
```
SELECT /*+ MAX_EXECUTION_TIME(30000) */(SELECT COUNT(formGuid) from forms WHERE active = 1 AND portalId = 123 AND formType IN (_formTypes_0:0,__formTypes_1:2) 
AND name LIKE CONCAT('%',Folder,'%')) as formsTotal,
(SELECT COUNT(id) from folders WHERE deletedAt = 0 AND portalId = 123 AND name LIKE CONCAT('%',Folder,'%')) as foldersTotal,
(SELECT /*+ MAX_EXECUTION_TIME(30000) */(SELECT COUNT(formGuid) from forms WHERE active = 1 AND portalId = 123 AND formType IN (_formTypes_0:0,__formTypes_1:2) 
AND name LIKE CONCAT('%',Folder,'%')) as formsInFolderTotal
```

### Binary Version

```
v13.0.0, commit bc4a960
```


### Operating System and Environment details

```
Can reproduce on both macos and linux, running unit tests in Java
```


### Log Fragments

```sh
Vitess vtgate error: message: "target: test_db_Fi4w2Y.0.primary: vttablet: rpc error: code = NotFound desc = Unknown column \'formsTotal\' in \'field list\' (errno 1054) (sqlstate 42S22) (CallerID: unsecure_grpc_client): Sql: \"select /*+ MAX_EXECUTION_TIME(30000) */ (select COUNT(formGuid) from forms where active = :vtg1 and portalId = :v1 and businessUnitId = :v2 and formType in (:v3, :v4) and `name` like CONCAT(:vtg2, :v5, :vtg2)) as formsTotal, (select COUNT(id) from folders where deletedAt = :vtg3 and portalId = :v6 and `name` like CONCAT(:vtg2, :v7, :vtg2)) as foldersTotal, formsTotal as formsInFolderTotal from dual\", BindVars: {v1: \"type:INT64 value:\\\"123\\\"\"v2: \"type:INT64 value:\\\"1\\\"\"v3: \"type:INT64 value:\\\"0\\\"\"v4: \"type:INT64 value:\\\"2\\\"\"v5: \"type:VARCHAR value:\\\"unit\\\"\"v6: \"type:INT64 value:\\\"123\\\"\"v7: \"type:VARCHAR value:\\\"unit\\\"\"vtg1: \"type:INT64 value:\\\"1\\\"\"vtg2: \"type:VARCHAR value:\\\"%\\\"\"vtg3: \"type:INT64 value:\\\"0\\\"\"}"
code: NOT_FOUND
 [statement:"SELECT(SELECT COUNT(formGuid) from forms WHERE active = 1 AND portalId = :portalId AND /* FILTER:businessUnitId [businessUnitId = :businessUnitId AND ] */formType IN (<formTypes>) AND name LIKE CONCAT('%',:searchName,'%')) as formsTotal,(SELECT COUNT(id) from folders WHERE deletedAt = 0 AND portalId = :portalId AND name LIKE CONCAT('%',:searchName,'%')) as foldersTotal,(SELECT formsTotal) as formsInFolderTotal", located:"SELECT(SELECT COUNT(formGuid) from forms WHERE active = 1 AND portalId = :portalId AND /* FILTER:businessUnitId [businessUnitId = :businessUnitId AND ] */formType IN (:__formTypes_0,:__formTypes_1) AND name LIKE CONCAT('%',:searchName,'%')) as formsTotal,(SELECT COUNT(id) from folders WHERE deletedAt = 0 AND portalId = :portalId AND name LIKE CONCAT('%',:searchName,'%')) as foldersTotal,(SELECT formsTotal) as formsInFolderTotal", rewritten:"SELECT /*+ MAX_EXECUTION_TIME(30000) */(SELECT COUNT(formGuid) from forms WHERE active = 1 AND portalId = ? AND businessUnitId = ? AND formType IN (?,?) AND name LIKE CONCAT('%',?,'%')) as formsTotal,(SELECT COUNT(id) from folders WHERE deletedAt = 0 AND portalId = ? AND name LIKE CONCAT('%',?,'%')) as foldersTotal,(SELECT formsTotal) as formsInFolderTotal", arguments:{ positional:{}, named:{portalId:123,searchName:'unit',__formTypes_0:0,__formTypes_1:2,businessUnitId:1}, finder:[]}]
```



Pull request description:
fix: do not rewrite single columns in derived tables
## Description
The Vitess vtgate planner was doing an bad query rewrite. A query such as this:

```sql
select 1 as x, (select x)
```

would get rewritten to:

```sql
select 1 as x, x as `(select x)`
```

This PR stops these rewrites from being performed.

## Related Issue(s)
Fixes #11246

## Checklist

-   [x] "Backport me!" label has been added if this change should be backported
-   [x] Tests were added or are not required
-   [x] Documentation was added or is not required

Patch:
diff --git a/go/vt/sqlparser/ast_rewriting.go b/go/vt/sqlparser/ast_rewriting.go
index 90bbd7ccc0..5ecdea8896 100644
--- a/go/vt/sqlparser/ast_rewriting.go
+++ b/go/vt/sqlparser/ast_rewriting.go
@@ -541,6 +541,14 @@ func (er *astRewriter) unnestSubQueries(cursor *Cursor, subquery *Subquery) {
 	if !ok {
 		return
 	}
+	_, isColName := expr.Expr.(*ColName)
+	if isColName {
+		// If we find a single col-name in a `dual` subquery, we can be pretty sure the user is returning a column
+		// already projected.
+		// `select 1 as x, (select x)`
+		// is perfectly valid - any aliased columns to the left are available inside subquery scopes
+		return
+	}
 	er.bindVars.NoteRewrite()
 	// we need to make sure that the inner expression also gets rewritten,
 	// so we fire off another rewriter traversal here
diff --git a/go/vt/vtgate/planbuilder/physical/filter.go b/go/vt/vtgate/planbuilder/physical/filter.go
index 67cc84809e..4647740faa 100644
--- a/go/vt/vtgate/planbuilder/physical/filter.go
+++ b/go/vt/vtgate/planbuilder/physical/filter.go
@@ -37,11 +37,6 @@ func (f *Filter) TableID() semantics.TableSet {
 	return f.Source.TableID()
 }
 
-// PushPredicate implements the PhysicalOperator interface
-func (f *Filter) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
-	panic("unimplemented")
-}
-
 // UnsolvedPredicates implements the PhysicalOperator interface
 func (f *Filter) UnsolvedPredicates(semTable *semantics.SemTable) []sqlparser.Expr {
 	panic("implement me")


Test patch:
diff --git a/go/vt/sqlparser/ast_rewriting_test.go b/go/vt/sqlparser/ast_rewriting_test.go
index 957128e473..95e84ae8c2 100644
--- a/go/vt/sqlparser/ast_rewriting_test.go
+++ b/go/vt/sqlparser/ast_rewriting_test.go
@@ -96,6 +96,10 @@ func TestRewrites(in *testing.T) {
 		in:       "select (select database() from dual) from dual",
 		expected: "select :__vtdbname as `(select database() from dual)` from dual",
 		db:       true,
+	}, {
+		// don't unnest solo columns
+		in:       "select 1 as foobar, (select foobar)",
+		expected: "select 1 as foobar, (select foobar from dual) from dual",
 	}, {
 		in:       "select id from user where database()",
 		expected: "select id from user where database()",
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index 9259e15688..3072533565 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -7694,3 +7694,39 @@ Gen4 error: unsupported: JOIN not supported between derived tables
     "user.music"
   ]
 }
+
+# Earlier columns are in scope in subqueries https://github.com/vitessio/vitess/issues/11246
+"SELECT 1 as x, (SELECT x)"
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT 1 as x, (SELECT x)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "Reference",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 1 as x, (select x from dual where 1 != 1) from dual where 1 != 1",
+    "Query": "select 1 as x, (select x from dual) from dual",
+    "Table": "dual"
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT 1 as x, (SELECT x)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "Reference",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 1 as x, (select x from dual where 1 != 1) from dual where 1 != 1",
+    "Query": "select 1 as x, (select x from dual) from dual",
+    "Table": "dual"
+  },
+  "TablesUsed": [
+    "main.dual"
+  ]
+}