modules/calcite/src/test/sql/function/generic/test_least_greatest.test

# name: test/sql/function/generic/test_least_greatest.test
# description: Test LEAST/GREATEST
# group: [generic]

# scalar usage
query I
SELECT LEAST(1)
----
1

query I
SELECT LEAST(1, 3)
----
1

query I
SELECT LEAST(1, 3, 0)
----
0

query I
SELECT LEAST(1, 3, 0, 2, 7, 8, 10, 11, -100, 30)
----
-100

query I
SELECT LEAST(1, 3, 0, 2, 7, 8, 10, 11, -100, 30, NULL)
----
NULL

query I
SELECT LEAST(NULL, 3, 0, 2, 7, 8, 10, 11, -100, 30, 1)
----
NULL

# double
query R
SELECT LEAST(1.0, 10.0)
----
1.000000

# strings
query T
SELECT LEAST('hello', 'world')
----
hello

query T
SELECT LEAST('hello', 'world', 'blabla', 'tree')
----
blabla

query T
SELECT LEAST('hello', 'world', 'blabla', 'tree')
----
blabla

# dates
query T
SELECT LEAST(DATE '1992-01-01', DATE '1994-02-02', DATE '1991-01-01')
----
1991-01-01

query T
SELECT LEAST(DATE '1992-01-01', DATE '1994-02-02', DATE '1991-01-01', NULL)
----
NULL

# tables
statement ok
CREATE TABLE t1(i INTEGER, j INTEGER)

statement ok
INSERT INTO t1 VALUES (1, NULL), (2, 1), (3, 7)

query II
SELECT LEAST(i, j), GREATEST(i, j) FROM t1 ORDER BY i
----
NULL	NULL
1	2
3	7

query II
SELECT LEAST(i, i + 1, j), GREATEST(i, i - 1, j) FROM t1 ORDER BY i
----
NULL	NULL
1	2
3	7

query II
SELECT LEAST(i, 800, i + 1, 1000, j), GREATEST(i, -1000, i - 1, -700, j, -800) FROM t1 ORDER BY i
----
NULL	NULL
1	2
3	7

query II
SELECT LEAST(i, 800, i + 1, 1000, j, NULL), GREATEST(i, -1000, i - 1, -700, j, -800) FROM t1 ORDER BY i
----
NULL	NULL
NULL	2
NULL	7

# selection vectors
query II
SELECT LEAST(i, j), GREATEST(i, j) FROM t1 WHERE j IS NOT NULL ORDER BY i
----
1	2
3	7

# generated strings
query T
SELECT LEAST(REPEAT(i::VARCHAR, 20), j::VARCHAR) FROM t1 ORDER BY i
----
NULL
1
33333333333333333333