SqlServer TINYINT incorrect results

[tangjiangling]

We mapped Trino TINYINT to SqlServer TINYINT, but I found that the mapping relationship is wrong, why?

• In Trino, the range of TINYINT is [-128, 127]
• In SqlServer, the range of TINYINT is [0, 255]

So we can't read or write TINYINT values of [128, 255] through Trino.

Assuming I have a table test_table (row_id integer, data tinyint) in SqlServer, and the following data in the table:

1 127
2 128

Then, if I read it through Trino, I will get the following result:

1 127
2 -128 <- error value

Since Trino's TINYINT supports negative numbers, what happens if I write a negative number to SqlServer via Trino? (We can't write negative numbers via SqlServer JDBC Statement)

INSERT INTO test_table VALUES (3, -1)

query via SqlServer JDBC:

1 127
2 128
3 255 <- error value

query via Trino:

1 127
2 -128
3 -1 <- error value

[tangjiangling]

Background

I found this bug while fixing this issue, so I filed an issue to track the problem and I will send a PR to fix it.

[findepi]

In SqlServer, the range of TINYINT is [0, 255]

so it's effectively "unsigned tinyint" and should be mapped to smallint.
Note that we dom't have unsigned numeric types.

Since Trino's TINYINT supports negative numbers, what happens if I write a negative number to SqlServer via Trino?

Failure. We don't need to do validation, since SQL Server will do it (right?)

This is how "unsigned tinyint" is handled in MySQL connector

trino/plugin/trino-mysql/src/main/java/io/trino/plugin/mysql/MySqlClient.java

Lines 674 to 675 in e72a9bc

	if (typeName.equalsIgnoreCase("tinyint unsigned")) {
	return Optional.of(smallintColumnMapping());

[tangjiangling]

Failure. We don't need to do validation, since SQL Server will do it (right?)

No, I have confirmed that if we use Statement, SqlServer will check if the value range is correct, but with PreparedStatement, SqlServer does not check the value range, and in Trino we are using PreparedStatement, so we should have to check the value range in Trino.

This is how "unsigned tinyint" is handled in MySQL connector

Got it, thank you for your guidance.

[tangjiangling]

No, I have confirmed that if we use Statement, SqlServer will check if the value range is correct, but with PreparedStatement, SqlServer does not check the value range, and in Trino we are using PreparedStatement, so we should have to check the value range in Trino.

This conclusion is wrong, SQL Server will check the range of values.