github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/numeric_references (about) 1 statement ok 2 CREATE TABLE x (a INT PRIMARY KEY, xx INT, b INT, c INT, INDEX bc (b,c)) 3 4 statement ok 5 INSERT INTO x VALUES (1), (2), (3) 6 7 statement ok 8 CREATE VIEW view_ref AS SELECT a, 1 FROM x 9 10 let $v_id 11 SELECT id FROM system.namespace WHERE name='view_ref' 12 13 statement error cannot specify an explicit column list when accessing a view by reference 14 SELECT * FROM [$v_id(1) AS _] 15 16 query II rowsort 17 SELECT * FROM [$v_id AS _] 18 ---- 19 1 1 20 2 1 21 3 1 22 23 query I rowsort 24 SELECT foo.a FROM [$v_id AS foo] 25 ---- 26 1 27 2 28 3 29 30 statement ok 31 CREATE SEQUENCE seq 32 33 let $seq_id 34 SELECT id FROM system.namespace WHERE name='seq' 35 36 query IIB 37 SELECT * FROM [$seq_id AS _] 38 ---- 39 0 0 true 40 41 # Col refs in sequences are ignored. 42 query IIB 43 SELECT * FROM [$seq_id(1) AS _] 44 ---- 45 0 0 true 46 47 query IIB 48 SELECT * FROM [$seq_id(1, 2) AS _] 49 ---- 50 0 0 true 51 52 statement ok 53 CREATE TABLE num_ref (a INT PRIMARY KEY, xx INT, b INT, c INT, INDEX bc (b,c)) 54 55 statement ok 56 CREATE TABLE num_ref_hidden (a INT, b INT) 57 58 statement ok 59 ALTER TABLE num_ref RENAME COLUMN b TO d 60 61 statement ok 62 ALTER TABLE num_ref RENAME COLUMN a TO p 63 64 statement ok 65 ALTER TABLE num_ref DROP COLUMN xx 66 67 statement ok 68 INSERT INTO num_ref VALUES (1, 10, 101), (2, 20, 200), (3, 30, 300) 69 70 statement ok 71 INSERT INTO num_ref_hidden VALUES (1, 10), (2, 20), (3, 30) 72 73 query III rowsort 74 SELECT * FROM num_ref 75 ---- 76 1 10 101 77 2 20 200 78 3 30 300 79 80 let $num_ref_id 81 SELECT id FROM system.namespace WHERE name='num_ref' 82 83 query III rowsort 84 SELECT * FROM [$num_ref_id as num_ref_alias] 85 ---- 86 1 10 101 87 2 20 200 88 3 30 300 89 90 query III rowsort 91 SELECT * FROM [$num_ref_id(4,3,1) AS num_ref_alias] 92 ---- 93 101 10 1 94 200 20 2 95 300 30 3 96 97 query I rowsort 98 SELECT * FROM [$num_ref_id(4) AS num_ref_alias]@[2] 99 ---- 100 101 101 200 102 300 103 104 query I rowsort 105 SELECT * FROM [$num_ref_id(1) AS num_ref_alias]@[1] 106 ---- 107 1 108 2 109 3 110 111 query III colnames,rowsort 112 SELECT * FROM [$num_ref_id(1,3,4) AS num_ref_alias(col1,col2,col3)] 113 ---- 114 col1 col2 col3 115 1 10 101 116 2 20 200 117 3 30 300 118 119 statement OK 120 UPSERT INTO [$num_ref_id AS num_ref_alias] VALUES (4, 40, 400) 121 122 statement OK 123 INSERT INTO [$num_ref_id(1) AS num_ref_alias] VALUES (5) 124 125 query III rowsort 126 SELECT * FROM [$num_ref_id as num_ref_alias] 127 ---- 128 1 10 101 129 2 20 200 130 3 30 300 131 4 40 400 132 5 NULL NULL 133 134 statement OK 135 DELETE FROM [$num_ref_id AS num_ref_alias]@bc WHERE @1=5 136 137 query I 138 DELETE FROM [$num_ref_id AS num_ref_alias] WHERE d=40 RETURNING num_ref_alias.c 139 ---- 140 400 141 142 query III rowsort 143 SELECT * FROM [$num_ref_id AS num_ref_alias] 144 ---- 145 1 10 101 146 2 20 200 147 3 30 300 148 149 statement OK 150 INSERT INTO [$num_ref_id AS num_ref_alias] (p, c) VALUES (4, 400) 151 152 query I 153 INSERT INTO [$num_ref_id(1,4) AS num_ref_alias] VALUES (5, 500) RETURNING num_ref_alias.d 154 ---- 155 NULL 156 157 query III rowsort 158 SELECT * FROM [$num_ref_id AS num_ref_alias] 159 ---- 160 1 10 101 161 2 20 200 162 3 30 300 163 4 NULL 400 164 5 NULL 500 165 166 query I 167 UPDATE [$num_ref_id AS num_ref_alias] SET d=40 WHERE p=4 RETURNING num_ref_alias.c 168 ---- 169 400 170 171 query III rowsort 172 SELECT * FROM [$num_ref_id AS num_ref_alias] 173 ---- 174 1 10 101 175 2 20 200 176 3 30 300 177 4 40 400 178 5 NULL 500 179 180 statement error pq: cannot specify both a list of column IDs and a list of column names 181 INSERT INTO [$num_ref_id(1,4) AS num_ref_alias] (p,c) VALUES (6, 600) 182 183 statement error pq: cannot specify a list of column IDs with DELETE 184 DELETE FROM [$num_ref_id(1) AS num_ref_alias] 185 186 statement error pq: cannot specify a list of column IDs with UPDATE 187 UPDATE [$num_ref_id(1) AS num_ref_alias] SET d=10 188 189 let $num_ref_hidden_id 190 SELECT id FROM system.namespace WHERE name='num_ref_hidden' 191 192 query I rowsort 193 SELECT * FROM [$num_ref_hidden_id(1,3) AS num_ref_hidden] 194 ---- 195 1 196 2 197 3 198 199 query I 200 SELECT count(rowid) FROM [$num_ref_hidden_id(3) AS num_ref_hidden] 201 ---- 202 3 203 204 # Ensure that privileges are checked when using numeric references. 205 user testuser 206 207 statement error pq: user testuser does not have SELECT privilege on relation num_ref 208 SELECT * FROM [$num_ref_id AS t] 209 210 statement error pq: user testuser does not have INSERT privilege on relation num_ref 211 INSERT INTO [$num_ref_id AS t] VALUES (1) 212 213 statement error pq: user testuser does not have DELETE privilege on relation num_ref 214 DELETE FROM [$num_ref_id AS t] 215 216 statement error pq: user testuser does not have UPDATE privilege on relation num_ref 217 UPDATE [$num_ref_id AS t] SET d=1