github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/ddl (about)

     1  # LogicTest: local
     2  
     3  # ------------------------------------------------------------------------------
     4  # ALTER TABLE tests.
     5  # ------------------------------------------------------------------------------
     6  statement ok
     7  CREATE TABLE other (b INT PRIMARY KEY)
     8  
     9  statement ok
    10  INSERT INTO other VALUES (9)
    11  
    12  statement ok
    13  CREATE TABLE t (a INT PRIMARY KEY CHECK(a > 0), f INT REFERENCES other, INDEX (f), FAMILY (a, f))
    14  
    15  statement ok
    16  INSERT INTO t VALUES (1, 9)
    17  
    18  statement ok
    19  SET tracing = on,kv,results; SELECT * FROM t; SET tracing = off
    20  
    21  query T
    22  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
    23   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
    24   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
    25  ----
    26  fetched: /t/primary/1/f -> /9
    27  output row: [1 9]
    28  
    29  statement ok
    30  ALTER TABLE t ADD b INT
    31  
    32  statement ok
    33  ALTER TABLE t ADD CONSTRAINT foo UNIQUE (b)
    34  
    35  statement ok
    36  SET tracing = on,kv,results; SELECT b FROM t@foo; SET tracing = off
    37  
    38  query T
    39  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
    40   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
    41   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
    42  ----
    43  fetched: /t/foo/NULL -> /1
    44  output row: [NULL]
    45  
    46  statement ok
    47  ALTER TABLE t ADD c INT
    48  
    49  statement ok
    50  INSERT INTO t VALUES (2, 9, 1, 1), (3, 9, 2, 1)
    51  
    52  statement ok
    53  ALTER TABLE t DROP CONSTRAINT check_a
    54  
    55  statement ok
    56  DROP INDEX foo CASCADE
    57  
    58  statement ok
    59  SET tracing = on,kv,results; SELECT * FROM t; SET tracing = off
    60  
    61  query T
    62  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
    63   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
    64   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
    65  ----
    66  fetched: /t/primary/1/f -> /9
    67  fetched: /t/primary/2/f/b/c -> /9/1/1
    68  fetched: /t/primary/3/f/b/c -> /9/2/1
    69  output row: [1 9 NULL NULL]
    70  output row: [2 9 1 1]
    71  output row: [3 9 2 1]
    72  
    73  statement ok
    74  ALTER TABLE t DROP b, DROP c
    75  
    76  statement ok
    77  SET tracing = on,kv,results; SELECT * FROM t; SET tracing = off
    78  
    79  query T
    80  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
    81   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
    82   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
    83  ----
    84  fetched: /t/primary/1/f -> /9
    85  fetched: /t/primary/2/f -> /9
    86  fetched: /t/primary/3/f -> /9
    87  output row: [1 9]
    88  output row: [2 9]
    89  output row: [3 9]
    90  
    91  # Verify limits and orderings are propagated correctly to the select.
    92  statement ok
    93  CREATE TABLE s (k1 INT, k2 INT, v INT, PRIMARY KEY (k1,k2))
    94  
    95  query TTTTT colnames
    96  EXPLAIN (VERBOSE) ALTER TABLE s SPLIT AT SELECT k1,k2 FROM s ORDER BY k1 LIMIT 3
    97  ----
    98  tree       field        description   columns                              ordering
    99  ·          distributed  false         ·                                    ·
   100  ·          vectorized   false         ·                                    ·
   101  split      ·            ·             (key, pretty, split_enforced_until)  ·
   102   └── scan  ·            ·             (k1, k2)                             +k1
   103  ·          table        s@primary     ·                                    ·
   104  ·          spans        LIMITED SCAN  ·                                    ·
   105  ·          limit        3             ·                                    ·
   106  
   107  statement ok
   108  DROP TABLE t; DROP TABLE other
   109  
   110  # ------------------------------------------------------------------------------
   111  # CREATE INDEX tests.
   112  # ------------------------------------------------------------------------------
   113  statement ok
   114  CREATE TABLE t (
   115    a INT PRIMARY KEY,
   116    b INT,
   117    FAMILY (a),
   118    FAMILY (b)
   119  )
   120  
   121  statement ok
   122  INSERT INTO t VALUES (1,1)
   123  
   124  statement ok
   125  SET tracing = on,kv,results; SELECT * FROM t; SET tracing = off
   126  
   127  query T
   128  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
   129   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
   130   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
   131  ----
   132  fetched: /t/primary/1 -> NULL
   133  fetched: /t/primary/1/b -> 1
   134  output row: [1 1]
   135  
   136  user root
   137  
   138  statement ok
   139  CREATE INDEX foo ON t (b)
   140  
   141  statement error relation \"foo\" already exists
   142  CREATE INDEX foo ON t (a)
   143  
   144  statement error column "c" does not exist
   145  CREATE INDEX bar ON t (c)
   146  
   147  statement error index \"bar\" contains duplicate column \"b\"
   148  CREATE INDEX bar ON t (b, b);
   149  
   150  statement ok
   151  SET tracing = on,kv,results; SELECT * FROM t@foo; SET tracing = off
   152  
   153  query T
   154  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
   155   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
   156   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
   157  ----
   158  fetched: /t/foo/1/1 -> NULL
   159  output row: [1 1]
   160  
   161  statement ok
   162  INSERT INTO t VALUES (2,1)
   163  
   164  statement ok
   165  SET tracing = on,kv,results; SELECT * FROM t@foo; SET tracing = off
   166  
   167  query T
   168  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
   169   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
   170   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
   171  ----
   172  fetched: /t/foo/1/1 -> NULL
   173  fetched: /t/foo/1/2 -> NULL
   174  output row: [1 1]
   175  output row: [2 1]
   176  
   177  # test for DESC index
   178  
   179  statement ok
   180  DROP TABLE t
   181  
   182  statement ok
   183  CREATE TABLE t (
   184    a INT PRIMARY KEY,
   185    b INT,
   186    c INT,
   187    FAMILY "primary" (a, b, c)
   188  )
   189  
   190  statement ok
   191  INSERT INTO t VALUES (1,1,1), (2,2,2)
   192  
   193  statement ok
   194  CREATE INDEX b_desc ON t (b DESC)
   195  
   196  statement ok
   197  CREATE INDEX b_asc ON t (b ASC, c DESC)
   198  
   199  # index b_desc is well formed and column c is scanned using the primary index.
   200  statement ok
   201  SET tracing = on,kv,results; SELECT * FROM t@b_desc; SET tracing = off
   202  
   203  query T
   204  SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY
   205   WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
   206   ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC
   207  ----
   208  fetched: /t/b_desc/2/2 -> NULL
   209  fetched: /t/b_desc/1/1 -> NULL
   210  fetched: /t/primary/2/b/c -> /2/2
   211  fetched: /t/primary/1/b/c -> /1/1
   212  output row: [2 2 2]
   213  output row: [1 1 1]
   214  
   215  statement ok
   216  DROP table t
   217  
   218  # ------------------------------------------------------------------------------
   219  # CREATE VIEW tests.
   220  # ------------------------------------------------------------------------------
   221  statement ok
   222  CREATE TABLE test_kv(k INT PRIMARY KEY, v INT, w DECIMAL);
   223    CREATE UNIQUE INDEX test_v_idx ON test_kv(v);
   224    CREATE INDEX test_v_idx2 ON test_kv(v DESC) STORING(w);
   225    CREATE INDEX test_v_idx3 ON test_kv(w) STORING(v);
   226    CREATE TABLE test_kvr1(k INT PRIMARY KEY REFERENCES test_kv(k));
   227    CREATE TABLE test_kvr2(k INT, v INT UNIQUE REFERENCES test_kv(k));
   228    CREATE TABLE test_kvr3(k INT, v INT UNIQUE REFERENCES test_kv(v));
   229    CREATE TABLE test_kvi1(k INT PRIMARY KEY) INTERLEAVE IN PARENT test_kv(k);
   230    CREATE TABLE test_kvi2(k INT PRIMARY KEY, v INT);
   231    CREATE UNIQUE INDEX test_kvi2_idx ON test_kvi2(v) INTERLEAVE IN PARENT test_kv(v);
   232    CREATE VIEW test_v1 AS SELECT v FROM test_kv;
   233    CREATE VIEW test_v2 AS SELECT v FROM test_v1;