Problem

Working in Databricks Runtime 14.3 LTS or below, you create a Delta table with a column that sets the char(255) data type in a notebook. SELECT * works to retrieve the records, but applying a filter on the column with the char(255) data type does not retrieve any records. 

Example query

Select * from <catalog-name>.<database-name>.<table-name>
Where <column-name> = “value”

Cause

In Databricks Runtime versions 14.3 LTS and below, the runtime tries to directly compare the char(255) data column name with the string value. Since it has a char type which has a length of 255 characters, it is unable to retrieve the result. 

Solution

There are three choices available. 

Use trim to remove the blank space from the right side of the string value.

Where trim(<column-name>) = “value”

Cast the column name as a string type.

Where cast(<column-name> as string) = “value”

Alternatively, upgrade to Databricks Runtime 15.4 LTS or above. These versions of Databricks Runtime handle the char(255) type using the rpad function to right-pad the column_name ‘name’ with spaces, to a total length of 255 characters. The issue therefore does not occur in Databricks Runtime 15.4 LTS or above.