orms

# TODO(mjibson): The fakedist-disk config produces an error. When fixed,
# remove this config line. See #38985.
# LogicTest: local fakedist 3node-tenant

## This test file contains various complex queries that ORMs issue during
## startup or general use.

## 12151
statement ok
CREATE TABLE a (id int UNIQUE, name string)

query TTTBOI
SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
    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
----
id     bigint  NULL            false  20  -1
name   text    NULL            false  25  -1
rowid  bigint  unique_rowid()  true   20  -1


# Ordered aggregations are possible.
# #12115
query TT
SELECT t.typname enum_name, array_agg(e.enumlabel ORDER BY enumsortorder) enum_value
    FROM pg_type t
    JOIN pg_enum e ON t.oid = e.enumtypid
    JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname = 'public'
    GROUP BY 1
----


## 12207
statement ok
CREATE TABLE customers (
    name STRING PRIMARY KEY,
    id INT,
    INDEX (id)
)

statement ok
INSERT INTO customers VALUES ('jordan', 12), ('cuong', 13)

query TBBTTTT colnames
SELECT i.relname AS name,
       ix.indisprimary AS PRIMARY,
       ix.indisunique AS UNIQUE,
       ix.indkey AS indkey,
       array_agg(a.attnum) AS column_indexes,
       array_agg(a.attname) AS column_names,
       pg_get_indexdef(ix.indexrelid) AS definition
FROM pg_class t,
     pg_class i,
     pg_index ix,
     pg_attribute a
WHERE t.oid = ix.indrelid
AND   i.oid = ix.indexrelid
AND   a.attrelid = t.oid
AND   t.relkind = 'r'
AND   t.relname = 'customers' -- this query is run once for each table
GROUP BY i.relname,
         ix.indexrelid,
         ix.indisprimary,
         ix.indisunique,
         ix.indkey
ORDER BY i.relname
----
name              primary  unique  indkey  column_indexes  column_names  definition
customers_id_idx  false    false   2       {1,2}           {name,id}     CREATE INDEX customers_id_idx ON test.public.customers USING btree (id ASC)
customers_pkey    true     true    1       {1,2}           {name,id}     CREATE UNIQUE INDEX customers_pkey ON test.public.customers USING btree (name ASC)


query TT colnames
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
                     WHERE  i.indrelid = '"a"'::regclass
                     AND    i.indisprimary
----
attname  data_type
rowid    bigint

statement ok
CREATE TABLE b (id INT, a_id INT, FOREIGN KEY (a_id) REFERENCES a (id))

# ActiveRecord query for foreign keys
# https://github.com/rails/rails/blob/355a2fcf/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb#L583
query TTTTTT
SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete
FROM pg_constraint c
JOIN pg_class t1 ON c.conrelid = t1.oid
JOIN pg_class t2 ON c.confrelid = t2.oid
JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid
JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid
JOIN pg_namespace t3 ON c.connamespace = t3.oid
WHERE c.contype = 'f'
AND t1.relname ='b'
AND t3.nspname = ANY (current_schemas(false))
ORDER BY c.conname
----
a  a_id  id  b_a_id_fkey  a  a

# Default value columns in Rails produce these kinds of queries:
query O
SELECT 'decimal(18,2)'::regtype::oid
----
1700

# NOTE: Before 19.2, this returned 25 (oid.T_text), but due to updates to the
# type system to more correctly handle OIDs, this now returns 1043
# (oid.T_varchar), which is what PG returns.
query O
SELECT 'character varying'::regtype::oid
----
1043

statement ok
CREATE INDEX b_idx ON b(a_id);

# ActiveRecord 4.2.x query for checking if an index exists
# Relies on OID IN tuple support
query I
SELECT count(*)
FROM pg_class t
INNER JOIN pg_index d ON t.oid = d.indrelid
INNER JOIN pg_class i ON d.indexrelid = i.oid
WHERE i.relkind = 'i'
AND i.relname = 'b_idx'
AND t.relname = 'b'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)))
----
1

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

# ActiveRecord query for determining primary key cols.
query T
SELECT
    a.attname
FROM
    (
        SELECT
            indrelid, indkey, generate_subscripts(indkey, 1) AS idx
        FROM
            pg_index
        WHERE
            indrelid = '"c"'::REGCLASS AND indisprimary
    )
        AS i
    JOIN pg_attribute AS a ON
            a.attrelid = i.indrelid AND a.attnum = i.indkey[i.idx]
ORDER BY
    i.idx
----
a
b

statement ok
CREATE TABLE metatest (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 = '"metatest"'::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 = 'metatest'
                        )
                        AND sch.nspname = 'public'
                )
    )
    AND attname = 'a';
----
a  110  true  false  false

# Hibernate query.

query TTTOBIIITTOT rowsort
SELECT * FROM (SELECT n.nspname, c.relname, a.attname, a.atttypid, a.attnotnull OR ((t.typtype = 'd') AND t.typnotnull) AS attnotnull, a.atttypmod, a.attlen, row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, pg_get_expr(def.adbin, def.adrelid) AS adsrc, dsc.description, t.typbasetype, t.typtype FROM pg_catalog.pg_namespace AS n JOIN pg_catalog.pg_class AS c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute AS a ON (a.attrelid = c.oid) JOIN pg_catalog.pg_type AS t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef AS def ON ((a.attrelid = def.adrelid) AND (a.attnum = def.adnum)) LEFT JOIN pg_catalog.pg_description AS dsc ON ((c.oid = dsc.objoid) AND (a.attnum = dsc.objsubid)) LEFT JOIN pg_catalog.pg_class AS dc ON ((dc.oid = dsc.classoid) AND (dc.relname = 'pg_class')) LEFT JOIN pg_catalog.pg_namespace AS dn ON ((dc.relnamespace = dn.oid) AND (dn.nspname = 'pg_catalog')) WHERE (((c.relkind IN ('r', 'v', 'f', 'm')) AND (a.attnum > 0)) AND (NOT a.attisdropped)) AND (n.nspname LIKE 'public')) AS c;
----
public  a          id     20  false  -1  8   1  NULL            NULL  0  b
public  a          rowid  20  true   -1  8   3  unique_rowid()  NULL  0  b
public  a          name   25  false  -1  -1  2  NULL            NULL  0  b
public  customers  id     20  false  -1  8   2  NULL            NULL  0  b
public  customers  name   25  true   -1  -1  1  NULL            NULL  0  b
public  b          rowid  20  true   -1  8   3  unique_rowid()  NULL  0  b
public  b          id     20  false  -1  8   1  NULL            NULL  0  b
public  b          a_id   20  false  -1  8   2  NULL            NULL  0  b
public  c          a      20  true   -1  8   1  NULL            NULL  0  b
public  c          b      20  true   -1  8   2  NULL            NULL  0  b
public  metatest   a      20  true   -1  8   1  NULL            NULL  0  b


# Regression test for windower not using EncDatum.Fingerprint.
statement ok
SELECT
	array_agg(t_pk.table_name ORDER BY t_pk.table_name)
FROM
	information_schema.statistics AS i
	LEFT JOIN (
			SELECT
				array_agg(c.column_name) AS table_primary_key_columns,
				c.table_name
			FROM
				information_schema.columns AS c
			GROUP BY
				c.table_name
		)
			AS t_pk ON i.table_name = t_pk.table_name
GROUP BY
	t_pk.table_primary_key_columns

# Regression test for pgcli's foreign key query.
query TTTTTT
SELECT
  s_p.nspname AS parentschema,
  t_p.relname AS parenttable,
  unnest(
    (
      SELECT
        array_agg(attname ORDER BY i)
      FROM
        (
          SELECT
            unnest(confkey) AS attnum,
            generate_subscripts(confkey, 1) AS i
        )
          AS x
        JOIN pg_catalog.pg_attribute AS c USING (attnum)
      WHERE
        c.attrelid = fk.confrelid
    )
  )
    AS parentcolumn,
  s_c.nspname AS childschema,
  t_c.relname AS childtable,
  unnest(
    (
      SELECT
        array_agg(attname ORDER BY i)
      FROM
        (
          SELECT
            unnest(conkey) AS attnum,
            generate_subscripts(conkey, 1) AS i
        )
          AS x
        JOIN pg_catalog.pg_attribute AS c USING (attnum)
      WHERE
        c.attrelid = fk.conrelid
    )
  )
    AS childcolumn
FROM
  pg_catalog.pg_constraint AS fk
  JOIN pg_catalog.pg_class AS t_p ON t_p.oid = fk.confrelid
  JOIN pg_catalog.pg_namespace AS s_p ON
      s_p.oid = t_p.relnamespace
  JOIN pg_catalog.pg_class AS t_c ON t_c.oid = fk.conrelid
  JOIN pg_catalog.pg_namespace AS s_c ON
      s_c.oid = t_c.relnamespace
WHERE
  fk.contype = 'f';
----
public  a  id  public  b  a_id

# Regression test for #66576

statement ok
CREATE TABLE regression_66576 ()

query TOTTBTITIT
SELECT
  typname,
  typnamespace,
  typtype,
  typcategory,
  typnotnull,
  typelem,
  typlen,
  typbasetype,
  typtypmod,
  typdefaultbin
FROM pg_type WHERE typname = 'regression_66576'
----
regression_66576  105  c  C  false  0  -1  0  -1  NULL

query T
SELECT reltype FROM pg_class WHERE relname = 'regression_65576'
----

let $oid
SELECT reltype FROM pg_class WHERE relname = 'regression_66576'

query T
SELECT typname FROM pg_type WHERE oid = $oid
----
regression_66576

let $oid
SELECT typrelid FROM pg_type WHERE typname = 'regression_66576'

query T
SELECT relname FROM pg_class WHERE oid = $oid
----
regression_66576