drill-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tshi...@apache.org
Subject [1/3] drill git commit: fix DATE_ADD/SUB
Date Tue, 02 Jun 2015 04:55:33 GMT
Repository: drill
Updated Branches:
  refs/heads/gh-pages 5deca3ed9 -> 1e05eb3e2


http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/083-select-having.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/083-select-having.md b/_docs/sql-reference/sql-commands/083-select-having.md
deleted file mode 100644
index 76828c5..0000000
--- a/_docs/sql-reference/sql-commands/083-select-having.md
+++ /dev/null
@@ -1,51 +0,0 @@
----
-title: "SELECT HAVING"
-parent: "SQL Commands"
----
-The HAVING clause filters group rows created by the GROUP BY clause. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause.
-
-## Syntax
-The HAVING clause supports the following syntax:  
-
-`[ HAVING  boolean_expression ]`  
-
-## Expression  
-A *boolean expression* can include one or more of the following operators:  
-
-  * AND
-  * OR
-  * NOT
-  * IS NULL
-  * IS NOT NULL
-  * LIKE 
-  * BETWEEN
-  * IN
-  * Comparison operators
-  * Quantified comparison operators  
-
-## Usage Notes
-  * Any column referenced in a HAVING clause must be either a grouping column or a column that refers to the result of an aggregate function.
-  * In a HAVING clause, you cannot specify:
-   * An alias that was defined in the select list. You must repeat the original, unaliased expression. 
-   * An ordinal number that refers to a select list item. Only the GROUP BY and ORDER BY clauses accept ordinal numbers.
-
-## Examples
-The following example query uses the HAVING clause to constrain an aggregate result. Drill queries the `dfs.clicks workspace` and  returns the total number of clicks for devices that indicate high click-throughs:  
-
-       0: jdbc:drill:> select t.user_info.device, count(*) from \`clicks/clicks.json\` t 
-       group by t.user_info.device having count(*) > 1000;  
-       
-       +------------+------------+
-       |   EXPR$0   |   EXPR$1   |
-       +------------+------------+
-       | IOS5       | 11814      |
-       | AOS4.2     | 5986       |
-       | IOS6       | 4464       |
-       | IOS7       | 3135       |
-       | AOS4.4     | 1562       |
-       | AOS4.3     | 3039       |
-       +------------+------------+  
-
-The aggregate is a count of the records for each different mobile device in the clickstream data. Only the activity for the devices that registered more than 1000 transactions qualify for the result set.
-
-

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/084-limit-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/084-limit-clause.md b/_docs/sql-reference/sql-commands/084-limit-clause.md
new file mode 100755
index 0000000..459c0c0
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/084-limit-clause.md
@@ -0,0 +1,51 @@
+---
+title: "LIMIT Clause"
+parent: "SQL Commands"
+---
+The LIMIT clause limits the result set to the specified number of rows. You can use LIMIT with or without an ORDER BY clause.
+
+
+## Syntax
+The LIMIT clause supports the following syntax:  
+
+       [ LIMIT { count | ALL } ]
+
+Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
+
+## Parameters
+*count*  
+
+Specifies the maximum number of rows to return.
+If the count expression evaluates to NULL, Drill treats it as LIMIT ALL. 
+
+## Examples
+The following example query includes the ORDER BY and LIMIT clauses and returns the top 20 sales totals by month and state:  
+
+       0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
+       order by 3 desc limit 20;
+       +------------+------------+------------+
+       |   month    |   state    |   sales    |
+       +------------+------------+------------+
+       | May        | ca         | 119586     |
+       | June       | ca         | 116322     |
+       | April      | ca         | 101363     |
+       | March      | ca         | 99540      |
+       | July       | ca         | 90285      |
+       | October    | ca         | 80090      |
+       | June       | tx         | 78363      |
+       | May        | tx         | 77247      |
+       | March      | tx         | 73815      |
+       | August     | ca         | 71255      |
+       | April      | tx         | 68385      |
+       | July       | tx         | 63858      |
+       | February   | ca         | 63527      |
+       | June       | fl         | 62199      |
+       | June       | ny         | 62052      |
+       | May        | fl         | 61651      |
+       | May        | ny         | 59369      |
+       | October    | tx         | 55076      |
+       | March      | fl         | 54867      |
+       | March      | ny         | 52101      |
+       +------------+------------+------------+
+       20 rows selected
+

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/084-select-limit.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/084-select-limit.md b/_docs/sql-reference/sql-commands/084-select-limit.md
deleted file mode 100644
index 9a1d6b5..0000000
--- a/_docs/sql-reference/sql-commands/084-select-limit.md
+++ /dev/null
@@ -1,51 +0,0 @@
----
-title: "SELECT LIMIT"
-parent: "SQL Commands"
----
-The LIMIT clause limits the result set to the specified number of rows. You can use LIMIT with or without an ORDER BY clause.
-
-
-## Syntax
-The LIMIT clause supports the following syntax:  
-
-       [ LIMIT { count | ALL } ]
-
-Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
-
-## Parameters
-*count*  
-
-Specifies the maximum number of rows to return.
-If the count expression evaluates to NULL, Drill treats it as LIMIT ALL. 
-
-## Examples
-The following example query includes the ORDER BY and LIMIT clauses and returns the top 20 sales totals by month and state:  
-
-       0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
-       order by 3 desc limit 20;
-       +------------+------------+------------+
-       |   month    |   state    |   sales    |
-       +------------+------------+------------+
-       | May        | ca         | 119586     |
-       | June       | ca         | 116322     |
-       | April      | ca         | 101363     |
-       | March      | ca         | 99540      |
-       | July       | ca         | 90285      |
-       | October    | ca         | 80090      |
-       | June       | tx         | 78363      |
-       | May        | tx         | 77247      |
-       | March      | tx         | 73815      |
-       | August     | ca         | 71255      |
-       | April      | tx         | 68385      |
-       | July       | tx         | 63858      |
-       | February   | ca         | 63527      |
-       | June       | fl         | 62199      |
-       | June       | ny         | 62052      |
-       | May        | fl         | 61651      |
-       | May        | ny         | 59369      |
-       | October    | tx         | 55076      |
-       | March      | fl         | 54867      |
-       | March      | ny         | 52101      |
-       +------------+------------+------------+
-       20 rows selected
-

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/085-offset-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/085-offset-clause.md b/_docs/sql-reference/sql-commands/085-offset-clause.md
new file mode 100755
index 0000000..92ee478
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/085-offset-clause.md
@@ -0,0 +1,29 @@
+---
+title: "OFFSET Clause"
+parent: "SQL Commands"
+---
+The OFFSET clause provides a way to skip a specified number of first rows in a result set before starting to return any rows.
+
+## Syntax
+The OFFSET clause supports the following syntax:
+
+       [ OFFSET start { ROW | ROWS } ]
+
+Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
+
+## Parameters
+*rows*  
+
+Specifies the number of rows Drill should skip before returning the result set. 
+
+## Usage Notes  
+   * The OFFSET number must be a positive integer and cannot be larger than the number of rows in the underlying result set or no rows are returned.
+   * You can use the OFFSET clause in conjunction with the LIMIT and ORDER BY clauses.
+   * When used with the LIMIT option, OFFSET rows are skipped before starting to count the LIMIT rows that are returned. If the LIMIT option is not used, the number of rows in the result set is reduced by the number of rows that are skipped.
+   * The rows skipped by an OFFSET clause still have to be scanned, so it might be inefficient to use a large OFFSET value.
+
+## Examples
+The following example query returns the result set from row 101 and on, skipping the first 100 rows of the table:
+
+       SELECT * FROM dfs.logs OFFSET 100 ROWS; 
+

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/085-select-offset.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/085-select-offset.md b/_docs/sql-reference/sql-commands/085-select-offset.md
deleted file mode 100644
index 9e97051..0000000
--- a/_docs/sql-reference/sql-commands/085-select-offset.md
+++ /dev/null
@@ -1,29 +0,0 @@
----
-title: "SELECT OFFSET"
-parent: "SQL Commands"
----
-The OFFSET clause provides a way to skip a specified number of first rows in a result set before starting to return any rows.
-
-## Syntax
-The OFFSET clause supports the following syntax:
-
-       [ OFFSET start { ROW | ROWS } ]
-
-Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
-
-## Parameters
-*rows*  
-
-Specifies the number of rows Drill should skip before returning the result set. 
-
-## Usage Notes  
-   * The OFFSET number must be a positive integer and cannot be larger than the number of rows in the underlying result set or no rows are returned.
-   * You can use the OFFSET clause in conjunction with the LIMIT and ORDER BY clauses.
-   * When used with the LIMIT option, OFFSET rows are skipped before starting to count the LIMIT rows that are returned. If the LIMIT option is not used, the number of rows in the result set is reduced by the number of rows that are skipped.
-   * The rows skipped by an OFFSET clause still have to be scanned, so it might be inefficient to use a large OFFSET value.
-
-## Examples
-The following example query returns the result set from row 101 and on, skipping the first 100 rows of the table:
-
-       SELECT * FROM dfs.logs OFFSET 100 ROWS; 
-

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/086-order-by-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/086-order-by-clause.md b/_docs/sql-reference/sql-commands/086-order-by-clause.md
new file mode 100755
index 0000000..10252af
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/086-order-by-clause.md
@@ -0,0 +1,71 @@
+---
+title: "ORDER BY Clause"
+parent: "SQL Commands"
+---
+The ORDER BY clause sorts the result set of a query.
+
+
+
+## Syntax
+The ORDER BY clause supports the following syntax:
+
+       [ ORDER BY expression
+       [ ASC | DESC ]
+       [ NULLS FIRST | NULLS LAST ]
+
+  
+
+## Parameters  
+*expression*  
+
+Defines the sort order of the query result set, typically by specifying one or more columns in the select list.  
+
+You can also specify:  
+
+   * Columns that are not in the select list 
+   * Expressions formed from one or more columns that exist in the tables referenced by the query
+   * Ordinal numbers that represent the position of select list entries (or the position of columns in the table if no select list exists)
+   * Aliases that define select list entries
+   
+When the ORDER BY clause contains multiple expressions, the result set is sorted according to the first expression, then the second expression is applied to rows that have matching values from the first expression, and so on.
+
+ASC  
+Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
+
+DESC  
+Specifies that the results should be returned in descending order. 
+
+NULLS FIRST  
+Specifies that NULL values should be returned before non-NULL values.  
+
+NULLS LAST  
+Specifies that NULL values should be returned after non-NULL values.
+
+## Usage Notes
+   * NULL values are considered "higher" than all other values. With default ascending sort order, NULL values sort at the end.  
+   * When a query does not contain an ORDER BY clause, the system returns result sets with no predictable ordering of the rows. The same query executed twice might return the result set in a different order.  
+   * In any parallel system, when ORDER BY does not produce a unique ordering, the order of the rows is non-deterministic. That is, if the ORDER BY expression produces duplicate values, the return order of those rows may vary from other systems or from one run the system to the next.
+
+## Examples
+The following example query returns sales totals for each month in descending order, listing the highest sales month to the lowest sales month:
+
+       0: jdbc:drill:> select `month`, sum(order_total)
+       from orders group by `month` order by 2 desc;
+       +------------+------------+
+       | month | EXPR$1 |
+       +------------+------------+
+       | June | 950481 |
+       | May | 947796 |
+       | March | 836809 |
+       | April | 807291 |
+       | July | 757395 |
+       | October | 676236 |
+       | August | 572269 |
+       | February | 532901 |
+       | September | 373100 |
+       | January | 346536 |
+       +------------+------------+
+
+
+
+

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/086-select-order-by.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/086-select-order-by.md b/_docs/sql-reference/sql-commands/086-select-order-by.md
deleted file mode 100644
index 794d029..0000000
--- a/_docs/sql-reference/sql-commands/086-select-order-by.md
+++ /dev/null
@@ -1,71 +0,0 @@
----
-title: "SELECT ORDER BY"
-parent: "SQL Commands"
----
-The ORDER BY clause sorts the result set of a query.
-
-
-
-## Syntax
-The ORDER BY clause supports the following syntax:
-
-       [ ORDER BY expression
-       [ ASC | DESC ]
-       [ NULLS FIRST | NULLS LAST ]
-
-  
-
-## Parameters  
-*expression*  
-
-Defines the sort order of the query result set, typically by specifying one or more columns in the select list.  
-
-You can also specify:  
-
-   * Columns that are not in the select list 
-   * Expressions formed from one or more columns that exist in the tables referenced by the query
-   * Ordinal numbers that represent the position of select list entries (or the position of columns in the table if no select list exists)
-   * Aliases that define select list entries
-   
-When the ORDER BY clause contains multiple expressions, the result set is sorted according to the first expression, then the second expression is applied to rows that have matching values from the first expression, and so on.
-
-ASC  
-Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
-
-DESC  
-Specifies that the results should be returned in descending order. 
-
-NULLS FIRST  
-Specifies that NULL values should be returned before non-NULL values.  
-
-NULLS LAST  
-Specifies that NULL values should be returned after non-NULL values.
-
-## Usage Notes
-   * NULL values are considered "higher" than all other values. With default ascending sort order, NULL values sort at the end.  
-   * When a query does not contain an ORDER BY clause, the system returns result sets with no predictable ordering of the rows. The same query executed twice might return the result set in a different order.  
-   * In any parallel system, when ORDER BY does not produce a unique ordering, the order of the rows is non-deterministic. That is, if the ORDER BY expression produces duplicate values, the return order of those rows may vary from other systems or from one run the system to the next.
-
-## Examples
-The following example query returns sales totals for each month in descending order, listing the highest sales month to the lowest sales month:
-
-       0: jdbc:drill:> select `month`, sum(order_total)
-       from orders group by `month` order by 2 desc;
-       +------------+------------+
-       | month | EXPR$1 |
-       +------------+------------+
-       | June | 950481 |
-       | May | 947796 |
-       | March | 836809 |
-       | April | 807291 |
-       | July | 757395 |
-       | October | 676236 |
-       | August | 572269 |
-       | February | 532901 |
-       | September | 373100 |
-       | January | 346536 |
-       +------------+------------+
-
-
-
-

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/087-select-union.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/087-select-union.md b/_docs/sql-reference/sql-commands/087-select-union.md
deleted file mode 100644
index 6d5af65..0000000
--- a/_docs/sql-reference/sql-commands/087-select-union.md
+++ /dev/null
@@ -1,42 +0,0 @@
----
-title: "SELECT UNION"
-parent: "SQL Commands"
----
-The UNION set operator returns all rows in the result sets of two separate query expressions. For example, if two employee tables exist, you can use the UNION set operator to merge the two tables and build a complete list of all the employees. Drill supports UNION ALL only. Drill does not support DISTINCT.
-
-
-## Syntax
-The UNION set operator supports the following syntax:
-
-       query
-       { UNION [ ALL ] }
-       query
-  
-
-## Parameters  
-*query*  
-
-Any SELECT query that Drill supports. See SELECT.
-
-## Usage Notes
-   * The two SELECT query expressions that represent the direct operands of the UNION must produce the same number of columns. Corresponding columns must contain compatible data types. See Supported Data Types.  
-   * Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.  
-   * You cannot use * in UNION ALL for schemaless data.
-
-## Examples
-The following example uses the UNION ALL set operator to combine click activity data before and after a marketing campaign. The data in the example exists in the `dfs.clicks workspace`.
- 
-       0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t 
-       union all 
-       select u.trans_id, u.user_info.cust_id  from `clicks/clicks.json` u limit 5;
-       +-------------+------------+
-       | transaction |  customer  |
-       +-------------+------------+
-       | 35232       | 18520      |
-       | 31995       | 17182      |
-       | 35760       | 18228      |
-       | 37090       | 17015      |
-       | 37838       | 18737      |
-       +-------------+------------+
-
-This UNION ALL query returns rows that exist in two files (and includes any duplicate rows from those files): `clicks.campaign.json` and `clicks.json`
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/087-union-set-operator.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/087-union-set-operator.md b/_docs/sql-reference/sql-commands/087-union-set-operator.md
new file mode 100755
index 0000000..d55c50e
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/087-union-set-operator.md
@@ -0,0 +1,42 @@
+---
+title: "UNION Set Operator"
+parent: "SQL Commands"
+---
+The UNION set operator returns all rows in the result sets of two separate query expressions. For example, if two employee tables exist, you can use the UNION set operator to merge the two tables and build a complete list of all the employees. Drill supports UNION ALL only. Drill does not support DISTINCT.
+
+
+## Syntax
+The UNION set operator supports the following syntax:
+
+       query
+       { UNION ALL }
+       query
+  
+
+## Parameters  
+*query*  
+
+Any SELECT query that Drill supports. See SELECT.
+
+## Usage Notes
+   * The two SELECT query expressions that represent the direct operands of the UNION must produce the same number of columns. Corresponding columns must contain compatible data types. See Supported Data Types.  
+   * Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.  
+   * You cannot use * in UNION ALL for schemaless data.
+
+## Examples
+The following example uses the UNION ALL set operator to combine click activity data before and after a marketing campaign. The data in the example exists in the `dfs.clicks workspace`.
+ 
+       0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t 
+       union all 
+       select u.trans_id, u.user_info.cust_id  from `clicks/clicks.json` u limit 5;
+       +-------------+------------+
+       | transaction |  customer  |
+       +-------------+------------+
+       | 35232       | 18520      |
+       | 31995       | 17182      |
+       | 35760       | 18228      |
+       | 37090       | 17015      |
+       | 37838       | 18737      |
+       +-------------+------------+
+
+This UNION ALL query returns rows that exist in two files (and includes any duplicate rows from those files): `clicks.campaign.json` and `clicks.json`
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/088-select-where.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/088-select-where.md b/_docs/sql-reference/sql-commands/088-select-where.md
deleted file mode 100644
index 5d600fa..0000000
--- a/_docs/sql-reference/sql-commands/088-select-where.md
+++ /dev/null
@@ -1,59 +0,0 @@
----
-title: "SELECT WHERE"
-parent: "SQL Commands"
----
-The WHERE clause selects rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result.
-
-## Syntax
-The WHERE clause supports the following syntax:
-
-       [ WHERE boolean_expression ]  
-
-## Expression  
-A boolean expression can include one or more of the following operators:  
-
-  * AND
-  * OR
-  * NOT
-  * IS NULL
-  * IS NOT NULL
-  * LIKE 
-  * BETWEEN
-  * IN
-  * EXISTS
-  * Comparison operators
-  * Quantified comparison operators
-
-
-## Examples
-The following query compares order totals where the states are California and New York:  
-
-       0: jdbc:drill:> select o1.cust_id, sum(o1.order_total) as ny_sales,
-       (select sum(o2.order_total) from hive.orders o2
-       where o1.cust_id=o2.cust_id and state='ca') as ca_sales
-       from hive.orders o1 where o1.state='ny' group by o1.cust_id
-       order by cust_id limit 20;
-       +------------+------------+------------+
-       |  cust_id   |  ny_sales  |  ca_sales  |
-       +------------+------------+------------+
-       | 1001       | 72         | 47         |
-       | 1002       | 108        | 198        |
-       | 1003       | 83         | null       |
-       | 1004       | 86         | 210        |
-       | 1005       | 168        | 153        |
-       | 1006       | 29         | 326        |
-       | 1008       | 105        | 168        |
-       | 1009       | 443        | 127        |
-       | 1010       | 75         | 18         |
-       | 1012       | 110        | null       |
-       | 1013       | 19         | null       |
-       | 1014       | 106        | 162        |
-       | 1015       | 220        | 153        |
-       | 1016       | 85         | 159        |
-       | 1017       | 82         | 56         |
-       | 1019       | 37         | 196        |
-       | 1020       | 193        | 165        |
-       | 1022       | 124        | null       |
-       | 1023       | 166        | 149        |
-       | 1024       | 233        | null       |
-       +------------+------------+------------+

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/088-where-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/088-where-clause.md b/_docs/sql-reference/sql-commands/088-where-clause.md
new file mode 100755
index 0000000..d233d6d
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/088-where-clause.md
@@ -0,0 +1,73 @@
+---
+title: "WHERE Clause"
+parent: "SQL Commands"
+---
+The WHERE clause selects rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result.
+
+## Syntax
+The WHERE clause supports the following syntax:
+
+       WHERE boolean_expression  
+
+## Expression  
+A boolean expression can include one or more of the following operators:  
+
+  * AND
+  * OR
+  * NOT
+  * IS NULL
+  * IS NOT NULL
+  * LIKE 
+  * BETWEEN
+  * IN
+  * EXISTS
+  * Comparison operators
+  * Quantified comparison operators
+
+
+## Examples
+The following query compares order totals where the states are California and New York:  
+
+       0: jdbc:drill:> select o1.cust_id, sum(o1.order_total) as ny_sales,
+       (select sum(o2.order_total) from hive.orders o2
+       where o1.cust_id=o2.cust_id and state='ca') as ca_sales
+       from hive.orders o1 where o1.state='ny' group by o1.cust_id
+       order by cust_id limit 20;
+       +------------+------------+------------+
+       |  cust_id   |  ny_sales  |  ca_sales  |
+       +------------+------------+------------+
+       | 1001       | 72         | 47         |
+       | 1002       | 108        | 198        |
+       | 1003       | 83         | null       |
+       | 1004       | 86         | 210        |
+       | 1005       | 168        | 153        |
+       | 1006       | 29         | 326        |
+       | 1008       | 105        | 168        |
+       | 1009       | 443        | 127        |
+       | 1010       | 75         | 18         |
+       | 1012       | 110        | null       |
+       | 1013       | 19         | null       |
+       | 1014       | 106        | 162        |
+       | 1015       | 220        | 153        |
+       | 1016       | 85         | 159        |
+       | 1017       | 82         | 56         |
+       | 1019       | 37         | 196        |
+       | 1020       | 193        | 165        |
+       | 1022       | 124        | null       |
+       | 1023       | 166        | 149        |
+       | 1024       | 233        | null       |
+       +------------+------------+------------+  
+
+The following query uses a workspace named `dfw.views` and joins a view named “custview” with a hive table named “orders” to determine sales for each membership type:
+
+       0: jdbc:drill:> select membership, sum(order_total) as sales from hive.orders, custview
+       where orders.cust_id=custview.cust_id
+       group by membership order by 2;
+       +------------+------------+
+       | membership |   sales    |
+       +------------+------------+
+       | "basic"    | 380665     |
+       | "silver"   | 708438     |
+       | "gold"     | 2787682    |
+       +------------+------------+
+       3 rows selected
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/089-select-with.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/089-select-with.md b/_docs/sql-reference/sql-commands/089-select-with.md
deleted file mode 100644
index 4253354..0000000
--- a/_docs/sql-reference/sql-commands/089-select-with.md
+++ /dev/null
@@ -1,95 +0,0 @@
----
-title: "SELECT WITH"
-parent: "SQL Commands"
----
-The WITH clause is an optional clause used to contain one or more common table
-expressions (CTE) where each CTE defines a temporary table that exists for the
-duration of the query. Each subquery in the WITH clause specifies a table
-name, an optional list of column names, and a SELECT statement.
-
-## Syntax
-
-The WITH clause supports the following syntax:
-
-    [ WITH with_subquery [, ...] ]
-    where with_subquery is:
-    with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query ) 
-
-## Parameters
-
-_with_subquery_table_name_
-
-A unique name for a temporary table that defines the results of a WITH clause
-subquery. You cannot use duplicate names within a single WITH clause. You must
-give each subquery a table name that can be referenced in the FROM clause.
-
-_column_name_
-
-An optional list of output column names for the WITH clause subquery,
-separated by commas. The number of column names specified must be equal to or
-less than the number of columns defined by the subquery.
-
-_query_
-
-Any SELECT query that Drill supports. See
-[SELECT]({{ site.baseurl }}/docs/SELECT+Statements).
-
-## Usage Notes
-
-Use the WITH clause to efficiently define temporary tables that Drill can
-access throughout the execution of a single query. The WITH clause is
-typically a simpler alternative to using subqueries in the main body of the
-SELECT statement. In some cases, Drill can evaluate a WITH subquery once and
-reuse the results for query optimization.
-
-You can use a WITH clause in the following SQL statements:
-
-  * SELECT (including subqueries within SELECT statements)
-
-  * CREATE TABLE AS
-
-  * CREATE VIEW
-
-  * EXPLAIN
-
-You can reference the temporary tables in the FROM clause of the query. If the
-FROM clause does not reference any tables defined by the WITH clause, Drill
-ignores the WITH clause and executes the query as normal.
-
-Drill can only reference a table defined by a WITH clause subquery in the
-scope of the SELECT query that the WITH clause begins. For example, you can
-reference such a table in the FROM clause of a subquery in the SELECT list,
-WHERE clause, or HAVING clause. You cannot use a WITH clause in a subquery and
-reference its table in the FROM clause of the main query or another subquery.
-
-You cannot specify another WITH clause inside a WITH clause subquery.
-
-For example, the following query includes a forward reference to table t2 in
-the definition of table t1:
-
-## Example
-
-The following example shows the WITH clause used to create a WITH query named
-`emp_data` that selects all of the rows from the `employee.json` file. The
-main query selects the `full_name, position_title, salary`, and `hire_date`
-rows from the `emp_data` temporary table (created from the WITH subquery) and
-orders the results by the hire date. The `emp_data` table only exists for the
-duration of the query.
-
-**Note:** The `employee.json` file is included with the Drill installation. It is located in the `cp.default` workspace which is configured by default. 
-
-    0: jdbc:drill:zk=local> with emp_data as (select * from cp.`employee.json`) select full_name, position_title, salary, hire_date from emp_data order by hire_date limit 10;
-    +------------------+-------------------------+------------+-----------------------+
-    | full_name        | position_title          |   salary   | hire_date             |
-    +------------------+-------------------------+------------+-----------------------+
-    | Bunny McCown     | Store Assistant Manager | 8000.0     | 1993-05-01 00:00:00.0 |
-    | Danielle Johnson | Store Assistant Manager | 8000.0     | 1993-05-01 00:00:00.0 |
-    | Dick Brummer     | Store Assistant Manager | 7900.0     | 1993-05-01 00:00:00.0 |
-    | Gregory Whiting  | Store Assistant Manager | 10000.0    | 1993-05-01 00:00:00.0 |
-    | Juanita Sharp    | HQ Human Resources      | 6700.0     | 1994-01-01 00:00:00.0 |
-    | Sheri Nowmer     | President               | 80000.0    | 1994-12-01 00:00:00.0 |
-    | Rebecca Kanagaki | VP Human Resources      | 15000.0    | 1994-12-01 00:00:00.0 |
-    | Shauna Wyro      | Store Manager           | 15000.0    | 1994-12-01 00:00:00.0 |
-    | Roberta Damstra  | VP Information Systems  | 25000.0    | 1994-12-01 00:00:00.0 |
-    | Pedro Castillo   | VP Country Manager      | 35000.0    | 1994-12-01 00:00:00.0 |
-    +------------+----------------+--------------+------------------------------------+
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-commands/089-with-clause.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/089-with-clause.md b/_docs/sql-reference/sql-commands/089-with-clause.md
new file mode 100755
index 0000000..5accdce
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/089-with-clause.md
@@ -0,0 +1,97 @@
+---
+title: "WITH Clause"
+parent: "SQL Commands"
+---
+The WITH clause is an optional clause used to contain one or more common table
+expressions (CTE) where each CTE defines a temporary table that exists for the
+duration of the query. Each subquery in the WITH clause specifies a table
+name, an optional list of column names, and a SELECT statement.
+
+## Syntax
+
+The WITH clause supports the following syntax:
+
+    WITH with_subquery [, ...]
+    where with_subquery is:
+    with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query ) 
+
+## Parameters
+
+_with\_subquery\_table\_name_
+
+A unique name for a temporary table that defines the results of a WITH clause
+subquery. You cannot use duplicate names within a single WITH clause. You must
+give each subquery a table name that can be referenced in the FROM clause.
+
+_column\_name_
+
+An optional list of output column names for the WITH clause subquery,
+separated by commas. The number of column names specified must be equal to or
+less than the number of columns defined by the subquery.
+
+_query_
+
+Any SELECT query that Drill supports. See
+[SELECT]({{ site.baseurl }}/docs/SELECT+Statements).
+
+## Usage Notes
+
+Use the WITH clause to efficiently define temporary tables that Drill can
+access throughout the execution of a single query. The WITH clause is
+typically a simpler alternative to using subqueries in the main body of the
+SELECT statement. In some cases, Drill can evaluate a WITH subquery once and
+reuse the results for query optimization.
+
+You can use a WITH clause in the following SQL statements:
+
+  * SELECT (including subqueries within SELECT statements)
+
+  * CREATE TABLE AS
+
+  * CREATE VIEW
+
+  * EXPLAIN
+
+You can reference the temporary tables in the FROM clause of the query. If the
+FROM clause does not reference any tables defined by the WITH clause, Drill
+ignores the WITH clause and executes the query as normal.
+
+Drill can only reference a table defined by a WITH clause subquery in the
+scope of the SELECT query that the WITH clause begins. For example, you can
+reference such a table in the FROM clause of a subquery in the SELECT list,
+WHERE clause, or HAVING clause. You cannot use a WITH clause in a subquery and
+reference its table in the FROM clause of the main query or another subquery.
+
+You cannot specify another WITH clause inside a WITH clause subquery.
+
+For example, the following query includes a forward reference to table t2 in
+the definition of table t1:
+
+## Example
+
+The following example shows the WITH clause used to create a WITH query named
+`emp_data` that selects all of the rows from the `employee.json` file. The
+main query selects the `full_name, position_title, salary`, and `hire_date`
+rows from the `emp_data` temporary table (created from the WITH subquery) and
+orders the results by the hire date. The `emp_data` table only exists for the
+duration of the query.
+
+**Note:** The `employee.json` file is included with the Drill installation. It is located in the `cp.default` workspace which is configured by default. 
+
+       0: jdbc:drill:zk=local> WITH emp_data AS (SELECT * FROM cp.`employee.json`) 
+       SELECT full_name, position_title, salary, hire_date 
+       FROM emp_data ORDER BY hire_date LIMIT 10;
+    +------------------+-------------------------+------------+-----------------------+
+    | full_name        | position_title          |   salary   | hire_date             |
+    +------------------+-------------------------+------------+-----------------------+
+    | Bunny McCown     | Store Assistant Manager | 8000.0     | 1993-05-01 00:00:00.0 |
+    | Danielle Johnson | Store Assistant Manager | 8000.0     | 1993-05-01 00:00:00.0 |
+    | Dick Brummer     | Store Assistant Manager | 7900.0     | 1993-05-01 00:00:00.0 |
+    | Gregory Whiting  | Store Assistant Manager | 10000.0    | 1993-05-01 00:00:00.0 |
+    | Juanita Sharp    | HQ Human Resources      | 6700.0     | 1994-01-01 00:00:00.0 |
+    | Sheri Nowmer     | President               | 80000.0    | 1994-12-01 00:00:00.0 |
+    | Rebecca Kanagaki | VP Human Resources      | 15000.0    | 1994-12-01 00:00:00.0 |
+    | Shauna Wyro      | Store Manager           | 15000.0    | 1994-12-01 00:00:00.0 |
+    | Roberta Damstra  | VP Information Systems  | 25000.0    | 1994-12-01 00:00:00.0 |
+    | Pedro Castillo   | VP Country Manager      | 35000.0    | 1994-12-01 00:00:00.0 |
+    +------------+----------------+--------------+------------------------------------+
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/a03aa49e/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
index a7de7ea..514651c 100644
--- a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
+++ b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
@@ -74,21 +74,27 @@ Returns the sum of a date/time and a number of days/hours, or of a date/time and
 
 ### DATE_ADD Syntax
 
-    DATE_ADD(keyword literal_date, integer)
+    DATE_ADD(keyword literal, integer)
 
     DATE_ADD(keyword literal, interval expr)
 
     DATE_ADD(column, integer)
 
+    DATE_ADD(column, interval expr)
+
 *keyword* is the word date, time, or timestamp.  
 *literal* is a date, time, or timestamp literal.  For example, a date in yyyy-mm-dd format enclosed in single quotation marks.  
 *integer* is a number of days to add to the date/time.  
 *column* is date, time, or timestamp data in a data source column.  
 *interval* is the keyword interval.  
-*expr* is an interval expression.  
+*expr* is an interval expression, such as the name of a data source column containing interval data.  
 
 ### DATE_ADD Examples
 
+The following examples show how to use the syntax variations.
+
+**`DATE_ADD(keyword literal, integer)` Syntax Example**
+
 Add two days to today's date May 15, 2015.
 
     SELECT DATE_ADD(date '2015-05-15', 2) FROM sys.version;
@@ -99,7 +105,9 @@ Add two days to today's date May 15, 2015.
     +------------+
     1 row selected (0.07 seconds)
 
-Using the example data from the ["Casting Intervals"]({{site.baseurl}}/docs/data-type-conversion/#casting-intervals) section, add intervals from the `intervals.json` file to a literal timestamp using an interval expression. Create an interval expression that casts the interval data in the intervals.json file to a timestamp.
+**`DATE_ADD(keyword literal, interval expr)` Syntax Example**
+
+Using the example data from the ["Casting Intervals"]({{site.baseurl}}/docs/data-type-conversion/#casting-intervals) section, add intervals from the `intervals.json` file to a literal timestamp. Create an interval expression that casts the INTERVALDAY_col column, which contains P1D, P2D, and P3D, to a timestamp.
 
     SELECT DATE_ADD(timestamp '2015-04-15 22:55:55', CAST(INTERVALDAY_col as interval second)) FROM dfs.`/Users/drilluser/apache-drill-1.0.0/intervals.json`;
     +------------------------+
@@ -111,39 +119,57 @@ Using the example data from the ["Casting Intervals"]({{site.baseurl}}/docs/data
     +------------------------+
     3 rows selected (0.105 seconds)
 
-The query returns the sum of the timestamp plus 1, 2, and 3 days becuase the INTERVALDAY_col contains P1D, P2D, and P3D, 
+The query output is the sum of the timestamp and 1, 2, and 3 days corresponding to P1D, P2D, and P3D.
 
-The Drill installation includes the `employee.json` file that has records of employee hire dates:
+**DATE_ADD(column, integer) Syntax Example**
 
-    SELECT * FROM cp.`employee.json` LIMIT 1;
-    +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
-    | employee_id  |   full_name   | first_name  | last_name  | position_id  | position_title  | store_id  | department_id  | birth_date  |       hire_date        |  salary  | supervisor_id  | education_level  | marital_status  | gender  |  management_role   |
-    +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
-    | 1            | Sheri Nowmer  | Sheri       | Nowmer     | 1            | President       | 0         | 1              | 1961-08-26  | 1994-12-01 00:00:00.0  | 80000.0  | 0              | Graduate Degree  | S               | F       | Senior Management  |
-    +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
-    1 row selected (0.137 seconds)
+Add two days to the value in the birth_date column.
 
-Look at the hire_dates for the employee 578 and 761 in `employee.json`.
+    SELECT DATE_ADD(CAST(birth_date AS date), 2) FROM cp.`employee.json` LIMIT 1;
+    +-------------+
+    |   EXPR$0    |
+    +-------------+
+    | 1961-08-28  |
+    +-------------+
+    1 row selected (0.209 seconds)
 
-    SELECT hire_date FROM cp.`employee.json` where employee_id IN( '578','761');
-    +------------------------+
-    |       hire_date        |
-    +------------------------+
-    | 1996-01-01 00:00:00.0  |
-    | 1998-01-01 00:00:00.0  |
-    +------------------------+
-    2 rows selected (0.135 seconds)
+**`DATE_ADD(column, interval expr)` Syntax Example**
 
-Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type. 
+Add a 10 hour interval to the hire dates of employees listed in the `employee.json` file, which Drill includes in the installation.
 
-    SELECT DATE_ADD(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` where employee_id IN( '578','761');
-    +------------------------+
-    |         EXPR$0         |
-    +------------------------+
-    | 1996-01-01 10:00:00.0  |
-    | 1998-01-01 10:00:00.0  |
-    +------------------------+
-    2 rows selected (0.172 seconds)
+1. Take a look at the employee data:
+
+        SELECT * FROM cp.`employee.json` LIMIT 1;
+        +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
+        | employee_id  |   full_name   | first_name  | last_name  | position_id  | position_title  | store_id  | department_id  | birth_date  |       hire_date        |  salary  | supervisor_id  | education_level  | marital_status  | gender  |  management_role   |
+        +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
+        | 1            | Sheri Nowmer  | Sheri       | Nowmer     | 1            | President       | 0         | 1              | 1961-08-26  | 1994-12-01 00:00:00.0  | 80000.0  | 0              | Graduate Degree  | S               | F       | Senior Management  |
+        +--------------+---------------+-------------+------------+--------------+-----------------+-----------+----------------+-------------+------------------------+----------+----------------+------------------+-----------------+---------+--------------------+
+        1 row selected (0.137 seconds)
+
+2. Look at the hire_dates for the employee 578 and 761 in `employee.json`.
+
+        SELECT hire_date FROM cp.`employee.json` where employee_id IN( '578','761');
+        +------------------------+
+        |       hire_date        |
+        +------------------------+
+        | 1996-01-01 00:00:00.0  |
+        | 1998-01-01 00:00:00.0  |
+        +------------------------+
+        2 rows selected (0.135 seconds)
+
+3. Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type. 
+
+        SELECT DATE_ADD(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` where employee_id IN( '578','761');
+        +------------------------+
+        |         EXPR$0         |
+        +------------------------+
+        | 1996-01-01 10:00:00.0  |
+        | 1998-01-01 10:00:00.0  |
+        +------------------------+
+        2 rows selected (0.172 seconds)
+
+**`DATE_ADD(keyword literal, integer)` Syntax Example**
 
 Add 1 year and 1 month to the timestamp 2015-04-15 22:55:55.
 
@@ -217,27 +243,21 @@ Returns the difference between a date/time and a number of days/hours, or betwee
 
     DATE_ADD(column, integer)  
 
+    DATE_SUB(column, interval expr)
+
 *keyword* is the word date, time, or timestamp.  
 *literal* is a date, time, or timestamp literal. For example, a date in yyyy-mm-dd format enclosed in single quotation marks.   
-*integer* is a number of days to subtract from the date, time, or timestamp.  
+*integer* is a number of days to subtract from the date, time, or timestamp.
 *column* is date, time, or timestamp data in the data source.  
 *interval* is the keyword interval.  
-*expr* is an interval expression.
+*expr* is an interval expression, such as the name of a data source column containing interval data.
 
 ### DATE_SUB Examples
+The following examples show how to apply the syntax variations.
 
-Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type. 
+**`DATE_SUB(keyword literal, integer)` Syntax Example**
 
-    SELECT DATE_SUB(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` WHERE employee_id IN( '578','761');
-    +------------------------+
-    |         EXPR$0         |
-    +------------------------+
-    | 1995-12-31 14:00:00.0  |
-    | 1997-12-31 14:00:00.0  |
-    +------------------------+
-    2 rows selected (0.161 seconds)
-
-Subtract two days to today's date May 15, 2015.
+Subtract two days from today's date May 15, 2015.
 
     SELECT DATE_SUB(date '2015-05-15', 2) FROM sys.version;
     +------------+
@@ -257,6 +277,8 @@ Subtact two months from April 15, 2015.
     +------------+
     1 row selected (0.088 seconds)
 
+**`DATE_SUB(keyword literal, interval expr)` Syntax Example**
+
 Subtract 10 hours from the timestamp 2015-04-15 22:55:55.
 
     SELECT DATE_SUB(timestamp '2015-04-15 22:55:55', interval '10' hour) FROM sys.version;
@@ -297,6 +319,29 @@ Subtract 1 day, 2 and 1/2 hours, and 45.100 seconds from the time 22:55:55.
     +---------------+
     1 row selected (0.095 seconds)
 
+**`DATE_SUB(column, integer)` Syntax Example**
+
+    SELECT DATE_SUB(CAST(birth_date AS date), 2) FROM cp.`employee.json` LIMIT 1;
+    +-------------+
+    |   EXPR$0    |
+    +-------------+
+    | 1961-08-24  |
+    +-------------+
+    1 row selected (0.158 seconds)
+
+**`DATE_SUB(column, interval expr)` Syntax Example**
+
+The `employee.json` file, which Drill includes in the installation, lists the hire dates of employees. Cast the hire_dates of the employees 578 and 761 to a timestamp, and add 10 hours to the hire_date timestamp. Because Drill reads data from JSON as VARCHAR, you need to cast the hire_date to the TIMESTAMP type. 
+
+    SELECT DATE_SUB(CAST(hire_date AS TIMESTAMP), interval '10' hour) FROM cp.`employee.json` WHERE employee_id IN( '578','761');
+    +------------------------+
+    |         EXPR$0         |
+    +------------------------+
+    | 1995-12-31 14:00:00.0  |
+    | 1997-12-31 14:00:00.0  |
+    +------------------------+
+    2 rows selected (0.161 seconds)
+
 ## Other Date and Time Functions
 
 The following examples show how to use these functions:
@@ -383,7 +428,7 @@ If you did not set up Drill for UTC time, TIMEOFDAY returns the local date and t
 
 Returns a component of a timestamp, time, date, or interval.
 
-#### EXTRACT Syntax
+### EXTRACT Syntax
 
     EXTRACT (extract_expression) 
 


Mime
View raw message