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

## This test file contains various complex queries that ORMs issue during
## startup or general use, that pre-CBO code can't handle. This file should be
## merged with the orms test file once the heuristic planner is gone.

statement ok
CREATE TABLE a (a INT PRIMARY KEY)

# ActiveRecord query that needs apply join.
query TTTBTITT
SELECT a.attname,
  format_type(a.atttypid, a.atttypmod),
  pg_get_expr(d.adbin, d.adrelid),
  a.attnotnull,
  a.atttypid,
  a.atttypmod,
  (SELECT c.collname
   FROM pg_collation c, pg_type t
   WHERE c.oid = a.attcollation
   AND t.oid = a.atttypid
   AND a.attcollation <> t.typcollation),
   col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"a"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
----
a  bigint  NULL  true  20  -1  NULL  NULL

# Navicat metadata query.

query TTBBB
SELECT
    attname AS name,
    attrelid AS tid,
    COALESCE(
        (
            SELECT
                attnum = ANY conkey
            FROM
                pg_constraint
            WHERE
                contype = 'p' AND conrelid = attrelid
        ),
        false
    )
        AS primarykey,
    NOT (attnotnull) AS allownull,
    (
        SELECT
            seq.oid
        FROM
            pg_class AS seq
            LEFT JOIN pg_depend AS dep
            ON seq.oid = dep.objid
        WHERE
            (
                seq.relkind = 'S'::CHAR
                AND dep.refobjsubid = attnum
            )
            AND dep.refobjid = attrelid
    )
    IS NOT NULL
        AS autoincrement
FROM
    pg_attribute
WHERE
    (
        attisdropped = false
        AND attrelid
            = (
                    SELECT
                        tbl.oid
                    FROM
                        pg_class AS tbl
                        LEFT JOIN pg_namespace AS sch
                        ON tbl.relnamespace = sch.oid
                    WHERE
                        (
                            tbl.relkind = 'r'::"char"
                            AND tbl.relname = 'a'
                        )
                        AND sch.nspname = 'public'
                )
    )
    AND attname = 'a';
----
a  53  true  false  false