Materialize/test/sqllogictest/cockroach/target_names.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/target_names
#
# 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

statement ok
CREATE TABLE t(a INT[], t STRING);

# Simple expressions get the underlying column name as name.
query ITTIIT colnames
SELECT *, a, a[0], (((((((((a))))))))), t COLLATE "en_US" FROM t
----
a t a a a t

# Functions and function-like expressions get the function name.
query ITTTTBTT colnames
SELECT array_length(a, 1),
       nullif(a, a),
       row(1,2,3),
       coalesce(a,a),
       iferror(a, a),
       iserror(a),
       if(true, a, a),
       current_user
FROM t
----
array_length  nullif  row  coalesce  iferror  iserror if current_user

# Literals get named just "?column?" except for true/false which are handled specially.
query ITRBBT colnames
SELECT 123, '123', 123.0, TRUE, FALSE, NULL
----
?column?  ?column?  ?column?  bool  bool   ?column?
123       123       123.0     true  false  NULL

# Casts get the underlying expression name if there is one,
# otherwise the name of the type.
query IITI colnames
SELECT t::INT, '123'::INT, t:::STRING, '123':::INT FROM t
----
t  int8  t  int8

# Field access gets the field name.
query T colnames
SELECT (pg_get_keywords()).word FROM t
----
word

# Array stuff is called "array"
query TT colnames
SELECT array[1,2,3], array(select 1)
----
array    array
{1,2,3}  {1}

# EXISTS in subqueries called "exists"
query B colnames
SELECT EXISTS(SELECT * FROM t)
----
exists
false

# CASE gets named after the ELSE branch, otherwise "case"
query IIIII colnames
SELECT CASE 1 WHEN 2 THEN 3 END,
       CASE 1 WHEN 2 THEN 3 ELSE a[0] END,
       CASE 1 WHEN 2 THEN 3 ELSE length(t) END,
       CASE 1 WHEN 2 THEN 3 ELSE (t||'a')::INT END,
       CASE 1 WHEN 2 THEN 3 ELSE 4 END
  FROM t
----
case  a  length  case  case

# Subqueries get named after the expression.
query III colnames
SELECT (SELECT 123 AS a),
       (VALUES (cos(1)::INT)),
       (SELECT cos(0)::INT)
----
a   column1 cos
123 0       1