Materialize/test/sqllogictest/cockroach/ordinality.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/ordinality
#
# 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.

# not supported yet
halt

mode cockroach

query TI colnames
SELECT * FROM (VALUES ('a'), ('b')) WITH ORDINALITY AS x(name, i)
----
name i
a 1
b 2

query I colnames
SELECT ordinality FROM (VALUES ('a'), ('b')) WITH ORDINALITY
----
ordinality
1
2

statement ok
CREATE TABLE foo (x CHAR PRIMARY KEY)

statement ok
INSERT INTO foo(x) VALUES ('a'), ('b')

query TI
SELECT * FROM foo WITH ORDINALITY
----
a 1
b 2

query TI
SELECT * FROM foo WITH ORDINALITY LIMIT 1
----
a 1

query I
SELECT max(ordinality) FROM foo WITH ORDINALITY
----
2

query TITI rowsort
SELECT * FROM foo WITH ORDINALITY AS a, foo WITH ORDINALITY AS b
----
a 1 a 1
a 1 b 2
b 2 a 1
b 2 b 2

query TI
SELECT * FROM (SELECT x||x FROM foo) WITH ORDINALITY
----
aa 1
bb 2

query TII
SELECT * FROM (SELECT x, ordinality*2 FROM foo WITH ORDINALITY AS a) JOIN foo WITH ORDINALITY AS b USING (x)
----
a 2 1
b 4 2

statement ok
INSERT INTO foo(x) VALUES ('c')

query TI
SELECT * FROM foo WITH ORDINALITY WHERE x > 'a'
----
b 2
c 3

query TI
SELECT * FROM foo WITH ORDINALITY WHERE ordinality > 1 ORDER BY ordinality DESC
----
c 3
b 2

query TI
SELECT * FROM (SELECT * FROM foo WHERE x > 'a') WITH ORDINALITY
----
b 1
c 2

query B
SELECT ordinality = row_number() OVER () FROM foo WITH ORDINALITY
----
true
true
true

# Regression test for cockroach#33659
statement ok
TABLE [SHOW ZONE CONFIGURATIONS] WITH ORDINALITY