Yingyi Bu has submitted this change and it was merged. Change subject: Polish the SQL++ reference doc. ......................................................................
Polish the SQL++ reference doc. Change-Id: Ifb3cc23ebbf9e390d6eabf9386b15550aa5d6d43 Reviewed-on: https://asterix-gerrit.ics.uci.edu/1358 Sonar-Qube: Jenkins <jenk...@fulliautomatix.ics.uci.edu> Tested-by: Jenkins <jenk...@fulliautomatix.ics.uci.edu> Reviewed-by: Till Westmann <ti...@apache.org> --- M asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md 2 files changed, 38 insertions(+), 21 deletions(-) Approvals: Till Westmann: Looks good to me, approved Jenkins: Verified; No violations found diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md index f3d4311..3fb3121 100644 --- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md +++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md @@ -286,9 +286,8 @@ FloatLiteral ::= <DIGITS> ( "f" | "F" ) | <DIGITS> ( "." <DIGITS> ( "f" | "F" ) )? | "." <DIGITS> ( "f" | "F" ) - DoubleLiteral ::= <DIGITS> - | <DIGITS> ( "." <DIGITS> )? - | "." <DIGITS> + DoubleLiteral ::= <DIGITS> "." <DIGITS> + | "." <DIGITS> Literals (constants) in SQL++ can be strings, integers, floating point values, double values, boolean constants, or special constant values like `NULL` and `MISSING`. The `NULL` value is like a `NULL` in SQL; it is used to represent an unknown field value. The specialy value `MISSING` is only meaningful in the context of SQL++ field accesses; it occurs when the accessed field simply does not exist at all in a object being accessed. 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 dc760ba..b90eb06 100644 --- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md +++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md @@ -96,6 +96,19 @@ ### <a id="Select_element">SELECT VALUE Clause</a> The `SELECT VALUE` clause in SQL++ returns a collection that contains the results of evaluating the `VALUE` expression, with one evaluation being performed per "binding tuple" (i.e., per `FROM` clause item) satisfying the statement's selection criteria. For historical reasons SQL++ also allows the keywords `ELEMENT` or `RAW` to be used in place of `VALUE` (not recommended). + +The following example shows a standard-alone `SELECT VALUE`, which wraps a value into an array. + +##### Example + + SELECT VALUE 1; + +This query return: + + [ + 1 + ] + The following example shows a query that selects one user from the GleambookUsers collection. ##### Example @@ -745,9 +758,10 @@ ##### Example - SELECT uid, (SELECT VALUE m.msg FROM msgs m) AS msgs - FROM GleambookMessages message - GROUP BY message.authorId AS uid GROUP AS msgs(message AS msg); + SELECT uid, (SELECT VALUE g.msg FROM g) AS msgs + FROM GleambookMessages gbm + GROUP BY gbm.authorId AS uid + GROUP AS g(gbm as msg); This variant of the example query returns: @@ -836,8 +850,9 @@ ##### Example SELECT uid, msg AS msgs - FROM GleambookMessages message - GROUP BY message.authorId AS uid GROUP AS msgs(message AS msg); + FROM GleambookMessages gbm + GROUP BY gbm.authorId AS uid + GROUP AS g(gbm as msg); This variant of the query exploits a bit of SQL-style "syntactic sugar" that SQL++ offers to shorten some user queries. In particular, in the `SELECT` list, the reference to the `GROUP` variable field `msg` -- because it references a field of the group variable -- is allowed but is "pluralized". As a result, the `msg` reference in the `SELECT` list is @@ -849,13 +864,14 @@ ##### Example SELECT uid, - (SELECT VALUE m.msg - FROM msgs m - WHERE m.msg.message LIKE '% like%' - ORDER BY m.msg.messageId + (SELECT VALUE g.msg + FROM g + WHERE g.msg.message LIKE '% like%' + ORDER BY g.msg.messageId LIMIT 2) AS msgs - FROM GleambookMessages message - GROUP BY message.authorId AS uid GROUP AS msgs(message AS msg); + FROM GleambookMessages gbm + GROUP BY gbm.authorId AS uid + GROUP AS g(gbm as msg); This example query returns: @@ -913,13 +929,14 @@ ##### Example SELECT authorId, - (SELECT VALUE g.msg - FROM g - WHERE g.msg.message LIKE '% like%' - ORDER BY g.msg.messageId - LIMIT 2) AS msgs + (SELECT VALUE g.msg + FROM g + WHERE g.msg.message LIKE '% like%' + ORDER BY g.msg.messageId + LIMIT 2) AS msgs FROM GleambookMessages gbm - GROUP BY gbm.authorId GROUP AS g(gbm as msg); + GROUP BY gbm.authorId + GROUP AS g(gbm as msg); This query returns: @@ -1020,7 +1037,7 @@ ### <a id="Aggregation_functions">Aggregation functions</a> In traditional SQL, which doesn't support nested data, grouping always also involves the use of aggregation -compute properties of the groups (e.g., the average number of messages per user rather than the actual set +to compute properties of the groups (e.g., the average number of messages per user rather than the actual set of messages per user). Each aggregation function in SQL++ takes a collection (e.g., the group of messages) as its input and produces a scalar value as its output. @@ -1548,6 +1565,7 @@ | Feature | SQL++ | SQL-92 | Why different? | |----------|--------|-------|------------------| | SELECT * | Returns nested objects | Returns flattened concatenated objects | Nested collections are 1st class citizens | +| SELECT list | order not preserved | order preserved | Fields in a JSON object is not ordered | | Subquery | Returns a collection | The returned collection is cast into a scalar value if the subquery appears in a SELECT list or on one side of a comparison or as input to a function | Nested collections are 1st class citizens | | LEFT OUTER JOIN | Fills in `MISSING`(s) for non-matches | Fills in `NULL`(s) for non-matches | "Absence" is more appropriate than "unknown" here. | | UNION ALL | Allows heterogeneous inputs and output | Input streams must be UNION-compatible and output field names are drawn from the first input stream | Heterogenity and nested collections are common | -- To view, visit https://asterix-gerrit.ics.uci.edu/1358 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ifb3cc23ebbf9e390d6eabf9386b15550aa5d6d43 Gerrit-PatchSet: 4 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Yingyi Bu <buyin...@gmail.com> Gerrit-Reviewer: Jenkins <jenk...@fulliautomatix.ics.uci.edu> Gerrit-Reviewer: Till Westmann <ti...@apache.org> Gerrit-Reviewer: Yingyi Bu <buyin...@gmail.com>
