You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@tajo.apache.org by ji...@apache.org on 2015/12/02 13:28:04 UTC
tajo git commit: TAJO-1854: Add in-subquery document.
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 <ji...@apache.org>
Authored: Wed Dec 2 21:27:35 2015 +0900
Committer: Jihoon Son <ji...@apache.org>
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.