github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/partial_index (about)

     1  # LogicTest: !3node-tenant
     2  #### Partial Indexes
     3  
     4  # TODO(mgartner): remove this once partial indexes are fully supported.
     5  statement ok
     6  SET experimental_partial_indexes=on
     7  
     8  #### Validate partial index predicates.
     9  
    10  statement ok
    11  CREATE TABLE t1 (a INT, INDEX (a) WHERE a = 0)
    12  
    13  statement ok
    14  CREATE TABLE t2 (a INT, INDEX (a) WHERE false)
    15  
    16  # Allow immutable functions.
    17  statement ok
    18  CREATE TABLE t3 (a INT, INDEX (a) WHERE abs(1) > 2)
    19  
    20  # Don't allow non-boolean expressions.
    21  statement error expected index predicate expression to have type bool, but '1' has type int
    22  CREATE TABLE error (a INT, INDEX (a) WHERE 1)
    23  
    24  # Don't allow columns not in table.
    25  statement error pgcode 42703 column "b" does not exist, referenced in "b = 3"
    26  CREATE TABLE error (a INT, INDEX (a) WHERE b = 3)
    27  
    28  # Don't allow mutable functions.
    29  # TODO(mgartner): The error code for this should be 42P17, not 0A000.
    30  statement error pgcode 0A000 impure functions are not allowed in index predicate
    31  CREATE TABLE error (t TIMESTAMPTZ, INDEX (t) WHERE t < now())
    32  
    33  # Don't allow variable subexpressions.
    34  statement error pgcode 42601 variable sub-expressions are not allowed in index predicate
    35  CREATE TABLE error (a INT, INDEX (a) WHERE count(*) = 1)
    36  
    37  # Don't allow subqueries.
    38  statement error pgcode 42601 variable sub-expressions are not allowed in index predicate
    39  CREATE TABLE error (a INT, INDEX (a) WHERE (SELECT true))
    40  
    41  # Don't allow aggregate functions.
    42  statement error pgcode 42803 aggregate functions are not allowed in index predicate
    43  CREATE TABLE error (a INT, INDEX (a) WHERE sum(a) > 1)
    44  
    45  # Don't allow window functions.
    46  statement error pgcode 42P20 window functions are not allowed in index predicate
    47  CREATE TABLE error (a INT, INDEX (a) WHERE row_number() OVER () > 1)
    48  
    49  # Don't allow set-returning functions.
    50  statement error pgcode 0A000 generator functions are not allowed in index predicate
    51  CREATE TABLE error (a INT, INDEX (a) WHERE generate_series(1, 1))
    52  
    53  # Fail on bad types.
    54  statement error pq: unsupported binary operator: <bool> - <bool>
    55  CREATE TABLE error (a INT, INDEX (a) WHERE false - true)
    56  
    57  # Don't allow references to other tables.
    58  statement error no data source matches prefix: t1
    59  CREATE TABLE error (a INT, INDEX (a) WHERE t1.a > 0)
    60  
    61  # Don't allow references to unknown tables.
    62  statement error no data source matches prefix: unknown
    63  CREATE TABLE error (a INT, INDEX (a) WHERE unknown.a > 0)
    64  
    65  # Don't allow reference to unknown databases.
    66  statement error no data source matches prefix: unknown.error
    67  CREATE TABLE error (a INT, INDEX (a) WHERE unknown.error.a > 9)
    68  
    69  #### Validate CREATE TABLE ... UNIQUE INDEX predicate.
    70  
    71  statement ok
    72  CREATE TABLE t4 (a INT, UNIQUE INDEX (a) WHERE a = 0)
    73  
    74  # Don't allow invalid predicates.
    75  statement error expected index predicate expression to have type bool, but '1' has type int
    76  CREATE TABLE error (a INT, UNIQUE INDEX (a) WHERE 1)
    77  
    78  #### Validate CREATE INDEX predicate.
    79  
    80  statement ok
    81  CREATE TABLE t5 (a INT)
    82  
    83  statement ok
    84  CREATE INDEX t5i ON t5 (a) WHERE a = 0
    85  
    86  # Don't allow invalid predicates.
    87  statement error expected index predicate expression to have type bool, but '1' has type int
    88  CREATE INDEX error ON t5 (a) WHERE 1
    89  
    90  # Don't allow references to other tables in predicates.
    91  statement error no data source matches prefix: t4
    92  CREATE INDEX error ON t5 (a) WHERE t4.a = 1
    93  
    94  #### Dequalify table references.
    95  
    96  statement ok
    97  CREATE TABLE t6 (
    98      a INT,
    99      INDEX (a) WHERE a > 0,
   100      INDEX (a) WHERE t6.a > 1,
   101      INDEX (a DESC) WHERE test.t6.a > 2,
   102      UNIQUE INDEX (a) WHERE a > 3,
   103      UNIQUE INDEX (a) WHERE t6.a > 4,
   104      UNIQUE INDEX (a DESC) WHERE test.t6.a > 5
   105  )
   106  
   107  statement ok
   108  CREATE INDEX t6i1 ON t6 (a) WHERE a > 6;
   109  CREATE INDEX t6i2 ON t6 (a) WHERE t6.a > 7;
   110  CREATE INDEX t6i3 ON t6 (a DESC) WHERE test.t6.a > 8;
   111  
   112  query TT
   113  SHOW CREATE TABLE t6
   114  ----
   115  t6  CREATE TABLE t6 (
   116      a INT8 NULL,
   117      INDEX t6_a_idx (a ASC) WHERE a > 0,
   118      INDEX t6_a_idx1 (a ASC) WHERE a > 1,
   119      INDEX t6_a_idx2 (a DESC) WHERE a > 2,
   120      UNIQUE INDEX t6_a_key (a ASC) WHERE a > 3,
   121      UNIQUE INDEX t6_a_key1 (a ASC) WHERE a > 4,
   122      UNIQUE INDEX t6_a_key2 (a DESC) WHERE a > 5,
   123      INDEX t6i1 (a ASC) WHERE a > 6,
   124      INDEX t6i2 (a ASC) WHERE a > 7,
   125      INDEX t6i3 (a DESC) WHERE a > 8,
   126      FAMILY "primary" (a, rowid)
   127  )
   128