BIGINT(20)/INT(20) type has wrong ColumnSize in SchemaTable

[erikmourits]

I would expect the columnsize of bigint and int to be 8 and 4 respectively.
But calling MySqlDataReader.GetSchemaTable() return ColumnSize of 20. Apparently this is a 'known' quirkiness in MySql (https://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20)

[erikmourits]

After some more investigation it seems that most (none?) of the ColumnSize returned by GetSchemaTable() are correct.

I would expect the ColumnSize to match the number of bytes specified here: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

For example: DateTime returns a ColumnSize of 19 instead of 8.

I am interpreting ColumnSize wrong? Or is this a bug?

[bgrainger]

It's currently intentional to show "display width" in this column.

using var connection = new MySqlConnection(cs.ConnectionString);
connection.Execute(@"drop table if exists test;
create table test(a int(5), b int(10), c bigint(15), d bigint(20));
insert into test values(1, 2, 3, 4);");
using var reader = connection.ExecuteReader("select * from test");

reader.GetSchemaTable will show 5, 10, 15, 20 as the ColumnSize, which matches the table definition in SQL.

However the numeric display width is now deprecated; https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html:

As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types.

Moreover, users who just use INT and BIGINT as the SQL data types may not even know that display width is a thing.

I would expect the ColumnSize to match the number of bytes specified here

I don't have expectation that ColumnSize has any relation to the number of bytes required for storage. It's certainly not used that way for TEXT, VARCHAR, DECIMAL, etc.

I am interpreting ColumnSize wrong?

I think so. Technically, it's the value returned by the MySQL Server for the column length field described in https://dev.mysql.com/doc/internals/en/com-query-response.html#packet-Protocol::ColumnDefinition. Given the changes in 8.0.17 (to deprecate display width), one could possibly consider this a Server bug, where its "column length" for integer columns is (now) computed incorrectly. (However, I suspect a lot of legacy software would break if it were changed, so getting it fixed feels unlikely?)

[erikmourits]

I am interpreting ColumnSize wrong?

I think so. Technically, it's the value returned by the MySQL Server for the column length field described in https://dev.mysql.com/doc/internals/en/com-query-response.html#packet-Protocol::ColumnDefinition. Given the changes in 8.0.17 (to deprecate display width), one could possibly consider this a Server bug, where its "column length" for integer columns is (now) computed incorrectly. (However, I suspect a lot of legacy software would break if it were changed, so getting it fixed feels unlikely?)

I was basing my assumption on this: https://docs.microsoft.com/en-us/dotnet/api/system.data.datatablereader.getschematable?view=net-5.0. Which states:
"ColumnSize -1 if the ColumnSize (or MaxLength) property of the DataColumn cannot be determined or is not relevant; otherwise, 0 or a positive integer that contains the MaxLength value.".
The DataColumn.MaxLength property is: "The maximum length of the column in characters."

Because this is an implementation of DataTableReader.GetSchemaTable() it shouldn't matter which database I connect to. I should expect the same schematable the same table definition for mysql, posgresql or mssql.

[bgrainger]

The maximum length of the column in characters.

Well... technically... the size of a BIGINT(20) column in characters is 20. 😀

But besides that, characters != bytes, so I'm not understanding the relevance to a numeric column type yet.

[bgrainger]

Because this is an implementation of DataTableReader.GetSchemaTable() it shouldn't matter which database I connect to. I should expect the same schematable the same table definition for mysql, posgresql or mssql.

I haven't checked those databases yet, but it sounds like something worth testing so I've opened an issue for it: mysql-net/AdoNetApiTest#223.

[erikmourits]

Because this is an implementation of DataTableReader.GetSchemaTable() it shouldn't matter which database I connect to. I should expect the same schematable the same table definition for mysql, posgresql or mssql.

I haven't checked those databases yet, but it sounds like something worth testing so I've opened an issue for it: mysql-net/AdoNetApiTest#223.

Awesome! Thanks for your responses.

[bgrainger]

MySqlConnector returns the "length" values returned by the MySQL Server; this is by design.