SQL_DESC_OCTET_LENGTH regression test difference

[df7cb]

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?

[davecramer]

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

[davecramer]

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

[Hunaid2000]

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

[df7cb]

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?

[davecramer]

@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

[df7cb]

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.)

[davecramer]

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

[Hunaid2000]

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.

psqlodbc/pgtypes.c

Lines 1098 to 1118 in 59728b9

	case PG_TYPE_VARCHAR:
	case PG_TYPE_BPCHAR:
	{
	int coef = 1;
	Int4 prec = pgtype_attr_column_size(conn, type, atttypmod, adtsize_or_longestlen, handle_unknown_size_as), maxvarc;
	if (SQL_NO_TOTAL == prec)
	return prec;
	#ifdef UNICODE_SUPPORT
	if (CC_is_in_unicode_driver(conn))
	return prec * WCLEN;
	#endif /* UNICODE_SUPPORT */
	coef = conn->mb_maxbyte_per_char;
	if (coef < 2 && (conn->connInfo).lf_conversion)
	/* CR -> CR/LF */
	coef = 2;
	if (coef == 1)
	return prec;
	maxvarc = conn->connInfo.drivers.max_varchar_size;
	if (prec <= maxvarc && prec * coef > maxvarc)
	return maxvarc;
	return coef * prec;

It depends on coef = conn->mb_maxbyte_per_char; which is set on the line 629 in this block.

psqlodbc/connection.c

Lines 615 to 632 in 59728b9

	CC_set_client_encoding(ConnectionClass *self, const char * encoding)
	{
	char *currenc = self->original_client_encoding;
	if (encoding)
	{
	self->original_client_encoding = strdup(encoding);
	self->ccsc = pg_CS_code(encoding);
	}
	else
	{
	self->original_client_encoding = NULL;
	self->ccsc = SQL_ASCII;
	}
	self->mb_maxbyte_per_char = pg_mb_maxlen(self->ccsc);
	if (currenc)
	free(currenc);
	}

In case of Ubuntu:

postgres=# show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

psqlodbc/multibyte.c

Lines 211 to 236 in 59728b9

	int
	pg_mb_maxlen(int characterset_code)
	{
	switch (characterset_code)
	{
	case UTF8:
	return 4;
	case EUC_TW:
	return 4;
	case EUC_JIS_2004:
	case EUC_JP:
	case GB18030:
	return 3;
	case SHIFT_JIS_2004:
	case SJIS:
	case BIG5:
	case GBK:
	case UHC:
	case EUC_CN:
	case EUC_KR:
	case JOHAB:
	return 2;
	default:
	return 1;
	}
	}

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.

[df7cb]

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.

[df7cb]

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.