Till Westmann has submitted this change and it was merged. Change subject: Make results in the SQL++ reference doc pretty. ......................................................................
Make results in the SQL++ reference doc pretty. Change-Id: Ifaba037b023e76b90a0520751b2914757ec191cf Reviewed-on: https://asterix-gerrit.ics.uci.edu/1287 Sonar-Qube: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Integration-Tests: Jenkins <[email protected]> Reviewed-by: Till Westmann <[email protected]> --- M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md 1 file changed, 713 insertions(+), 131 deletions(-) Approvals: Till Westmann: Looks good to me, approved Jenkins: Verified; No violations found; Verified diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md index f1ebc47..5ca0e1f 100644 --- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md +++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md @@ -106,10 +106,31 @@ This query returns: - [ - { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" } - - ] + [{ + "userSince": "2012-08-20T10:10:00.000Z", + "friendIds": [ + 2, + 3, + 6, + 10 + ], + "gender": "F", + "name": "MargaritaStoddard", + "nickname": "Mags", + "alias": "Margarita", + "id": 1, + "employment": [ + { + "organizationName": "Codetechno", + "start-date": "2006-08-06" + }, + { + "end-date": "2010-01-26", + "organizationName": "geomedia", + "start-date": "2010-06-17" + } + ] + } ] ### <a id="SQL_select">SQL-style SELECT</a> In SQL++, the traditional SQL-style `SELECT` syntax is also supported. @@ -123,9 +144,10 @@ Returns: - [ - {"user_alias":"Margarita","user_name":"MargaritaStoddard"} - ] + [ { + "user_name": "MargaritaStoddard", + "user_alias": "Margarita" + } ] ### <a id="Select_star">SELECT *</a> In SQL++, `SELECT *` returns a record with a nested field for each input tuple. Each field has as its field name the name of a binding variable generated by either the `FROM` clause or `GROUP BY` clause in the current enclosing `SELECT` statement, and its field is the value of that binding variable. @@ -137,11 +159,71 @@ Since `user` is the only binding variable generated in the `FROM` clause, this query returns: - [ - { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" } }, - { "user": { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" } }, - { "user": { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } } - ] + [ { + "user": { + "userSince": "2012-08-20T10:10:00.000Z", + "friendIds": [ + 2, + 3, + 6, + 10 + ], + "gender": "F", + "name": "MargaritaStoddard", + "nickname": "Mags", + "alias": "Margarita", + "id": 1, + "employment": [ + { + "organizationName": "Codetechno", + "start-date": "2006-08-06" + }, + { + "end-date": "2010-01-26", + "organizationName": "geomedia", + "start-date": "2010-06-17" + } + ] + } + }, { + "user": { + "userSince": "2011-01-22T10:10:00.000Z", + "friendIds": [ + 1, + 4 + ], + "name": "IsbelDull", + "nickname": "Izzy", + "alias": "Isbel", + "id": 2, + "employment": [ + { + "organizationName": "Hexviafind", + "startDate": "2010-04-27" + } + ] + } + }, { + "user": { + "userSince": "2012-07-10T10:10:00.000Z", + "friendIds": [ + 1, + 5, + 8, + 9 + ], + "name": "EmoryUnk", + "alias": "Emory", + "id": 3, + "employment": [ + { + "organizationName": "geomedia", + "endDate": "2010-01-26", + "startDate": "2010-06-17" + } + ] + } + } ] ### <a id="Select_distinct">SELECT DISTINCT</a> SQL++'s `DISTINCT` keyword is used to eliminate duplicate items in results. The following example shows how it works. @@ -152,11 +234,13 @@ This query returns: - [ - { "foo": 1 }, - { "foo": 2 }, - { "foo": 3 } - ] + [ { + "foo": 1 + }, { + "foo": 2 + }, { + "foo": 3 + } ] ##### Example @@ -164,7 +248,10 @@ This version of the query returns: - [ 1, 2, 3 ] + [ 1 + , 2 + , 3 + ] ### <a id="Unnamed_projections">Unnamed projections</a> Similar to standard SQL, SQL++ supports unnamed projections (a.k.a, unnamed `SELECT` clause items), for which names are generated. @@ -182,9 +269,10 @@ This query outputs: - [ - { "$1": "Stoddard", "alias": "Margarita" } - ] + [ { + "alias": "Margarita", + "$1": "Stoddard" + } ] In the result, `$1` is the generated name for `substr(user.name, 1)`, while `alias` is the generated name for `user.alias`. @@ -199,9 +287,10 @@ Outputs: - [ - { "lname": "Stoddard", "alias": "Margarita" } - ] + [ { + "lname": "Stoddard", + "alias": "Margarita" + } ] ## <a id="Unnest_clauses">UNNEST Clause</a> For each of its input tuples, the `UNNEST` clause flattens a collection-valued expression into individual items, producing multiple tuples, each of which is one of the expression's original input tuples augmented with a flattened item from its collection. @@ -218,10 +307,13 @@ This query returns: - [ - { "userId": 1, "orgName": "Codetechno" }, - { "userId": 1, "orgName": "geomedia" } - ] + [ { + "orgName": "Codetechno", + "userId": 1 + }, { + "orgName": "geomedia", + "userId": 1 + } ] Note that `UNNEST` has SQL's inner join semantics --- that is, if a user has no employment history, no tuple corresponding to that user will be emitted in the result. @@ -237,9 +329,9 @@ Returns: - [ - { "userId": 1 } - ] + [ { + "userId": 1 + } ] Note that if `u.hobbies` is an empty collection or leads to a `MISSING` (as above) or `NULL` value for a given input tuple, there is no corresponding binding value for variable `h` for an input tuple. A `MISSING` value will be generated for `h` so that the input tuple can still be propagated. @@ -256,15 +348,28 @@ This returns: - [ - { "uname": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" }, - { "uname": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" }, - { "uname": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" }, - { "uname": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" }, - { "uname": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" }, - { "uname": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" }, - { "uname": "IsbelDull", "message": " like samsung the plan is amazing" } - ] + [ { + "uname": "MargaritaStoddard", + "message": " can't stand at&t its plan is terrible" + }, { + "uname": "MargaritaStoddard", + "message": " dislike iphone its touch-screen is horrible" + }, { + "uname": "MargaritaStoddard", + "message": " can't stand at&t the network is horrible:(" + }, { + "uname": "MargaritaStoddard", + "message": " like verizon the 3G is awesome:)" + }, { + "uname": "MargaritaStoddard", + "message": " can't stand motorola the touch-screen is terrible" + }, { + "uname": "IsbelDull", + "message": " like t-mobile its platform is mind-blowing" + }, { + "uname": "IsbelDull", + "message": " like samsung the plan is amazing" + } ] Similarly, the above query can also be expressed as the `UNNEST`ing of a correlated SQL++ subquery: @@ -345,15 +450,28 @@ Returns: - [ - { "name": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" }, - { "name": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" }, - { "name": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" }, - { "name": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" }, - { "name": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" }, - { "name": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" }, - { "name": "IsbelDull", "message": " like samsung the plan is amazing" } - ] + [ { + "name": "MargaritaStoddard", + "message": " like verizon the 3G is awesome:)" + }, { + "name": "MargaritaStoddard", + "message": " can't stand motorola the touch-screen is terrible" + }, { + "name": "MargaritaStoddard", + "message": " can't stand at&t its plan is terrible" + }, { + "name": "MargaritaStoddard", + "message": " dislike iphone its touch-screen is horrible" + }, { + "name": "MargaritaStoddard", + "message": " can't stand at&t the network is horrible:(" + }, { + "name": "IsbelDull", + "message": " like samsung the plan is amazing" + }, { + "name": "IsbelDull", + "message": " like t-mobile its platform is mind-blowing" + } ] ##### Example @@ -367,11 +485,8 @@ Returns: - Error: Need an alias for the enclosed expression: - (select element $GleambookMessages - from $GleambookMessages as $GleambookMessages - where ($GleambookMessages.authorId = $GleambookUsers.id) - ) + Error: "Syntax error: Need an alias for the enclosed expression:\n(select element GleambookMessages\n from GleambookMessages as GleambookMessages\n where (GleambookMessages.authorId = GleambookUsers.id)\n )", + "query_from_user": "use TinySocial;\n\nSELECT GleambookUsers.name, GleambookMessages.message\n FROM GleambookUsers,\n (\n SELECT VALUE GleambookMessages\n FROM GleambookMessages\n WHERE GleambookMessages.authorId = GleambookUsers.id\n );" ## <a id="Join_clauses">JOIN clauses</a> The join clause in SQL++ supports both inner joins and left outer joins from standard SQL. @@ -392,16 +507,30 @@ Returns: - [ - { "uname": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" }, - { "uname": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" }, - { "uname": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" }, - { "uname": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" }, - { "uname": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" }, - { "uname": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" }, - { "uname": "IsbelDull", "message": " like samsung the plan is amazing" }, - { "uname": "EmoryUnk" } - ] + [ { + "uname": "MargaritaStoddard", + "message": " like verizon the 3G is awesome:)" + }, { + "uname": "MargaritaStoddard", + "message": " can't stand motorola the touch-screen is terrible" + }, { + "uname": "MargaritaStoddard", + "message": " can't stand at&t its plan is terrible" + }, { + "uname": "MargaritaStoddard", + "message": " dislike iphone its touch-screen is horrible" + }, { + "uname": "MargaritaStoddard", + "message": " can't stand at&t the network is horrible:(" + }, { + "uname": "IsbelDull", + "message": " like samsung the plan is amazing" + }, { + "uname": "IsbelDull", + "message": " like t-mobile its platform is mind-blowing" + }, { + "uname": "EmoryUnk" + } ] For non-matching left-side tuples, SQL++ produces `MISSING` values for the right-side binding variables; that is why the last record in the above result doesn't have a `message` field. Note that this is slightly different from standard SQL, which instead would fill in `NULL` values for the right-side fields. The reason for this difference is that, for non-matches in its join results, SQL++ views fields from the right-side as being "not there" (a.k.a. `MISSING`) instead of as being "there but unknown" (i.e., `NULL`). @@ -434,15 +563,99 @@ This first example query returns: - [ - { "uid": 1, "msgs": [ { "msg": { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } }, - { "msg": { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" } }, - { "msg": { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" } }, - { "msg": { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" } }, - { "msg": { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } } ] }, - { "uid": 2, "msgs": [ { "msg": { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } }, - { "msg": { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } } ] } - ] + [ { + "msgs": [ + { + "msg": { + "senderLocation": [ + 38.97, + 77.49 + ], + "inResponseTo": 1, + "messageId": 11, + "authorId": 1, + "message": " can't stand at&t its plan is terrible" + } + }, + { + "msg": { + "senderLocation": [ + 41.66, + 80.87 + ], + "inResponseTo": 4, + "messageId": 2, + "authorId": 1, + "message": " dislike iphone its touch-screen is horrible" + } + }, + { + "msg": { + "senderLocation": [ + 37.73, + 97.04 + ], + "inResponseTo": 2, + "messageId": 4, + "authorId": 1, + "message": " can't stand at&t the network is horrible:(" + } + }, + { + "msg": { + "senderLocation": [ + 40.33, + 80.87 + ], + "inResponseTo": 11, + "messageId": 8, + "authorId": 1, + "message": " like verizon the 3G is awesome:)" + } + }, + { + "msg": { + "senderLocation": [ + 42.5, + 70.01 + ], + "inResponseTo": 12, + "messageId": 10, + "authorId": 1, + "message": " can't stand motorola the touch-screen is terrible" + } + } + ], + "uid": 1 + }, { + "msgs": [ + { + "msg": { + "senderLocation": [ + 31.5, + 75.56 + ], + "inResponseTo": 1, + "messageId": 6, + "authorId": 2, + "message": " like t-mobile its platform is mind-blowing" + } + }, + { + "msg": { + "senderLocation": [ + 48.09, + 81.01 + ], + "inResponseTo": 4, + "messageId": 3, + "authorId": 2, + "message": " like samsung the plan is amazing" + } + } + ], + "uid": 2 + } ] As we can see from the above query result, each group in the example query's output has an associated group variable value called `msgs` that appears in the `SELECT *`'s result. @@ -464,13 +677,85 @@ This variant of the example query returns: - { "uid": 1, "msgs": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" }, - { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" }, - { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" }, - { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" }, - { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } ] }, - { "uid": 2, "msgs": [ { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" }, - { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } ] } + [ { + "msgs": [ + { + "senderLocation": [ + 38.97, + 77.49 + ], + "inResponseTo": 1, + "messageId": 11, + "authorId": 1, + "message": " can't stand at&t its plan is terrible" + }, + { + "senderLocation": [ + 41.66, + 80.87 + ], + "inResponseTo": 4, + "messageId": 2, + "authorId": 1, + "message": " dislike iphone its touch-screen is horrible" + }, + { + "senderLocation": [ + 37.73, + 97.04 + ], + "inResponseTo": 2, + "messageId": 4, + "authorId": 1, + "message": " can't stand at&t the network is horrible:(" + }, + { + "senderLocation": [ + 40.33, + 80.87 + ], + "inResponseTo": 11, + "messageId": 8, + "authorId": 1, + "message": " like verizon the 3G is awesome:)" + }, + { + "senderLocation": [ + 42.5, + 70.01 + ], + "inResponseTo": 12, + "messageId": 10, + "authorId": 1, + "message": " can't stand motorola the touch-screen is terrible" + } + ], + "uid": 1 + }, { + "msgs": [ + { + "senderLocation": [ + 31.5, + 75.56 + ], + "inResponseTo": 1, + "messageId": 6, + "authorId": 2, + "message": " like t-mobile its platform is mind-blowing" + }, + { + "senderLocation": [ + 48.09, + 81.01 + ], + "inResponseTo": 4, + "messageId": 3, + "authorId": 2, + "message": " like samsung the plan is amazing" + } + ], + "uid": 2 + } ] Because this is a fairly common case, a third variant with output identical to the second variant is also possible: @@ -500,11 +785,45 @@ This example query returns: - [ - { "uid": 1, "msgs": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } ] }, - { "uid": 2, "msgs": [ { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" }, - { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } ] } - ] + [ { + "msgs": [ + { + "senderLocation": [ + 40.33, + 80.87 + ], + "inResponseTo": 11, + "messageId": 8, + "authorId": 1, + "message": " like verizon the 3G is awesome:)" + } + ], + "uid": 1 + }, { + "msgs": [ + { + "senderLocation": [ + 48.09, + 81.01 + ], + "inResponseTo": 4, + "messageId": 3, + "authorId": 2, + "message": " like samsung the plan is amazing" + }, + { + "senderLocation": [ + 31.5, + 75.56 + ], + "inResponseTo": 1, + "messageId": 6, + "authorId": 2, + "message": " like t-mobile its platform is mind-blowing" + } + ], + "uid": 2 + } ] ### <a id="Implicit_group_key_variables">Implicit grouping key variables</a> In the SQL++ syntax, providing named binding variables for `GROUP BY` key expressions is optional. @@ -530,11 +849,45 @@ This query returns: - [ - { "authorId": 1, "msgs": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } ] }, - { "authorId": 2, "msgs": [ { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" }, - { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } ] } - ] + [ { + "msgs": [ + { + "senderLocation": [ + 40.33, + 80.87 + ], + "inResponseTo": 11, + "messageId": 8, + "authorId": 1, + "message": " like verizon the 3G is awesome:)" + } + ], + "authorId": 1 + }, { + "msgs": [ + { + "senderLocation": [ + 48.09, + 81.01 + ], + "inResponseTo": 4, + "messageId": 3, + "authorId": 2, + "message": " like samsung the plan is amazing" + }, + { + "senderLocation": [ + 31.5, + 75.56 + ], + "inResponseTo": 1, + "messageId": 6, + "authorId": 2, + "message": " like t-mobile its platform is mind-blowing" + } + ], + "authorId": 2 + } ] Based on the three variable generation rules, the generated variable for the grouping key expression `message.authorId` is `authorId` (which is how it is referred to in the example's `SELECT` clause). @@ -559,11 +912,24 @@ This query returns: - [ - { "uid": 1, "msgs": [ { "message": " like verizon the 3G is awesome:)" } ] }, - { "uid": 2, "msgs": [ { "message": " like samsung the plan is amazing" }, - { "message": " like t-mobile its platform is mind-blowing" } ] } - ] + [ { + "msgs": [ + { + "message": " like verizon the 3G is awesome:)" + } + ], + "uid": 1 + }, { + "msgs": [ + { + "message": " like samsung the plan is amazing" + }, + { + "message": " like t-mobile its platform is mind-blowing" + } + ], + "uid": 2 + } ] Note that in the query above, in principle, `message` is not an in-scope variable in the `SELECT` clause. However, the query above is a syntactically-sugared simplification of the following query and it is thus @@ -627,10 +993,13 @@ This query returns: - [ - { "uid": 1, "msgCnt": 5 }, - { "uid": 2, "msgCnt": 2 } - ] + [ { + "uid": 1, + "msgCnt": 5 + }, { + "uid": 2, + "msgCnt": 2 + } ] Notice how the query forms groups where each group involves a message author and their messages. (SQL cannot do this because the grouped intermediate result is non-1NF in nature.) @@ -675,10 +1044,13 @@ This query outputs: - [ - { "authorId": 1, "$1": 5 }, - { "authorId": 2, "$1": 2 } - ] + [ { + "authorId": 1, + "$1": 5 + }, { + "authorId": 2, + "$1": 2 + } ] In principle, a `msg` reference in the query's `SELECT` clause would be "sugarized" as a collection (as described in [Implicit group variables](#Implicit_group_variables)). @@ -701,10 +1073,13 @@ This query returns: - [ - { "aid": 1, "$1": 5 }, - { "aid": 2, "$1": 2 } - ] + [ { + "$1": 5, + "aid": 1 + }, { + "$1": 2, + "aid": 2 + } ] ## <a id="Where_having_clauses">WHERE clauses and HAVING clauses</a> Both `WHERE` clauses and `HAVING` clauses are used to filter input data based on a condition expression. @@ -725,11 +1100,65 @@ This query returns: - [ - { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, - { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } - { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" } - ] + [ { + "userSince": "2012-08-20T10:10:00.000Z", + "friendIds": [ + 2, + 3, + 6, + 10 + ], + "gender": "F", + "name": "MargaritaStoddard", + "nickname": "Mags", + "alias": "Margarita", + "id": 1, + "employment": [ + { + "organizationName": "Codetechno", + "start-date": "2006-08-06" + }, + { + "end-date": "2010-01-26", + "organizationName": "geomedia", + "start-date": "2010-06-17" + } + ] + }, { + "userSince": "2012-07-10T10:10:00.000Z", + "friendIds": [ + 1, + 5, + 8, + 9 + ], + "name": "EmoryUnk", + "alias": "Emory", + "id": 3, + "employment": [ + { + "organizationName": "geomedia", + "endDate": "2010-01-26", + "startDate": "2010-06-17" + } + ] + }, { + "userSince": "2011-01-22T10:10:00.000Z", + "friendIds": [ + 1, + 4 + ], + "name": "IsbelDull", + "nickname": "Izzy", + "alias": "Isbel", + "id": 2, + "employment": [ + { + "organizationName": "Hexviafind", + "startDate": "2010-04-27" + } + ] + } ] ## <a id="Limit_clauses">LIMIT clauses</a> The `LIMIT` clause is used to limit the result set to a specified constant size. @@ -744,9 +1173,31 @@ This query returns: - [ - { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" } - ] + [ { + "userSince": "2012-08-20T10:10:00.000Z", + "friendIds": [ + 2, + 3, + 6, + 10 + ], + "gender": "F", + "name": "MargaritaStoddard", + "nickname": "Mags", + "alias": "Margarita", + "id": 1, + "employment": [ + { + "organizationName": "Codetechno", + "start-date": "2006-08-06" + }, + { + "end-date": "2010-01-26", + "organizationName": "geomedia", + "start-date": "2010-06-17" + } + ] + } ] ## <a id="With_clauses">WITH clauses</a> As in standard SQL, `WITH` clauses are available to improve the modularity of a query. @@ -764,10 +1215,49 @@ This query returns: - [ - { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, - { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } - ] + [ { + "userSince": "2012-08-20T10:10:00.000Z", + "friendIds": [ + 2, + 3, + 6, + 10 + ], + "gender": "F", + "name": "MargaritaStoddard", + "nickname": "Mags", + "alias": "Margarita", + "id": 1, + "employment": [ + { + "organizationName": "Codetechno", + "start-date": "2006-08-06" + }, + { + "end-date": "2010-01-26", + "organizationName": "geomedia", + "start-date": "2010-06-17" + } + ] + }, { + "userSince": "2012-07-10T10:10:00.000Z", + "friendIds": [ + 1, + 5, + 8, + 9 + ], + "name": "EmoryUnk", + "alias": "Emory", + "id": 3, + "employment": [ + { + "organizationName": "geomedia", + "endDate": "2010-01-26", + "startDate": "2010-06-17" + } + ] + } ] The query is equivalent to the following, more complex, inlined form of the query: @@ -806,10 +1296,85 @@ This query lists `GleambookUsers` that have posted `GleambookMessages` and shows all authored messages for each listed user. It returns: - [ - { "messages": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" }, { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" }, { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" }, { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" }, { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } ], "uname": "MargaritaStoddard" }, - { "messages": [ { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" }, { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } ], "uname": "IsbelDull" } - ] + [ { + "uname": "MargaritaStoddard", + "messages": [ + { + "senderLocation": [ + 38.97, + 77.49 + ], + "inResponseTo": 1, + "messageId": 11, + "authorId": 1, + "message": " can't stand at&t its plan is terrible" + }, + { + "senderLocation": [ + 41.66, + 80.87 + ], + "inResponseTo": 4, + "messageId": 2, + "authorId": 1, + "message": " dislike iphone its touch-screen is horrible" + }, + { + "senderLocation": [ + 37.73, + 97.04 + ], + "inResponseTo": 2, + "messageId": 4, + "authorId": 1, + "message": " can't stand at&t the network is horrible:(" + }, + { + "senderLocation": [ + 40.33, + 80.87 + ], + "inResponseTo": 11, + "messageId": 8, + "authorId": 1, + "message": " like verizon the 3G is awesome:)" + }, + { + "senderLocation": [ + 42.5, + 70.01 + ], + "inResponseTo": 12, + "messageId": 10, + "authorId": 1, + "message": " can't stand motorola the touch-screen is terrible" + } + ] + }, { + "uname": "IsbelDull", + "messages": [ + { + "senderLocation": [ + 31.5, + 75.56 + ], + "inResponseTo": 1, + "messageId": 6, + "authorId": 2, + "message": " like t-mobile its platform is mind-blowing" + }, + { + "senderLocation": [ + 48.09, + 81.01 + ], + "inResponseTo": 4, + "messageId": 3, + "authorId": 2, + "message": " like samsung the plan is amazing" + } + ] + } ] This query is equivalent to the following query that does not use the `LET` clause: @@ -839,10 +1404,11 @@ This query returns: [ - " like t-mobile its platform is mind-blowing", - " like samsung the plan is amazing", - { "uname": "IsbelDull" } - ] + " like t-mobile its platform is mind-blowing" + , { + "uname": "IsbelDull" + }, " like samsung the plan is amazing" + ] ## <a id="Subqueries">Subqueries</a> In SQL++, an arbitrary subquery can appear anywhere that an expression can appear. @@ -865,10 +1431,26 @@ For our sample data set, this query returns: - [ - { "uid": 1, "msgs": [ { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" } ] }, - { "uid": 2, "msgs": [ ] } - ] + [ { + "msgs": [ + { + "senderLocation": [ + 41.66, + 80.87 + ], + "inResponseTo": 4, + "messageId": 2, + "authorId": 1, + "message": " dislike iphone its touch-screen is horrible" + } + ], + "uid": 1 + }, { + "msgs": [ + + ], + "uid": 2 + } ] Note that a subquery, like a top-level `SELECT` statment, always returns a collection -- regardless of where within a query the subquery occurs -- and again, its result is never automatically cast into a scalar. -- To view, visit https://asterix-gerrit.ics.uci.edu/1287 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ifaba037b023e76b90a0520751b2914757ec191cf Gerrit-PatchSet: 3 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Yingyi Bu <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Till Westmann <[email protected]>
