[jira] Updated: (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 (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-887:
--------------------------------

    Attachment: derby-887-01-ag-noImplicitCasts.diff

Attaching derby-887-01-ag-noImplicitCasts.diff. This patch disables the illegal implicit and explicit casting between BOOLEANs and non-string data types. I am running regression tests now.

As pointed out in previous comments, one tricky piece of this bug is the fact that the Derby metadata queries rely on illegal casts which involve BOOLEANs. The solution proposed here is:

1) Raise coercion errors when trying to bind an expression which needs to cast between BOOLEANs and non-string types.

2) Change all of the metadata queries which have illegal BOOLEAN casts. Replace the casts with calls to the INTEGER function. That function can already convert a BOOLEAN to an INT, although our user guides can't say that because technically BOOLEAN is not supported.

3) Adds new test cases to verify the correct casting behavior. One class of tests cannot be written today: the explicit casting of other data types to BOOLEAN. Those tests cannot be written until BOOLEAN is re-enabled.

Touches the following files:

M      java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
M      java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
M      java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
M      java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
M      java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
M      java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java

Raise errors if an attempt is made to coerce between BOOLEAN and a non-string data type.


M      java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java

Change metadata queries to use the INTEGER function rather than illegal casts.


M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java

Add new test cases to verify that the bug is fixed and to verify that we have disabled implicit and explict casting between BOOLEANs and non-string data types.



M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
M      java/testing/org/apache/derbyTesting/functionTests/master/subquery.out

Removed a test case which relied on illegal casts.


M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java

Reworded a test case which relied on illegal casts.


> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-887
>                 URL: https://issues.apache.org/jira/browse/DERBY-887
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.6
>         Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
>            Reporter: Manjula Kutty
>            Assignee: Rick Hillegas
>         Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
>  ij> select id,date from inbox where id = 0<3;
> ID                  |DATE
> -----------------------------------------------
> 25                  |2006-01-26 14:35:46.584
> 34                  |2006-01-26 14:36:16.588
> 21                  |2006-01-26 14:34:46.455
> 22                  |2006-01-26 14:34:47.176
> 27                  |2006-01-26 14:35:47.054
> 24                  |2006-01-26 14:35:16.58
> 28                  |2006-01-26 14:35:47.305
> 35                  |2006-01-26 14:36:18.771
> 31                  |2006-01-26 14:35:48.496
> 32                  |2006-01-26 14:35:48.887
> 33                  |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID                  |DATE
> -----------------------------------------------
> 21                  |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID                  |DATE
> -----------------------------------------------
> 22                 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.