#   Copyright 2011-2015 Quickstep Technologies LLC.
#   Copyright 2015-2016 Pivotal Software, Inc.
#   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
#     University of Wisconsin—Madison.
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.

SELECT *
FROM test
WHERE int_col < 5
ORDER BY int_col DESC,
         long_col ASC
LIMIT 5;
--
+-----------+--------------------+---------------+------------------------+--------------------+
|int_col    |long_col            |float_col      |double_col              |char_col            |
+-----------+--------------------+---------------+------------------------+--------------------+
|          4|                  16|              2|                       8|          4 2.000000|
|          2|                   4|     1.41421354|      2.8284271247461903|          2 1.414214|
|         -1|                   1|              1|                      -1|         -1 1.000000|
|         -3|                   9|     1.73205078|      -5.196152422706632|         -3 1.732051|
|         -5|                  25|     2.23606801|     -11.180339887498949|         -5 2.236068|
+-----------+--------------------+---------------+------------------------+--------------------+
==

# Simple SELECT.
SELECT *
FROM test
WHERE int_col < 10
  AND long_col < 100
  AND double_col < 0;
--
+-----------+--------------------+---------------+------------------------+--------------------+
|int_col    |long_col            |float_col      |double_col              |char_col            |
+-----------+--------------------+---------------+------------------------+--------------------+
|         -1|                   1|              1|                      -1|         -1 1.000000|
|         -3|                   9|     1.73205078|      -5.196152422706632|         -3 1.732051|
|         -5|                  25|     2.23606801|     -11.180339887498949|         -5 2.236068|
|         -7|                  49|     2.64575124|     -18.520259177452136|         -7 2.645751|
|         -9|                  81|              3|                     -27|         -9 3.000000|
+-----------+--------------------+---------------+------------------------+--------------------+
==

# Modulo operator
SELECT int_col % 2,
       long_col % (int_col * int_col),
       float_col % 1,
       double_col % 1.0
FROM test;
--
+-----------+----------------------------+---------------+------------------------+
|(int_col%2)|(long_col%(int_col*int_col))|(float_col%1)  |(double_col%1.0)        |
+-----------+----------------------------+---------------+------------------------+
|       NULL|                        NULL|              0|                    NULL|
|         -1|                           0|              0|                       0|
|          0|                           0|    0.414213538|     0.82842712474619029|
|         -1|                           0|    0.732050776|    -0.19615242270663202|
|          0|                           0|              0|                       0|
|         -1|                           0|     0.23606801|    -0.18033988749894903|
|          0|                           0|    0.449489832|     0.69693845669906729|
|         -1|                           0|    0.645751238|     -0.5202591774521359|
|          0|                           0|    0.828427076|     0.62741699796952233|
|         -1|                           0|              0|                       0|
|       NULL|                        NULL|    0.162277699|                    NULL|
|         -1|                           0|     0.31662488|    -0.48287269390939969|
|          0|                           0|    0.464101553|     0.56921938165305619|
|         -1|                           0|    0.605551243|    -0.87216658103185551|
|          0|                           0|    0.741657495|      0.3832034148351795|
|         -1|                           0|    0.872983456|   -0.094750193111252656|
|          0|                           0|              0|                       0|
|         -1|                           0|    0.123105526|   -0.092795635500223739|
|          0|                           0|    0.242640495|     0.36753236814712409|
|         -1|                           0|    0.358899117|    -0.81907992727280998|
|       NULL|                        NULL|    0.472136021|                    NULL|
|         -1|                           0|    0.582575798|    -0.23408959407264263|
|          0|                           0|    0.690415859|     0.18914671611545941|
|         -1|                           0|     0.79583168|    -0.30412503619254494|
|          0|                           0|    0.898979664|      0.5755076535925383|
+-----------+----------------------------+---------------+------------------------+
==

SELECT i,
       i/4 AS bit2,
       (i%4)/2 AS bit1,
       i%2 AS bit0
FROM generate_series(0, 7) AS gs(i);

--
+-----------+-----------+-----------+-----------+
|i          |bit2       |bit1       |bit0       |
+-----------+-----------+-----------+-----------+
|          0|          0|          0|          0|
|          1|          0|          0|          1|
|          2|          0|          1|          0|
|          3|          0|          1|          1|
|          4|          1|          0|          0|
|          5|          1|          0|          1|
|          6|          1|          1|          0|
|          7|          1|          1|          1|
+-----------+-----------+-----------+-----------+
==

SELECT *
FROM test
WHERE int_col < 10
  AND long_col < 100;
--
+-----------+--------------------+---------------+------------------------+--------------------+
|int_col    |long_col            |float_col      |double_col              |char_col            |
+-----------+--------------------+---------------+------------------------+--------------------+
|         -1|                   1|              1|                      -1|         -1 1.000000|
|          2|                   4|     1.41421354|      2.8284271247461903|          2 1.414214|
|         -3|                   9|     1.73205078|      -5.196152422706632|         -3 1.732051|
|          4|                  16|              2|                       8|          4 2.000000|
|         -5|                  25|     2.23606801|     -11.180339887498949|         -5 2.236068|
|          6|                  36|     2.44948983|      14.696938456699067|          6 2.449490|
|         -7|                  49|     2.64575124|     -18.520259177452136|         -7 2.645751|
|          8|                  64|     2.82842708|      22.627416997969522|          8 2.828427|
|         -9|                  81|              3|                     -27|         -9 3.000000|
+-----------+--------------------+---------------+------------------------+--------------------+
==

SELECT int_col,
       int_col+100 AS alias_col,
       char_col AS char_col
FROM test
WHERE int_col < 10
  AND long_col < 100;
--
+-----------+-----------+--------------------+
|int_col    |alias_col  |char_col            |
+-----------+-----------+--------------------+
|         -1|         99|         -1 1.000000|
|          2|        102|          2 1.414214|
|         -3|         97|         -3 1.732051|
|          4|        104|          4 2.000000|
|         -5|         95|         -5 2.236068|
|          6|        106|          6 2.449490|
|         -7|         93|         -7 2.645751|
|          8|        108|          8 2.828427|
|         -9|         91|         -9 3.000000|
+-----------+-----------+--------------------+
==

# Nested queries.
SELECT *
FROM
  (SELECT *
   FROM
     (SELECT *
      FROM
        (SELECT *
         FROM test
         WHERE long_col < 100) a
      WHERE double_col<0) a
   WHERE long_col < 200) a
WHERE int_col < 10;
--
+-----------+--------------------+---------------+------------------------+--------------------+
|int_col    |long_col            |float_col      |double_col              |char_col            |
+-----------+--------------------+---------------+------------------------+--------------------+
|         -1|                   1|              1|                      -1|         -1 1.000000|
|         -3|                   9|     1.73205078|      -5.196152422706632|         -3 1.732051|
|         -5|                  25|     2.23606801|     -11.180339887498949|         -5 2.236068|
|         -7|                  49|     2.64575124|     -18.520259177452136|         -7 2.645751|
|         -9|                  81|              3|                     -27|         -9 3.000000|
+-----------+--------------------+---------------+------------------------+--------------------+
==

WITH subquery1 AS
  (SELECT *
   FROM test
   WHERE int_col < 10),
     subquery2 AS
  (SELECT *
   FROM subquery1
   WHERE long_col < 100)
SELECT *
FROM subquery2
WHERE double_col < 0;
--
[same as above]
==

SELECT NULL,
       1/NULL,
       int_col+NULL,
       int_col+long_col,
       float_col+NULL,
       -NULL
FROM test
WHERE long_col < 100
ORDER BY 4 NULLS LAST;
--
+----+--------+--------------+--------------------+----------------+-----+
|NULL|(1/NULL)|(int_col+NULL)|(int_col+long_col)  |(float_col+NULL)|-NULL|
+----+--------+--------------+--------------------+----------------+-----+
|NULL|    NULL|          NULL|                   0|            NULL| NULL|
|NULL|    NULL|          NULL|                   6|            NULL| NULL|
|NULL|    NULL|          NULL|                   6|            NULL| NULL|
|NULL|    NULL|          NULL|                  20|            NULL| NULL|
|NULL|    NULL|          NULL|                  20|            NULL| NULL|
|NULL|    NULL|          NULL|                  42|            NULL| NULL|
|NULL|    NULL|          NULL|                  42|            NULL| NULL|
|NULL|    NULL|          NULL|                  72|            NULL| NULL|
|NULL|    NULL|          NULL|                  72|            NULL| NULL|
|NULL|    NULL|          NULL|                NULL|            NULL| NULL|
+----+--------+--------------+--------------------+----------------+-----+
==

SELECT int_col "
this

is multi-line

column name
"
FROM test
WHERE int_col < 10
  AND long_col < 100
  AND double_col < 0;
--
+----------------------------------+
|
this

is multi-line

column name
|
+----------------------------------+
|                                -1|
|                                -3|
|                                -5|
|                                -7|
|                                -9|
+----------------------------------+
==

# Alias support.
SELECT int_col+100 AS alias_col,
       char_col AS char_col
FROM test
WHERE int_col < 10
  AND long_col < 100;
--
+-----------+--------------------+
|alias_col  |char_col            |
+-----------+--------------------+
|         99|         -1 1.000000|
|        102|          2 1.414214|
|         97|         -3 1.732051|
|        104|          4 2.000000|
|         95|         -5 2.236068|
|        106|          6 2.449490|
|         93|         -7 2.645751|
|        108|          8 2.828427|
|         91|         -9 3.000000|
+-----------+--------------------+
==

# The execution engine currently does not support self-join.
SELECT a.int_col,
       a.int_col*b.int_col,
       b.long_col
FROM test AS a,
     test AS b
WHERE a.int_col*b.int_col = b.long_col;
--
ERROR: NestedLoopsJoin does not support self-join yet
==

# The nested loops join is not a self-join, because the predicate "a.int_col<10" is pushed under the join, which results
# in a new relation.
SELECT a.int_col,
       a.int_col*b.int_col,
       b.long_col
FROM test AS a,
     test AS b
WHERE a.long_col < 100
  AND a.int_col*b.int_col = b.long_col;
--
+-----------+---------------------+--------------------+
|int_col    |(a.int_col*b.int_col)|long_col            |
+-----------+---------------------+--------------------+
|         -1|                    1|                   1|
|          2|                    4|                   4|
|         -3|                    9|                   9|
|          4|                   16|                  16|
|         -5|                   25|                  25|
|          6|                   36|                  36|
|         -7|                   49|                  49|
|          8|                   64|                  64|
|         -9|                   81|                  81|
+-----------+---------------------+--------------------+
==

# Hash join does not support the self-join.
SELECT a.int_col,
       b.int_col
FROM test AS a,
     test AS b
WHERE a.int_col = b.int_col;
--
ERROR: Self-join is not supported
==

# This is not a self-join, because there is a Select under the HashJoin for "a.long_col < 50".
SELECT a.int_col,
       b.int_col
FROM test AS a,
     test AS b
WHERE a.long_col < 50
  AND a.int_col = b.int_col;
--
+-----------+-----------+
|int_col    |int_col    |
+-----------+-----------+
|         -1|         -1|
|          2|          2|
|         -3|         -3|
|          4|          4|
|         -5|         -5|
|          6|          6|
|         -7|         -7|
+-----------+-----------+
==

# Hash join does not support a join predicate with two different types of attributes.
SELECT a.int_col,
       b.long_col
FROM test AS a,
     test AS b
WHERE a.int_col = b.long_col;
--
ERROR: Equality join predicate between two attributes of different types is not allowed in HashJoin
==

# Join with char-type attributes.
SELECT a.char_col,
       b.char_col,
       a.int_col
FROM test AS a,
     test AS b
WHERE a.char_col = b.char_col
  AND a.long_col < 100;
--
+--------------------+--------------------+-----------+
|char_col            |char_col            |int_col    |
+--------------------+--------------------+-----------+
|          0 0.000000|          0 0.000000|       NULL|
|         -1 1.000000|         -1 1.000000|         -1|
|          2 1.414214|          2 1.414214|          2|
|         -3 1.732051|         -3 1.732051|         -3|
|          4 2.000000|          4 2.000000|          4|
|         -5 2.236068|         -5 2.236068|         -5|
|          6 2.449490|          6 2.449490|          6|
|         -7 2.645751|         -7 2.645751|         -7|
|          8 2.828427|          8 2.828427|          8|
|         -9 3.000000|         -9 3.000000|         -9|
+--------------------+--------------------+-----------+
==

# Cross-product
SELECT a.int_col,
       b.int_col
FROM test AS a,
     test AS b
WHERE a.int_col BETWEEN 0 AND 3
  AND b.int_col BETWEEN 0 AND 3;
--
+-----------+-----------+
|int_col    |int_col    |
+-----------+-----------+
|          2|          2|
+-----------+-----------+
==

# Mixed multiple nested loops joins and hash joins
SELECT a.int_col,
       b.int_col,
       c.float_col,
       d.float_col+a.int_col,
       d.float_col,
       b.double_col,
       c.int_col
FROM test AS a,
     test AS b,
     test AS c,
  (SELECT a.float_col
   FROM test AS a,
        test AS b
   WHERE a.int_col < 5) AS d
WHERE a.int_col = b.int_col
  AND a.long_col = b.long_col
  AND c.float_col > d.float_col+a.int_col
  AND d.float_col <= 1
  AND a.int_col BETWEEN 0 AND 5
  AND c.float_col > 4.7;
--
+-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+
|int_col    |int_col    |float_col      |(d.float_col+a.int_col)|float_col      |double_col              |int_col    |
+-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.79583168|                      3|              1|      2.8284271247461903|        -23|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
|          2|          2|     4.89897966|                      3|              1|      2.8284271247461903|         24|
+-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+
==

# FIXME(qzeng): Inner nested-loops join has no output column, causing query
# result to be INCORRECT.
SELECT COUNT(*)
FROM
  (SELECT a.float_col
   FROM test a,
        test b
   WHERE a.float_col > 1) c;
--
+--------------------+
|COUNT(*)            |
+--------------------+
|                   0|
+--------------------+
==

SELECT DATE '2000-02-29' + INTERVAL '1 year',
       DATE '1998-03-31' + INTERVAL '1 month',
       DATE '2000-03-31' - INTERVAL '1 month',
       DATE '2000-01-31' + INTERVAL '1 month',
       DATE '2000-02-29' + INTERVAL '1 day',
       DATE '2000-02-29' - INTERVAL '1 year'
FROM test
WHERE int_col = 2;
--
+----------------------------------------------------+-----------------------------------------------------+-----------------------------------------------------+-----------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
|(Datetime('2000-02-29')+YearMonthInterval('1 year'))|(Datetime('1998-03-31')+YearMonthInterval('1 month'))|(Datetime('2000-03-31')-YearMonthInterval('1 month'))|(Datetime('2000-01-31')+YearMonthInterval('1 month'))|(Datetime('2000-02-29')+DatetimeInterval('1 day'))|(Datetime('2000-02-29')-YearMonthInterval('1 year'))|
+----------------------------------------------------+-----------------------------------------------------+-----------------------------------------------------+-----------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
|                                 2001-02-28T00:00:00|                                  1998-04-30T00:00:00|                                  2000-02-29T00:00:00|                                  2000-02-29T00:00:00|                               2000-03-01T00:00:00|                                 1999-02-28T00:00:00|
+----------------------------------------------------+-----------------------------------------------------+-----------------------------------------------------+-----------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
==

SELECT DATE '2000-02-29' - DATE '2000-02-28',
       DATE '2000-02-29' - DATE '1999-02-28'
FROM test
WHERE int_col = 2;
--
+-----------------------------------------------+-----------------------------------------------+
|(Datetime('2000-02-29')-Datetime('2000-02-28'))|(Datetime('2000-02-29')-Datetime('1999-02-28'))|
+-----------------------------------------------+-----------------------------------------------+
|                                 1 day 00:00:00|                              366 days 00:00:00|
+-----------------------------------------------+-----------------------------------------------+
==

SELECT INTERVAL '1 year' + INTERVAL '1 month',
       INTERVAL '1 year' + INTERVAL '13 month',
       INTERVAL '21 day' + INTERVAL '14 day',
       INTERVAL '21 day' * 4
FROM test
WHERE int_col = 2;
--
+----------------------------------------------------------+-----------------------------------------------------------+-------------------------------------------------------+----------------------------------------+
|(YearMonthInterval('1 year')+YearMonthInterval('1 month'))|(YearMonthInterval('1 year')+YearMonthInterval('13 month'))|(DatetimeInterval('21 day')+DatetimeInterval('14 day'))|(DatetimeInterval('21 day')*4)          |
+----------------------------------------------------------+-----------------------------------------------------------+-------------------------------------------------------+----------------------------------------+
|                                              1 year 1 mon|                                              2 years 1 mon|                                       35 days 00:00:00|                        84 days 00:00:00|
+----------------------------------------------------------+-----------------------------------------------------------+-------------------------------------------------------+----------------------------------------+
==

SELECT DATE '+1980-02-29' + INTERVAL '1 year'
FROM test
WHERE int_col = 2;
--
+-----------------------------------------------------+
|(Datetime('+1980-02-29')+YearMonthInterval('1 year'))|
+-----------------------------------------------------+
|                                  1981-02-28T00:00:00|
+-----------------------------------------------------+
==

SELECT int_col
FROM test
GROUP BY int_col;
--
+-----------+
|int_col    |
+-----------+
|          2|
|          4|
|          6|
|          8|
|         12|
|         14|
|         16|
|         18|
|         22|
|         24|
|        -23|
|        -21|
|        -19|
|        -17|
|        -15|
|        -13|
|        -11|
|         -9|
|         -7|
|         -5|
|         -3|
|         -1|
+-----------+
==

SELECT COUNT(*),
       COUNT(1),
       COUNT(0),
       SUM(int_col) / COUNT(*),
       AVG(int_col+0) * COUNT(1),
       MAX(double_col+100),
       MIN(float_col+1)
FROM test;
--
+--------------------+--------------------+--------------------+-----------------------+---------------------------+------------------------+------------------+
|COUNT(*)            |COUNT(1)            |COUNT(0)            |(SUM(int_col)/COUNT(*))|(AVG((int_col+0))*COUNT(1))|MAX((double_col+100))   |MIN((float_col+1))|
+--------------------+--------------------+--------------------+-----------------------+---------------------------+------------------------+------------------+
|                  25|                  25|                  25|                      0|        -20.454545454545457|      217.57550765359252|                 1|
+--------------------+--------------------+--------------------+-----------------------+---------------------------+------------------------+------------------+
==

SELECT COUNT(NULL)
FROM test;
--
+--------------------+
|COUNT(NULL)         |
+--------------------+
|                   0|
+--------------------+
==

SELECT long_col/100 AS group_col,
       COUNT(*),
       SUM(int_col)
FROM test
GROUP BY group_col
HAVING MIN(float_col) > 0;
--
+--------------------+--------------------+--------------------+
|group_col           |COUNT(*)            |SUM(int_col)        |
+--------------------+--------------------+--------------------+
|                   1|                   5|                   2|
|                   2|                   3|                 -16|
|                   3|                   2|                  -1|
|                   4|                   3|                   1|
|                   5|                   2|                   1|
+--------------------+--------------------+--------------------+
==

SELECT COUNT(*),
       long_col/100 AS group_col1,
       long_col/50 AS group_col2
FROM test
GROUP BY group_col1,
         group_col2
HAVING COUNT(*) > 0
AND group_col2 > 5
ORDER BY group_col1,
         group_col2;
--
+--------------------+--------------------+--------------------+
|COUNT(*)            |group_col1          |group_col2          |
+--------------------+--------------------+--------------------+
|                   1|                   3|                   6|
|                   1|                   3|                   7|
|                   2|                   4|                   8|
|                   1|                   4|                   9|
|                   1|                   5|                  10|
|                   1|                   5|                  11|
+--------------------+--------------------+--------------------+
==


CREATE TABLE dates (value DATETIME);
INSERT INTO dates VALUES (DATE '2016-01-02 10:20:30');
INSERT INTO dates VALUES (DATE '2016-02-03 11:21:31');
INSERT INTO dates VALUES (DATE '2016-03-04 12:22:32');

SELECT EXTRACT(YEAR FROM value) * 10000 +
       EXTRACT(MONTH FROM value) * 100 +
       EXTRACT(DAY FROM value) AS date_digits
FROM dates;
--
+--------------------+
|date_digits         |
+--------------------+
|            20160102|
|            20160203|
|            20160304|
+--------------------+
==

SELECT SUM(EXTRACT(SECOND FROM value))
FROM dates;
--
+-------------------------------+
|SUM(EXTRACT(SECOND FROM value))|
+-------------------------------+
|                             93|
+-------------------------------+
==

SELECT EXTRACT(hour FROM value + INTERVAL '1 hour')
FROM dates;
--
+-----------------------------------------------------+
|EXTRACT(hour FROM (value+DatetimeInterval('1 hour')))|
+-----------------------------------------------------+
|                                                   11|
|                                                   12|
|                                                   13|
+-----------------------------------------------------+
==

SELECT i,
       CASE i%2
         WHEN 1 THEN 'odd'
         ELSE 'even'
       END
FROM generate_series(1, 5) AS gs(i);
--
+-----------+--------------------------------------------+
|i          |CASE (i%2) WHEN 1 THEN 'odd' ELSE 'even' END|
+-----------+--------------------------------------------+
|          1|                                         odd|
|          2|                                        even|
|          3|                                         odd|
|          4|                                        even|
|          5|                                         odd|
+-----------+--------------------------------------------+
==

SELECT SUM(CASE WHEN i < 4 THEN i
                ELSE i * i
           END) AS result
FROM generate_series(1, 5) AS gs(i);
--
+--------------------+
|result              |
+--------------------+
|                  47|
+--------------------+
==

SELECT *
FROM generate_series(1, 3) AS gs1(i),
     generate_series(1, 3) AS gs2(j)
WHERE CASE WHEN i < j THEN i
           ELSE j
       END > 1;
--
+-----------+-----------+
|i          |j          |
+-----------+-----------+
|          2|          2|
|          2|          3|
|          3|          2|
|          3|          3|
+-----------+-----------+
==

SELECT i AS odd
FROM generate_series(0, 10, 1) AS gs1(i)
WHERE
  NOT EXISTS (
    SELECT *
    FROM generate_series(0, 10, 2) AS gs2(even)
    WHERE i = even
  );
--
+-----------+
|odd        |
+-----------+
|          1|
|          3|
|          5|
|          7|
|          9|
+-----------+
==

SELECT i
FROM generate_series(0, 100, 3) AS gs1(i)
WHERE
  EXISTS (
    SELECT *
    FROM generate_series(0, 100, 5) AS gs2(i)
    WHERE gs1.i = gs2.i
  )
  AND NOT EXISTS (
    SELECT *
    FROM generate_series(0, 100, 10) AS gs3(i)
    WHERE gs1.i = gs3.i
  )
  AND (i < 40 OR i > 60);
--
+-----------+
|i          |
+-----------+
|         15|
|         75|
+-----------+
==

# IN predicate
SELECT *
FROM generate_series(1, 5) AS gs(i)
WHERE i IN (2, 4);
--
+-----------+
|i          |
+-----------+
|          2|
|          4|
+-----------+
==

SELECT *
FROM generate_series(1, 5) AS gs(i)
WHERE i NOT IN (2, 4);
--
+-----------+
|i          |
+-----------+
|          1|
|          3|
|          5|
+-----------+
==

SELECT *
FROM generate_series(1, 5) AS gs(i)
WHERE i NOT IN (i*i-6, CASE WHEN i < 3 THEN 1 ELSE 4 END);
--
+-----------+
|i          |
+-----------+
|          2|
|          5|
+-----------+
==

SELECT *
FROM generate_series(1, 10) AS gs(i)
WHERE i NOT IN (
  SELECT *
  FROM generate_series(2, 10, 2)
);
--
+-----------+
|i          |
+-----------+
|          1|
|          3|
|          5|
|          7|
|          9|
+-----------+
==

# TODO(team): Support uncorrelated queries.
# SELECT COUNT(*)
# FROM test
# WHERE double_col < 0
#   AND long_col > (SELECT AVG(long_col) FROM test)

# TODO(team): Fix Issue #9 to enable COUNT(*).
SELECT COUNT(long_col)
FROM test,
     (SELECT AVG(long_col) a FROM test) subquery
WHERE double_col < 0
  AND long_col > subquery.a;
--
+--------------------+
|COUNT(long_col)     |
+--------------------+
|                   5|
+--------------------+