Repository: vitessio/vitess
Pull request #10179 (2022-04-29 19:25:08)

Applied some format check on the comment_pull_request workflow
## Description

This PR changes the format of the `comment_pull_request`.

## 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



frouioui: Thank you @deepthi 

Patch:
diff --git a/.github/workflows/comment_pull_request.yml b/.github/workflows/comment_pull_request.yml
index 8abd279ce9..25b1ebfcb9 100644
--- a/.github/workflows/comment_pull_request.yml
+++ b/.github/workflows/comment_pull_request.yml
@@ -8,7 +8,7 @@ permissions:
   pull-requests: write
 
 jobs:
-    review_checklist:
+  review_checklist:
     if: ${{ !github.event.pull_request.draft }}
     runs-on: ubuntu-latest
     name: Comment Pull Request with the Review Checklist
diff --git a/go/vt/sqlparser/ast_rewriting.go b/go/vt/sqlparser/ast_rewriting.go
index ecf6f32298..5a2576eb78 100644
--- a/go/vt/sqlparser/ast_rewriting.go
+++ b/go/vt/sqlparser/ast_rewriting.go
@@ -406,6 +406,8 @@ func (er *astRewriter) rewrite(cursor *Cursor) bool {
 				er.bindVars.AddSysVar(sysVar)
 			}
 		}
+	case *ExistsExpr:
+		er.existsRewrite(cursor, node)
 	}
 	return true
 }
@@ -560,6 +562,46 @@ func (er *astRewriter) unnestSubQueries(cursor *Cursor, subquery *Subquery) {
 	cursor.Replace(rewritten)
 }
 
+func (er *astRewriter) existsRewrite(cursor *Cursor, node *ExistsExpr) {
+	switch node := node.Subquery.Select.(type) {
+	case *Select:
+		if node.Limit == nil {
+			node.Limit = &Limit{}
+		}
+		node.Limit.Rowcount = NewIntLiteral("1")
+
+		if node.Having != nil {
+			// If the query has HAVING, we can't take any shortcuts
+			return
+		}
+
+		allAggregation := forAll(node.SelectExprs, func(s SelectExpr) bool {
+			return ContainsAggregation(s)
+		})
+		if allAggregation && len(node.GroupBy) == 0 {
+			// in these situations, we are guaranteed to always get a non-empty result,
+			// so we can replace the EXISTS with a literal true
+			cursor.Replace(BoolVal(true))
+		}
+
+		// If we are not doing HAVING, we can safely replace all select expressions with a
+		// single `1` and remove any grouping
+		node.SelectExprs = SelectExprs{
+			&AliasedExpr{Expr: NewIntLiteral("1")},
+		}
+		node.GroupBy = nil
+	}
+}
+
+func forAll(coll SelectExprs, test func(SelectExpr) bool) bool {
+	for _, k := range coll {
+		if !test(k) {
+			return false
+		}
+	}
+	return true
+}
+
 func bindVarExpression(name string) Expr {
 	return NewArgument(name)
 }


Test patch:
diff --git a/go/test/endtoend/vtgate/gen4/gen4_test.go b/go/test/endtoend/vtgate/gen4/gen4_test.go
index 83e8e2718f..62a6a56a55 100644
--- a/go/test/endtoend/vtgate/gen4/gen4_test.go
+++ b/go/test/endtoend/vtgate/gen4/gen4_test.go
@@ -66,12 +66,44 @@ func TestCorrelatedExistsSubquery(t *testing.T) {
 		_, _ = utils.ExecAllowError(t, conn, `delete from t2`)
 	}()
 	// insert some data.
-	utils.Exec(t, conn, `insert into t1(id, col) values (100, 123),(10, 12), (1, 13), (4, 13),(1000, 1234)`)
+	utils.Exec(t, conn, `insert into t1(id, col) values (100, 123), (10, 12), (1, 13), (4, 13), (1000, 1234)`)
 	utils.Exec(t, conn, `insert into t2(id, tcol1, tcol2) values (100, 13, 1),(9, 7, 15),(1, 123, 123),(1004, 134, 123)`)
 
-	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol2)`, `[[INT64(100)]]`)
-	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol1) order by id`, `[[INT64(1)] [INT64(4)] [INT64(100)]]`)
-	utils.AssertMatches(t, conn, `select id from t1 where id in (select id from t2) order by id`, `[[INT64(1)] [INT64(100)]]`)
+	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol2)`,
+		`[[INT64(100)]]`)
+	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol1) order by id`,
+		`[[INT64(1)] [INT64(4)] [INT64(100)]]`)
+	utils.AssertMatches(t, conn, `select id from t1 where id in (select id from t2) order by id`,
+		`[[INT64(1)] [INT64(100)]]`)
+
+	utils.AssertMatches(t, conn, `
+select id 
+from t1 
+where exists(
+	select t2.id, count(*) 
+	from t2 
+	where t1.col = t2.tcol2
+    having count(*) > 0
+)`,
+		`[[INT64(100)]]`)
+	utils.AssertMatches(t, conn, `
+select id 
+from t1 
+where exists(
+	select t2.id, count(*) 
+	from t2 
+	where t1.col = t2.tcol1
+) order by id`,
+		`[[INT64(1)] [INT64(4)] [INT64(100)]]`)
+	utils.AssertMatchesNoOrder(t, conn, `
+select id 
+from t1 
+where exists(
+	select count(*) 
+	from t2 
+	where t1.col = t2.tcol1
+) order by id`,
+		`[[INT64(1)] [INT64(4)] [INT64(100)] [INT64(1000)] [INT64(10)]]`)
 }
 
 func TestGroupBy(t *testing.T) {
diff --git a/go/vt/sqlparser/ast_rewriting_test.go b/go/vt/sqlparser/ast_rewriting_test.go
index d1d4c5f36a..8f63ebb776 100644
--- a/go/vt/sqlparser/ast_rewriting_test.go
+++ b/go/vt/sqlparser/ast_rewriting_test.go
@@ -165,7 +165,7 @@ func TestRewrites(in *testing.T) {
 		expected: "select 42 as `(select 42 from dual)` from dual",
 	}, {
 		in:       "select exists(select 1) from user",
-		expected: "select exists(select 1) from user",
+		expected: "select exists(select 1 limit 1) from user",
 	}, {
 		in:       "select * from user where col = (select 42)",
 		expected: "select * from user where col = 42",
@@ -259,6 +259,24 @@ func TestRewrites(in *testing.T) {
 	}, {
 		in:       "SELECT * FROM tbl WHERE not id not regexp '%foobar'",
 		expected: "SELECT * FROM tbl WHERE id regexp '%foobar'",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2 from other_table where foo > bar)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2 from other_table where foo > bar limit 100 offset 34)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1 offset 34)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2, count(*) from other_table where foo > bar group by col1, col2)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2 from other_table where foo > bar group by col1, col2)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select count(*) from other_table where foo > bar)",
+		expected: "SELECT * FROM tbl WHERE true",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2, count(*) from other_table where foo > bar group by col1, col2 having count(*) > 3)",
+		expected: "SELECT * FROM tbl WHERE exists(select col1, col2, count(*) from other_table where foo > bar group by col1, col2 having count(*) > 3 limit 1)",
 	}, {
 		in:                          "SHOW VARIABLES",
 		expected:                    "SHOW VARIABLES",
@@ -439,7 +457,7 @@ func TestRewritesWithDefaultKeyspace(in *testing.T) {
 		expected: "SELECT 1 from (select 2 from sys.test) t",
 	}, {
 		in:       "SELECT 1 from test where exists (select 2 from test)",
-		expected: "SELECT 1 from sys.test where exists (select 2 from sys.test)",
+		expected: "SELECT 1 from sys.test where exists (select 1 from sys.test limit 1)",
 	}, {
 		in:       "SELECT 1 from dual",
 		expected: "SELECT 1 from dual",
diff --git a/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt b/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt
index a14de3d77f..0fc37af57d 100644
--- a/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt
+++ b/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt
@@ -128,7 +128,7 @@ select name from user where id not in (select id from t1) /* non-correlated subq
 ----------------------------------------------------------------------
 select name from user where exists (select id from t1) /* non-correlated subquery as EXISTS */
 
-1 ks_unsharded/-: select id from t1 limit 10001 /* non-correlated subquery as EXISTS */
+1 ks_unsharded/-: select 1 from t1 limit 1 /* non-correlated subquery as EXISTS */
 2 ks_sharded/-40: select `name` from `user` where 1 limit 10001 /* non-correlated subquery as EXISTS */
 2 ks_sharded/40-80: select `name` from `user` where 1 limit 10001 /* non-correlated subquery as EXISTS */
 2 ks_sharded/80-c0: select `name` from `user` where 1 limit 10001 /* non-correlated subquery as EXISTS */
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 3810378a82..d147c64730 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -2496,7 +2496,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select count(*) from `user` where 1 != 1",
-        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10)",
+        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10 limit 1)",
         "Table": "`user`"
       }
     ]
@@ -2518,7 +2518,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select count(*) from `user` where 1 != 1",
-        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10)",
+        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10 limit 1)",
         "Table": "`user`"
       }
     ]
diff --git a/go/vt/vtgate/planbuilder/testdata/dml_cases.txt b/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
index 4480b0af93..5e24a7e743 100644
--- a/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
@@ -2953,7 +2953,7 @@ Gen4 plan same as above
     },
     "TargetTabletType": "PRIMARY",
     "MultiShardAutocommit": false,
-    "Query": "insert into user_privacy_consents(user_id, accepted_at) select user_id, accepted_at from (select 1 as user_id, 1629194864 as accepted_at from dual) as tmp where not exists (select user_id from user_privacy_consents where user_id = 1) for update",
+    "Query": "insert into user_privacy_consents(user_id, accepted_at) select user_id, accepted_at from (select 1 as user_id, 1629194864 as accepted_at from dual) as tmp where not exists (select 1 from user_privacy_consents where user_id = 1 limit 1) for update",
     "TableName": "user_privacy_consents"
   }
 }
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index 685fa720dc..824d91d751 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -1722,15 +1722,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
@@ -1757,15 +1763,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
@@ -2614,7 +2626,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select u1.col from `user` as u1 where 1 != 1",
-    "Query": "select u1.col from `user` as u1 where u1.id = 5 and exists (select u2.`name` from `user` as u2 where u2.id = 5)",
+    "Query": "select u1.col from `user` as u1 where u1.id = 5 and exists (select 1 from `user` as u2 where u2.id = 5 limit 1)",
     "Table": "`user`",
     "Values": [
       "INT64(5)"
@@ -2637,7 +2649,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select u1.col from `user` as u1 where 1 != 1",
-    "Query": "select u1.col from `user` as u1 where u1.id = 5 and not exists (select u2.`name` from `user` as u2 where u2.id = 5)",
+    "Query": "select u1.col from `user` as u1 where u1.id = 5 and not exists (select 1 from `user` as u2 where u2.id = 5 limit 1)",
     "Table": "`user`",
     "Values": [
       "INT64(5)"
@@ -2667,8 +2679,8 @@ Gen4 plan same as above
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select u2.`name` from `user` as u2 where 1 != 1",
-        "Query": "select u2.`name` from `user` as u2 where u2.id = 5",
+        "FieldQuery": "select 1 from `user` as u2 where 1 != 1",
+        "Query": "select 1 from `user` as u2 where u2.id = 5 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(5)"
@@ -2706,8 +2718,8 @@ Gen4 plan same as above
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select u2.`name` from `user` as u2 where 1 != 1",
-        "Query": "select u2.`name` from `user` as u2 where u2.id = 5",
+        "FieldQuery": "select 1 from `user` as u2 where 1 != 1",
+        "Query": "select 1 from `user` as u2 where u2.id = 5 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(5)"
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index 1a22f84b3e..5d5c4a0ccc 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -2979,7 +2979,7 @@ Gen4 plan same as above
       "Sharded": false
     },
     "FieldQuery": "select 1 from dual where 1 != 1",
-    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where information_schema.`TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and information_schema.`TABLES`.TABLE_SCHEMA = :__vtschemaname)",
+    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where information_schema.`TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and information_schema.`TABLES`.TABLE_SCHEMA = :__vtschemaname limit 1)",
     "SysTableTableName": "[TABLES_TABLE_NAME:VARCHAR(\"proc\")]",
     "SysTableTableSchema": "[VARCHAR(\"mysql\")]",
     "Table": "dual"
@@ -2996,7 +2996,7 @@ Gen4 plan same as above
       "Sharded": false
     },
     "FieldQuery": "select 1 from dual where 1 != 1",
-    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where `TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and `TABLES`.TABLE_SCHEMA = :__vtschemaname)",
+    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where `TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and `TABLES`.TABLE_SCHEMA = :__vtschemaname limit 1)",
     "SysTableTableName": "[TABLES_TABLE_NAME:VARCHAR(\"proc\")]",
     "SysTableTableSchema": "[VARCHAR(\"mysql\")]",
     "Table": "dual"
@@ -3132,7 +3132,7 @@ Gen4 plan same as above
           "Sharded": false
         },
         "FieldQuery": "select 1 from dual where 1 != 1",
-        "Query": "select 1 from dual",
+        "Query": "select 1 from dual limit 1",
         "Table": "dual"
       },
       {
@@ -3164,7 +3164,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select exists (select 1 from dual where 1 != 1) from `user` where 1 != 1",
-    "Query": "select exists (select 1 from dual) from `user` where id = 5",
+    "Query": "select exists (select 1 from dual limit 1) from `user` where id = 5",
     "Table": "`user`",
     "Values": [
       "INT64(5)"
@@ -3345,8 +3345,8 @@ Gen4 plan same as above
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id = 4",
+        "FieldQuery": "select 1 from `user` where 1 != 1",
+        "Query": "select 1 from `user` where id = 4 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(4)"
@@ -3377,8 +3377,8 @@ Gen4 plan same as above
       "Name": "user",
       "Sharded": true
     },
-    "FieldQuery": "select exists (select id from `user` where 1 != 1) from dual where 1 != 1",
-    "Query": "select exists (select id from `user` where id = 4) from dual",
+    "FieldQuery": "select exists (select 1 from `user` where 1 != 1) from dual where 1 != 1",
+    "Query": "select exists (select 1 from `user` where id = 4 limit 1) from dual",
     "Table": "dual",
     "Values": [
       "INT64(4)"
@@ -3401,15 +3401,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select * from `user` where 1 != 1",
-        "Query": "select * from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
@@ -3436,15 +3442,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select * from `user` where 1 != 1",
-        "Query": "select * from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt b/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt
index 057916f9a6..6cba9e6182 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt
@@ -18,8 +18,8 @@
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select * from `user` where 1 != 1",
-        "Query": "select * from `user` where id = 5",
+        "FieldQuery": "select 1 from `user` where 1 != 1",
+        "Query": "select 1 from `user` where id = 5 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(5)"
@@ -50,8 +50,8 @@
       "Name": "user",
       "Sharded": true
     },
-    "FieldQuery": "select exists (select * from `user` where 1 != 1) from dual where 1 != 1",
-    "Query": "select exists (select * from `user` where id = 5) from dual",
+    "FieldQuery": "select exists (select 1 from `user` where 1 != 1) from dual where 1 != 1",
+    "Query": "select exists (select 1 from `user` where id = 5 limit 1) from dual",
     "Table": "dual",
     "Values": [
       "INT64(5)"
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index 74b962f20a..c066ee6b57 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -1103,7 +1103,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select l1.l_orderkey, l1.l_suppkey, count(*) as numwait, weight_string(l1.l_orderkey), weight_string(l1.l_suppkey) from lineitem as l1 where 1 != 1 group by l1.l_orderkey, weight_string(l1.l_orderkey), l1.l_suppkey, weight_string(l1.l_suppkey)",
-                                "Query": "select l1.l_orderkey, l1.l_suppkey, count(*) as numwait, weight_string(l1.l_orderkey), weight_string(l1.l_suppkey) from lineitem as l1 where l1.l_receiptdate \u003e l1.l_commitdate and exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) group by l1.l_orderkey, weight_string(l1.l_orderkey), l1.l_suppkey, weight_string(l1.l_suppkey)",
+                                "Query": "select l1.l_orderkey, l1.l_suppkey, count(*) as numwait, weight_string(l1.l_orderkey), weight_string(l1.l_suppkey) from lineitem as l1 where l1.l_receiptdate \u003e l1.l_commitdate and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) group by l1.l_orderkey, weight_string(l1.l_orderkey), l1.l_suppkey, weight_string(l1.l_suppkey)",
                                 "Table": "lineitem"
                               },
                               {
@@ -1114,7 +1114,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select count(*) as numwait from orders where 1 != 1",
-                                "Query": "select count(*) as numwait from orders where o_orderstatus = 'F' and exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) and o_orderkey = :l1_l_orderkey",
+                                "Query": "select count(*) as numwait from orders where o_orderstatus = 'F' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and o_orderkey = :l1_l_orderkey",
                                 "Table": "orders",
                                 "Values": [
                                   ":l1_l_orderkey"
@@ -1140,7 +1140,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select s_nationkey, count(*) as numwait, weight_string(s_nationkey), s_name, weight_string(s_name) from supplier where 1 != 1 group by s_nationkey, weight_string(s_nationkey), s_name, weight_string(s_name)",
-                                "Query": "select s_nationkey, count(*) as numwait, weight_string(s_nationkey), s_name, weight_string(s_name) from supplier where exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) and s_suppkey = :l1_l_suppkey group by s_nationkey, weight_string(s_nationkey), s_name, weight_string(s_name)",
+                                "Query": "select s_nationkey, count(*) as numwait, weight_string(s_nationkey), s_name, weight_string(s_name) from supplier where exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and s_suppkey = :l1_l_suppkey group by s_nationkey, weight_string(s_nationkey), s_name, weight_string(s_name)",
                                 "Table": "supplier",
                                 "Values": [
                                   ":l1_l_suppkey"
@@ -1155,7 +1155,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select count(*) as numwait from nation where 1 != 1",
-                                "Query": "select count(*) as numwait from nation where n_name = 'SAUDI ARABIA' and exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) and n_nationkey = :s_nationkey",
+                                "Query": "select count(*) as numwait from nation where n_name = 'SAUDI ARABIA' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and n_nationkey = :s_nationkey",
                                 "Table": "nation",
                                 "Values": [
                                   ":s_nationkey"