diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.1.query.sqlpp
new file mode 100644
index 0000000..9f94406
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.1.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test IN (scalar) in SQL-compat mode
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select value r
+from range(0, 3) r
+where r in (2)
+order by r;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.2.query.sqlpp
new file mode 100644
index 0000000..9a9c282
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.2.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test IN scalar in SQL-compat mode
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select value r
+from range(0, 3) r
+where r in 2
+order by r;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.3.query.sqlpp
new file mode 100644
index 0000000..c6be9b3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test NOT IN (scalar) in SQL-compat mode
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select value r
+from range(0, 3) r
+where r not in (2)
+order by r;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.4.query.sqlpp
new file mode 100644
index 0000000..e706c07
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/in_non_list_01/in_non_list_01.4.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test NOT IN scalar in SQL-compat mode
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select value r
+from range(0, 3) r
+where r not in 2
+order by r;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.1.adm
new file mode 100644
index 0000000..d8263ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.1.adm
@@ -0,0 +1 @@
+2
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.2.adm
new file mode 100644
index 0000000..d8263ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.2.adm
@@ -0,0 +1 @@
+2
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.3.adm
new file mode 100644
index 0000000..3237a60
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.3.adm
@@ -0,0 +1,3 @@
+0
+1
+3
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.4.adm
new file mode 100644
index 0000000..3237a60
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/in_non_list_01/in_non_list_01.4.adm
@@ -0,0 +1,3 @@
+0
+1
+3
\ No newline at end of file
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 d1f19ea..27575fb 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -10142,6 +10142,11 @@
         <expected-error>ASX1168: Ambiguous projection in SELECT clause (in line 32, at column 8)</expected-error>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="in_non_list_01">
+        <output-dir compare="Text">in_non_list_01</output-dir>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="statement-params">
     <test-case FilePath="statement-params">
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
index 69eb156..8a701e7 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
@@ -19,16 +19,27 @@
 
 package org.apache.asterix.lang.sqlpp.rewrites.visitor;
 
+import java.util.ArrayList;
+import java.util.List;
+
 import org.apache.asterix.common.exceptions.CompilationException;
+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.struct.OperatorType;
 import org.apache.asterix.lang.sqlpp.clause.Projection;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppSimpleExpressionVisitor;
+import org.apache.asterix.om.functions.BuiltinFunctions;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+import org.apache.hyracks.api.exceptions.SourceLocation;
 
 /**
  * Applies initial rewritings for "SQL-compatible" evaluation mode
  * <ol>
- * <li>Rewrites "SELECT *" into "SELECT *.*".
+ * <li>Rewrites {@code SELECT *} into {@code SELECT *.*}
+ * <li>Rewrites {@code NOT? IN expr} into {@code NOT? IN to_array(expr)} if {@code expr} can return a non-list
  * </ol>
  */
 public final class SqlCompatRewriteVisitor extends AbstractSqlppSimpleExpressionVisitor {
@@ -40,4 +51,44 @@
         }
         return super.visit(projection, arg);
     }
+
+    @Override
+    public Expression visit(OperatorExpr opExpr, ILangExpression arg) throws CompilationException {
+        List<OperatorType> opTypeList = opExpr.getOpList();
+        if (opTypeList.size() == 1) {
+            switch (opTypeList.get(0)) {
+                case IN:
+                case NOT_IN:
+                    List<Expression> exprList = opExpr.getExprList();
+                    Expression arg1 = exprList.get(1);
+                    if (!alwaysReturnsList(arg1)) {
+                        List<Expression> newExprList = new ArrayList<>(2);
+                        newExprList.add(exprList.get(0));
+                        newExprList.add(createCallExpr(BuiltinFunctions.TO_ARRAY, arg1, opExpr.getSourceLocation()));
+                        opExpr.setExprList(newExprList);
+                    }
+                    break;
+            }
+        }
+        return super.visit(opExpr, arg);
+    }
+
+    private static boolean alwaysReturnsList(Expression expr) {
+        switch (expr.getKind()) {
+            case LIST_CONSTRUCTOR_EXPRESSION:
+            case LIST_SLICE_EXPRESSION:
+            case SELECT_EXPRESSION:
+                return true;
+            default:
+                return false;
+        }
+    }
+
+    private static CallExpr createCallExpr(FunctionIdentifier fid, Expression inExpr, SourceLocation sourceLoc) {
+        List<Expression> argList = new ArrayList<>(1);
+        argList.add(inExpr);
+        CallExpr callExpr = new CallExpr(new FunctionSignature(fid), argList);
+        callExpr.setSourceLocation(sourceLoc);
+        return callExpr;
+    }
 }