github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/sql_fn (about) 1 # LogicTest: local 2 3 # ------------------------------------------------------------------------------ 4 # AddGeometryColumn tests 5 # ------------------------------------------------------------------------------ 6 statement ok 7 CREATE TABLE my_spatial_table (k INT PRIMARY KEY) 8 9 query T 10 SELECT AddGeometryColumn ('test','public','my_spatial_table','geom1',4326,'POINT',2) 11 ---- 12 test.public.my_spatial_table.geom1 SRID:4326 TYPE:POINT DIMS:2 13 14 query TTTTT 15 EXPLAIN (VERBOSE) SELECT AddGeometryColumn ('test','public','my_spatial_table','geom1',4326,'POINT',2) 16 ---- 17 · distributed false · · 18 · vectorized false · · 19 root · · (addgeometrycolumn) · 20 ├── values · · (addgeometrycolumn) · 21 │ size 1 column, 1 row · · 22 │ row 0, expr 0 addgeometrycolumn('test', 'public', 'my_spatial_table', 'geom1', 4326, 'POINT', 2) · · 23 └── subquery · · · · 24 │ id @S1 · · 25 │ original sql ALTER TABLE test.public.my_spatial_table ADD COLUMN geom1 GEOMETRY(POINT,4326) · · 26 │ exec mode all rows · · 27 └── buffer node · · () · 28 │ label buffer 1 · · 29 └── alter table · · () · 30 31 query T 32 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 33 ---- 34 CREATE TABLE my_spatial_table ( 35 k INT8 NOT NULL, 36 geom1 GEOMETRY(POINT,4326) NULL, 37 CONSTRAINT "primary" PRIMARY KEY (k ASC), 38 FAMILY "primary" (k, geom1) 39 ) 40 41 query T 42 SELECT AddGeometryColumn ('public','my_spatial_table','geom2',4326,'POLYGON',2) 43 ---- 44 public.my_spatial_table.geom2 SRID:4326 TYPE:POLYGON DIMS:2 45 46 query T 47 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 48 ---- 49 CREATE TABLE my_spatial_table ( 50 k INT8 NOT NULL, 51 geom1 GEOMETRY(POINT,4326) NULL, 52 geom2 GEOMETRY(POLYGON,4326) NULL, 53 CONSTRAINT "primary" PRIMARY KEY (k ASC), 54 FAMILY "primary" (k, geom1, geom2) 55 ) 56 57 query T 58 SELECT AddGeometryColumn ('my_spatial_table','geom3',4326,'MULTIPOLYGON',2) 59 ---- 60 my_spatial_table.geom3 SRID:4326 TYPE:MULTIPOLYGON DIMS:2 61 62 query T 63 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 64 ---- 65 CREATE TABLE my_spatial_table ( 66 k INT8 NOT NULL, 67 geom1 GEOMETRY(POINT,4326) NULL, 68 geom2 GEOMETRY(POLYGON,4326) NULL, 69 geom3 GEOMETRY(MULTIPOLYGON,4326) NULL, 70 CONSTRAINT "primary" PRIMARY KEY (k ASC), 71 FAMILY "primary" (k, geom1, geom2, geom3) 72 ) 73 74 query T 75 SELECT AddGeometryColumn ('test','public','my_spatial_table','geom4',4326,'LINESTRING',2,true) 76 ---- 77 test.public.my_spatial_table.geom4 SRID:4326 TYPE:LINESTRING DIMS:2 78 79 query T 80 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 81 ---- 82 CREATE TABLE my_spatial_table ( 83 k INT8 NOT NULL, 84 geom1 GEOMETRY(POINT,4326) NULL, 85 geom2 GEOMETRY(POLYGON,4326) NULL, 86 geom3 GEOMETRY(MULTIPOLYGON,4326) NULL, 87 geom4 GEOMETRY(LINESTRING,4326) NULL, 88 CONSTRAINT "primary" PRIMARY KEY (k ASC), 89 FAMILY "primary" (k, geom1, geom2, geom3, geom4) 90 ) 91 92 query T 93 SELECT AddGeometryColumn ('public','my_spatial_table','geom5',4326,'MULTIPOINT',2,true) 94 ---- 95 public.my_spatial_table.geom5 SRID:4326 TYPE:MULTIPOINT DIMS:2 96 97 query T 98 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 99 ---- 100 CREATE TABLE my_spatial_table ( 101 k INT8 NOT NULL, 102 geom1 GEOMETRY(POINT,4326) NULL, 103 geom2 GEOMETRY(POLYGON,4326) NULL, 104 geom3 GEOMETRY(MULTIPOLYGON,4326) NULL, 105 geom4 GEOMETRY(LINESTRING,4326) NULL, 106 geom5 GEOMETRY(MULTIPOINT,4326) NULL, 107 CONSTRAINT "primary" PRIMARY KEY (k ASC), 108 FAMILY "primary" (k, geom1, geom2, geom3, geom4, geom5) 109 ) 110 111 query T 112 SELECT AddGeometryColumn ('my_spatial_table','geom6',4326,'MULTILINESTRING',2,true) 113 ---- 114 my_spatial_table.geom6 SRID:4326 TYPE:MULTILINESTRING DIMS:2 115 116 query T 117 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 118 ---- 119 CREATE TABLE my_spatial_table ( 120 k INT8 NOT NULL, 121 geom1 GEOMETRY(POINT,4326) NULL, 122 geom2 GEOMETRY(POLYGON,4326) NULL, 123 geom3 GEOMETRY(MULTIPOLYGON,4326) NULL, 124 geom4 GEOMETRY(LINESTRING,4326) NULL, 125 geom5 GEOMETRY(MULTIPOINT,4326) NULL, 126 geom6 GEOMETRY(MULTILINESTRING,4326) NULL, 127 CONSTRAINT "primary" PRIMARY KEY (k ASC), 128 FAMILY "primary" (k, geom1, geom2, geom3, geom4, geom5, geom6) 129 ) 130 131 query error relation "missing_table" does not exist 132 SELECT AddGeometryColumn ('missing_table','geom',4326,'POINT',2) 133 134 query error column "geom6" of relation "my_spatial_table" already exists 135 SELECT AddGeometryColumn ('my_spatial_table','geom6',4326,'POINT',2) 136 137 query error unimplemented: useTypmod=false is currently not supported with AddGeometryColumn 138 SELECT AddGeometryColumn ('my_spatial_table','geom7',4326,'POINT',2,false) 139 140 query error only dimension=2 is currently supported 141 SELECT AddGeometryColumn ('my_spatial_table','geom7',4326,'POINT',3) 142 143 query error unimplemented: non-constant argument passed to addgeometrycolumn 144 SELECT AddGeometryColumn ('my_spatial_table','geom'||k::string,4326,'POINT',2) FROM my_spatial_table 145 146 query error at or near "fakeshape": syntax error 147 SELECT AddGeometryColumn ('my_spatial_table','geom7',4326,'FAKESHAPE',2) 148 149 statement ok 150 CREATE TABLE other_table (k INT PRIMARY KEY); INSERT INTO other_table VALUES (1), (2) 151 152 # It's ok to select other columns and multiple rows. Each geom column only gets 153 # added once. 154 query ITT 155 SELECT 156 k, 157 AddGeometryColumn ('my_spatial_table','geom7',4326,'POINT',2), 158 AddGeometryColumn ('my_spatial_table','geom8',4326,'POINT',2) 159 FROM other_table 160 ---- 161 1 my_spatial_table.geom7 SRID:4326 TYPE:POINT DIMS:2 my_spatial_table.geom8 SRID:4326 TYPE:POINT DIMS:2 162 2 my_spatial_table.geom7 SRID:4326 TYPE:POINT DIMS:2 my_spatial_table.geom8 SRID:4326 TYPE:POINT DIMS:2 163 164 query TTTTT 165 EXPLAIN (VERBOSE) SELECT 166 k, 167 AddGeometryColumn ('my_spatial_table','geom7',4326,'POINT',2), 168 AddGeometryColumn ('my_spatial_table','geom8',4326,'POINT',2) 169 FROM other_table 170 ---- 171 · distributed false · · 172 · vectorized true · · 173 root · · (k, addgeometrycolumn, addgeometrycolumn) · 174 ├── render · · (k, addgeometrycolumn, addgeometrycolumn) · 175 │ │ render 0 k · · 176 │ │ render 1 addgeometrycolumn('my_spatial_table', 'geom7', 4326, 'POINT', 2) · · 177 │ │ render 2 addgeometrycolumn('my_spatial_table', 'geom8', 4326, 'POINT', 2) · · 178 │ └── scan · · (k) · 179 │ table other_table@primary · · 180 │ spans FULL SCAN · · 181 ├── subquery · · · · 182 │ │ id @S1 · · 183 │ │ original sql ALTER TABLE my_spatial_table ADD COLUMN geom7 GEOMETRY(POINT,4326) · · 184 │ │ exec mode all rows · · 185 │ └── buffer node · · () · 186 │ │ label buffer 1 · · 187 │ └── alter table · · () · 188 └── subquery · · · · 189 │ id @S2 · · 190 │ original sql ALTER TABLE my_spatial_table ADD COLUMN geom8 GEOMETRY(POINT,4326) · · 191 │ exec mode all rows · · 192 └── buffer node · · () · 193 │ label buffer 2 · · 194 └── alter table · · () · 195 196 # TODO(rytaft): Figure out why goem7 didn't get added even though the ALTER 197 # TABLE statement is included in the EXPLAIN plan above. 198 query T 199 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 200 ---- 201 CREATE TABLE my_spatial_table ( 202 k INT8 NOT NULL, 203 geom1 GEOMETRY(POINT,4326) NULL, 204 geom2 GEOMETRY(POLYGON,4326) NULL, 205 geom3 GEOMETRY(MULTIPOLYGON,4326) NULL, 206 geom4 GEOMETRY(LINESTRING,4326) NULL, 207 geom5 GEOMETRY(MULTIPOINT,4326) NULL, 208 geom6 GEOMETRY(MULTILINESTRING,4326) NULL, 209 geom8 GEOMETRY(POINT,4326) NULL, 210 CONSTRAINT "primary" PRIMARY KEY (k ASC), 211 FAMILY "primary" (k, geom1, geom2, geom3, geom4, geom5, geom6, geom8) 212 ) 213 214 # In a WHERE clause. 215 query I 216 SELECT * FROM other_table WHERE AddGeometryColumn ('my_spatial_table','geom9',4326,'GEOMETRY',2) IS NOT NULL 217 ---- 218 1 219 2 220 221 # In a subquery. 222 query T 223 SELECT (SELECT AddGeometryColumn ('my_spatial_table','geom10',0,'GEOMETRYCOLLECTION',2)) 224 ---- 225 my_spatial_table.geom10 SRID:0 TYPE:GEOMETRYCOLLECTION DIMS:2 226 227 query T 228 SELECT create_statement FROM [SHOW CREATE TABLE my_spatial_table] 229 ---- 230 CREATE TABLE my_spatial_table ( 231 k INT8 NOT NULL, 232 geom1 GEOMETRY(POINT,4326) NULL, 233 geom2 GEOMETRY(POLYGON,4326) NULL, 234 geom3 GEOMETRY(MULTIPOLYGON,4326) NULL, 235 geom4 GEOMETRY(LINESTRING,4326) NULL, 236 geom5 GEOMETRY(MULTIPOINT,4326) NULL, 237 geom6 GEOMETRY(MULTILINESTRING,4326) NULL, 238 geom8 GEOMETRY(POINT,4326) NULL, 239 geom9 GEOMETRY(GEOMETRY,4326) NULL, 240 geom10 GEOMETRY(GEOMETRYCOLLECTION) NULL, 241 CONSTRAINT "primary" PRIMARY KEY (k ASC), 242 FAMILY "primary" (k, geom1, geom2, geom3, geom4, geom5, geom6, geom8, geom9, geom10) 243 )