Teradata JDBC Driver returns the wrong schema / column nullability

Connectivity
Enthusiast

Re: Teradata JDBC Driver returns the wrong schema / column nullability

And as a follow-up question, could the driver use isNullable=N + mayBeNull=Y to instead return columnNullableUnknown?

Teradata Employee

Re: Teradata JDBC Driver returns the wrong schema / column nullability

isNullable refers to the storage, while mayBeNull refers to the result set column.

As I recall, OLEDB is the only database interface that exposes the concept of mayBeNull. The concept of mayBeNull, referring to the result set column, is not present in the JDBC API.

The javadoc for the ResultSetMetaData.isNullable method is fairly clear that the method returns information about the underlying storage column, not the result set column, thus it is more appropriate for the Teradata JDBC Driver to base the return value of the ResultSetMetaData.isNullable method on the isNullable value received from the Teradata Database.

Having said that, we'd like to help you get your application working as desired. Please provide some more detail about why your application needs the nullability information, and what your application would do with that information. Then we can perhaps suggest a workaround, or compose a new feature request.

Enthusiast

Re: Teradata JDBC Driver returns the wrong schema / column nullability

> The javadoc for the ResultSetMetaData.isNullable method is fairly clear that the method returns information about the underlying storage column ...

I'm really not so sure about this interpretation.  Could you point me to the doc that lead you to this?  I read "Indicates the nullability of values in the designated column" as referring to the column in the result set itself not the storage column.

See https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html#isNullable-int-

> Please provide some more detail about why your application needs the nullability information

Apache Spark uses the value returned from ResultSetMetaData.isNullable to generate Java code to e.g. read the value of the integer in a field.  If the column could contain nulls, it will first check whether the value is null before doing the read.  If the column is expected to never contain nulls, it doesn't need to do this check.

> Then we can perhaps suggest a workaround, or compose a new feature request.

I'm really starting to think that the logic should be changed so that ResultSetMetaData.isNullable actually returns the value of OLEDB's mayBeNull if (as you've said) it refers whether the result set column can contain null values.  Is this going to be a difficult change to make?

Teradata Employee

Re: Teradata JDBC Driver returns the wrong schema / column nullability

The Teradata JDBC Driver's ResultSetMetaData.isNullable method has worked the way it does for several years, and we need to preserve its current behavior for applications that expect it.

We can consider providing a new connection parameter such as MAYBENULL=ON that would enable you to choose a different behavior for the ResultSetMetaData.isNullable method. The default would be MAYBENULL=OFF to yield the existing old behavior.

Enthusiast

Re: Teradata JDBC Driver returns the wrong schema / column nullability

I can see why it would be important to preserve existing behaviour, and I think the approach of using a connection parameter as you've described would work well.

Enthusiast

Re: Teradata JDBC Driver returns the wrong schema / column nullability

Tom, what would be the next steps for this?  Is there another system I should be putting this request into?  How would I track when it is likely to be resolved and a patch provided?  Thanks!

Teradata Employee

Re: Teradata JDBC Driver returns the wrong schema / column nullability

To cover this feature request, I created JDBC RFC 183490 "Connection parameter MAYBENULL for ResultSetMetaData.isNullable variant behavior".

If you're a customer, you can use Teradata at Your Service (TaYS) to create an incident requesting the new feature and mentioning the JDBC RFC number. Doing that will enable us to classify the RFC as a customer RFC and to prioritize it higher.

Regards,

--Tom

Enthusiast

Re: Teradata JDBC Driver returns the wrong schema / column nullability

Thanks Tom!  I'll see about getting our client to do as you've suggested.

Enthusiast

Re: Teradata JDBC Driver returns the wrong schema / column nullability

Hi Tom,

Our client has raised incident RECH34MET on T@YS.  What would typically be the next steps to getting a change request actioned & released?

Thanks!

Teradata Employee

Re: Teradata JDBC Driver returns the wrong schema / column nullability

I have marked JDBC RFC 183490 as a Customer RFC now. We will begin planning to include it in an upcoming release. Obviously, I don't have a release date yet. Our mutual customer should use incident RECH34MET to let us know their required/desired timeframe for this feature, so we can use that info in our planning process.

Thanks,

--Tom