http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810-2.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810-2.ast b/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810-2.ast index 8b9e4b9..c75e253 100644 --- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810-2.ast +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810-2.ast @@ -104,7 +104,9 @@ Let Variable [ Name=$expensives ] Field=l_discount ] ] - FROM [ Variable [ Name=$l ] + FROM [ FunctionCall Metadata.dataset@1[ + LiteralExpr [STRING] [l] + ] AS Variable [ Name=$i ] ] Where @@ -123,7 +125,9 @@ Let Variable [ Name=$cheaps ] SELECT ELEMENT [ Variable [ Name=$i ] ] - FROM [ Variable [ Name=$l ] + FROM [ FunctionCall Metadata.dataset@1[ + LiteralExpr [STRING] [l] + ] AS Variable [ Name=$i ] ] Where @@ -165,7 +169,9 @@ Let Variable [ Name=$charges ] ] ] ] - FROM [ Variable [ Name=$l ] + FROM [ FunctionCall Metadata.dataset@1[ + LiteralExpr [STRING] [l] + ] AS Variable [ Name=$i ] ] ) @@ -189,7 +195,9 @@ Let Variable [ Name=$disc_prices ] ] ] ] - FROM [ Variable [ Name=$l ] + FROM [ FunctionCall Metadata.dataset@1[ + LiteralExpr [STRING] [l] + ] AS Variable [ Name=$i ] ] )
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810.ast b/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810.ast index a7dec25..612bbfa 100644 --- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810.ast +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/query-issue810.ast @@ -87,7 +87,9 @@ Let Variable [ Name=$cheap ] SELECT ELEMENT [ Variable [ Name=$m ] ] - FROM [ Variable [ Name=$l ] + FROM [ FunctionCall Metadata.dataset@1[ + LiteralExpr [STRING] [l] + ] AS Variable [ Name=$m ] ] Where @@ -106,7 +108,9 @@ Let Variable [ Name=$expensive ] SELECT ELEMENT [ Variable [ Name=$a ] ] - FROM [ Variable [ Name=$l ] + FROM [ FunctionCall Metadata.dataset@1[ + LiteralExpr [STRING] [l] + ] AS Variable [ Name=$a ] ] Where http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/2.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/2.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/2.ast index 5471490..9e50f02 100644 --- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/2.ast +++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/2.ast @@ -94,7 +94,6 @@ Let Variable [ Name=$by_chapter ] FunctionCall Metadata.resolve@-1[ LiteralExpr [STRING] [es] Variable [ Name=#4 ] - Variable [ Name=$e ] Variable [ Name=$sponsor ] Variable [ Name=$sig_sponsorship_count ] Variable [ Name=$chapter_name ] http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast index 0fdf31a..aadad7e 100644 --- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast +++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast @@ -3,12 +3,22 @@ SELECT [ FunctionCall null.sqrt@1[ OperatorExpr [ FieldAccessor [ - Variable [ Name=$t ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [t] + Variable [ Name=$u ] + Variable [ Name=$root ] + Variable [ Name=#1 ] + ] Field=a ] * FieldAccessor [ - Variable [ Name=$t ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [t] + Variable [ Name=$u ] + Variable [ Name=$root ] + Variable [ Name=#1 ] + ] Field=b ] ] http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias2.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias2.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias2.ast index 78238d1..9cf8d6f 100644 --- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias2.ast +++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias2.ast @@ -6,7 +6,6 @@ FunctionCall null.sqrt@1[ FunctionCall Metadata.resolve@-1[ LiteralExpr [STRING] [t] Variable [ Name=$u ] - Variable [ Name=$root ] Variable [ Name=#1 ] Variable [ Name=$id ] ] @@ -17,7 +16,6 @@ FunctionCall null.sqrt@1[ FunctionCall Metadata.resolve@-1[ LiteralExpr [STRING] [t] Variable [ Name=$u ] - Variable [ Name=$root ] Variable [ Name=#1 ] Variable [ Name=$id ] ] @@ -47,13 +45,22 @@ Groupby Let Variable [ Name=$u ] := FieldAccessor [ - Variable [ Name=$root ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [root] + Variable [ Name=#1 ] + Variable [ Name=$id ] + ] Field=time ] HAVING OperatorExpr [ FieldAccessor [ - Variable [ Name=$root ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [root] + Variable [ Name=$u ] + Variable [ Name=#1 ] + Variable [ Name=$id ] + ] Field=orders ] > http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast index 9bf4094..92fcd53 100644 --- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast +++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast @@ -7,12 +7,22 @@ RecordConstructor [ FunctionCall null.sqrt@1[ OperatorExpr [ FieldAccessor [ - Variable [ Name=$t ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [t] + Variable [ Name=$u ] + Variable [ Name=$root ] + Variable [ Name=#1 ] + ] Field=a ] * FieldAccessor [ - Variable [ Name=$t ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [t] + Variable [ Name=$u ] + Variable [ Name=$root ] + Variable [ Name=#1 ] + ] Field=b ] ] http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/nestedFLWOGR3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/nestedFLWOGR3.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/nestedFLWOGR3.ast index af44c39..9c4682d 100644 --- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/nestedFLWOGR3.ast +++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/nestedFLWOGR3.ast @@ -94,7 +94,6 @@ Let Variable [ Name=$by_chapter ] FunctionCall Metadata.resolve@-1[ LiteralExpr [STRING] [es] Variable [ Name=#4 ] - Variable [ Name=$e ] Variable [ Name=$sponsor ] Variable [ Name=$sig_sponsorship_count ] Variable [ Name=$chapter_name ] http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/big-object/big_object_groupby/big_object_groupby.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/big-object/big_object_groupby/big_object_groupby.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/big-object/big_object_groupby/big_object_groupby.3.query.sqlpp index 641b8e6..b5388a9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/big-object/big_object_groupby/big_object_groupby.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/big-object/big_object_groupby/big_object_groupby.3.query.sqlpp @@ -26,8 +26,9 @@ use test; -select element {'partkey':partkey,'lines':i} +select element {'partkey':partkey,'lines': (from g select value i) } from Line as i group by i.l_partkey as partkey +group as g order by partkey ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at02/at02.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at02/at02.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at02/at02.3.query.sqlpp index 3c6be99..82e458f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at02/at02.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at02/at02.3.query.sqlpp @@ -28,14 +28,15 @@ use test; select element {'group':g,'item':( select element {'num':p,'mid':a.`message-id`} from ( - select element ii - from m as ii - order by ii.`message-id` + select element ii.m + from gr as ii + order by ii.m.`message-id` ) as a at p )} from FacebookUsers as u, FacebookMessages as m -where (u.id = m.`author-id`) +where u.id = m.`author-id` group by u.id as g +group as gr order by g ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at03/at03.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at03/at03.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at03/at03.3.query.sqlpp index 44ecea4..51b8ff8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at03/at03.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at03/at03.3.query.sqlpp @@ -28,15 +28,16 @@ use test; select element {'user-since':fus,'users':( select element {'num':ip,'name':f.name} from ( - select element i1 - from i as i1 - order by i1.name + select element i1.i + from g as i1 + order by i1.i.name ) as f at ip )} from ( select element fb from FacebookUsers as fb ) as i -group by test.`get-year`(i.`user-since`) as fus +group by `get-year`(i.`user-since`) as fus +group as g order by fus ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at04/at04.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at04/at04.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at04/at04.3.query.sqlpp index cbe542a..bb97545 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at04/at04.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at04/at04.3.query.sqlpp @@ -28,9 +28,9 @@ use test; select element {'o_custkey':ckey,'users':( select element {'num':ip,'orderkey':f.o_orderkey} from ( - select element i1 - from i as i1 - order by i1.o_orderkey + select element i1.i + from g as i1 + order by i1.i.o_orderkey ) as f at ip )} from ( @@ -38,6 +38,7 @@ from ( from Orders as o ) as i group by i.o_custkey as ckey +group as g order by ckey limit 3 ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at05/at05.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at05/at05.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at05/at05.3.query.sqlpp index 7905776..86318a9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at05/at05.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/at05/at05.3.query.sqlpp @@ -27,16 +27,17 @@ use test; select element si from - ( select element {'xx':xx,'x':x,'uid':uid} + ( select element {'xx':xx,'x': (from g select value g.x),'uid':uid} from Orders as x - where (x.o_custkey < 4) + where x.o_custkey < 4 group by x.o_custkey as uid - with xx as ( + group as g + let xx = ( select element {'uid':uid,'seq':i,'item':y.o_orderkey} from ( - select element xxx - from x as xxx - order by xxx.o_orderkey + select element xxx.x + from g as xxx + order by xxx.x.o_orderkey ) as y at i ) order by uid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-ASTERIXDB-883/query-ASTERIXDB-883.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-ASTERIXDB-883/query-ASTERIXDB-883.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-ASTERIXDB-883/query-ASTERIXDB-883.2.update.sqlpp index f288510..a29e59b 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-ASTERIXDB-883/query-ASTERIXDB-883.2.update.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-ASTERIXDB-883/query-ASTERIXDB-883.2.update.sqlpp @@ -25,7 +25,8 @@ load dataset page_views using localfs insert into tmp( from page_views as t group by t.user + group as g select value { - "groups": t + "groups": (select value g.t from g) } ); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-issue550/query-issue550.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-issue550/query-issue550.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-issue550/query-issue550.3.query.sqlpp index 33c7947..9969f77 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-issue550/query-issue550.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/query-issue550/query-issue550.3.query.sqlpp @@ -25,9 +25,10 @@ with sample as {{{'r':1,'uid':'1a2b','t':datetime('2000-01-01T01:00:00'),'event':'e1'},{'r':2,'uid':'1a2b','t':datetime('2000-01-01T01:01:00'),'event':'e2'},{'r':3,'uid':'3c4d','t':datetime('2000-01-01T01:02:00'),'event':'e1'},{'r':4,'uid':'3c4d','t':datetime('2000-01-01T01:03:00'),'event':'e3'},{'r':5,'uid':'1a2b','t':datetime('2000-01-01T01:04:00'),'event':'e1'},{'r':6,'uid':'1a2b','t':datetime('2000-01-01T01:05:00'),'event':'e4'}}} select element {'u':u,'recs':( - select element srec - from s as srec + select element srec.s + from g as srec )} from sample as s group by s.uid as u +group as g ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.1.ddl.sqlpp new file mode 100644 index 0000000..7cb4811 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.1.ddl.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. + */ + +DROP DATAVERSE gby IF EXISTS; +CREATE DATAVERSE gby; + +USE gby; + +CREATE TYPE EmpType AS { + name : string +} + +CREATE DATASET Employee(EmpType) PRIMARY KEY name; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.2.update.sqlpp new file mode 100644 index 0000000..a3c4c35 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.2.update.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. + */ + +USE gby; + +INSERT INTO Employee ( + [ + { 'name': 'Bill', + 'deptno': 'K55', + 'salary': 2000 }, + + { 'name': 'Fred', + 'deptno': 'K55', + 'salary': 3000 } + ] +); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.3.query.sqlpp new file mode 100644 index 0000000..56b042c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-01-error/core-01-error.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 gby; + + +FROM Employee e +GROUP BY e.deptno AS deptno +SELECT ELEMENT { + 'deptno': deptno, + 'avgpay': coll_avg( (FROM e AS i SELECT ELEMENT i.salary) ), + 'workers': (FROM e AS i SELECT ELEMENT {'name': i.name, 'salary': i.salary}) +}; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.1.ddl.sqlpp new file mode 100644 index 0000000..7cb4811 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.1.ddl.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. + */ + +DROP DATAVERSE gby IF EXISTS; +CREATE DATAVERSE gby; + +USE gby; + +CREATE TYPE EmpType AS { + name : string +} + +CREATE DATASET Employee(EmpType) PRIMARY KEY name; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.2.update.sqlpp new file mode 100644 index 0000000..a3c4c35 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.2.update.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. + */ + +USE gby; + +INSERT INTO Employee ( + [ + { 'name': 'Bill', + 'deptno': 'K55', + 'salary': 2000 }, + + { 'name': 'Fred', + 'deptno': 'K55', + 'salary': 3000 } + ] +); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.3.query.sqlpp new file mode 100644 index 0000000..b752fe0 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/core-02-error/core-02-error.3.query.sqlpp @@ -0,0 +1,30 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +USE gby; + + +FROM Employee e +GROUP BY e.deptno AS deptno +GROUP AS g(e as f) +SELECT ELEMENT { + 'deptno': deptno, + 'avgpay': coll_avg( (FROM f AS i SELECT ELEMENT i.salary) ), + 'workers': (FROM f AS i SELECT ELEMENT {'name': i.name, 'salary': i.salary}) +}; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp index 7522795..8600077 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp @@ -23,6 +23,6 @@ FROM Employee e JOIN Incentive i ON e.job_category = i.job_category JOIN SuperStars s ON e.id = s.id GROUP BY e.department_id GROUP AS g -HAVING coll_count( ( SELECT VALUE e FROM g) )>0 +HAVING coll_count( ( SELECT VALUE g.e FROM g) )>0 SELECT e.department_id as deptId, SUM(e.salary + i.bonus) AS star_cost; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-01-negative/sugar-01-negative.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-01-negative/sugar-01-negative.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-01-negative/sugar-01-negative.3.query.sqlpp index e278914..2bc83ef 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-01-negative/sugar-01-negative.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-01-negative/sugar-01-negative.3.query.sqlpp @@ -23,5 +23,5 @@ USE gby; // cannot be used like a SQL-92 sugar, e.g., AVG. Its input must be a collection. FROM Employee e GROUP BY e.deptno AS deptno GROUP AS g -SELECT deptno AS deptno, COLL_AVG(e.salary) AS avgpay, +SELECT deptno AS deptno, COLL_AVG(g.e.salary) AS avgpay, (SELECT i.e.name AS name, i.e.salary AS salary FROM g AS i) AS workers; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04-2/sugar-04-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04-2/sugar-04-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04-2/sugar-04-2.3.query.sqlpp index 4ba84b7..3852109 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04-2/sugar-04-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04-2/sugar-04-2.3.query.sqlpp @@ -22,10 +22,11 @@ USE gby; FROM Employee e, Incentive i WHERE e.job_category = i.job_category GROUP BY e.department_id +GROUP AS g SELECT e.department_id AS deptId, - ( FROM i AS i - SELECT i.job_category AS category, i.bonus AS bonus - ORDER BY i.bonus DESC LIMIT 3 + ( FROM g AS g + SELECT g.i.job_category AS category, g.i.bonus AS bonus + ORDER BY g.i.bonus DESC LIMIT 3 ) AS job_category_details ORDER BY deptId DESC; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04/sugar-04.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04/sugar-04.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04/sugar-04.3.query.sqlpp index d8fb8c9..abc02ea 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04/sugar-04.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-04/sugar-04.3.query.sqlpp @@ -22,10 +22,11 @@ USE gby; FROM Employee e, Incentive i WHERE e.job_category = i.job_category GROUP BY e.department_id AS deptId +GROUP AS g SELECT deptId AS deptId, - ( FROM i AS i - SELECT i.job_category AS category, i.bonus AS bonus - ORDER BY i.bonus DESC LIMIT 3 + ( FROM g AS g + SELECT g.i.job_category AS category, g.i.bonus AS bonus + ORDER BY g.i.bonus DESC LIMIT 3 ) AS job_category_details ORDER BY deptId DESC; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_02/hdfs_02.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_02/hdfs_02.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_02/hdfs_02.3.query.sqlpp index ef54a7e..358eeda 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_02/hdfs_02.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_02/hdfs_02.3.query.sqlpp @@ -26,9 +26,10 @@ use test; -select element {'word':tok,'count':test.coll_count(token)} +select element {'word':tok,'count':test.coll_count(( from g select value token ))} from TextDataset as line, test.`word-tokens`(line.content) as token group by token as tok +group as g order by tok ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_03/hdfs_03.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_03/hdfs_03.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_03/hdfs_03.3.query.sqlpp index da1ffb5..fb67e5b 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_03/hdfs_03.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_03/hdfs_03.3.query.sqlpp @@ -28,9 +28,10 @@ use test; -select element {'word':tok,'count':test.coll_count(token)} +select element {'word':tok,'count':test.coll_count((from g select value token))} from TextDataset as line, test.`word-tokens`(line.content) as token group by token as tok +group as g order by tok ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_shortcircuit/hdfs_shortcircuit.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_shortcircuit/hdfs_shortcircuit.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_shortcircuit/hdfs_shortcircuit.3.query.sqlpp index 3fe03f1..998dd43 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_shortcircuit/hdfs_shortcircuit.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hdfs/hdfs_shortcircuit/hdfs_shortcircuit.3.query.sqlpp @@ -27,9 +27,10 @@ use test; -select element {'word':tok,'count':test.coll_count(token)} +select element {'word':tok,'count':test.coll_count((from g select value token))} from TextDataset as line, test.`word-tokens`(line.content) as token group by token as tok +group as g order by tok ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hints/issue_251_dataset_hint_6/issue_251_dataset_hint_6.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hints/issue_251_dataset_hint_6/issue_251_dataset_hint_6.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hints/issue_251_dataset_hint_6/issue_251_dataset_hint_6.3.query.sqlpp index 97e4186..b889ba9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hints/issue_251_dataset_hint_6/issue_251_dataset_hint_6.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/hints/issue_251_dataset_hint_6/issue_251_dataset_hint_6.3.query.sqlpp @@ -27,9 +27,10 @@ use test; -select element {'word':tok,'count':test.coll_count(token)} +select element {'word':tok,'count':test.coll_count((from g select value token))} from TextDataset as line, test.`word-tokens`(line.content) as token group by token as tok +group as g order by tok ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/groupby-orderby-count/groupby-orderby-count.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/groupby-orderby-count/groupby-orderby-count.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/groupby-orderby-count/groupby-orderby-count.3.query.sqlpp index ea20ddf..075bfb8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/groupby-orderby-count/groupby-orderby-count.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/groupby-orderby-count/groupby-orderby-count.3.query.sqlpp @@ -20,9 +20,10 @@ use twitter; -select element {'word':tok,'count':twitter.coll_count(token)} +select element {'word':tok,'count':twitter.coll_count(( from g select value token ))} from TwitterData as t, - twitter.`word-tokens`(t.text) as token + `word-tokens`(t.text) as token group by token as tok -order by twitter.coll_count(token) desc,tok +group as g +order by coll_count(( from g select value token )) desc,tok ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/open-closed/query-issue456/query-issue456.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/open-closed/query-issue456/query-issue456.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/open-closed/query-issue456/query-issue456.3.query.sqlpp index 5eb9a68..341481a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/open-closed/query-issue456/query-issue456.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/open-closed/query-issue456/query-issue456.3.query.sqlpp @@ -26,9 +26,10 @@ use test; -select element [x.int_m,test.count(id)] +select element [x.int_m, coll_count(( from g select value x ))] from DataOpen as x -with id as x.id +let id = x.id group by x.int_m +group as g order by x.int_m ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/semistructured/count-nullable/count-nullable.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/semistructured/count-nullable/count-nullable.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/semistructured/count-nullable/count-nullable.3.query.sqlpp index 7102a14..9705de5 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/semistructured/count-nullable/count-nullable.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/semistructured/count-nullable/count-nullable.3.query.sqlpp @@ -20,8 +20,9 @@ use test; -select element {'custage':age,'count':test.coll_count(c)} +select element {'custage':age,'count': coll_count(( from g select value c))} from Customers as c group by c.age as age +group as g order by age ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation-with-filtering/cell-aggregation-with-filtering.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation-with-filtering/cell-aggregation-with-filtering.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation-with-filtering/cell-aggregation-with-filtering.3.query.sqlpp index feaa109..63442c4 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation-with-filtering/cell-aggregation-with-filtering.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation-with-filtering/cell-aggregation-with-filtering.3.query.sqlpp @@ -22,10 +22,11 @@ use test; select element {'cell':c,'count':num} from TwitterData as t -with keywords as 'Allergies', - region as test.polygon('\n\t33.80503407287759,-126.41235263538363 \n\t44.9090773200516,-126.41235263538363 \n\t44.9090773200516,-87.65258701038363 \n\t33.80503407287759,-87.65258701038363') -where (test.`spatial-intersect`(t.loc,region) and (t.time > test.datetime('2011-05-15T00:00:00Z')) and (t.time < test.datetime('2011-05-16T23:59:59Z')) and test.contains(t.text,keywords)) -group by test.`spatial-cell`(t.loc,test.`create-point`(24.5,-125.5),3.0,3.0) as c -with num as test.coll_count(t) +let keywords = 'Allergies', + region = polygon('\n\t33.80503407287759,-126.41235263538363 \n\t44.9090773200516,-126.41235263538363 \n\t44.9090773200516,-87.65258701038363 \n\t33.80503407287759,-87.65258701038363') +where (`spatial-intersect`(t.loc,region) and (t.time > datetime('2011-05-15T00:00:00Z')) and (t.time < datetime('2011-05-16T23:59:59Z')) and contains(t.text,keywords)) +group by `spatial-cell`(t.loc,`create-point`(24.5,-125.5),3.0,3.0) as c +group as g +let num = coll_count((from g select value t)) order by num ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation/cell-aggregation.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation/cell-aggregation.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation/cell-aggregation.3.query.sqlpp index 7d7cfc8..eac020a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation/cell-aggregation.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/spatial/cell-aggregation/cell-aggregation.3.query.sqlpp @@ -23,8 +23,9 @@ use test; with grid as ( select element {'cell':c,'count':num} from MyData as o - group by test.`spatial-cell`(o.loc,test.`create-point`(0.0,0.0),5.0,5.0) as c - with num as test.coll_count(o) + group by `spatial-cell`(o.loc, `create-point`(0.0,0.0),5.0,5.0) as c + group as g + let num = coll_count(( from g select value o )) order by num ) select element g http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp index 1d4cc5b..4a5eae4 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp @@ -27,28 +27,15 @@ use tpch; set `import-private-functions` `true`; -select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.coll_sum(( - select element i.l_quantity - from l as i - )),'sum_base_price':tpch.coll_sum(( - select element i.l_extendedprice - from l as i - )),'sum_disc_price':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i - )),'sum_charge':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax)) - from l as i - )),'ave_qty':tpch.coll_avg(( - select element i.l_quantity - from l as i - )),'ave_price':tpch.coll_avg(( - select element i.l_extendedprice - from l as i - )),'ave_disc':tpch.coll_avg(( - select element i.l_discount - from l as i - )),'count_order':tpch.coll_count(l)} +select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus, + 'sum_qty': sum(l.l_quantity), + 'sum_base_price': sum(l.l_extendedprice), + 'sum_disc_price': sum(l.l_extendedprice * (1 - l.l_discount)), + 'sum_charge': sum(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)), + 'ave_qty': avg(l.l_quantity), + 'ave_price': avg(l.l_extendedprice), + 'ave_disc': avg(l.l_discount), + 'count_order': count(*)} from LineItem as l where (l.l_shipdate <= '1998-09-02') /* +hash */ http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_0/overlap_bins_gby_0.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_0/overlap_bins_gby_0.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_0/overlap_bins_gby_0.3.query.sqlpp index 40fcaea..07fa4fa 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_0/overlap_bins_gby_0.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_0/overlap_bins_gby_0.3.query.sqlpp @@ -26,12 +26,13 @@ use test; select element {'tbin':gen0.bin,'interval':itv,'overlap':test.`get-overlapping-interval`(gen0.bin,itv)} from - ( select element {'bin':bin,'i':i} + ( select element {'bin':bin,'i': (from g select value i) } from (select element i from tsdata as i order by i.time) as i, test.`overlap-bins`(test.`interval-start-from-time`(i.time,i.duration),test.time('00:00:00'),test.`day_time_duration`('PT1H30M')) as j group by j as bin + group as g order by `get-interval-start`(bin) ) as gen0, gen0.i as x -with itv as test.`interval-start-from-time`(x.time,x.duration) +let itv = test.`interval-start-from-time`(x.time,x.duration) order by test.`get-interval-start`(gen0.bin); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp index 941ecbc..ce3159a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp @@ -24,16 +24,19 @@ use test; -select element {'timebin':bin,'count':count(i2),'total_ms':coll_sum(( - select element `ms_from_day_time_duration`(`duration-from-interval`(`get-overlapping-interval`(bin,i3.interval))) - from i2 as i3 +select element {'timebin':bin, + 'count':count((select value 1 from g)), + 'total_ms':coll_sum(( + select element + `ms_from_day_time_duration`(`duration-from-interval`(`get-overlapping-interval`(bin, g.i2.interval))) + from g ))} from ( select element {'interval':`interval-start-from-time`(i1.time,i1.duration)} from tsdata as i1 order by i1.time -) as i2, - `overlap-bins`(i2.interval,time('00:00:00'),`day_time_duration`('PT1H30M')) as j +) as i2, `overlap-bins`(i2.interval,time('00:00:00'),`day_time_duration`('PT1H30M')) as j group by j as bin +group as g order by `get-interval-start`(bin) ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/query-ASTERIXDB-1063.23.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/query-ASTERIXDB-1063.23.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/query-ASTERIXDB-1063.23.query.sqlpp index c6afab6..0cad34a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/query-ASTERIXDB-1063.23.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/query-ASTERIXDB-1063.23.query.sqlpp @@ -19,12 +19,13 @@ use TinySocial; select h as `hour`, - count(t) as `count`, + count(*) as `count`, ( - select min as `minute`, count(k) as `sum` - from t as k - group by `get-minute`(k.`send-time`) as min + select min as `minute`, count(*) as `sum` + from g as k + group by `get-minute`(k.t.`send-time`) as min order by min ) as `finer` from TweetMessages as t -group by `get-hour`(t.`send-time`) as h; +group by `get-hour`(t.`send-time`) as h +group as g; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.15.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.15.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.15.query.sqlpp index feec968..598d509 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.15.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.15.query.sqlpp @@ -23,7 +23,7 @@ use TinySocial; -select element {'user':uid,'count':TinySocial.count(t)} +select element {'user':uid,'count':TinySocial.count(*)} from TweetMessages as t group by t.user.`screen-name` as uid order by uid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.16.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.16.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.16.query.sqlpp index f275bc4..3d114fb 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.16.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.16.query.sqlpp @@ -23,7 +23,7 @@ use TinySocial; -select element {'user':uid,'count':TinySocial.count(t)} +select element {'user':uid,'count':TinySocial.count(*)} from TweetMessages as t /* +hash */ group by t.user.`screen-name` as uid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.17.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.17.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.17.query.sqlpp index 8ad2c9f..c81a86e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.17.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.17.query.sqlpp @@ -26,7 +26,7 @@ use TinySocial; select element {'user':uid,'count':c} from TweetMessages as t group by t.user.`screen-name` as uid -with c as TinySocial.count(t) +with c as TinySocial.count(*) order by c,uid desc limit 3 ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/query-ASTERIXDB-1063.23.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/query-ASTERIXDB-1063.23.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/query-ASTERIXDB-1063.23.query.sqlpp index c6afab6..8ba39bc 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/query-ASTERIXDB-1063.23.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/query-ASTERIXDB-1063.23.query.sqlpp @@ -19,12 +19,14 @@ use TinySocial; select h as `hour`, - count(t) as `count`, + count(*) as `count`, ( - select min as `minute`, count(k) as `sum` - from t as k - group by `get-minute`(k.`send-time`) as min + select min as `minute`, count(*) as `sum` + from g as k + group by `get-minute`(k.t.`send-time`) as min order by min ) as `finer` from TweetMessages as t -group by `get-hour`(t.`send-time`) as h; +group by `get-hour`(t.`send-time`) as h +group as g +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.15.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.15.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.15.query.sqlpp index feec968..598d509 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.15.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.15.query.sqlpp @@ -23,7 +23,7 @@ use TinySocial; -select element {'user':uid,'count':TinySocial.count(t)} +select element {'user':uid,'count':TinySocial.count(*)} from TweetMessages as t group by t.user.`screen-name` as uid order by uid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.16.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.16.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.16.query.sqlpp index f275bc4..3d114fb 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.16.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.16.query.sqlpp @@ -23,7 +23,7 @@ use TinySocial; -select element {'user':uid,'count':TinySocial.count(t)} +select element {'user':uid,'count':TinySocial.count(*)} from TweetMessages as t /* +hash */ group by t.user.`screen-name` as uid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.17.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.17.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.17.query.sqlpp index 8ad2c9f..f1c82e3 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.17.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.17.query.sqlpp @@ -26,7 +26,7 @@ use TinySocial; select element {'user':uid,'count':c} from TweetMessages as t group by t.user.`screen-name` as uid -with c as TinySocial.count(t) +let c = TinySocial.count(*) order by c,uid desc limit 3 ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp index 1286f34..9456961 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp @@ -65,6 +65,6 @@ WHERE i_color = 'orchid' GROUP BY c_last_name ,c_first_name ,s_store_name -HAVING paid > (SELECT value (0.05*avg(netpaid)) - FROM ssales)[0] +GROUP AS g +HAVING paid > (SELECT value 0.05*avg(g.ssales.netpaid) FROM g)[0] ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp index 07fa88f..0c80d1d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp @@ -65,6 +65,6 @@ WHERE i_color = 'chiffon' GROUP BY c_last_name ,c_first_name ,s_store_name -HAVING SUM(netpaid) > (SELECT value (0.05*avg(netpaid)) - FROM ssales)[0] +GROUP AS g +HAVING paid > (SELECT value 0.05*avg(g.ssales.netpaid) FROM g)[0] ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp index ae276b5..e42ce21 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp @@ -19,34 +19,34 @@ use tpch; +set hash_merge "true" -set `import-private-functions` `true`; - -select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.coll_sum(( - select element i.l_quantity - from l as i - )),'sum_base_price':tpch.coll_sum(( - select element i.l_extendedprice - from l as i - )),'sum_disc_price':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i - )),'sum_charge':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax)) - from l as i - )),'ave_qty':tpch.coll_avg(( - select element i.l_quantity - from l as i - )),'ave_price':tpch.coll_avg(( - select element i.l_extendedprice - from l as i - )),'ave_disc':tpch.coll_avg(( - select element i.l_discount - from l as i - )),'count_order':tpch.count(l)} +select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':COLL_SUM(( + select element i.l.l_quantity + from g as i + )),'sum_base_price':COLL_SUM(( + select element i.l.l_extendedprice + from g as i + )),'sum_disc_price':COLL_SUM(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i + )),'sum_charge':COLL_SUM(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount) * (1 + i.l.l_tax)) + from g as i + )),'ave_qty':COLL_AVG(( + select element i.l.l_quantity + from g as i + )),'ave_price':COLL_AVG(( + select element i.l.l_extendedprice + from g as i + )),'ave_disc':COLL_AVG(( + select element i.l.l_discount + from g as i + )),'count_order':COLL_COUNT(( from g select value l ))} from LineItem as l where (l.l_shipdate <= '1998-09-02') /* +hash */ group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus +group as g order by l_returnflag,l_linestatus ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp ---------------------------------------------------------------------- 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 cbb55c4..24180d2 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 @@ -45,9 +45,9 @@ declare function tmp1() { }; declare function tmp2() { ( - select element {'p_partkey':p_partkey,'ps_min_supplycost':tpch.coll_min(( - select element i.ps_supplycost - from pssrn as i + select element {'p_partkey':p_partkey,'ps_min_supplycost':COLL_MIN(( + select element i.pssrn.ps_supplycost + from g as i ))} from Part as p, ( @@ -69,6 +69,7 @@ declare function tmp2() { where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS' /* +hash */ group by pssrn.p_partkey as p_partkey + group as g ) }; select element {'s_acctbal':t1.s_acctbal,'s_name':t1.s_name,'n_name':t1.n_name,'p_partkey':t1.p_partkey,'p_mfgr':t1.p_mfgr,'s_address':t1.s_address,'s_phone':t1.s_phone,'s_comment':t1.s_comment} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp index bddbb53..f076d71 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp @@ -27,9 +27,10 @@ from Customer as c, where (((c.c_mktsegment = 'BUILDING') and (c.c_custkey = o.o_custkey)) and ((l.l_orderkey = o.o_orderkey) and (o.o_orderdate < '1995-03-15') and (l.l_shipdate > '1995-03-15'))) /* +hash */ group by l.l_orderkey as l_orderkey,o.o_orderdate as o_orderdate,o.o_shippriority as o_shippriority -with revenue as tpch.coll_sum(( +group as g +let revenue = COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + from (from g select value l) as i )) order by revenue desc,o_orderdate limit 10 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q04_order_priority/q04_order_priority.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q04_order_priority/q04_order_priority.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q04_order_priority/q04_order_priority.3.query.sqlpp index 5293499..81c19c9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q04_order_priority/q04_order_priority.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q04_order_priority/q04_order_priority.3.query.sqlpp @@ -27,7 +27,7 @@ declare function tmp() { where (l.l_commitdate < l.l_receiptdate) ) }; -select element {'order_priority':o_orderpriority,'count':COLL_COUNT(o)} +select element {'order_priority':o_orderpriority,'count': count(*)} from Orders as o, tpch.tmp() as t where ((o.o_orderkey = t.o_orderkey) and (o.o_orderdate >= '1993-07-01') and (o.o_orderdate < '1993-10-01')) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp index 9050001..b82242e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp @@ -46,9 +46,10 @@ from Customer as c, where ((c.c_nationkey = o1.s_nationkey) and (c.c_custkey = o1.o_custkey)) /* +hash */ group by o1.n_name as n_name -with revenue as tpch.coll_sum(( +group as g +let revenue = COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from o1 as i + from (from g select value o1) as i )) order by revenue desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp index 94ee1cd..b35e791 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp @@ -24,4 +24,4 @@ select sum(l.l_extendedprice * l.l_discount) as revenue from LineItem as l where l.l_shipdate >= '1994-01-01' and l.l_shipdate < '1995-01-01' and l.l_discount >= 0.05 and l.l_discount <= 0.07 and l.l_quantity < 24 -; +; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp index deeec6b..b6344b2 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp @@ -49,9 +49,10 @@ from ( with l_year0 as tpch.`get-year`(locs.l_shipdate) where ((locs.c_nationkey = t.c_nationkey) and (locs.s_nationkey = t.s_nationkey)) group by t.supp_nation as supp_nation,t.cust_nation as cust_nation,l_year0 as l_year -with revenue as tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from locs as i +group as g +let revenue = COLL_SUM(( + select element (i.locs.l_extendedprice * (1 - i.locs.l_discount)) + from g as i )) order by supp_nation,cust_nation,l_year ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q08_national_market_share/q08_national_market_share.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q08_national_market_share/q08_national_market_share.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q08_national_market_share/q08_national_market_share.3.query.sqlpp index a8b4bac..3aea572 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q08_national_market_share/q08_national_market_share.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q08_national_market_share/q08_national_market_share.3.query.sqlpp @@ -21,11 +21,11 @@ use tpch; select element {'year':year,'mkt_share':(tpch.coll_sum(( - select element case when i.s_name = 'BRAZIL' then i.revenue else 0.0 end - from t as i + select element case when i.t.s_name = 'BRAZIL' then i.t.revenue else 0.0 end + from g as i )) / tpch.coll_sum(( - select element i.revenue - from t as i + select element i.t.revenue + from g as i )))} from ( select element {'year':o_year,'revenue':(slnrcop.l_extendedprice * (1 - slnrcop.l_discount)),'s_name':n2.n_name} @@ -65,5 +65,6 @@ from ( where (slnrcop.s_nationkey = n2.n_nationkey) ) as t group by t.year as year +group as g order by year -; +; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp index aa5ccf7..6dfd5af 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp @@ -21,8 +21,8 @@ use tpch; select element {'nation':nation,'o_year':o_year,'sum_profit':COLL_SUM(( - select element pr.amount - from profit as pr + select element pr.profit.amount + from g as pr ))} from ( select element {'nation':l3.n_name,'o_year':o_year,'amount':amount} @@ -48,10 +48,11 @@ from ( ) as l2 where (tpch.contains(p.p_name,'green') and (p.p_partkey = l2.l_partkey)) ) as l3 - with amount as ((l3.l_extendedprice * (1 - l3.l_discount)) - (l3.ps_supplycost * l3.l_quantity)), - o_year as tpch.`get-year`(o.o_orderdate) + let amount = ((l3.l_extendedprice * (1 - l3.l_discount)) - (l3.ps_supplycost * l3.l_quantity)), + o_year = tpch.`get-year`(o.o_orderdate) where (o.o_orderkey = l3.l_orderkey) ) as profit group by profit.nation as nation,profit.o_year as o_year +group as g order by nation,o_year desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item/q10_returned_item.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item/q10_returned_item.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item/q10_returned_item.3.query.sqlpp index cce248c..6038d15 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item/q10_returned_item.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item/q10_returned_item.3.query.sqlpp @@ -34,9 +34,10 @@ from ( where ((l.l_orderkey = ocn.o_orderkey) and (l.l_selectflag = 'R')) ) as locn group by locn.c_custkey as c_custkey,locn.c_name as c_name,locn.c_acctbal as c_acctbal,locn.c_phone as c_phone,locn.n_name as n_name,locn.c_address as c_address,locn.c_comment as c_comment -with revenue as COLL_SUM(( +group as g +let revenue = COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from locn as i + from (from g select value locn) as i )) order by revenue desc limit 20 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp index cce248c..3a6ac4a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp @@ -34,9 +34,10 @@ from ( where ((l.l_orderkey = ocn.o_orderkey) and (l.l_selectflag = 'R')) ) as locn group by locn.c_custkey as c_custkey,locn.c_name as c_name,locn.c_acctbal as c_acctbal,locn.c_phone as c_phone,locn.n_name as n_name,locn.c_address as c_address,locn.c_comment as c_comment -with revenue as COLL_SUM(( +group as g +let revenue = COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from locn as i + from (from g select value locn) as i )) order by revenue desc limit 20 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q11_important_stock/q11_important_stock.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q11_important_stock/q11_important_stock.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q11_important_stock/q11_important_stock.3.query.sqlpp index 073835c..2fb8110 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q11_important_stock/q11_important_stock.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q11_important_stock/q11_important_stock.3.query.sqlpp @@ -20,32 +20,33 @@ use tpch; -with sum as tpch.coll_sum(( +with sum as COLL_SUM(( select element (ps.ps_supplycost * ps.ps_availqty) from Partsupp as ps, ( select element {'s_suppkey':s.s_suppkey} from Supplier as s, Nation as n - where (s.s_nationkey = n.n_nationkey) + where s.s_nationkey = n.n_nationkey ) as sn - where (ps.ps_suppkey = sn.s_suppkey) + where ps.ps_suppkey = sn.s_suppkey )) select element {'partkey':t1.ps_partkey,'part_value':t1.part_value} from ( - select element {'ps_partkey':ps_partkey,'part_value':tpch.coll_sum(( - select element (i.ps_supplycost * i.ps_availqty) - from ps as i + select element {'ps_partkey':ps_partkey,'part_value':COLL_SUM(( + select element (i.ps.ps_supplycost * i.ps.ps_availqty) + from g as i ))} from Partsupp as ps, ( select element {'s_suppkey':s.s_suppkey} from Supplier as s, Nation as n - where (s.s_nationkey = n.n_nationkey) + where s.s_nationkey = n.n_nationkey ) as sn - where (ps.ps_suppkey = sn.s_suppkey) + where ps.ps_suppkey = sn.s_suppkey group by ps.ps_partkey as ps_partkey + group as g ) as t1 where (t1.part_value > (sum * 0.00001)) order by t1.part_value desc http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q12_shipping/q12_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q12_shipping/q12_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q12_shipping/q12_shipping.3.query.sqlpp index c576c6c..a98ec6e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q12_shipping/q12_shipping.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q12_shipping/q12_shipping.3.query.sqlpp @@ -19,23 +19,23 @@ use tpch; - select element {'l_shipmode':l_shipmode,'high_line_count':tpch.coll_sum(( - select element case i.o_orderpriority = '1-URGENT' or i.o_orderpriority = '2-HIGH' + select element case i.o.o_orderpriority = '1-URGENT' or i.o.o_orderpriority = '2-HIGH' when true then 1 when false then 0 end - from o as i + from g as i )),'low_line_count':tpch.coll_sum(( - select element case i.o_orderpriority = '1-URGENT' or i.o_orderpriority = '2-HIGH' + select element case i.o.o_orderpriority = '1-URGENT' or i.o.o_orderpriority = '2-HIGH' when true then 0 when false then 1 end - from o as i + from g as i ))} from LineItem as l, Orders as o where ((o.o_orderkey = l.l_orderkey) and (l.l_commitdate < l.l_receiptdate) and (l.l_shipdate < l.l_commitdate) and (l.l_receiptdate >= '1994-01-01') and (l.l_receiptdate < '1995-01-01') and ((l.l_shipmode = 'MAIL') or (l.l_shipmode = 'SHIP'))) group by l.l_shipmode as l_shipmode +group as g order by l_shipmode ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp ---------------------------------------------------------------------- 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 24f4f46..4823a39 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 @@ -24,9 +24,9 @@ set `import-private-functions` `true`; select element {'c_count':c_count,'custdist':custdist} from ( - select element {'c_custkey':c_custkey,'c_count':tpch.coll_sum(( + select element {'c_custkey':c_custkey,'c_count':COLL_SUM(( select element i.o_orderkey_count - from co as i + from (select value co from g2) as i ))} from ( select element {'c_custkey':c.c_custkey,'o_orderkey_count':coll_count(( @@ -37,8 +37,10 @@ from ( from Customer as c ) as co group by co.c_custkey as c_custkey + group as g2 ) as gco group by gco.c_count as c_count -with custdist as tpch.count(gco) +group as g +let custdist = count(( select value gco from g )) order by custdist desc,c_count desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp ---------------------------------------------------------------------- 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 445da07..2ec6a9c 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 @@ -20,19 +20,16 @@ use tpch; -select element (100.0 * tpch.coll_sum(( - select element case i.p_type like 'PROMO%' - when true then i.l_extendedprice * (1 - i.l_discount) - when false then 0.0 - end - from lp as i - )) / tpch.coll_sum(( +select element (100.0 * COLL_SUM(( + select element case i.p_type like 'PROMO%' when true then i.l_extendedprice * (1 - i.l_discount) else 0.0 end + from (from g select value lp) as i + )) / COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from lp as i + from (from g select value lp) as i ))) from LineItem as l, Part as p let lp = {'p_type': p.p_type, 'l_extendedprice': l.l_extendedprice, 'l_discount': l.l_discount} -where ((l.l_partkey = p.p_partkey) and (l.l_shipdate >= '1995-09-01') and (l.l_shipdate < '1995-10-01')) -group by 1 as t group as g(lp as lp) -; +where l.l_partkey = p.p_partkey and l.l_shipdate >= '1995-09-01' and l.l_shipdate < '1995-10-01' +group by 1 as t +group as g(lp as lp); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp index fdc4fcc..acd8537 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp @@ -22,21 +22,22 @@ use tpch; declare function revenue() { ( - select element {'supplier_no':l_suppkey,'total_revenue':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + select element {'supplier_no':l_suppkey,'total_revenue':COLL_SUM(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i ))} from LineItem as l - where ((l.l_shipdate >= '1996-01-01') and (l.l_shipdate < '1996-04-01')) + where l.l_shipdate >= '1996-01-01' and l.l_shipdate < '1996-04-01' group by l.l_suppkey as l_suppkey + group as g ) }; -with m as tpch.coll_max(( +with m as COLL_MAX(( select element r2.total_revenue - from tpch.revenue() as r2 + from revenue() as r2 )) select element {'s_suppkey':s.s_suppkey,'s_name':s.s_name,'s_address':s.s_address,'s_phone':s.s_phone,'total_revenue':r.total_revenue} from Supplier as s, tpch.revenue() as r -where ((s.s_suppkey = r.supplier_no) and (r.total_revenue < (m + 0.000000001)) and (r.total_revenue > (m - 0.000000001))) +where s.s_suppkey = r.supplier_no and r.total_revenue < (m + 0.000000001) and r.total_revenue > (m - 0.000000001) ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp ---------------------------------------------------------------------- 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 131c8a0..47dc549 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 @@ -41,9 +41,10 @@ from ( group by t.p_brand as p_brand1,t.p_type as p_type1,t.p_size as p_size1,t.ps_suppkey as ps_suppkey1 ) as t2 group by t2.p_brand as p_brand,t2.p_type as p_type,t2.p_size as p_size -with supplier_cnt as coll_count(( +group as g +let supplier_cnt = COLL_COUNT(( select element i.ps_suppkey - from t2 as i + from (from g select value t2) as i )) order by supplier_cnt desc,p_brand,p_type,p_size ;
