Issue with column defined as varchar(1) and is Nullable.

[tbtcust]

Hi all. I have a column (Column_A) which is defined as varchar(1) and is Nullable. There are 10 rows where Column_A contains null and there are 10 rows where Column_A contains X

SELECT * FROM Schema.Table WHERE Schema.Table.Column_A <> 'X' returns 0 rows. I expect 10.

SELECT * FROM Schema.Table WHERE Schema.Table.Column_A IS NULL returns 10 rows. I expect 10.

I update 1 null row setting Column_A to Y

SELECT * FROM Schema.Table WHERE Schema.Table.Column_A <> 'X' returns 1 rows. I expect 11.


Can someone explain what is happing here?
Does it have to do with Column_A being defined as varchar(1)?
Can I avoid having to drop the table and changing Column_A to CHAR(1)?
Is there a workaround?

Thanks in advance for any help

 

[blom0344]

This is a classic case..

Null values are a special case. The expression :

Schema.Table.Column_A <> 'X'

can only be evaluated for non-null values. Evaluation for null values is not possible, hence it defaults to false and only the one value for 'Y' is returned..

[Long essays have been written about this behavior]

Ties Blom

 

[tbtcust]

Thanks blom0344. In all the years I have worked on DB2 I have never encountered this. Thanks again.

 

[Truusvlugindewind]

varchar(1) with nulls allowed? you must be owning shares in the disk-drive industry. what a waste! 200% overhead if you ask me.

 

[tbtcust]

I think you are correct Truusvlugindewind. What a waste!!! Unfortunately I inherited this DB.