github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/show_source (about) 1 # LogicTest: local 2 3 query T colnames 4 SELECT * FROM [SHOW client_encoding] 5 ---- 6 client_encoding 7 UTF8 8 9 query T colnames 10 SELECT c.x FROM [SHOW client_encoding] AS c(x) 11 ---- 12 x 13 UTF8 14 15 query TI colnames 16 SELECT * FROM [SHOW client_encoding] WITH ORDINALITY 17 ---- 18 client_encoding ordinality 19 UTF8 1 20 21 # We filter here because optimizer will be different depending on which 22 # configuration this logic test is running in, and session ID will vary. 23 query TT colnames 24 SELECT * 25 FROM [SHOW ALL] 26 WHERE variable != 'optimizer' AND variable != 'crdb_version' AND variable != 'session_id' 27 ---- 28 variable value 29 application_name · 30 bytea_output hex 31 client_encoding UTF8 32 client_min_messages notice 33 database test 34 datestyle ISO, MDY 35 default_int_size 8 36 default_tablespace · 37 default_transaction_isolation serializable 38 default_transaction_priority normal 39 default_transaction_read_only off 40 distsql off 41 enable_experimental_alter_column_type_general off 42 enable_implicit_select_for_update on 43 enable_insert_fast_path on 44 enable_zigzag_join on 45 experimental_distsql_planning off 46 experimental_enable_enums off 47 experimental_enable_hash_sharded_indexes off 48 experimental_enable_temp_tables off 49 experimental_optimizer_foreign_key_cascades on 50 experimental_partial_indexes off 51 extra_float_digits 0 52 force_savepoint_restart off 53 foreign_key_cascades_limit 10000 54 idle_in_transaction_session_timeout 0 55 integer_datetimes on 56 intervalstyle postgres 57 locality region=test,dc=dc1 58 lock_timeout 0 59 max_identifier_length 128 60 max_index_keys 32 61 node_id 1 62 optimizer_foreign_keys on 63 optimizer_use_histograms on 64 optimizer_use_multicol_stats on 65 reorder_joins_limit 4 66 require_explicit_primary_keys off 67 results_buffer_size 16384 68 row_security off 69 search_path public 70 serial_normalization rowid 71 server_encoding UTF8 72 server_version 9.5.0 73 server_version_num 90500 74 session_user root 75 sql_safe_updates off 76 standard_conforming_strings on 77 statement_timeout 0 78 synchronize_seqscans on 79 timezone UTC 80 tracing off 81 transaction_isolation serializable 82 transaction_priority normal 83 transaction_read_only off 84 transaction_status NoTxn 85 vectorize on 86 vectorize_row_count_threshold 0 87 88 query T colnames 89 SELECT * FROM [SHOW CLUSTER SETTING sql.defaults.distsql] 90 ---- 91 sql.defaults.distsql 92 off 93 94 query TTTBT colnames 95 SELECT * FROM [SHOW ALL CLUSTER SETTINGS] WHERE variable LIKE '%organization' 96 ---- 97 variable value setting_type public description 98 cluster.organization · s true organization name 99 100 query TTTT colnames 101 SELECT * FROM [SHOW CLUSTER SETTINGS] WHERE variable LIKE '%organization' 102 ---- 103 variable value setting_type description 104 cluster.organization · s organization name 105 106 query TTTT colnames 107 SELECT * FROM [SHOW PUBLIC CLUSTER SETTINGS] WHERE variable LIKE '%organization' 108 ---- 109 variable value setting_type description 110 cluster.organization · s organization name 111 112 query T colnames 113 SELECT * FROM [SHOW SESSION_USER] 114 ---- 115 session_user 116 root 117 118 query T colnames 119 SELECT * FROM [SHOW DATABASE] 120 ---- 121 database 122 test 123 124 query TT colnames 125 SELECT * FROM [SHOW ZONE CONFIGURATIONS] LIMIT 0 126 ---- 127 target raw_config_sql 128 129 query TT colnames 130 SELECT * FROM [SHOW ZONE CONFIGURATION FOR TABLE system.users] LIMIT 0 131 ---- 132 target raw_config_sql 133 134 query T colnames,rowsort 135 SELECT * FROM [SHOW DATABASES] 136 ---- 137 database_name 138 defaultdb 139 postgres 140 system 141 test 142 143 query TTTTT colnames,rowsort 144 SELECT * FROM [SHOW GRANTS ON system.descriptor] 145 ---- 146 database_name schema_name table_name grantee privilege_type 147 system public descriptor admin GRANT 148 system public descriptor admin SELECT 149 system public descriptor root GRANT 150 system public descriptor root SELECT 151 152 query TTBITTBB colnames 153 SELECT * FROM [SHOW INDEX FROM system.descriptor] 154 ---- 155 table_name index_name non_unique seq_in_index column_name direction storing implicit 156 descriptor primary false 1 id ASC false false 157 158 query TTTTB colnames 159 SELECT * FROM [SHOW CONSTRAINT FROM system.descriptor] 160 ---- 161 table_name constraint_name constraint_type details validated 162 descriptor primary PRIMARY KEY PRIMARY KEY (id ASC) true 163 164 query TTBITTBB colnames 165 SELECT * FROM [SHOW KEYS FROM system.descriptor] 166 ---- 167 table_name index_name non_unique seq_in_index column_name direction storing implicit 168 descriptor primary false 1 id ASC false false 169 170 query T colnames,rowsort 171 SELECT * FROM [SHOW SCHEMAS FROM system] 172 ---- 173 schema_name 174 crdb_internal 175 information_schema 176 pg_catalog 177 pg_extension 178 public 179 180 query T colnames 181 SELECT * FROM [SHOW SEQUENCES FROM system] 182 ---- 183 sequence_name 184 185 query TTT colnames,rowsort 186 SELECT * FROM [SHOW TABLES FROM system] 187 ---- 188 schema_name table_name type 189 public namespace table 190 public descriptor table 191 public users table 192 public zones table 193 public settings table 194 public tenants table 195 public lease table 196 public eventlog table 197 public rangelog table 198 public ui table 199 public jobs table 200 public web_sessions table 201 public table_statistics table 202 public locations table 203 public role_members table 204 public comments table 205 public replication_constraint_stats table 206 public replication_critical_localities table 207 public replication_stats table 208 public reports_meta table 209 public namespace2 table 210 public protected_ts_meta table 211 public protected_ts_records table 212 public role_options table 213 public statement_bundle_chunks table 214 public statement_diagnostics_requests table 215 public statement_diagnostics table 216 217 query TTTT colnames,rowsort 218 SELECT * FROM [SHOW TABLES FROM system WITH COMMENT] 219 ---- 220 schema_name table_name type comment 221 public namespace table · 222 public descriptor table · 223 public users table · 224 public zones table · 225 public settings table · 226 public tenants table · 227 public lease table · 228 public eventlog table · 229 public rangelog table · 230 public ui table · 231 public jobs table · 232 public web_sessions table · 233 public table_statistics table · 234 public locations table · 235 public role_members table · 236 public comments table · 237 public replication_constraint_stats table · 238 public replication_critical_localities table · 239 public replication_stats table · 240 public reports_meta table · 241 public namespace2 table · 242 public protected_ts_meta table · 243 public protected_ts_records table · 244 public role_options table · 245 public statement_bundle_chunks table · 246 public statement_diagnostics_requests table · 247 public statement_diagnostics table · 248 249 query ITTT colnames 250 SELECT node_id, user_name, application_name, active_queries 251 FROM [SHOW SESSIONS] 252 WHERE active_queries != '' 253 ---- 254 node_id user_name application_name active_queries 255 1 root · SELECT node_id, user_name, application_name, active_queries FROM [SHOW CLUSTER SESSIONS] WHERE active_queries != '' 256 257 query ITT colnames 258 SELECT node_id, user_name, query FROM [SHOW QUERIES] 259 ---- 260 node_id user_name query 261 1 root SELECT node_id, user_name, query FROM [SHOW CLUSTER QUERIES] 262 263 264 query T colnames,rowsort 265 SELECT * FROM [SHOW SCHEMAS] 266 ---- 267 schema_name 268 crdb_internal 269 information_schema 270 pg_catalog 271 pg_extension 272 public 273 274 query TTT colnames 275 CREATE TABLE foo(x INT); SELECT * FROM [SHOW TABLES] 276 ---- 277 schema_name table_name type 278 public foo table 279 280 281 query T colnames 282 SELECT * FROM [SHOW TIMEZONE] 283 ---- 284 timezone 285 UTC 286 287 288 query T colnames 289 SELECT * FROM [SHOW TIME ZONE] 290 ---- 291 timezone 292 UTC 293 294 295 query T colnames 296 SELECT * FROM [SHOW TRANSACTION ISOLATION LEVEL] 297 ---- 298 transaction_isolation 299 serializable 300 301 302 query T colnames 303 SELECT * FROM [SHOW TRANSACTION PRIORITY] 304 ---- 305 transaction_priority 306 normal 307 308 query T colnames 309 SELECT * FROM [SHOW TRANSACTION STATUS] 310 ---- 311 transaction_status 312 NoTxn 313 314 315 query TT colnames 316 SELECT * FROM [SHOW CREATE TABLE system.descriptor] 317 ---- 318 table_name create_statement 319 system.public.descriptor CREATE TABLE descriptor ( 320 id INT8 NOT NULL, 321 descriptor BYTES NULL, 322 CONSTRAINT "primary" PRIMARY KEY (id ASC), 323 FAMILY "primary" (id), 324 FAMILY fam_2_descriptor (descriptor) 325 ) 326 327 328 query TT colnames 329 CREATE VIEW v AS SELECT id FROM system.descriptor; SELECT * FROM [SHOW CREATE VIEW v] 330 ---- 331 table_name create_statement 332 v CREATE VIEW v (id) AS SELECT id FROM system.public.descriptor 333 334 335 query TTT colnames 336 SELECT * FROM [SHOW USERS] ORDER BY 1 337 ---- 338 username options member_of 339 admin CREATEROLE {} 340 root CREATEROLE {admin} 341 testuser · {} 342 343 344 query TTTI colnames 345 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE system.descriptor] 346 ---- 347 start_key end_key replicas lease_holder 348 NULL NULL {1} 1 349 350 query TTTI colnames 351 CREATE INDEX ix ON foo(x); SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM INDEX foo@ix] 352 ---- 353 start_key end_key replicas lease_holder 354 NULL NULL {1} 1 355 356 query TTTTTTT colnames 357 SELECT * FROM [SHOW TRACE FOR SESSION] LIMIT 0 358 ---- 359 timestamp age message tag location operation span 360 361 query TTTT colnames 362 SELECT * FROM [SHOW COMPACT TRACE FOR SESSION] LIMIT 0 363 ---- 364 age message tag operation 365 366 query ITTTTTTTTTTRTI colnames 367 SELECT * FROM [SHOW JOBS] LIMIT 0 368 ---- 369 job_id job_type description statement user_name status running_status created started finished modified fraction_completed error coordinator_id 370 371 query TT colnames 372 SELECT * FROM [SHOW SYNTAX 'select 1; select 2'] 373 ---- 374 field message 375 sql SELECT 1 376 sql SELECT 2 377 378 # Test the SHOW SYNTAX statement. We avoid printing out the line number 379 # here because that is highly likely to change as code is edited. 380 query TT colnames 381 SELECT field, replace(message, e'\n', ' ') AS message FROM [SHOW SYNTAX 'foo'] 382 WHERE field != 'line' 383 ---- 384 field message 385 error at or near "foo": syntax error 386 code 42601 387 file lexer.go 388 function Error 389 detail source SQL: foo ^ 390 391 392 # Test the SHOW INDEXES FROM DATABASE COMMAND 393 statement ok 394 CREATE DATABASE showdbindexestest; 395 396 statement ok 397 CREATE TABLE showdbindexestest.table1 (key1 INT PRIMARY KEY); 398 399 statement ok 400 CREATE TABLE showdbindexestest.table2 (key2 INT PRIMARY KEY); 401 402 query TTBITTBB 403 SHOW INDEXES FROM DATABASE showdbindexestest; 404 ---- 405 table1 primary false 1 key1 ASC false false 406 table2 primary false 1 key2 ASC false false 407 408 statement ok 409 CREATE DATABASE "$peci@l"; 410 411 statement ok 412 CREATE TABLE "$peci@l".table1 (key1 INT PRIMARY KEY); 413 414 statement ok 415 CREATE TABLE "$peci@l".table2 (key2 INT PRIMARY KEY); 416 417 query TTBITTBB 418 SHOW INDEXES FROM DATABASE "$peci@l"; 419 ---- 420 table1 primary false 1 key1 ASC false false 421 table2 primary false 1 key2 ASC false false 422 423 # Test SHOW LOCALITY telemetry. 424 query T 425 SHOW LOCALITY 426 ---- 427 region=test,dc=dc1 428 429 query T 430 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.locality' AND usage_count > 0 431 ---- 432 sql.show.locality 433 434 # Test SHOW INDEXES telemetry. 435 statement ok 436 CREATE TABLE show_test (x INT PRIMARY KEY); 437 SHOW INDEXES FROM show_test 438 439 query T 440 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.indexes' AND usage_count > 0 441 ---- 442 sql.show.indexes 443 444 # Test SHOW CONSTRAINTS telemetry. 445 statement ok 446 SHOW CONSTRAINTS FROM show_test 447 448 query T 449 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.constraints' AND usage_count > 0 450 ---- 451 sql.show.constraints 452 453 # Test SHOW QUERIES telemetry. 454 statement ok 455 SHOW QUERIES 456 457 query T 458 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.queries' AND usage_count > 0 459 ---- 460 sql.show.queries 461 462 # Test SHOW JOBS telemetry. 463 statement ok 464 SHOW JOBS 465 466 query T 467 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.jobs' AND usage_count > 0 468 ---- 469 sql.show.jobs 470 471 # Tests identically named indexes do not show up twice with SHOW INDEXES. 472 subtest regression_46333 473 474 statement ok 475 CREATE TABLE t ( 476 x INT, 477 y INT, 478 z INT, 479 INDEX i1 (x), 480 INDEX i2 (y), 481 INDEX i3 (z) 482 ); CREATE TABLE t2 ( 483 x INT, 484 y INT, 485 z INT, 486 INDEX i1 (x), 487 INDEX i2 (y), 488 INDEX i3 (z) 489 ); COMMENT ON COLUMN t.x IS 'comment1'; 490 COMMENT ON COLUMN t.z IS 'comm"en"t2'; 491 COMMENT ON INDEX t@i2 IS 'comm''ent3' 492 493 query TTBITTBBT 494 SHOW INDEXES FROM t WITH COMMENT 495 ---- 496 t primary false 1 rowid ASC false false NULL 497 t i1 true 1 x ASC false false NULL 498 t i1 true 2 rowid ASC false true NULL 499 t i2 true 1 y ASC false false comm'ent3 500 t i2 true 2 rowid ASC false true comm'ent3 501 t i3 true 1 z ASC false false NULL 502 t i3 true 2 rowid ASC false true NULL 503 504 query TTBITTBBT 505 SHOW INDEXES FROM t2 WITH COMMENT 506 ---- 507 t2 primary false 1 rowid ASC false false NULL 508 t2 i1 true 1 x ASC false false NULL 509 t2 i1 true 2 rowid ASC false true NULL 510 t2 i2 true 1 y ASC false false NULL 511 t2 i2 true 2 rowid ASC false true NULL 512 t2 i3 true 1 z ASC false false NULL 513 t2 i3 true 2 rowid ASC false true NULL