SNOW-897463: SnowflakeDatabaseMetaData returning 0 not null for N/A columns in COLUMN_SIZE field from getColumns method

[tmeagher1981]

1. What version of JDBC driver are you using?
   Verified against 3.13.30, however issue looks the same on the latest source on master (SnowflakeDatabaseMetaData - https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/jdbc/SnowflakeDatabaseMetaData.java lines 1798-1820, where the latest commit on master at the time of writing was 3bffa14)

2. What operating system and processor architecture are you using?
   N/A, but ..

Edition Windows 10 Enterprise
Version 22H2
Installed on ‎25/‎01/‎2023
OS build 19045.3324
Experience Windows Feature Experience Pack 1000.19041.1000.0

Device name LAPTOP-1817UUV4
Processor 12th Gen Intel(R) Core(TM) i7-12800H 2.40 GHz
Installed RAM 32.0 GB (31.7 GB usable)
System type 64-bit operating system, x64-based processor

3. What version of Java are you using?
   N/A, but... amazon-corretto-11.0.18.10.1

4. What did you do?

Get a connection to a snowflake DB using the snowflake jdbc driver
Make a call to conn.getMetaData().getColumns(catalog, schemaPattern, tablePattern, columnPattern) to retrieve information on the columns.

Example code (creds & URL details masked):

		try (Connection conn = DriverManager.getConnection(
				"jdbc:snowflake://my.database.host.snowflakecomputing.com/?db=<someDB>&application=<someApp>&warehouse=<someWharehouse>",
				"**someUser**", "**somePassword**");) {

			ResultSet rs = conn.getMetaData().getColumns("<someCatalog>", null, null, null);

			int sqlDataTypeFieldIndex = 5;
			int sqlColumnSizeFieldIndex = 7;

			while (rs.next()) {

				int dataType = rs.getInt(sqlDataTypeFieldIndex);
				Object colSize = rs.getObject(sqlColumnSizeFieldIndex);

				switch (dataType) {
				case Types.VARCHAR:
				case Types.CHAR:
				case Types.BINARY:
				case Types.DECIMAL:
				case Types.BIGINT:
				case Types.TIME:
				case Types.TIMESTAMP:
					// DO nothing.
					break;
				default:

					if (colSize == null) {
						throw new RuntimeException("Received a null. This is what we should expect for column-types with an N/A size according to spec.");
					}
					
					assert ((Integer)colSize).intValue() == 0 : "Received non-zero. From code analysis was expecting zeroes or nulls for these types.";
					
					
					System.out.println(rs.getMetaData().getColumnName(sqlDataTypeFieldIndex) + "=" + dataType + ", "
							+ rs.getMetaData().getColumnName(sqlColumnSizeFieldIndex) + "=" + (colSize == null
									? "(null)"
									: colSize));				
				}

			}

5. What did you expect to see?

As per the JDBC spec as defined in the DatabaseMetaData javadoc:

The COLUMN_SIZE column specifies the column size for the given column. For numeric data, this is the maximum precision. For character data, this is the length in characters. For datetime datatypes, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data types where the column size is not applicable.

Therefore, if no column size is applicable for the data type in question I would expect null to be returned.
This means that I would expect a RuntimeException to be thrown from the above code for some cases.
This will never happen though (and the assertion will never be triggered) due to the code in the SnowflakeDatabaseMetaData.
From the comments in the SnowflakeDatabaseMetaData code though - especially the last line, it seems like the author is also was intending to return null.

            // The COLUMN_SIZE column specifies the column size for the given
            // column. For numeric data, this is the maximum precision. For
            // character data, this is the length in characters. For datetime
            // datatypes, this is the length in characters of the String
            // representation (assuming the maximum allowed precision of the
            // fractional seconds component). For binary data, this is the
            // length in bytes. For the ROWID datatype, this is the length in
            // bytes. Null is returned for data types where the column size
            // is not applicable.

Instead, due to the way the code is written, for all of the data types which aren't any of Types.VARCHAR, Types.CHAR, Types.BINARY, Types.DECIMAL, Types.BIGINT, Types.TIME or Types.TIMESTAMP, the driver will return 0 and not null.

6. Can you set logging to DEBUG and collect the logs?

Can attach logs if really required, but ... when I tried running these seemed pretty big and really shouldn't be necessary as the issue is trivial to reproduce and seems obvious from the code that the code deviates from what the jdbc spec says.

[sfc-gh-dszmolka]

hi - thanks for filing this one and really appreciate all the details and the reproduction 👍 ! indeed quite straightforward - we'll see how to best proceed with it.

[sfc-gh-dszmolka]

A small update here - indeed the current behaviour , unfortunately, is divergent from the standard. Seems to be introduced around the dawn and creation of this driver.

Which also means, some users might have already taken dependency on the current (non-conform) behaviour, and changing the behaviour to the conform one will break their use-case.
Thus, fixing this will be a behaviour change / breaking change release.

The team will consider this requirement for the next major release of the Snowflake JDBC driver.