[NO ISSUE][COMP] Subquery coercion in SQL-compat mode - user model changes: no - storage format changes: no - interface changes: no Details: - Implement subquery coercion rules in SQL-compat mode - Add runtime for scalar-first-element() - Change LogicalComplexBinaryComparator to compare multisets using array comparator instead of raw byte comparator - Add tests Change-Id: I44c50eda32e6e235fe92de10687cb90398040286 Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/13964 Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu> Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com> Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>

commit: 81771df2076de444c6ba5747091efd17124ee2ed [log] [tgz]
author: Dmitry Lychagin <dmitry.lychagin@couchbase.com> Fri Nov 05 17:12:19 2021 -0700
committer: Dmitry Lychagin <dmitry.lychagin@couchbase.com> Wed Nov 10 15:53:23 2021 +0000
tree: b03a1fc235fd9dd0caf040792a08390223e7d81d
parent: 8d6cf7574430de4f4049fbf5e6726cf2ede5d8aa [diff]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.1.query.sqlpp
new file mode 100644
index 0000000..681abe2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.1.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * SELECT subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1, (select max(r2) from range(0, r1) r2) r3
+from range(1,3) r1
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.2.query.sqlpp
new file mode 100644
index 0000000..de72fe5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.2.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * Subquery that does not return a single tuple is coerced to MISSING
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1, (select r2 from range(0, r1) r2 where r2 > 1) is missing r3
+from range(1,3) r1
+order by r1;
+

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.3.query.sqlpp
new file mode 100644
index 0000000..5776ef4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.3.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * WHERE subquery ...
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1,3) r1
+where (select max(r2) > 0 from range(0, r1) r2)
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.4.query.sqlpp
new file mode 100644
index 0000000..6c0bc36
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.4.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * GROUP BY subquery ...
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select x, array_sort(array_agg(r1)) r1
+from range(1,3) r1
+group by ((select max(r2) > 1 from range(0, r1) r2) x)
+order by x;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.5.query.sqlpp
new file mode 100644
index 0000000..f3fe9ab
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.5.query.sqlpp

@@ -0,0 +1,36 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * HAVING subquery ...
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select k
+from range(1,6) r1
+group by r1 % 3 k group as g
+having (select max(gi.r1) != 6 from g gi)
+order by k;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.6.query.sqlpp
new file mode 100644
index 0000000..6d8c526
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.6.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * Subquery in arithmetic operator
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1,
+  (select max(r2) from range(0, r1+1) r2) - (select max(r3) from range(0, r1) r3) r2
+from range(1,3) r1
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.7.query.sqlpp
new file mode 100644
index 0000000..5bf519f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.7.query.sqlpp

@@ -0,0 +1,33 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ *
+ * WITH var AS subquery, LET var = subquery -- no coercion
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+with r4 as (select max(r5) from range(0, 2) r5)
+select r1, r3, r4
+from range(1,3) r1
+let r3 = (select max(r2) from range(0, r1) r2)
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.8.query.sqlpp
new file mode 100644
index 0000000..682ba12
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.8.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * SELECT VALUE subquery is not coerced
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1, (select value max(r2) from range(0, r1) r2) r3
+from range(1,3) r1
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.9.query.sqlpp
new file mode 100644
index 0000000..a6f397e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.9.query.sqlpp

@@ -0,0 +1,43 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * Subquery has UNION ALL with ORDER BY / LIMIT
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1,
+(
+  select max(r2) m2
+  from range(0, r1+1) r2
+  union all
+  select max(r3) m2
+  from range(0, r1) r3
+  order by m2
+  limit 1
+) r3
+from range(1,3) r1
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.1.query.sqlpp
new file mode 100644
index 0000000..f379f5d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.1.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * FAILURE: subquery returns more than one field
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1, (select min(r2), max(r2) from range(0, r1) r2) r3
+from range(1,3) r1
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.2.query.sqlpp
new file mode 100644
index 0000000..57aab66
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.2.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * FAILURE: SELECT * is not supported in subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1, (select * from range(0, r1) r2, range(0, r1+1) r3)
+from range(1,3) r1
+order by r1;
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.3.query.sqlpp
new file mode 100644
index 0000000..63a4a20
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.3.query.sqlpp

@@ -0,0 +1,40 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * FAILURE: UNION ALL between SELECT and SELECT VALUE
+ * (this is currently not supported)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1,
+(
+   select r2 from range(0, 1) r2 where r2 < 1
+   union all
+   select value r3 from range(0, 1) r3 where r3 > 1
+)
+from range(1, 3) r1
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.4.query.sqlpp
new file mode 100644
index 0000000..08c8bce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_02_scalar_negative/subquery_coercion_02_scalar_negative.4.query.sqlpp

@@ -0,0 +1,39 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * FAILURE: UNION ALL between SELECT and subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1,
+(
+  select r2 from range(0, 1) r2 where r2 < 1
+  union all
+  (let x = 1 select x)
+)
+from range(1, 3) r1
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.1.query.sqlpp
new file mode 100644
index 0000000..06beeea
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.1.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 3) r1
+where r1 = (select null_if(max(r2), 2) from range(0, r1) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.10.query.sqlpp
new file mode 100644
index 0000000..1b533f2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.10.query.sqlpp

@@ -0,0 +1,40 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * Test when the subquery returns more than one tuple
+ * (coercion rewriting should produce MISSING and therefore an empty result)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*) cnt
+from [
+  { "r": 1, "x": [ { "y": 0 }, { "y": 1 } ] },
+  { "r": 2, "x": [ { "y": 0 }, { "y": 1 }, { "y": 2 } ] },
+  { "r": 3, "x": [ { "y": 0 }, { "y": 1 }, { "y": 2 }, { "y": 3 } ] }
+] v
+where v.x = (select y from range(0, v.r) y);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.11.query.sqlpp
new file mode 100644
index 0000000..adcd52e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.11.query.sqlpp

@@ -0,0 +1,40 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = subquery, rhs = expr
+ *
+ * Test when the subquery returns more than one tuple
+ * (coercion rewriting should produce MISSING)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*) cnt
+from [
+  { "r": 1, "x": [ { "y": 0 }, { "y": 1 } ] },
+  { "r": 2, "x": [ { "y": 0 }, { "y": 1 }, { "y": 2 } ] },
+  { "r": 3, "x": [ { "y": 0 }, { "y": 1 }, { "y": 2 }, { "y": 3 } ] }
+] v
+where (select y from range(0, v.r) y) is not distinct from missing;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.12.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.12.query.sqlpp
new file mode 100644
index 0000000..01de9f4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.12.query.sqlpp

@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * NO COERCION for SELECT VALUE
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 3) r1
+let y = [{"x": r1}]
+where y = (select value { "x" : null_if(max(r2), 2) } from range(0, r1) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.13.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.13.query.sqlpp
new file mode 100644
index 0000000..f3f3277
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.13.query.sqlpp

@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit array constructor expression
+ *
+ * lhs = (e1,e2), rhs = subquery
+ *
+ * NO COERCION for SELECT VALUE
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where ({"x": r1, "y": r1+1}, {"x": r1+1, "y": r1+2})
+    = (select value { "x": r2 - 1, "y": r2 } from range(2, 3) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.14.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.14.query.sqlpp
new file mode 100644
index 0000000..d41e202
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.14.query.sqlpp

@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit array constructor expression
+ *
+ * lhs = (e1,e2), rhs = subquery
+ *
+ * NO COERCION for UNION ALL of SELECT VALUE
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where ({"x": r1, "y": r1+1}, {"x": r1+1, "y": r1+2})
+    = (
+        select value { "x": r2 - 1, "y": r2 } from range(2, 3) r2 where r2 < 3
+        union all
+        select value { "x": r2 - 1, "y": r2 } from range(2, 3) r2 where r2 > 2
+      )
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.2.query.sqlpp
new file mode 100644
index 0000000..00e2a27
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.2.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = subquery, rhs = expr
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 3) r1
+where (select null_if(max(r2), 2) from range(0, r1) r2) = r1
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.3.query.sqlpp
new file mode 100644
index 0000000..820e920
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.3.query.sqlpp

@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = subquery, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 3) r1
+where
+  (select null_if(max(r2), 2) from range(0, r1) r2)
+  =
+  (select null_if(max(-r2), 2) from range(-r1, 0) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.4.query.sqlpp
new file mode 100644
index 0000000..b4f402e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.4.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit array constructor expression
+ *
+ * lhs = (e1,e2), rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where (r1, r1+1) = (select r2 - 1, r2 from range(2, 3) r2 where r2 < 3)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.5.query.sqlpp
new file mode 100644
index 0000000..99d120c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.5.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit array constructor expression
+ *
+ * lhs = subquery, rhs = (e1,e2)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where (select r2 - 1, r2 from range(2, 3) r2 where r2 < 3) = (r1, r1+1)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.6.query.sqlpp
new file mode 100644
index 0000000..c5ebf84
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.6.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit array constructor expression
+ *
+ * lhs = [e1,e2], rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where [r1, r1+1] = (select r2 - 1, r2 from range(2, 3) r2 where r2 < 3)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.7.query.sqlpp
new file mode 100644
index 0000000..93338c6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.7.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit array constructor expression
+ *
+ * lhs = subquery, rhs = [e1,e2]
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where (select r2 - 1, r2 from range(2, 3) r2 where r2 < 3) = [r1, r1+1]
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.8.query.sqlpp
new file mode 100644
index 0000000..13c2439
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.8.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit multiset constructor expression
+ *
+ * lhs = {{e1,e2}}, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where {{r1, r1+1}} = (select r2 - 1, r2 from range(2, 3) r2 where r2 < 3)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.9.query.sqlpp
new file mode 100644
index 0000000..c131067
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.9.query.sqlpp

@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into an array when it's compared
+ * with an explicit multiset constructor expression
+ *
+ * lhs = subquery, rhs = {{e1,e2}}
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(0, 1) r1
+where (select r2 - 1, r2 from range(2, 3) r2 where r2 < 3) = {{r1, r1+1}}
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.1.query.sqlpp
new file mode 100644
index 0000000..b50b5fa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.1.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: subquery returns more than one field
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 = (select r2, r2+1 from range(0, r1) r2);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.2.query.sqlpp
new file mode 100644
index 0000000..73bec19
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.2.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = subquery, rhs = expr
+ *
+ * FAILURE: subquery returns more than one field
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where (select r2, r2+1 from range(0, r1) r2) = r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.3.query.sqlpp
new file mode 100644
index 0000000..d15b588
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.3.query.sqlpp

@@ -0,0 +1,40 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: UNION ALL between SELECT and SELECT VALUE
+ * (this is currently not supported)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 = (
+  select r2 from range(0, 1) r2 where r2 < 1
+  union all
+  select value r3 from range(0, 1) r3 where r3 > 1
+);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.4.query.sqlpp
new file mode 100644
index 0000000..8b61916
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.4.query.sqlpp

@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: UNION ALL between SELECT VALUE and SELECT
+ * (this is currently not supported)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 =
+(
+  select value r3 from range(0, 1) r3 where r3 > 1
+  union all
+  select r2 from range(0, 1) r2 where r2 < 1
+);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.5.query.sqlpp
new file mode 100644
index 0000000..ad05ecf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.5.query.sqlpp

@@ -0,0 +1,39 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: UNION ALL between SELECT and subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 = (
+  select r2 from range(0, 1) r2 where r2 < 1
+  union all
+  (let x = 1 select x)
+);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.6.query.sqlpp
new file mode 100644
index 0000000..5f55753
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.6.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: SELECT * is not supported in subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 = (select * from range(0, r1) r2);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.7.query.sqlpp
new file mode 100644
index 0000000..3e5dc56
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.7.query.sqlpp

@@ -0,0 +1,36 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: SELECT * is not supported in subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where
+  (select * from range(0, r1) r2) = (select * from range(0, r1) r3);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.8.query.sqlpp
new file mode 100644
index 0000000..2b2998e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_04_cmp_negative/subquery_coercion_04_cmp_negative.8.query.sqlpp

@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into array when it's compared
+ * with an expression which is an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: SELECT * is not supported in subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where
+  (r1, r1+1) =
+  (select * from range(0, r1) r2, range(0, r1+1) r3);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.1.query.sqlpp
new file mode 100644
index 0000000..7f9d531
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.1.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where r1 IN (select r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.10.query.sqlpp
new file mode 100644
index 0000000..ab2f0dc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.10.query.sqlpp

@@ -0,0 +1,39 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery UNION ALL subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 6) r1
+where r1 NOT IN (
+  select r2 from range(1, 2) r2
+  UNION ALL
+  select r3 from range(5, 6) r3
+)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.2.query.sqlpp
new file mode 100644
index 0000000..0eb8e05
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.2.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit array constructor
+ *
+ * lhs = (e1,e2), rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where (r1-1, r1) IN (select r2-1, r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.3.query.sqlpp
new file mode 100644
index 0000000..b1d48fc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.3.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit array constructor
+ *
+ * lhs = [e1,e2], rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where [r1-1, r1] IN (select r2-1, r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.4.query.sqlpp
new file mode 100644
index 0000000..0d8edc9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.4.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit multiset constructor
+ *
+ * lhs = {{e1,e2}}, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where {{r1-1, r1}} IN (select r2-1, r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.5.query.sqlpp
new file mode 100644
index 0000000..3886216
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.5.query.sqlpp

@@ -0,0 +1,39 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery UNION ALL subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 6) r1
+where r1 IN (
+  select r2 from range(1, 2) r2
+  UNION ALL
+  select r3 from range(5, 6) r3
+)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.6.query.sqlpp
new file mode 100644
index 0000000..b04b0e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.6.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where r1 NOT IN (select r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.7.query.sqlpp
new file mode 100644
index 0000000..9d8f18e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.7.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is an explicit array constructor
+ *
+ * lhs = (e1,e2), rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where (r1-1, r1) NOT IN (select r2-1, r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.8.query.sqlpp
new file mode 100644
index 0000000..cdde8e4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.8.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is an explicit array constructor
+ *
+ * lhs = [e1,e2], rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where [r1-1, r1] NOT IN (select r2-1, r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.9.query.sqlpp
new file mode 100644
index 0000000..db0c322
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.9.query.sqlpp

@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is an explicit multiset constructor
+ *
+ * lhs = {{e1,e2}}, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 5) r1
+where {{r1-1, r1}} NOT IN (select r2-1, r2 from range(0, r1-(r1 % 2)) r2)
+order by r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.1.query.sqlpp
new file mode 100644
index 0000000..72a003e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.1.query.sqlpp

@@ -0,0 +1,36 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: subquery returns more than one field
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 IN (select r2, r2+1 from range(0, r1) r2);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.2.query.sqlpp
new file mode 100644
index 0000000..f28d860
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.2.query.sqlpp

@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: subquery returns more than one field
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 NOT IN
+  (select r2, r2+1 from range(0, r1) r2);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.3.query.sqlpp
new file mode 100644
index 0000000..9642dff
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.3.query.sqlpp

@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: UNION ALL between SELECT and SELECT VALUE
+ * (this is currently not supported)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 IN (
+  select r2 from range(0, 1) r2 where r2 < 1
+  union all
+  select value r3 from range(0, 1) r3 where r3 > 1
+);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.4.query.sqlpp
new file mode 100644
index 0000000..d7bf919
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.4.query.sqlpp

@@ -0,0 +1,42 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the NOT IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: UNION ALL between SELECT VALUE and SELECT
+ * (this is currently not supported)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 NOT IN
+(
+  select value r2 from range(0, 1) r2 where r2 < 1
+  union all
+  select r3 from range(0, 1) r3 where r3 > 1
+);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.5.query.sqlpp
new file mode 100644
index 0000000..8cfb6f8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.5.query.sqlpp

@@ -0,0 +1,40 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: UNION ALL between SELECT and subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 IN (
+  select r2 from range(0, 1) r2 where r2 < 1
+  union all
+  (let x = 1 select x)
+);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.6.query.sqlpp
new file mode 100644
index 0000000..575b6d9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.6.query.sqlpp

@@ -0,0 +1,36 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: SELECT * is not supported in subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where r1 IN (select * from range(0, r1) r2);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.7.query.sqlpp
new file mode 100644
index 0000000..bee7d0b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_06_in_negative/subquery_coercion_06_in_negative.7.query.sqlpp

@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into array when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery
+ *
+ * FAILURE: SELECT * is not supported in subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select count(*)
+from range(1, 3) r1
+where
+  (r1, r1+1) IN (select * from range(0, r1) r2, range(0, r1+1) r3);

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.1.query.sqlpp
new file mode 100644
index 0000000..c1feeb3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test no subquery coercion if the subquery is in the FROM clause
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r2.r1
+from (select r1 from range(1, 3) r1) r2
+order by r2.r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.2.query.sqlpp
new file mode 100644
index 0000000..bdefbdd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.2.query.sqlpp

@@ -0,0 +1,31 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test no subquery coercion if the subquery is in the JOIN clause
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r2.nr1, r4.nr3
+from (select r1, -r1 as nr1 from range(1, 4) r1) r2
+join (select r3, -r3 as nr3 from range(2, 5) r3) r4 on r2.r1 = r4.r3
+order by r2.nr1 desc;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.3.query.sqlpp
new file mode 100644
index 0000000..36b88d7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.3.query.sqlpp

@@ -0,0 +1,31 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test no subquery coercion if the subquery is in the UNNEST clause
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select v1.x, v2.y
+from (select x, [x, x+1, x+2] as y from range(1, 3) x) v1
+unnest (select y from v1.y) v2
+order by v1.x, v2.y;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.4.query.sqlpp
new file mode 100644
index 0000000..833d06e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.4.query.sqlpp

@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test no subquery coercion if the subquery is in the IN clause
+ * of a quantified expression
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r2.r1
+from (select r1 from range(1, 3) r1) r2
+where some r3 in (select r4 from range(0, 2) r4) satisfies r3.r4 = r2.r1
+order by r2.r1;

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.1.adm
new file mode 100644
index 0000000..b56511d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.1.adm

@@ -0,0 +1,3 @@
+{ "r1": 1, "r3": 1 }
+{ "r1": 2, "r3": 2 }
+{ "r1": 3, "r3": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.2.adm
new file mode 100644
index 0000000..ccb7162
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.2.adm

@@ -0,0 +1,3 @@
+{ "r1": 1, "r3": true }
+{ "r1": 2, "r3": false }
+{ "r1": 3, "r3": true }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.3.adm
new file mode 100644
index 0000000..572906d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.3.adm

@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 2 }
+{ "r1": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.4.adm
new file mode 100644
index 0000000..b691ed70d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.4.adm

@@ -0,0 +1,2 @@
+{ "r1": [ 1 ], "x": false }
+{ "r1": [ 2, 3 ], "x": true }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.5.adm
new file mode 100644
index 0000000..2b2c0bb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.5.adm

@@ -0,0 +1,2 @@
+{ "k": 1 }
+{ "k": 2 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.6.adm
new file mode 100644
index 0000000..9abbfb0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.6.adm

@@ -0,0 +1,3 @@
+{ "r1": 1, "r2": 1 }
+{ "r1": 2, "r2": 1 }
+{ "r1": 3, "r2": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.7.adm
new file mode 100644
index 0000000..2f0a933
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.7.adm

@@ -0,0 +1,3 @@
+{ "r1": 1, "r3": [ { "$2": 1 } ], "r4": [ { "$1": 2 } ] }
+{ "r1": 2, "r3": [ { "$2": 2 } ], "r4": [ { "$1": 2 } ] }
+{ "r1": 3, "r3": [ { "$2": 3 } ], "r4": [ { "$1": 2 } ] }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.8.adm
new file mode 100644
index 0000000..69413bd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.8.adm

@@ -0,0 +1,3 @@
+{ "r1": 1, "r3": [ 1 ] }
+{ "r1": 2, "r3": [ 2 ] }
+{ "r1": 3, "r3": [ 3 ] }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.9.adm
new file mode 100644
index 0000000..b56511d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.9.adm

@@ -0,0 +1,3 @@
+{ "r1": 1, "r3": 1 }
+{ "r1": 2, "r3": 2 }
+{ "r1": 3, "r3": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.1.adm
new file mode 100644
index 0000000..29cc57e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.1.adm

@@ -0,0 +1,2 @@
+{ "r1": 1 }
+{ "r1": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.10.adm
new file mode 100644
index 0000000..bacb60c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.10.adm

@@ -0,0 +1 @@
+{ "cnt": 0 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.11.adm
new file mode 100644
index 0000000..6280446
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.11.adm

@@ -0,0 +1 @@
+{ "cnt": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.12.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.12.adm
new file mode 100644
index 0000000..29cc57e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.12.adm

@@ -0,0 +1,2 @@
+{ "r1": 1 }
+{ "r1": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.13.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.13.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.13.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.14.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.14.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.14.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.2.adm
new file mode 100644
index 0000000..29cc57e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.2.adm

@@ -0,0 +1,2 @@
+{ "r1": 1 }
+{ "r1": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.3.adm
new file mode 100644
index 0000000..29cc57e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.3.adm

@@ -0,0 +1,2 @@
+{ "r1": 1 }
+{ "r1": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.4.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.4.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.5.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.5.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.6.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.6.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.7.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.7.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.8.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.8.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.9.adm
new file mode 100644
index 0000000..7dd2c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.9.adm

@@ -0,0 +1 @@
+{ "r1": 1 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.1.adm
new file mode 100644
index 0000000..6372bdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.1.adm

@@ -0,0 +1,2 @@
+{ "r1": 2 }
+{ "r1": 4 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.10.adm
new file mode 100644
index 0000000..bcec791
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.10.adm

@@ -0,0 +1,2 @@
+{ "r1": 3 }
+{ "r1": 4 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.2.adm
new file mode 100644
index 0000000..6372bdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.2.adm

@@ -0,0 +1,2 @@
+{ "r1": 2 }
+{ "r1": 4 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.3.adm
new file mode 100644
index 0000000..6372bdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.3.adm

@@ -0,0 +1,2 @@
+{ "r1": 2 }
+{ "r1": 4 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.4.adm
new file mode 100644
index 0000000..6372bdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.4.adm

@@ -0,0 +1,2 @@
+{ "r1": 2 }
+{ "r1": 4 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.5.adm
new file mode 100644
index 0000000..776e930
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.5.adm

@@ -0,0 +1,4 @@
+{ "r1": 1 }
+{ "r1": 2 }
+{ "r1": 5 }
+{ "r1": 6 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.6.adm
new file mode 100644
index 0000000..f90118e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.6.adm

@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 3 }
+{ "r1": 5 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.7.adm
new file mode 100644
index 0000000..f90118e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.7.adm

@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 3 }
+{ "r1": 5 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.8.adm
new file mode 100644
index 0000000..f90118e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.8.adm

@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 3 }
+{ "r1": 5 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.9.adm
new file mode 100644
index 0000000..f90118e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.9.adm

@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 3 }
+{ "r1": 5 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.1.adm
new file mode 100644
index 0000000..572906d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.1.adm

@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 2 }
+{ "r1": 3 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.2.adm
new file mode 100644
index 0000000..902bb20
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.2.adm

@@ -0,0 +1,3 @@
+{ "nr1": -2, "nr3": -2 }
+{ "nr1": -3, "nr3": -3 }
+{ "nr1": -4, "nr3": -4 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.3.adm
new file mode 100644
index 0000000..f93af83
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.3.adm

@@ -0,0 +1,9 @@
+{ "x": 1, "y": 1 }
+{ "x": 1, "y": 2 }
+{ "x": 1, "y": 3 }
+{ "x": 2, "y": 2 }
+{ "x": 2, "y": 3 }
+{ "x": 2, "y": 4 }
+{ "x": 3, "y": 3 }
+{ "x": 3, "y": 4 }
+{ "x": 3, "y": 5 }
\ No newline at end of file

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.4.adm
new file mode 100644
index 0000000..250a30a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_07_from/subquery_coercion_07_from.4.adm

@@ -0,0 +1,2 @@
+{ "r1": 1 }
+{ "r1": 2 }
\ 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 597034d..87c4457 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml

@@ -10146,6 +10146,11 @@
   </test-group>
   <test-group name="sql-compat">
     <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-case FilePath="sql-compat">
       <compilation-unit name="select_star_01">
         <output-dir compare="Text">select_star_01</output-dir>
       </compilation-unit>
@@ -10157,8 +10162,57 @@
       </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 name="subquery_coercion_01_scalar">
+        <output-dir compare="Text">subquery_coercion_01_scalar</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="subquery_coercion_02_scalar_negative">
+        <output-dir compare="Text">none</output-dir>
+        <expected-error>ASX1169: Unable to do subquery coercion. Subquery returns more than one field (in line 32, at column 29)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Unsupported projection kind (in line 32, at column 20)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Both SELECT and SELECT VALUE are present (in line 35, at column 4)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion.  (in line 34, at column 3)</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="subquery_coercion_03_cmp">
+        <output-dir compare="Text">subquery_coercion_03_cmp</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="subquery_coercion_04_cmp_negative">
+        <output-dir compare="Text">none</output-dir>
+        <expected-error>ASX1169: Unable to do subquery coercion. Subquery returns more than one field (in line 35, at column 26)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Subquery returns more than one field (in line 35, at column 21)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Both SELECT and SELECT VALUE are present (in line 37, at column 3)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Both SELECT and SELECT VALUE are present (in line 38, at column 3)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion.  (in line 36, at column 3)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Unsupported projection kind (in line 35, at column 20)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Unsupported projection kind (in line 36, at column 11)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Unsupported projection kind (in line 37, at column 11)</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="subquery_coercion_05_in">
+        <output-dir compare="Text">subquery_coercion_05_in</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="subquery_coercion_06_in_negative">
+        <output-dir compare="Text">none</output-dir>
+        <expected-error>ASX1169: Unable to do subquery coercion. Subquery returns more than one field (in line 36, at column 27)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Subquery returns more than one field (in line 37, at column 17)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Both SELECT and SELECT VALUE are present (in line 38, at column 3)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Both SELECT and SELECT VALUE are present (in line 39, at column 3)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion.  (in line 37, at column 3)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Unsupported projection kind (in line 36, at column 21)</expected-error>
+        <expected-error>ASX1169: Unable to do subquery coercion. Unsupported projection kind (in line 37, at column 25)</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="subquery_coercion_07_from">
+        <output-dir compare="Text">subquery_coercion_07_from</output-dir>
       </compilation-unit>
     </test-case>
   </test-group>

diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
index 84e5c22..848bbdf 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java

@@ -254,6 +254,7 @@
     INVALID_FOREIGN_KEY_DEFINITION_REF_PK_MISMATCH(1166),
     CANNOT_CHANGE_PRIMARY_KEY(1167),
     AMBIGUOUS_PROJECTION(1168),
+    COMPILATION_SUBQUERY_COERCION_ERROR(1169),
 
     // Feed errors
     DATAFLOW_ILLEGAL_STATE(3001),

diff --git a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
index be4f512..51d3dab 100644
--- a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
+++ b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties

@@ -256,6 +256,7 @@
 1166 = Invalid foreign key definition: foreign key does not match primary key of %1$s %2$s
 1167 = Cannot change primary key of %1$s %2$s
 1168 = Ambiguous projection in SELECT clause
+1169 = Unable to do subquery coercion. %1$s
 
 # Feed Errors
 3001 = Illegal state.

diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/base/AbstractExpression.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/base/AbstractExpression.java
index 744d3cc..612f79f 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/base/AbstractExpression.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/base/AbstractExpression.java

@@ -58,4 +58,15 @@
     public List<IExpressionAnnotation> getHints() {
         return hints;
     }
+
+    public <T extends IExpressionAnnotation> T findHint(Class<T> hintClass) {
+        if (hints != null) {
+            for (IExpressionAnnotation hint : hints) {
+                if (hint.getClass().equals(hintClass)) {
+                    return hintClass.cast(hint);
+                }
+            }
+        }
+        return null;
+    }
 }

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 b28f4b9..7394c95 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

@@ -227,7 +227,7 @@
         if (!sqlCompatMode) {
             return;
         }
-        SqlCompatRewriteVisitor visitor = new SqlCompatRewriteVisitor();
+        SqlCompatRewriteVisitor visitor = new SqlCompatRewriteVisitor(context);
         rewriteTopExpr(visitor, null);
     }
 

diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SetOperationVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SetOperationVisitor.java
index da8b5f2..1266d9a 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SetOperationVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SetOperationVisitor.java

@@ -96,6 +96,7 @@
         SelectExpression newSelectExpression = new SelectExpression(selectExpression.getLetList(),
                 newSelectSetOperation, orderBy, limit, selectExpression.isSubquery());
         newSelectExpression.setSourceLocation(sourceLoc);
+        newSelectExpression.addHints(selectExpression.getHints());
         return super.visit(newSelectExpression, arg);
     }
 

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 8a701e7..0c6732d 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

@@ -23,15 +23,41 @@
 import java.util.List;
 
 import org.apache.asterix.common.exceptions.CompilationException;
+import org.apache.asterix.common.exceptions.ErrorCode;
 import org.apache.asterix.common.functions.FunctionSignature;
+import org.apache.asterix.lang.common.base.AbstractClause;
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.clause.LetClause;
+import org.apache.asterix.lang.common.clause.LimitClause;
 import org.apache.asterix.lang.common.expression.CallExpr;
+import org.apache.asterix.lang.common.expression.IndexAccessor;
+import org.apache.asterix.lang.common.expression.ListConstructor;
+import org.apache.asterix.lang.common.expression.LiteralExpr;
 import org.apache.asterix.lang.common.expression.OperatorExpr;
+import org.apache.asterix.lang.common.expression.QuantifiedExpression;
+import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.literal.IntegerLiteral;
+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.common.struct.VarIdentifier;
+import org.apache.asterix.lang.sqlpp.clause.FromClause;
+import org.apache.asterix.lang.sqlpp.clause.FromTerm;
+import org.apache.asterix.lang.sqlpp.clause.JoinClause;
 import org.apache.asterix.lang.sqlpp.clause.Projection;
+import org.apache.asterix.lang.sqlpp.clause.SelectBlock;
+import org.apache.asterix.lang.sqlpp.clause.SelectClause;
+import org.apache.asterix.lang.sqlpp.clause.SelectElement;
+import org.apache.asterix.lang.sqlpp.clause.SelectSetOperation;
+import org.apache.asterix.lang.sqlpp.clause.UnnestClause;
+import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
+import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
+import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
+import org.apache.asterix.lang.sqlpp.util.FunctionMapUtil;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppSimpleExpressionVisitor;
 import org.apache.asterix.om.functions.BuiltinFunctions;
+import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.api.exceptions.SourceLocation;
 
@@ -41,9 +67,28 @@
  * <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>
+ * <p/>
+ * Also applies subquery coercion rewritings as follows:
+ * <ol>
+ * <li> FROM/JOIN/UNNEST (subquery) --> no subquery coercion
+ * <li> WITH/LET v = (subquery) --> no subquery coercion
+ * <li> SOME v IN (subquery) --> no subquery coercion
+ * <li> WHERE (x,y) = (subquery) --> coerce the subquery into a single array
+ * <li> WHERE x IN (subquery) --> coerce the subquery into a collection of values
+ * <li> WHERE (x,y) IN (subquery) --> coerce the subquery into a collection of arrays
+ * <li> otherwise --> coerce the subquery into a single value
+ * </ol>
  */
 public final class SqlCompatRewriteVisitor extends AbstractSqlppSimpleExpressionVisitor {
 
+    private final LangRewritingContext context;
+
+    private final SelectSetOpInfo setOpInfo = new SelectSetOpInfo();
+
+    public SqlCompatRewriteVisitor(LangRewritingContext context) {
+        this.context = context;
+    }
+
     @Override
     public Expression visit(Projection projection, ILangExpression arg) throws CompilationException {
         if (projection.getKind() == Projection.Kind.STAR) {
@@ -53,34 +98,204 @@
     }
 
     @Override
+    public Expression visit(FromTerm fromTerm, ILangExpression arg) throws CompilationException {
+        Expression expr = fromTerm.getLeftExpression();
+        if (expr.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+            annotateSubqueryNoCoercion((SelectExpression) expr);
+        }
+        return super.visit(fromTerm, arg);
+    }
+
+    @Override
+    public Expression visit(JoinClause joinClause, ILangExpression arg) throws CompilationException {
+        Expression expr = joinClause.getRightExpression();
+        if (expr.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+            annotateSubqueryNoCoercion((SelectExpression) expr);
+        }
+        return super.visit(joinClause, arg);
+    }
+
+    @Override
+    public Expression visit(UnnestClause unnestClause, ILangExpression arg) throws CompilationException {
+        Expression expr = unnestClause.getRightExpression();
+        if (expr.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+            annotateSubqueryNoCoercion((SelectExpression) expr);
+        }
+        return super.visit(unnestClause, arg);
+    }
+
+    @Override
+    public Expression visit(LetClause letClause, ILangExpression arg) throws CompilationException {
+        Expression expr = letClause.getBindingExpr();
+        if (expr.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+            annotateSubqueryNoCoercion((SelectExpression) expr);
+        }
+        return super.visit(letClause, arg);
+    }
+
+    @Override
+    public Expression visit(QuantifiedExpression qe, ILangExpression arg) throws CompilationException {
+        for (QuantifiedPair pair : qe.getQuantifiedList()) {
+            Expression expr = pair.getExpr();
+            if (expr.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+                annotateSubqueryNoCoercion((SelectExpression) expr);
+            }
+        }
+        return super.visit(qe, 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)) {
+            OperatorType opType = opTypeList.get(0);
+            if (OperatorExpr.opIsComparison(opType)) {
+                List<Expression> argList = opExpr.getExprList();
+                Expression lhs = argList.get(0);
+                Expression rhs = argList.get(1);
+                if (lhs.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+                    annotateComparisonOpSubquery((SelectExpression) lhs, rhs);
+                }
+                if (rhs.getKind() == Expression.Kind.SELECT_EXPRESSION) {
+                    annotateComparisonOpSubquery((SelectExpression) rhs, lhs);
+                }
+            } else if (opType == OperatorType.IN || opType == OperatorType.NOT_IN) {
+                List<Expression> argList = opExpr.getExprList();
+                Expression lhs = argList.get(0);
+                Expression rhs = argList.get(1);
+                switch (rhs.getKind()) {
+                    case SELECT_EXPRESSION:
+                        annotateInOpSubquery((SelectExpression) rhs, lhs);
+                        break;
+                    case LIST_CONSTRUCTOR_EXPRESSION:
+                    case LIST_SLICE_EXPRESSION:
+                        // NOT? IN [] -> keep as is
+                        break;
+                    default:
+                        // NOT? IN expr -> NOT? IN to_array(expr)
                         List<Expression> newExprList = new ArrayList<>(2);
-                        newExprList.add(exprList.get(0));
-                        newExprList.add(createCallExpr(BuiltinFunctions.TO_ARRAY, arg1, opExpr.getSourceLocation()));
+                        newExprList.add(lhs);
+                        newExprList.add(createCallExpr(BuiltinFunctions.TO_ARRAY, rhs, opExpr.getSourceLocation()));
                         opExpr.setExprList(newExprList);
-                    }
-                    break;
+                        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;
+    @Override
+    public Expression visit(SelectExpression selectExpr, ILangExpression arg) throws CompilationException {
+        SqlCompatSelectExpressionCoercionAnnotation selectExprAnn = null;
+        if (selectExpr.isSubquery()) {
+            selectExprAnn = selectExpr.findHint(SqlCompatSelectExpressionCoercionAnnotation.class);
+            if (selectExprAnn == null) {
+                // all other cases --> coerce the subquery into a scalar value
+                if (annotateSubquery(selectExpr, SqlCompatSelectExpressionCoercionAnnotation.SCALAR,
+                        SqlCompatSelectBlockCoercionAnnotation.SCALAR)) {
+                    selectExprAnn = SqlCompatSelectExpressionCoercionAnnotation.SCALAR;
+                }
+            }
+        }
+        Expression newExpr = super.visit(selectExpr, arg);
+        if (selectExprAnn != null) {
+            newExpr = rewriteSelectExpression(newExpr, selectExprAnn);
+        }
+        return newExpr;
+    }
+
+    @Override
+    public Expression visit(SelectBlock selectBlock, ILangExpression arg) throws CompilationException {
+        super.visit(selectBlock, arg);
+        SelectExpression selectExpr = (SelectExpression) arg;
+        SqlCompatSelectBlockCoercionAnnotation selectBlockAnn =
+                selectExpr.findHint(SqlCompatSelectBlockCoercionAnnotation.class);
+        if (selectBlockAnn != null) {
+            rewriteSelectBlock(selectBlock, selectBlockAnn);
+        }
+        return null;
+    }
+
+    private void annotateSubqueryNoCoercion(SelectExpression subqueryExpr) {
+        // FROM/JOIN/UNNEST/LET (subquery) -> do NOT coerce the subquery
+        subqueryExpr.addHint(SqlCompatSelectExpressionCoercionAnnotation.COLLECTION);
+    }
+
+    private void annotateComparisonOpSubquery(SelectExpression subqueryExpr, Expression otherArg)
+            throws CompilationException {
+        // (x,y) = (subquery) -> coerce the subquery into a single array
+        // x = (subquery) -> coerce the subquery into a scalar value
+        annotateSubquery(subqueryExpr, SqlCompatSelectExpressionCoercionAnnotation.SCALAR,
+                getSelectBlockAnnotationForOpSubquery(otherArg));
+    }
+
+    private void annotateInOpSubquery(SelectExpression subqueryExpr, Expression otherArg) throws CompilationException {
+        // (x,y) in (subquery) -> coerce the subquery into a collection of arrays
+        // x in (subquery) -> coerce the subquery into a collection of scalar values
+        annotateSubquery(subqueryExpr, SqlCompatSelectExpressionCoercionAnnotation.COLLECTION,
+                getSelectBlockAnnotationForOpSubquery(otherArg));
+    }
+
+    private static SqlCompatSelectBlockCoercionAnnotation getSelectBlockAnnotationForOpSubquery(Expression otherArg)
+            throws CompilationException {
+        if (otherArg.getKind() == Expression.Kind.LIST_CONSTRUCTOR_EXPRESSION) {
+            ListConstructor lc = (ListConstructor) otherArg;
+            switch (lc.getType()) {
+                case ORDERED_LIST_CONSTRUCTOR:
+                    return SqlCompatSelectBlockCoercionAnnotation.ARRAY;
+                case UNORDERED_LIST_CONSTRUCTOR:
+                    return SqlCompatSelectBlockCoercionAnnotation.MULTISET;
+                default:
+                    throw new CompilationException(ErrorCode.ILLEGAL_STATE, otherArg.getSourceLocation(), "");
+            }
+        } else {
+            return SqlCompatSelectBlockCoercionAnnotation.SCALAR;
+        }
+    }
+
+    private boolean annotateSubquery(SelectExpression subqueryExpr,
+            SqlCompatSelectExpressionCoercionAnnotation selectExprAnnotation,
+            SqlCompatSelectBlockCoercionAnnotation selectBlockAnn) throws CompilationException {
+        setOpInfo.reset();
+        analyzeSelectSetOp(subqueryExpr.getSelectSetOperation(), setOpInfo);
+        if (setOpInfo.subqueryExists) {
+            throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
+                    subqueryExpr.getSourceLocation(), "");
+        }
+        if (setOpInfo.selectRegularExists) {
+            if (setOpInfo.selectElementExists) {
+                throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
+                        subqueryExpr.getSourceLocation(), "Both SELECT and SELECT VALUE are present");
+            }
+            subqueryExpr.addHint(selectExprAnnotation);
+            subqueryExpr.addHint(selectBlockAnn);
+            return true;
+        } else {
+            return false;
+        }
+    }
+
+    private static void analyzeSelectSetOp(SelectSetOperation setOp, SelectSetOpInfo outSelectExprInfo)
+            throws CompilationException {
+        analyzeSelectSetOpInput(setOp.getLeftInput(), outSelectExprInfo);
+        if (setOp.hasRightInputs()) {
+            for (SetOperationRight rhs : setOp.getRightInputs()) {
+                analyzeSelectSetOpInput(rhs.getSetOperationRightInput(), outSelectExprInfo);
+            }
+        }
+    }
+
+    private static void analyzeSelectSetOpInput(SetOperationInput setOpInput, SelectSetOpInfo outSelectSetOpInfo)
+            throws CompilationException {
+        if (setOpInput.selectBlock()) {
+            SelectBlock selectBlock = setOpInput.getSelectBlock();
+            SelectClause selectClause = selectBlock.getSelectClause();
+            outSelectSetOpInfo.selectRegularExists |= selectClause.selectRegular();
+            outSelectSetOpInfo.selectElementExists |= selectClause.selectElement();
+        } else if (setOpInput.subquery()) {
+            outSelectSetOpInfo.subqueryExists = true;
+        } else {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, "");
         }
     }
 
@@ -91,4 +306,215 @@
         callExpr.setSourceLocation(sourceLoc);
         return callExpr;
     }
+
+    private static final class SelectSetOpInfo {
+        private boolean subqueryExists;
+        private boolean selectRegularExists;
+        private boolean selectElementExists;
+
+        void reset() {
+            subqueryExists = false;
+            selectRegularExists = false;
+            selectElementExists = false;
+        }
+    }
+
+    private enum SqlCompatSelectExpressionCoercionAnnotation implements IExpressionAnnotation {
+        /**
+         * Indicates that the result of the {@link SelectExpression}
+         * must be coerced into a single item if its cardinality is 1 or to MISSING otherwise.
+         */
+        SCALAR,
+
+        /**
+         * Indicates that no transformation is needed
+         */
+        COLLECTION
+    }
+
+    private enum SqlCompatSelectBlockCoercionAnnotation implements IExpressionAnnotation {
+        /**
+         * Indicates that the output record of the {@link SelectBlock} must be transformed
+         * into a scalar value if that output record has 1 field, or transformed into MISSING value otherwise
+         */
+        SCALAR,
+
+        /**
+         * Indicates that the output record of the {@link SelectBlock} must be transformed
+         * into an array
+         */
+        ARRAY,
+
+        /**
+         * Indicates that the output record of the {@link SelectBlock} must be transformed
+         * into a multiset
+         */
+        MULTISET
+    }
+
+    private void rewriteSelectBlock(SelectBlock selectBlock, SqlCompatSelectBlockCoercionAnnotation ann)
+            throws CompilationException {
+        SelectClause selectClause = selectBlock.getSelectClause();
+        List<Projection> projectList = selectClause.getSelectRegular().getProjections();
+        switch (ann) {
+            case SCALAR:
+                /*
+                 * SELECT x -> SELECT VALUE x
+                 * SELECT x, y -> ERROR
+                 * SELECT * -> ERROR
+                 */
+                if (projectList.size() > 1) {
+                    throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
+                            projectList.get(1).getSourceLocation(), "Subquery returns more than one field");
+                }
+                Projection projection = projectList.get(0);
+                if (projection.getKind() != Projection.Kind.NAMED_EXPR) {
+                    throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
+                            projection.getSourceLocation(), "Unsupported projection kind");
+                }
+                SelectElement selectElement = new SelectElement(projection.getExpression());
+                selectElement.setSourceLocation(selectClause.getSourceLocation());
+                selectClause.setSelectElement(selectElement);
+                break;
+            case ARRAY:
+            case MULTISET:
+                /*
+                 * SELECT x -> SELECT VALUE [x]  (or SELECT VALUE {{x}})
+                 * SELECT x, y -> SELECT VALUE [x, y] (or SELECT VALUE {{x, y}})
+                 * SELECT * -> ERROR
+                 */
+                List<Expression> exprList = new ArrayList<>(projectList.size());
+                for (Projection p : projectList) {
+                    if (p.getKind() != Projection.Kind.NAMED_EXPR) {
+                        throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
+                                p.getSourceLocation(), "Unsupported projection kind");
+                    }
+                    exprList.add(p.getExpression());
+                }
+                ListConstructor.Type listType = ann == SqlCompatSelectBlockCoercionAnnotation.ARRAY
+                        ? ListConstructor.Type.ORDERED_LIST_CONSTRUCTOR
+                        : ListConstructor.Type.UNORDERED_LIST_CONSTRUCTOR;
+                ListConstructor listExpr = new ListConstructor(listType, exprList);
+                listExpr.setSourceLocation(selectClause.getSourceLocation());
+                selectElement = new SelectElement(listExpr);
+                selectElement.setSourceLocation(selectClause.getSourceLocation());
+                selectClause.setSelectElement(selectElement);
+                break;
+            default:
+                throw new CompilationException(ErrorCode.ILLEGAL_STATE, selectBlock.getSourceLocation(),
+                        ann.toString());
+        }
+    }
+
+    private Expression rewriteSelectExpression(Expression inExpr, SqlCompatSelectExpressionCoercionAnnotation ann)
+            throws CompilationException {
+        switch (ann) {
+            case SCALAR:
+                /*
+                 * (SELECT ...)
+                 * ->
+                 * STRICT_FIRST_ELEMENT
+                 * (
+                 *  SELECT VALUE v2[(LEN(v2)-1)*2]
+                 *  LET v2 = (SELECT VALUE v1 FROM (SELECT ...) v1 LIMIT 2)
+                 * )
+                 */
+                SourceLocation sourceLoc = inExpr.getSourceLocation();
+
+                /*
+                 * E1: SELECT VALUE v1 FROM (SELECT ...) v1 LIMIT 2
+                 */
+                VarIdentifier v1 = context.newVariable();
+                VariableExpr v1Ref1 = new VariableExpr(v1);
+                v1Ref1.setSourceLocation(sourceLoc);
+                FromTerm ft1 = new FromTerm(inExpr, v1Ref1, null, null);
+                ft1.setSourceLocation(sourceLoc);
+                List<FromTerm> fc1Terms = new ArrayList<>(1);
+                fc1Terms.add(ft1);
+                FromClause fc1 = new FromClause(fc1Terms);
+                fc1.setSourceLocation(sourceLoc);
+                VariableExpr v1Ref2 = new VariableExpr(v1);
+                v1Ref2.setSourceLocation(sourceLoc);
+                SelectElement sv1 = new SelectElement(v1Ref2);
+                sv1.setSourceLocation(sourceLoc);
+                SelectClause sc1 = new SelectClause(sv1, null, false);
+                sc1.setSourceLocation(sourceLoc);
+                SelectBlock sb1 = new SelectBlock(sc1, fc1, null, null, null);
+                sv1.setSourceLocation(sourceLoc);
+                SelectSetOperation sop1 = new SelectSetOperation(new SetOperationInput(sb1, null), null);
+                sop1.setSourceLocation(sourceLoc);
+                LimitClause lc1 = new LimitClause(new LiteralExpr(new IntegerLiteral(2)), null);
+                lc1.setSourceLocation(sourceLoc);
+                SelectExpression se1 = new SelectExpression(null, sop1, null, lc1, true);
+                se1.setSourceLocation(sourceLoc);
+
+                /*
+                 * E2:
+                 *   SELECT VALUE v2[(LEN(v2)-1)*2]
+                 *   LET v2 = (..E1..)
+                 *
+                 * E2 returns {{ item }} if LEN(E1) == 1, otherwise it returns {{ MISSING }}
+                 */
+                VarIdentifier v2 = context.newVariable();
+                VariableExpr v2Ref1 = new VariableExpr(v2);
+                v2Ref1.setSourceLocation(sourceLoc);
+                LetClause lc2 = new LetClause(v2Ref1, se1);
+                lc2.setSourceLocation(sourceLoc);
+
+                VariableExpr v2Ref2 = new VariableExpr(v2);
+                v2Ref2.setSourceLocation(sourceLoc);
+                List<Expression> lenArgs = new ArrayList<>(1);
+                lenArgs.add(v2Ref2);
+                CallExpr lenExpr = new CallExpr(new FunctionSignature(BuiltinFunctions.LEN), lenArgs);
+                lenExpr.setSourceLocation(sourceLoc);
+
+                OperatorExpr minusExpr = new OperatorExpr();
+                minusExpr.setCurrentop(true);
+                minusExpr.addOperator(OperatorType.MINUS);
+                minusExpr.addOperand(lenExpr);
+                minusExpr.addOperand(new LiteralExpr(new IntegerLiteral(1)));
+                minusExpr.setSourceLocation(sourceLoc);
+
+                OperatorExpr mulExpr = new OperatorExpr();
+                mulExpr.setCurrentop(true);
+                mulExpr.addOperator(OperatorType.MUL);
+                mulExpr.addOperand(minusExpr);
+                mulExpr.addOperand(new LiteralExpr(new IntegerLiteral(2)));
+                mulExpr.setSourceLocation(sourceLoc);
+
+                VariableExpr v2Ref3 = new VariableExpr(v2);
+                v2Ref3.setSourceLocation(sourceLoc);
+                IndexAccessor iaExpr = new IndexAccessor(v2Ref3, IndexAccessor.IndexKind.ELEMENT, mulExpr);
+                iaExpr.setSourceLocation(sourceLoc);
+
+                SelectElement sv2 = new SelectElement(iaExpr);
+                sv2.setSourceLocation(sourceLoc);
+                SelectClause sc2 = new SelectClause(sv2, null, false);
+                sc2.setSourceLocation(sourceLoc);
+                List<AbstractClause> sb2Clauses = new ArrayList<>(1);
+                sb2Clauses.add(lc2);
+                SelectBlock sb2 = new SelectBlock(sc2, null, sb2Clauses, null, null);
+                sb2.setSourceLocation(sourceLoc);
+                SelectSetOperation sop2 = new SelectSetOperation(new SetOperationInput(sb2, null), null);
+                sop2.setSourceLocation(sourceLoc);
+                SelectExpression se2 = new SelectExpression(null, sop2, null, null, true);
+                se2.setSourceLocation(sourceLoc);
+
+                /*
+                 * E3: STRICT_FIRST_ELEMENT(..E2..)
+                 */
+                List<Expression> firstElemArgs = new ArrayList<>(1);
+                firstElemArgs.add(se2);
+                FunctionIdentifier firstElemFun =
+                        FunctionMapUtil.createCoreAggregateFunctionIdentifier(BuiltinFunctions.SCALAR_FIRST_ELEMENT);
+                CallExpr firstElemExpr = new CallExpr(new FunctionSignature(firstElemFun), firstElemArgs);
+                firstElemExpr.setSourceLocation(sourceLoc);
+                return firstElemExpr;
+            case COLLECTION:
+                // indicates that no transformation is necessary
+                return inExpr;
+            default:
+                throw new CompilationException(ErrorCode.ILLEGAL_STATE, inExpr.getSourceLocation(), ann.toString());
+        }
+    }
 }

diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java
index a46f10b..2d41345 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java

@@ -204,4 +204,9 @@
         }
         return functionName;
     }
+
+    public static FunctionIdentifier createCoreAggregateFunctionIdentifier(FunctionIdentifier scalarfi) {
+        return BuiltinFunctions.getAggregateFunction(scalarfi) != null ? new FunctionIdentifier(scalarfi.getNamespace(),
+                CORE_AGGREGATE_PREFIX + scalarfi.getName(), scalarfi.getArity()) : null;
+    }
 }

diff --git a/asterixdb/asterix-om/src/main/java/org/apache/asterix/dataflow/data/nontagged/comparators/LogicalComplexBinaryComparator.java b/asterixdb/asterix-om/src/main/java/org/apache/asterix/dataflow/data/nontagged/comparators/LogicalComplexBinaryComparator.java
index 93ccaa3..4c51b0d 100644
--- a/asterixdb/asterix-om/src/main/java/org/apache/asterix/dataflow/data/nontagged/comparators/LogicalComplexBinaryComparator.java
+++ b/asterixdb/asterix-om/src/main/java/org/apache/asterix/dataflow/data/nontagged/comparators/LogicalComplexBinaryComparator.java

@@ -38,7 +38,6 @@
 import org.apache.asterix.om.util.container.ListObjectPool;
 import org.apache.asterix.om.utils.RecordUtil;
 import org.apache.hyracks.api.exceptions.HyracksDataException;
-import org.apache.hyracks.data.std.accessors.RawBinaryComparatorFactory;
 
 public final class LogicalComplexBinaryComparator implements ILogicalBinaryComparator {
 
@@ -189,9 +188,7 @@
         if (!isEquality) {
             return Result.INCOMPARABLE;
         }
-        return ILogicalBinaryComparator
-                .asResult(RawBinaryComparatorFactory.compare(left.getByteArray(), left.getStartOffset(),
-                        left.getLength(), right.getByteArray(), right.getStartOffset(), right.getLength()));
+        return compareArrays(leftType, left, rightType, right);
     }
 
     private Result compareRecords(IAType leftType, TaggedValueReference left, IAType rightType,

diff --git a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/scalar/ScalarFirstElementAggregateDescriptor.java b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/scalar/ScalarFirstElementAggregateDescriptor.java
new file mode 100644
index 0000000..22831d7
--- /dev/null
+++ b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/scalar/ScalarFirstElementAggregateDescriptor.java

@@ -0,0 +1,43 @@
+/*
+ * 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.runtime.aggregates.scalar;
+
+import org.apache.asterix.om.functions.BuiltinFunctions;
+import org.apache.asterix.om.functions.IFunctionDescriptorFactory;
+import org.apache.asterix.runtime.aggregates.collections.FirstElementAggregateDescriptor;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+
+public class ScalarFirstElementAggregateDescriptor extends AbstractScalarAggregateDescriptor {
+
+    private static final long serialVersionUID = 1L;
+
+    public static final FunctionIdentifier FID = BuiltinFunctions.SCALAR_FIRST_ELEMENT;
+
+    public static final IFunctionDescriptorFactory FACTORY = ScalarFirstElementAggregateDescriptor::new;
+
+    private ScalarFirstElementAggregateDescriptor() {
+        super(FirstElementAggregateDescriptor.FACTORY);
+    }
+
+    @Override
+    public FunctionIdentifier getIdentifier() {
+        return FID;
+    }
+}

diff --git a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/functions/FunctionCollection.java b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/functions/FunctionCollection.java
index 427e7ee..b32d0db 100644
--- a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/functions/FunctionCollection.java
+++ b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/functions/FunctionCollection.java

@@ -37,6 +37,7 @@
 import org.apache.asterix.runtime.aggregates.scalar.ScalarAvgDistinctAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarCountAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarCountDistinctAggregateDescriptor;
+import org.apache.asterix.runtime.aggregates.scalar.ScalarFirstElementAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarKurtosisAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarKurtosisDistinctAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarMaxAggregateDescriptor;
@@ -773,6 +774,7 @@
         fc.add(ScalarSkewnessAggregateDescriptor.FACTORY);
         fc.add(ScalarSkewnessDistinctAggregateDescriptor.FACTORY);
         fc.add(ScalarUnionMbrAggregateDescriptor.FACTORY);
+        fc.add(ScalarFirstElementAggregateDescriptor.FACTORY);
 
         // SQL aggregates
         fc.add(SqlCountAggregateDescriptor.FACTORY);
commit	81771df2076de444c6ba5747091efd17124ee2ed	[log] [tgz]
author	Dmitry Lychagin <dmitry.lychagin@couchbase.com>	Fri Nov 05 17:12:19 2021 -0700
committer	Dmitry Lychagin <dmitry.lychagin@couchbase.com>	Wed Nov 10 15:53:23 2021 +0000
tree	b03a1fc235fd9dd0caf040792a08390223e7d81d
parent	8d6cf7574430de4f4049fbf5e6726cf2ede5d8aa [diff]