github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/alter_table (about) 1 exec-ddl 2 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c STRING, INDEX b (b), UNIQUE INDEX bc (b,c)) 3 ---- 4 5 # Tests for ALTER TABLE SPLIT AT. 6 build 7 ALTER TABLE abc SPLIT AT VALUES (1), (2) 8 ---- 9 alter-table-split abc 10 ├── columns: key:2 pretty:3 split_enforced_until:4 11 ├── values 12 │ ├── columns: column1:1!null 13 │ ├── (1,) 14 │ └── (2,) 15 └── CAST(NULL AS STRING) 16 17 build 18 ALTER TABLE abc SPLIT AT VALUES (1), (2) WITH EXPIRATION '2200-01-01 00:00:00.0' 19 ---- 20 alter-table-split abc 21 ├── columns: key:2 pretty:3 split_enforced_until:4 22 ├── values 23 │ ├── columns: column1:1!null 24 │ ├── (1,) 25 │ └── (2,) 26 └── '2200-01-01 00:00:00.0' 27 28 build 29 ALTER TABLE abc SPLIT AT VALUES (1, 2), (3, 4) 30 ---- 31 error (42601): too many columns in SPLIT AT data 32 33 build 34 ALTER INDEX abc@bc SPLIT AT VALUES (1), (2) WITH EXPIRATION '2200-01-01 00:00:00.0' 35 ---- 36 alter-table-split abc@bc 37 ├── columns: key:2 pretty:3 split_enforced_until:4 38 ├── values 39 │ ├── columns: column1:1!null 40 │ ├── (1,) 41 │ └── (2,) 42 └── '2200-01-01 00:00:00.0' 43 44 build 45 ALTER INDEX abc@bc SPLIT AT VALUES (1, 'foo'), (2, 'bar') 46 ---- 47 alter-table-split abc@bc 48 ├── columns: key:3 pretty:4 split_enforced_until:5 49 ├── values 50 │ ├── columns: column1:1!null column2:2!null 51 │ ├── (1, 'foo') 52 │ └── (2, 'bar') 53 └── CAST(NULL AS STRING) 54 55 build 56 ALTER INDEX abc@bc SPLIT AT VALUES (1, 2), (3, 4) 57 ---- 58 error (42601): SPLIT AT data column 2 (c) must be of type string, not type int 59 60 build 61 ALTER INDEX abc@bc SPLIT AT SELECT b FROM abc ORDER BY a 62 ---- 63 alter-table-split abc@bc 64 ├── columns: key:4 pretty:5 split_enforced_until:6 65 ├── project 66 │ ├── columns: b:2 [hidden: a:1!null] 67 │ ├── ordering: +1 68 │ └── scan abc 69 │ ├── columns: a:1!null b:2 c:3 70 │ └── ordering: +1 71 └── CAST(NULL AS STRING) 72 73 # Tests for ALTER TABLE UNSPLIT. 74 build 75 ALTER TABLE abc UNSPLIT AT VALUES (1), (2) 76 ---- 77 alter-table-unsplit abc 78 ├── columns: key:1 pretty:2 79 └── values 80 ├── columns: column1:6!null 81 ├── (1,) 82 └── (2,) 83 84 build 85 ALTER TABLE abc UNSPLIT ALL 86 ---- 87 alter-table-unsplit-all abc 88 └── columns: key:1 pretty:2 89 90 build 91 ALTER TABLE abc UNSPLIT AT VALUES (1, 2), (3, 4) 92 ---- 93 error (42601): too many columns in UNSPLIT AT data 94 95 build 96 ALTER INDEX abc@bc UNSPLIT ALL 97 ---- 98 alter-table-unsplit-all abc@bc 99 └── columns: key:1 pretty:2 100 101 build 102 ALTER INDEX abc@bc UNSPLIT AT VALUES (1, 'foo'), (2, 'bar') 103 ---- 104 alter-table-unsplit abc@bc 105 ├── columns: key:1 pretty:2 106 └── values 107 ├── columns: column1:6!null column2:7!null 108 ├── (1, 'foo') 109 └── (2, 'bar') 110 111 build 112 ALTER INDEX abc@bc UNSPLIT AT VALUES (1, 2), (3, 4) 113 ---- 114 error (42601): UNSPLIT AT data column 2 (c) must be of type string, not type int 115 116 build 117 ALTER INDEX abc@bc UNSPLIT AT SELECT b FROM abc ORDER BY a 118 ---- 119 alter-table-unsplit abc@bc 120 ├── columns: key:1 pretty:2 121 └── project 122 ├── columns: b:7 [hidden: a:6!null] 123 ├── ordering: +6 124 └── scan abc 125 ├── columns: a:6!null b:7 c:8 126 └── ordering: +6 127 128 # Tests for ALTER TABLE EXPERIMENTAL_RELOCATE. 129 build 130 ALTER TABLE abc EXPERIMENTAL_RELOCATE VALUES (ARRAY[1,2,3], 1), (ARRAY[4], 2) 131 ---- 132 alter-table-relocate abc 133 ├── columns: key:1 pretty:2 134 └── values 135 ├── columns: column1:3 column2:4!null 136 ├── (ARRAY[1,2,3], 1) 137 └── (ARRAY[4], 2) 138 139 build 140 ALTER TABLE abc EXPERIMENTAL_RELOCATE LEASE VALUES (10), (11) 141 ---- 142 error (42601): less than 2 columns in EXPERIMENTAL_RELOCATE LEASE data 143 144 build 145 ALTER TABLE abc EXPERIMENTAL_RELOCATE LEASE VALUES (10, 1, 2), (11, 3, 4) 146 ---- 147 error (42601): too many columns in EXPERIMENTAL_RELOCATE LEASE data 148 149 build 150 ALTER INDEX abc@bc EXPERIMENTAL_RELOCATE VALUES (ARRAY[5], 1, 'foo'), (ARRAY[6,7,8], 2, 'bar') 151 ---- 152 alter-table-relocate abc@bc 153 ├── columns: key:1 pretty:2 154 └── values 155 ├── columns: column1:3 column2:4!null column3:5!null 156 ├── (ARRAY[5], 1, 'foo') 157 └── (ARRAY[6,7,8], 2, 'bar') 158 159 build 160 ALTER INDEX abc@bc EXPERIMENTAL_RELOCATE VALUES (5, 1, 'foo'), (6, 2, 'bar') 161 ---- 162 error (42601): EXPERIMENTAL_RELOCATE data column 1 (relocation array) must be of type int[], not type int 163 164 build 165 ALTER INDEX abc@bc EXPERIMENTAL_RELOCATE LEASE VALUES (ARRAY[5], 1, 'foo'), (ARRAY[6,7,8], 2, 'bar') 166 ---- 167 error (42601): EXPERIMENTAL_RELOCATE LEASE data column 1 (target leaseholder) must be of type int, not type int[] 168 169 build 170 ALTER INDEX abc@bc EXPERIMENTAL_RELOCATE VALUES (1, 2), (3, 4) 171 ---- 172 error (42601): EXPERIMENTAL_RELOCATE data column 1 (relocation array) must be of type int[], not type int 173 174 build 175 ALTER INDEX abc@bc EXPERIMENTAL_RELOCATE VALUES (ARRAY[1,2], 1, 2), (ARRAY[3,4], 3, 4) 176 ---- 177 error (42601): EXPERIMENTAL_RELOCATE data column 3 (c) must be of type string, not type int 178 179 build 180 ALTER INDEX abc@bc EXPERIMENTAL_RELOCATE LEASE VALUES (10, 1, 'foo'), (11, 3, 'bar') 181 ---- 182 alter-table-relocate abc@bc [lease] 183 ├── columns: key:1 pretty:2 184 └── values 185 ├── columns: column1:3!null column2:4!null column3:5!null 186 ├── (10, 1, 'foo') 187 └── (11, 3, 'bar')