Dmitry Lychagin has submitted this change and it was merged. Change subject: [NO ISSUE][DOC] Documentation improvements ......................................................................
[NO ISSUE][DOC] Documentation improvements - user model changes: no - storage format changes: no - interface changes: no Details: - Add variable.* to SELECT clause - Add section and example for variable scope in JOIN subclauses Change-Id: I654fb35f3632cb46fc0ecdce3f12af72c2356091 Reviewed-on: https://asterix-gerrit.ics.uci.edu/2993 Integration-Tests: Jenkins <[email protected]> Sonar-Qube: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Reviewed-by: Till Westmann <[email protected]> --- M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_3_resolution.md 2 files changed, 102 insertions(+), 5 deletions(-) Approvals: Anon. E. Moose #1000171: 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 22313d0..365c6e3 100644 --- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md +++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md @@ -39,7 +39,7 @@ SelectClause ::= <SELECT> ( <ALL> | <DISTINCT> )? ( SelectRegular | SelectValue ) SelectRegular ::= Projection ( "," Projection )* SelectValue ::= ( <VALUE> | <ELEMENT> | <RAW> ) Expression - Projection ::= ( Expression ( <AS> )? Identifier | "*" ) + Projection ::= ( Expression ( <AS> )? Identifier | "*" | Identifier "." "*" ) FromClause ::= <FROM> FromTerm ( "," FromTerm )* FromTerm ::= Expression (( <AS> )? Variable)? @@ -246,7 +246,7 @@ } ] ### <a id="Select_star">SELECT *</a> -`SELECT *` returns a object with a nested field for each input tuple. +`SELECT *` returns an object 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 value is the value of that binding variable. @@ -393,6 +393,84 @@ } } ] +### <a id="Select_variable_star">SELECT _variable_.*</a> + +Whereas `SELECT *` returns all the fields bound to all the variables which are currently defined, +the notation `SELECT c.*` returns all the fields of the object bound to variable `c`. +The variable `c` must be bound to an object for this to work. + +##### Example + + SELECT user.* + FROM GleambookUsers user; + +Compare this query with the first example given under [SELECT *](#Select_star). +This query returns all users from the `GleambookUsers` dataset, +but the `user` variable name is omitted from the results: + + [ + { + "id": 1, + "alias": "Margarita", + "name": "MargaritaStoddard", + "nickname": "Mags", + "userSince": "2012-08-20T10:10:00", + "friendIds": [ + 2, + 3, + 6, + 10 + ], + "employment": [ + { + "organizationName": "Codetechno", + "start-date": "2006-08-06" + }, + { + "organizationName": "geomedia", + "start-date": "2010-06-17", + "end-date": "2010-01-26" + } + ], + "gender": "F" + }, + { + "id": 2, + "alias": "Isbel", + "name": "IsbelDull", + "nickname": "Izzy", + "userSince": "2011-01-22T10:10:00", + "friendIds": [ + 1, + 4 + ], + "employment": [ + { + "organizationName": "Hexviafind", + "startDate": "2010-04-27" + } + ] + }, + { + "id": 3, + "alias": "Emory", + "name": "EmoryUnk", + "userSince": "2012-07-10T10:10:00", + "friendIds": [ + 1, + 5, + 8, + 9 + ], + "employment": [ + { + "organizationName": "geomedia", + "startDate": "2010-06-17", + "endDate": "2010-01-26" + } + ] + } + ] ### <a id="Select_distinct">SELECT DISTINCT</a> The `DISTINCT` keyword is used to eliminate duplicate items in results. The following example shows how it works. @@ -666,7 +744,7 @@ The join clause in the query language supports both inner joins and left outer joins from standard SQL. ### <a id="Inner_joins">Inner joins</a> -Using a `JOIN` clause, the inner join intent from the preceeding examples can also be expressed as follows: +Using a `JOIN` clause, the inner join intent from the preceding examples can also be expressed as follows: ##### Example @@ -719,6 +797,22 @@ ) m; In general, SQL-style join queries can also be expressed by `UNNEST` clauses and left outer join queries can be expressed by `LEFT OUTER UNNESTs`. + +### <a id="Join_variable_scope">Variable scope in JOIN clauses</a> + +Variables defined by `JOIN` subclauses are not visible to other subclauses in the same `FROM` clause. +This also applies to the `FROM` variable that starts the `JOIN` subclause. + +##### Example + + SELECT * FROM GleambookUsers u + JOIN (SELECT VALUE m + FROM GleambookMessages m + WHERE m.authorId = u.id) m + ON u.id = m.authorId; + +The variable `u` defined by the `FROM` clause is not visible inside the `JOIN` subclause, +so this query returns no results. ## <a id="Group_By_clauses">GROUP BY Clauses</a> The `GROUP BY` clause generalizes standard SQL's grouping and aggregation semantics, but it also retains backward compatibility with the standard (relational) SQL `GROUP BY` and aggregation features. @@ -839,9 +933,9 @@ The group variable in the query language makes more complex, composable, nested subqueries over a group possible, which is important given the language's more complex data model (relative to SQL). As a simple example of this, as we really just want the messages associated with each user, we might wish to avoid -the "extra wrapping" of each message as the `msg` field of a object. +the "extra wrapping" of each message as the `msg` field of an object. (That wrapping is useful in more complex cases, but is essentially just in the way here.) -We can use a subquery in the `SELECT` clase to tunnel through the extra nesting and produce the desired result. +We can use a subquery in the `SELECT` clause to tunnel through the extra nesting and produce the desired result. ##### Example diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_3_resolution.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_3_resolution.md index 1357571..c3a6a19 100644 --- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_3_resolution.md +++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_3_resolution.md @@ -197,6 +197,9 @@ SELECT e.name, pay ORDER BY pay +Note that variables defined by `JOIN` subclauses are not visible to other subclauses in the same `FROM` clause. +This also applies to the `FROM` variable that starts the `JOIN` subclause. + ## <a id="Resolving_names">Resolving Names</a> The process of name resolution begins with the leftmost identifier in the name. -- To view, visit https://asterix-gerrit.ics.uci.edu/2993 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: I654fb35f3632cb46fc0ecdce3f12af72c2356091 Gerrit-PatchSet: 4 Gerrit-Project: asterixdb Gerrit-Branch: stabilization-f69489 Gerrit-Owner: Dmitry Lychagin <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Dmitry Lychagin <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Till Westmann <[email protected]>
