Re: [jira] Commented: (DERBY-887) Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause

[Rick Hillegas]

I have trudged some way into this bug and would like to ask the 
community's advice.

If we adopt the SQL spec's rules for casting to/from BOOLEAN, then we 
have to forbid the casting of BOOLEAN to integer types. Unfortunately, 
we have system procedures which do just this. Some of our system 
procedures cast the BOOLEAN columns in system tables to SMALLINT. In 
particular, SYSIBM.SQLGETTYPEINFO performs this cast when asked to 
retrieve ODBC type info.

I suspect we can kludge around this particular problem by replacing the 
CAST with a call to a coercing function. Howver, I fear this may be the 
tip of the iceberg. I am worried that removing the old, non-ANSI casting 
behavior (inherited from Cloudscape days) may create other backward 
compatibility problems.

I would like to ask for your guidance.

Thanks,
-Rick