github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/pgadmin (about) 1 # Correlated subqueries. 2 3 # For testing, create the schema for the builtin tables. 4 exec-ddl 5 CREATE TABLE pg_stat_activity ( 6 datid OID NULL, 7 datname NAME NULL, 8 pid INTEGER NULL, 9 usesysid OID NULL, 10 username NAME NULL, 11 application_name STRING NULL, 12 client_addr INET NULL, 13 client_hostname STRING NULL, 14 client_port INTEGER NULL, 15 backend_start TIMESTAMP WITH TIME ZONE NULL, 16 xact_start TIMESTAMP WITH TIME ZONE NULL, 17 query_start TIMESTAMP WITH TIME ZONE NULL, 18 state_change TIMESTAMP WITH TIME ZONE NULL, 19 wait_event_type STRING NULL, 20 wait_event STRING NULL, 21 state STRING NULL, 22 backend_xid INTEGER NULL, 23 backend_xmin INTEGER NULL, 24 query STRING NULL 25 ) 26 ---- 27 28 exec-ddl 29 CREATE TABLE pg_roles ( 30 oid OID NULL, 31 rolname NAME NULL, 32 rolsuper BOOL NULL, 33 rolinherit BOOL NULL, 34 rolcreaterole BOOL NULL, 35 rolcreatedb BOOL NULL, 36 rolcatupdate BOOL NULL, 37 rolcanlogin BOOL NULL, 38 rolreplication BOOL NULL, 39 rolconnlimit INT NULL, 40 rolpassword STRING NULL, 41 rolvaliduntil TIMESTAMP WITH TIME ZONE NULL, 42 rolbypassrls BOOL NULL, 43 rolconfig STRING[] NULL 44 ) 45 ---- 46 47 opt 48 SELECT 49 pid AS "PID", 50 username AS "User", 51 datname AS "Database", 52 backend_start AS "Backend start", 53 CASE 54 WHEN client_hostname IS NOT NULL 55 AND client_hostname != '' 56 THEN client_hostname::STRING 57 || ':' 58 || client_port::STRING 59 WHEN client_addr IS NOT NULL 60 AND client_addr::STRING != '' 61 THEN client_addr::STRING || ':' || client_port::STRING 62 WHEN client_port = -1 THEN 'local pipe' 63 ELSE 'localhost:' || client_port::STRING 64 END 65 AS "Client", 66 application_name AS "Application", 67 query AS "Query", 68 query_start AS "Query start", 69 xact_start AS "Xact start" 70 FROM 71 pg_stat_activity AS sa 72 WHERE 73 ( 74 SELECT 75 r.rolsuper OR r.oid = sa.usesysid 76 FROM 77 pg_roles AS r 78 WHERE 79 r.rolname = current_user() 80 ) 81 ---- 82 project 83 ├── columns: PID:3 User:5 Database:2 "Backend start":10 Client:37 Application:6 Query:19 "Query start":12 "Xact start":11 84 ├── stable 85 ├── select 86 │ ├── columns: datname:2 pid:3 username:5 application_name:6 client_addr:7 client_hostname:8 client_port:9 backend_start:10 xact_start:11 query_start:12 query:19 sa.rowid:20!null "?column?":36!null 87 │ ├── stable 88 │ ├── key: (20) 89 │ ├── fd: ()-->(36), (20)-->(2,3,5-12,19) 90 │ ├── ensure-distinct-on 91 │ │ ├── columns: datname:2 pid:3 username:5 application_name:6 client_addr:7 client_hostname:8 client_port:9 backend_start:10 xact_start:11 query_start:12 query:19 sa.rowid:20!null "?column?":36 92 │ │ ├── grouping columns: sa.rowid:20!null 93 │ │ ├── error: "more than one row returned by a subquery used as an expression" 94 │ │ ├── stable 95 │ │ ├── key: (20) 96 │ │ ├── fd: (20)-->(2,3,5-12,19,36) 97 │ │ ├── left-join-apply 98 │ │ │ ├── columns: datname:2 pid:3 usesysid:4 username:5 application_name:6 client_addr:7 client_hostname:8 client_port:9 backend_start:10 xact_start:11 query_start:12 query:19 sa.rowid:20!null "?column?":36 99 │ │ │ ├── stable 100 │ │ │ ├── fd: (20)-->(2-12,19) 101 │ │ │ ├── scan sa 102 │ │ │ │ ├── columns: datname:2 pid:3 usesysid:4 username:5 application_name:6 client_addr:7 client_hostname:8 client_port:9 backend_start:10 xact_start:11 query_start:12 query:19 sa.rowid:20!null 103 │ │ │ │ ├── key: (20) 104 │ │ │ │ └── fd: (20)-->(2-12,19) 105 │ │ │ ├── project 106 │ │ │ │ ├── columns: "?column?":36 107 │ │ │ │ ├── outer: (4) 108 │ │ │ │ ├── stable 109 │ │ │ │ ├── select 110 │ │ │ │ │ ├── columns: oid:21 rolname:22!null rolsuper:23 111 │ │ │ │ │ ├── stable 112 │ │ │ │ │ ├── scan r 113 │ │ │ │ │ │ └── columns: oid:21 rolname:22 rolsuper:23 114 │ │ │ │ │ └── filters 115 │ │ │ │ │ └── rolname:22 = current_user() [outer=(22), stable, constraints=(/22: (/NULL - ])] 116 │ │ │ │ └── projections 117 │ │ │ │ └── rolsuper:23 OR (oid:21 = usesysid:4) [as="?column?":36, outer=(4,21,23)] 118 │ │ │ └── filters (true) 119 │ │ └── aggregations 120 │ │ ├── const-agg [as=datname:2, outer=(2)] 121 │ │ │ └── datname:2 122 │ │ ├── const-agg [as=pid:3, outer=(3)] 123 │ │ │ └── pid:3 124 │ │ ├── const-agg [as=username:5, outer=(5)] 125 │ │ │ └── username:5 126 │ │ ├── const-agg [as=application_name:6, outer=(6)] 127 │ │ │ └── application_name:6 128 │ │ ├── const-agg [as=client_addr:7, outer=(7)] 129 │ │ │ └── client_addr:7 130 │ │ ├── const-agg [as=client_hostname:8, outer=(8)] 131 │ │ │ └── client_hostname:8 132 │ │ ├── const-agg [as=client_port:9, outer=(9)] 133 │ │ │ └── client_port:9 134 │ │ ├── const-agg [as=backend_start:10, outer=(10)] 135 │ │ │ └── backend_start:10 136 │ │ ├── const-agg [as=xact_start:11, outer=(11)] 137 │ │ │ └── xact_start:11 138 │ │ ├── const-agg [as=query_start:12, outer=(12)] 139 │ │ │ └── query_start:12 140 │ │ ├── const-agg [as=query:19, outer=(19)] 141 │ │ │ └── query:19 142 │ │ └── const-agg [as="?column?":36, outer=(36)] 143 │ │ └── "?column?":36 144 │ └── filters 145 │ └── "?column?":36 [outer=(36), constraints=(/36: [/true - /true]; tight), fd=()-->(36)] 146 └── projections 147 └── CASE WHEN (client_hostname:8 IS NOT NULL) AND (client_hostname:8 != '') THEN (client_hostname:8 || ':') || client_port:9::STRING WHEN (client_addr:7 IS NOT NULL) AND (client_addr:7::STRING != '') THEN (client_addr:7::STRING || ':') || client_port:9::STRING WHEN client_port:9 = -1 THEN 'local pipe' ELSE 'localhost:' || client_port:9::STRING END [as=Client:37, outer=(7-9)]