Repository: tajo Updated Branches: refs/heads/master 068c79e7b -> 0af53d025
TAJO-1854: Add in-subquery document. Closes #889 Project: http://git-wip-us.apache.org/repos/asf/tajo/repo Commit: http://git-wip-us.apache.org/repos/asf/tajo/commit/0af53d02 Tree: http://git-wip-us.apache.org/repos/asf/tajo/tree/0af53d02 Diff: http://git-wip-us.apache.org/repos/asf/tajo/diff/0af53d02 Branch: refs/heads/master Commit: 0af53d025150d7126fda2117e42a40e2e7f31fb5 Parents: 068c79e Author: Jihoon Son <[email protected]> Authored: Wed Dec 2 21:27:35 2015 +0900 Committer: Jihoon Son <[email protected]> Committed: Wed Dec 2 21:27:35 2015 +0900 ---------------------------------------------------------------------- CHANGES | 2 ++ .../src/main/sphinx/sql_language/joins.rst | 4 +-- .../src/main/sphinx/sql_language/predicates.rst | 35 +++++++++++++++----- .../src/main/sphinx/sql_language/queries.rst | 32 +++++++++--------- 4 files changed, 48 insertions(+), 25 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/tajo/blob/0af53d02/CHANGES ---------------------------------------------------------------------- diff --git a/CHANGES b/CHANGES index f0b5d34..86ed3cd 100644 --- a/CHANGES +++ b/CHANGES @@ -92,6 +92,8 @@ Release 0.12.0 - unreleased TASKS + TAJO-1854: Add in-subquery document. (jihoon) + TAJO-2003: Fix typo in worker configuration. (jinho) TAJO-1962: Add description for session variables. (jihoon) http://git-wip-us.apache.org/repos/asf/tajo/blob/0af53d02/tajo-docs/src/main/sphinx/sql_language/joins.rst ---------------------------------------------------------------------- diff --git a/tajo-docs/src/main/sphinx/sql_language/joins.rst b/tajo-docs/src/main/sphinx/sql_language/joins.rst index a47ff0d..7402063 100644 --- a/tajo-docs/src/main/sphinx/sql_language/joins.rst +++ b/tajo-docs/src/main/sphinx/sql_language/joins.rst @@ -155,6 +155,6 @@ Thus, they can perform join without expensive data shuffle. Tajo provides a session variable for broadcast join configuration. (For more detailed information of session variables, please refer to :doc:`/tsql/variables`.) -* ``DIST_QUERY_BROADCAST_JOIN_THRESHOLD`` is a threshold for broadcast join. Only the relations who are larger than this value can be broadcasted. +* ``BROADCAST_NON_CROSS_JOIN_THRESHOLD`` and ``BROADCAST_CROSS_JOIN_THRESHOLD`` are thresholds for broadcast join. Only the relations who are larger than this threshold can be broadcasted. -You can also apply this configuration system widely by setting ``tajo.dist-query.join.broadcast.threshold-bytes`` in ``${TAJO_HOME}/conf/tajo-site.xml``. \ No newline at end of file +You can also apply this configuration system widely by setting ``tajo.dist-query.broadcast.non-cross-join.threshold-kb`` or ``tajo.dist-query.broadcast.cross-join.threshold-kb`` in ``${TAJO_HOME}/conf/tajo-site.xml``. \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/0af53d02/tajo-docs/src/main/sphinx/sql_language/predicates.rst ---------------------------------------------------------------------- diff --git a/tajo-docs/src/main/sphinx/sql_language/predicates.rst b/tajo-docs/src/main/sphinx/sql_language/predicates.rst index b96376b..8e44e4d 100644 --- a/tajo-docs/src/main/sphinx/sql_language/predicates.rst +++ b/tajo-docs/src/main/sphinx/sql_language/predicates.rst @@ -1,19 +1,19 @@ -***************** +*********** Predicates -***************** +*********** -===================== +============= IN Predicate -===================== +============= -IN predicate provides row and array comparison. +IN predicate provides a comparison of row, array, and result of a subquery. *Synopsis* .. code-block:: sql - column_reference IN (val1, val2, ..., valN) - column_reference NOT IN (val1, val2, ..., valN) + column_reference (NOT) IN (val1, val2, ..., valN) + column_reference (NOT) IN (SELECT ... FROM ...) AS alias_name Examples are as follows: @@ -26,7 +26,7 @@ Examples are as follows: -- this statement filters lists down all the records where col1 value is neither 1, 2 nor 3: SELECT col1, col2 FROM table1 WHERE col1 NOT IN (1, 2, 3); -You can use 'IN clause' on text data domain as follows: +You can use `IN clause` on text data domain as follows: .. code-block:: sql @@ -34,6 +34,25 @@ You can use 'IN clause' on text data domain as follows: SELECT col1, col2 FROM table1 WHERE col2 NOT IN ('tajo', 'hadoop'); +Finally, you can use subqueries in the `IN clause`. + +.. code-block:: sql + + SELECT col1, col2 + FROM table1 + WHERE col3 IN ( + SELECT avg(col2) as avg_col2 + FROM table2 + GROUP BY col1 + HAVING avg_col2 > 100); + + SELECT col1, col2 + FROM table1 + WHERE col3 NOT IN ( + SELECT avg(col2) as avg_col2 + FROM table2 + GROUP BY col1 + HAVING avg_col2 > 100); ================================== String Pattern Matching Predicates http://git-wip-us.apache.org/repos/asf/tajo/blob/0af53d02/tajo-docs/src/main/sphinx/sql_language/queries.rst ---------------------------------------------------------------------- diff --git a/tajo-docs/src/main/sphinx/sql_language/queries.rst b/tajo-docs/src/main/sphinx/sql_language/queries.rst index 185c9cc..d5117d3 100644 --- a/tajo-docs/src/main/sphinx/sql_language/queries.rst +++ b/tajo-docs/src/main/sphinx/sql_language/queries.rst @@ -1,10 +1,10 @@ -************************** +******* Queries -************************** +******* -===================== +======== Overview -===================== +======== *Synopsis* @@ -19,9 +19,9 @@ Overview -===================== +=========== From Clause -===================== +=========== *Synopsis* @@ -120,18 +120,20 @@ both join tables. These common columns appear only once in the output table. If **Subqueries** -Subqueries allow users to specify a derived table. It requires enclosing a SQL statement in parentheses and an alias name. -For example: +A subquery is a query that is nested inside another query. It can be embedded in the FROM and WHERE clauses. + +Example: .. code-block:: sql - FROM (SELECT * FROM table1) AS alias_name + FROM (SELECT col1, sum(col2) FROM table1 WHERE col3 > 0 group by col1 order by col1) AS alias_name + WHERE col1 IN (SELECT col1 FROM table1 WHERE col2 > 0 AND col2 < 100) AS alias_name For more detailed information, please refer to :doc:`joins`. -===================== +============ Where Clause -===================== +============ The syntax of the WHERE Clause is @@ -181,9 +183,9 @@ Aggregation functions can be used with ``DISTINCT`` keywords. It forces an indiv SELECT l_partkey, COUNT(distinct l_quantity), SUM(distinct l_extendedprice) AS total FROM lineitem GROUP BY l_partkey; -========================== +========================= Orderby and Limit Clauses -========================== +========================= *Synopsis* @@ -199,9 +201,9 @@ Orderby and Limit Clauses before or after non-null values in the sort ordering. By default, null values are dealt as if larger than any non-null value; that is, ``NULLS FIRST`` is the default for ``DESC`` order, and ``NULLS LAST`` otherwise. -========================== +================ Window Functions -========================== +================ A window function performs a calculation across multiple table rows that belong to some window frame.
