Repository: vitessio/vitess
Pull request #9263 (2021-11-18 14:26:53)

Gen4: filter on derived union system table with star projection
<!--
  Thank you for your contribution to the Vitess project.
  How to contribute: https://vitess.io/docs/contributing/
  Please first make sure there is an open Issue to discuss the feature/fix suggested in this PR.
  If this is a new feature, please mark the Issue as "RFC".
 -->

<!-- if this PR is Work in Progress please create it as a Draft Pull Request -->

## Description
<!-- A few sentences describing the overall goals of the pull request's commits. -->
<!-- If this is a bug fix and you think the fix should be backported, please write so. -->

This PR add support for filter on derived union system table with star projection.

## Related Issue(s)
<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->

- Closes https://github.com/vitessio/vitess/issues/9214

## Checklist
- [X] Tests were added or are not required
- [X] Documentation was added or is not required

## Deployment Notes
<!-- Notes regarding deployment of the contained body of work. These should note any db migrations, etc. -->


Patch:
diff --git a/go/vt/vtgate/planbuilder/abstract/concatenate.go b/go/vt/vtgate/planbuilder/abstract/concatenate.go
index f522c4f4b0..205fa57536 100644
--- a/go/vt/vtgate/planbuilder/abstract/concatenate.go
+++ b/go/vt/vtgate/planbuilder/abstract/concatenate.go
@@ -44,8 +44,20 @@ func (c *Concatenate) TableID() semantics.TableSet {
 }
 
 // PushPredicate implements the Operator interface
-func (c *Concatenate) PushPredicate(sqlparser.Expr, *semantics.SemTable) error {
-	return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
+func (c *Concatenate) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+	for index, source := range c.Sources {
+		if len(c.SelectStmts[index].SelectExprs) != 1 {
+			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
+		}
+		if _, isStarExpr := c.SelectStmts[index].SelectExprs[0].(*sqlparser.StarExpr); !isStarExpr {
+			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
+		}
+		err := source.PushPredicate(expr, semTable)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
 }
 
 // UnsolvedPredicates implements the Operator interface


Test patch:
diff --git a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
index ca5fbb12c1..7d26c02dd2 100644
--- a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
@@ -1425,3 +1425,24 @@ Gen4 plan same as above
     "Table": "information_schema.random"
   }
 }
+
+# systable union query in derived table with constraint on outside (star projection)
+"select * from (select * from `information_schema`.`key_column_usage` `kcu` where `kcu`.`table_schema` = 'user' and `kcu`.`table_name` = 'user_extra' union select * from `information_schema`.`key_column_usage` `kcu` where `kcu`.`table_schema` = 'user' and `kcu`.`table_name` = 'music') `kcu` where `constraint_name` = 'primary'"
+"symbol constraint_name not found in table or subquery"
+{
+  "QueryType": "SELECT",
+  "Original": "select * from (select * from `information_schema`.`key_column_usage` `kcu` where `kcu`.`table_schema` = 'user' and `kcu`.`table_name` = 'user_extra' union select * from `information_schema`.`key_column_usage` `kcu` where `kcu`.`table_schema` = 'user' and `kcu`.`table_name` = 'music') `kcu` where `constraint_name` = 'primary'",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select * from (select * from information_schema.key_column_usage as kcu where 1 != 1 union select * from information_schema.key_column_usage as kcu where 1 != 1) as kcu where 1 != 1",
+    "Query": "select * from (select * from information_schema.key_column_usage as kcu where kcu.table_schema = :__vtschemaname and kcu.table_name = :kcu_table_name and constraint_name = 'primary' union select * from information_schema.key_column_usage as kcu where kcu.table_schema = :__vtschemaname and kcu.table_name = :kcu_table_name1 and constraint_name = 'primary') as kcu",
+    "SysTableTableName": "[kcu_table_name1:VARBINARY(\"music\"), kcu_table_name:VARBINARY(\"user_extra\")]",
+    "SysTableTableSchema": "[VARBINARY(\"user\"), VARBINARY(\"user\")]",
+    "Table": "information_schema.key_column_usage"
+  }
+}
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index f9755132a6..9caafc33da 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -642,3 +642,8 @@ Gen4 error: unsupported: in scatter query: complex order by expression: a + 1
 "select sum(col) from (select col from user union all select col from unsharded) t"
 "unsupported: cross-shard query with aggregates"
 Gen4 error: unsupported: aggregation on unions
+
+# systable union query in derived table with constraint on outside (without star projection)
+"select id from (select id from `information_schema`.`key_column_usage` `kcu` where `kcu`.`table_schema` = 'user' and `kcu`.`table_name` = 'user_extra' union select id from `information_schema`.`key_column_usage` `kcu` where `kcu`.`table_schema` = 'user' and `kcu`.`table_name` = 'music') `kcu` where `id` = 'primary'"
+"unsupported: filtering on results of cross-shard subquery"
+Gen4 error: can't push predicates on concatenate