Yingyi Bu has uploaded a new change for review.
https://asterix-gerrit.ics.uci.edu/1287
Change subject: Make results in the SQL++ reference doc pretty.
......................................................................
Make results in the SQL++ reference doc pretty.
Change-Id: Ifaba037b023e76b90a0520751b2914757ec191cf
---
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
1 file changed, 713 insertions(+), 131 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/87/1287/1
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..c77ba84 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: newchange
Gerrit-Change-Id: Ifaba037b023e76b90a0520751b2914757ec191cf
Gerrit-PatchSet: 1
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Owner: Yingyi Bu <[email protected]>
