Derby
  1. Derby
  2. DERBY-887

Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
    • Urgency:
      Normal
    • Bug behavior facts:
      Wrong query result

      Description

      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

      1. DERBY-887.sql
        0.3 kB
        Manjula Kutty
      2. bug887_interim.diff
        198 kB
        Rick Hillegas
      3. bug887_interim.diff
        198 kB
        Rick Hillegas
      4. derby-887-01-ag-noImplicitCasts.diff
        27 kB
        Rick Hillegas
      5. releaseNote.html
        2 kB
        Rick Hillegas
      6. derby-887-01-aj-noImplicitCasts.diff
        28 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Kathey Marsden added a comment -

          resolving for 10.5 and reassign Rick.

          Show
          Kathey Marsden added a comment - resolving for 10.5 and reassign Rick.
          Hide
          Kathey Marsden added a comment -

          Assigning to myself for back port to 10.5

          Show
          Kathey Marsden added a comment - Assigning to myself for back port to 10.5
          Hide
          Rick Hillegas added a comment -

          Committed derby-887-01-aj-noImplicitCasts.diff at subversion revision 827505.

          Show
          Rick Hillegas added a comment - Committed derby-887-01-aj-noImplicitCasts.diff at subversion revision 827505.
          Hide
          Rick Hillegas added a comment -

          Hooray. The regression tests passed cleanly on the derby-887-01-aj-noImplicitCasts.diff patch.

          Show
          Rick Hillegas added a comment - Hooray. The regression tests passed cleanly on the derby-887-01-aj-noImplicitCasts.diff patch.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-887-01-aj-noImplicitCasts.diff. This differs from the previous version of the patch as follows:

          1) The builtin INTEGER function cannot be used to correct the ODBC metadata queries because the parser turns invocations of this builtin into CASTs to INT. That, of course, fails as a result of the core changes made by this bug-fix.

          2) Instead of using the INTEGER function to correct the ODBC metadata queries, I now correct them by adding CASE clauses which map BOOLEAN values to INTEGER values.

          I am running the regression tests now.

          Show
          Rick Hillegas added a comment - Attaching derby-887-01-aj-noImplicitCasts.diff. This differs from the previous version of the patch as follows: 1) The builtin INTEGER function cannot be used to correct the ODBC metadata queries because the parser turns invocations of this builtin into CASTs to INT. That, of course, fails as a result of the core changes made by this bug-fix. 2) Instead of using the INTEGER function to correct the ODBC metadata queries, I now correct them by adding CASE clauses which map BOOLEAN values to INTEGER values. I am running the regression tests now.
          Hide
          Rick Hillegas added a comment -

          There are a 108 errors in DatabaseMetaDataTest, so this patch needs a little work.

          Show
          Rick Hillegas added a comment - There are a 108 errors in DatabaseMetaDataTest, so this patch needs a little work.
          Hide
          Rick Hillegas added a comment -

          Attaching first rev of a release note for this bug-fix.

          Show
          Rick Hillegas added a comment - Attaching first rev of a release note for this bug-fix.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          The legal SQL casts are laid out in a table in the SQL Standard, part 2, section 6.12 (<cast specification>), syntax rule 6. The only allowed casts involving booleans are these:

          1) the cast of a boolean to a character type

          2) the cast of a character type to a boolean

          Show
          Rick Hillegas added a comment - The legal SQL casts are laid out in a table in the SQL Standard, part 2, section 6.12 (<cast specification>), syntax rule 6. The only allowed casts involving booleans are these: 1) the cast of a boolean to a character type 2) the cast of a character type to a boolean
          Hide
          Rick Hillegas added a comment -

          Marking as wrong query result.

          Show
          Rick Hillegas added a comment - Marking as wrong query result.
          Hide
          Rick Hillegas added a comment -

          Triaged for 10.5.3: assigned normal urgency,

          Show
          Rick Hillegas added a comment - Triaged for 10.5.3: assigned normal urgency,
          Hide
          Jazarine Jamal added a comment -

          i encoutnered the following problem when trying to run the queries.....

          ij> create table test887(id bigint);
          0 rows inserted/updated/deleted

          ij> INSERT INTO TEST887 VALUES (801), (802), (803), (804);

          ij> select * from test887 where id = 0<3;
          ID
          --------------------
          801
          802
          803
          804

          ij> select * from test887 where id = true;
          ERROR 42X01: Syntax error: true.

          i dont understand why is the second statement giving a syntax error while the first one generates the correct output....
          both (0<3) and true are equivalent....

          Show
          Jazarine Jamal added a comment - i encoutnered the following problem when trying to run the queries..... ij> create table test887(id bigint); 0 rows inserted/updated/deleted ij> INSERT INTO TEST887 VALUES (801), (802), (803), (804); ij> select * from test887 where id = 0<3; ID -------------------- 801 802 803 804 ij> select * from test887 where id = true; ERROR 42X01: Syntax error: true. i dont understand why is the second statement giving a syntax error while the first one generates the correct output.... both (0<3) and true are equivalent....
          Hide
          Rick Hillegas added a comment -

          Unassigning myself from this issue. We have moved out the timeframe for fixing this bug.

          Show
          Rick Hillegas added a comment - Unassigning myself from this issue. We have moved out the timeframe for fixing this bug.
          Hide
          Rick Hillegas added a comment -

          Detaching this issue from 10.3. Fixing it seems to require changing the non-ANSI behavior of implicit and explicit casts. Changing this behavior probably will affect existing applications and it seems to be the sort of fix which we need to defer to a major release. That is, this is appropriate for 11.0 but not 10.3.

          Show
          Rick Hillegas added a comment - Detaching this issue from 10.3. Fixing it seems to require changing the non-ANSI behavior of implicit and explicit casts. Changing this behavior probably will affect existing applications and it seems to be the sort of fix which we need to defer to a major release. That is, this is appropriate for 11.0 but not 10.3.
          Hide
          Rick Hillegas added a comment -

          Reducing the priority of this bug. With the patch for DERBY-1029, the behavior has reverted to what it was in 10.1.

          Show
          Rick Hillegas added a comment - Reducing the priority of this bug. With the patch for DERBY-1029 , the behavior has reverted to what it was in 10.1.
          Hide
          Rick Hillegas added a comment -

          This issue is related to DERBY-1029.

          Show
          Rick Hillegas added a comment - This issue is related to DERBY-1029 .
          Hide
          Satheesh Bandaram added a comment -

          Moving this to fix under 10.3. David proposed that boolean work be left on trunk to be completed in 10.3 timeframe and removed from 10.2 once a branch is cut. Since the rollback of boolean work for 10.2 is being tracked under DERBY-1029, I am moving this one to 10.3.

          Rick, I am assigning this 10.3 issue back to you... expecting you would be working on this for 10.3. Let me know if this is incorrect.

          Show
          Satheesh Bandaram added a comment - Moving this to fix under 10.3. David proposed that boolean work be left on trunk to be completed in 10.3 timeframe and removed from 10.2 once a branch is cut. Since the rollback of boolean work for 10.2 is being tracked under DERBY-1029 , I am moving this one to 10.3. Rick, I am assigning this 10.3 issue back to you... expecting you would be working on this for 10.3. Let me know if this is incorrect.
          Hide
          Rick Hillegas added a comment -

          Hi Satheesh,

          I have attached my half-baked work on this bug: bug887_interim.diff. If someone else wants to pick up this JIRA, they may find that patch useful. In particular, the patch contains changes to various datatype classes which disable the illegal explicit and implicit casts. However, although the patch fixes the bugs, it breaks some other functionality including some unit tests. In particular, it breaks the ODBC metadata mapping, which relies on these illegal casts. My fix to that problem was to add a system function which performs the job formerly done by the illegal cast.

          I have not worked on this for a week and a half and it's clear that I am swamped with other work. I too would like to see this bug fixed for 10.2 but the reality is, I am not going to get to it soon. If someone else is itching badly, please go ahead and scratch.

          Show
          Rick Hillegas added a comment - Hi Satheesh, I have attached my half-baked work on this bug: bug887_interim.diff. If someone else wants to pick up this JIRA, they may find that patch useful. In particular, the patch contains changes to various datatype classes which disable the illegal explicit and implicit casts. However, although the patch fixes the bugs, it breaks some other functionality including some unit tests. In particular, it breaks the ODBC metadata mapping, which relies on these illegal casts. My fix to that problem was to add a system function which performs the job formerly done by the illegal cast. I have not worked on this for a week and a half and it's clear that I am swamped with other work. I too would like to see this bug fixed for 10.2 but the reality is, I am not going to get to it soon. If someone else is itching badly, please go ahead and scratch.
          Hide
          Rick Hillegas added a comment -

          Hi Satheesh,

          I have attached my half-baked work on this bug: bug887_interim.diff. If someone else wants to pick up this JIRA, they may find that patch useful. In particular, the patch contains changes to various datatype classes which disable the illegal explicit and implicit casts. However, although the patch fixes the bugs, it breaks some other functionality including some unit tests. In particular, it breaks the ODBC metadata mapping, which relies on these illegal casts. My fix to that problem was to add a system function which performs the job formerly done by the illegal cast.

          I have not worked on this for a week and a half and it's clear that I am swamped with other work. I too would like to see this bug fixed for 10.2 but the reality is, I am not going to get to it soon. If someone else is itching badly, please go ahead and scratch.

          Show
          Rick Hillegas added a comment - Hi Satheesh, I have attached my half-baked work on this bug: bug887_interim.diff. If someone else wants to pick up this JIRA, they may find that patch useful. In particular, the patch contains changes to various datatype classes which disable the illegal explicit and implicit casts. However, although the patch fixes the bugs, it breaks some other functionality including some unit tests. In particular, it breaks the ODBC metadata mapping, which relies on these illegal casts. My fix to that problem was to add a system function which performs the job formerly done by the illegal cast. I have not worked on this for a week and a half and it's clear that I am swamped with other work. I too would like to see this bug fixed for 10.2 but the reality is, I am not going to get to it soon. If someone else is itching badly, please go ahead and scratch.
          Hide
          Satheesh Bandaram added a comment -

          Rick, do you know if you or someone else might be able to address this issue soon? I thought, at least, part of the problem was introduced by boolean type work. If it can be deemed a "regression", I think this bug should be on high priority list for someone. You know who is looking for regressions in the product, very closely

          Show
          Satheesh Bandaram added a comment - Rick, do you know if you or someone else might be able to address this issue soon? I thought, at least, part of the problem was introduced by boolean type work. If it can be deemed a "regression", I think this bug should be on high priority list for someone. You know who is looking for regressions in the product, very closely
          Hide
          Rick Hillegas added a comment -

          I am relinquishing this bug right now. It has been crowded off my calendar.

          Show
          Rick Hillegas added a comment - I am relinquishing this bug right now. It has been crowded off my calendar.
          Hide
          Kathey Marsden added a comment -

          Thanks Rick for looking at this issue.
          As part of the fix, can you add BOOLEAN to the the cast and comparison tables in the lang/casting.java test?

          Thanks

          Kathey

          Show
          Kathey Marsden added a comment - Thanks Rick for looking at this issue. As part of the fix, can you add BOOLEAN to the the cast and comparison tables in the lang/casting.java test? Thanks Kathey
          Hide
          Rick Hillegas added a comment -

          Although DERBY-499 introduced some additional casting cases, the root causes of this bug are in an older version of Derby. Older releases allow illegal comparisons with BOOLEANs. In addition, the problem query can be expressed in a way which gets past the 10.1.1.0 parser and produces incorrect results. The following incorrect results may be observed in 10.1.1.0:

          ij> create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2), primary key(id))
          ;
          0 rows inserted/updated/deleted
          ij> insert into t(col1) values('ca');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('ba');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('ea');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('dd');
          1 row inserted/updated/deleted
          ij> select * from t ;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where id=0<3;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where (id=0)<3;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where id=(0<3);
          ID |COL1
          -------------------------
          2 |ba

          1 row selected

          I think there are at least two bugs in here:

          o A pre-existing 10.1.1.0 bug.
          o Some illegal casts introduced by DERBY-499.

          Show
          Rick Hillegas added a comment - Although DERBY-499 introduced some additional casting cases, the root causes of this bug are in an older version of Derby. Older releases allow illegal comparisons with BOOLEANs. In addition, the problem query can be expressed in a way which gets past the 10.1.1.0 parser and produces incorrect results. The following incorrect results may be observed in 10.1.1.0: ij> create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2), primary key(id)) ; 0 rows inserted/updated/deleted ij> insert into t(col1) values('ca'); 1 row inserted/updated/deleted ij> insert into t(col1) values('ba'); 1 row inserted/updated/deleted ij> insert into t(col1) values('ea'); 1 row inserted/updated/deleted ij> insert into t(col1) values('dd'); 1 row inserted/updated/deleted ij> select * from t ; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where id=0<3; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where (id=0)<3; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where id=(0<3); ID |COL1 ------------------------- 2 |ba 1 row selected I think there are at least two bugs in here: o A pre-existing 10.1.1.0 bug. o Some illegal casts introduced by DERBY-499 .
          Hide
          Rick Hillegas added a comment -

          If you add an explicit cast to the problem query, it returns "correct" results:

          select * from t where cast(id as boolean) = true;

          However, as Bernt points out, the cast (explicit or implicit) is illegal according to the ANSI spec.

          So, I propose to fix this bug by forbidding these casts, unless someone objects. Thanks to Manjula for logging this issue and for everyone's feedback so far.

          Show
          Rick Hillegas added a comment - If you add an explicit cast to the problem query, it returns "correct" results: select * from t where cast(id as boolean) = true; However, as Bernt points out, the cast (explicit or implicit) is illegal according to the ANSI spec. So, I propose to fix this bug by forbidding these casts, unless someone objects. Thanks to Manjula for logging this issue and for everyone's feedback so far.
          Hide
          Daniel John Debrunner added a comment -

          Fixing this will also disable previous queries that compiled successfully and possibly returned "valid" results. I couldn't tell from the original description if this query was returning all rows in the table.

          select id,date from inbox where id = 0<3

          Show
          Daniel John Debrunner added a comment - Fixing this will also disable previous queries that compiled successfully and possibly returned "valid" results. I couldn't tell from the original description if this query was returning all rows in the table. select id,date from inbox where id = 0<3
          Hide
          Kathey Marsden added a comment -

          I am marking this as critical since we get wrong results for a query that previously failed with a syntax error.

          Show
          Kathey Marsden added a comment - I am marking this as critical since we get wrong results for a query that previously failed with a syntax error.
          Hide
          Kathey Marsden added a comment -

          marking this issue to block DERBY-499.

          With Dan's review of the first checkin he noted that there was still work needed in the area of casting and comparisons for DERBY-499. Hopefully that work will resolve this issue.

          ignore previous link from 877. Slipped a digit

          Show
          Kathey Marsden added a comment - marking this issue to block DERBY-499 . With Dan's review of the first checkin he noted that there was still work needed in the area of casting and comparisons for DERBY-499 . Hopefully that work will resolve this issue. ignore previous link from 877. Slipped a digit
          Hide
          Rajesh Kartha added a comment -

          Bernt Johnsen replied to my question on the SQL standard on the mailing list. Following is the archive link:

          http://article.gmane.org/gmane.comp.apache.db.derby.devel/13128

          <quote>

          The SQL spec (99) chapter 6.22 states that a BOOLEAN may be converted
          (with a CAST specification) to CHAR, VARCHAR, BOOLEAN and CLOB and
          that CHAR, VARCHAR, CLOB and BOOLEAN may be converted to BOOLEAN.

          <unquote>

          So if the BOOLEAN and INT types are not compatabile, the select statements with 'id=0<3' and 'id=true'
          needs to throw an error.

          Show
          Rajesh Kartha added a comment - Bernt Johnsen replied to my question on the SQL standard on the mailing list. Following is the archive link: http://article.gmane.org/gmane.comp.apache.db.derby.devel/13128 <quote> The SQL spec (99) chapter 6.22 states that a BOOLEAN may be converted (with a CAST specification) to CHAR, VARCHAR, BOOLEAN and CLOB and that CHAR, VARCHAR, CLOB and BOOLEAN may be converted to BOOLEAN. <unquote> So if the BOOLEAN and INT types are not compatabile, the select statements with 'id=0<3' and 'id=true' needs to throw an error.
          Hide
          Rajesh Kartha added a comment -

          Would be interesting to know what the SQL standard proposes for comparing int and boolean.

          Also, does boolean in Derby mean false (0) or true (>0) ?

          In that case, if comparisions are allowed, one would expect all the rows with id>0 to be returned for the

          select * from t where id=true;

          statement under both the scenarios (with and without primary key).

          The results in case of the table without the primary key looks consistent and so does the id=0<3 statement.

          Show
          Rajesh Kartha added a comment - Would be interesting to know what the SQL standard proposes for comparing int and boolean. Also, does boolean in Derby mean false (0) or true (>0) ? In that case, if comparisions are allowed, one would expect all the rows with id>0 to be returned for the select * from t where id=true; statement under both the scenarios (with and without primary key). The results in case of the table without the primary key looks consistent and so does the id=0<3 statement.
          Hide
          Manjula Kutty added a comment -

          Even I feel the same. Still, if it is valid then it should show consistent behaviour.
          I just happend to run this query and was expecting an error message , but ij returned me some rows. In my schema I had primary key on the column 'id'. If I remove the primary key then every thing works fine

          I'm attaching the script to reproduce the problem.

          The derby jars I have used are :

          [C:\pantry\derby.jar] 10.2.0.0 alpha - (372715)
          [C:\pantry\derbytools.jar] 10.2.0.0 alpha - (372715)
          ......................................................................................................

          Here is the results with primary key :
          ij> create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2), primary key(id));
          0 rows inserted/updated/deleted
          ij> insert into t(col1) values('ca');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('ba');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('ea');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('dd');
          1 row inserted/updated/deleted
          ij> select * from t ;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where id=0<3;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where id=true;
          ID |COL1
          -------------------------
          2 |ba

          --------------------------------------------------------
          With out primary key

          ij>create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2));
          0 rows inserted/updated/deleted
          ij> insert into t(col1) values('ca');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('ba');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('ea');
          1 row inserted/updated/deleted
          ij> insert into t(col1) values('dd');
          1 row inserted/updated/deleted
          ij> select * from t ;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where id=0<3;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected
          ij> select * from t where id=true;
          ID |COL1
          -------------------------
          1 |ca
          2 |ba
          3 |ea
          4 |dd

          4 rows selected

          Show
          Manjula Kutty added a comment - Even I feel the same. Still, if it is valid then it should show consistent behaviour. I just happend to run this query and was expecting an error message , but ij returned me some rows. In my schema I had primary key on the column 'id'. If I remove the primary key then every thing works fine I'm attaching the script to reproduce the problem. The derby jars I have used are : [C:\pantry\derby.jar] 10.2.0.0 alpha - (372715) [C:\pantry\derbytools.jar] 10.2.0.0 alpha - (372715) ...................................................................................................... Here is the results with primary key : ij> create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2), primary key(id)); 0 rows inserted/updated/deleted ij> insert into t(col1) values('ca'); 1 row inserted/updated/deleted ij> insert into t(col1) values('ba'); 1 row inserted/updated/deleted ij> insert into t(col1) values('ea'); 1 row inserted/updated/deleted ij> insert into t(col1) values('dd'); 1 row inserted/updated/deleted ij> select * from t ; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where id=0<3; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where id=true; ID |COL1 ------------------------- 2 |ba -------------------------------------------------------- With out primary key ij>create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2)); 0 rows inserted/updated/deleted ij> insert into t(col1) values('ca'); 1 row inserted/updated/deleted ij> insert into t(col1) values('ba'); 1 row inserted/updated/deleted ij> insert into t(col1) values('ea'); 1 row inserted/updated/deleted ij> insert into t(col1) values('dd'); 1 row inserted/updated/deleted ij> select * from t ; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where id=0<3; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected ij> select * from t where id=true; ID |COL1 ------------------------- 1 |ca 2 |ba 3 |ea 4 |dd 4 rows selected
          Hide
          Manjula Kutty added a comment -

          Even I feel the same. Still if it is valid then it should show consistent behaviour.
          I just happend to run this query and was expecting an error message , but ij returned me some rows. In my schema I had primary key on the column id. If I remove the primary key then evry thing works fine

          I'm attaching the script to reproduce the problem.

          The derby jars I have used are :

          Show
          Manjula Kutty added a comment - Even I feel the same. Still if it is valid then it should show consistent behaviour. I just happend to run this query and was expecting an error message , but ij returned me some rows. In my schema I had primary key on the column id. If I remove the primary key then evry thing works fine I'm attaching the script to reproduce the problem. The derby jars I have used are :
          Hide
          Daniel John Debrunner added a comment -

          Is it valid to compare integer columns to boolean columns?

          Show
          Daniel John Debrunner added a comment - Is it valid to compare integer columns to boolean columns?
          Hide
          Rick Hillegas added a comment -

          Hi Manjula,

          Could you attach a script (including the ddl and table population) which demonstrates this problem? The following simplified test seems to work correctly. Thanks.

          ij> connect 'jdbc:derby:testdb';
          ij> --connect 'jdbc:derby:testdb;create=true';
          --connect 'jdbc:derby://localhost:8246/testdb';

          drop table t1;
          0 rows inserted/updated/deleted
          ij> create table t1( id bigint );
          0 rows inserted/updated/deleted
          ij> insert into t1 ( id ) values ( 0 ), ( 1 ), ( 2 ), ( 4 );
          4 rows inserted/updated/deleted
          ij> select * from t1
          where id = true;
          ID
          --------------------
          1
          2
          4

          3 rows selected
          ij> select * from t1 where id = 0<3;
          ID
          --------------------
          1
          2
          4

          3 rows selected

          Show
          Rick Hillegas added a comment - Hi Manjula, Could you attach a script (including the ddl and table population) which demonstrates this problem? The following simplified test seems to work correctly. Thanks. ij> connect 'jdbc:derby:testdb'; ij> --connect 'jdbc:derby:testdb;create=true'; --connect 'jdbc:derby://localhost:8246/testdb'; drop table t1; 0 rows inserted/updated/deleted ij> create table t1( id bigint ); 0 rows inserted/updated/deleted ij> insert into t1 ( id ) values ( 0 ), ( 1 ), ( 2 ), ( 4 ); 4 rows inserted/updated/deleted ij> select * from t1 where id = true; ID -------------------- 1 2 4 3 rows selected ij> select * from t1 where id = 0<3; ID -------------------- 1 2 4 3 rows selected

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Manjula Kutty
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development