Materialize/test/sqllogictest/table_func.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_repeat_row = true
----
COMPLETE 0

statement ok
CREATE TABLE y (a JSONB)

# Ensure this does not panic.
query TTTT
SELECT * FROM y a, y b, jsonb_each(a.a);
----

query I rowsort
SELECT generate_series FROM generate_series(1, 3)
----
1
2
3

query I rowsort
SELECT generate_series FROM generate_series(2, 4)
----
2
3
4

query II colnames,rowsort
SELECT * FROM generate_series(2, 4) WITH ORDINALITY
----
generate_series ordinality
2 1
3 2
4 3

query I rowsort
SELECT generate_series FROM generate_series(-2, 2)
----
0
1
2
-2
-1

query I rowsort
SELECT generate_series FROM generate_series(-2::bigint, 2)
----
0
1
2
-2
-1

query I
SELECT generate_series FROM generate_series(null, 1)
----

query I
SELECT generate_series FROM generate_series(1, null)
----

query error db error: ERROR: function generate_series\(unknown, unknown\) is not unique
SELECT generate_series FROM generate_series(null, null)
----

statement error invalid input syntax for type integer: invalid digit found in string: "foo"
SELECT generate_series FROM generate_series('foo', 2)

statement error invalid input syntax for type integer: invalid digit found in string: "foo"
SELECT generate_series FROM generate_series(1, 'foo')

statement error db error: ERROR: function generate_series\(integer\) does not exist
SELECT generate_series FROM generate_series(2)

query T multiline
EXPLAIN RAW PLAN FOR SELECT generate_series FROM generate_series(-2, 2)
----
CallTable generate_series(-2, 2, 1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT generate_series FROM generate_series(-2, 2)
----
Explained Query (fast path):
  Constant
    - (0)
    - (-1)
    - (1)
    - (2)
    - (-2)

Target cluster: quickstart

EOF

query I colnames
SELECT x FROM generate_series(1, 3) x
----
x
1
2
3

# generate_series with lateral joins.

statement ok
CREATE TABLE x (a INT PRIMARY KEY, b INT)

statement ok
INSERT INTO x VALUES (1, 2), (2, 3), (3, 4)

query III
SELECT * FROM x, generate_series(1, a)
----
1  2  1
2  3  1
2  3  2
3  4  1
3  4  2
3  4  3

# Both from the first one.
query IIIII
SELECT * FROM x x1, x x2, generate_series(x1.a, x1.b) WHERE x1.b = x2.b
----
1  2  1  2  1
1  2  1  2  2
2  3  2  3  2
2  3  2  3  3
3  4  3  4  3
3  4  3  4  4

# Both from the second one.
query IIIII
SELECT * FROM x x1, x x2, generate_series(x2.a, x2.b) WHERE x1.b = x2.b
----
1  2  1  2  1
1  2  1  2  2
2  3  2  3  2
2  3  2  3  3
3  4  3  4  3
3  4  3  4  4

# One from each.
query IIIII rowsort
SELECT * FROM x x1, x x2, generate_series(x1.a, x2.a) WHERE x1.b = x2.b
----
1  2  1  2  1
2  3  2  3  2
3  4  3  4  3

# Regression test for database-issues#1206: a table function as the first FROM item inside of
# a LATERAL subquery should not miscount outer scope depth.
query II
SELECT x.a, generate_series FROM x, LATERAL (SELECT * FROM generate_series(1, x.a))
----
1  1
2  1
2  2
3  1
3  2
3  3

# Regression test for database-issues#1700: crash when a filter references an output column of
# a table function
query IIIII rowsort
SELECT * FROM x x1, x x2, generate_series(x1.a, x2.b) AS x3(b) WHERE x1.b = x2.b AND x1.a = x3.b
----
1 2 1 2 1
2 3 2 3 2
3 4 3 4 3

query T multiline
EXPLAIN RAW PLAN FOR SELECT * FROM x, generate_series(1, a)
----
CrossJoin
  Get materialize.public.x
  CallTable generate_series(1, #^0{a}, 1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR SELECT * FROM x, generate_series(100::bigint, a)
----
CrossJoin
  Get materialize.public.x
  CallTable generate_series(integer_to_bigint(100), integer_to_bigint(#^0{a}), 1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM x, generate_series(1, 10)
----
Explained Query:
  CrossJoin type=differential // { arity: 3 }
    implementation
      %0:x[×] » %1[×]
    ArrangeBy keys=[[]] // { arity: 2 }
      ReadStorage materialize.public.x // { arity: 2 }
    ArrangeBy keys=[[]] // { arity: 1 }
      Constant // { arity: 1 }
        - (1)
        - (2)
        - (3)
        - (4)
        - (5)
        - (6)
        - (7)
        - (8)
        - (9)
        - (10)

Source materialize.public.x

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM x, generate_series(1, a)
----
Explained Query:
  FlatMap generate_series(1, #0{a}, 1) // { arity: 3 }
    ReadStorage materialize.public.x // { arity: 2 }

Source materialize.public.x

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM x x1, x x2, generate_series(x1.a, x2.a) WHERE x1.b = x2.b
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]] // { arity: 2 }
        Filter (#1{b}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.x // { arity: 2 }
  Return // { arity: 5 }
    Project (#0{a}..=#2{a}, #1{b}, #3) // { arity: 5 }
      FlatMap generate_series(#0{a}, #2{a}, 1) // { arity: 4 }
        Project (#0{a}..=#2{a}) // { arity: 3 }
          Join on=(#1{b} = #3{b}) type=differential // { arity: 4 }
            implementation
              %0:l0[#1{b}]K » %1:l0[#1{b}]K
            Get l0 // { arity: 2 }
            Get l0 // { arity: 2 }

Source materialize.public.x
  filter=((#1{b}) IS NOT NULL)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM x x1, x x2, generate_series(x1.a, x2.a) WHERE x1.b = x2.b
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]] // { arity: 2 }
        Filter (#1{b}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.x // { arity: 2 }
  Return // { arity: 5 }
    Project (#0{a}..=#2{a}, #1{b}, #3) // { arity: 5 }
      FlatMap generate_series(#0{a}, #2{a}, 1) // { arity: 4 }
        Project (#0{a}..=#2{a}) // { arity: 3 }
          Join on=(#1{b} = #3{b}) type=differential // { arity: 4 }
            implementation
              %0:l0[#1{b}]K » %1:l0[#1{b}]K
            Get l0 // { arity: 2 }
            Get l0 // { arity: 2 }

Source materialize.public.x
  filter=((#1{b}) IS NOT NULL)

Target cluster: quickstart

EOF

# Regression test for database-issues#1700: crash when a filter references an output column of
# a table function around a join
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM x x1, x x2, generate_series(x1.a, x2.b) AS x3(b) WHERE x1.b = x2.b AND x1.a = x3.b
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]] // { arity: 2 }
        Filter (#1{b}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.x // { arity: 2 }
  Return // { arity: 5 }
    Project (#0{a}..=#2{a}, #1{b}, #3) // { arity: 5 }
      Filter (#0{a} = #3{b}) // { arity: 4 }
        FlatMap generate_series(#0{a}, #1{b}, 1) // { arity: 4 }
          Project (#0{a}..=#2{a}) // { arity: 3 }
            Join on=(#1{b} = #3{b}) type=differential // { arity: 4 }
              implementation
                %0:l0[#1{b}]K » %1:l0[#1{b}]K
              Get l0 // { arity: 2 }
              Get l0 // { arity: 2 }

Source materialize.public.x
  filter=((#1{b}) IS NOT NULL)

Target cluster: quickstart

EOF

query I
SELECT * FROM generate_series(0,3), repeat_row(generate_series);
----
1
2
2
3
3
3

query I
SELECT abs(generate_series) FROM generate_series(-1, 2), repeat_row(generate_series);
----
2
2

statement error  Negative multiplicity in constant result: -1
SELECT * FROM (values ('a')), repeat_row(-1)

statement error constant folding encountered reduce on collection with non-positive multiplicities
SELECT (SELECT 1 FROM repeat_row(-1))

query T
SELECT generate_series FROM generate_series(null, null, null)
----

query I
SELECT generate_series FROM generate_series(1, 3, 1)
----
1
2
3

query error step size cannot equal zero
SELECT generate_series FROM generate_series(1, 100, 0)
----

query error step size cannot equal zero
SELECT generate_series FROM generate_series(1::bigint, 100::bigint, 0::bigint)
----


query I
SELECT generate_series FROM generate_series(1, 10, 11)
----
1

query I
SELECT generate_series FROM generate_series(1::bigint, 10::bigint, 11::bigint)
----
1

query I
SELECT generate_series FROM generate_series(3, 1, -1)
----
1
2
3

query I
SELECT generate_series FROM generate_series(3::bigint, 1::bigint, -1::bigint)
----
1
2
3

query I
SELECT generate_series FROM generate_series(1, 10, -1)
----

query I
SELECT generate_series FROM generate_series(1::bigint, 10::bigint, -1::bigint)
----

query I
SELECT generate_series FROM generate_series(2, 4, 2)
----
2
4

query I
SELECT generate_series FROM generate_series(2::bigint, 4::bigint, 2::bigint)
----
2
4

query I rowsort
SELECT generate_series FROM generate_series(-2, 2, 3)
----
1
-2

query I rowsort
SELECT generate_series FROM generate_series(-2::bigint, 2::bigint, 3::bigint)
----
1
-2

query I
SELECT generate_series FROM generate_series(-2::bigint, 2, 1)
ORDER BY 1
----
-2
-1
0
1
2

query I
SELECT generate_series FROM generate_series(null, 1, 1)
----

query I
SELECT generate_series FROM generate_series(null, 1::bigint, 1::bigint)
----

query I
SELECT generate_series FROM generate_series(1, null, 1)
----

query I
SELECT generate_series FROM generate_series(1::bigint, null, 1::bigint)
----

query T multiline
EXPLAIN RAW PLAN FOR SELECT generate_series FROM generate_series(-2, 2, 1)
----
CallTable generate_series(-2, 2, 1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR SELECT generate_series FROM generate_series(-2::bigint, 2::bigint, 1::bigint)
----
CallTable generate_series(integer_to_bigint(-2), integer_to_bigint(2), integer_to_bigint(1))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT generate_series FROM generate_series(-2, 2, 2)
----
Explained Query (fast path):
  Constant
    - (0)
    - (2)
    - (-2)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT generate_series FROM generate_series(-2::bigint, 2::bigint, 2::bigint)
----
Explained Query (fast path):
  Constant
    - (0)
    - (2)
    - (-2)

Target cluster: quickstart

EOF

# information_schema._pg_expandarray

query error db error: ERROR: function information_schema\._pg_expandarray\(\) does not exist
SELECT information_schema._pg_expandarray()

query error db error: ERROR: function information_schema\._pg_expandarray\(\) does not exist
SELECT * FROM information_schema._pg_expandarray()

query error cannot determine type of empty array
SELECT information_schema._pg_expandarray(ARRAY[])

query error cannot determine type of empty array
SELECT * FROM information_schema._pg_expandarray(ARRAY[])

query error could not determine polymorphic type because input has type unknown
SELECT * FROM information_schema._pg_expandarray(NULL)

query error could not determine polymorphic type because input has type unknown
SELECT information_schema._pg_expandarray(NULL)

query T colnames
SELECT information_schema._pg_expandarray(ARRAY[]::int[])
----
_pg_expandarray

query TI colnames
SELECT * FROM information_schema._pg_expandarray(ARRAY[]::int[])
----
x  n

query T colnames
SELECT information_schema._pg_expandarray(ARRAY[100])
----
 _pg_expandarray
(100,1)

query TI
SELECT * FROM information_schema._pg_expandarray(ARRAY[100])
----
100 1

query T
SELECT information_schema._pg_expandarray(ARRAY[2, 1]) ORDER BY 1
----
(1,2)
(2,1)

query II
SELECT * FROM information_schema._pg_expandarray(ARRAY[2, 1]) ORDER BY x
----
1 2
2 1

query T
SELECT information_schema._pg_expandarray(ARRAY[3, 2, 1]) ORDER BY 1
----
(1,3)
(2,2)
(3,1)

query II
SELECT * FROM information_schema._pg_expandarray(ARRAY[3, 2, 1]) ORDER BY x
----
1 3
2 2
3 1

query T
SELECT information_schema._pg_expandarray(ARRAY['a'])
----
(a,1)

query TI
SELECT * FROM information_schema._pg_expandarray(ARRAY['a'])
----
a 1

query T
SELECT information_schema._pg_expandarray(ARRAY['b', 'a']) ORDER BY 1
----
(a,2)
(b,1)

query TI
SELECT * FROM information_schema._pg_expandarray(ARRAY['b', 'a']) ORDER BY x
----
a 2
b 1

query T
SELECT information_schema._pg_expandarray(ARRAY['c', 'b', 'a']) ORDER BY 1
----
(a,3)
(b,2)
(c,1)

query TI
SELECT * FROM information_schema._pg_expandarray(ARRAY['c', 'b', 'a']) ORDER BY x
----
a 3
b 2
c 1

# Test table and column naming for table functions that return 1 column.

query T colnames
SELECT generate_series.* FROM generate_series(1, 1)
----
generate_series
1

query T
SELECT generate_series.generate_series FROM generate_series(1, 1)
----
1

query T colnames
SELECT g FROM generate_series(1, 1) AS g
----
g
1

query T
SELECT g.g FROM generate_series(1, 1) AS g
----
1

query T colnames
SELECT g.* FROM generate_series(1, 1) AS g
----
g
1

query T colnames
SELECT g FROM generate_series(1, 1) AS g(a)
----
g
1

query T colnames
SELECT g FROM ROWS FROM (generate_series(1, 1)) AS g(a)
----
g
1

query T colnames
SELECT jsonb_array_elements FROM ROWS FROM (jsonb_array_elements('[1]'), generate_series(1, 1));
----
jsonb_array_elements
(1,1)

query T colnames
SELECT jsonb_array_elements.generate_series FROM ROWS FROM (jsonb_array_elements('[1]'), generate_series(1, 1));
----
generate_series
1

query T colnames,rowsort
SELECT repeat_row FROM ROWS FROM (repeat_row(2), generate_series(1, 1));
----
repeat_row
()
(1)

query T colnames
SELECT g.a FROM generate_series(1, 1) AS g(a)
----
a
1

query T colnames
SELECT g.* FROM generate_series(1, 1) AS g(a)
----
a
1

statement error column "g.g" does not exist
SELECT g.g FROM generate_series(1, 1) AS g(a)

statement error column "generate_series" does not exist
SELECT generate_series FROM generate_series(1, 1) AS g

statement error column "g.generate_series" does not exist
SELECT g.generate_series FROM generate_series(1, 1) AS g

statement error column "generate_series.g" does not exist
SELECT generate_series.g FROM generate_series(1, 1) AS g

# Test table and column naming for set functions that return more than 1 column.

query T colnames
SELECT g FROM information_schema._pg_expandarray(ARRAY[100]) AS g
----
g
(100,1)

query TT colnames
SELECT _pg_expandarray.* FROM information_schema._pg_expandarray(ARRAY[100])
----
x n
100 1

query T
SELECT _pg_expandarray.x FROM information_schema._pg_expandarray(ARRAY[100])
----
100

query T
SELECT g FROM information_schema._pg_expandarray(ARRAY[100]) AS g
----
(100,1)

query T
SELECT g.x FROM information_schema._pg_expandarray(ARRAY[100]) AS g
----
100

query TT
SELECT g.* FROM information_schema._pg_expandarray(ARRAY[100]) AS g
----
100 1

query T colnames
SELECT information_schema._pg_expandarray(ARRAY['a']) AS x
----
x
(a,1)

# Test aliasing table functions and using named columns of the results.

query T rowsort
SELECT jsonb_array_elements.value->>'a' FROM jsonb_array_elements('[{"a":1},{"a":2},{"a":3}]')
----
1
2
3

query T rowsort
SELECT js.value->>'a' FROM jsonb_array_elements('[{"a":1},{"a":2},{"a":3}]') js
----
1
2
3

# Test more table/set functions in select projections.

statement ok
CREATE TABLE t (i int)

statement ok
INSERT INTO t VALUES (1), (2)

query II colnames
SELECT t.i, g.g FROM t, LATERAL generate_series(3,4) g(g) ORDER BY i, g
----
i g
1 3
1 4
2 3
2 4

# This should be identical to the above.
query II colnames
SELECT t.i, generate_series(3,4) g FROM t ORDER BY i, g
----
i g
1 3
1 4
2 3
2 4

query T colnames
SELECT jsonb_each('{"3":4,"1":2}'::JSONB) ORDER BY 1
----
jsonb_each
(1,2)
(3,4)

query ITT colnames
SELECT 1, jsonb_object_keys('{"1":2,"3":4}'::JSONB), jsonb_object_keys('{"1":2,"3":4,"5":6}'::JSONB) ORDER BY 3
----
?column? jsonb_object_keys jsonb_object_keys
1  1  1
1  3  3
1  NULL  5

query T colnames
SELECT jsonb_build_object(jsonb_object_keys('{"a":2, "b":3}'), 1, 'c', 3) ORDER BY 1
----
jsonb_build_object
{"a":1,"c":3}
{"b":1,"c":3}

query TT
SELECT jsonb_build_object(jsonb_object_keys('{"a":2, "b":3}'), 1, 'c', 3), jsonb_build_object(jsonb_object_keys('{"a":2, "b":3}'), 1, 'c', 3) ORDER BY 1
----
{"a":1,"c":3} {"a":1,"c":3}
{"b":1,"c":3} {"b":1,"c":3}

query error key cannot be null
SELECT jsonb_build_object(jsonb_object_keys('{"a":2, "b":3}'), 1, 'c', 3), jsonb_build_object(jsonb_object_keys('{"a":2}'), 1, 'c', 3)

query TT rowsort
SELECT jsonb_build_array(jsonb_object_keys('{"a":2, "b":3}')), jsonb_build_array(jsonb_object_keys('{"a":2}'));
----
["b"]  [null]
["a"]  ["a"]

query error table functions are not allowed in other table functions
SELECT jsonb_array_elements(jsonb_array_elements('[[1,2],[3,4]]')), jsonb_array_elements(jsonb_array_elements('[[1],[3,4,5]]')) ORDER BY 1

query error table functions are not allowed in other table functions
SELECT jsonb_array_elements(jsonb_array_elements('[[1,2],[3,4]]')), jsonb_array_elements('[7,8,9]') ORDER BY 1

# Postgres explicitly disallows table funcs (although it uses "set-returning
# functions") in CASE and COALESCE.
query error table functions are not allowed in CASE
SELECT i, CASE WHEN i > 0 THEN generate_series(1, 5) ELSE 0 END FROM t

query error table functions are not allowed in COALESCE
SELECT COALESCE(1, generate_series(1, 1))

query error table functions are not allowed in aggregate function calls
SELECT array_agg(generate_series(1, 2))

# Subqueries avoid the CASE errors.
query I
SELECT COALESCE(1, (SELECT generate_series(1, 1)))
----
1

query error table functions are not allowed in WHERE clause
SELECT 1 WHERE generate_series(1, 1)

# timestamp-based generate series

query T
SELECT * FROM generate_series('2021-01-01 00:00:00'::TIMESTAMP, '2021-01-01 02:00:00'::TIMESTAMP, '1 hour') ORDER BY 1
----
2021-01-01 00:00:00
2021-01-01 01:00:00
2021-01-01 02:00:00

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2021-01-01 00:00:00'::TIMESTAMP, '-1 hour') ORDER BY 1
----
2021-01-01 00:00:00
2021-01-01 01:00:00
2021-01-01 02:00:00
2021-01-01 03:00:00

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2021-01-03 00:00:00'::TIMESTAMP, '1 day') ORDER BY 1
----
2021-01-01 03:00:00
2021-01-02 03:00:00

query T
SELECT * FROM generate_series('2021-01-03 00:00:00'::TIMESTAMP, '2021-01-01 03:00:00'::TIMESTAMP, '1 day') ORDER BY 1
----

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2021-01-03 00:00:00'::TIMESTAMP, '-1 day') ORDER BY 1
----

query T
SELECT * FROM generate_series('2021-01-31 03:00:00'::TIMESTAMP, '2022-01-31 00:00:00'::TIMESTAMP, '1 month') ORDER BY 1
----
2021-01-31 03:00:00
2021-02-28 03:00:00
2021-03-28 03:00:00
2021-04-28 03:00:00
2021-05-28 03:00:00
2021-06-28 03:00:00
2021-07-28 03:00:00
2021-08-28 03:00:00
2021-09-28 03:00:00
2021-10-28 03:00:00
2021-11-28 03:00:00
2021-12-28 03:00:00
2022-01-28 03:00:00

query T
SELECT * FROM generate_series('2021-01-31 03:00:00'::TIMESTAMP, '2021-04-30 00:00:00'::TIMESTAMP, '1 month') ORDER BY 1
----
2021-01-31 03:00:00
2021-02-28 03:00:00
2021-03-28 03:00:00
2021-04-28 03:00:00

# Leap years
query T
SELECT * FROM generate_series('2020-01-31 03:00:00'::TIMESTAMP, '2020-04-30 00:00:00'::TIMESTAMP, '1 month') ORDER BY 1
----
2020-01-31 03:00:00
2020-02-29 03:00:00
2020-03-29 03:00:00
2020-04-29 03:00:00

query T
SELECT * FROM generate_series('2020-01-31 03:00:00'::TIMESTAMP, '2020-11-30 00:00:00'::TIMESTAMP, '2 month') ORDER BY 1
----
2020-01-31 03:00:00
2020-03-31 03:00:00
2020-05-31 03:00:00
2020-07-31 03:00:00
2020-09-30 03:00:00

query T
SELECT * FROM generate_series('2020-01-01 22:00:00'::TIMESTAMP, '2021-01-01 00:00:00'::TIMESTAMP, '1 month 1 day 1 hour') ORDER BY 1
----
2020-01-01 22:00:00
2020-02-02 23:00:00
2020-03-04 00:00:00
2020-04-05 01:00:00
2020-05-06 02:00:00
2020-06-07 03:00:00
2020-07-08 04:00:00
2020-08-09 05:00:00
2020-09-10 06:00:00
2020-10-11 07:00:00
2020-11-12 08:00:00
2020-12-13 09:00:00

query error step size cannot equal zero
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2021-01-03 00:00:00'::TIMESTAMP, '0 day');

# timestamptz-based generate series

query T
SELECT * FROM generate_series('2021-01-01 00:00:00'::TIMESTAMPTZ, '2021-01-01 02:00:00'::TIMESTAMPTZ, '1 hour') ORDER BY 1
----
2021-01-01 00:00:00+00
2021-01-01 01:00:00+00
2021-01-01 02:00:00+00

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMPTZ, '2021-01-01 00:00:00'::TIMESTAMPTZ, '-1 hour') ORDER BY 1
----
2021-01-01 00:00:00+00
2021-01-01 01:00:00+00
2021-01-01 02:00:00+00
2021-01-01 03:00:00+00

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMPTZ, '2021-01-03 00:00:00'::TIMESTAMPTZ, '1 day') ORDER BY 1
----
2021-01-01 03:00:00+00
2021-01-02 03:00:00+00

query T
SELECT * FROM generate_series('2021-01-03 00:00:00'::TIMESTAMPTZ, '2021-01-01 03:00:00'::TIMESTAMPTZ, '1 day') ORDER BY 1
----

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMPTZ, '2021-01-03 00:00:00'::TIMESTAMPTZ, '-1 day') ORDER BY 1
----

query T
SELECT * FROM generate_series('2021-01-31 03:00:00'::TIMESTAMPTZ, '2022-01-31 00:00:00'::TIMESTAMPTZ, '1 month') ORDER BY 1
----
2021-01-31 03:00:00+00
2021-02-28 03:00:00+00
2021-03-28 03:00:00+00
2021-04-28 03:00:00+00
2021-05-28 03:00:00+00
2021-06-28 03:00:00+00
2021-07-28 03:00:00+00
2021-08-28 03:00:00+00
2021-09-28 03:00:00+00
2021-10-28 03:00:00+00
2021-11-28 03:00:00+00
2021-12-28 03:00:00+00
2022-01-28 03:00:00+00

query T
SELECT * FROM generate_series('2021-01-31 03:00:00'::TIMESTAMPTZ, '2021-04-30 00:00:00'::TIMESTAMPTZ, '1 month') ORDER BY 1
----
2021-01-31 03:00:00+00
2021-02-28 03:00:00+00
2021-03-28 03:00:00+00
2021-04-28 03:00:00+00

# Leap years
query T
SELECT * FROM generate_series('2020-01-31 03:00:00'::TIMESTAMPTZ, '2020-04-30 00:00:00'::TIMESTAMPTZ, '1 month') ORDER BY 1
----
2020-01-31 03:00:00+00
2020-02-29 03:00:00+00
2020-03-29 03:00:00+00
2020-04-29 03:00:00+00

query T
SELECT * FROM generate_series('2020-01-31 03:00:00'::TIMESTAMPTZ, '2020-11-30 00:00:00'::TIMESTAMPTZ, '2 month') ORDER BY 1
----
2020-01-31 03:00:00+00
2020-03-31 03:00:00+00
2020-05-31 03:00:00+00
2020-07-31 03:00:00+00
2020-09-30 03:00:00+00

query T
SELECT * FROM generate_series('2020-01-01 22:00:00'::TIMESTAMPTZ, '2021-01-01 00:00:00'::TIMESTAMPTZ, '1 month 1 day 1 hour') ORDER BY 1
----
2020-01-01 22:00:00+00
2020-02-02 23:00:00+00
2020-03-04 00:00:00+00
2020-04-05 01:00:00+00
2020-05-06 02:00:00+00
2020-06-07 03:00:00+00
2020-07-08 04:00:00+00
2020-08-09 05:00:00+00
2020-09-10 06:00:00+00
2020-10-11 07:00:00+00
2020-11-12 08:00:00+00
2020-12-13 09:00:00+00

query error step size cannot equal zero
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMPTZ, '2021-01-03 00:00:00'::TIMESTAMPTZ, '0 day');

query error step size cannot equal zero
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2022-01-03 00:00:00'::TIMESTAMP, '1 month -30 day') ORDER BY 1;

query error step size cannot equal zero
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2022-01-03 00:00:00'::TIMESTAMP, '1 day -24 hr') ORDER BY 1;

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2021-01-05 00:00:00'::TIMESTAMP, '1 day -12 hrs') ORDER BY 1;
----
2021-01-01 03:00:00
2021-01-01 15:00:00
2021-01-02 03:00:00
2021-01-02 15:00:00
2021-01-03 03:00:00
2021-01-03 15:00:00
2021-01-04 03:00:00
2021-01-04 15:00:00

query T
SELECT * FROM generate_series('2021-01-01 03:00:00'::TIMESTAMP, '2021-02-01 00:00:00'::TIMESTAMP, '1 month -15 days') ORDER BY 1;
----
2021-01-01 03:00:00
2021-01-17 03:00:00

# Test ROWS FROM

query I colnames
SELECT * FROM LATERAL ROWS FROM (generate_series(1,1));
----
generate_series
1

query II colnames
SELECT * FROM LATERAL ROWS FROM (generate_series(1, 2), generate_series(3, 6)) ORDER BY 2;
----
generate_series generate_series
1 3
2 4
NULL 5
NULL 6

query III colnames
SELECT * FROM LATERAL ROWS FROM (generate_series(1, 2), generate_series(3, 6)) WITH ORDINALITY ORDER BY 3;
----
generate_series generate_series ordinality
1 3 1
2 4 2
NULL 5 3
NULL 6 4

query IIII colnames
SELECT * FROM ROWS FROM (generate_series(1, 3), generate_series(1, 6), generate_series(1, 9), generate_series(1, 12)) ORDER BY 4;
----
generate_series generate_series generate_series generate_series
1     1     1     1
2     2     2     2
3     3     3     3
NULL  4     4     4
NULL  5     5     5
NULL  6     6     6
NULL  NULL  7     7
NULL  NULL  8     8
NULL  NULL  9     9
NULL  NULL  NULL  10
NULL  NULL  NULL  11
NULL  NULL  NULL  12

query IIII colnames
SELECT * FROM ROWS FROM (generate_series(1, 2), information_schema._pg_expandarray(array[9]), generate_series(3, 6)) ORDER BY 4;
----
generate_series x n generate_series
1  9  1  3
2  NULL  NULL  4
NULL  NULL  NULL  5
NULL  NULL  NULL  6

query I colnames
SELECT generate_series FROM ROWS FROM (generate_series(1, 1))
----
generate_series
1

query error column reference "generate_series" is ambiguous
SELECT generate_series FROM ROWS FROM (generate_series(1, 1), generate_series(2, 2))

query I colnames
SELECT generate_series FROM ROWS FROM (generate_series(1, 1), information_schema._pg_expandarray(array[9]))
----
generate_series
1

query error column "_pg_expandarray" does not exist
SELECT _pg_expandarray FROM ROWS FROM (generate_series(1, 1), information_schema._pg_expandarray(array[9]))

query I
SELECT generate_series.x FROM ROWS FROM (generate_series(1, 1), information_schema._pg_expandarray(array[9]))
----
9

query I colnames
SELECT x FROM ROWS FROM (generate_series(1, 1), information_schema._pg_expandarray(array[9]))
----
x
9

# Convert to text because sqllogictest doesn't know how to format records.
query T colnames
SELECT _pg_expandarray::text FROM ROWS FROM (information_schema._pg_expandarray(array[9]))
----
_pg_expandarray
(9,1)

query II colnames
SELECT _pg_expandarray.* FROM ROWS FROM (information_schema._pg_expandarray(array[9]))
----
x n
9 1

# ROWS FROM with aliases

query error Expected right parenthesis
SELECT * FROM LATERAL ROWS FROM (generate_series(1,1) AS g)


query I colnames
SELECT * FROM LATERAL ROWS FROM (generate_series(1,1)) AS g
----
g
1

query I colnames
SELECT g.g FROM LATERAL ROWS FROM (generate_series(1,1)) AS g
----
g
1

query I colnames
SELECT g.g FROM LATERAL ROWS FROM (generate_series(1,1)) AS g(g)
----
g
1

query II colnames
SELECT * FROM LATERAL ROWS FROM (generate_series(1,1), generate_series(1,1)) AS g
----
generate_series generate_series
1 1

query II colnames
SELECT g.* FROM LATERAL ROWS FROM (generate_series(1,1), generate_series(1,1)) AS g
----
generate_series generate_series
1 1

query III colnames
SELECT g.* FROM LATERAL ROWS FROM (generate_series(1,1), information_schema._pg_expandarray(array[1])) AS g
----
generate_series x n
1 1 1

query III colnames
SELECT * FROM LATERAL ROWS FROM (generate_series(1,1), information_schema._pg_expandarray(array[1])) AS g(a,b,c)
----
a b c
1 1 1

# Multiple table funcs in SELECT projection.

query II colnames rowsort
SELECT generate_series(1,2) x, generate_series(1,2) ORDER BY 1
----
x generate_series
1 1
2 2

query II colnames rowsort
SELECT generate_series(1,2), generate_series(1,2) y ORDER BY 1
----
generate_series y
1 1
2 2

query II colnames rowsort
SELECT generate_series(1,2) x, generate_series(1,2) y ORDER BY 1
----
x y
1 1
2 2

query II
SELECT generate_series(1, 1), generate_series(2, 2)
----
1 2

query IT colnames
SELECT (information_schema._pg_expandarray(array[10])).x, information_schema._pg_expandarray(array[10])
----
x _pg_expandarray
10 (10,1)

query IIITITT colnames
SELECT
    (information_schema._pg_expandarray(ARRAY[100])).*,
    (information_schema._pg_expandarray(ARRAY[100])).x,
    information_schema._pg_expandarray(ARRAY[100])::text,
    generate_series(1, 2),
    jsonb_array_elements('[3]'),
    jsonb_array_elements('[4,5]')
ORDER BY generate_series
----
x n x _pg_expandarray generate_series jsonb_array_elements jsonb_array_elements
100 1 100 (100,1) 1 3 4
NULL NULL NULL NULL 2 NULL 5

query error column reference "generate_series" is ambiguous
SELECT
    generate_series(1, 2),
    generate_series(1, 3)
ORDER BY generate_series

# Duplicate, identical table functions are not ambiguous.
query II
SELECT
    generate_series(1, 2),
    generate_series(1, 2)
ORDER BY generate_series
----
1 1
2 2

query IIIT colnames,rowsort
SELECT generate_series(1, 2), (information_schema._pg_expandarray(ARRAY[100])).*, jsonb_array_elements('[3]')::text ORDER BY 1
----
generate_series x n jsonb_array_elements
1 100 1 3
2 NULL NULL NULL

query ITI colnames,rowsort
SELECT generate_series(1, 2), information_schema._pg_expandarray(ARRAY[100])::text, generate_series(1, 3) ORDER BY 3
----
generate_series _pg_expandarray generate_series
1 (100,1) 1
2 NULL 2
NULL NULL 3

# Some error cases

query error table functions are not allowed in WHERE clause
SELECT 1 FROM t WHERE generate_series(1, 1) = 1

# TODO: This error should complain about the DISTINCT.
query error table functions are not allowed in SELECT clause
SELECT generate_series(DISTINCT 1, 1)

# Subqueries avoid the uniqueness check.
query II
SELECT generate_series(1, 1), (SELECT generate_series(2, 2))
----
1 2

query error type integer is not composite
SELECT (generate_series(1, 1)).*

query error not a composite type
SELECT (generate_series(1, 1)).generate_series

# GENERATE_SUBSCRIPTS

query I
SELECT generate_subscripts('{1,2,3,4}'::int[], 1) AS s;
----
1
2
3
4

query I
SELECT generate_subscripts('{1,NULL,3,NULL}'::int[], 1) AS s;
----
1
2
3
4

query I
SELECT generate_subscripts('{1,2,3,4}'::TEXT[], 1) AS s;
----
1
2
3
4


query I
SELECT generate_subscripts('{1,2,3,4}'::int[], 0) AS s;
----

query I
SELECT generate_subscripts('{1,2,3,4}'::int[], -1) AS s;
----

query I
SELECT generate_subscripts('{1,2,3,4}'::int[], 2) AS s;
----

query I
SELECT generate_subscripts('{1,2,3,4}'::int[], 12345) AS s;
----

query T
SELECT generate_subscripts('{{1,2,3,4}, {5,6,7,8}}'::int[], 1) AS s;
----
1
2

query T
SELECT generate_subscripts('{{1,2,3,4}, {5,6,7,8}}'::int[], 2) AS s;
----
1
2
3
4

query error table functions are not allowed in other table functions
SELECT generate_subscripts('{1,2,3,4}'::int[], 1), repeat_row(generate_series(1, 1)) AS s

# test lower bound, this should return indices 2,3,4
# but currently we dont support this syntax
query error invalid input syntax for type array
SELECT generate_subscripts('[2:4]={1,2,3,4}'::int[], 1) AS s;

query I
select generate_subscripts(ARRAY[ARRAY[1,2,3], ARRAY[4,5,6]], 1);
----
1
2

query I
select generate_subscripts(ARRAY[ARRAY[1,2,3], ARRAY[4,5,6]], 2);
----
1
2
3

query error could not determine polymorphic type because input has type unknown
SELECT generate_subscripts(NULL, 1)

# Regression test for database-issues#2939: LATERAL column references in ROWS FROM

query III
SELECT * FROM generate_series(1, 3) as foo(a), ROWS FROM (generate_series(foo.a, foo.a + 2), generate_series(foo.a, foo.a + 1)) order by 1, 2, 3;
----
1  1  1
1  2  2
1  3  NULL
2  2  2
2  3  3
2  4  NULL
3  3  3
3  4  4
3  5  NULL

query IIII
SELECT * FROM generate_series(1, 3) as foo(a), ROWS FROM (generate_series(foo.a, foo.a + 2), generate_series(foo.a, foo.a + 1), generate_series(foo.a, foo.a)) order by 1, 2, 3, 4;
----
1  1  1  1
1  2  2  NULL
1  3  NULL  NULL
2  2  2  2
2  3  3  NULL
2  4  NULL  NULL
3  3  3  3
3  4  4  NULL
3  5  NULL  NULL

# Regression test for database-issues#2941
statement ok
create view bar as select * from y, rows from (generate_series(1, 2), jsonb_array_elements(y.a));

# Regression for database-issues#3078
query IT
WITH a(x) AS (SELECT 'a') SELECT generate_series(1, 2), * FROM a ORDER BY generate_series
----
1 a
2 a

# Test optimization for single table function in scalar position
# Plan should be just a simple FlatMap + Project
# Regression test for database-issues#3173
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT jsonb_object_keys(a) FROM y;
----
Explained Query:
  Project (#1) // { arity: 1 }
    FlatMap jsonb_object_keys(#0{a}) // { arity: 2 }
      ReadStorage materialize.public.y // { arity: 1 }

Source materialize.public.y

Target cluster: quickstart

EOF

# Test that table functions should produce deterministic plans
query T multiline
EXPLAIN RAW PLAN FOR SELECT jsonb_object_keys('{"1":2}'::JSONB), jsonb_object_keys('{"1":2}'::JSONB);
----
Project (#0, #0)
  CallTable jsonb_object_keys(text_to_jsonb("{\"1\":2}"))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR SELECT jsonb_object_keys('{"1":2}'::JSONB), jsonb_object_keys('{"3":4}'::JSONB);
----
Project (#5, #6)
  Map (case when (#1) IS NULL then null else #0{jsonb_object_keys} end, case when (#3) IS NULL then null else #2{jsonb_object_keys} end)
    Project (#0, #1, #3..=#5)
      Map (coalesce(#2, #4))
        FullOuterJoin (#2 = #4)
          Map (row_number() over (order by []), #1)
            CallTable jsonb_object_keys(text_to_jsonb("{\"1\":2}"))
          Map (row_number() over (order by []))
            CallTable jsonb_object_keys(text_to_jsonb("{\"3\":4}"))

Target cluster: quickstart

EOF

# Disallowed table function behaviour

query I
SELECT generate_series FROM generate_series(1, 3) WHERE (generate_series < 3);
----
1
2

query error Expected right parenthesis, found BY
SELECT generate_series FROM generate_series(1, 3) OVER (ORDER BY generate_series);

query error Expected right parenthesis, found number "1"
SELECT generate_series FROM generate_series(DISTINCT 1, 3);

# Regression for https://github.com/MaterializeInc/database-issues/issues/6180
query error db error: ERROR: regexp_extract must specify at least one capture group
select regexp_extract('aaa', 'a')