MySQL Bugs: #121192: Connector/ODBC returns incorrect DATA_TYPE and COLUMN

Bug #121192	Connector/ODBC returns incorrect DATA_TYPE and COLUMN_SIZE for BIT(n) columns
Submitted:	31 Aug 8:55
Reporter:	Tomohiko Kondo	Email Updates:
Status:	Open	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	8.4.0, 9.7.0, 26.7.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Description:
Connector/ODBC returns incorrect metadata for MySQL BIT(n) columns in recent versions.

The issue is reproducible with Connector/ODBC 9.7 and 26.7.1, while Connector/ODBC 5.3 returns the expected values.

The incorrect values are returned by SQLColumns() and affect both DATA_TYPE and COLUMN_SIZE.

For example:

BIT(2) is reported as SQL_BIT instead of SQL_BINARY.
BIT(5) is reported with COLUMN_SIZE = 2 instead of 1.
BIT(64) is reported with COLUMN_SIZE = 9 instead of 8.

The MySQL Server itself returns the correct NUMERIC_PRECISION values from INFORMATION_SCHEMA.COLUMNS.

How to repeat:
MySQL Server: 9.7.0
OS: Windows
Test tool: Microsoft ODBC Test (odbcte32.exe)
Connector/ODBC versions tested:
5.3 Unicode: expected result
9.7 ANSI / Unicode: incorrect result
26.7.1 Unicode: incorrect result

Steps to reproduce:

Create a table containing BIT columns.

Example:

CREATE TABLE bittest (
bit_col BIT,
bit1 BIT(1),
bit2 BIT(2),
bit3 BIT(3),
bit4 BIT(4),
bit5 BIT(5),
bit8 BIT(8),
bit9 BIT(9),
bit12 BIT(12),
bit13 BIT(13),
bit16 BIT(16),
bit64 BIT(64)
);

Confirm the server-side metadata.

Run:

SELECT
COLUMN_NAME,
DATA_TYPE,
COLUMN_TYPE,
NUMERIC_PRECISION,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '<database_name>'
AND TABLE_NAME = 'bittest'
ORDER BY ORDINAL_POSITION;

The server returns the expected values, for example:

BIT(2) -> NUMERIC_PRECISION = 2
BIT(5) -> NUMERIC_PRECISION = 5
BIT(8) -> NUMERIC_PRECISION = 8
BIT(9) -> NUMERIC_PRECISION = 9
BIT(64) -> NUMERIC_PRECISION = 64

Connect to the same database using odbcte32.exe and execute SQLColumns() for table "bittest".

Expected result:

BIT(1):
DATA_TYPE = SQL_BIT (-7)
COLUMN_SIZE = 1
BUFFER_LENGTH = 1

BIT(2) - BIT(8):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 1
BUFFER_LENGTH = 1

BIT(9) - BIT(16):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 2
BUFFER_LENGTH = 2

BIT(17) - BIT(24):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 3
BUFFER_LENGTH = 3

In general:

COLUMN_SIZE = ceil(n / 8)

for BIT(n), except BIT(1), which is reported as SQL_BIT.

This is also the behavior observed with Connector/ODBC 5.3.

Actual result with Connector/ODBC 9.7 and 26.7.1:

BIT(1) - BIT(4):
DATA_TYPE = SQL_BIT (-7)
COLUMN_SIZE = 1

BIT(5) - BIT(12):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 2

BIT(13) - BIT(20):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 3

BIT(21) - BIT(28):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 4

...

BIT(61) - BIT(64):
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 9

The returned COLUMN_SIZE therefore behaves approximately as:

ceil((n + 4) / 8)

instead of:

ceil(n / 8)

Representative comparison:

BIT(2)

Connector/ODBC 5.3:
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 1
BUFFER_LENGTH = 1

Connector/ODBC 9.7 / 26.7.1:
DATA_TYPE = SQL_BIT (-7)
COLUMN_SIZE = 1
BUFFER_LENGTH = 1

BIT(5)

Connector/ODBC 5.3:
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 1
BUFFER_LENGTH = 1

Connector/ODBC 9.7 / 26.7.1:
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 2
BUFFER_LENGTH = 2

BIT(64)

Connector/ODBC 5.3:
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 8
BUFFER_LENGTH = 8

Connector/ODBC 9.7 / 26.7.1:
DATA_TYPE = SQL_BINARY (-2)
COLUMN_SIZE = 9
BUFFER_LENGTH = 9

Additional information:

The issue is independent of the MySQL Server metadata.

INFORMATION_SCHEMA.COLUMNS returns the correct NUMERIC_PRECISION for all BIT(n) columns.

For example:

BIT(1) -> 1
BIT(2) -> 2
BIT(5) -> 5
BIT(8) -> 8
BIT(9) -> 9
BIT(64) -> 64

Therefore, the incorrect metadata appears to be generated inside Connector/ODBC.

This behavior also causes an application compatibility issue.

Our application uses the ODBC metadata to determine how BIT columns should be bound.

With Connector/ODBC 5.3, BIT(2) is reported as SQL_BINARY and binary values can be written correctly.

With Connector/ODBC 9.7 and 26.7.1, BIT(2) is reported as SQL_BIT, which changes the parameter binding behavior and results in incorrect values being written.

This appears to be a regression from Connector/ODBC 5.3 behavior.

Connector/ODBC 8.0.33 release notes also state that multi-bit BIT columns should continue to be reported as SQL_BINARY for backward compatibility, so reporting BIT(2) through BIT(4) as SQL_BIT appears inconsistent with that behavior.

Please confirm whether this is a Connector/ODBC bug and whether a fix is planned.