diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
index 13eb399..03b530b 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
@@ -930,7 +930,6 @@
                     }
                 } else {
                     AbstractFunctionCallExpression f = createFunctionCallExpressionForBuiltinOperator(ops.get(i));
-
                     if (i == 0) {
                         f.getArguments().add(new MutableObject<>(e));
                         currExpr = f;
@@ -1182,53 +1181,40 @@
 
     protected AbstractFunctionCallExpression createFunctionCallExpressionForBuiltinOperator(OperatorType t)
             throws AsterixException {
-
-        FunctionIdentifier fid = null;
+        FunctionIdentifier fid;
         switch (t) {
-            case PLUS: {
+            case PLUS:
                 fid = AlgebricksBuiltinFunctions.NUMERIC_ADD;
                 break;
-            }
-            case MINUS: {
+            case MINUS:
                 fid = AsterixBuiltinFunctions.NUMERIC_SUBTRACT;
                 break;
-            }
-            case MUL: {
+            case MUL:
                 fid = AsterixBuiltinFunctions.NUMERIC_MULTIPLY;
                 break;
-            }
-            case DIV: {
+            case DIV:
                 fid = AsterixBuiltinFunctions.NUMERIC_DIVIDE;
                 break;
-            }
-            case MOD: {
+            case MOD:
                 fid = AsterixBuiltinFunctions.NUMERIC_MOD;
                 break;
-            }
-            case IDIV: {
+            case IDIV:
                 fid = AsterixBuiltinFunctions.NUMERIC_IDIV;
                 break;
-            }
-            case CARET: {
+            case CARET:
                 fid = AsterixBuiltinFunctions.CARET;
                 break;
-            }
-            case AND: {
+            case AND:
                 fid = AlgebricksBuiltinFunctions.AND;
                 break;
-            }
-            case OR: {
+            case OR:
                 fid = AlgebricksBuiltinFunctions.OR;
                 break;
-            }
-            case FUZZY_EQ: {
+            case FUZZY_EQ:
                 fid = AsterixBuiltinFunctions.FUZZY_EQ;
                 break;
-            }
-
-            default: {
+            default:
                 throw new NotImplementedException("Operator " + t + " is not yet implemented");
-            }
         }
         return new ScalarFunctionCallExpression(FunctionUtil.getFunctionInfo(fid));
     }
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in.sqlpp
new file mode 100644
index 0000000..0920d6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATABASE test IF EXISTS;
+CREATE DATABASE test;
+USE test;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey: int32
+}
+
+CREATE TYPE CustomerType AS {
+  c_custkey: int32
+}
+
+
+CREATE COLLECTION Customers(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE COLLECTION Orders(OrderType) PRIMARY KEY o_orderkey;
+
+SELECT c.c_custkey customer_name
+FROM Customers c
+WHERE c.c_custkey IN (
+    SELECT VALUE o.o_custkey
+    FROM Orders o
+  )  AND c.c_nationkey = 5
+ORDER BY customer_name;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_correlated.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_correlated.sqlpp
new file mode 100644
index 0000000..df1a526
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_correlated.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+DROP DATABASE test IF EXISTS;
+CREATE DATABASE test;
+USE test;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey: int32
+}
+
+CREATE TYPE CustomerType AS {
+  c_custkey: int32
+}
+
+
+CREATE COLLECTION Customers(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE COLLECTION Orders(OrderType) PRIMARY KEY o_orderkey;
+
+SELECT c.c_custkey customer_name
+FROM Customers c
+WHERE c.c_custkey IN (
+    SELECT VALUE o.o_custkey
+    FROM Orders o
+    WHERE c.c_custkey = o.o_custkey
+  )  AND c.c_nationkey = 5
+ORDER BY customer_name;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
index 37dcce2..4495551 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$28]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$19]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$28(ASC)] HASH:[$$28]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$24]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$19(ASC)] HASH:[$$19]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$15]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
index b0b5a88..1d34c95 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
@@ -2,20 +2,20 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$21(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$21(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$15(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$15(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- HYBRID_HASH_JOIN [$$23][$$24]  |PARTITIONED|
-                    -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$$17][$$18]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$17]  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
                         -- ASSIGN  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- DATASOURCE_SCAN  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                    -- HASH_PARTITION_EXCHANGE [$$24]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
                       -- ASSIGN  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
index f600468..1d34c95 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
@@ -2,20 +2,20 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$15(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$15(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- HYBRID_HASH_JOIN [$$21][$$22]  |PARTITIONED|
-                    -- HASH_PARTITION_EXCHANGE [$$21]  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$$17][$$18]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$17]  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
                         -- ASSIGN  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- DATASOURCE_SCAN  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                    -- HASH_PARTITION_EXCHANGE [$$22]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
                       -- ASSIGN  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan
new file mode 100644
index 0000000..8276df2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan
@@ -0,0 +1,35 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$26(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$26(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- STREAM_SELECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$19]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- STREAM_SELECT  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$19][$$7]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- STREAM_SELECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$7]  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
new file mode 100644
index 0000000..e0b4473
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
@@ -0,0 +1,33 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$31(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$31(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- STREAM_SELECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$23]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- STREAM_SELECT  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$23][$$22]  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- STREAM_SELECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$22]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_01/like_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_01/like_01.3.query.sqlpp
index c5ebc10..fa4a4e0 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_01/like_01.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_01/like_01.3.query.sqlpp
@@ -20,4 +20,4 @@
 use test;
 
 
-select element [test.like('A6BBB','_6%'),test.like('A8BBB','_6%')];
+select element ['A6BBB' LIKE '_6%', 'A8BBB' LIKE '_6%' ];
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_null/like_null.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_null/like_null.3.query.sqlpp
index caa1618..3251659 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_null/like_null.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/string/like_null/like_null.3.query.sqlpp
@@ -20,4 +20,4 @@
 use test;
 
 
-{'field1':test.like('A8BBB',null),'field2':test.like(null,'_6%')};
+{'field1': 'A8BBB' LIKE null,'field2': null LIKE '_6%'};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.1.ddl.sqlpp
new file mode 100644
index 0000000..733ae9c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.1.ddl.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATABASE test IF EXISTS;
+CREATE DATABASE test;
+USE test;
+
+
+CREATE TYPE OrderType AS CLOSED {
+  o_orderkey: int32,
+  o_custkey: int32,
+  o_orderstatus: string,
+  o_totalprice: double,
+  o_orderdate: string,
+  o_orderpriority: string,
+  o_clerk: string,
+  o_shippriority: int32,
+  o_comment: string
+}
+
+CREATE TYPE CustomerType AS CLOSED {
+  c_custkey: int32,
+  c_name: string,
+  c_address: string,
+  c_nationkey: int32,
+  c_phone: string,
+  c_acctbal: double,
+  c_mktsegment: string,
+  c_comment: string
+}
+
+
+CREATE EXTERNAL TABLE Customers(CustomerType) USING `localfs`
+((`path`=`asterix_nc1://data/tpch0.001/customer.tbl`),
+(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+CREATE EXTERNAL TABLE Orders(OrderType) USING `localfs`
+((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),
+(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.2.update.sqlpp
new file mode 100644
index 0000000..7220975
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.2.update.sqlpp
@@ -0,0 +1,18 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.3.query.sqlpp
new file mode 100644
index 0000000..12985e1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in/in.3.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT c.c_custkey customer_name
+FROM Customers c
+WHERE c.c_custkey IN (
+    SELECT VALUE o.o_custkey
+    FROM Orders o
+  )  AND c.c_nationkey = 5
+ORDER BY customer_name;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.1.ddl.sqlpp
new file mode 100644
index 0000000..733ae9c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.1.ddl.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATABASE test IF EXISTS;
+CREATE DATABASE test;
+USE test;
+
+
+CREATE TYPE OrderType AS CLOSED {
+  o_orderkey: int32,
+  o_custkey: int32,
+  o_orderstatus: string,
+  o_totalprice: double,
+  o_orderdate: string,
+  o_orderpriority: string,
+  o_clerk: string,
+  o_shippriority: int32,
+  o_comment: string
+}
+
+CREATE TYPE CustomerType AS CLOSED {
+  c_custkey: int32,
+  c_name: string,
+  c_address: string,
+  c_nationkey: int32,
+  c_phone: string,
+  c_acctbal: double,
+  c_mktsegment: string,
+  c_comment: string
+}
+
+
+CREATE EXTERNAL TABLE Customers(CustomerType) USING `localfs`
+((`path`=`asterix_nc1://data/tpch0.001/customer.tbl`),
+(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+CREATE EXTERNAL TABLE Orders(OrderType) USING `localfs`
+((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),
+(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.2.update.sqlpp
new file mode 100644
index 0000000..7220975
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.2.update.sqlpp
@@ -0,0 +1,18 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.3.query.sqlpp
new file mode 100644
index 0000000..4e05644
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/in_correlated/in_correlated.3.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT c.c_custkey customer_name
+FROM Customers c
+WHERE c.c_custkey IN (
+    SELECT VALUE o.o_custkey
+    FROM Orders o
+    WHERE c.c_custkey = o.o_custkey
+  )  AND c.c_nationkey = 5
+ORDER BY customer_name;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.1.ddl.sqlpp
new file mode 100644
index 0000000..733ae9c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.1.ddl.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATABASE test IF EXISTS;
+CREATE DATABASE test;
+USE test;
+
+
+CREATE TYPE OrderType AS CLOSED {
+  o_orderkey: int32,
+  o_custkey: int32,
+  o_orderstatus: string,
+  o_totalprice: double,
+  o_orderdate: string,
+  o_orderpriority: string,
+  o_clerk: string,
+  o_shippriority: int32,
+  o_comment: string
+}
+
+CREATE TYPE CustomerType AS CLOSED {
+  c_custkey: int32,
+  c_name: string,
+  c_address: string,
+  c_nationkey: int32,
+  c_phone: string,
+  c_acctbal: double,
+  c_mktsegment: string,
+  c_comment: string
+}
+
+
+CREATE EXTERNAL TABLE Customers(CustomerType) USING `localfs`
+((`path`=`asterix_nc1://data/tpch0.001/customer.tbl`),
+(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+CREATE EXTERNAL TABLE Orders(OrderType) USING `localfs`
+((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),
+(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.2.update.sqlpp
new file mode 100644
index 0000000..7220975
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.2.update.sqlpp
@@ -0,0 +1,18 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.3.query.sqlpp
new file mode 100644
index 0000000..36819df
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/not_in/not_in.3.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT c.c_custkey customer_name
+FROM Customers c
+WHERE c.c_custkey NOT IN (
+    SELECT VALUE o.o_custkey
+    FROM Orders o
+  )  AND c.c_nationkey = 5
+ORDER BY customer_name;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index 499899d..cbb55c4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@ -40,7 +40,7 @@
         ) as srn
         where (srn.s_suppkey = ps.ps_suppkey)
     ) as pssrn
-    where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+    where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS'
 )
 };
 declare function tmp2() {
@@ -66,7 +66,7 @@
         ) as srn
         where (srn.s_suppkey = ps.ps_suppkey)
     ) as pssrn
-    where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+    where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS'
     /* +hash */
     group by pssrn.p_partkey as p_partkey
 )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 45888f3..24f4f46 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -32,7 +32,7 @@
         select element {'c_custkey':c.c_custkey,'o_orderkey_count':coll_count((
                 select element o.o_orderkey
                 from  Orders as o
-                where c.c_custkey = o.o_custkey and NOT like(o.o_comment,'%special%requests%')
+                where c.c_custkey = o.o_custkey and o.o_comment not like '%special%requests%'
             ))}
         from  Customer as c
     ) as co
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index 6c4e330..eb556b16 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -21,7 +21,7 @@
 
 
 select element (100.0 * tpch.coll_sum((
-      select element tpch.`switch-case`(tpch.like(i.p_type,'PROMO%'),true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
+      select element tpch.`switch-case`(i.p_type like 'PROMO%',true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
       from  lp as i
   )) / tpch.coll_sum((
       select element (i.l_extendedprice * (1 - i.l_discount))
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index a6a53bb..131c8a0 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -27,10 +27,10 @@
         select element {'p_brand':p.p_brand,'p_type':p.p_type,'p_size':p.p_size,'ps_suppkey':ps.ps_suppkey}
         from  Partsupp as ps,
               Part as p
-        where p.p_partkey = ps.ps_partkey and p.p_brand != 'Brand#45' and not like(p.p_type,'MEDIUM POLISHED%')
+        where p.p_partkey = ps.ps_partkey and p.p_brand != 'Brand#45' and p.p_type not like 'MEDIUM POLISHED%'
     ) as psp,
           Supplier as s
-    where psp.ps_suppkey = s.s_suppkey and not like(s.s_comment,'%Customer%Complaints%')
+    where psp.ps_suppkey = s.s_suppkey and s.s_comment not like '%Customer%Complaints%'
 )
 };
 select element {'p_brand':p_brand,'p_type':p_type,'p_size':p_size,'supplier_cnt':supplier_cnt}
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index b13a11a..e1704fe 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@ -26,7 +26,7 @@
  FROM Nation JOIN Region ON n_regionkey = r_regionkey and r_name = 'EUROPE'
  JOIN Supplier ON s_nationkey = n_nationkey
  JOIN Partsupp ON s_suppkey = ps_suppkey
- JOIN Part ON p_partkey = ps_partkey AND like(p_type, '%BRASS')
+ JOIN Part ON p_partkey = ps_partkey AND p_type LIKE '%BRASS'
 ),
 q2_minimum_cost_supplier_tmp2 AS
 (
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 31d4dbc..bd5f431 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -28,7 +28,7 @@
                            (
                             select element o_orderkey
                             from  Orders
-                            where c_custkey = o_custkey and not(like(o_comment,'%special%requests%'))
+                            where c_custkey = o_custkey and o_comment NOT LIKE '%special%requests%'
                            )
                          ) AS o_orderkey_count
                 from  Customer
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index 56d7637..1c10baf 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -21,7 +21,7 @@
 
 
 select element 100.0 *
-     sum(`switch-case`(like(p_type,'PROMO%'),true,(l_extendedprice * (1 - l_discount)),false,0.0))
+     sum(`switch-case`(p_type LIKE 'PROMO%',true, l_extendedprice * (1 - l_discount), false, 0.0))
      / sum(l_extendedprice * (1 - l_discount))
 FROM  LineItem,
       Part
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index 2b56eab..5d9eef3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -28,10 +28,10 @@
             FROM  Partsupp,
                   Part
             WHERE p_partkey = ps_partkey AND p_brand != 'Brand#45' AND
-              not(like(p_type,'MEDIUM POLISHED%'))
+                  p_type NOT LIKE 'MEDIUM POLISHED%'
            ) AS psp,
            Supplier
-    WHERE ps_suppkey = s_suppkey AND not(like(s_comment,'%Customer%Complaints%'))
+    WHERE ps_suppkey = s_suppkey AND s_comment NOT LIKE '%Customer%Complaints%'
 )
 
 SELECT p_brand, p_type, p_size, count(ps_suppkey) supplier_cnt
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index 636fb04..8f84f62 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@ -39,7 +39,7 @@
   JOIN Partsupp ps
   ON s.s_suppkey = ps.ps_suppkey
   JOIN Part p
-  ON p.p_partkey = ps.ps_partkey AND like(p.p_type, '%BRASS')
+  ON p.p_partkey = ps.ps_partkey AND p.p_type LIKE '%BRASS'
 ),
 q2_minimum_cost_supplier_tmp2 AS
 (SELECT
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 3124fdc..fb3e8e7 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -28,7 +28,7 @@
                            (
                             select element o.o_orderkey
                             from  Orders as o
-                            where c.c_custkey = o.o_custkey and NOT like(o.o_comment,'%special%requests%')
+                            where c.c_custkey = o.o_custkey and o.o_comment NOT LIKE '%special%requests%'
                            )
                          ) AS o_orderkey_count
                 from  Customer c
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index 57dde0d..dc26e42 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -21,7 +21,7 @@
 
 
 select element 100.0 *
-     sum(`switch-case`(like(p.p_type,'PROMO%'),true,(l.l_extendedprice * (1 - l.l_discount)),false,0.0))
+     sum(`switch-case`(p.p_type like 'PROMO%',true,(l.l_extendedprice * (1 - l.l_discount)),false,0.0))
      / sum(l.l_extendedprice * (1 - l.l_discount))
 FROM  LineItem l,
       Part p
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index c96b23b..18a4d43 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -27,10 +27,10 @@
             SELECT p.p_brand p_brand, p.p_type p_type, p.p_size p_size, ps.ps_suppkey ps_suppkey
             FROM  Partsupp AS ps,
                   Part AS p
-            WHERE p.p_partkey = ps.ps_partkey AND p.p_brand != 'Brand#45' AND NOT like(p.p_type,'MEDIUM POLISHED%')
+            WHERE p.p_partkey = ps.ps_partkey AND p.p_brand != 'Brand#45' AND p.p_type not like 'MEDIUM POLISHED%'
            ) AS psp,
            Supplier AS s
-    WHERE psp.ps_suppkey = s.s_suppkey AND NOT like(s.s_comment,'%Customer%Complaints%')
+    WHERE psp.ps_suppkey = s.s_suppkey AND s.s_comment not like '%Customer%Complaints%'
 )
 };
 
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index 499899d..cbb55c4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@ -40,7 +40,7 @@
         ) as srn
         where (srn.s_suppkey = ps.ps_suppkey)
     ) as pssrn
-    where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+    where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS'
 )
 };
 declare function tmp2() {
@@ -66,7 +66,7 @@
         ) as srn
         where (srn.s_suppkey = ps.ps_suppkey)
     ) as pssrn
-    where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+    where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS'
     /* +hash */
     group by pssrn.p_partkey as p_partkey
 )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 45888f3..24f4f46 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -32,7 +32,7 @@
         select element {'c_custkey':c.c_custkey,'o_orderkey_count':coll_count((
                 select element o.o_orderkey
                 from  Orders as o
-                where c.c_custkey = o.o_custkey and NOT like(o.o_comment,'%special%requests%')
+                where c.c_custkey = o.o_custkey and o.o_comment not like '%special%requests%'
             ))}
         from  Customer as c
     ) as co
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index 6c4e330..eb556b16 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -21,7 +21,7 @@
 
 
 select element (100.0 * tpch.coll_sum((
-      select element tpch.`switch-case`(tpch.like(i.p_type,'PROMO%'),true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
+      select element tpch.`switch-case`(i.p_type like 'PROMO%',true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
       from  lp as i
   )) / tpch.coll_sum((
       select element (i.l_extendedprice * (1 - i.l_discount))
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index a6a53bb..131c8a0 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -27,10 +27,10 @@
         select element {'p_brand':p.p_brand,'p_type':p.p_type,'p_size':p.p_size,'ps_suppkey':ps.ps_suppkey}
         from  Partsupp as ps,
               Part as p
-        where p.p_partkey = ps.ps_partkey and p.p_brand != 'Brand#45' and not like(p.p_type,'MEDIUM POLISHED%')
+        where p.p_partkey = ps.ps_partkey and p.p_brand != 'Brand#45' and p.p_type not like 'MEDIUM POLISHED%'
     ) as psp,
           Supplier as s
-    where psp.ps_suppkey = s.s_suppkey and not like(s.s_comment,'%Customer%Complaints%')
+    where psp.ps_suppkey = s.s_suppkey and s.s_comment not like '%Customer%Complaints%'
 )
 };
 select element {'p_brand':p_brand,'p_type':p_type,'p_size':p_size,'supplier_cnt':supplier_cnt}
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index edae05b..f6ad005 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@ -40,7 +40,7 @@
         ) as srn
         where (srn.s_suppkey = ps.ps_suppkey)
     ) as pssrn
-    where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+    where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS'
 )
 };
 declare function tmp2() {
@@ -66,7 +66,7 @@
         ) as srn
         where (srn.s_suppkey = ps.ps_suppkey)
     ) as pssrn
-    where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+    where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS'
     /* +hash */
     group by pssrn.p_partkey as p_partkey
 )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index c5ae516..e68fcc4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -32,7 +32,7 @@
         select element {'c_custkey':c.c_custkey,'o_orderkey_count':coll_count((
                 select element o.o_orderkey
                 from  Orders as o
-                where c.c_custkey = o.o_custkey and NOT like(o.o_comment,'%special%requests%')
+                where c.c_custkey = o.o_custkey and o.o_comment not like '%special%requests%'
             ))}
         from  Customer as c
     ) as co
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index bcb5d55..ba7de7d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -21,7 +21,7 @@
 
 
 select element (100.0 * COLL_SUM((
-      select element tpch.`switch-case`(LIKE(i.p_type,'PROMO%'),true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
+      select element tpch.`switch-case`(i.p_type like 'PROMO%',true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
       from  lp as i
   )) / COLL_SUM((
       select element (i.l_extendedprice * (1 - i.l_discount))
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index 481d4d1..0cdbcfd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -27,10 +27,10 @@
         select element {'p_brand':p.p_brand,'p_type':p.p_type,'p_size':p.p_size,'ps_suppkey':ps.ps_suppkey}
         from  Partsupp as ps,
               Part as p
-        where p.p_partkey = ps.ps_partkey and p.p_brand != 'Brand#45' and not like(p.p_type,'MEDIUM POLISHED%')
+        where p.p_partkey = ps.ps_partkey and p.p_brand != 'Brand#45' and p.p_type not like 'MEDIUM POLISHED%'
     ) as psp,
           Supplier as s
-    where psp.ps_suppkey = s.s_suppkey and NOT LIKE(s.s_comment,'%Customer%Complaints%')
+    where psp.ps_suppkey = s.s_suppkey and s.s_comment not like '%Customer%Complaints%'
 )
 };
 select element {'p_brand':p_brand,'p_type':p_type,'p_size':p_size,'supplier_cnt':supplier_cnt}
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/in/in.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/in/in.1.adm
new file mode 100644
index 0000000..c9e9c6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/in/in.1.adm
@@ -0,0 +1,2 @@
+{ "customer_name": 10 }
+{ "customer_name": 85 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/not_in/not_in.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/not_in/not_in.1.adm
new file mode 100644
index 0000000..b01a98a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/not_in/not_in.1.adm
@@ -0,0 +1,4 @@
+{ "customer_name": 42 }
+{ "customer_name": 108 }
+{ "customer_name": 123 }
+{ "customer_name": 138 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_01/like_01.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_01/like_01.3.ast
index ca602eb..ee361dd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_01/like_01.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_01/like_01.3.ast
@@ -2,12 +2,14 @@
 Query:
 SELECT ELEMENT [
 OrderedListConstructor [
-  FunctionCall test.like@2[
+  OperatorExpr [
     LiteralExpr [STRING] [A6BBB]
+    like
     LiteralExpr [STRING] [_6%]
   ]
-  FunctionCall test.like@2[
+  OperatorExpr [
     LiteralExpr [STRING] [A8BBB]
+    like
     LiteralExpr [STRING] [_6%]
   ]
 ]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_null/like_null.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_null/like_null.3.ast
index 6cf5fca..a829f6b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_null/like_null.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/string/like_null/like_null.3.ast
@@ -4,16 +4,18 @@
   (
     LiteralExpr [STRING] [field1]
     :
-    FunctionCall test.like@2[
+    OperatorExpr [
       LiteralExpr [STRING] [A8BBB]
+      like
       LiteralExpr [NULL]
     ]
   )
   (
     LiteralExpr [STRING] [field2]
     :
-    FunctionCall test.like@2[
+    OperatorExpr [
       LiteralExpr [NULL]
+      like
       LiteralExpr [STRING] [_6%]
     ]
   )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast
index 0fa04eb..72ac671 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast
@@ -313,11 +313,12 @@
           ]
         ]
         and
-        FunctionCall tpch.like@2[
+        OperatorExpr [
           FieldAccessor [
             Variable [ Name=$p ]
             Field=p_type
           ]
+          like
           LiteralExpr [STRING] [%BRASS]
         ]
       ]
@@ -588,11 +589,12 @@
           ]
         ]
         and
-        FunctionCall tpch.like@2[
+        OperatorExpr [
           FieldAccessor [
             Variable [ Name=$p ]
             Field=p_type
           ]
+          like
           LiteralExpr [STRING] [%BRASS]
         ]
       ]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.ast
index 38af625..4ab59b1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.ast
@@ -83,14 +83,13 @@
                       ]
                     ]
                     and
-                    FunctionCall null.not@1[
-                      FunctionCall tpch.like@2[
-                        FieldAccessor [
-                          Variable [ Name=$o ]
-                          Field=o_comment
-                        ]
-                        LiteralExpr [STRING] [%special%requests%]
+                    OperatorExpr [
+                      FieldAccessor [
+                        Variable [ Name=$o ]
+                        Field=o_comment
                       ]
+                      not_like
+                      LiteralExpr [STRING] [%special%requests%]
                     ]
                   ]
               )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.ast
index 1f8c630..f9f5c62 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.ast
@@ -8,11 +8,12 @@
     (
       SELECT ELEMENT [
       FunctionCall tpch.switch-case@5[
-        FunctionCall tpch.like@2[
+        OperatorExpr [
           FieldAccessor [
             Variable [ Name=$i ]
             Field=p_type
           ]
+          like
           LiteralExpr [STRING] [PROMO%]
         ]
         LiteralExpr [TRUE]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast
index e00c9c7..aef4422 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast
@@ -103,14 +103,13 @@
               LiteralExpr [STRING] [Brand#45]
             ]
             and
-            FunctionCall null.not@1[
-              FunctionCall tpch.like@2[
-                FieldAccessor [
-                  Variable [ Name=$p ]
-                  Field=p_type
-                ]
-                LiteralExpr [STRING] [MEDIUM POLISHED%]
+            OperatorExpr [
+              FieldAccessor [
+                Variable [ Name=$p ]
+                Field=p_type
               ]
+              not_like
+              LiteralExpr [STRING] [MEDIUM POLISHED%]
             ]
           ]
       )
@@ -133,14 +132,13 @@
           ]
         ]
         and
-        FunctionCall null.not@1[
-          FunctionCall tpch.like@2[
-            FieldAccessor [
-              Variable [ Name=$s ]
-              Field=s_comment
-            ]
-            LiteralExpr [STRING] [%Customer%Complaints%]
+        OperatorExpr [
+          FieldAccessor [
+            Variable [ Name=$s ]
+            Field=s_comment
           ]
+          not_like
+          LiteralExpr [STRING] [%Customer%Complaints%]
         ]
       ]
   )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast
index 0fa04eb..72ac671 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.ast
@@ -313,11 +313,12 @@
           ]
         ]
         and
-        FunctionCall tpch.like@2[
+        OperatorExpr [
           FieldAccessor [
             Variable [ Name=$p ]
             Field=p_type
           ]
+          like
           LiteralExpr [STRING] [%BRASS]
         ]
       ]
@@ -588,11 +589,12 @@
           ]
         ]
         and
-        FunctionCall tpch.like@2[
+        OperatorExpr [
           FieldAccessor [
             Variable [ Name=$p ]
             Field=p_type
           ]
+          like
           LiteralExpr [STRING] [%BRASS]
         ]
       ]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.ast
index 38af625..4ab59b1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.ast
@@ -83,14 +83,13 @@
                       ]
                     ]
                     and
-                    FunctionCall null.not@1[
-                      FunctionCall tpch.like@2[
-                        FieldAccessor [
-                          Variable [ Name=$o ]
-                          Field=o_comment
-                        ]
-                        LiteralExpr [STRING] [%special%requests%]
+                    OperatorExpr [
+                      FieldAccessor [
+                        Variable [ Name=$o ]
+                        Field=o_comment
                       ]
+                      not_like
+                      LiteralExpr [STRING] [%special%requests%]
                     ]
                   ]
               )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.ast
index 1f8c630..f9f5c62 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.ast
@@ -8,11 +8,12 @@
     (
       SELECT ELEMENT [
       FunctionCall tpch.switch-case@5[
-        FunctionCall tpch.like@2[
+        OperatorExpr [
           FieldAccessor [
             Variable [ Name=$i ]
             Field=p_type
           ]
+          like
           LiteralExpr [STRING] [PROMO%]
         ]
         LiteralExpr [TRUE]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast
index e00c9c7..aef4422 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.ast
@@ -103,14 +103,13 @@
               LiteralExpr [STRING] [Brand#45]
             ]
             and
-            FunctionCall null.not@1[
-              FunctionCall tpch.like@2[
-                FieldAccessor [
-                  Variable [ Name=$p ]
-                  Field=p_type
-                ]
-                LiteralExpr [STRING] [MEDIUM POLISHED%]
+            OperatorExpr [
+              FieldAccessor [
+                Variable [ Name=$p ]
+                Field=p_type
               ]
+              not_like
+              LiteralExpr [STRING] [MEDIUM POLISHED%]
             ]
           ]
       )
@@ -133,14 +132,13 @@
           ]
         ]
         and
-        FunctionCall null.not@1[
-          FunctionCall tpch.like@2[
-            FieldAccessor [
-              Variable [ Name=$s ]
-              Field=s_comment
-            ]
-            LiteralExpr [STRING] [%Customer%Complaints%]
+        OperatorExpr [
+          FieldAccessor [
+            Variable [ Name=$s ]
+            Field=s_comment
           ]
+          not_like
+          LiteralExpr [STRING] [%Customer%Complaints%]
         ]
       ]
   )
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index f2d5704..944565c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -5230,6 +5230,21 @@
         <output-dir compare="Text">decorrelate_with_unique_id_2</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="subquery">
+      <compilation-unit name="in">
+        <output-dir compare="Text">in</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="subquery">
+      <compilation-unit name="in_correlated">
+        <output-dir compare="Text">in</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="subquery">
+      <compilation-unit name="not_in">
+        <output-dir compare="Text">not_in</output-dir>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="subset-collection">
     <test-case FilePath="subset-collection">
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/OperatorExpr.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/OperatorExpr.java
index 65422b2..8f2374f 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/OperatorExpr.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/OperatorExpr.java
@@ -35,9 +35,9 @@
 
     public OperatorExpr() {
         super();
-        exprList = new ArrayList<Expression>();
-        exprBroadcastIdx = new ArrayList<Integer>();
-        opList = new ArrayList<OperatorType>();
+        exprList = new ArrayList<>();
+        exprBroadcastIdx = new ArrayList<>();
+        opList = new ArrayList<>();
     }
 
     public OperatorExpr(List<Expression> exprList, List<Integer> exprBroadcastIdx, List<OperatorType> opList,
@@ -83,47 +83,78 @@
         exprList.add(operand);
     }
 
-    public final static boolean opIsComparison(OperatorType t) {
-        return t == OperatorType.EQ || t == OperatorType.NEQ || t == OperatorType.GT || t == OperatorType.GE
-                || t == OperatorType.LT || t == OperatorType.LE;
+    public static final boolean opIsComparison(OperatorType t) {
+        boolean cmp = t == OperatorType.EQ || t == OperatorType.NEQ || t == OperatorType.GT;
+        cmp = cmp || t == OperatorType.GE || t == OperatorType.LT || t == OperatorType.LE;
+        return cmp;
     }
 
     public void addOperator(String strOp) {
-        if ("or".equals(strOp)) {
-            opList.add(OperatorType.OR);
-        } else if ("and".equals(strOp)) {
-            opList.add(OperatorType.AND);
-        } else if ("<".equals(strOp)) {
-            opList.add(OperatorType.LT);
-        } else if (">".equals(strOp)) {
-            opList.add(OperatorType.GT);
-        } else if ("<=".equals(strOp)) {
-            opList.add(OperatorType.LE);
-        } else if ("<=".equals(strOp)) {
-            opList.add(OperatorType.LE);
-        } else if (">=".equals(strOp)) {
-            opList.add(OperatorType.GE);
-        } else if ("=".equals(strOp)) {
-            opList.add(OperatorType.EQ);
-        } else if ("!=".equals(strOp)) {
-            opList.add(OperatorType.NEQ);
-        } else if ("+".equals(strOp)) {
-            opList.add(OperatorType.PLUS);
-        } else if ("-".equals(strOp)) {
-            opList.add(OperatorType.MINUS);
-        } else if ("*".equals(strOp)) {
-            opList.add(OperatorType.MUL);
-        } else if ("/".equals(strOp)) {
-            opList.add(OperatorType.DIV);
-        } else if ("%".equals(strOp)) {
-            opList.add(OperatorType.MOD);
-        } else if ("^".equals(strOp)) {
-            opList.add(OperatorType.CARET);
-        } else if ("idiv".equals(strOp)) {
-            opList.add(OperatorType.IDIV);
-        } else if ("~=".equals(strOp)) {
-            opList.add(OperatorType.FUZZY_EQ);
+        switch (strOp) {
+            case "or":
+                opList.add(OperatorType.OR);
+                break;
+            case "and":
+                opList.add(OperatorType.AND);
+                break;
+            case "<":
+                opList.add(OperatorType.LT);
+                break;
+            case ">":
+                opList.add(OperatorType.GT);
+                break;
+            case "<=":
+                opList.add(OperatorType.LE);
+                break;
+            case ">=":
+                opList.add(OperatorType.GE);
+                break;
+            case "=":
+                opList.add(OperatorType.EQ);
+                break;
+            case "!=":
+                opList.add(OperatorType.NEQ);
+                break;
+            case "+":
+                opList.add(OperatorType.PLUS);
+                break;
+            case "-":
+                opList.add(OperatorType.MINUS);
+                break;
+            case "*":
+                opList.add(OperatorType.MUL);
+                break;
+            case "/":
+                opList.add(OperatorType.DIV);
+                break;
+            case "%":
+                opList.add(OperatorType.MOD);
+                break;
+            case "^":
+                opList.add(OperatorType.CARET);
+                break;
+            case "idiv":
+                opList.add(OperatorType.IDIV);
+                break;
+            case "~=":
+                opList.add(OperatorType.FUZZY_EQ);
+                break;
+            case "like":
+                opList.add(OperatorType.LIKE);
+                break;
+            case "not_like":
+                opList.add(OperatorType.NOT_LIKE);
+                break;
+            case "in":
+                opList.add(OperatorType.IN);
+                break;
+            case "not_in":
+                opList.add(OperatorType.NOT_IN);
+                break;
+            default:
+                throw new UnsupportedOperationException("Unsupported operator: " + strOp);
         }
+
     }
 
     @Override
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java
index d8d77ae2..9036413 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java
@@ -23,8 +23,9 @@
 import org.apache.asterix.lang.common.struct.VarIdentifier;
 
 public final class LangRewritingContext {
+    private int mark = 0;
     private int varCounter;
-    private HashMap<Integer, VarIdentifier> oldVarIdToNewVarId = new HashMap<Integer, VarIdentifier>();
+    private HashMap<Integer, VarIdentifier> oldVarIdToNewVarId = new HashMap<>();
 
     public LangRewritingContext(int varCounter) {
         this.varCounter = varCounter;
@@ -65,6 +66,14 @@
         return new VarIdentifier("#" + id, id);
     }
 
+    public void markCounter() {
+        mark = varCounter;
+    }
+
+    public void resetCounter() {
+        varCounter = mark;
+    }
+
     private int newId() {
         varCounter++;
         return varCounter;
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
index c063665..bb15ecd 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
@@ -35,7 +35,11 @@
     MOD("%"),
     CARET("^"),
     IDIV("idiv"), // integer divide
-    FUZZY_EQ("~=");
+    FUZZY_EQ("~="),
+    LIKE("like"),
+    NOT_LIKE("not_like"),
+    IN("in"),
+    NOT_IN("not_in");
 
     private final String symbol;
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
index 8f41015..0189711 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
@@ -40,6 +40,9 @@
         // Inlines WITH expressions.
         inlineWithExpressions();
 
+        // Rewrites like/not-like expressions.
+        rewriteOperatorExpression();
+
         // Rewrites SQL-92 global aggregations.
         rewriteGlobalAggregations();
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
index fab5c07..daffb97 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
@@ -51,6 +51,7 @@
 import org.apache.asterix.lang.sqlpp.parser.SqlppParserFactory;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.InlineColumnAliasVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.InlineWithExpressionVisitor;
+import org.apache.asterix.lang.sqlpp.rewrites.visitor.OperatorExpressionVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppBuiltinFunctionRewriteVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppGlobalAggregationSugarVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppGroupByVisitor;
@@ -87,6 +88,9 @@
     @Override
     public void rewrite(List<FunctionDecl> declaredFunctions, Query topExpr, AqlMetadataProvider metadataProvider,
             LangRewritingContext context) throws AsterixException {
+        // Marks the current variable counter.
+        context.markCounter();
+
         // Sets up parameters.
         setup(declaredFunctions, topExpr, metadataProvider, context);
 
@@ -96,6 +100,9 @@
         // Inlines WITH expressions.
         inlineWithExpressions();
 
+        // Rewrites like/not-like expressions.
+        rewriteOperatorExpression();
+
         // Rewrites SQL-92 global aggregations.
         rewriteGlobalAggregations();
 
@@ -113,6 +120,11 @@
         // names could be case sensitive.
         rewriteFunctionNames();
 
+        // Resets the variable counter to the previous marked value.
+        // Therefore, the variable ids in the final query plans will not be perturbed
+        // by the additon or removal of intermediate AST rewrites.
+        context.resetCounter();
+
         // Replace global variable access with the dataset function for inlined expressions.
         variableCheckAndRewrite(true);
 
@@ -149,6 +161,15 @@
         inlineWithExpressionVisitor.visit(topExpr, null);
     }
 
+    protected void rewriteOperatorExpression() throws AsterixException {
+        if (topExpr == null) {
+            return;
+        }
+        // Rewrites like/not-like operators into function call expressions.
+        OperatorExpressionVisitor likeExpressionVisitor = new OperatorExpressionVisitor(context);
+        likeExpressionVisitor.visit(topExpr, null);
+    }
+
     protected void inlineColumnAlias() throws AsterixException {
         if (topExpr == null) {
             return;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
new file mode 100644
index 0000000..c591d72
--- /dev/null
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
@@ -0,0 +1,98 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.asterix.lang.sqlpp.rewrites.visitor;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+
+import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.common.functions.FunctionSignature;
+import org.apache.asterix.lang.common.base.Expression;
+import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.expression.CallExpr;
+import org.apache.asterix.lang.common.expression.OperatorExpr;
+import org.apache.asterix.lang.common.expression.QuantifiedExpression;
+import org.apache.asterix.lang.common.expression.QuantifiedExpression.Quantifier;
+import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
+import org.apache.asterix.lang.common.struct.OperatorType;
+import org.apache.asterix.lang.common.struct.QuantifiedPair;
+import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppExpressionScopingVisitor;
+
+public class OperatorExpressionVisitor extends AbstractSqlppExpressionScopingVisitor {
+
+    public OperatorExpressionVisitor(LangRewritingContext context) {
+        super(context);
+    }
+
+    @Override
+    public Expression visit(OperatorExpr operatorExpr, ILangExpression arg) throws AsterixException {
+
+        List<Expression> newExprList = new ArrayList<>();
+        for (Expression expr : operatorExpr.getExprList()) {
+            newExprList.add(expr.accept(this, operatorExpr));
+        }
+        operatorExpr.setExprList(newExprList);
+        if (operatorExpr.getExprList().size() == 2) {
+            OperatorType opType = operatorExpr.getOpList().get(0);
+            switch (opType) {
+                case LIKE:
+                case NOT_LIKE:
+                    return processLikeOperator(operatorExpr, opType);
+                case IN:
+                case NOT_IN:
+                    return processInOperator(operatorExpr, opType);
+                default:
+                    break;
+            }
+        }
+        return operatorExpr;
+    }
+
+    private Expression processLikeOperator(OperatorExpr operatorExpr, OperatorType opType) {
+        Expression likeExpr = new CallExpr(new FunctionSignature(null, "like", 2), operatorExpr.getExprList());
+        if (opType == OperatorType.LIKE) {
+            return likeExpr;
+        }
+        return new CallExpr(new FunctionSignature(null, "not", 1),
+                new ArrayList<Expression>(Collections.singletonList(likeExpr)));
+    }
+
+    private Expression processInOperator(OperatorExpr operatorExpr, OperatorType opType) {
+        VariableExpr bindingVar = new VariableExpr(context.newVariable());
+        Expression itemExpr = operatorExpr.getExprList().get(0);
+        Expression collectionExpr = operatorExpr.getExprList().get(1);
+        OperatorExpr comparison = new OperatorExpr();
+        comparison.addOperand(itemExpr);
+        comparison.addOperand(bindingVar);
+        comparison.setCurrentop(true);
+        if (opType == OperatorType.IN) {
+            comparison.addOperator("=");
+            return new QuantifiedExpression(Quantifier.SOME, new ArrayList<QuantifiedPair>(
+                    Collections.singletonList(new QuantifiedPair(bindingVar, collectionExpr))), comparison);
+        } else {
+            comparison.addOperator("!=");
+            return new QuantifiedExpression(Quantifier.EVERY, new ArrayList<QuantifiedPair>(
+                    Collections.singletonList(new QuantifiedPair(bindingVar, collectionExpr))), comparison);
+        }
+    }
+
+}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index f587e50..ebcfeea 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -1662,6 +1662,7 @@
 
 Expression RelExpr()throws ParseException:
 {
+  boolean not = false;
   OperatorExpr op = null;
   Expression operand = null;
   boolean broadcast = false;
@@ -1679,24 +1680,28 @@
     }
 
     (
-      LOOKAHEAD(2)( <LT> | <GT> | <LE> | <GE> | <EQ> | <NE> |<SIMILAR>)
+      LOOKAHEAD(2)( <LT> | <GT> | <LE> | <GE> | <EQ> | <NE> |<SIMILAR> | (<NOT> { not = true; })? (<LIKE>|<IN>))
         {
           String mhint = getHint(token);
           if (mhint != null) {
             if (mhint.equals(INDEXED_NESTED_LOOP_JOIN_HINT)) {
-            annotation = IndexedNLJoinExpressionAnnotation.INSTANCE;
-          } else if (mhint.equals(SKIP_SECONDARY_INDEX_SEARCH_HINT)) {
-            annotation = SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE;
+                annotation = IndexedNLJoinExpressionAnnotation.INSTANCE;
+            } else if (mhint.equals(SKIP_SECONDARY_INDEX_SEARCH_HINT)) {
+                annotation = SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE;
+            }
           }
-        }
+          String operator = token.image.toLowerCase();
+          if(not){
+            operator = "not_" + operator;
+          }
           if (op == null) {
             op = new OperatorExpr();
             op.addOperand(operand, broadcast);
-          op.setCurrentop(true);
-          broadcast = false;
+            op.setCurrentop(true);
+            broadcast = false;
           }
-        op.addOperator(token.image);
-      }
+          op.addOperator(operator);
+        }
 
        operand = IsExpr()
       {
@@ -2770,6 +2775,7 @@
   | <ASC : "asc">
   | <AT : "at">
   | <AUTOGENERATED : "autogenerated">
+  | <BETWEEN : "between">
   | <BTREE : "btree">
   | <BY : "by">
   | <CASE : "case">
@@ -2823,6 +2829,7 @@
   | <LEFT : "left">
   | <LETTING : "letting">
   | <LET : "let">
+  | <LIKE : "like">
   | <LIMIT : "limit">
   | <LOAD : "load">
   | <NEST : "nest">