Till Westmann has uploaded a new change for review.
https://asterix-gerrit.ics.uci.edu/2631
Change subject: [NO ISSUE] SQL++ doc updates
......................................................................
[NO ISSUE] SQL++ doc updates
Change-Id: I3cdb400893609c578b6467586bcc2d15f2106996
---
M asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/4_error.md
4 files changed, 27 insertions(+), 23 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/31/2631/1
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 93e2f5d..3f8f5e1 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md
@@ -50,7 +50,7 @@
|-----------------------------------------------------------------------------|-----------|
| EXISTS, NOT EXISTS
| Collection emptiness testing |
| ^
| Exponentiation |
-| *, /, %
| Multiplication, division, modulo |
+| *, /, DIV, %, MOD
| Multiplication, division, modulo |
| +, -
| Addition, subtraction |
| ||
| String concatenation |
| IS NULL, IS NOT NULL, IS MISSING, IS NOT MISSING, <br/>IS UNKNOWN, IS NOT
UNKNOWN, IS VALUED, IS NOT VALUED | Unknown value comparison |
@@ -66,13 +66,13 @@
[comparison operators](#Comparison_operators) and [logical
operators](#Logical_operators).
### <a id="Arithmetic_operators">Arithmetic Operators</a>
-Arithmetic operators are used to exponentiate, add, subtract, multiply, and
divide numeric values, or concatenate string values.
+Arithmetic operators are used to exponentiate, add, subtract, multiply, and
divide numeric values, or concatenate string values. For division, / operates
on numbers and returns a number, while DIV offers truncating integer division;
% and MOD are synonyms for the modulo operation.
| Operator | Purpose
| Example |
|--------------|-------------------------------------------------------------------------|------------|
| +, - | As unary operators, they denote a <br/>positive or negative
expression | SELECT VALUE -1; |
| +, - | As binary operators, they add or subtract
| SELECT VALUE 1 + 2; |
-| *, /, % | Multiply, divide, modulo
| SELECT VALUE 4 / 2.0; |
+| *, /, DIV, %, MOD | Multiply, divide (numbers or integers), modulo
| SELECT VALUE 4 / 2.0; |
| ^ | Exponentiation
| SELECT VALUE 2^3; |
| || | String concatenation
| SELECT VALUE "ab"||"c"||"d"; |
@@ -312,7 +312,7 @@
### <a id="Variable_references">Variable References</a>
VariableReference ::= <IDENTIFIER>|<DelimitedIdentifier>
- <IDENTIFIER> ::= <LETTER> (<LETTER> | <DIGIT> | "_" | "$")*
+ <IDENTIFIER> ::= (<LETTER> | "_") (<LETTER> | <DIGIT> | "_" |
"$")*
<LETTER> ::= ["A" - "Z", "a" - "z"]
DelimitedIdentifier ::= "`" (<EscapeQuot>
| <EscapeBslash>
@@ -328,7 +328,7 @@
A variable in SQL++ can be bound to any legal data model value. A variable
reference refers to the value to which an in-scope variable is
bound. (E.g., a variable binding may originate from one of the `FROM`, `WITH`
or `LET` clauses of a `SELECT` statement or from an
input parameter in the context of a function body.) Backticks, for example,
\`id\`, are used for delimited identifiers. Delimiting is needed when
-a variable's desired name clashes with a SQL++ keyword or includes characters
not allowed in regular identifiers.
+a variable's desired name clashes with a SQL++ keyword or includes characters
not allowed in regular identifiers. More information on exactly how variable
references are resolved can be found in the appendix section on Variable
Resolution.
##### Examples
diff --git
a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
index 1d1ab8e..4197546 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_declare_dataverse.md
@@ -21,10 +21,10 @@
DatabaseDeclaration ::= "USE" Identifier
-At the uppermost level, the world of data is organized into data namespaces
called **dataverses**.
-To set the default dataverse for a series of statements, the USE statement is
provided in SQL++.
+At the uppermost level, the world of data is organized into data namespaces
called **namespaces**.
+To set the default namespace for a series of statements, the USE statement is
provided in SQL++.
-As an example, the following statement sets the default dataverse to be
"TinySocial".
+As an example, the following statement sets the default namespace to be
"TinySocial".
##### Example
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 56103e5..d75ebff 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -46,7 +46,7 @@
( ( JoinType )? ( JoinClause | UnnestClause ) )*
JoinClause ::= <JOIN> Expression (( <AS> )? Variable)? <ON>
Expression
- UnnestClause ::= ( <UNNEST> | <CORRELATE> | <FLATTEN> ) Expression
+ UnnestClause ::= ( <UNNEST> ) Expression
( <AS> )? Variable ( <AT> Variable )?
JoinType ::= ( <INNER> | <LEFT> ( <OUTER> )? )
@@ -60,8 +60,8 @@
GroupbyClause ::= <GROUP> <BY> Expression ( ( (<AS>)? Variable )?
( "," Expression ( (<AS>)? Variable )? )* )
( <GROUP> <AS> Variable
- ("(" Variable <AS> VariableReference
- ("," Variable <AS> VariableReference )* ")")?
+ ("(" VariableReference <AS> Identifier
+ ("," VariableReference <AS> Identifier )* ")")?
)?
HavingClause ::= <HAVING> Expression
@@ -446,7 +446,7 @@
In the result, `$1` is the generated name for `substr(user.name, 1)`, while
`alias` is the generated name for `user.alias`.
### <a id="Abbreviated_field_access_expressions">Abbreviated Field Access
Expressions</a>
-As in standard SQL, SQL++ field access expressions can be abbreviated (not
recommended) when there is no ambiguity. In the next example, the variable
`user` is the only possible variable reference for fields `id`, `name` and
`alias` and thus could be omitted in the query.
+As in standard SQL, SQL++ field access expressions can be abbreviated (not
recommended!) when there is no ambiguity. In the next example, the variable
`user` is the only possible variable reference for fields `id`, `name` and
`alias` and thus could be omitted in the query. More information on
abbbreviated field access can be found in the appendix section on Variable
Resolution.
##### Example
@@ -659,6 +659,8 @@
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 );"
+More information on implicit binding variables can be found in the appendix
section on Variable Resolution.
+
## <a id="Join_clauses">JOIN Clauses</a>
The join clause in SQL++ supports both inner joins and left outer joins from
standard SQL.
@@ -724,7 +726,7 @@
In a `GROUP BY` clause, in addition to the binding variable(s) defined for the
grouping key(s), SQL++ allows a user to define a *group variable* by using the
clause's `GROUP AS` extension to denote the resulting group.
After grouping, then, the query's in-scope variables include the grouping
key's binding variables as well as this group variable which will be bound to
one collection value for each group. This per-group collection (i.e., multiset)
value will be a set of nested objects in which each field of the object is the
result of a renamed variable defined in parentheses following the group
variable's name. The `GROUP AS` syntax is as follows:
- <GROUP> <AS> Variable ("(" Variable <AS> VariableReference ("," Variable
<AS> VariableReference )* ")")?
+ <GROUP> <AS> Variable ("(" VariableReference <AS> Identifier (","
VariableReference <AS> Identifier )* ")")?
##### Example
@@ -1199,6 +1201,8 @@
GROUP BY msg.authorId AS authorId
GROUP AS `$1`(msg AS msg);
+<!--- (NOTE: THE FOLLOWING ALIAS FEATURE IS BEING RETRACTED!)
+
### <a id="Column_aliases">Column Aliases</a>
SQL++ also allows column aliases to be used as `ORDER BY` keys.
@@ -1219,6 +1223,8 @@
"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.
Only tuples for which the condition expression evaluates to `TRUE` are
propagated.
@@ -1228,6 +1234,7 @@
The `ORDER BY` clause is used to globally sort data in either ascending order
(i.e., `ASC`) or descending order (i.e., `DESC`).
During ordering, `MISSING` and `NULL` are treated as being smaller than any
other value if they are encountered
in the ordering key(s). `MISSING` is treated as smaller than `NULL` if both
occur in the data being sorted.
+The ordering of values of a given type is consistent with its type's <=
ordering; the ordering of values across types is implementation-defined but
stable.
The following example returns all `GleambookUsers` in descending order by
their number of friends.
##### Example
@@ -1613,27 +1620,25 @@
| 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 |
+| SELECT list | order not preserved | order preserved | Fields in a JSON
object are 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. |
+| 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 |
| IN constant_expr | The constant expression has to be an array or multiset,
i.e., [..,..,...] | The constant collection can be represented as
comma-separated items in a paren pair | Nested collections are 1st class
citizens |
| String literal | Double quotes or single quotes | Single quotes only |
Double quoted strings are pervasive |
| Delimited identifiers | Backticks | Double quotes | Double quoted strings
are pervasive |
-The following SQL-92 features are not implemented yet. However, SQL++ does not
conflict those features:
+The following SQL-92 features are not implemented yet. However, SQL++ does not
conflict with these features:
* CROSS JOIN, NATURAL JOIN, UNION JOIN
* RIGHT and FULL OUTER JOIN
* INTERSECT, EXCEPT, UNION with set semantics
* CAST expression
- * NULLIF expression
* COALESCE expression
* ALL and SOME predicates for linking to subqueries
* UNIQUE predicate (tests a collection for duplicates)
* MATCH predicate (tests for referential integrity)
* Row and Table constructors
- * DISTINCT aggregates
* Preserved order for expressions in a SELECT list
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/4_error.md
b/asterixdb/asterix-doc/src/main/markdown/sqlpp/4_error.md
index e043fc2..e5cea1d 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/4_error.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/4_error.md
@@ -56,7 +56,7 @@
## <a id="Identifier_resolution_errors">Identifier Resolution Errors</a>
-Referring an undefined identifier can cause an error if the identifier
+Referring to an undefined identifier can cause an error if the identifier
cannot be successfully resolved as a valid field access.
##### Example
@@ -64,7 +64,7 @@
SELECT *
FROM GleambookUser user;
-Assume we have a typo in "GleambookUser" which misses the ending "s",
+If we have a typo as above in "GleambookUsers" that misses the dataset name's
ending "s",
we will get an identifier resolution error as follows:
Error: Cannot find dataset GleambookUser in dataverse Default nor an alias
with name GleambookUser!
@@ -74,8 +74,7 @@
SELECT name, message
FROM GleambookUsers u JOIN GleambookMessages m ON m.authorId = u.id;
-If the compiler cannot figure out all possible fields in
-`GleambookUsers` and `GleambookMessages`,
+If the compiler cannot figure out how to resolve an unqualified field name,
which will occur if there is more than one variable in scope (e.g.,
`GleambookUsers u` and `GleambookMessages m` as above),
we will get an identifier resolution error as follows:
Error: Cannot resolve ambiguous alias reference for undefined identifier
name
@@ -94,7 +93,7 @@
Since function `abs` can only process numeric input values,
we will get a type error as follows:
- Error: Arithmetic operations are not implemented for string
+ Error: Type mismatch: function abs expects its 1st input parameter to be
type tinyint, smallint, integer, bigint, float or double, but the actual input
type is string
## <a id="Resource_errors">Resource Errors</a>
--
To view, visit https://asterix-gerrit.ics.uci.edu/2631
To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I3cdb400893609c578b6467586bcc2d15f2106996
Gerrit-PatchSet: 1
Gerrit-Project: asterixdb
Gerrit-Branch: release-0.9.4-pre-rc
Gerrit-Owner: Till Westmann <[email protected]>
