Repository: vitessio/vitess
Pull request #8673 (2021-08-23 19:18:39)

Gen4: added support for query comments, locking reads, select with into
<!--
  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. -->

Support added for query comments, locking reads statements and select with into query

## Related Issue(s)
<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->

#7280 

## 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/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 2391743b01..0d27063739 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -148,6 +148,10 @@ func newBuildSelectPlan(sel *sqlparser.Select, reservedVars *sqlparser.ReservedV
 		return nil, err
 	}
 
+	if err := setMiscFunc(plan, sel); err != nil {
+		return nil, err
+	}
+
 	if err := plan.WireupGen4(semTable); err != nil {
 		return nil, err
 	}
diff --git a/go/vt/vtgate/semantics/analyzer.go b/go/vt/vtgate/semantics/analyzer.go
index b64f3dcf47..5309ca0ed3 100644
--- a/go/vt/vtgate/semantics/analyzer.go
+++ b/go/vt/vtgate/semantics/analyzer.go
@@ -205,9 +205,21 @@ func checkForInvalidConstructs(cursor *sqlparser.Cursor) error {
 			return Gen4NotSupportedF("HAVING")
 		}
 	case *sqlparser.Subquery:
-		if _, ok := node.Select.(*sqlparser.Select); !ok {
+		sel, ok := node.Select.(*sqlparser.Select)
+		if !ok {
 			return Gen4NotSupportedF("%T in subquery", node.Select)
 		}
+		if sel.Into != nil {
+			return vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.CantUseOptionHere, "Incorrect usage/placement of 'INTO'")
+		}
+	case *sqlparser.DerivedTable:
+		sel, ok := node.Select.(*sqlparser.Select)
+		if !ok {
+			return nil
+		}
+		if sel.Into != nil {
+			return vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.CantUseOptionHere, "Incorrect usage/placement of 'INTO'")
+		}
 	case *sqlparser.FuncExpr:
 		if sqlparser.IsLockingFunc(node) {
 			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%v allowed only with dual", sqlparser.String(node))


Test patch:
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index 5cac9bb847..a7ef1afc56 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -1227,6 +1227,43 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select user.col from user join user_extra on user.id \u003c user_extra.user_id",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-2",
+    "JoinVars": {
+      "user_id": 0
+    },
+    "TableName": "`user`_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.id, `user`.col from `user` where 1 != 1",
+        "Query": "select `user`.id, `user`.col from `user`",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from user_extra where 1 != 1",
+        "Query": "select 1 from user_extra where :user_id \u003c user_extra.user_id",
+        "Table": "user_extra"
+      }
+    ]
+  }
+}
 
 # sharded join, non-col reference RHS
 "select user.col from user join user_extra on user.id = 5"
@@ -1561,6 +1598,21 @@ Gen4 plan same as above
     "Table": "`user`, ref"
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select ref.col from ref join user",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectScatter",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select ref.col from ref, `user` where 1 != 1",
+    "Query": "select ref.col from ref, `user`",
+    "Table": "`user`, ref"
+  }
+}
 
 # reference table can merge with other opcodes left to right and vindex value is in the plan.
 # This tests that route.Merge also copies the condition to the LHS.
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index 442ffe8e92..5c3f44e9cf 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -612,6 +612,51 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select user.col1 as a, user.col2, music.col3 from user join music on user.id = music.id where user.id = 1 order by null",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-2,-3,1",
+    "JoinVars": {
+      "user_id": 0
+    },
+    "TableName": "`user`_music",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.id, `user`.col1 as a, `user`.col2 from `user` where 1 != 1",
+        "Query": "select `user`.id, `user`.col1 as a, `user`.col2 from `user` where `user`.id = 1 order by null",
+        "Table": "`user`",
+        "Values": [
+          1
+        ],
+        "Vindex": "user_index"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select music.col3 from music where 1 != 1",
+        "Query": "select music.col3 from music where music.id = :user_id",
+        "Table": "music",
+        "Values": [
+          ":user_id"
+        ],
+        "Vindex": "music_user_map"
+      }
+    ]
+  }
+}
 
 # ORDER BY non-key column for join
 "select user.col1 as a, user.col2, music.col3 from user join music on user.id = music.id where user.id = 1 order by a"
@@ -2004,3 +2049,22 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
+
+# Order by has subqueries
+"select id from unsharded order by (select id from unsharded)"
+"unsupported: subqueries disallowed in GROUP or ORDER BY"
+{
+  "QueryType": "SELECT",
+  "Original": "select id from unsharded order by (select id from unsharded)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectUnsharded",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select id from unsharded where 1 != 1",
+    "Query": "select id from unsharded order by (select id from unsharded) asc",
+    "Table": "unsharded"
+  }
+}
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index 0513d02422..8fad6083f2 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -817,6 +817,7 @@ Gen4 plan same as above
     ]
   }
 }
+Gen4 plan same as above
 
 # Field query should work for joins select bind vars
 "select user.id, (select user.id+outm.m+unsharded.m from unsharded) from user join unsharded outm"
@@ -1677,6 +1678,7 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
+Gen4 plan same as above
 
 # select from unsharded keyspace into outfile
 "select * from main.unsharded into outfile 'x.txt' character set binary fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\\n'"
@@ -1695,6 +1697,7 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
+Gen4 plan same as above
 
 # select from unsharded keyspace into outfile s3
 "select * from main.unsharded into outfile s3 'out_file_name' character set binary format csv header fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\n' manifest on overwrite off"
@@ -1713,6 +1716,7 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
+Gen4 plan same as above
 
 # Union after into outfile is incorrect
 "select id from user into outfile 'out_file_name' union all select id from music"
@@ -1722,6 +1726,7 @@ Gen4 plan same as above
 # Into outfile s3 in sub-query is incorrect
 "select id from (select id from user into outfile s3 'inner_outfile') as t2"
 "Incorrect usage/placement of 'INTO'"
+Gen4 plan same as above
 
 "select (select u.id from user as u where u.id = 1), a.id from user as a where a.id = 1"
 {
diff --git a/go/vt/vtgate/planbuilder/testdata/show_cases.txt b/go/vt/vtgate/planbuilder/testdata/show_cases.txt
index 11aca628e8..652ff40c8a 100644
--- a/go/vt/vtgate/planbuilder/testdata/show_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/show_cases.txt
@@ -597,7 +597,7 @@ Gen4 plan same as above
     "OperatorType": "SHOW WARNINGS"
   }
 }
-
+Gen4 plan same as above
 
 # show global status
 "show global status"
diff --git a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
index 2bd2aefddc..20efcd45f2 100644
--- a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
@@ -624,6 +624,7 @@ Gen4 plan same as above
     "Table": "information_schema.a"
   }
 }
+Gen4 plan same as above
 
 # query trying to query two different keyspaces at the same time
 "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'user' AND TABLE_SCHEMA = 'main'"
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 9c9c9d52cc..95515d3368 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -147,10 +147,6 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
 "select user.col1 as a from user order by 1 collate utf8_general_ci"
 "unsupported: in scatter query: complex order by expression: 1 collate utf8_general_ci"
 
-# Order by has subqueries
-"select id from unsharded order by (select id from unsharded)"
-"unsupported: subqueries disallowed in GROUP or ORDER BY"
-
 # subqueries in update
 "update user set col = (select id from unsharded)"
 "unsupported: subqueries in sharded DML"