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

# Use generate_series
SELECT * FROM generate_series(1, 10);
--
+---------------+
|generate_series|
+---------------+
|              1|
|              2|
|              3|
|              4|
|              5|
|              6|
|              7|
|              8|
|              9|
|             10|
+---------------+
==

# Use generate_series: non-default step width
SELECT * FROM generate_series(1, 10, 2);
--
+---------------+
|generate_series|
+---------------+
|              1|
|              3|
|              5|
|              7|
|              9|
+---------------+
==

# Use generate_series: negative step width
SELECT * FROM generate_series(10, 1, -2);
--
+---------------+
|generate_series|
+---------------+
|             10|
|              8|
|              6|
|              4|
|              2|
+---------------+
==

# Use generate_series: float number
SELECT * FROM generate_series(1.5, 5.5);
--
+------------------------+
|generate_series         |
+------------------------+
|                     1.5|
|                     2.5|
|                     3.5|
|                     4.5|
|                     5.5|
+------------------------+
==

# Use generate_series: float number step width
SELECT * FROM generate_series(1, 3, 0.5);
--
+------------------------+
|generate_series         |
+------------------------+
|                       1|
|                     1.5|
|                       2|
|                     2.5|
|                       3|
+------------------------+
==

# Use generate_series: extra columns
SELECT i, i+1, i*2 FROM generate_series(1, 3, 0.5) AS gs(i);
--
+------------------------+------------------------+------------------------+
|i                       |(i+1)                   |(i*2)                   |
+------------------------+------------------------+------------------------+
|                       1|                       2|                       2|
|                     1.5|                     2.5|                       3|
|                       2|                       3|                       4|
|                     2.5|                     3.5|                       5|
|                       3|                       4|                       6|
+------------------------+------------------------+------------------------+
==


# Use generate_series: cross product
SELECT i, j, i+j FROM generate_series(1, 5) AS gs1(i), generate_series(0, 1, 0.5) AS gs2(j);
--
+-----------+------------------------+------------------------+
|i          |j                       |(i+j)                   |
+-----------+------------------------+------------------------+
|          1|                       0|                       1|
|          1|                     0.5|                     1.5|
|          1|                       1|                       2|
|          2|                       0|                       2|
|          2|                     0.5|                     2.5|
|          2|                       1|                       3|
|          3|                       0|                       3|
|          3|                     0.5|                     3.5|
|          3|                       1|                       4|
|          4|                       0|                       4|
|          4|                     0.5|                     4.5|
|          4|                       1|                       5|
|          5|                       0|                       5|
|          5|                     0.5|                     5.5|
|          5|                       1|                       6|
+-----------+------------------------+------------------------+
==

# Use generate_series: join alias
SELECT *
FROM (SELECT i FROM generate_series(1, 10) as GS(i)) AS R(r1),
     (SELECT i FROM generate_series(1, 10) as GS(i)) AS S(s1)
WHERE R.r1 = S.s1;
--
+-----------+-----------+
|r1         |s1         |
+-----------+-----------+
|          1|          1|
|          2|          2|
|          3|          3|
|          4|          4|
|          5|          5|
|          6|          6|
|          7|          7|
|          8|          8|
|          9|          9|
|         10|         10|
+-----------+-----------+
==

# Use generate_series: filter
SELECT i, j, k
FROM generate_series(1, 9) AS gs1(i),
     generate_series(1, 9) AS gs2(j),
     generate_series(1, 81) AS gs3(k)
WHERE i <= j AND i * j = k;
--
+-----------+-----------+-----------+
|i          |j          |k          |
+-----------+-----------+-----------+
|          1|          1|          1|
|          1|          2|          2|
|          1|          3|          3|
|          1|          4|          4|
|          1|          5|          5|
|          1|          6|          6|
|          1|          7|          7|
|          1|          8|          8|
|          1|          9|          9|
|          2|          2|          4|
|          2|          3|          6|
|          2|          4|          8|
|          2|          5|         10|
|          2|          6|         12|
|          2|          7|         14|
|          2|          8|         16|
|          2|          9|         18|
|          3|          3|          9|
|          3|          4|         12|
|          3|          5|         15|
|          3|          6|         18|
|          3|          7|         21|
|          3|          8|         24|
|          3|          9|         27|
|          4|          4|         16|
|          4|          5|         20|
|          4|          6|         24|
|          4|          7|         28|
|          4|          8|         32|
|          4|          9|         36|
|          5|          5|         25|
|          5|          6|         30|
|          5|          7|         35|
|          5|          8|         40|
|          5|          9|         45|
|          6|          6|         36|
|          6|          7|         42|
|          6|          8|         48|
|          6|          9|         54|
|          7|          7|         49|
|          7|          8|         56|
|          7|          9|         63|
|          8|          8|         64|
|          8|          9|         72|
|          9|          9|         81|
+-----------+-----------+-----------+
==

# Use generate_series: aggregation
SELECT count(*) FROM generate_series(1, 1000);
--
+--------------------+
|count(*)            |
+--------------------+
|                1000|
+--------------------+
==

SELECT sum(i) FROM generate_series(1, 1000) AS gs(i);
--
+--------------------+
|sum(i)              |
+--------------------+
|              500500|
+--------------------+
==

SELECT avg(i) FROM generate_series(1, 1000) AS gs(i);
--
+------------------------+
|avg(i)                  |
+------------------------+
|                   500.5|
+------------------------+
==

# Use generate_series: error messages
SELECT * FROM generate_series();
--
ERROR: Invalid number of arguments (1 : 15)
SELECT * FROM generate_series();
              ^
==

SELECT * FROM generate_series('a', 3);
--
ERROR: Invalid argument types (1 : 15)
SELECT * FROM generate_series('a', 3);
              ^
==

SELECT * FROM generate_series(1, 5, -1);
--
ERROR: Invalid step width (1 : 15)
SELECT * FROM generate_series(1, 5, -1);
              ^
==