oid_test.go 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460
  1. package pgserver
  2. import (
  3. "bytes"
  4. "encoding/binary"
  5. "net"
  6. "strconv"
  7. "testing"
  8. "github.com/danfragoso/pizzasql-next/pkg/executor"
  9. "github.com/danfragoso/pizzasql-next/pkg/parser"
  10. )
  11. func TestGetOIDForTypeDatetime(t *testing.T) {
  12. c := &Connection{}
  13. cases := map[string]int32{
  14. "DATETIME": 1184,
  15. "TIMESTAMP": 1184,
  16. "DATE": 1082,
  17. // Integer types narrower than the int64 carrier report int8 because the
  18. // engine does not range-check stored values. INT2 is the executor's
  19. // exact-width spelling for a constant SMALLINT cast that fits int2.
  20. "SMALLINT": 20,
  21. "INT2": 21,
  22. "TINYINT": 20,
  23. "MEDIUMINT": 20,
  24. "INTEGER": 23,
  25. "INT": 23,
  26. "INT4": 23,
  27. "BIGINT": 20,
  28. "INT8": 20,
  29. // NUMERIC/DECIMAL carry float64 values, so they report float8.
  30. "NUMERIC": 701,
  31. "DECIMAL": 701,
  32. "REAL": 700,
  33. "FLOAT": 700,
  34. "DOUBLE": 701,
  35. "TEXT": 25,
  36. "VARCHAR": 25,
  37. "CHAR": 25,
  38. "UUID": 25,
  39. "BOOLEAN": 16,
  40. "BOOL": 16,
  41. "BLOB": 17,
  42. }
  43. for name, want := range cases {
  44. if got := c.getOIDForType(name); got != want {
  45. t.Errorf("getOIDForType(%q) = %d, want %d", name, got, want)
  46. }
  47. }
  48. }
  49. func TestSendRowDescriptionTimestampOID(t *testing.T) {
  50. c, cc := newCountingTestConnection(t)
  51. if err := c.sendRowDescription([]string{"created_at", "id"}, []string{"TIMESTAMP", "BIGINT"}); err != nil {
  52. t.Fatal(err)
  53. }
  54. if err := c.sendReadyForQuery(); err != nil {
  55. t.Fatal(err)
  56. }
  57. msgs := readAllMessages(t, cc.bytes())
  58. rd := findRowDescription(t, msgs)
  59. oids := rowDescriptionOIDs(t, rd.Data)
  60. if len(oids) != 2 {
  61. t.Fatalf("got %d columns, want 2", len(oids))
  62. }
  63. if oids[0] != 1184 {
  64. t.Errorf("timestamp OID = %d, want 1184", oids[0])
  65. }
  66. if oids[1] != 20 {
  67. t.Errorf("bigint OID = %d, want 20", oids[1])
  68. }
  69. }
  70. func TestSendResultEmptyProjectionSendsRowDescription(t *testing.T) {
  71. c, cc := newCountingTestConnection(t)
  72. result := executor.NewResult("SELECT")
  73. result.AddColumnWithType("id", "BIGINT")
  74. result.AddColumnWithType("created_at", "DATETIME")
  75. if err := c.sendResult(result, &parser.SelectStmt{}, result.ColumnTypes, nil); err != nil {
  76. t.Fatal(err)
  77. }
  78. if err := c.sendReadyForQuery(); err != nil {
  79. t.Fatal(err)
  80. }
  81. msgs := readAllMessages(t, cc.bytes())
  82. rd := findRowDescription(t, msgs)
  83. oids := rowDescriptionOIDs(t, rd.Data)
  84. if len(oids) != 2 || oids[0] != 20 || oids[1] != 1184 {
  85. t.Fatalf("oids = %v, want [20 1184]", oids)
  86. }
  87. }
  88. func findRowDescription(t *testing.T, msgs []*Message) *Message {
  89. t.Helper()
  90. for _, m := range msgs {
  91. if m.Type == MsgRowDescription {
  92. return m
  93. }
  94. }
  95. t.Fatal("no RowDescription message found")
  96. return nil
  97. }
  98. func rowDescriptionOIDs(t *testing.T, data []byte) []int32 {
  99. t.Helper()
  100. if len(data) < 2 {
  101. t.Fatalf("row description too short")
  102. }
  103. count := int(binary.BigEndian.Uint16(data[:2]))
  104. pos := 2
  105. oids := make([]int32, 0, count)
  106. for i := 0; i < count; i++ {
  107. end := bytes.IndexByte(data[pos:], 0)
  108. if end < 0 {
  109. t.Fatalf("unterminated column name")
  110. }
  111. pos += end + 1 // name
  112. pos += 4 // table OID
  113. pos += 2 // column attribute number
  114. if pos+4 > len(data) {
  115. t.Fatalf("truncated row description")
  116. }
  117. oids = append(oids, int32(binary.BigEndian.Uint32(data[pos:pos+4])))
  118. pos += 4 // type OID
  119. pos += 2 // type size
  120. pos += 4 // type modifier
  121. pos += 2 // format code
  122. }
  123. return oids
  124. }
  125. func TestGetTypeSizeForType(t *testing.T) {
  126. c := &Connection{}
  127. cases := map[string]int16{
  128. "SMALLINT": 8,
  129. "TINYINT": 8,
  130. "MEDIUMINT": 8,
  131. "INTEGER": 4,
  132. "BIGINT": 8,
  133. "INT2": 2,
  134. "REAL": 4,
  135. "DOUBLE": 8,
  136. "NUMERIC": 8,
  137. "DECIMAL": 8,
  138. "BOOLEAN": 1,
  139. "DATE": 4,
  140. "TIMESTAMP": 8,
  141. "TEXT": -1,
  142. "BLOB": -1,
  143. }
  144. for name, want := range cases {
  145. if got := c.getTypeSizeForType(name); got != want {
  146. t.Errorf("getTypeSizeForType(%q) = %d, want %d", name, got, want)
  147. }
  148. }
  149. }
  150. // projectionOIDs runs sql and returns the type OIDs in its RowDescription.
  151. func projectionOIDs(t *testing.T, c *Connection, client net.Conn, sql string) []int32 {
  152. t.Helper()
  153. msgs := resultMessages(runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(sql), 0)}))
  154. return rowDescriptionOIDs(t, findRowDescription(t, msgs).Data)
  155. }
  156. // TestRowDescriptionProjectionOIDs asserts the wire OIDs for column
  157. // projections, expression projections, and empty result sets.
  158. func TestRowDescriptionProjectionOIDs(t *testing.T) {
  159. c, client := newDataConnection(t)
  160. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  161. `CREATE TABLE typed (
  162. i INTEGER PRIMARY KEY,
  163. big BIGINT,
  164. small SMALLINT,
  165. price NUMERIC,
  166. flag BOOLEAN,
  167. name TEXT,
  168. data BLOB,
  169. created_at TIMESTAMP,
  170. day DATE
  171. )`), 0)})
  172. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  173. `INSERT INTO typed VALUES (1, 9000000000, 7, 19.5, TRUE, 'x', X'00', '2024-05-06 07:08:09', '2024-05-06')`), 0)})
  174. cases := []struct {
  175. name string
  176. sql string
  177. want []int32
  178. }{
  179. {
  180. name: "columns",
  181. sql: "SELECT i, big, small, price, flag, name, data, created_at, day FROM typed",
  182. want: []int32{23, 20, 20, 701, 16, 25, 17, 1184, 1082},
  183. },
  184. {name: "count star", sql: "SELECT count(*) FROM typed", want: []int32{20}},
  185. {name: "sum integer", sql: "SELECT sum(i) FROM typed", want: []int32{20}},
  186. {name: "sum numeric", sql: "SELECT sum(price) FROM typed", want: []int32{701}},
  187. {name: "avg integer", sql: "SELECT avg(i) FROM typed", want: []int32{701}},
  188. {name: "comparison", sql: "SELECT i = 1 FROM typed", want: []int32{16}},
  189. {name: "string literal", sql: "SELECT 'text'", want: []int32{25}},
  190. {name: "integer literal", sql: "SELECT 42", want: []int32{23}},
  191. {name: "wide integer literal", sql: "SELECT 4294967296", want: []int32{20}},
  192. {name: "numeric literal", sql: "SELECT 1.5", want: []int32{701}},
  193. {name: "cast", sql: "SELECT CAST(i AS BIGINT) FROM typed", want: []int32{20}},
  194. {name: "narrow integer cast", sql: "SELECT CAST(i AS SMALLINT) FROM typed", want: []int32{20}},
  195. {
  196. name: "empty result",
  197. sql: "SELECT i, created_at, i + 1 FROM typed WHERE i = 999",
  198. want: []int32{23, 1184, 20},
  199. },
  200. {
  201. name: "aggregate empty result",
  202. sql: "SELECT count(*), max(created_at) FROM typed WHERE i = 999",
  203. want: []int32{20, 1184},
  204. },
  205. }
  206. for _, tc := range cases {
  207. t.Run(tc.name, func(t *testing.T) {
  208. got := projectionOIDs(t, c, client, tc.sql)
  209. if len(got) != len(tc.want) {
  210. t.Fatalf("%s: OIDs = %v, want %v", tc.sql, got, tc.want)
  211. }
  212. for i := range tc.want {
  213. if got[i] != tc.want[i] {
  214. t.Fatalf("%s: OIDs = %v, want %v", tc.sql, got, tc.want)
  215. }
  216. }
  217. })
  218. }
  219. }
  220. // rowDescriptionColumns decodes the column names from a RowDescription message.
  221. func rowDescriptionColumns(t *testing.T, data []byte) []string {
  222. t.Helper()
  223. if len(data) < 2 {
  224. t.Fatalf("row description too short")
  225. }
  226. count := int(binary.BigEndian.Uint16(data[:2]))
  227. pos := 2
  228. names := make([]string, 0, count)
  229. for i := 0; i < count; i++ {
  230. end := bytes.IndexByte(data[pos:], 0)
  231. if end < 0 {
  232. t.Fatalf("unterminated column name")
  233. }
  234. names = append(names, string(data[pos:pos+end]))
  235. pos += end + 1 // name
  236. pos += 4 // table OID
  237. pos += 2 // column attribute number
  238. pos += 4 // type OID
  239. pos += 2 // type size
  240. pos += 4 // type modifier
  241. pos += 2 // format code
  242. }
  243. return names
  244. }
  245. // countMessageType counts messages of a type in a query response.
  246. func countMessageType(msgs []*Message, msgType byte) int {
  247. count := 0
  248. for _, msg := range msgs {
  249. if msg.Type == msgType {
  250. count++
  251. }
  252. }
  253. return count
  254. }
  255. // TestFloatCarrierOIDsMatchValues asserts that projections carried as float64
  256. // are advertised as float8 and that their text values parse as float8. NUMERIC
  257. // (1700) is never advertised because the engine has no exact decimal carrier.
  258. func TestFloatCarrierOIDsMatchValues(t *testing.T) {
  259. c, client := newDataConnection(t)
  260. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  261. `CREATE TABLE floats (i INTEGER PRIMARY KEY, price NUMERIC, amount DECIMAL, score DOUBLE)`), 0)})
  262. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  263. `INSERT INTO floats VALUES (1, 19.5, 3.25, 0.1)`), 0)})
  264. cases := []struct {
  265. name string
  266. sql string
  267. }{
  268. {name: "numeric column", sql: "SELECT price FROM floats"},
  269. {name: "decimal column", sql: "SELECT amount FROM floats"},
  270. {name: "double column", sql: "SELECT score FROM floats"},
  271. {name: "avg integer", sql: "SELECT avg(i) FROM floats"},
  272. {name: "avg numeric", sql: "SELECT avg(price) FROM floats"},
  273. {name: "sum numeric", sql: "SELECT sum(price) FROM floats"},
  274. {name: "decimal literal", sql: "SELECT 1.5"},
  275. {name: "float arithmetic", sql: "SELECT price + 0.5 FROM floats"},
  276. }
  277. for _, tc := range cases {
  278. t.Run(tc.name, func(t *testing.T) {
  279. msgs := resultMessages(runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(tc.sql), 0)}))
  280. oids := rowDescriptionOIDs(t, findRowDescription(t, msgs).Data)
  281. if len(oids) != 1 {
  282. t.Fatalf("%s: OIDs = %v, want one column", tc.sql, oids)
  283. }
  284. if oids[0] == 1700 {
  285. t.Fatalf("%s: OID = 1700 (numeric) for a float64 carrier", tc.sql)
  286. }
  287. if oids[0] != 701 {
  288. t.Fatalf("%s: OID = %d, want 701 (float8)", tc.sql, oids[0])
  289. }
  290. values := dataRowValues(t, msgs[1])
  291. if len(values) != 1 {
  292. t.Fatalf("%s: values = %v, want one value", tc.sql, values)
  293. }
  294. if _, err := strconv.ParseFloat(values[0], 64); err != nil {
  295. t.Fatalf("%s: float8 value %q does not parse: %v", tc.sql, values[0], err)
  296. }
  297. })
  298. }
  299. }
  300. // TestNarrowIntegerOIDsUseCarrierType pins the chosen narrow-integer behavior:
  301. // the engine carries int64 without range checks, so SMALLINT/TINYINT/MEDIUMINT
  302. // report int8 (20) rather than a width a stored value may not fit.
  303. func TestNarrowIntegerOIDsUseCarrierType(t *testing.T) {
  304. c, client := newDataConnection(t)
  305. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  306. `CREATE TABLE narrow (id INTEGER PRIMARY KEY, small SMALLINT, tiny TINYINT, medium MEDIUMINT)`), 0)})
  307. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  308. `INSERT INTO narrow VALUES (1, 40000, 200, 9000000)`), 0)})
  309. cases := []struct {
  310. name string
  311. sql string
  312. value string
  313. }{
  314. {name: "smallint column", sql: "SELECT small FROM narrow", value: "40000"},
  315. {name: "tinyint column", sql: "SELECT tiny FROM narrow", value: "200"},
  316. {name: "mediumint column", sql: "SELECT medium FROM narrow", value: "9000000"},
  317. {name: "cast smallint", sql: "SELECT CAST(small AS SMALLINT) FROM narrow", value: "40000"},
  318. {name: "cast integer", sql: "SELECT CAST(small AS INTEGER) FROM narrow", value: "40000"},
  319. }
  320. for _, tc := range cases {
  321. t.Run(tc.name, func(t *testing.T) {
  322. msgs := resultMessages(runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(tc.sql), 0)}))
  323. oids := rowDescriptionOIDs(t, findRowDescription(t, msgs).Data)
  324. if len(oids) != 1 || oids[0] != 20 {
  325. t.Fatalf("%s: OIDs = %v, want [20] (int8 carrier)", tc.sql, oids)
  326. }
  327. values := dataRowValues(t, msgs[1])
  328. if len(values) != 1 || values[0] != tc.value {
  329. t.Fatalf("%s: values = %v, want [%s]", tc.sql, values, tc.value)
  330. }
  331. if _, err := strconv.ParseInt(values[0], 10, 64); err != nil {
  332. t.Fatalf("%s: int8 value %q does not parse: %v", tc.sql, values[0], err)
  333. }
  334. })
  335. }
  336. }
  337. // TestEmptyResultWildcardMatchesNames guards the constant-false WHERE path:
  338. // wildcard projections must expand names and types into the same columns.
  339. func TestEmptyResultWildcardMatchesNames(t *testing.T) {
  340. c, client := newDataConnection(t)
  341. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  342. `CREATE TABLE star_t (id INTEGER PRIMARY KEY, created_at TIMESTAMP, name TEXT)`), 0)})
  343. runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(
  344. `CREATE TABLE star_b (b_id BIGINT PRIMARY KEY, score DOUBLE)`), 0)})
  345. cases := []struct {
  346. name string
  347. sql string
  348. wantNames []string
  349. wantOIDs []int32
  350. }{
  351. {
  352. name: "qualified star",
  353. sql: "SELECT star_t.* FROM star_t WHERE 1 = 0",
  354. wantNames: []string{"id", "created_at", "name"},
  355. wantOIDs: []int32{23, 1184, 25},
  356. },
  357. {
  358. name: "aliased qualified star",
  359. sql: "SELECT s.* FROM star_t AS s WHERE 1 = 0",
  360. wantNames: []string{"id", "created_at", "name"},
  361. wantOIDs: []int32{23, 1184, 25},
  362. },
  363. {
  364. name: "star",
  365. sql: "SELECT * FROM star_t WHERE 1 = 0",
  366. wantNames: []string{"id", "created_at", "name"},
  367. wantOIDs: []int32{23, 1184, 25},
  368. },
  369. {
  370. name: "column and expression",
  371. sql: "SELECT star_t.name, star_t.id + 1 FROM star_t WHERE 1 = 0",
  372. wantNames: []string{"name", "column2"},
  373. wantOIDs: []int32{25, 20},
  374. },
  375. {
  376. name: "join star",
  377. sql: "SELECT * FROM star_t JOIN star_b ON star_t.id = star_b.b_id WHERE 1 = 0",
  378. wantNames: []string{"id", "created_at", "name", "b_id", "score"},
  379. wantOIDs: []int32{23, 1184, 25, 20, 701},
  380. },
  381. }
  382. for _, tc := range cases {
  383. t.Run(tc.name, func(t *testing.T) {
  384. msgs := resultMessages(runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(tc.sql), 0)}))
  385. rd := findRowDescription(t, msgs)
  386. names := rowDescriptionColumns(t, rd.Data)
  387. oids := rowDescriptionOIDs(t, rd.Data)
  388. if len(names) != len(tc.wantNames) {
  389. t.Fatalf("%s: names = %v, want %v", tc.sql, names, tc.wantNames)
  390. }
  391. for i := range tc.wantNames {
  392. if names[i] != tc.wantNames[i] {
  393. t.Fatalf("%s: names = %v, want %v", tc.sql, names, tc.wantNames)
  394. }
  395. }
  396. if len(oids) != len(tc.wantOIDs) {
  397. t.Fatalf("%s: OIDs = %v, want %v", tc.sql, oids, tc.wantOIDs)
  398. }
  399. for i := range tc.wantOIDs {
  400. if oids[i] != tc.wantOIDs[i] {
  401. t.Fatalf("%s: OIDs = %v, want %v", tc.sql, oids, tc.wantOIDs)
  402. }
  403. }
  404. if rows := countMessageType(msgs, MsgDataRow); rows != 0 {
  405. t.Fatalf("%s: got %d data rows, want 0", tc.sql, rows)
  406. }
  407. })
  408. }
  409. }
  410. func TestGetOIDForTypeArrays(t *testing.T) {
  411. c := &Connection{}
  412. cases := map[string]int32{
  413. "TEXT[]": 1009,
  414. "VARCHAR[]": 1015,
  415. "VARCHAR(10)[]": 1015,
  416. "CHAR[]": 1009,
  417. "INTEGER[]": 1007,
  418. "INT[]": 1007,
  419. "BIGINT[]": 1016,
  420. "SMALLINT[]": 1016,
  421. "BOOLEAN[]": 1000,
  422. "BOOL[]": 1000,
  423. "REAL[]": 1021,
  424. "DOUBLE[]": 1022,
  425. "DOUBLE PRECISION[]": 1022,
  426. "NUMERIC[]": 1231,
  427. }
  428. for name, want := range cases {
  429. if got := c.getOIDForType(name); got != want {
  430. t.Errorf("getOIDForType(%q) = %d, want %d", name, got, want)
  431. }
  432. if size := c.getTypeSizeForType(name); size != -1 {
  433. t.Errorf("getTypeSizeForType(%q) = %d, want -1", name, size)
  434. }
  435. }
  436. }