|
Is it valid to compare integer columns to boolean columns?
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 : 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 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. 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. I am marking this as critical since we get wrong results for a query that previously failed with a syntax error.
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 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. 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. 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 I am relinquishing this bug right now. It has been crowded off my calendar.
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 :)
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. 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. 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
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. This issue is related to
Reducing the priority of this bug. With the patch for
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.
Unassigning myself from this issue. We have moved out the timeframe for fixing this bug.
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.... Triaged for 10.5.3: assigned normal urgency,
Marking as wrong query result.
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 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. Attaching first rev of a release note for this bug-fix.
There are a 108 errors in DatabaseMetaDataTest, so this patch needs a little work.
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. Hooray. The regression tests passed cleanly on the derby-887-01-aj-noImplicitCasts.diff patch.
Committed derby-887-01-aj-noImplicitCasts.diff at subversion revision 827505.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Resolved
Major
Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
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