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 <Ri...@Sun.COM>]

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

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

[Kathey Marsden <km...@sbcglobal.net>]

Rick Hillegas wrote:

> 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.
>
Thanks Rick for thinking about backward compatibility.

For internal changes an adjustment like  that seems  ok but the
*really*  important thing is that  we make sure that  if 
metadata.properties is being changed that these calls still work on soft
upgrade and particularly going back to an earlier version.  I have
always been an advocate of just dropping these all together when you go
up or down in version, but got no traction with this idea when I
mentioned it before.  We have had some very unfortunate bugs in the past
because of changes to the metadata file requiring time travel to fix.

There may be an external impact to some users as well.   It really
bothers me to disable f functionality that users may be relying upon,
but in this case I am really hoping it is not going to be a big problem,
since that functionality  looks pretty broken anyway and is not
documented.  You may wish to  mention the change to derby-user and see
if anyone has objections.   It is important to document this risk in the
release notes.  I have on my list to start a Jira entry or Wiki to
compile all of the changes that might affect existing users, but have
not gotten to it yet.

As for it being the tip of the iceburg, I don't know. 

Kathey