github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/pgwire/testdata/pgtest/row_description (about) 1 # This test verifies that we're populating the TableID and PGAttributeNum in the 2 # RowDescription message of the wire protocol. The IDs should remain consistent 3 # even when joining tables or when using views. 4 5 # Prepare the environment. 6 send 7 Query {"String": "DROP VIEW IF EXISTS v; DROP TABLE IF EXISTS tab3; DROP TABLE IF EXISTS tab2; DROP TABLE IF EXISTS tab1"} 8 ---- 9 10 until ignore=NoticeResponse 11 ReadyForQuery 12 ---- 13 {"Type":"CommandComplete","CommandTag":"DROP VIEW"} 14 {"Type":"CommandComplete","CommandTag":"DROP TABLE"} 15 {"Type":"CommandComplete","CommandTag":"DROP TABLE"} 16 {"Type":"CommandComplete","CommandTag":"DROP TABLE"} 17 {"Type":"ReadyForQuery","TxStatus":"I"} 18 19 # Start of test. 20 21 send 22 Query {"String": "CREATE TABLE tab1 (a INT8 PRIMARY KEY, b INT8)"} 23 ---- 24 25 until 26 ReadyForQuery 27 ---- 28 {"Type":"CommandComplete","CommandTag":"CREATE TABLE"} 29 {"Type":"ReadyForQuery","TxStatus":"I"} 30 31 send 32 Query {"String": "CREATE TABLE tab2 (c INT8 PRIMARY KEY, tab1_a INT8 REFERENCES tab1(a))"} 33 ---- 34 35 until 36 ReadyForQuery 37 ---- 38 {"Type":"CommandComplete","CommandTag":"CREATE TABLE"} 39 {"Type":"ReadyForQuery","TxStatus":"I"} 40 41 send 42 Query {"String": "INSERT INTO tab1 VALUES(1,2)"} 43 ---- 44 45 until 46 ReadyForQuery 47 ---- 48 {"Type":"CommandComplete","CommandTag":"INSERT 0 1"} 49 {"Type":"ReadyForQuery","TxStatus":"I"} 50 51 send 52 Query {"String": "INSERT INTO tab2 VALUES(4,1)"} 53 ---- 54 55 until 56 ReadyForQuery 57 ---- 58 {"Type":"CommandComplete","CommandTag":"INSERT 0 1"} 59 {"Type":"ReadyForQuery","TxStatus":"I"} 60 61 send 62 Query {"String": "CREATE VIEW v (v1, v2) AS SELECT a, tab1_a FROM tab1 JOIN tab2 ON tab1.a = tab2.tab1_a"} 63 ---- 64 65 until 66 ReadyForQuery 67 ---- 68 {"Type":"CommandComplete","CommandTag":"CREATE VIEW"} 69 {"Type":"ReadyForQuery","TxStatus":"I"} 70 71 send 72 Query {"String": "SELECT a FROM tab1"} 73 ---- 74 75 # With postgres we don't control the table OID. 76 until ignore_table_oids noncrdb_only 77 RowDescription 78 ---- 79 {"Type":"RowDescription","Fields":[{"Name":"a","TableOID":0,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]} 80 81 until crdb_only 82 RowDescription 83 ---- 84 {"Type":"RowDescription","Fields":[{"Name":"a","TableOID":52,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]} 85 86 until 87 ReadyForQuery 88 ---- 89 {"Type":"DataRow","Values":[{"text":"1"}]} 90 {"Type":"CommandComplete","CommandTag":"SELECT 1"} 91 {"Type":"ReadyForQuery","TxStatus":"I"} 92 93 send 94 Query {"String": "SELECT tab1.a, tab2.c FROM tab1 JOIN tab2 ON tab1.a = tab2.tab1_a"} 95 ---- 96 97 # With postgres we don't control the table OID. 98 until ignore_table_oids noncrdb_only 99 RowDescription 100 ---- 101 {"Type":"RowDescription","Fields":[{"Name":"a","TableOID":0,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"c","TableOID":0,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]} 102 103 until crdb_only 104 RowDescription 105 ---- 106 {"Type":"RowDescription","Fields":[{"Name":"a","TableOID":52,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"c","TableOID":53,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]} 107 108 until 109 ReadyForQuery 110 ---- 111 {"Type":"DataRow","Values":[{"text":"1"},{"text":"4"}]} 112 {"Type":"CommandComplete","CommandTag":"SELECT 1"} 113 {"Type":"ReadyForQuery","TxStatus":"I"} 114 115 send 116 Query {"String": "SELECT * FROM v WHERE v1 = 1"} 117 ---- 118 119 # With postgres we don't control the table OID. 120 until ignore_table_oids noncrdb_only 121 RowDescription 122 ---- 123 {"Type":"RowDescription","Fields":[{"Name":"v1","TableOID":0,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"v2","TableOID":0,"TableAttributeNumber":2,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]} 124 125 until crdb_only 126 RowDescription 127 ---- 128 {"Type":"RowDescription","Fields":[{"Name":"v1","TableOID":52,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"v2","TableOID":53,"TableAttributeNumber":2,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]} 129 130 until ignore_table_oids 131 ReadyForQuery 132 ---- 133 {"Type":"DataRow","Values":[{"text":"1"},{"text":"1"}]} 134 {"Type":"CommandComplete","CommandTag":"SELECT 1"} 135 {"Type":"ReadyForQuery","TxStatus":"I"} 136 137 send 138 Query {"String": "CREATE TABLE tab3 (a INT8 PRIMARY KEY, b CHAR(8))"} 139 ---- 140 141 until 142 ReadyForQuery 143 ---- 144 {"Type":"CommandComplete","CommandTag":"CREATE TABLE"} 145 {"Type":"ReadyForQuery","TxStatus":"I"} 146 147 send 148 Query {"String": "INSERT INTO tab3 VALUES(4,'hello')"} 149 ---- 150 151 until 152 ReadyForQuery 153 ---- 154 {"Type":"CommandComplete","CommandTag":"INSERT 0 1"} 155 {"Type":"ReadyForQuery","TxStatus":"I"} 156 157 send 158 Query {"String": "SELECT b FROM tab3"} 159 ---- 160 161 until ignore_table_oids noncrdb_only 162 RowDescription 163 ---- 164 {"Type":"RowDescription","Fields":[{"Name":"b","TableOID":0,"TableAttributeNumber":2,"DataTypeOID":1042,"DataTypeSize":-1,"TypeModifier":12,"Format":0}]} 165 166 until crdb_only 167 RowDescription 168 ---- 169 {"Type":"RowDescription","Fields":[{"Name":"b","TableOID":55,"TableAttributeNumber":2,"DataTypeOID":1042,"DataTypeSize":-1,"TypeModifier":12,"Format":0}]} 170 171 # The following discrepancy is a bug. 172 # See: https://github.com/cockroachdb/cockroach/issues/49639 173 174 until noncrdb_only 175 DataRow 176 ---- 177 {"Type":"DataRow","Values":[{"text":"hello "}]} 178 179 until crdb_only 180 DataRow 181 ---- 182 {"Type":"DataRow","Values":[{"text":"hello"}]} 183 184 until 185 ReadyForQuery 186 ---- 187 {"Type":"CommandComplete","CommandTag":"SELECT 1"} 188 {"Type":"ReadyForQuery","TxStatus":"I"} 189 190 # 80 = ASCII 'P' for Portal 191 send 192 Parse {"Name": "s", "Query": "SELECT b FROM tab3"} 193 Bind {"DestinationPortal": "p", "PreparedStatement": "s"} 194 Describe {"ObjectType": 80, "Name": "p"} 195 Execute {"Portal": "p"} 196 Sync 197 ---- 198 199 until 200 BindComplete 201 ---- 202 {"Type":"ParseComplete"} 203 {"Type":"BindComplete"} 204 205 # The following discrepancy is another bug. 206 # See: https://github.com/cockroachdb/cockroach/issues/49215 207 until noncrdb_only ignore_table_oids 208 RowDescription 209 ---- 210 {"Type":"RowDescription","Fields":[{"Name":"b","TableOID":0,"TableAttributeNumber":2,"DataTypeOID":1042,"DataTypeSize":-1,"TypeModifier":12,"Format":0}]} 211 212 until crdb_only 213 RowDescription 214 ---- 215 {"Type":"RowDescription","Fields":[{"Name":"b","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":1042,"DataTypeSize":-1,"TypeModifier":12,"Format":0}]} 216 217 # The following discrepancy is the first bug above. 218 # See: https://github.com/cockroachdb/cockroach/issues/49639 219 220 until noncrdb_only 221 DataRow 222 ---- 223 {"Type":"DataRow","Values":[{"text":"hello "}]} 224 225 until crdb_only 226 DataRow 227 ---- 228 {"Type":"DataRow","Values":[{"text":"hello"}]} 229 230 until 231 ReadyForQuery 232 ---- 233 {"Type":"CommandComplete","CommandTag":"SELECT 1"} 234 {"Type":"ReadyForQuery","TxStatus":"I"}