Subject: [postgresql-interfaces/psqlodbc] issue #51: SQL_DESC_OCTET_LENGTH regression test difference

From: "df7cb (@df7cb)" <[email-masked]>
Date: Fri, 27 Sep 2024 11:45:11 +0000

Building 16.00.0006 against PG17.0, I'm getting this regression test diff:
```
*** ./expected/descrec.out	Fri Sep 27 11:28:28 2024
--- results/descrec.out	Fri Sep 27 11:42:56 2024
***************
*** 19,25 ****
  -- Column 3 --
  SQL_DESC_NAME: col3
  SQL_DESC_TYPE: 12
! SQL_DESC_OCTET_LENGTH: 40
  SQL_DESC_PRECISION: 0
  SQL_DESC_SCALE: 0
  SQL_DESC_NULLABLE: 0
--- 19,25 ----
  -- Column 3 --
  SQL_DESC_NAME: col3
  SQL_DESC_TYPE: 12
! SQL_DESC_OCTET_LENGTH: 10
  SQL_DESC_PRECISION: 0
  SQL_DESC_SCALE: 0
  SQL_DESC_NULLABLE: 0
```

Could you have a look?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 27 Sep 2024 14:01:25 +0000

hmmm ok, thanks, I'll look into this right now

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 27 Sep 2024 14:56:06 +0000

Interestingly enough it passes https://github.com/postgresql-interfaces/psqlodbc/pull/52/checks. I wonder if this is due to needing version 17?

---

From: "Hunaid2000 (@Hunaid2000)" <[email-masked]>
Date: Mon, 30 Sep 2024 07:01:04 +0000

Which OS?
I built psqlodbc (tag: REL-16_00_0006) with pg17 and pg16.4 binaries on Windows, all regressions are good.

---

From: "df7cb (@df7cb)" <[email-masked]>
Date: Fri, 04 Oct 2024 11:23:14 +0000

17.00.0002 still fails here (Debian unstable, PG 17.0) in the same way.

@davecramer from our chat last week, I thought you had an idea how to tackle it and didn't follow up here. Do you need any more details?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 04 Oct 2024 12:14:18 +0000

@df7cb I thought I sent you the query that the driver does. 
```
CREATE TEMPORARY TABLE desctable (col1 int4 not null, col2 numeric(4,2), col3 varchar(10) not null, col4 bigint not null); 
and then
select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype 
when 'd' then t.typbasetype else 0 end, t.typtypmod, 0, attidentity, c.relhassubclass from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 16832) 
inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) 
inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum;

Change 16832 to reloid of the table in pg_class
```

---

From: "df7cb (@df7cb)" <[email-masked]>
Date: Fri, 04 Oct 2024 14:57:29 +0000

Running that against 16 and 17 gives the same results (modulo different pg_temp_NN nspname and table oid).

```
CREATE TEMPORARY TABLE desctable (col1 int4 not null, col2 numeric(4,2), col3 varchar(10) not null, col4 bigint not null);
select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod,
a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype
when 'd' then t.typbasetype else 0 end, t.typtypmod, 0, attidentity, c.relhassubclass from
(((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid =
'desctable'::regclass)
inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid)
inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and
d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum;
  nspname   │  relname  │ attname │ atttypid │ typname │ attnum │ attlen │ atttypmod │ attnotnull │ relhasrules │ relkind │  oid  │ pg_get_expr │ case │ typtypmod │ ?column? │ attidentity │ relhassubclass 
────────────┼───────────┼─────────┼──────────┼─────────┼────────┼────────┼───────────┼────────────┼─────────────┼─────────┼───────┼─────────────┼──────┼───────────┼──────────┼─────────────┼────────────────
 pg_temp_50 │ desctable │ col1    │       23 │ int4    │      1 │      4 │        -1 │ t          │ f           │ r       │ 64095 │ ∅           │    0 │        -1 │        0 │             │ f
 pg_temp_50 │ desctable │ col2    │     1700 │ numeric │      2 │     -1 │    262150 │ f          │ f           │ r       │ 64095 │ ∅           │    0 │        -1 │        0 │             │ f
 pg_temp_50 │ desctable │ col3    │     1043 │ varchar │      3 │     -1 │        14 │ t          │ f           │ r       │ 64095 │ ∅           │    0 │        -1 │        0 │             │ f
 pg_temp_50 │ desctable │ col4    │       20 │ int8    │      4 │      8 │        -1 │ t          │ f           │ r       │ 64095 │ ∅           │    0 │        -1 │        0 │             │ f
(4 Zeilen)
```

There are already two expected output files in test/expected/descrec.out and test/expected/descrec_1.out with this difference:

```
--- test/expected/descrec_1.out	2024-10-04 16:48:37.623757902 +0200
+++ test/expected/descrec.out	2024-10-04 16:48:37.623757902 +0200
@@ -19,7 +19,7 @@
 -- Column 3 --
 SQL_DESC_NAME: col3
 SQL_DESC_TYPE: 12
-SQL_DESC_OCTET_LENGTH: 20
+SQL_DESC_OCTET_LENGTH: 40
 SQL_DESC_PRECISION: 0
 SQL_DESC_SCALE: 0
 SQL_DESC_NULLABLE: 0
```

I haven't yet figured out what that length means, but perhaps "10" is another valid output besides 20 and 40?

(I hate pg_regress for making it hard to put comments on _1.out alternate output files.)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 04 Oct 2024 20:51:01 +0000

So looking at the results of the query COL3 is 14, which is 20 decimal, no?

---

From: "Hunaid2000 (@Hunaid2000)" <[email-masked]>
Date: Sat, 05 Oct 2024 10:10:20 +0000

It basically depends on client encoding, I guess.
I get 20 on Windows and 40 on Ubuntu. The length is set in `pgtype_attr_buffer_length` function.
 
https://github.com/postgresql-interfaces/psqlodbc/blob/59728b929b54b3b230aead909895483b5b2f35a1/pgtypes.c#L1098-L1118

It depends on `coef = conn->mb_maxbyte_per_char;` which is set on the line `629` in this block.
https://github.com/postgresql-interfaces/psqlodbc/blob/59728b929b54b3b230aead909895483b5b2f35a1/connection.c#L615-L632

In case of Ubuntu:
```
postgres=# show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)
```
https://github.com/postgresql-interfaces/psqlodbc/blob/59728b929b54b3b230aead909895483b5b2f35a1/multibyte.c#L211-L236

which maps to 4 here: `pg_mb_maxlen(self->ccsc);`. As a result, we get 10 * 4 = 40.
For Windows, I believe its just `prec * WCLEN` so 10 * 2 = 20.

@df7cb In your case, I guess `pg_mb_maxlen` returns 1.

---

From: "df7cb (@df7cb)" <[email-masked]>
Date: Tue, 29 Oct 2024 14:37:29 +0000

Something is definitely wrong in this area. I've now added yet another descrec_2.out file to accept `10` as valid and the build passes on amd64, but it then fails on s390x (which is big endian):

```
14:33:57 *** ./expected/descrec.out	Wed Oct  2 11:15:31 2024
14:33:57 --- results/descrec.out	Tue Oct 29 14:33:57 2024
14:33:57 ***************
14:33:57 *** 2,33 ****
14:33:57   
14:33:57   -- Column 1 --
14:33:57   SQL_DESC_NAME: col1
14:33:57 ! SQL_DESC_TYPE: 4
14:33:57 ! SQL_DESC_OCTET_LENGTH: 0
14:33:57   SQL_DESC_PRECISION: 0
14:33:57   SQL_DESC_SCALE: 0
14:33:57   SQL_DESC_NULLABLE: 0
14:33:57   
14:33:57   -- Column 2 --
14:33:57   SQL_DESC_NAME: col2
14:33:57 ! SQL_DESC_TYPE: 2
14:33:57 ! SQL_DESC_OCTET_LENGTH: 6
14:33:57 ! SQL_DESC_PRECISION: 4
14:33:57 ! SQL_DESC_SCALE: 2
14:33:57 ! SQL_DESC_NULLABLE: 1
14:33:57   
14:33:57   -- Column 3 --
14:33:57   SQL_DESC_NAME: col3
14:33:57 ! SQL_DESC_TYPE: 12
14:33:57 ! SQL_DESC_OCTET_LENGTH: 40
14:33:57   SQL_DESC_PRECISION: 0
14:33:57   SQL_DESC_SCALE: 0
14:33:57   SQL_DESC_NULLABLE: 0
14:33:57   
14:33:57   -- Column 4 --
14:33:57   SQL_DESC_NAME: col4
14:33:57 ! SQL_DESC_TYPE: -5
14:33:57 ! SQL_DESC_OCTET_LENGTH: 8
14:33:57   SQL_DESC_PRECISION: 0
14:33:57   SQL_DESC_SCALE: 0
14:33:57   SQL_DESC_NULLABLE: 0
14:33:57 --- 2,33 ----
14:33:57   
14:33:57   -- Column 1 --
14:33:57   SQL_DESC_NAME: col1
14:33:57 ! SQL_DESC_TYPE: 0
14:33:57 ! SQL_DESC_OCTET_LENGTH: -67120400
14:33:57   SQL_DESC_PRECISION: 0
14:33:57   SQL_DESC_SCALE: 0
14:33:57   SQL_DESC_NULLABLE: 0
14:33:57   
14:33:57   -- Column 2 --
14:33:57   SQL_DESC_NAME: col2
14:33:57 ! SQL_DESC_TYPE: 0
14:33:57 ! SQL_DESC_OCTET_LENGTH: -67120400
14:33:57 ! SQL_DESC_PRECISION: 0
14:33:57 ! SQL_DESC_SCALE: 0
14:33:57 ! SQL_DESC_NULLABLE: 0
14:33:57   
14:33:57   -- Column 3 --
14:33:57   SQL_DESC_NAME: col3
14:33:57 ! SQL_DESC_TYPE: 0
14:33:57 ! SQL_DESC_OCTET_LENGTH: -67120400
14:33:57   SQL_DESC_PRECISION: 0
14:33:57   SQL_DESC_SCALE: 0
14:33:57   SQL_DESC_NULLABLE: 0
14:33:57   
14:33:57   -- Column 4 --
14:33:57   SQL_DESC_NAME: col4
14:33:57 ! SQL_DESC_TYPE: -1
14:33:57 ! SQL_DESC_OCTET_LENGTH: -67120400
14:33:57   SQL_DESC_PRECISION: 0
14:33:57   SQL_DESC_SCALE: 0
14:33:57   SQL_DESC_NULLABLE: 0
```

... which looks suspiciously like an endianess problem.

---

From: "df7cb (@df7cb)" <[email-masked]>
Date: Thu, 27 Feb 2025 16:08:08 +0000

Fwiw, I tried digging into this, but couldn't find the problem. I bet it is somewhere where the `.buflen` value gets assigned to one of these pointers, but I don't know which codepath is the bad one and they all look slightly different.

I'll just ignore this test altogether on Debian, but this is clearly a bug, both on big and (imho) on little endian.