Driver (?mssql server) returns wrong column nullability in ResultSet

[DmitriyGod]

Driver version

12.8.1

SQL Server version

mcr.microsoft.com/mssql/server:2022-latest

Client Operating System

any

JAVA/JVM version

any

Table schema

create table table1(idi1 int not null, idf1 int not null); create table table2(idi2 int not null, idf2 int not null); create table table3(idi3 int not null, idf3 int not null); insert into table1 values(1,2); insert into table1 values(2,3); insert into table1 values(3,4);

Problem description

Knowledge of ResultSet column nullability is crucial for convenient developer experience (application stability too). I looked at the driver code associated with nullability, and it seems like driver reach nullability throw network protocol in "flag" field in TypeInfo. I found simple case, where driver fails with nullability:

var pStmt = connection.prepareStatement("""
    select idi1, idi2, idi3
    from table1 t1
        left join table2 t2 on t1.idf1 = t2.idi2
        inner join table3 t3 on t2.idf2 = t3.idi3
    """);
var metaData = pStmt.getMetaData();
Assertions.assertEquals(0, metaData.isNullable(1)); // return 0
Assertions.assertEquals(0, metaData.isNullable(2)); // return 1
Assertions.assertEquals(0, metaData.isNullable(3)); // return 0

So, Oracle give correct answer.

Expected behavior

driver should return 0 (NotNull) for column 2

Actual behavior

driver return 1 (Nullable) for column 2, which is untruth

Error message/stack trace

-

Any other details that can be helpful

-

JDBC trace logs

-

[machavan]

This can be reproduced on server side with below repro steps: (not an issue in the driver)

create view testnullable as 
select idi1, idi2, idi3
    from table1 t1
        left join table2 t2 on t1.idf1 = t2.idi2
        inner join table3 t3 on t2.idf2 = t3.idi3

select * From INFORMATION_SCHEMA.columns where table_name = 'testnullable'

idi1    NO
idi2    YES
idi3    NO

The field idi2 in being flagged as NULLABLE.

[DmitriyGod]

Ok, how we can escalate this to SQLServer?

[DmitriyGod]

And, I found yet one example: select avg(idi1) as idi1 from table1 t1

[machavan]

Just an update about an observation on the NULL behavior of idi2 column in the above select query (of reporting it as NULLABLE) is same in other databases such as Oracle, PostgreSQL, MySQL. (Note: PostgreSQL seem to report all three idi1, idi2 and idi3 as NULLABLE unlike the others which report only idi2 as NULLABLE)

[DmitriyGod]

It’s info are from drivers or from dbs?

[machavan]

It is from the dbs, using the same repro steps as used above

create view testnullable as select idi1, idi2, idi3 from table1 t1 left join table2 t2 on t1.idf1 = t2.idi2 inner join table3 t3 on t2.idf2 = t3.idi3

[DmitriyGod]

I’ve tested the same. Mariadb, Mysql returns not null, nullable, not null. Oracle returns not null, not null, not null.

[machavan]

It could be the case that the behavior varied across different versions of Oracle.

[lilgreenbird]

@DmitriyGod

This repo driver issues only the driver will only return values from the server. If you have issues with SQL server you can open a support case with Microsoft if you have a support contract and/or start a discussion in a public forum for server issues.

Please let us know if you have any more questions about the driver otherwise we will be closing this issue shortly.