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