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