# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you 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|
+-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+
==

SELECT COUNT(*)
FROM
  (SELECT a.float_col
   FROM test a,
        test b
   WHERE a.float_col > 1) c;
--
+--------------------+
|COUNT(*)            |
+--------------------+
|                 575|
+--------------------+
==

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

SELECT DATETIME '2000-02-29' - DATETIME '2000-02-28',
       DATETIME '2000-02-29' - DATETIME '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;
--
+-------------------------------------------------+
|(Date('+1980-02-29')+YearMonthInterval('1 year'))|
+-------------------------------------------------+
|                                       1981-02-28|
+-------------------------------------------------+
==

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

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 (DATETIME '2016-01-02 10:20:30');
INSERT INTO dates VALUES (DATETIME '2016-02-03 11:21:31');
INSERT INTO dates VALUES (DATETIME '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|
+-----------+
==

# SUBSTRING function
SELECT char_col,
       SUBSTRING(char_col FROM 1 FOR 2) AS negative_value
FROM test
WHERE SUBSTRING(char_col FROM 1 FOR 1) = '-'
--
+--------------------+--------------+
|char_col            |negative_value|
+--------------------+--------------+
|         -1 1.000000|            -1|
|         -3 1.732051|            -3|
|         -5 2.236068|            -5|
|         -7 2.645751|            -7|
|         -9 3.000000|            -9|
|        -11 3.316625|            -1|
|        -13 3.605551|            -1|
|        -15 3.872983|            -1|
|        -17 4.123106|            -1|
|        -19 4.358899|            -1|
|        -21 4.582576|            -2|
|        -23 4.795832|            -2|
+--------------------+--------------+
==

# 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|
+-----------+
==

# Scalar subquery expression
SELECT *
FROM test
WHERE double_col < 0
  AND long_col > (SELECT AVG(long_col) FROM test);
--
+-----------+--------------------+---------------+------------------------+--------------------+
|int_col    |long_col            |float_col      |double_col              |char_col            |
+-----------+--------------------+---------------+------------------------+--------------------+
|        -15|                 225|     3.87298346|     -58.094750193111253|        -15 3.872983|
|        -17|                 289|     4.12310553|     -70.092795635500224|        -17 4.123106|
|        -19|                 361|     4.35889912|      -82.81907992727281|        -19 4.358899|
|        -21|                 441|      4.5825758|     -96.234089594072643|        -21 4.582576|
|        -23|                 529|     4.79583168|     -110.30412503619254|        -23 4.795832|
+-----------+--------------------+---------------+------------------------+--------------------+
==

SELECT i + (
  SELECT SUM(j)
  FROM generate_series(1, 10) AS gs(j))
FROM generate_series(1, 5) AS gs(i);
--
+----------------------+
|(i+SubqueryExpression)|
+----------------------+
|                    56|
|                    57|
|                    58|
|                    59|
|                    60|
+----------------------+
==

SELECT i + (
  SELECT SUM(j)
  FROM generate_series(1, 10) AS gs1(j),
       generate_series(1, 10) AS gs2(k)
  WHERE i = k AND j <= k)
FROM generate_series(1, 5) AS gs(i);
--
+----------------------+
|(i+SubqueryExpression)|
+----------------------+
|                     2|
|                     5|
|                     9|
|                    14|
|                    20|
+----------------------+
==

# This query is to test that the output columns have the correct alias name as specified.
SELECT *
FROM (
  SELECT i, SUM(i) AS sum
  FROM generate_series(1, 2) AS gs(i)
  GROUP BY i
) t1 JOIN (
  SELECT j, AVG(j) AS avg
  FROM generate_series(1, 2) AS gs(j)
  GROUP BY j
) t2 ON i = j;
--
+-----------+--------------------+-----------+------------------------+
|i          |sum                 |j          |avg                     |
+-----------+--------------------+-----------+------------------------+
|          1|                   1|          1|                       1|
|          2|                   2|          2|                       2|
+-----------+--------------------+-----------+------------------------+
==

# Same shared subplan referenced multiple times.
WITH t(x, y) AS (
  SELECT i % 5, i
  FROM generate_series(1, 20) AS g(i)
)
SELECT *
FROM t
WHERE t.y = (
  SELECT MAX(y)
  FROM t t1
  WHERE t.x = t1.x
)
ORDER BY x;
--
+-----------+-----------+
|x          |y          |
+-----------+-----------+
|          0|         20|
|          1|         16|
|          2|         17|
|          3|         18|
|          4|         19|
+-----------+-----------+
==

SELECT COUNT(*)
FROM test,
     (SELECT AVG(long_col) a FROM test) subquery
WHERE double_col < 0
  AND long_col > subquery.a;
--
+--------------------+
|COUNT(*)            |
+--------------------+
|                   5|
+--------------------+
==

# Window Aggregation Test.
SELECT char_col, long_col, avg(long_col) OVER w FROM test
WINDOW w AS
(ORDER BY char_col DESC NULLS LAST
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
--
+--------------------+--------------------+------------------------+
|char_col            |long_col            |avg(long_col)           |
+--------------------+--------------------+------------------------+
|          8 2.828427|                  64|                      64|
|          6 2.449490|                  36|                      50|
|          4 2.000000|                  16|      38.666666666666664|
|         24 4.898979|                 576|                     173|
|         22 4.690416|                 484|      235.19999999999999|
|         20 4.472136|                 400|      262.66666666666669|
|          2 1.414214|                   4|      225.71428571428572|
|         18 4.242641|                 324|                     238|
|         16 4.000000|                 256|                     240|
|         14 3.741657|                 196|      235.59999999999999|
|         12 3.464102|                 144|      227.27272727272728|
|         10 3.162278|                 100|      216.66666666666666|
|          0 0.000000|                   0|                     200|
|         -9 3.000000|                  81|                   191.5|
|         -7 2.645751|                  49|                     182|
|         -5 2.236068|                  25|                172.1875|
|         -3 1.732051|                   9|      162.58823529411765|
|        -23 4.795832|                 529|      182.94444444444446|
|        -21 4.582576|                 441|      196.52631578947367|
|        -19 4.358899|                 361|                  204.75|
|        -17 4.123106|                 289|      208.76190476190476|
|        -15 3.872983|                 225|                   209.5|
|        -13 3.605551|                 169|       207.7391304347826|
|        -11 3.316625|                 121|                 204.125|
|         -1 1.000000|                   1|                     196|
+--------------------+--------------------+------------------------+
==

SELECT long_col, int_col, avg(int_col) OVER w FROM test
WINDOW w AS
(ORDER BY long_col DESC NULLS LAST
 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
--
+--------------------+-----------+------------------------+
|long_col            |int_col    |avg(int_col)            |
+--------------------+-----------+------------------------+
|                 576|         24|       7.666666666666667|
|                 529|        -23|                     0.5|
|                 484|         22|                     0.5|
|                 441|        -21|                  -10.25|
|                 400|       NULL|                       0|
|                 361|        -19|                   -9.75|
|                 324|         18|                    -0.5|
|                 289|        -17|     -3.3999999999999999|
|                 256|         16|      3.2000000000000002|
|                 225|        -15|                      -3|
|                 196|         14|      2.7999999999999998|
|                 169|        -13|     -2.6000000000000001|
|                 144|         12|                     0.5|
|                 121|        -11|                   -5.25|
|                 100|       NULL|                       0|
|                  81|         -9|                   -4.75|
|                  64|          8|                    -0.5|
|                  49|         -7|     -1.3999999999999999|
|                  36|          6|                     1.2|
|                  25|         -5|                      -1|
|                  16|          4|     0.80000000000000004|
|                   9|         -3|    -0.59999999999999998|
|                   4|          2|                     0.5|
|                   1|         -1|    -0.66666666666666663|
|                   0|       NULL|                     0.5|
+--------------------+-----------+------------------------+
==

SELECT float_col, double_col, avg(double_col) OVER
(ORDER BY float_col DESC NULLS LAST, int_col ASC NULLS FIRST
 RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)
FROM test;
--
+---------------+------------------------+------------------------+
|float_col      |double_col              |avg(double_col)         |
+---------------+------------------------+------------------------+
|     4.89897966|      117.57550765359254|     -5.2010907233390986|
|     4.79583168|     -110.30412503619254|     -3.3458568752518572|
|     4.69041586|      103.18914671611546|     -3.3458568752518572|
|      4.5825758|     -96.234089594072643|     -4.2942570191393745|
|     4.47213602|                    NULL|     -4.2942570191393745|
|     4.35889912|      -82.81907992727281|     -3.1771278735018194|
|      4.2426405|      76.367532368147124|     -3.1771278735018194|
|     4.12310553|     -70.092795635500224|     -3.6217507631683268|
|              4|                      64|     -3.0100796703699935|
|     3.87298346|     -58.094750193111253|     -3.0100796703699935|
|      3.7416575|       52.38320341483518|     -3.0100796703699935|
|     3.60555124|     -46.872166581031856|     -3.1193833079868254|
|     3.46410155|      41.569219381653056|     -3.1193833079868254|
|     3.31662488|       -36.4828726939094|     -2.8361542397614437|
|      3.1622777|                    NULL|     -2.8361542397614437|
|              3|                     -27|     -2.7526926834086507|
|     2.82842708|      22.627416997969522|     -8.4826069851706123|
|     2.64575124|     -18.520259177452136|     -9.0010404404476727|
|     2.44948983|      14.696938456699067|     -4.1547599319129516|
|     2.23606801|     -11.180339887498949|     -4.2708832009567148|
|              2|                       8|     0.11724429467951912|
|     1.73205078|      -5.196152422706632|     -4.7108157334609286|
|     1.41421354|      2.8284271247461903|     -5.1226841602152344|
|              1|                      -1|      -1.638218767582549|
|              0|                    NULL|      1.1580686755098886|
+---------------+------------------------+------------------------+
==

SELECT sum(avg(int_col) OVER w) FROM test
WINDOW w AS
(PARTITION BY char_col
 ORDER BY long_col
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
--
+------------------------+
|sum(avg(int_col))       |
+------------------------+
|                     -18|
+------------------------+